Last updated November 14, 2022

Ruby on Rails and some other web based frameworks ship with support for a small database called sqlite3 by default. SQLite is ideal for users getting started since it can be run in memory and backed by small files on disk that are easily created and moved around. While easy to use, SQLite is not intended as a production grade database. Instead Heroku provides production grade PostgreSQL databases as a service.

Why is SQLite a bad fit for running on Heroku?

Disk backed storage

SQLite runs in memory, and backs up its data store in files on disk. While this strategy works well for development, Heroku’s Cedar stack has an ephemeral filesystem. You can write to it, and you can read from it, but the contents will be cleared periodically. If you were to use SQLite on Heroku, you would lose your entire database at least once every 24 hours.

Even if Heroku’s disks were persistent running SQLite would still not be a good fit. Since SQLite does not run as a service, each dyno would run a separate running copy. Each of these copies need their own disk backed store. This would mean that each dyno powering your app would have a different set of data since the disks are not synchronized.

Instead of using SQLite on Heroku you can configure your app to run on Postgres.

Running Rails on Postgres

PostgreSQL database can be used by any language and framework, this section covers how to connect to it through the Ruby on Rails framework. It is important that you use the same database in production as in development, so you will need to install the PostgreSQL database locally.

If you are starting a new Rails app, you can make postgres the default database by running:

$ rails new -d postgresql

This will install the pg gem in your Gemfile and write the correct config/database.yml configuration locally.

If you have a pre-existing Rails app, or you ran the rails new command without the -d postgresql flag you can convert your application manually.

First open your Gemfile and remove this line:

gem 'sqlite3'

Replace with this line:

gem 'pg'

Then run bundle install.

Next you will need to convert your config/database.yml. Open the existing file, which might look something like this:

development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

You will need to change the adapter from

  adapter: sqlite3

to this:

  adapter: postgresql

Note the adapter name is postgresql not postgres or pg. You will also need to change the database: to a custom name. A final version might look something like this:

development:
  adapter: postgresql
  database: my_database_development
  pool: 5
  timeout: 5000
test:
  adapter: postgresql
  database: my_database_test
  pool: 5
  timeout: 5000

production:
  adapter: postgresql
  database: my_database_production
  pool: 5
  timeout: 5000

Once you’ve installed the pg gem and migrated your config/database.yml file you will need to create your database and run any pre existing migrations against it:

$ rake db:create
$ rake db:migrate

Now you need to $ git add . and $ git commit -m postgres then you will be good to go. Now when you push to Heroku using Rails a development grade postgres instance will be provisioned and connected to your app automatically. If you’re not using Rails, you may need to manually add the postgres addon by running

$ heroku addons:create heroku-postgresql

Getting a SQLite error even though it is not in the Gemfile

If you’ve removed the gem 'sqlite3' line from your Gemfile and are still getting errors while deploying to Heroku it is likely that another gem you are using has sqlite3 as a dependency. To help find the source of this dependency look in your Gemfile.lock for sqlite3. Find the gem that has sqlite3 as a dependency and remove it from your Gemfile. Once you’ve done this run bundle install and ensure that sqlite3 no longer exists in your Gemfile.lock.