SQLite3 to PostgreSQL in Rails 5

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. If you’re using Postgres.app and Postico, here’s how to set up your database:

    • Make sure your server is running inside Postgres.app, and double click a database inside Postgres.app to open Postico.
    • In Postico, right click and select “New Database”. (If your right click isn’t working, try clicking closer to any existing databases on the first row 3; their UI is finicky.)
    • Create a new database called xxx_dev where xxx is the name of your app.
  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
  1. 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.

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

Congratulations, you’re now running PostgreSQL.

About the author

I'm Mark Thomas Miller, a full stack engineer and designer currently working at ConvertKit. (We're hiring!) People like Arnold Schwarzenegger, Lindsey Stirling, and Tim Ferriss use features I've built to connect with their fans. I'm currently geeking out about Svelte, mechanical keyboards, and minimalist UI design, and replaying Ocarina of Time.