Setting up PostgreSQL with Rails Application

PostgreSQL is an advanced, open-source relational database management system which gives it the importance of being extensible. Compared to other RDBMSs, PostgreSQL differs itself with its assist for complex data manipulation. Because of these benefits, and some other reasons, most developers now migrate to Postgres for storing data. In this short tutorial, we will be explaining how the sqlite3database that comes with every rails application can be changed to Postgres:

1. First make Postgres available on your machine by following the instructions stated on their website https://www.postgresql.org/download/. Also download either Postico or pgAdmin, these are intuitive applications that help you interact with your database table and records. The only important reason why some people choose pgAdmin over Postico is because it’s opened to contributions, and it’s being actively contributed to. I personally recommend Postico due to its simplicity, download Postico here https://eggerapps.at/postico/
2. Connect Postico to your database: Open Postico and Click on the `New Favorite` button, then fill in only the `Host` and `Database` fields as shown in the image below. Click on `Connect` to complete the process.

Postico

3. Now that Postgres is setup, It’s time to tell your rails app that you want to use Postgres. Open the Gemfile in the root directory and add the these gems:

gem 'pg'
gem 'rails_12factor', group: :production

The gem rails_12factor is needed for logging on Heroku and it’s only needed if your Rails version is lower than 5.0. This would be useful by the time you finally deploy the application, hence the reason for restricting it to only production environment.

Now you might want to remove the line that has:

gem ‘sqlite3’

Since sqlite is no more needed. Then run

bundle install

to install the new gem(s).

4. Open config/database.yml and replace the content with:

default: &default
  adapter: postgresql
  encoding: unicode
  username: <%= ENV['POSTGRES_USER'] %>
  password: <%= ENV['POSTGRES_PASSWORD'] %>
  pool: 5
  timeout: 5000
  host: <%= ENV['POSTGRES_HOST'] %>
development:
  <<: *default
  database: <%= ENV['POSTGRES_DB'] %>
test:
  <<: *default
  database: <%= ENV['POSTGRES_TEST_DB'] %>
production:
  <<: *default
  database: <%= ENV['POSTGRES_DB'] %>

Here we are specifying postgresql as our database, and at the same time specifying different databases for different environments. It’s a common practice to always keep your secret keys safe, and dotenv-rails gem helps us with this. So go ahead and add:

gem 'dotenv-rails'

to your Gemfile and run

bundle install

Create a .env file in your root directory and put the following:

POSTGRES_USER=''
POSTGRES_PASSWORD=''
POSTGRES_HOST='localhost'
POSTGRES_DB='your_database_name'
POSTGRES_TEST_DB='your_database_name_test'

As you can see, we have specified a different database for the test environment. This is because it’s a good practice to always have a separate database for running tests.

4. Run

rake db:setup

to create your database. This command automatically creates your database, runs migrations if you have any, and seeds the necessary tables if you have any rake tasks. Now open your Postico to view your database, here you should see both your_database_name and your_database_name_test.

If this command gives an error that looks like this:

rake aborted!
Gem::LoadError: Specified 'postgresql' for database adapter, but the gem is not loaded. Add `gem 'pg'` to your Gemfile (and ensure its version is at the minimum required by ActiveRecord).
/app/vendor/bundle/ruby/2.2.0/gems/activerecord-4.2.6/lib/active_record/connection_adapters/connection_specification.rb:177:in `rescue in spec'...

Kindly specify a version number for the pg in your Gemfile, to have something like:

gem 'pg', '~> 0.20'

This issue has been fixed in Rails 5.1.5 as discussed here: https://github.com/rails/rails/issues/31673

Note: Using different databases for development and production is not recommended. Occasionally, Rails behaves differently on different databases. So you are advised to set up and develop on PostgreSQL , and more importantly if you intend to deploy your application to Heroku because Heroku has no support for sqlite.

If you have any suggestion/comment /question as regards this tutorial, kindly post as comment below.