How to build a complete web app with Rails and CockroachDB

To show how easy it is to use CockroachDB in a real-world project, we’re going to build and deploy a production-ready application with basic CRUD functionality. Together, we’ll build a Ruby on Rails application that simulates a game leaderboard, using Active Record to talk with our CockroachDB Serverless database. Cockroach Labs maintains the ActiveRecord Adapter Gem, a direct extension of the PostgreSQL adapter. This makes it very straightforward to switch from a default option to CockroachDB.

We’ll be using Ruby 3.0 to build a Rails app running Rails version 6.1.4. At the end of the tutorial, we’ll deploy the application to the internet on Heroku, which is running on AWS behind the scenes.

Setting up your environment

Let’s dive into the tutorial. You can follow along step-by-step, but you can also view the complete code in its repository on GitHub.

We begin the process by installing Homebrew. We’ll use Homebrew to install rbenv, which we can use to easily switch between versions of Ruby.

If you don’t already have Ruby installed on your machine, there are a number of different ways to install the language depending on your system and/or preferences. Because Ruby was initially developed around Unix-based systems, developing on Windows machines requires some extra setup. Some articles suggest using RubyInstaller, like this one from GeeksForGeeks and this one from Stackify. Many developers using macOS prefer using rbenv to install Ruby versions and switch between them. rbenv is a standard way to manage different Ruby versions. If you don’t already have a Ruby version manager, and would like to user rbenv, you can Install rbenv by running the following command in your terminal:

brew install rbenv

Next, you must use your Ruby version manager to install the language itself. If you’re using rbenv, you can install Ruby 3.0.0 with the following command. Keep in mind that rbenv installs dependencies like openssl, readline, automake, and libyaml. Ruby will be compiled from source so this may take some time. Get a cup of your favorite beverage. Go for a walk. Meditate. Make peace with your god(s).

rbenv install 3.0.3

After installing rbenv, follow the instructions for setting your path. This may change based on your machine, but it will look like this command:

export PATH="$HOME/.rbenv/bin:$PATH"
eval "$(rbenv init -)"

Then, change your current directory to Ruby 3.0.3:

rbenv local 3.0.3

Finally, install Rails globally by running the following command:

gem install rails -v 6.1.4.4

You now have most of the dependencies and tools needed to create incredible experiences with Ruby on Rails.

Creating the app

Once you’ve installed all the necessary packages, use the rails new command to generate a new Rails project:

 rails \_6.1.4.4\_ new game-leaderboard

Some systems, like Windows, may require installing additional dependencies before running this command, so be sure to follow the linked instructions if you’re working on Windows. Also, some systems may require running this without the underscores surrounding the version number. 

 Next, go to the newly created directory of your app:

 cd game-leaderboard

Running your server after you create a new app is a great way to make sure things are working right from the start. Run the app locally with the following command:

 rails server

Navigate to localhost:3000 in your browser.

Before you run anything else, terminate the local server by entering CTRL+C in your terminal.

Now, let’s add the CockroachDB Active Record Adapter. In the app’s Gemfile, add the gem with the correct version by adding the following line:

gem 'activerecord-cockroachdb-adapter', '~> 6.1.4'

 Before you bundle, you must first install postgres:

 brew install postgresql

Next, run the following command:

bundle install

Now that we have an application with the right dependencies, we’ll create a CockroachDB Serverless account. Then we’ll expand the functionality of the application.

Creating a CockroachDB Serverless account

1. Creating an account for Cockroach Serverless is simple and free. You can create an account by registering with your email or by connecting to a GitHub account.  

2. Once you’re registered, the wizard walks you through creating a cluster. This gives you options to choose things like the cloud provider.

Make sure you note the password provided. We’ll come back to everything else later! Leave this tab of your browser open so you can reference it when we’re connecting the application.

Building out the app

Developers don’t always have the luxury of starting a project from scratch. So for this tutorial, we’re going to build the application with the default database setup and then switch over to CockroachDB.

We’re building a game leaderboard, so we need to have a list of scores along with who has each score, preferably listed by the score in descending order. The game that’s being played doesn’t matter for our application.

Because Rails heavily emphasizes the model–view–controller architecture, we’ll start by framing the problem in terms of the model. We want a model that can contain a username and a score. Traditionally, a player has a score, so we’ll create a user model that has attributes of username and score.

We could create the necessary files and code manually, but Rails has generation tools that can get us close to what we need with very little effort.

In the project’s root directory, run the following command from your command line:

rails generate scaffold user

This will create the following objects:

• Migration files to create a user table.
• A model file for the User object.
• Routes for the User resource.
• A controller for the User object.
• Test files, which we won’t use in this tutorial.

Because we only created the migration file, we still need to run it. 

Run any pending migrations with the following command:

rails db:migrate

This creates the table in your local SQLite 3 database. It also alters the schema in the project to reflect the new table. Creating tables with migrations is well-described in the Rails Documentation.

Your db/schema.rb file should now look similar to this:

ActiveRecord::Schema.define(version: 2022_01_09_212905) do

  create_table "users", force: :cascade do |t|
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

end

You’ll have a different version in the definition, and possibly some comments above, but everything else should be identical. 

Your user model will have a username attribute of string type and a score attribute of integer type. We’ll use a migration to create these tables.

In your terminal, run the following command:

rails generate migration AddUsernameToUser

Unlike in the first migration we just did, Rails didn’t automatically generate the code. Instead, it created a file. You’ll find your migration in the db/migrate folder with a name similar to add_username_to_user.

Before any changes, the migration file only contains an empty change method: 

class AddUsernameToUser < ActiveRecord::Migration\[6.1]
  def change
  end
end

We’ll add a single line to the change method, which adds a column to our users table that is of string type and disallows a null value. Your migration should look like this:

class AddUsernameToUser < ActiveRecord::Migration\[6.1]
  def change
    add_column :users, :username, :string, null: false
  end
end

Next, run the migration once more with the following command:

rails db:migrate

We’ll use a similar process to create the score column, but it’s of integer type and has a default value of 0.

Create the migration by running the following command:

rails generate migration AddScoreToUser

Then, in the newly created migration file, add the necessary add_column call inside the change method:

add_column :users, :score, :integer, default: 0

Finally, run the following command to finish:

rails db:migrate

Now we need to add fields to the user form that has already been generated. This is so the user of the application can create new objects with the correct values and update existing ones.

In app/views/users/_form.html.erb, add the fields for username and score values. Your whole file should look like this:

<%= form_with(model: user) do |form| %>
  <% if user.errors.any? %>
    <div id="error_explanation">
      <h2><%= pluralize(user.errors.count, "error") %> prohibited this user from being saved:</h2>

      <ul>
        <% user.errors.each do |error| %>
          <li><%= error.full_message %></li>
        <% end %>
      </ul>
    </div>
  <% end %> 

  <div class="field">
    <%= form.label :username %>
    <%= form.text_field :username %>
  </div>

  <div class="field">
    <%= form.label :score %>
    <%= form.number_field :score %>
  </div>
 
  <div class="actions">
    <%= form.submit %>
  </div>
<% end %>

This will give your form the proper fields so that users can enter a score and username. After restarting your rails server, you can view the application without any styling at localhost:3000/users/new.

Make sure you allow the newly created attributes to pass through to the controller by replacing the user_params method in app/controllers/users_controller.rb with the following code:

def user_params
      params.require(:user).permit(:username, :score)
end

Cleaning up the front end

Next, let’s clean up the index view.

In app/views/users/index.html.erb, change the entire <tbody> tag to match the following code:

  <tbody>
    <% @users.each do |user| %>
      <tr>
        <th>Username</th>
        <th>Score</th>
        <th>Actions</th>
        <th colspan="3"></th>
      </tr>
      <tr>
        <td><%= user.username %></td>
        <td><%= user.score %></td>
        <td><%= link_to 'Show', user %></td>
        <td><%= link_to 'Edit', edit_user_path(user) %></td>
        <td><%= link_to 'Destroy', user, method: :delete, data: { confirm: 'Are you sure?' } %></td>
      </tr>
    <% end %>
  </tbody>

We’re now able to create, edit, and delete users. We can also view a list of users along with their scores. We’ll refine this by sorting the list by score and in descending order. This is a small change - we edit a single line in app/controllers/users_controller.rb.

The index method should now look like this:

def index
  @users = User.all.order("score DESC")
end

This sorts the users that are returned and used by the index view by the score from highest to lowest.

You can check the index view at its route, localhost:3000/users, to view it. There will be a “New User” link on the page, and you can follow the form to create a few new users to test out the table.

To fully develop this application and make it enjoyable to use, we would add some functionality to change scores quickly and probably some styling. For the sake of this tutorial, however, we’re more interested in how CockroachDB can solve our database needs than how to write stylesheets.

Adding CockroachDB

Out of the box, your Rails application defaults to SQLite 3 for simplicity’s sake. Let’s change that.

First, in the config/database.yml file, change the adapter value under the default header to “CockroachDB.” The default section should now look like this:

default: &default
  adapter: cockroachdb
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

Now, you’ll need to refer to the connection setup in the Creating a CockroachDB Serverless account section above.

First, you need to download the CockroachDB client. You can find the command to download the client in the connection window.

Next, download the CA certificate so your machine can talk to the database securely over SSL.

Finally, run the provided command to connect to the database from your terminal. If the connection is successful, run the exit command to exit the SQL terminal that appears.

Although you’ve downloaded the SSL certificate, you now need to make it available to your application.

Copy it to your project directory with the following command:

cp $HOME/.postgresql/root.crt ./root.crt

Next, replace the contents of your config/database.yml file with the following code:

default: &default
  adapter: cockroachdb
  host: <your-host>
  port: 26257
  user: <your-user>
  password: <your-password>
  database: <your-database-name>
  sslmode: 'verify-full'
  sslrootcert: './root.crt'
  options: "--cluster=<your-cluster-name>"
 
development:
  <<: *default
 
production:
  <<: *default

Make sure to swap out the credentials for those provided in the connection window. We don’t want to store sensitive credentials in a Git repository, so it’s a common best practice to use Rails’ credentials manager to encrypt and store them for us. Then, we’d use an environment variable to reference the information we need, such as the username, password, and cluster name.

We could use the dotenv library to manage our secret API keys, but we’ll use Rails’ recently added and built-in encrypted credentials manager instead. The embedded Ruby that we put in the YAML will pull the secrets out and decrypt them.

First, you need to set the access key in the Rails credentials file.

In your shell, run the following command to unencrypt and open the credentials file in Vim:

EDITOR=vim bin/rails credentials:edit

Let’s add a section for the new credentials we’ll be storing.

Add the following code to the file that has been opened by Vim:

cockroachdb:
  host: <your-host>
  user: <your-user>
  password: <your-password>
  database: <your-database-name>

Save your changes and close Vim.

Now, let’s switch our usage of the credentials in the YAML file to instead use calls to the Rails credentials manager.

Replace the default section in your config/database.yml file with the following code:

default: &default
  adapter: cockroachdb
  host: <%= Rails.application.credentials.dig(:cockroachdb, :host) %>
  port: 26257
  user: <%= Rails.application.credentials.dig(:cockroachdb, :user) %>
  password: <%= Rails.application.credentials.dig(:cockroachdb, :password) %>
  database: <%= Rails.application.credentials.dig(:cockroachdb, :database) %>
  sslmode: 'verify-full'
  sslrootcert: './root.crt'
  options: "--cluster=<your-cluster-name>"

Our application now uses Active Record to talk to a CockroachDB Serverless cluster so it can store and retrieve the information our app uses. This setup allows us to share our application data between environments.

To test this, we’ll deploy our application to Heroku. With our application and database both running in the cloud, we’ll have created a fully serverless application.

Deploying the app

To deploy to Heroku, you’ll first need to create an account. Then, download the Heroku CLI so you can interface with Heroku from the command line.

1. Start your deployment by logging in to Heroku. 

2. From your terminal, run the following command:

heroku login

3. Next, create a new application on Heroku from your project directory by running the following command:

heroku create

4. Heroku requires a little bit of preparation before deploying. First, remove the SQLite 3 gem from the Gemfile by deleting the line specifying it and run the following:

bundle install

5. If you’re writing your application on a Mac with Apple Silicon, you may get an error when you push to Heroku because your Gemfile-specified architecture will conflict. You can pre-empt this by running the following line:

bundle lock --add-platform x86_64-linux

6. Next, add the changes to the staging area, commit the changes, and deploy your application to Heroku with the following commands:

git add .
git commit -a
git push heroku main

7. If deployment was successful, our app is almost ready to go. Although it’s live, you still need to configure your Rails credentials manager on Heroku with the master key you have locally.

The master key is a string stored in config/master.key.

8. Run the following command to finish the configuration:

heroku config:set RAILS_MASTER_KEY=<your-master-key-here>

This resets your Heroku server. The app is now nearly up and running.

It’s likely that Heroku has automatically assigned your application a postgres database. This will set the DATABASE_URL environment variable which will override our carefully chosen values in config/database.yml.

9. To confirm that this is the case, sign in to Heroku on the web and select your application. In the Resources tab, check for a “Heroku Postgres” item in the Add-ons list. If you see one, delete it by clicking on the arrows on the far right. 

10. Then, go to the Settings tab in the web application and reveal the “Config Vars” section. If there is still an entry for DATABASE_URL, delete it. 

11. Now, your application can properly ingest values from config/database.yml. Make any small changes as you see fit — perhaps a code comment or a README — and commit the code.

Then, redeploy by running: 

git push heroku main

12. You can view the user index page we created by appending /users to the url provided by Heroku and visiting it in your browser.

Conclusion

By following this tutorial, you’ve built a new Ruby on Rails application that creates, reads, updates, and deletes information from a database. You configured Active Record to use a CockroachDB Serverless cluster rather than SQLite 3, and you deployed the application to Heroku. 

Along the way, you’ve seen how easy it is to drop CockroachDB into a Ruby on Rails application and use it in development and production environments. If you’d like to learn more about how CockroachDB can give your databases a boost, visit their website and sign up for a free CockroachDB Serverless account.