Alembic with Async SQLAlchemy
Tags: python, database, sql, tutorial
Published: 2023-12-12 20:19:45

[Alembic](https://alembic.sqlalchemy.org/en/latest/) is a lightweight database migration tool for usage with [SQLAlchemy](https://www.sqlalchemy.org/). The term _migration_ can be a little misleading, because in this context it doesn't mean to migrate to a different database in the sense of using a different version or a different type of database. In this context, migration refers to changes to the database schema: add a new column to a table, modify the type of an existing column, create a new index, etc..

Alembic "provides for the creation, management, and invocation of change management scripts for a relational database, using SQLAlchemy as the underlying engine". It is designed to handle changes to the database schema over time, allowing for a version-controlled approach to database evolution, so you can keep track of changes and revert back to previous versions if necessary.

## Steps to use it

1. Install Alembic.
```bash
$ pip install alembic # with pip
$ poetry add alembic  # with poetry
```
2. Initialize Alembic in the project. Use the `-t async` flag for [asynchronous support](https://alembic.sqlalchemy.org/en/latest/cookbook.html#using-asyncio-with-alembic).
```bash
$ alembic init alembic
$ alembic init -t async alembic # for asynchronous support
```
3. The init command creates the following project structure.
```bash
├── alembic
│   ├── README
│   ├── env.py
│   ├── script.py.mako
│   └── versions
└── alembic.ini
```
4. `alembic.ini` is a configuration file initialized with default generic values. You need to update the `sqlalchemy.url` value with your database URL. If the value depends on environment variables (so it isn’t a fixed value that can be directly written in the `alembic.ini` file), the `alembic/env.py` can be modified to update the Alembic configuration based on the environment variables.
```python
from app.core.settings import get_settings

# update database URL
settings = get_settings()
config.set_main_option("sqlalchemy.url", settings.get_db_url())
```

## Autogenerate Migrations

One of Alembic's key features is its ability to [auto-generate migration scripts](https://alembic.sqlalchemy.org/en/latest/autogenerate.html). By analyzing the current database state and comparing it with the application's table metadata, Alembic can automatically generate the necessary migration scripts using the `--autogenerate` flag in the `alembic revision` command. Note that [autogenerate does not detect all database changes](https://alembic.sqlalchemy.org/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect) and it is always necessary to manually review (and correct if needed) the candidate migrations that autogenerate produces.

To support autogeneration, the `target_metadata` variable in the `alembic/env.py` file must be set to your tables metadata.
```python
from app.models import *  # noqa: F403
from app.models.database import Base

# all models must have been previously imported
# (from app.models import *)
target_metadata = Base.metadata
```

Now the autogeneration command can be run. Once it is executed, Alembic will generate a new migration script with the necessary changes based on the comparison between the database and the table metadata in the application. The migration script will be created in the alembic/versions directory.
```bash
alembic revision --autogenerate -m "Initial tables"
```

The generated migration script can be applied to the database using the `upgrade` command.
```bash
$ alembic upgrade head
```

## Manually Create Migrations
There are times when you will need to manually create migrations, such as when making complex changes that autogeneration might miss. To accomplish this, you can use the `revision` command.
```bash
$ alembic revision -m "Add new column to User table"
```

Then open the generated migration script in the `alembic/versions` directory and add the necessary changes to the `upgrade` and `downgrade` functions.
```python
def upgrade():
    op.add_column('user', sa.Column('new_column', sa.String()))

def downgrade():
    op.drop_column('user', 'new_column')
```