DEV Community

Cover image for Setting up FastAPI, Ormar and Alembic
Amal Shaji
Amal Shaji Subscriber

Posted on • Edited on • Originally published at amalshaji.com

Setting up FastAPI, Ormar and Alembic

#python #database

If you're reading this, chances are you are already familiar with FastAPI and SQLAlchemy. Still, I'll give a slight introduction to both these libraries.

FastAPI

FastAPI is the 3rd most popular python web framework. The factors like asynchronous views, easy to learn, and fast setup have contributed to its quick adoption.

SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language. - from official docs

It is the most popular ORM for python, mostly seen in use with Flask.

ormar is a mini async ORM for python. It uses sqlalchemy for building queries, databases for asynchronous execution of queries, and pydantic for data validation. You can create an ormar model and generate pydantic models from it.

If you have read my post, Pydantic for FastAPI, you will understand how pydantic is very useful for your FastAPI application.

SQLAlchemy uses alembic for migrations. Since ormar is built on top of sqlalchemy, we can use the same for migrations.

⚠️ The project pydantic and my Twitter handle @pydantic are not associated, just saying.

Setup the project 

$ mkdir fastapi-ormar-alembic && cd $_ $ mkdir .venv $ pipenv install fastapi uvicorn ormar alembic aiosqlite
Enter fullscreen mode Exit fullscreen mode

Setup the database and models

⚠️ Pinned dependencies are available in the Piplock file in the source code repository.

Create a new file, db.py, in the root of the project. This file will contain the database setup and an example table.

import databases import ormar import sqlalchemy database = databases.Database("sqlite:///db.sqlite") metadata = sqlalchemy.MetaData() class BaseMeta(ormar.ModelMeta): database = database metadata = metadata class Users(ormar.Model): class Meta(BaseMeta): tablename = "users" id: int = ormar.Integer(primary_key=True) email: str = ormar.String(max_length=64, unique=True) password: str = ormar.String(max_length=128)
Enter fullscreen mode Exit fullscreen mode

Creating a BaseMeta lets you add the database and metadata variables to all your models.

We created a simple model with three basic fields, now let's set up migrations with alembic.

The migrations 

(fastapi-ormar-alembic) $ alembic init migrations
Enter fullscreen mode Exit fullscreen mode

Your project structure should look like this:

├── Pipfile ├── Pipfile.lock ├── alembic.ini ├── db.py ├── db.sqlite ├── main.py └── migrations
Enter fullscreen mode Exit fullscreen mode

Add the database URL to the alembic.ini file. You're actually modifying a single like:

sqlalchemy.url = sqlite:///db.sqlite
Enter fullscreen mode Exit fullscreen mode

Now, tell the migration environment where our metadata is stored. Add(modify) the following in migrations/env.py:

... from db import BaseMeta ... target_metadata = BaseMeta.metadata
Enter fullscreen mode Exit fullscreen mode

Finally, create the migration script. You can use the --autogenerate option to generate migrations based on the metadata automatically:

(fastapi-ormar-alembic) $ alembic revision --autogenerate -m "Added users table" INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.autogenerate.compare] Detected added table 'users' Generating /home/amalshaji/Workspace/Python/blog-code-repository/fastapi-ormar- alembic/migrations/versions/c07fe5d55962_added_users_table.py ... done
Enter fullscreen mode Exit fullscreen mode

Now, run migrations:

(fastapi-ormar-alembic) $ alembic upgrade head
Enter fullscreen mode Exit fullscreen mode

This produced a new file, migrations/versions/c07fe5d55962_added_users_table.py

File name inferred from the migration output.

"""Added users table Revision ID: c07fe5d55962 Revises: Create Date: 2021-08-14 11:55:46.845709 """ from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision = 'c07fe5d55962' down_revision = None branch_labels = None depends_on = None def upgrade(): # ### commands auto generated by Alembic - please adjust! ###  op.create_table('users', sa.Column('id', sa.Integer(), nullable=False), sa.Column('email', sa.String(length=64), nullable=False), sa.Column('password', sa.String(length=128), nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email') ) # ### end Alembic commands ###  def downgrade(): # ### commands auto generated by Alembic - please adjust! ###  op.drop_table('users') # ### end Alembic commands ###
Enter fullscreen mode Exit fullscreen mode

It worked as expected. Now let's modify our table to add a new field and run migration.

# db.py  class Users(ormar.Model): class Meta(BaseMeta): tablename = "users" id: int = ormar.Integer(primary_key=True) email: str = ormar.String(max_length=64, unique=True) password: str = ormar.String(max_length=128) is_active: bool = ormar.Boolean(default=True) # new
Enter fullscreen mode Exit fullscreen mode

Run the migration:

(fastapi-ormar-alembic) $ alembic revision --autogenerate -m "Added is_active to users table" INFO [alembic.runtime.migration] Context impl SQLiteImpl. INFO [alembic.runtime.migration] Will assume non-transactional DDL. INFO [alembic.autogenerate.compare] Detected added column 'users.is_active' Generating /home/amalshaji/Workspace/Python/blog-code-repository/fastapi-ormar- alembic/migrations/versions/026a9a23ebbe_added_is_active_to_users_table.py ... done (fastapi-ormar-alembic) $ alembic upgrade head
Enter fullscreen mode Exit fullscreen mode

This created a new file, migrations/versions/026a9a23ebbe_added_is_active_to_users_table.py:

"""Added is_active to users table Revision ID: 026a9a23ebbe Revises: c07fe5d55962 Create Date: 2021-08-14 12:20:36.817128 """ from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision = '026a9a23ebbe' down_revision = 'c07fe5d55962' branch_labels = None depends_on = None def upgrade(): # ### commands auto generated by Alembic - please adjust! ###  op.add_column('users', sa.Column('is_active', sa.Boolean(), nullable=True)) # ### end Alembic commands ###  def downgrade(): # ### commands auto generated by Alembic - please adjust! ###  op.drop_column('users', 'is_active') # ### end Alembic commands ###
Enter fullscreen mode Exit fullscreen mode

Lets verify the same by checking the database schema:

$ sqlite3 db.sqlite SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .schema users CREATE TABLE users ( id INTEGER NOT NULL, email VARCHAR(64) NOT NULL, password VARCHAR(128) NOT NULL, is_active BOOLEAN, PRIMARY KEY (id), UNIQUE (email) ); sqlite> .quit
Enter fullscreen mode Exit fullscreen mode

Now that we have seen how to set up ormar + alembic let's see how to initialize our database connection in the fastapi application.

FastAPI application 

from fastapi import FastAPI from db import database app = FastAPI() @app.on_event("startup") async def startup(): if not database.is_connected: await database.connect() @app.on_event("shutdown") async def shutdown(): if database.is_connected: await database.disconnect()
Enter fullscreen mode Exit fullscreen mode

We used the FastAPI's(Starlette's) startup and shutdown events to create/close database connection. Actually, we are creating a connection pool. Whenever a database connection is created, it is added to the pool so that the connection can later use it for another request. Essentially removing the time taken to create a new connection.

Handling multiple ormar tables and migrations

Let's say you have a db.py with the following BaseMeta definition.

... class BaseMeta(ormar.ModelMeta): database = database metadata = metadata ...
Enter fullscreen mode Exit fullscreen mode

Add ormar models in different files.

  1. accounts/models.py 
class Users(ormar.Model): class Meta(BaseMeta): tablename = "users" ...
Enter fullscreen mode Exit fullscreen mode
  1. posts/models.py 
class Posts(ormar.Model): class Meta(BaseMeta): tablename = "posts" ...
Enter fullscreen mode Exit fullscreen mode

Inorder for the automatic migration to work, you need to import the models in your
env.py

... from accounts.models import Users from posts.models import Posts from db import BaseMeta ... target_metadata = BaseMeta.metadata
Enter fullscreen mode Exit fullscreen mode

Source Code: fastapi-ormar-alembic

Top comments (0)