Switch your Rails app from sqlite to PostgreSQL

When you create a new Ruby on Rails application with the rails new command your application will use a sqlite database by default. This is fine for development however if you plan on deploying your application you should be using a more robust database such as PostgreSQL. Many hosting platforms such as Heroku do not support sqlite. In this post I will guide you through the steps to switch your Ruby on Rails application from sqlite to postgreSQL.

(Note: You can read more on why sqlite is a bad fit for Heroku at their devcenter: https://devcenter.heroku.com/articles/sqlite3)

If there is any chance that you will deploy your application it is typically much easier to start off with a PostgresSQL database. You can do this by initializing your rails app with the command ruby new app_name -d postgresql. However, if you have been developing with sqlite there are a few modifications you will need to make to switch to postgreSQL.

Step 1 — Install PostgreSQL

First things first you will need to install PostgreSQL on your local computer, the process differs depending on your operating system. If you are on MacOS you can install with homebrew, or APT on Linux. There is also a graphical installer available for Windows. You can follow the official installation guide by selecting your operating system at https://www.postgresql.org/download/ and following the steps for your preferred method.

Step 2 — Update your Gemfile

Once you have PostgreSQL installed, update your Gemfile within your application by removing the sqlite gem and adding in the postgreSQL gem.

Change:

# Use sqlite3 as the database for Active Record
gem 'sqlite3'

To:

# Use postgresql as the database for Active Record
gem 'pg'

Now run bundle install to install the postgreSQL gem, this will update your Gemfile.lock (which will be used in production).

Step 3 — Update your database configuration file.

Update your database.yml file in order to configure your application with PostgreSQL.

Change:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
​
development:
  <<: *default
  database: db/development.sqlite3
​
test:
  <<: *default
  database: db/test.sqlite3
​
production:
  <<: *default
  database: db/production.sqlite3
​

To:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # http://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
​
development:
  <<: *default
  database: YOUR_APP_NAME_HERE_development
​
test:
  <<: *default
  database: YOUR_APP_NAME_HERE_test
​
production:
  <<: *default
  database: YOUR_APP_NAME_HERE_production
  username: YOUR_APP_NAME_HERE
  password: <%= ENV['YOUR_APP_NAME_HERE_DATABASE_PASSWORD'] %>
​

Replacing all instances of YOUR_APP_NAME_HERE with the the name you gave your rails app when you initialized it with rails new app_name.

Step 4 — Reset your database.

This step will erase your sqlite database, make sure you have backed up any important data. A good seeds.rb file will help you populate your new database.

Run rails db:migrate:reset to delete your sqlite database and re-setup with PostgreSQL. Then run rails s to start the server and visit localhost:3000.Your rails app should now be up and running on a production ready database management system!