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

SQAlchemy, ORM, and information model management with Python

Illustration by Icons 8 from Ouch!

Many apps require some kind of storage. Perhaps you’re a enterprise promoting your articles on-line, and it’s good to preserve a list. Perhaps you’re constructing a discussion board and wish to trace everybody’s posts. I personally am distributing novels, and must have an inventory of my books and subscriptions available.

On this article, we’ll take a look at how one can simply arrange a PostgreSQL database to your app, utilizing Flask and Heroku.

Should you’re seeking to get began constructing your app with Flask, you possibly can examine first steps in my starting boost article. I additionally offered a code template for a full-stack internet app here (dissected within the article linked above).


To ensure that this text to make extra sense as you learn it, let’s go over the steps with a fowl’s-eye view.

You’ll first want to determine an information mannequin—what tables it’s good to create to retailer your data. You are able to do all of your information modeling with Python, by creating a category for every desk you want.

Then, you possibly can instantiate your database domestically with a script I’ve offered within the code template. The SQLAlchemy library will convert your Python lessons to SQL statements, and generate a binary file in your repository to retailer all the info. For this step, two bash instructions suffice.

Illustration by Icons 8 from Ouch!

When you’ve acquired your native database, you possibly can check information actions, question your tables in your routing capabilities, and ensure every little thing is working the way in which you need it to. Your native database can server as a staging space.

If you’ve acquired a database that works together with your app in your machine, you possibly can run the identical script that instantiated it domestically from Heroku, and begin utilizing the PostgreSQL database it’ll generate for manufacturing.

Relying on the complexity of the objects it’s good to retailer, this may take from a couple of minutes to some hours of reflexion. Should you’re like me, and also you’ve solely acquired to maintain monitor of two issues, it’s fairly straight-forward.

ORM with SQLAlchemy

With Flask and SQLAlchemy, you possibly can construction a whole database utilizing solely Python. As soon as you realize what fashions it’s good to create, you possibly can construct them as Python lessons, with no single line of SQL.

In my case, I’ve acquired an inventory of books, after which I’ve acquired individuals signing as much as learn them. I’ve chosen the only approach to go about modeling that, with as few tables as doable: one for books, and one for subscriptions. Right here is my app/ file (with just a few strains taken out for brevity):

Every desk is represented by a category inheriting from sqlachemy.Mannequin. This constitutes what we name an Object-Relational Mapping (ORM), which handles conversion between SQL tables and Python lessons, and permits you to manipulate the rows of a desk as situations of a category.

Conventionally, your class will bear a titled, singular title, and be assigned a lowercase, singular¹ desk title within the database, utilizing the __tablename__ attribute.

In your class, you possibly can outline your desk columns as class attributes (versus occasion attributes). In different phrases: no want for a constructor². All columns are sqlalchemy.Column situations, every with a selected sort—which may also be an occasion of a category from the sqlalchemy library. You could find an inventory of generic SQLAlchemy varieties here.

database diagram ensuing from the Python fashions (picture by writer)

I’ve additionally added unicity constraints, which run fairly in style and supply a useful safeguard towards duplicate rows and actions customers ought to solely carry out as soon as. You possibly can create constraints by including them as arguments in your Column() initiator—like I did with the primary_key argument strains 9 and 23—or with a __table_args__ attribute, in the event that they span over a number of columns-to-be—like I did on strains 30–32, with a unicity constraint on the electronic mail, book_id index).

Translating Overseas Keys: Flask Relationships

It’s straightforward to see how Python lessons can correspond to SQL tables with SQLAlchemy. The syntax of a category is similar to that of a CREATE TABLE assertion. Nonetheless, there’s yet another factor an ORM will do for you.

Let’s zoom in on strains 27–28 from the code instance above:

class Subscription(db.Mannequin):
book_id = db.Column(db.Integer, db.ForeignKey(''))
guide = db.relationship('E-book', backref='guide')

The book_id attribute is our overseas key to the guide desk, like it could be if we had been making a desk with SQL. So what’s the guide attribute about?

The relationship function (right here in a many-to-one formation) hyperlinks the occasion of the E-book class with the matching id attribute to your Subscription object, which suggests your E-book occasion might be accessible from all Subscription situations which have its id saved of their book_id attribute. For example, you’ll be capable to run this type of code:

>>> from app.fashions import Subscription
>>> s = Subscription.question.get(5)
>>> s
<Subscription 5>
<E-book 3>
'The Image of Dorian Grey'

When you’ve outlined all of the tables in your mannequin, you’ll want to truly create the database. With the intention to have a staging occasion, separate from manufacturing, you possibly can create an area database, in your machine.

Illustration by Icons 8 from Ouch!

Within the app/ file, the road SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or 'sqlite:///' + part of(basedir, 'app.db') will deal with defining the right deal with to your database relying on the present surroundings. If DATABASE_URL is an current surroundings variable (manufacturing surroundings), that’s the place your app will search for your information. In any other case, it’ll load an app/app.db file (domestically).

The code I’ve offered will generate each the DATABASE_URL surroundings variable in your manufacturing server and the app/app.db file in your machine mechanically once you run the following steps.

Producing a Native Database File

You solely must run the file with the right arguments to provoke after which modify your database. For initiation (which you’ll clearly must run first), you possibly can run (in bash) python db init or flask db init.

You need to then see one thing alongside the strains of:

Creating listing /absolute/path/to_your_repo/migrations ...  executed
Creating listing /absolute/path/to_your_repo/migrations/variations ... executed
Producing /absolute/path/to_your_repo/migrations/ ... executed
Producing /absolute/path/to_your_repo/migrations/ ... executed
Producing /absolute/path/to_your_repo/migrations/README ... executed
Producing /absolute/path/to_your_repo/migrations/alembic.ini ... executed
Please edit configuration/connection/logging settings in '/absolute/path/to_your_repo/migrations/alembic.ini' earlier than continuing.

And your database is now initiated. The migrations folder you now discover on the root of your undertaking comprises information needed for model management. However your information fashions aren’t there (i.e. your tables don’t exist) but. To deploy them, you possibly can run (nonetheless bash) python db migrate or flask db migrate. You’ll then see (tailored to your personal tables):

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [] Detected added desk 'guide'
INFO [] Detected added desk 'subscription'
Producing /absolute/path/to_your_repo/migrations/variations/ ... executed

If you run a migration, Alembic (the Flask extension that handles model management to your information fashions) compares your up to date fashions to your current ones, and recaps the adjustments, that are then set for deployment.

Your tables nonetheless aren’t prepared to be used! However we’re getting there. The ultimate transfer is precise deployment, which you run with python db improve or flask db improve. This may run the precise CREATE TABLE statements, and show:

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

If every little thing went nicely, you’re now set to populate and question your tables! ✨

Illustration by Oleg Shcherba from Ouch!

Each time you make adjustments to your fashions, you’ll must migrate and improve once more. ⚠️ Cautious, Alembic doesn’t track all changes to your models; so even with model management, you must assume long and hard about the way you’re structuring your information.

Creating the PostgreSQL Database on Heroku

Some excellent news: the steps for deploying your fashions on Heroku are the identical as for producing an area binary file—you simply must run your bash instructions from the server straight.

You don’t must explicitly add the add-on for PostgreSQL, as Heroku mechanically supplies it for apps with sure libraries (corresponding to SQLAlchemy). Heroku may also generate your DATABASE_URL surroundings variable for you, which you will need to copy to your native surroundings file in the event you’re planning on writing and studying out of your database out of your machine (i.e. you don’t need a staging surroundings).

You solely must commit and push your adjustments to your app/ file, whereas ensuring git isn’t monitoring your migrations folder nor your app/app.db file (add them to your .gitignore).

You possibly can then run:

heroku run bash
>>> python db init
>>> python db migrate
>>> python db improve

And you might be executed.

As I discussed, your app/ file will deal with collection of the right database (native binary file vs PostgreSQL occasion) relying in your surroundings. Make sure that by no means to combine your migrations folders collectively to keep away from conflicts!

There are basically two methods you possibly can go about operating DML³ statements. You possibly can both create a connection to your database and use SQL, or you need to use the ORM SQLAlchemy supplies. I’d advocate utilizing the ORM, because it spares you the connection set-up.

To run on SQL, you need to use a neat little perform corresponding to this one:

After which you possibly can run inserts and updates, or question your database with common SQL (keep in mind to make use of PostgreSQL syntax).

It’s also possible to question your database like this (nice in the event you want a DataFrame for additional manipulation):

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

Or run an insert or replace like this (significantly useful for fast fixes):

from app import db

We’ll now cowl the ORM methodology. Let’s begin by saying you’ll find numerous what I’ve gathered on this article in the official documentation.


With an ORM, your mannequin lessons (who inherited from the Flask Mannequin class) have built-in capabilities for choice.

Illustration by Igor Kapustin from Ouch!

Let’s take a look at how I might go about retrieving books from my cabinets.

If I do know the guide’s ID (say it’s within the parameters AJAX despatched to the route for which I’m now operating the perform), I can merely run E-book.question.get(<book-id>) to retrieve the thing. I may retrieve all books as an inventory, with E-book.question.all(); or I can filter the outcomes with a syntax I can solely describe as “Pythonized SQL”.

One latest function of my web-app is a search bar for the bookshelf. Should you seek for “Sherlock Holmes” as an illustration, you must discover The Adventures of Sherlock Holmes. I’ve stored it easy for now (no tolerance for spelling errors), with a easy filter over the title of the guide.

☝️ When a consumer sends a kind with an enter for the search bar, I can use my ORM to retrieve an inventory by changing the enter to lowercase and fuzzy-search for it in my database with the like perform—which you’ll acknowledge from typical SQL syntax.

You could find extra SQLAlchemy emulations of SQL functionalities here. When the queries I would like get sophisticated, I have a tendency to simply undergo SQL straight, as I’m already comfy with it⁴.


Writing information utilizing an ORM is so simple as object creation in Python. Say I need to add Leo Tolstoy’s Anna Karenina to my guide desk. I simply must create a guide object, like so:

On line 8, SQLAlchemy provides the guide to my database, however just for the present session. If I open a brand new connection, the guide gained’t be there. Line 9 commits the brand new row, and inserts it “completely”. The explanation I’ve added strive / besides clauses is your commit gained’t work if it breaches any constraints.

If you attempt to commit a change that violates the principles you’ve put in place, SQLAlchemy gained’t tolerate any additional DML earlier than you’re taking again your mistake—which is why I do a rollback on line 10.


Working updates consists in merely retrieving the thing you need to replace, replace the brand new worth with Python, and run a commit to your database.

For instance, if I understand I’ve attributed Jane Eyre to Jane Austen and I need to set the file straight, I may go about it this manner:

from app import db
from app.fashions import E-book
guide = E-book.question.filter_by(title = 'Jane Eyre')
guide.author_name = 'Charlotte Brontë'

Price Mentioning: for batch inserts and updates, I additionally flip to SQL. You possibly can create all your objects and commit them together, however I discover the SQL connection cleaner.

Heroku and Flask vastly simplify using a PostgreSQL database to your internet app. There are various obstacles on the trail to deploying one for the primary time, however this text has hopefully helped you keep away from most of them.

Illustration by Natasha Remarchuk from Ouch!

With a database in your machine for staging, you possibly can develop your fashions and check them as you run your app domestically. When you’re happy, you possibly can deploy them to your server with the very same instructions, run from the Heroku CLI.

All the time watch out to not combine up migrations between your native and server databases, otherwise you’ll threat having to sacrifice model management to get issues operating once more (which I’ve executed a number of instances).

Should you run into any points, have any questions, or discovered a wiser approach to go about it, let me know! I might be going by extra full-stack web-app specifics in future articles.

More Posts