How to Connect Rails to CockroachDB
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.
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 libraryare 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:
You should see a list in the terminal that includes the newly-installed
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
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
# connection config to the "testdatabase" database
dbconn = PG.connect(
# 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|
# Closing the connection to the database
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:
Your results should look like the following:
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.
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