Build a Ruby App with CockroachDB and ActiveRecord

This tutorial shows you how build a simple Ruby application with CockroachDB and ActiveRecord. CockroachDB provides an ActiveRecord adapter for CockroachDB as a RubyGem.

Tip:

For a more realistic use of ActiveRecord with CockroachDB in a Rails app, see our examples-orms repository.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachDB Serverless (beta). The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachDB Cloud account.
  2. Log in to your CockroachDB Cloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select CockroachDB Serverless.

    Note:

    This cluster will be free forever.

  5. Click Create your free cluster.

Your cluster will be created in approximately 20-30 seconds.

Set up your cluster connection

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

  1. In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:

    icon/buttons/copy

    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
    

    Your cert file will be downloaded to %APPDATA%/.postgres/root.crt.

  2. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

    Warning:

    This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

    icon/buttons/copy

    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-id>'
    

    icon/buttons/copy
    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-id>'
    
    icon/buttons/copy
    cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt&options=--cluster=<cluster-id>"
    

    Where:

    • <username> is the SQL user. By default, this is your CockroachDB Cloud account username.
    • <password> is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.
    • <serverless-host> is the hostname of the serverless cluster.
    • <cluster-name> is the short name of your cluster plus the tenant ID. For example, funny-skunk-3. The <cluster-name> is used to identify your tenant cluster on a multitenant host.

    You can find these settings in the Connection parameters tab of the Connection info dialog.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    icon/buttons/copy
    $ cockroach demo \
    --no-example-database
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

    Note:

    If cockroach demo fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory ~/.postgresql.

  3. Take note of the (sql) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
    #   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
    #   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
    

Step 2. Create a database

  1. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    icon/buttons/copy
    > CREATE USER <username> WITH PASSWORD <password>;
    

    Take note of the username and password. You will use it in your application code later.

  3. Give the user the necessary permissions:

    icon/buttons/copy
    > GRANT ALL ON DATABASE bank TO <username>;
    
  1. If you haven't already, download the CockroachDB binary.
  2. Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console earlier:

    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'
    

    In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the <certs_dir> placeholder with the path to the certs directory that you created earlier.

  3. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  4. Exit the SQL shell:

    icon/buttons/copy
    > \q
    

Step 3. Get the code

Clone the code's GitHub repository.

icon/buttons/copy
git clone https://github.com/cockroachlabs/hello-world-ruby-activerecord

Check out the 5.2 branch:

icon/buttons/copy

git checkout 5.2

Check out the cockroachcloud branch:

icon/buttons/copy

git checkout cockroachcloud

Check out the cockroachcloud-5.2 branch:

icon/buttons/copy
git checkout cockroachcloud-5.2

Step 4. Configure the dependencies

  1. Install libpq for your platform. For example, to install it on Mac with Homebrew:

    icon/buttons/copy

    brew install libpq
    
  2. Configure bundle to use libpq. For example, if you installed libpq on Mac using Homebrew:

    icon/buttons/copy

    bundle config --local build.pg --with-opt-dir="/usr/local/opt/libpq"
    

    Set --with-opt-dir to the location of libpq on your OS.

Step 5. Install the dependencies

icon/buttons/copy
bundle install

Step 6. Update the connection parameters

Update the connection parameters to connect to your cluster.

icon/buttons/copy

ActiveRecord::Base.establish_connection(

  # Specify the CockroachDB ActiveRecord adapter
  adapter:     'cockroachdb',
  username:    'maxroach',
  password:    'password',
  database:    'bank',
  host:        'localhost',
  port:        26257,
  sslmode:     'require'

  #sslrootcert: 'certs/ca.crt'
)

Where {port} is the port number from the connection string you noted earlier, {username} is the database username you created, and {password} is the database user's password.

icon/buttons/copy

ActiveRecord::Base.establish_connection(

  # Specify the CockroachDB ActiveRecord adapter
  adapter:     'cockroachdb',
  username:    '{user}',
  password:    '{password}',
  database:    '{cluster_name}.bank',
  host:        '{globalhost}',
  port:        26257,
  sslmode:     'verify-full',
  sslrootcert: '{path to the CA certificate}'
)

Where:

  • {username} and {password} specify the SQL username and password that you created earlier.
  • {globalhost} is the name of the CockroachDB Cloud free tier host (e.g., free-tier.gcp-us-central1.cockroachlabs.cloud).
  • {path to the CA certificate} is the path to the cc-ca.crt file that you downloaded from the CockroachDB Cloud Console.
  • {cluster_name} is the name of your cluster.
Note:

If you are using the connection string that you copied from the Connection info modal, your username, password, hostname, and cluster name will be pre-populated.

Step 7. Run the Ruby code

Run the code to create a table and insert some rows, and then you'll run code to read and update values as an atomic transaction.

icon/buttons/copy
ruby main.rb

The output should be:

-- create_table(:accounts, {:force=>true, :id=>:integer})
   -> 0.3951s
account: 1 balance: 1000
account: 2 balance: 250

What's next?

Read more about using ActiveRecord, or check out a more realistic implementation of ActiveRecord with CockroachDB in a Rails app in our examples-orms repository.

You might also be interested in the following pages:

YesYes NoNo