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! 🎉
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.
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.
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:firstname.lastname@example.org: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. # email@example.com:63117/defaultdb>
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.
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:firstname.lastname@example.org:63117?sslmode=require
From this information we can see
- we have a user named
- the CockroachDB server is listenting at
localhost) at port
sslmodeis set to
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.
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
First, we’ll change the
adapter value from
--- 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
- we have a user named
- the CockroachDB server is listenting at
localhost) at port
sslmodeis set to
--- 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. 😅
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
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
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
--- 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. 🕺🏾
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!!!
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.