If you're reading this, chances are you’re already familiar with and . FastAPI SQLAlchemy Still, I'll give a little 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.” - Source. It is the most popular ORM for python, primarily seen in use with Flask. is a mini async ORM for python. It uses SQLAlchemy for building queries, for asynchronous execution of queries, and for data validation. You can create an Ormar model and generate Pydantic models from it. Ormar databases Pydantic If you have read my post, , you will understand how Pydantic is very useful for your FastAPI application. Pydantic for FastAPI SQLAlchemy uses for migrations. Since Ormar is built on top of SQLAlchemy, we can use the same for migrations. Alembic Setup the Project mkdir fastapi-ormar-alembic && cd $_
mkdir .venv
pipenv install fastapi uvicorn ormar alembic aiosqlite Setup the Database and Models Pinned dependencies are available in the Piplock file in the source code repository. Create a new file at the root of the project. This file will contain the database setup and an example table. db.py 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) Creating a lets you add the and variables to all your models. BaseMeta database metadata 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 Your project structure should look like this: ├── Pipfile
├── Pipfile.lock
├── alembic.ini
├── db.py
├── db.sqlite
├── main.py
└── migrations Add the database URL to the file. You're modifying a single line: alembic.ini sqlalchemy.url = sqlite:///db.sqlite 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 Finally, create the migration script. You can use the option to generate migrations based on the metadata automatically: --autogenerate (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 Now, run migrations: (fastapi-ormar-alembic) $ alembic upgrade head 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 ### A migration script is easy to understand. Apart from the few metadata on the top, the and functions play a significant role. The upgrade function adds all the changes we have made to the model, and the downgrade function reverts the changes made to the database. upgrade downgrade It worked as expected. Now let's modify our table to add a new field and run migration. Modify the like so: 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 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 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 ### Let’s 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 Now that we have set up Ormar + Alembic let's see how to initialize our database connection in the fastAPI application. Wiring up FastAPI Application Create a in the root of the project: main.py 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() We used the FastAPI's ( ) startup and shutdown events to create/close the database connections. We are creating a . Starlette's connection pool After a database connection is created, it is added to the pool and used again for another request. The process essentially removes the time taken to make a new connection. Handling multiple ormar tables and migrations Let's say you have a with the following definition. db.py BaseMeta class BaseMeta(ormar.ModelMeta):
    database = database
    metadata = metadata And ormar models in different files. accounts/models.py class Users(ormar.Model):
  class Meta(BaseMeta):
      tablename = "users"

  ... 2. posts/models.py class Posts(ormar.Model):
  class Meta(BaseMeta):
      tablename = "posts"

  ... 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 There you have it. Your FastAPI application should be up and running now. First seen here.

2022 - HackerNoon Contributor of the Year - Beginners Guide

Coding Guru of 2022

Nominated for 2022 - HackerNoon Contributor of the Year - Beginners Guide

Nominated for Coding Guru of 2022

Too Long; Didn't Read

ML Practitioners - Ready to Level Up your Skills?

How to set up FastAPI, Ormar, and Alembic

Too Long; Didn't Read

Company Mentioned

@amalshaji

RELATED STORIES