SQLite on Heroku
Last updated November 14, 2022
Table of Contents
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.
Did you get Postgres working locally? This is important. 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:
Replace with this line:
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
Note the adapter name is
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
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