Build a Ruby App with CockroachDB and ActiveRecord

Warning:
This version of CockroachDB is no longer supported. For more details, see the Release Support Policy.

This tutorial shows you how build a simple Ruby application with CockroachDB and the ActiveRecord ORM.

We have tested the ActiveRecord ORM enough to claim beta-level support in CockroachDB v19.2. If you encounter problems, please open an issue with details to help us make progress toward full support.

Tip:

For a more realistic use of ActiveRecord with CockroachDB, see our examples-orms repository.

Before you begin

  1. Install CockroachDB.
  2. Start up a secure or insecure local cluster.
  3. Choose the instructions that correspond to whether your cluster is secure or insecure:

Step 1. Install the ActiveRecord ORM

To install ActiveRecord as well as the pg driver and a CockroachDB Ruby package that accounts for some minor differences between CockroachDB and PostgreSQL, run the following command:

icon/buttons/copy
$ gem install activerecord pg activerecord-cockroachdb-adapter
Note:

The exact command above will vary depending on the desired version of ActiveRecord. Specifically, version 4.2.x of ActiveRecord requires version 0.1.x of the adapter; version 5.1.x of ActiveRecord requires version 0.2.x of the adapter; version 5.2.x of ActiveRecord requires version 5.2.x of the adapter.

Step 2. Create the maxroach user and bank database

Start the built-in SQL shell:

icon/buttons/copy
$ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the maxroach user and bank database:

icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

icon/buttons/copy
> \q

Step 3. Generate a certificate for the maxroach user

Create a certificate and key for the maxroach user by running the following command. The code samples will run as this user.

icon/buttons/copy
$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key

Step 4. Run the Ruby code

The following code uses the ActiveRecord ORM to map Ruby-specific objects to SQL operations. Specifically, Schema.new.change() creates an accounts table based on the Account model (or drops and recreates the table if it already exists), Account.create() inserts rows into the table, and Account.all selects from the table so that balances can be printed.

Copy the code or download it directly.

icon/buttons/copy
require 'active_record'
require 'activerecord-cockroachdb-adapter'
require 'pg'

# Connect to CockroachDB through ActiveRecord.
# In Rails, this configuration would go in config/database.yml as usual.
ActiveRecord::Base.establish_connection(
  adapter:     'cockroachdb',
  username:    'maxroach',
  database:    'bank',
  host:        'localhost',
  port:        26257,
  sslmode:     'require',
  sslrootcert: 'certs/ca.crt',
  sslkey:      'certs/client.maxroach.key',
  sslcert:     'certs/client.maxroach.crt'
)


# Define the Account model.
# In Rails, this would go in app/models/ as usual.
class Account < ActiveRecord::Base
  validates :id, presence: true
  validates :balance, presence: true
end

# Define a migration for the accounts table.
# In Rails, this would go in db/migrate/ as usual.
class Schema < ActiveRecord::Migration[5.0]
  def change
    create_table :accounts, force: true do |t|
      t.integer :balance
    end
  end
end

# Run the schema migration by hand.
# In Rails, this would be done via rake db:migrate as usual.
Schema.new.change()

# Create two accounts, inserting two rows into the accounts table.
Account.create(id: 1, balance: 1000)
Account.create(id: 2, balance: 250)

# Retrieve accounts and print out the balances
Account.all.each do |acct|
  puts "#{acct.id} #{acct.balance}"
end

Then run the code:

icon/buttons/copy
$ ruby activerecord-basic-sample.rb

The output should be:

-- create_table(:accounts, {:force=>true})
   -> 0.0361s
1 1000
2 250

To verify that the table and rows were created successfully, start the built-in SQL client:

icon/buttons/copy
$ cockroach sql --certs-dir=certs --database=bank

Then, issue the following statement:

icon/buttons/copy
> SELECT id, balance FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

Step 2. Create the maxroach user and bank database

Start the built-in SQL shell:

icon/buttons/copy
$ cockroach sql --insecure

In the SQL shell, issue the following statements to create the maxroach user and bank database:

icon/buttons/copy
> CREATE USER IF NOT EXISTS maxroach;
icon/buttons/copy
> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

icon/buttons/copy
> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

icon/buttons/copy
> \q

Step 3. Run the Ruby code

The following code uses the ActiveRecord ORM to map Ruby-specific objects to SQL operations. Specifically, Schema.new.change() creates an accounts table based on the Account model (or drops and recreates the table if it already exists), Account.create() inserts rows into the table, and Account.all selects from the table so that balances can be printed.

Copy the code or download it directly.

icon/buttons/copy
require 'active_record'
require 'activerecord-cockroachdb-adapter'
require 'pg'

# Connect to CockroachDB through ActiveRecord.
# In Rails, this configuration would go in config/database.yml as usual.
ActiveRecord::Base.establish_connection(
  adapter:     'cockroachdb',
  username:    'maxroach',
  database:    'bank',
  host:        'localhost',
  port:        26257,
  sslmode:     'disable'
)

# Define the Account model.
# In Rails, this would go in app/models/ as usual.
class Account < ActiveRecord::Base
  validates :id, presence: true
  validates :balance, presence: true
end

# Define a migration for the accounts table.
# In Rails, this would go in db/migrate/ as usual.
class Schema < ActiveRecord::Migration[5.0]
  def change
    create_table :accounts, force: true do |t|
      t.integer :balance
    end
  end
end

# Run the schema migration by hand.
# In Rails, this would be done via rake db:migrate as usual.
Schema.new.change()

# Create two accounts, inserting two rows into the accounts table.
Account.create(id: 1, balance: 1000)
Account.create(id: 2, balance: 250)

# Retrieve accounts and print out the balances
Account.all.each do |acct|
  puts "#{acct.id} #{acct.balance}"
end

Then run the code:

icon/buttons/copy
$ ruby activerecord-basic-sample.rb

The output should be:

-- create_table(:accounts, {:force=>true})
   -> 0.0361s
1 1000
2 250

To verify that the table and rows were created successfully, start the built-in SQL client:

icon/buttons/copy
$ cockroach sql --insecure --database=bank

Then, issue the following statement:

icon/buttons/copy
> SELECT id, balance FROM accounts;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     250 |
+----+---------+
(2 rows)

What's next?

Read more about using the ActiveRecord ORM, or check out a more realistic implementation of ActiveRecord with CockroachDB in our examples-orms repository.

You might also be interested in the following pages:

YesYes NoNo