Build a Ruby App with CockroachDB and ActiveRecord

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 Select a plan page, select Serverless.
5. On the Cloud & Regions page, select a cloud provider (GCP or AWS) in the Cloud provider section.
6. In the Regions section, select a region for the cluster. Refer to CockroachDB Cloud Regions for the regions where CockroachDB Serverless clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions. A cluster can have at most six regions.
7. Click Next: Capacity.
8. On the Capacity page, select Start for free. Click Next: Finalize.

9. On the Finalize page, click Create cluster.

   Your cluster will be created in a few seconds and the Create SQL user dialog will display.

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:

   Mac Linux Windows

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

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

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

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

   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%/.postgresql/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 for the cluster, found at https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users.

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

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

   cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt"
   

   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 cluster.
   • <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:

   $ 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.

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

   > CREATE DATABASE bank;
   

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

   > CREATE DATABASE bank;
   

ActiveRecord::Base.establish_connection(

  # Specify the CockroachDB ActiveRecord adapter
  adapter:     'cockroachdb',
  url: ENV['DATABASE_URL'].gsub('postgresql://', 'cockroachdb://').concat('&application_name=docs_simplecrud_activerecord')
)

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.

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