Setting up FastAPI, Ormar and Alembic
Tags: python, database
Published: 2021-08-14 07:58:10

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](https://fastapi.tiangolo.com/)

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](https://www.sqlalchemy.org/)

> 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](https://collerek.github.io/ormar/)** is a mini async ORM for python. It uses sqlalchemy for building queries, [databases](https://github.com/encode/databases) for asynchronous execution of queries, and [pydantic](https://pydantic-docs.helpmanual.io/) for data validation. You can create an ormar model and generate pydantic models from it.

If you have read my post, *[Pydantic for FastAPI](https://www.amalshaji.com/blog/pydantic-for-fastapi)*, you will understand how pydantic is very useful for your FastAPI application. 

SQLAlchemy uses [alembic](https://alembic.sqlalchemy.org/) 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

```bash
$ 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, `db.py`, in the root of the project. This file will contain the database setup and an example table. 

```python
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 `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

```bash
(fastapi-ormar-alembic) $ alembic init migrations
```

Your project structure should look like this:

```bash
├── Pipfile
├── Pipfile.lock
├── alembic.ini
├── db.py
├── db.sqlite
├── main.py
└── migrations
```

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

```ini
sqlalchemy.url = sqlite:///db.sqlite
```

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

```python
...
from db import BaseMeta
...


target_metadata = BaseMeta.metadata
```

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

```bash
(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:

```bash
(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. 

```python
"""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 ###
```

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

```python
# 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:

```bash
(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`:

```python
"""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 ###
```

Lets verify the same by checking the database schema:

```bash
$ 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 seen how to set up ormar + alembic let's see how to initialize our database connection in the fastapi application.  

## FastAPI application

```python
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([Starlette's](https://github.com/encode/starlette)) startup and shutdown events to create/close database connection. Actually, we are creating a [connection pool](https://en.wikipedia.org/wiki/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.

```python
...
class BaseMeta(ormar.ModelMeta):
    database = database
    metadata = metadata
...
```

Add ormar models in different files.

1. `accounts/models.py`

```python
class Users(ormar.Model):
  class Meta(BaseMeta):
      tablename = "users"

  ...
```

2. `posts/models.py`

```python
class Posts(ormar.Model):
  class Meta(BaseMeta):
      tablename = "posts"

  ...
```

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

```python
...
from accounts.models import Users
from posts.models import Posts
from db import BaseMeta
...


target_metadata = BaseMeta.metadata
```
Source Code: [fastapi-ormar-alembic](https://github.com/amalshaji/blog/tree/master/fastapi-ormar-alembic)