Converting a Rails database from SQLite to PostgreSQL

Image for post
Image for post
 !     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

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).

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
g̶e̶m̶ ̶'̶s̶q̶l̶i̶t̶e̶3̶'̶ # remove
gem 'pg' # add
# 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
# 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
$ rake db:create
$ rake db:migrate
# if you have a seed file
$ rake db:seed

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).

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

Current Web Development student at https://flatironschool.com/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store