SQLite3 to PostgreSQL in Rails 5

December 19, 2018

When you create a new Rails 5 project, it defaults to using an SQLite3 database. However, most people prefer to use PostgreSQL in production. In this post, we’ll go over how to switch from using SQLite3 to PostgreSQL. We won’t cover transitioning your data – this will create a fresh new database with no entries.

1. In your Gemfile, change gem 'sqlite3' to gem 'pg'.

2. Run bundle install.

3. Set up PostgreSQL. I prefer to use Postgres.app and Postico. Then, set up your database.

4. Change config/database.yml to this:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

  <<: *default
  database: xxx_dev # change
  host: localhost
  port: 5432

  <<: *default
  database: xxx_prod # change
  username: yourusername # change
  password: <%= ENV['XXX_DATABASE_PASSWORD'] %> # change

# This database is erased and regenerated on testing.
# Don't point it to the same database as dev or prod.
  <<: *default
  database: xxx_test # change

5. Open Postico and click New Favorite. Enter the name of your development database from Steps 3 and 4. You may be able to leave everything else blank depending on how you set up Postgres. Then, click Connect.

6. Migrate your database with rails db:migrate. And then run rails s.

Congratulations, you’re now running PostgreSQL.