Hi there, in this article I will go through the steps to set up a database (db) on your Heroku project, the ‘musts’ to consider to make a connection to a bd manager and the deployment of a dash-app capable to commit data to the database.
This tutorial is tied to a multi-page Dash app that I built and shared with you in this GitHub repository. However, the focus of this article will be on connecting a Dash app to a PostgreSQL Database, so even if you have a one page Dash app, this will show you how to make the app-to-database connection.
Create a db.
Let’s start on your Heroku app dashboard, to create the database, click the squares-grid button, near the top right.
In the dropdown menu, go for the “Elements” option.
At the “Heroku Elements Marketplace”, choose “Heroku Postgres”, this option will enable a free db (limited to 10,000 rows) to your project which can be managed through a db manager. Just to let you know, in this tab you can choose a premium option as large as you need. Finally, click on the “Install Heroku Postgres” button.
Now, type the app’s name in the bar to assign the new product to the desired app.
It is time to push the submit button in order to receive the new db credentials. Then, the website will direct you back to the app dashboard. Click on the “Heroku Postgres” link.
The selection will open up another dashboard, in here, go to “Settings” and then, on the right side click on “View Credentials”.
The db credentials will enable you to make the db connection .
Make a connection to a db manager.
Firstly, you need to get a db manager, I recommend one called dbeaver because it lets you manage several kinds of databases such as Postgres. As previously mentioned, a db manager allows the user to see and manipulate a dataset. Moreover, any test you do while committing data will be visible to you. I will allow you to choose and set up your own db manager.
Let’s move on to your db manager settings. There are two important actions you must do: check your preferred db manager’s documentation on how to import a dataset and how to make a connection (remember that the db provided is on Postgres).
Power a Dash app + db: step by step.
Okay, everything is almost set, however, to deploy it you need to understand a little bit how the files work and connect amongst them. Take a look at this app’s repository. For the first step, your db connection should be already done in your preferred db manager, so next the tables must be created.
- Create a table: the user_preferences.py file has the snippet to create a table on the db. In order to do so, uncomment the “if-name-main” lines, and it’ll let you run the file locally. Run it and after a few seconds, this file will create the table in which the data will be committed. Stop it. First table is done. Repeat it for each table you need to create.
from flask_sqlalchemy import SQLAlchemy
from flask import Flask
from app_framework.db_connection.db_conn import DbConn
app = Flask(__name__)
connections = DbConn().get_connection()
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
class UserPreferences(db.Model):
__tablename__ = 'user_preferences'
id = db.Column(db.Integer, primary_key = True, nullable=False)
user_name = db.Column(db.String(100), nullable=False)
selection = db.Column(db.Integer())
legend = db.Column(db.String(100))
created_at = db.Column(db.DateTime)
def __init__(self, id, user_name, selection, legend, created_at):
self.id = id
self.user_name = user_name
self.selection = selection
self.legend = legend
self.created_at = created_at
def __repr__(self):
return f'<User {self.name!r}>'
# if __name__ == "__main__":
# db.create_all()
# app.run(debug=True)
Do not forget to comment out the “if-name-main” lines once you created the table, or else it will raise errors while deploying it.
Notice that the line tablename sets the table name and the lines below it are the column properties. It is important that you declare those properties (id est, the accepted data type in the column: strings, integers, float, datetime, etc.), otherwise, the table is filled with string-like data by default; there’s a complete guide about it in this reference.
To check up on your new table go back to the db manager, on the right side there is the db pane, you may follow a route quite similar to the next: “your db” > Schemas > public > Tables, once there just refresh it.
- Commit data: for this purpose, there is the write_user.py file. This class collects the inputs from the Dash Plotly objects and commits such inputs in the db. Notice that this, as any other file with connection to the database, has a session.close() command. The reason is that there might be cases in which your app overflows the connection pool size to your db, crashing it while running.
Session.add() stages your data, and session.commit() commits it. You must declare both commands.
from app_framework.commit_db.table.user_preferences import UserPreferences
from sqlalchemy.orm import sessionmaker
from datetime import datetime
import datetime
from sqlalchemy import create_engine
import psycopg2
import os
conn = os.environ["TABLE_CONN"]
class WriteUser:
def __init__(self):
return print("Writing on DB...")
def committing_data(self, user_name_, selection_):
engine = create_engine(conn, echo = True, pool_size=500, max_overflow=-1, pool_pre_ping=True)
Session = sessionmaker(bind=engine)
session = Session()
if selection_ == 1:
legend_ = "Population"
elif selection_ == 2:
legend_ = "GHG emissions"
elif selection_ == 3:
legend_ = "Power production"
elif selection_ == 4:
legend_ = "Increase of agricultural land"
elif selection_ == 5:
legend_ = "Decrease of forest land"
else:
pass
try:
if user_name_ == '':
user_name_= None
else:
pass
commit_preferences = UserPreferences(user_name = user_name_, selection = 1, legend = legend_,
created_at = datetime.datetime.now())
session.add(commit_preferences)
session.commit()
except:
pass
print("Data commited!")
session.close()
return
Another important point is that successful commits only occur if you commit the data type you set as column property, for instance the data type accepted in the table user_preferences for the user_name column is string, if the input was a number (integer), it would not commit. A little bit like Tinder for data types, it only works if that’s a match! Be aware of this simple annotation, contrarily, it can be a true headache.
- Connect app-db & secret variables: there are many alternatives to connect your code to the db, I chose a class to do so; db_conn.py does it for this app.
import psycopg2
import os
class DbConn:
def __init__(self):
print("initializing Connection class")
def get_connection(self):
host = os.environ["HOST"]
db_name = os.environ["DB_NAME"]
user = os.environ["USER"]
password = os.environ["PASSWORD"]
conn = psycopg2.connect("host='{}' port={} dbname='{}' user={} password={}".format(host, "5432", db_name, user, password))
table_conn = os.environ["TABLE_CONN"]
dict_conn = {"conn": conn, "table_conn": table_conn}
return dict_conn
Psycopg2 and os are the libraries that assist in making this connection: the first tries the connection, whereas the second adds an extra layer of security by processing credentials (and any other sensible variable) remotely from the Heroku dashboard. In order to hide sensible variables, go to the Heroku app dashboard, and click on settings.
A “config vars” button will pop up, hereby just add as many secret variables the app requires.
You will be able to call it out as shown in this line:
sensible_variable= os.environ["SENSIBLE_VARIABLE"]
Hence, sensible information will not be exposed and is now protected under Heroku.
-
Data processing: in order to provide a clear structure to the app, the “models” directory encompasses the files that make sql queries and process the data to the needed outcomes to run this app. Check the annexes section below.
-
Pages. These contents have the layouts of each page you in the multi-page app, check this YouTube channel to find complete tutorials on how to set up your Dash objects.
Follow this tutorial to deploy your app to Heroku, and you are all set, make the most of your Dash app with db!
Annexes:
App structure.
The following app structure is just a way to make it work in one of the most recent dash layouts for a multi-page app which is quite helpful to organise and show your data.
What you see are the directory labels, I made it generic so you can see and understand it easily. The “app directory” contains all of the files and folders, likewise “Contents” keeps “db manipulation”, “db connection”, “models”, and on the other hand “pages” holds the dash objects files in.
Below, you can see the actual app “architecture”, the grey area shows the files, whereas the headers depict the directories those are in.
Take a look at this app’s repository to see more details.
