to navigateEnterto select Escto close

    PostgreSQL in depth

    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.

    We had already installed Postgres while deploying the application to Heroku in Deploying application on heroku chapter.

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

    All migrations shown in this chapter are only shown as an example. Do not make these changes in the Granite application.

    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 you 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.1]
    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.

    When you run rails generate model post title:string your migration file will look something like this:

    1class CreatePosts < ActiveRecord::Migration[6.1]
    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 and 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.

    PostgreSQL database directory

    The database configuration file is in config/database.yml. The PostgreSQL database itself however resides in a central location on your local machine. Rails uses the information present in the config/database.yml file to connect to the database. Once the connection is established, your data is stored in the PostgreSQL database outside of your application.

    To find the database directory, open the psql terminal and enter the following command:

    1SHOW data_directory;

    You can also open the application on your Mac then, click on Server settings, and a dialog box will be opened, containing a bunch of information along with the path to the data directory.

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