Creating a Database for Your Flask Python Web App | by Chloé Lagrue | Jul, 2022

SQAlchemy, ORM, and data version control with Python

Illustration by Icons 8 from Ouch!

Many apps require some sort of storage. Maybe you’re a business selling your articles online, and you need to keep an inventory. Maybe you’re building a forum and need to track everyone’s posts. I personally am distributing novels, and need to have a list of my books and subscriptions readily available.

In this article, we’ll look at how you can easily set up a PostgreSQL database for your app, using Flask and Heroku.

If you’re looking to get started building your app with Flask, you can read about first steps in my starting boost article. I also provided a code template for a full-stack web app here (dissected in the article linked above).

Overview

In order for this article to make more sense as you read it, let’s go over the steps with a bird’s-eye view.

You’ll first need to figure out a data model—what tables you need to create to store your information. You can do all your data modeling with Python, by creating a class for each table you need.

Then, you can instantiate your database locally with a script I’ve provided in the code template. The SQLAlchemy library will convert your Python classes to SQL statements, and generate a binary file in your repository to store all the data. For this step, two bash commands suffice.

Illustration by Icons 8 from Ouch!

Once you’ve got your local database, you can test data movements, query your tables in your routing functions, and make sure everything is working the way you want it to. Your local database can server as a staging area.

When you’ve got a database that works with your app on your machine, you can run the same script that instantiated it locally from Heroku, and start using the PostgreSQL database it will generate for production.

Depending on the complexity of the objects you need to store, this might take from a few minutes to a couple hours of reflexion. If you’re like me, and you’ve only got to keep track of two things, it’s pretty straight-forward.

ORM with SQLAlchemy

With Flask and SQLAlchemy, you can structure an entire database using only Python. Once you know what models you need to create, you can build them as Python classes, without a single line of SQL.

In my case, I’ve got a list of books, and then I’ve got people signing up to read them. I’ve chosen the simplest way to go about modeling that, with as few tables as possible: one for books, and one for subscriptions. Here is my app/models.py file (with a few lines taken out for brevity):

Each table is represented by a class inheriting from sqlachemy.Model. This constitutes what we call an Object-Relational Mapping (ORM), which handles conversion between SQL tables and Python classes, and allows you to manipulate the rows of a table as instances of a class.

Conventionally, your class will bear a titled, singular name, and be assigned a lowercase, singular¹ table name in the database, using the __tablename__ attribute.

In your class, you can define your table columns as class attributes (as opposed to instance attributes). In other words: no need for a constructor². All columns are sqlalchemy.Column instances, each with a specific type—which will also be an instance of a class from the sqlalchemy library. You can find a list of generic SQLAlchemy types here.

database diagram resulting from the Python models (image by author)

I’ve also added unicity constraints, which run pretty popular and provide a helpful safeguard against duplicate rows and actions users should only perform once. You can create constraints by adding them as arguments in your Column() initiator—like I did with the primary_key argument lines 9 and 23—or with a __table_args__ attribute, if they span over several columns-to-be—like I did on lines 30–32, with a unicity constraint on the email, book_id index).

Translating Foreign Keys: Flask Relationships

It’s easy to see how Python classes can correspond to SQL tables with SQLAlchemy. The syntax of a class is very similar to that of a CREATE TABLE statement. However, there’s one more thing an ORM will do for you.

Let’s zoom in on lines 27–28 from the code example above:

class Subscription(db.Model):
[...]
book_id = db.Column(db.Integer, db.ForeignKey('book.id'))
book = db.relationship('Book', backref='book')

The book_id attribute is our foreign key to the book table, like it would be if we were creating a table with SQL. So what’s the book attribute about?

The relationship function (here in a many-to-one formation) links the instance of the Book class with the matching id attribute to your Subscription object, which means your Book instance will be accessible from all Subscription instances that have its id stored in their book_id attribute. For instance, you’ll be able to run this kind of code:

>>> from app.models import Subscription
>>> s = Subscription.query.get(5)
>>> s
<Subscription 5>
>>> s.book
<Book 3>
>>> s.book.title
'The Picture of Dorian Gray'

Once you’ve defined all the tables in your model, you’ll need to actually create the database. In order to have a staging instance, separate from production, you can create a local database, on your machine.

Illustration by Icons 8 from Ouch!

In the app/config.py file, the line SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///' + os.path.join(basedir, 'app.db') will handle defining the correct address for your database depending on the current environment. If DATABASE_URL is an existing environment variable (production environment), that’s where your app will look for your data. Otherwise, it will load an app/app.db file (locally).

The code I’ve provided will generate both the DATABASE_URL environment variable on your production server and the app/app.db file on your machine automatically when you run the next steps.

Generating a Local Database File

You only need to run the manage.py file with the correct arguments to initiate and then modify your database. For initiation (which you’ll obviously have to run first), you can run (in bash) python manage.py db init or flask db init.

You should then see something along the lines of:

Creating directory /absolute/path/to_your_repo/migrations ...  done
Creating directory /absolute/path/to_your_repo/migrations/versions ... done
Generating /absolute/path/to_your_repo/migrations/script.py.mako ... done
Generating /absolute/path/to_your_repo/migrations/env.py ... done
Generating /absolute/path/to_your_repo/migrations/README ... done
Generating /absolute/path/to_your_repo/migrations/alembic.ini ... done
Please edit configuration/connection/logging settings in '/absolute/path/to_your_repo/migrations/alembic.ini' before proceeding.

And your database is now initiated. The migrations folder you now find at the root of your project contains files necessary for version control. But your data models aren’t there (ie your tables don’t exist) yet. To deploy them, you can run (still bash) python manage.py db migrate or flask db migrate. You will then see (adapted to your own tables):

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'book'
INFO [alembic.autogenerate.compare] Detected added table 'subscription'
Generating /absolute/path/to_your_repo/migrations/versions/8be207c95451_.py ... done

When you run a migration, Alembic (the Flask extension that handles version control for your data models) compares your updated models to your existing ones, and recaps the changes, which are then set for deployment.

Your tables still aren’t ready for use! But we’re getting there. The final move is actual deployment, which you run with python manage.py db upgrade or flask db upgrade. This will run the actual CREATE TABLE statements, and display:

INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 8be207c95451, empty message

If everything went well, you’re now set to populate and query your tables! ✨

Illustration by Oleg Shcherba from Ouch!

Every time you make changes to your models, you’ll need to migrate and upgrade again. ⚠️ Careful, Alembic doesn’t track all changes to your models; so even with version control, you should think hard and long about how you’re structuring your data.

Creating the PostgreSQL Database on Heroku

Some good news: the steps for deploying your models on Heroku are the same as for generating a local binary file—you just need to run your bash commands from the server directly.

You don’t need to explicitly add the add-on for PostgreSQL, as Heroku automatically provides it for apps with certain libraries (such as SQLAlchemy). Heroku will also generate your DATABASE_URL environment variable for you, which you may want to copy to your local environment file if you’re planning on writing and reading from your database from your machine (ie you don’t want a staging environment).

You only need to commit and push your changes to your app/models.py file, while making sure git isn’t tracking your migrations folder no your app/app.db file (add them to your .gitignore).

You can then run:

heroku run bash
>>> python manage.py db init
>>> python manage.py db migrate
>>> python manage.py db upgrade

And you are done.

As I mentioned, your app/config.py file will handle selection of the correct database (local binary file vs PostgreSQL instance) depending on your environment. Make sure never to mix your migrations folders together to avoid conflicts!

There are essentially two ways you can go about running DML³ statements. You can either create a connection to your database and use SQL, or you can use the ORM SQLAlchemy provides. I’d recommend using the ORM, as it spares you the connection set-up.

To run on SQL, you can use a neat little function such as this one:

And then you can run inserts and updates, or query your database with regular SQL (remember to use PostgreSQL syntax).

You can also query your database like this (great if you need a DataFrame for further manipulation):

import pandas as pd
from app import db
df = pd.read_sql(<YOUR-SQL-QUERY>, db.session.bind)

Or run an insert or update like this (particularly helpful for quick fixes):

from app import db
db.session.bind.execute(<YOUR-SQL-STATEMENT>)

We’ll now cover the ORM method. Let’s start by saying you can find a lot of what I’ve gathered in this article in the official documentation.

Selects

With an ORM, your model classes (who inherited from the Flask Model class) have built-in functions for selection.

Illustration by Igor Kapustin from Ouch!

Let’s look at how I would go about retrieving books from my shelves.

If I know the book’s ID (say it’s in the parameters AJAX sent to the route for which I’m now running the function), I can simply run Book.query.get(<book-id>) to retrieve the object. I can also retrieve all books as a list, with Book.query.all(); or I can filter the results with a syntax I can only describe as “Pythonized SQL”.

One recent feature of my web-app is a search bar for the bookshelf. If you search for “Sherlock Holmes” for instance, you should find The Adventures of Sherlock Holmes. I’ve kept it simple for now (no tolerance for spelling errors), with a simple filter over the title of the book.

☝️ When a user sends a form with an input for the search bar, I can use my ORM to retrieve a list by converting the input to lowercase and fuzzy-search for it in my database with the like function—which you’ll recognize from typical SQL syntax.

You can find more SQLAlchemy emulations of SQL functionalities here. When the queries I need get complicated, I tend to just go through SQL directly, as I’m already comfortable with it⁴.

Inserts

Writing data using an ORM is as simple as object creation in Python. Say I want to add Leo Tolstoy’s Anna Karenina to my book table. I just need to create a book object, like so:

On line 8, SQLAlchemy adds the book to my database, but only for the current session. If I open a new connection, the book won’t be there. Line 9 commits the new row, and inserts it “permanently”. The reason I’ve added try / except clauses is your commit won’t work if it breaches any constraints.

When you try to commit a change that violates the rules you’ve put in place, SQLAlchemy won’t tolerate any further DML before you take back your mistake—which is why I do a rollback on line 10.

Updates

Running updates consists in simply retrieving the object you want to update, update the new value with Python, and run a commit for your database.

For example, if I realize I’ve attributed Jane Eyre to Jane Austen and I want to set the record straight, I could go about it this way:

from app import db
from app.models import Book
book = Book.query.filter_by(title = 'Jane Eyre')
book.author_name = 'Charlotte Brontë'
db.session.commit()

Worth Mentioning: for batch inserts and updates, I also turn to SQL. You can create all your objects and commit them together, but I find the SQL connection cleaner.

Heroku and Flask greatly simplify the use of a PostgreSQL database for your web app. There are many obstacles on the path to deploying one for the first time, but this article has hopefully helped you avoid most of them.

Illustration by Natasha Remarchuk from Ouch!

With a database on your machine for staging, you can develop your models and test them as you run your app locally. Once you’re satisfied, you can deploy them to your server with the exact same commands, run from the Heroku CLI.

Always be careful not to mix up migrations between your local and server databases, or you’ll risk having to sacrifice version control to get things running again (which I’ve done several times).

If you run into any issues, have any questions, or found a smarter way to go about it, let me know! I will be going through more full-stack web-app specifics in future articles.

Leave a Comment