Migrating from Sqlite3 to Postrgresql in Ruby on Rails

Often times while creating web applications, we do not give much thought to the storage method used. Sqlite3 is the default database that is shipped with Ruby on Rails, and functions well enough in the local development capacity. However, once we start thinking of deploying our applications to production, we must consider other options.

The first step in working with postgres is installing it on our system:

gem install pg

Now that we have installed postgres on our system, we will need to create a new “role” for ourselves in postgres. In the following command, we switch into the postgres user and create a new role, passing in as parameters the name of the role, which will end up being your username, and the password:

su - postgres
create role stockapp with createdb login password 'password'

We now run the following command to generate a new app while specifying which database we would like to use:

rails new stockapp --database=postgresql

Obviously name your app whatever you like. The file that we must work with now is config/database.yml. We must change it to look like the following:

I have omitted the password field values as I had them hard-coded. This is not a safe habit and not recommended, and in your own application, the preferred methodology is to store your passwords in environment variables in a separate file, and reference those variables in the password field. If using Github, you will need to include that file that stores your environment variables in your .gitignore file before committing to Github.

For instance, in the config directory in Rails, you can great a file called pg_key.yml, and there we can set an environment variable like so:

PG_KEY: “password”

And in out config/application.rb file, past the following code:

Before our application loads, it will look for the pg_key.yml file and give your rails application access to the environment variables set in the file.

Now run the server:

bin/rails server

And voila! You have successfully created an application using postgresql!

Summary

Postgres is a very robust database that is used frequently in production. There is obviously quite a bit more setup here than with using the standard database that ships with Rails. However, the benefits are reaped in the future. If you are planning on pushing your application to production, do yourself a favor and use postgres for both development and production environments, as this is the preferred approach.