SHARE:
Uncategorized

Deploying a Sqlite3 Database to Heroku for Production Using Postgres

Flatiron School / 29 July 2013

The following is a guest post by Jordan Trevino and originally appeared on his blog. Jordan is currently a student at The Flatiron School. You can follow him on Twitter here.

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:

  • Kegkong: an Arduino-powered flow-meter for the Keg, with a Sinatra web-interface
  • InstaQuiz: a Ruby on Rails web application to empower teachers and students to collaborate in the creation of class quizzes

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 likeDigital 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:

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.

Challenges

There were two additional problems that I had to deal with:

  • Transferring data from a sqlite3 database to postgres
  • Precompiling assets when the Rails app on Heroku could not locate the application.js file.

Both of these problems remain largely unresolved, I avoided the first by restarting the database and not transferring data as I wanted to do. And I avoided the second by simply deleting the application.js file. None of these are the appropriate solutions, and so I plan to tackle these problems in my next posts.

Happy coding.

The Woods Previous Post Next Post