Learn Ruby on Rails Book

Using PostgreSQL

This chapter combined with the Configuring and connecting to a database chapter will give you insights into how we can set up our Rails application to use PostgreSQL.

You don't need to make the following changes to the granite application since we have already done a lot using SQLite.

Setting up PostgreSQL

Install PostgreSQL in the system

There are two ways to install PostgreSQL in Mac:

  • Using Postgres.app

If you are using a Mac, Postgres.app is an easy way to install PostgreSQL.

The config/database.yml file should look like this.

  • Using Homebrew
1brew install postgresql

After installing PostgreSQL, you can check the version you are running by executing

1postgres -V

Please note that the config/database.yml file to be added after brew install will be defined in the upcoming chapters and it is different from the one defined for the Postgres.app installation.

Update Gemfile to use PostgreSQL

We had already added the pg gem for the production environment, like so:

1gem 'jbuilder', '~> 2.7'
3gem 'pg', :group => [:production]
5gem 'bcrypt', '~> 3.1.13'

You can modify the Gemfile entry for pg and make it available for all environments, like so:

1gem 'jbuilder', '~> 2.7'
3gem 'pg'
5gem 'bcrypt', '~> 3.1.13'

Install the gem:

1bundle install

Using UUID as primary key

UUIDs are a popular alternative to auto-incremental integer primary keys. Using UUIDs as the id in your Rails models instead of incrementing integers helps you avoid collisions. The UUIDs are globally unique, meaning you can know that different models cannot possibly have the same id.

With an incrementing integer id the size of your data can be inferred from the outside i.e. id 5 is the fifth record created. With UUIDs no-one can guess the size of your database tables, which might be information you are keen to keep secret. You can get round this by generating ‘public ids’ or ‘slugs’ for exposed URLs… but then, why not use a built-in tool?

Ruby on Rails has had the ability to use UUIDs as the id for ActiveRecord models since version 5.0.

For PostgreSQL >= 9.4, ActiveRecord will now use pgcrypto’s gen_random_uuid function whereas previously uuid-ossp’s uuid_generate_v4 function was used.

Follow these steps to add UUID primary keys to your Rails 5.1 application.

Add a migration

First we need to enable the PostgreSQL pgcrypto extension in a migration. Lets start by creating a blank migration:

1rails generate migration enable_pgcrypto_extension

Then change the file to enable the extension. It should look something like this:

1class EnablePgcryptoExtension < ActiveRecord::Migration[6.0]
2  def change
3    enable_extension 'pgcrypto'
4  end

Change the primary default type

When you generate models ActiveRecord will use Integer as the type for id by default. From Rails 5 you can change this behaviour by adding the following to your config/application.rb file.

1config.generators do |g|
2  g.orm :active_record, primary_key_type: :uuid

This changes the default column type for primary keys, configuring your migration generator to set id: :uuid for new tables.

Now when you run rails generate model post title:string your migration file should look something like this:

1class CreatePosts < ActiveRecord::Migration[6.0]
2  def change
3    create_table :posts, id: :uuid do |t|
4      t.string :title
6      t.timestamps
7    end
8  end

Using citext

Text fields in Postgres are case-sensitive. We may encounter problems when we use text fields to store E-Mails, as SamSmith@example.com and samsmith@example.com will not be considered the same.

The best way to get around this is to define the email field with citext type, which is available in the citext module.

If you want to change an existing column to be of type citext, here is an example rails migration to achieve this:

1class ChangeEmailToCitext < ActiveRecord::Migration
2  def change
3    enable_extension("citext")
5    change_column :users, :email, :citext
6  end

Also, you can add an index to this column:

1class AddIndexToUsersEmail < ActiveRecord::Migration
2  disable_ddl_transaction!
4  def change
5    add_index :users, :email, unique: true, algorithm: :concurrently
6  end

One more key step required in transitioning to PostgreSQL is to setup the database configuration as mentioned in the chapter named "Configuring and connecting to a database".

We at BigBinary use the devise gem in most of our projects. The devise gem is a very popular authentication gem in the Rails community.

The devise gem automatically lowercases the email field before storing it in the database. Thus we won't have to add citext into our Gemfile if using devise.

There is nothing to commit in this chapter since we do not intend to use PostgreSQL in our granite application across all environments.

    to navigateEnterto select Escto close