Exporting 10,000+ SQLite Rows to Heroku

TL;DR 10k+ rows in SQLite not support by Heroku, dumped data into PostgreSQL, migrated to Amazon RDS, then link RDS to Heroku.

I’m within my first year of programming, so chances are I’m doing things incorrectly; worst case, I’m proving Cunningham’s Law. I’m posting this experience in case someone else needs the reference, or just for my future self.

I settled on using this database of boardgames — an SQLite DB with 90k+ rows, and built a RESTful API with Node+Express. After getting the app working locally, I tried putting it into production with Heroku.

The fi rst issue I came across is that Heroku doesn’t support SQLite. There are several ways to dump SQLite into PostgreSQL, but I did it with the SQLite and PostgreSQL CLI. Using GUIs like DB Browser for SQLite and pgAdmin will run into other difficulties, such as pgAdmin’s size limit for importing files.

Break up the postgresql_db.sql into chunks to address errors faster, instead of waiting for over 90k+ rows to return lots of errors.

Leave a Comment