This tutorial shows you how build a simple Ruby application with CockroachDB and ActiveRecord. CockroachDB provides an ActiveRecord adapter for CockroachDB as a RubyGem.

Tip:

For a more realistic use of ActiveRecord with CockroachDB in a Rails app, 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 PostgreSQL

pg and activerecord are both dependencies of activerecord-cockroachdb-adapter. Both libraries require a PostgreSQL installation.

To install PostgreSQL from source code, follow the instructions on their documentation website.

You can also use a package manager to install PostgreSQL. For example, to install PostgreSQL on macOS, run the following command:

copy
icon/buttons/copy
$ brew install postgresql

To install PostgreSQL on a Debian-based Linux distribution (e.g., Ubuntu), run the following command:

copy
icon/buttons/copy
$ apt-get install postgresql

Step 2. Create the maxroach user and bank database

Start the built-in SQL shell:

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

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

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

Give the maxroach user the necessary permissions:

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

Exit the SQL shell:

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

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

copy
icon/buttons/copy
# Use bundler inline - these would typically go in a Gemfile
require 'bundler/inline'
gemfile do
  source 'https://rubygems.org'
  gem 'pg'
  gem 'activerecord', '5.2.0'

  # CockroachDB ActiveRecord adapter dependency
  gem 'activerecord-cockroachdb-adapter', '5.2.0'
end

require 'pg'
require 'active_record'
require 'activerecord-cockroachdb-adapter'

# Connect to CockroachDB using ActiveRecord.
# In Rails, this configuration would go in config/database.yml as usual.
ActiveRecord::Base.establish_connection(

  # Specify the CockroachDB ActiveRecord adapter
  adapter:     'cockroachdb',
  username:    'maxroach',
  database:    'bank',
  host:        'localhost',
  port:        26257,
  sslmode:     'require',

  # These are the certificate files created in the previous step
  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 :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 programmatically.
# 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 "account: #{acct.id} balance: #{acct.balance}"
end

Then run the code:

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

The output should be:

-- create_table(:accounts, {:force=>true, :id=>:integer})
   -> 0.0883s
account: 1 balance: 1000
account: 2 balance: 250

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

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

Then, issue the following statement:

copy
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:

copy
icon/buttons/copy
$ cockroach sql --insecure

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

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

Give the maxroach user the necessary permissions:

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

Exit the SQL shell:

copy
icon/buttons/copy
> \q

Step 3. Run the Ruby code

The following code uses ActiveRecord to map Ruby-specific objects to database tables. 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.

copy
icon/buttons/copy
# Use bundler inline - these would typically go in a Gemfile
require 'bundler/inline'
gemfile do
  source 'https://rubygems.org'
  gem 'pg'
  gem 'activerecord', '5.2.0'

  # CockroachDB ActiveRecord adapter dependency
  gem 'activerecord-cockroachdb-adapter', '5.2.0'
end

require 'pg'
require 'active_record'
require 'activerecord-cockroachdb-adapter'

# Connect to CockroachDB using ActiveRecord.
# In Rails, this configuration would go in config/database.yml as usual.
ActiveRecord::Base.establish_connection(

  # Specify the CockroachDB ActiveRecord adapter
  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 :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 programmatically.
# 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 "account: #{acct.id} balance: #{acct.balance}"
end

Then run the code (no need to run bundler first):

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

The output should be:

-- create_table(:accounts, {:force=>true, :id=>:integer})
   -> 0.0883s
account: 1 balance: 1000
account: 2 balance: 250

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

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

Then, issue the following statement:

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

What's next?

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

You might also be interested in the following pages:



Yes No