Hands-on Tutorials
Setting up Heroku PostgreSQL for your app in Python
So here’s my problem. I have a ton of data, and I need it to be dynamically available to run apps on my dashboard. Sound familiar? More specifically, I run the Philosophy Data Project, which is a website that implements data-driven tools to help people explore the history of philosophy. So I have something like 330,000 sentences of philosophy and I add to it all the time, and I need all that to be dynamically available for people to search easily via the apps on my site.
I can’t just dump a giant spreadsheet into Heroku; that would break the slug size and would be incredibly slow. Luckily, Heroku is very helpful and they will provide you with a free Heroku Postgres server to house your data and have it accessible.
Great, but it turned out that actually setting this up wasn’t as simple as all that, and while there were definitely some people with great tutorials online, none of them really took us from start to finish. So that’s what I’ll do here.
There are three main steps:
1. Set up Heroku Postgres and connect it to your app.
2. Add data to your server.
3. Call the data and run queries in your app.
Let’s go through them one at a time. To start, all you’ll need is an existing Heroku app.
1. SET UP HEROKU POSTGRES
There are two ways to do this. The first is through the Heroku website interface. Log in and go to your app. Then, under the resources tab, search Postgres from the addons menu. Click the option titled ‘Heroku Postgres.’
You’ll be prompted to choose a tier; choose whichever is best for your needs (there is a free one, but it limits you to 10,000 rows).
Great, now click the addon and you’ll be taken to your Postgres dashboard.
Perfect, you’re good to go! Note that all the information regarding your passwords and URLs is under the ‘Settings’ tab. Heroku automatically populates your relevant configuration variables, but if you need it in the future, that’s where you can find it.
If you prefer using the command line, you can follow these steps. First, open a terminal and navigate to your app. Then use the command heroku addons to check what addons you currently have installed.
Now you can add the Postgres database using heroku addons:create heroku-postgresql:hobby-dev. Naturally, if you want a different plan, substitute that in for hobby-dev.
Ok, you’re good to go. Note that either way you do this, you will now have a new DATABASE_URL configuration variable in your Heroku environment; this variable stores - you guessed it - the database url.
2. ADD DATA TO YOUR SERVER
Ok cool, now you have a server. But you don’t have any data. So you’ll need to add it. There are probably about a million ways to do this, but here is a simply way that you can run from a Jupyter notebook using only Python.
First, have your data set up as you’d like it in a pandas dataframe.
Now, use the following code.
#import the relevant sql library from sqlalchemy import create_engine# link to your databaseengine = create_engine(<YOUR DATABASE URL>, echo = False)# attach the data frame (df) to the database with a name of the
# table; the name can be whatever you likedf.to_sql(‘phil_nlp’, con = engine, if_exists='append')# run a quick test
print(engine.execute(“SELECT * FROM phil_nlp”).fetchone())
This code connects to your database based on its url, then just uploads it all.
This code can populate an empty database or append new values to an existing one. To mess with how it inserts values, change the if_exists parameter.
3. USE THE DATA IN YOUR APP
Cool, now you have a database populated, you have built a beautiful home for all your precious little datas. Your data babies, however, will surely die of neglect if you do not query them properly.
But how can you query them from your app? They’re all the way over in that database over there, surely it is impossible?
Not at all, my friend, in fact it is quite easy. I built my app in Python using Dash, but the same principles should apply to any other Python app.
To connect to the database, you will need the psycopg2 library. Establish a connection like this:
import psycopg2DATABASE_URL = os.environ.get(‘DATABASE_URL’)con = psycopg2.connect(DATABASE_URL)cur = con.cursor()
This code reads the database url from your environment variables and uses it to establish a connection to the server.
Note that if you run the app on your local machine without going through Heroku local, the app will not work; the url is saved in your Heroku environment and is not generally available to your local environment unless you specify it yourself.
Great, so let’s put it all together — here’s an example of an app that lets the user search the database for every occurrence of a given word.
And there you have it! Hopefully that was helpful. Please feel free to reach out to me via LinkedIn or via email if you have any questions, and check out the search app repo for PhilosophyData.com for an example of this work in action. Thanks!
