How to Connect Rails to CockroachDB

Introduction

If you’re a web developer, you may be interested in using CockroachDB in a Ruby app. Fortunately, connecting to CockroachDB from Ruby on Rails is a simple task– CockroachDB is compatible with the PostgreSQL wire protocol, which means you can use existing PostgreSQL drivers to talk to CockroachDB. In order to connect Rails to CockroachDB, all you need is the correct driver that allows Ruby to interact with the PostgreSQL engine. In this article, we’ll provide instructions for installing the Ruby pg driver in CockroachDB.

Prerequisites

Before we can move forward with connecting Rails to CockroachDB, there are a few system requirements to consider. For this task, the prerequisites include:

  • In order to have full access to all PostgreSQL module features, you’ll need to install PostgreSQL 8.0 or later. For more information on PostgreSQL, visit their official website http://www.postgresql.org/ and documentation.

  • You’ll need to make sure that CockroachDB has been properly installed and configured beforehand.

  • You’ll also need to make sure that the development files for the C libpq library are installed in order to compile the Ruby pg module.

Installing Ruby pg Driver

Now that we’ve covered the system requirements, we can begin the installation process. Use the following command in a terminal window:

gem install pg

To verify that the pg gem was properly installed, use:

gem list

You should see a list in the terminal that includes the newly-installed pg gem.

Connecting Ruby to CockroachDB Database

Creating CockroachDB Database and user

Now that we’ve installed the Ruby pg driver, let’s start the CockroachDB SQL Client:

cockroach sql --insecure

Next, use the following statements to create the testuser user and testdatabase database.

CREATE USER IF NOT EXISTS test;

CREATE DATABASE testdatabase;

For the purposes of this tutorial, the user will be given read and write permission to the database. This can be accomplished with the following command:

GRANT ALL ON DATABASE testdatabase TO testuser

Finally, just type \q to exit the interactive SQL shell.

Creating a simple Ruby script to interact with the CockroachDB database

Once you set up your test database and user, you can try out some Ruby code that talks to CockroachDB. The code shown below is a simple Ruby script “sample.rb” that will interact with the CockroachDB database we created in the previous section:

# importing the driver
require 'pg'

# connection config to the "testdatabase" database
dbconn = PG.connect(
user: 'testuser',
dbname: 'testdatabase',
host: 'localhost',
port: 25257,
sslmode: 'disable'
)

# creating a "score" table

dbconn.exec('CREATE TABLE IF NOT EXISTS scores (id INT PRIMARY KEY, score INT)')


# Inserting records into the "scores" table

dbconn.exec('INSERT INTO scores (id, score) VALUES (1, 25), (2, 30), (3, 23)')


# Print the result of the insert command

puts 'available scores:'
dbconn.exec('SELECT id, score FROM scores') do |res|
res.each do |row|
puts row
end
end

# Closing the connection to the database
dbconn.close()

In the code, we did a few different things: We created a table called scores that contains an id column, and an integer score column. After that, we inserted several records into this new table, and finally we selected all rows from the table and printed them.

To run this code, use the command shown below:

ruby sample.rb

Your results should look like the following:

available scores:

{"id"=>"1", "score"=>"25"}
{"id"=>"2", "score"=>"30"}
{"id"=>"3", "score"=>"23"}

We can tell by this output that our table was successfully created, our test records were inserted, and those records were then able to be queried. This confirms that our Ruby script was able to interact successfully with CockroachDB.

Conclusion

Ruby on Rails is one of the most widely-used open-source frameworks for building web applications, so it’s important to have the ability to use CockroachDB in your Ruby apps. If you’re planning to build a Ruby app that uses CockroachDB, it’s easy to get started– with just a few simple steps, you’ll be ready to write and run your Ruby code. Using the instructions provided in this tutorial, you’ll have no trouble installing the Ruby pg driver in CockroachDB.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.