Using CockroachDB with Rails

When I work in Rails apps, I don’t find myself worrying too much about the database.

Since Rails natively supports popular databases like MySQL and PostgreSQL, I usually only need to make a few config changes to get an application’s database up and running. I don’t find myself running into too many problems using databases that Rails doesn’t support either.

Thanks to Rails’ well documented database interface and strong community support, I still only need a few config changes to use databases like Oracle and SQL Server.

If you’re with me so far, then it should come as no surprise that it’s just as easy to use CockroachDB with Rails! 🎉

What’s a CockroachDB??

Glad you asked! CockroachDB, built by Cockroach Labs, is a database that’s designed to be scalable and highly available. It also uses the PostgreSQL wire protocol so you can use it almost anywhere you’d use PostgreSQL. Almost (more on that later).

So how do we use CockroachDB with Rails? Since I like learning by example, let’s configure an existing Rails app to use CockroachDB.

Using CockroachDB with Rails

In this example, we’re going to change the CodeTriage Rails app so it uses CockroachDB instead of PostgreSQL.

After following the CodeTriage contributing guide to get the app running locally, the app will be ready to talk to PostgreSQL.

To switch to using CockroachDB, we’ll first need to install and configure CockroachDB.

Install CockroachDB

First, install CockroachDB per the install guide. Next, we’ll use the cockroach demo command to create a single-node CockroachDB cluster. We’ll run the command with the --empty flag so the we don’t run into any conflicts loading the CodeTriage schema later.

$ cockroach demo --empty
#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB cluster of 1 node.
#
# This demo session will attempt to enable enterprise features
# by acquiring a temporary license from Cockroach Labs in the background.
# To disable this behavior, set the environment variable
# COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true.
#
# Reminder: your changes to data stored in the demo session will not be saved!
#
# Connection parameters:
#   (console) http://127.0.0.1:63115
#   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fzj%2F41x2d76s4kq4vv8_c8qrl1z00000gn%2FT%2Fdemo900101820&port=26257
#   (sql/tcp) postgres://root:admin@127.0.0.1:63117?sslmode=require
#
#
# The user "root" with password "admin" has been created. Use it to access the Web UI!
#
# Server version: CockroachDB CCL v20.2.5 (x86_64-apple-darwin14, built 2021/02/16 12:57:34, go1.13.14) (same version as client)
# Cluster ID: 83ec1cc1-4b7a-410f-b0b4-dea5ea562b9b
#
# Enter \? for a brief introduction.
#
root@127.0.0.1:63117/defaultdb>

After the cockroach demo command creates the empty database, it opens an interactive SQL shell. The demo database only exists in memory while the shell is open, so we’ll keep it open until we’re done.

The cockroach demo command also gives us some information on how to connect to it.

# Connection parameters:
#   (console) http://127.0.0.1:63115
#   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fzj%2F41x2d76s4kq4vv8_c8qrl1z00000gn%2FT%2Fdemo900101820&port=26257
#   (sql/tcp) postgres://root:admin@127.0.0.1:63117?sslmode=require

From this information we can see

1. we have a user named root with password admin
2. the CockroachDB server is listenting at 127.0.0.1 (a.k.a. localhost) at port 63117
3. and sslmode is set to require

Most of these details will be the same when you run the cockroach demo command, but the port might be different.

Take note of these connection details as we’ll need them later.

Now that CockroachDB is up and running locally, we’re ready to make some config changes to CodeTriage.

Add the ActiveRecord CockroachDB Adapter

First, we’ll edit the Gemfile and replace the pg gem with the ActiveRecord CockroachDB Adapter gem. Since CodeTriage is currently running against Rails 6.1, we’ll install v6.1.0.beta1 of the ActiveRecord CockroachDB Adapter.

--- a/Gemfile
+++ b/Gemfile
@@ -31,7 +31,7 @@ gem 'local_time', '2.1.0'
 gem 'maildown', '~> 3.1'
 gem 'omniauth', '~> 1.9.1'
 gem 'omniauth-github'
-gem 'pg'
+gem 'activerecord-cockroachdb-adapter', '6.1.0beta1'
 gem 'puma'
 gem 'rack-timeout'
 gem 'rrrretry'

Then after installing the gem with bundle install, we’ll make some changes to config/database.yml.

Configure CodeTriage to use the ActiveRecord CockroachDB Adapter

First, we’ll change the adapter value from postgresql to cockroachdb.

--- a/config/database.yml
+++ b/config/database.yml
@@ -1,5 +1,5 @@
 defaults: &defaults
-  adapter: postgresql
+  adapter: cockroachdb
   encoding: utf8
   pool: 5
   host: localhost

Next, we’ll grab the connection details we noted earlier from the CockroachDB interactive SQL shell

 
1. we have a user named root with password admin
 
2. the CockroachDB server is listenting at 127.0.0.1 (a.k.a. localhost) at port 63117
 
3. and sslmode is set to require

and set port, user, password, and requiressl.

--- a/config/database.yml
+++ b/config/database.yml
@@ -3,7 +3,10 @@ defaults: &defaults
   encoding: utf8
   pool: 5
   host: localhost
-  password:
+  port: 63117
+  user: root
+  password: admin
+  requiressl: true

Now CodeTriage should be ready to use CockroachDB! Let’s set up the database by running bin/rake db:create db:schema:load db:seed.

$ bin/rake db:create db:schema:load db:seed
Created database 'triage_development'
Created database 'triage_test'
rake aborted!
ActiveRecord::StatementInvalid: PG::FeatureNotSupported: ERROR:  unimplemented: extension "pg_stat_statements" is not yet supported
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/54516/v20.2
/Users/alimi/.rvm/gems/ruby-2.7.2/gems/activerecord-6.1.0/lib/active_record/connection_adapters/postgresql_adapter.rb:678:in `exec_params'
/Users/alimi/.rvm/gems/ruby-2.7.2/gems/activerecord-6.1.0/lib/active_record/connection_adapters/postgresql_adapter.rb:678:in `block (2 levels) in exec_no_cache'
/Users/alimi/.rvm/gems/ruby-2.7.2/gems/activesupport-6.1.0/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'

Uhh…that doesn’t look good. 😅

CockroachDB quacks like PostgreSQL but it isn’t PostgreSQL

If we take a look at that last command/error again, we can see the CodeTriage databases were created in CockroachDB.

$ bin/rake db:create db:schema:load db:seed
Created database 'triage_development'
Created database 'triage_test'

But things went wrong when trying to load the database schema from db/schema.rb.

rake aborted!
ActiveRecord::StatementInvalid: PG::FeatureNotSupported: ERROR:  unimplemented: extension "pg_stat_statements" is not yet supported
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/54516/v20.2

In db/schema.rb, CodeTriage is enabling the pg_stat_statements extension but as the error tells us CockroachDB doesn’t support it.

Although CockroachDB uses the PostgreSQL wire protocol and acts a lot like PostgreSQL, it’s very important to remember CockroachDB ain’t PostgreSQL. You can use CockroachDB as if it were PostgreSQL in a lot of places which means you won’t have to learn a bunch of new stuff to use it. But you might run into small differences in behavior like this.

For demonstrations purposes, we’ll change CodeTriage’s db/schema.rb so it no longer enables the pg_stat_statments extension (nor the plpgsql extension).

--- a/db/schema.rb
+++ b/db/schema.rb
@@ -12,9 +12,6 @@
 
 ActiveRecord::Schema.define(version: 2020_11_15_123025) do
 
-  # These are extensions that must be enabled in order to support this database
-  enable_extension "pg_stat_statements"
-  enable_extension "plpgsql"
 
   create_table "data_dumps", id: :serial, force: :cascade do |t|
     t.text "data"

Now, let’s try loading the schema and seeds again.

$ bin/rake db:schema:load db:seed
success
....................................................................................................%

OK, that looks a lot better. But can we really get away without having these extensions?

CodeTriage will error wherever it’s expecting the PostgreSQL extensions to be installed and available. We don’t need to worry about this here because this is just a blog post, but it would give me pause if I was changing a production database. If this was a real migration, I’d review the compatibility doc and update the application so it no longer depends on PostgreSQL features.

Now that we’ve done the config changes and set up the database, we should be able to talk to CockroachDB from CodeTriage. 🕺🏾

Connecting to the CockroachDB database from CodeTriage

Let’s spin up a rails console and get some data! Since we ran bin/rake db:seed earlier, our database should have some seed data.

$ bin/rails console
Loading development environment (Rails 6.1.0)
>> User.count
   (67.7ms)  SELECT COUNT(*) FROM "users"
=> 101

OK, we have 101 users. Let’s try fetching the first one.

>> User.first
  User Load (2.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1  [["LIMIT", 1]]
  TRANSACTION (0.9ms)  BEGIN
  User Update (3.9ms)  UPDATE "users" SET "updated_at" = $1, "account_delete_token" = $2 WHERE "users"."id" = $3  [["updated_at", "2021-03-04 01:00:45.821404"], ["account_delete_token", "874464f621a5930c859c5b99b9d1d26705386d61bd34caf00b1288e949dec48dc257459c6fcb297da12ef32dd16419ff64bc7289d94425a94c46fd94ffb89ce9"], ["id", 637885482494296065]]
  TRANSACTION (22.5ms)  COMMIT
=> #<User id: 637885482494296065, email: "", created_at: "2021-03-03 02:13:20.466321000 +0000", updated_at: "2021-03-04 01:00:45.821404000 +0000", zip: nil, phone_number: nil, twitter: nil, github: "schneems", github_access_token: nil, admin: nil, avatar_url: "http://gravatar.com/avatar/default", name: nil, private: false, favorite_languages: nil, daily_issue_limit: 50, skip_issues_with_pr: false, account_delete_token: "874464f621a5930c859c5b99b9d1d26705386d61bd34caf00b...", last_clicked_at: "2021-03-03 02:13:20.466267000 +0000", email_frequency: "daily", email_time_of_day: nil, old_token: nil, raw_streak_count: 0, raw_emails_since_click: 0, last_email_at: nil>

It works! 🙌🏾

You might notice this user has a really big id. CodeTriage specifies a Serial id for the users table so you might expect our first user to have an id of 1. CockroachDB recognizes Serial, but instead of assigning user id’s sequentially from 1 it will assign them based on the transaction timestamp and the node’s id. CockroachDB does this to ensure globally unique id’s are used across nodes in a performant manner. In case you forgot, CockroachDB is not the same as PostgreSQL!

I can also spin up the application by running bin/rails server and watch the server output to see ActiveRecord make some queries to CockroachDB.

$ bin/rails s
=> Booting Puma
=> Rails 6.1.0 application starting in development
…
Started GET "/" for 127.0.0.1 at 2021-03-03 20:25:52 -0500
   (0.7ms)  SHOW crdb_version
   (3.5ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
Processing by PagesController#index as HTML
   (1.8ms)  SELECT COUNT(*) FROM "users"
  ↳ app/controllers/pages_controller.rb:59:in `block in description'
   (1.1ms)  SELECT COUNT(*) FROM "repos"
  ↳ app/controllers/pages_controller.rb:60:in `block in description'
…
  ↳ app/views/pages/_repos_with_pagination.html.slim:1
  Repo Load (2.7ms)  SELECT "repos"."id", "repos"."updated_at", "repos"."issues_count", "repos"."language", "repos"."full_name", "repos"."name", "repos"."description" FROM "repos" WHERE (issues_count > 0) ORDER BY issues_count DESC LIMIT $1 OFFSET $2  [["LIMIT", 50], ["OFFSET", 0]]
…
Completed 200 OK in 661ms (Views: 387.9ms | ActiveRecord: 226.8ms | Allocations: 117408)

The queries work! And the app loads!!!

Use CockroachDB with Rails today

Thanks to the ActiveRecord CockroachDB Adapter, we can use CockroachDB in Rails apps just like any other database. And since CockroachDB talks and acts a lot like PostgreSQL, it can almost be a drop in replacement for PostgreSQL (almost 😉).

Try using CockroachDB in your Rails apps today!

Humblebrag: Marla and I had a lot of fun working with Cockroach Labs to get the ActiveRecord CockroachDB Adapter ready for Rails 5.2 and beyond! Our work with Cockroach Labs is done, but the adapter lives on. Follow Cockroach Labs’ progress on GitHub.