Make the move from Sqlite3 to Postgres in Rails 6

If you created a rails project without specifying a database, chances are rails defaulted to a sqlite3 database. While this is a great start for development, it can lead to substantial roadblocks as you encounter the need to deploy to services with ephemeral storage or want to use more advanced database features such as jsonb and array datatypes.

There are plenty of relational database options out there, but the most widely used and, in my opinion, the most versatile option is Postgresql. In this article I'll break down the process of installing postgres, modifying db configs, creating your database, and finally importing your existing database.

Install Postgres

If you are using a mac, the easiest way to get started with postgres is to follow the install instructions at https://postgresapp.com/.

For Debian based linux users, install is even easier. Just run:

sudo apt-get install postgresql

Check to make sure postgres is running:

sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2020-08-27 16:20:11 CDT; 50min ago
    Process: 1845 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 1845 (code=exited, status=0/SUCCESS)

Aug 27 16:20:11 ra systemd[1]: Starting PostgreSQL RDBMS...
Aug 27 16:20:11 ra systemd[1]: Finished PostgreSQL RDBMS.

Since this command returned an Active status, we are good to go!

Update Gemfile

Remove the sqlite3 gem from your project:

bundle remove sqlite3

Now add and install the postgresql gem:

bundle add pg --install

Update Database Config

Replace the current contents of config/database.yml with the following config. Make sure you replace <app_name> with the actual name of your application.

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: <app_name>_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: <app_name>_test

production:
  <<: *default
  database: <app_name>_production
  username: pixel_place
  password: <%= ENV['PIXEL_PLACE_DATABASE_PASSWORD'] %>

Setup The New Database

Now that postgres has been added to your project and configured, a few changes need to be made to your migrations. If you have any columns of the type t.string in your migrations, it is highly recommended that you replace that type with t.text.

The main reason for this stems from the way ActiveRecord casts types between different databases. The type t.string will cast to a VARCHAR(255) in postgresql, limiting any existing string data to only 255 characters. Sqlite3 on the other hand is much more lenient with storing character data so your data may have been saved as text to begin with. To save ourselves any headaches further down the line, defaulting to the t.text type is a safe bet.

# db/migrate/01_create_users.rb
class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.text :first_name
      t.text :last_name
      t.text :username

      t.timestamps
    end
  end
end

You are are now ready to run a few rake commands:

rake db:setup

# or

rake db:create db:migrate db:seed

At this point you should have a working postgresql database. Be sure to run rails s and navigate to http://localhost:3000 to make sure your application is running without error.

Bonus: Import an existing sqlite3 database into postgres

If you have a populated database that needs to be saved, the process of importing data into postgresql is made trivial with a tool called pgloader.

First, install pgloader on your system:

# linux
apt-get install pgloader

# mac
brew install pgloader

Then use pgloader to import your sqlite3 db file:

pgloader ./db/db_name.sqlite3 postgresql:///db_name