Build a Ruby App with CockroachDB and ActiveRecord

Warning:
Cockroach Labs will stop providing Assistance Support for this version on May 10, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

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-name>-<tenant-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-name>-<tenant-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-name>-<tenant-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 CockroachDB Serverless (beta) cluster.
    • <cluster-name>-<tenant-id> is the short name of your cluster plus the tenant ID. For example, funny-skunk-123. The <tenant-id> is used to identify your tenant cluster on a multitenant host.
    • <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

    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 \
    --empty
    

    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.

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

    # Connection parameters:
    #   (console) http://127.0.0.1:61009
    #   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
    #   (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require    
    

    In this example, the port number is 61011. You will use the port number in your application code later.

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;
    

Step 3. Get the code

Clone the code's GitHub repository.

icon/buttons/copy
git clone https://github.com/cockroachlabs/example-app-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 Serverless (beta) 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 dialog, 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