Skip to main content
Test Double company logo
Services
Services Overview
Holistic software investment consulting
Software Delivery
Accelerate quality software development
Product Management
Launch modern product orgs
Legacy Modernization
Renovate legacy software systems
DevOps
Scale infrastructure smoothly
Upgrade Rails
Update Rails versions seamlessly
Technical Recruitment
Build tech & product teams
Technical Assessments
Uncover root causes & improvements
Case Studies
Solutions
Accelerate Quality Software
Software Delivery, DevOps, & Product Delivery
Maximize Software Investments
Product Performance, Product Scaling, & Technical Assessments
Future-Proof Innovative Software
Legacy Modernization, Product Transformation, Upgrade Rails, Technical Recruitment
About
About
What's a test double?
Approach
Meeting you where you are
Founder's Story
The origin of our mission
Culture
Culture & Careers
Double Agents decoded
Great Causes
Great code for great causes
EDI
Equity, diversity & inclusion
Insights
All Insights
Hot takes and tips for all things software
Leadership
Bold opinions and insights for tech leaders
Developer
Essential coding tutorials and tools
Product Manager
Practical advice for real-world challenges
Say Hello
Test Double logo
Menu
Services
BackGrid of dots icon
Services Overview
Holistic software investment consulting
Software Delivery
Accelerate quality software development
Product Management
Launch modern product orgs
Legacy Modernization
Renovate legacy software systems
Cycle icon
DevOps
Scale infrastructure smoothly
Upgrade Rails
Update Rails versions seamlessly
Technical Recruitment
Build tech & product teams
Technical Assessments
Uncover root causes & improvements
Case Studies
Solutions
Solutions
Accelerate Quality Software
Software Delivery, DevOps, & Product Delivery
Maximize Software Investments
Product Performance, Product Scaling, & Technical Assessments
Future-Proof Innovative Software
Legacy Modernization, Product Transformation, Upgrade Rails, Technical Recruitment
About
About
About
What's a test double?
Approach
Meeting you where you are
Founder's Story
The origin of our mission
Culture
Culture
Culture & Careers
Double Agents decoded
Great Causes
Great code for great causes
EDI
Equity, diversity & inclusion
Insights
Insights
All Insights
Hot takes and tips for all things software
Leadership
Bold opinions and insights for tech leaders
Developer
Essential coding tutorials and tools
Product Manager
Practical advice for real-world challenges
Say hello
Developers
Developers
Developers
Accelerate quality software

Using CockroachDB with Rails

Discover how to set up CockroachDB with your Rails app. Follow our step-by-step guide for seamless integration and enhanced scalability.
Ali Ibrahim
|
March 25, 2021
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

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.  
  3. the CockroachDB server is listenting at 127.0.0.1 (a.k.a. localhost) at port 63117
  4.  
  5. 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!!!

CodeTriage runs locally with CockroachDB

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.

Related Insights

🔗
The looming demise of the 10x developer
🔗
16 things software developers believe, per a Justin Searls survey

Explore our insights

See all insights
Leadership
Leadership
Leadership
Audentes Fortuna Iuvat: being bold amidst uncertainty

What should businesses do in the face of uncertainty? This is not the first time leaders are dealing with strange economic environments, and it won’t be the last.

by
Ed Frank
Developers
Developers
Developers
LLMallard: the low-key AI chat bot you secretly need

The most perfect dev workflow for taking advantage of deeply powerful AI tooling that’s hyper efficient on token usage with minimal API calls—and the perfect pair programming partner.

by
Daniel Huss
Leadership
Leadership
Leadership
Why we coach the system, not just the team

Slow delivery isn’t usually about your people—it’s about your system. Shifting focus to incremental improvements in the system helps change not just processes but behaviors for lasting change.

by
Doc Norton
Letter art spelling out NEAT

Join the conversation

Technology is a means to an end: answers to very human questions. That’s why we created a community for developers and product managers.

Explore the community
Test Double Executive Leadership Team

Learn about our team

Like what we have to say about building great software and great teams?

Get to know us
No items found.
Test Double company logo
Improving the way the world builds software.
What we do
Services OverviewSoftware DeliveryProduct ManagementLegacy ModernizationDevOpsUpgrade RailsTechnical RecruitmentTechnical Assessments
Who WE ARE
About UsCulture & CareersGreat CausesEDIOur TeamContact UsNews & AwardsN.E.A.T.
Resources
Case StudiesAll InsightsLeadership InsightsDeveloper InsightsProduct InsightsPairing & Office Hours
NEWSLETTER
Sign up hear about our latest innovations.
Your email has been added!
Oops! Something went wrong while submitting the form.
Standard Ruby badge
614.349.4279hello@testdouble.com
Privacy Policy
© 2020 Test Double. All Rights Reserved.