Converting a Rails database from SQLite to PostgreSQL

You may have set up a Rails project using the default values, but now want to deploy it to a hosting site such as Heroku. If you follow the instructions on Heroku to push your project to their site you may receive the following error:

 !     Detected sqlite3 gem which is not supported on Heroku.
 !     http://devcenter.heroku.com/articles/how-do-i-use-sqlite3-for-development
 !
 !     Heroku push rejected, failed to compile Ruby/rails app

As the error message states, Heroku does not support the built in database management system (DBMS) for Rails, SQLite.

Why not?

As this is such a common issue for Rails developers the first time they try to deploy their app on Heroku, they have an article on their site explaining the reasoning (https://devcenter.heroku.com/articles/sqlite3).

From what I understand of their explanation, it is due to the way the SQLite database is stored and Heroku’s ephemeral filesystem.

An SQLite database’s data is stored as a single file saved onto disk. Heroku’s ephemeral filesystem allows for temporary writing to disk during an application’s runtime, however every time an update is deployed or Heroku run’s their backend processes those files written to disk by the application are cleared and the database would be dropped.

Looking into how PostgreSQL works, it uses a client/server database model. This means that unlike SQLite, where the app communicates directly with the database file stored on disk, the postgres server process runs as an in-between on all data reads and writes. This process along with Heroku’s PostgreSQL support allows the data to persist through any ephemeral filesystem data cleanup.

How do I use PostgreSQL with my Rails app?

The easiest way to use PostgreSQL with your Rails app is to set it up from the start. When creating a new app add the following to your rails new command:

--database=postgresql

If you already have an app you’ve been working on, there are a couple steps to take to convert your database. To start off you will want to remove the sqlite3 gem from your Gemfile.

g̶e̶m̶ ̶'̶s̶q̶l̶i̶t̶e̶3̶'̶ # remove
gem 'pg' # add

Once the Gemfile is updated, you’ll want to run bundle install to install any necessary gems and update your Gemfile.lock.

Next you will have to update your config/database.yml file. It currently should look something like this:

# SQLite. Versions 3.8.0 and up are supported.
#   gem install sqlite3
#
#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
#
default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
 development:
  <<: *default
  database: db/development.sqlite3
 
# 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: db/test.sqlite3
 production:
  <<: *default
  database: db/production.sqlite3

To update it for PostgreSQL, replace any adapter: sqlite3 with adapter: postgresql and rename any dabatase: db/*region*.sqlite3 with database: *your_app_name*_*region*. Also add encoding: unicode to each database (or just the default when used as above). Using the above example, the update would look as follows:

# PostgreSQL. Versions 9.3 and up are supported.
#
# Install the pg driver:#   gem install pg
# On macOS with Homebrew:#   gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On macOS with MacPorts:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
default: &default
  adapter: postgresql 
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
development:
  <<: *default
  database: *your_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: *your_app_name*_test
production:
  <<: *default
  database: *your_app_name*_production

Now that your database.yml has been updated, you can delete any old .sqlite3 database files from your app folder. Next you’ll want to set up your PostgreSQL databases by running the following:

$ rake db:create
$ rake db:migrate
# if you have a seed file
$ rake db:seed

Your database should now be converted to PostgreSQL! If you now want to upload it to Heroku, follow the directions on their deploy tab under your application.

How to look at your databases

You might have noticed that no files were created in your db folder when you ran db:create or db:migrate. This is due to the different way the data is stored as mentioned above. If you were used to being able to look at your tables using a GUI interface such as DB Browser for SQLite, there is a similar solution for PostgreSQL on MacOS (it should also work on Windows/Linux however I have not tested it).

pgAdmin is a tool that will allow you to view the data in your PostgreSQL database. Download the software for your operating system. For MacOS open the .dmg file and copy the pgAdmin file to your Applications folder. After it is copied, run the file from your Applications folder and it will start up the pgAdmin server and open a window in your web browser. You will first be prompted to create a password, this password can be whatever you want and does not have to match anything with your database set-up. It should bring you to the Dashboard page, where you’ll want to click “Add new Server”.

On the General Tab, give your server a name.

On the Connection tab, type in localhost for your Host name/address. Leave the port as 5432 if you left everything as the default in your database setup (it is not 3000 like your Rails app, this is a different port). Finally where it asks for a Username, fill in the username on your current machine (type $ whoami in terminal if you are unsure). Then click save.

This should add a Servers dropdown on the lefthand side of the screen. Double-click on it to open, then double click on Databases to open up a list of PostgreSQL databases on your machine. Double-click on the server you want to view, then double-click on Schema and Tables to open up a list of the tables in your database.

To view any of the data in any of the tables, right click on the name of the table and select View/Edit Data and pick the amount of rows you would like to see. This should then open up a window on the right that displays the data in the table you selected.

Resources

SQLite on Heroku

Sqlite vs. MySQL vs. PostgreSQL: A Comparison of Relational Databases | Logz.io

pgAdmin