Introduction Pricing and Billing FAQ Account FAQ Technical FAQ Tutorials Contact Us

Setting Up Ruby on Rails with Postgres

We will learn how to configure Ruby on Rails to work with PostgreSQL as its database backend. This allows you to connect Rails to a Database Labs database (or to any other Postgres database.) Database Labs offers Postgres databases as a service, letting you focus your time on your app rather than on learning the intricacies of Postgres.

Prerequisites

This tutorial assumes you have a basic general knowledge of Ruby on Rails and are comfortable using the command line and a text editor. No specific Postgres experience is required.

Before starting, your computer should have the following software installed:

A text editor
Ruby on Rails
rbenv
A Postgres client, such as pgadmin or the command line client psql.

Getting a Postgres Database

You can provision a database from Database Labs in a few minutes by creating an account, or you can run your own Postgres database.

For brevity, we assume you've got a Database Labs Postgres with a hostname similar to eerie-koala-406.db.databaselabs.io. If you're running your own Postgres, substitute your hostname for that.

Getting a Rails app

We're going to use a prebuilt TODO app in this tutorial, so we can focus on the database configuration. The procedure is the same for any Rails app. If you'd like to clone the example application to follow along, run:

git clone https://github.com/Xe/rails-todo-tdd

Then install the Ruby gems bundle used by the app. (Install rbenv if you don't have the "bundle" command.)

cd rails-todo-tdd
bundle install

We'll also need Ruby's "pg" gem. This library lets Ruby talk to Postgres:

gem install pg

For a real app, you should add the pg gem to the app's Gemfile so that it'll be installed as part of the bundle install.

Setting up the Database

Now let's set up the database. We'll connect to our Postgres server with the psql command line client. You can also use pgadmin if you prefer a graphical client.

Our database will be called "rails". This name is arbitrary; you can call yours whatever you like, as long as you change the name everywhere. We'll also create a database user called "rails", which you can also change to any name you like. Finally, we will allow the "rails" user to have full control of the "rails" database.

Here's how we do it (you type the parts in bold):

% psql -h eerie-koala-406.db.databaselabs.io -U postgres
postgres=# CREATE DATABASE rails;
CREATE DATABASE
postgres=# CREATE USER rails WITH PASSWORD 's3kr1+';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE rails TO rails;
GRANT

Next, edit the file config/database.yml. This tells Rails how to connect to its database. We must set the adapter to "postgresql" and set the hostname, database name, username, and password we created earlier:

production:
  adapter: postgresql
  encoding: unicode
  database: rails
  pool: 5
  username: rails
  password: s3kr1+
  host: eerie-koala-406.db.databaselabs.io

Next, "migrate" the database. This causes Rails to connect to the database and create the database tables used by its models:

% bundle exec bin/rake db:migrate RAILS_ENV=production

Then start up the rails server:

% RAILS_ENV=production bundle exec bin/rails server

You should be able to see the Rails website in your browser at http://127.0.0.1:3000

Use the database

Let's add a model to the database as an example. Run the following in your shell to add a new "user" model with "username" and "password" fields:

% bundle exec bin/rails generate model user username:string{30}:uniq password:string
      invoke  active_record
      create    db/migrate/20141223183742_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml

Then, re-migrate, to create the table used by the model in the database:

% bundle exec bin/rake db:migrate RAILS_ENV=production
== 20141223183742 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0093s
-- add_index(:users, :username, {:unique=>true})
   -> 0.0017s
== 20141223183742 CreateUsers: migrated (0.0115s) =============================

This creates a table in the database called “users” that will be populated with columns for a username and a password.

You can use this model by generating and using a simple controller:

% rails generate controller user
      create  app/controllers/user_controller.rb
      invoke  haml
      create    app/views/user
      invoke  test_unit
      create    test/controllers/user_controller_test.rb
      invoke  helper
      create    app/helpers/user_helper.rb
      invoke    test_unit
      create      test/helpers/user_helper_test.rb
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/user.js.coffee
      invoke    scss
      create      app/assets/stylesheets/user.css.scss

Load http://127.0.0.1:3000/user/new and you will see a form for submitting users to the database.

Now we need to add the code for adding them to the database:

 # edit app/controllers/user_controller.rb
class UserController < ApplicationController
        def new
        end

        def create
                @user = User.new(params.require(:user).permit(:username, :password))

                @user.save
                redirect_to '/'
        end
end

Filling out the form will then insert data into the database!