Alembic with Async SQLAlchemy

Alembic is a lightweight database migration tool for usage with SQLAlchemy. 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.

$ pip install alembic # with pip
$ poetry add alembic  # with poetry

1. Initialize Alembic in the project. Use the -t async flag for asynchronous support.

$ alembic init alembic
$ alembic init -t async alembic # for asynchronous support

1. The init command creates the following project structure.

├── alembic
│   ├── README
│   ├── env.py
│   ├── script.py.mako
│   └── versions
└── alembic.ini

1. 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.

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. 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 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.

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.

alembic revision --autogenerate -m "Initial tables"

The generated migration script can be applied to the database using the upgrade command.

$ 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.

$ 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.

def upgrade():
    op.add_column('user', sa.Column('new_column', sa.String()))

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

Comments

[Michael Tharrington]

Looking like another good one here, Mati! 🙌

[Mati B]

Thanks Michael! 💪🏼