The first thing I did was to dump my Postgres database. Since the basic/free Heroku account only supports databases with at most 10k rows, I truncated the zip code database. There are a total of 42k+ rows, so chomping off this many rows is significant, but I’m mostly doing this just as a demonstration.
Usually, you’d run
pg_dump to export an entire Postgres database to a file. However,
pg_dump is an all-or-nothing command and since the zip code database is too big, I first created a copy of the database with the right number of rows. I also shifted things around so that the truncated table matches the name that DataMapper expects.
CREATE TABLE zipcodes_full AS SELECT * FROM zipcodes; DROP TABLE zipcodes; CREATE TABLE zipcodes AS SELECT * FROM zipcodes_full LIMIT 10000;
Then I ran
pg_dump on the smaller table. Note that
pg_dump is a command-line utility, so I ran this from a bash shell. [Note: make sure you’re inside the Postgres.app’s bin folder so you’re using the right version of Postgres.]
./pg_dump -t zipcodes_truncated mydb > /path/to/dump/file/mydb.sql
To create a Heroku project, I first installed the Heroku toolbelt and then ran:
heroku create zipcode-lookup
To push a Postgres database to Heroku, you need to add a database to the Heroku app. To restore a database from a
*.sql file, the file needs to be HTTP-accessible (I used Dropbox for this).
DATABASE_URL references the application’s current database URL.
heroku addons:add heroku-postgresql:hobby-dev heroku addons:add pgbackups heroku pgbackups:restore DATABASE_URL 'https://dl.dropboxusercontent.com/u//mydb.sql'
Note that if you do not run
heroku addons:add heroku-postgresql:hobby-dev, then you are likely to see errors like
! Your app has no databases.
Now if I ping Heroku, I can verify that all 10k rows were uploaded
$ heroku pg === HEROKU_POSTGRESQL_MAROON_URL (DATABASE_URL) Plan: Hobby-dev Status: Available Connections: 2/20 PG Version: 9.3.3 Created: 2014-10-16 09:57 UTC Data Size: 7.7 MB Tables: 1 Rows: 10000/10000 (In compliance) Fork/Follow: Unsupported Rollback: Unsupported
For the final step, push the Sinatra code to Heroku. Note that before doing this, you should update the line of code that references the database URL
git push heroku master
Now when I go to
http://zipcode-lookup.herokuapp.com/zipcode/10456, you’ll see the appropriate JSON result.
Note, if you run into trouble, you can always look at the logs to see the error messages:
heroku logs --tail