Deploying a Sqlite3 Database to Heroku for Production Using Postgres
In this post I want to discuss how we deployed to production on Heroku despite having applications running sqlite databases.
The problem
Sqlite is a very easy database to setup, and hence, it has been the starting point for the projects I have worked on at the Flatiron School. These include:
-
: an Arduino-powered flow-meter for the Keg, with a Sinatra web-interface : a Ruby on Rails web application to empower teachers and students to collaborate in the creation of class quizzes : The webpage of an iPhone app I have been working on, that will facilitate discovery and participation on Instagram photo contests.
The natural evolution of development is to want to deploy to a server on the web and link the project to a meaningful domain name such as www.kegkong.com, by using a CNAME.
(There is definitely a place for taking control of your server and using something like Digital Ocean, however, Heroku is my preferred service for quick deployment.)
One problem my teams have run into when trying to deploy to Heroku, is that Heroku no longer supports sqlite as a production database. This is troublesome because in the development of our projects, there is a significant amount of data that we have generated which we would like to maintain. This is also somewhat of an inconvenience because we have not been fully versed in the use of other more production-ready databases.
I will assume for this post that the reader is already comfortable with deploying to Heroku and will focus on the database aspect.
Deploying to Heroku
Once you have an account with Heroku, and have initialized the git repository, it is very easy to deploy the application from your project folder on your console. The commands are:
This will provide you with a temporary url, such as: http://vast-ravine-3721.herokuapp.com.
If your application is running on sqlite3, Heroku will generate errors telling you this isn’t working.
Enabling postgres for production
Now you need to tell your application to use postgres when in production. This requires the use of the ‘pg’ gem, and code as follows in your Gemfile:
You must also setup a config directory and a database.yml file in it to tell postgres the credentials to your database. Mine looks like this:
If you are using Sinatra, you must tell the App to establish a connection between Activerecord and your database, otherwise you will not be able to run your migrations. Add this code to your app.rb or custom environment.rb file:
When I ran this code, Heroku would still be unable to open a link to the database server. To fix this, I had to add a heroku add-on, and manually activate a postgres database.
Do this at the following link: https://addons.heroku.com/heroku-postgresql . You can select the free database plan and select your application. Heroku then gives you a path name for your database. In my case, this was: HEROKU_POSTGRESQL_COBALT_URL .
Now replace the line ‘DATABASE_URL’ with the path name, and your Heroku application should recognize the database.
Now you redeploy your code to Heroku, and this time there should be no errors associated to sqlite3.
Setting up your database on Heroku
Although the application now recognizes a postgres database, it still does not have the proper tables and data required to function properly.
The best solution I have found thus far, is to reinitialize the database using seed data. I will discuss why merely transferring data from your development environment to Heroku has not worked for me at the end of the post.
First, you need to setup your tables, to do this you can still use (a soon to be deprecated Heroku command):
After that you seed your shiny and new postgres database:
And lastly you can test that your database is up and running with the right data via the Heroku console:
If there are no other errors, your application should now work as expected.
Django: Deploying an application on Heroku with sqlite3 as the database
I want to deploy an application with sqlite3 as the database on Heroku. However, it seems to be that Heroku doesn’t support applications with sqlite3 as the database. Is it true? Is there no way to deploy my sqlite3-backed application on Heroku?
PS: I have successfully deployed my application using PythonAnywhere, but would now like to know whether there’s any possible way to deploy it using Heroku.
2 Answers 2
As Heroku’s dynos don’t have a filesystem that persists across deploys, a file-based database like SQLite3 isn’t going to be suitable. It’s a great DB for development/quick prototypes, though.
Heroku do have a Postgres offering however that will suit — with a free tier and a basic $9/month tier that are good for hobby/small projects. The biggest benefit over SQLite is that you get backups that you wouldn’t get otherwise (plus all the other Postgres features).
DamonLC21 / rails-sqlite3-heroku.md
Check if the postgresql service is running via brew :
If not run the rollowing command :
When Deploying an existing application to Heroku first
You must change your Gemfile:
SQLite3 is not supported by Heroku and therefore we must use Postgres to deploy our database.
Next you will need to convert your config/database.yml .
We will be changing the adapter to postgresql.
We will also be naming our database for production,test,and development for postgres to use as well. That change will look something similar to.
Once all that is changed go ahead and save your changes.
If you haven’t already run bundle install to update the Gemfile to use gem ‘pg’ . Now we will create our postgres database and run our migrations.
At this point you are ready to deploy to heroku.
Note that your rails api will not have a home page so you will have to add a controller for your root page and add
in your config/routes.rb to have home page.
Deploying to Heroku
Before we begin make sure to save all changes and push to the github repo you will be saving your Rails app to.
Also if you don’t have Heroku cli installed do so now with the code below
Now that we have the Heroku cli installed. First we will run heroku create to create our heroku app. Make sure you are in the directory that contains your Rails app. You may be prompted to login to heroku at this point.
Once our heroku app is created we can deploy our code to heroku with the following command:
Now we run our migrations but this time via heroku:
And if you have seeds to add :
You have successfully deployed your app to Heroku visit your given link to see your Rails API live .
A few additional random notes I found incase you run into these errors:
Be sure to add the <%= %> erb tags to this line, as we found today:
if your database isn’t seeding or migrating try running
heroku run:detached rake db:migrate
and if you’re starting a new project you can also use
rails new myapp —database=postgresql
and also if you’re getting Cannot run more than 1 free size dynos
run
which lists out any open session.
The following is an example of what could be listed after running “heroku ps”
run.5656 (Free): up 2016/01/12 21:28:41 (
Using Sqlite on Heroku
Heroku does not support sqlite. That doesn’t mean we have to stop using sqlite in development, but it does mean we need to put in some workarounds to support our deployment environment. The rest of this article will use ruby and Sinatra.
Assuming you have a heroku app deployed and you have sqlite already working locally, this only takes a few steps. First we need to add a SQL database to our heroku app. From the project directory, we’ll add the heroku-postgresql addon to our app.
The dev piece of this command tells heroku we want the small, free database. This database supports up to 10,000 rows and has a 99.5% uptime. Best of all: it’s free. Other options have you pay $9/mo for 10,000,000 rows or $50+ for Unlimited usage. I recommend you start small.
Hopefully you got some success statements after adding heroku-postgresql. They should have included some new environment variables, which are links to your new Postgres database. Record these; we’ll use them a little later.
Now we need to set up the back-end to be able to access a Postgres database when necessary. Hopefully you’re using a decent abstraction library in your app that can access any SQL database. For ruby, I find Sequel to be sufficient.
In our Gemfile, we’ve probably already included the sqlite gem for use in our local environment. We can go ahead and move that into a development block, and we need to add the pg gem to either production or the global block.
Heroku sets ENV[‘RACK_ENV’] to “production” for us, which means that the pg gem should get picked up the next time we deploy. Now we need to tell our app which database to use in which situation.
One of the easiest places to make this decision is in Sinatra’s configure block. I keep my local db in an environment variable called LOCAL_DATABASE_URL . This is where you use the environment variable heroku set for you when you set up your Postgres database; mine was called HEROKU_POSTGRESQL_MAROON_URL .
This works because the default environment is “development.” Test locally, and then we can deploy.
Posted by Larry Price Mar 29 th , 2014 heroku, ollert, ruby, sequel, sinatra, sql