Build a Ruby App with CockroachDB and the Ruby pg Driver

This tutorial shows you how build a simple Ruby application with CockroachDB and the Ruby pg driver.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachCloud. The instructions below will adjust accordingly.

Create a free cluster

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


    This cluster will be free forever.

  5. (Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.

  6. 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 Connection info 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. Click the name of the cc-ca.crt to download the CA certificate to your local machine.
  2. Create a certs directory on your local machine:

    $ mkdir certs
  3. Move the downloaded cc-ca.crt file to the certs directory:

    $ mv <path>/<to>/cc-ca.crt <path>/<to>/certs

    For example:

    $ mv Users/maxroach/Downloads/cc-ca.crt Users/maxroach/certs
  4. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).


    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.

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

    $ cockroach demo \

    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)
    #   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
    #   (sql/tcp) postgres://root:admin@    

    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:

  2. Create a SQL user for your app:

    > 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:

    > 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 CockroachCloud Console earlier:

    $ 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 CockroachCloud 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:


Step 3. Get the code

Clone the code's GitHub repository.

git clone

The code connects as the user you created and executes some basic SQL statements: creating a table, inserting rows, and reading and printing the rows.

Check out the cockroachcloud branch:

git checkout cockroachcloud

Step 4. Configure the dependencies

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


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


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

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

Step 5. Install the dependencies

bundle install

Step 6. Update the connection parameters

Update the connection parameters to connect to your cluster.


  conn = PG.connect(
    user: '{username}',
    password: '{password}',
    dbname: 'bank',
    host: 'localhost',
    port: {port},
    sslmode: 'require'

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.


  conn = PG.connect(
    user: '{username}',
    password: '{password}',
    dbname: '{cluster_name}.bank',
    host: '{globalhost}',
    port: 26257,
    sslmode: 'verify-full',
    sslrootcert: '{path to the CA certificate}'


  • {username} and {password} specify the SQL username and password that you created earlier.
  • {globalhost} is the name of the CockroachCloud Free (beta) host (e.g.,
  • {path to the CA certificate} is the path to the cc-ca.crt file that you downloaded from the CockroachCloud Console.
  • {cluster_name} is the name of your cluster.

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.

ruby main.rb

The output should be:

print_balances(): Balances as of '2021-02-23 11:56:54 -0800':
{"id"=>"1", "balance"=>"1000"}
{"id"=>"2", "balance"=>"250"}
transfer_funds(): Trying to transfer 100 from account 1 to account 2
print_balances(): Balances as of '2021-02-23 11:56:55 -0800':
{"id"=>"1", "balance"=>"900"}
{"id"=>"2", "balance"=>"350"}

What's next?

Read more about using the Ruby pg driver.

You might also be interested in the following pages:

YesYes NoNo