Roman Imankulov

Roman Imankulov

Full-stack Python web developer from Porto

search results (esc to close)
28 Nov 2021

SQLAlchemy and Alembic notes

My notes on SQLAlchemy and Alembic

SQLAlchemy pitfalls

Columns are nullable by default

The default value of SQLAlchemy nullable is False unless it’s a primary key. A foreign key is also nullable by default. If you came from the Django ORM, where the default values for fields are null=False, blank=False, you might accidentally create table definitions with nullable fields, which bites you in the future.

This behavior matches the default SQL, though, where the column would be considerably nullable unless you explicitly set NOT NULL for it.

Include date and time to Alembic migration files

Default migration files have the format <revision>_<slug>.py. A randomly generated revision makes it harder to find recently added migrations. The solution is to add a date and time prefix to the template generator.

Update alembic.ini to define the file template:

file_template = %%(year)d%%(month).2d%%(day).2d_%%(rev)s_%%(slug)s

The command alembic revision -m "Add new column" will generate a file like this: migrations/versions/20211128_2eb46b06af52_add_new_column.py.

Alternatively, you can generate a new revision manually with the --rev-id flag.

Source: StackOverflow

How to create a data migration

Usually, a data migration inserts new records in upgrade() and removes them in downgrade(). The recommended way is not to go too far into the ORM and model things and modify data with op.bulk_insert() or op.execute() instead.

The advantage of the simplified approach is that it still can generate the SQL version (with an --sql option) when necessary.

Note that while writing the script, you should probably repeat the table definition inside it. It’s OK only to define the fields that you need for the migration.

Alembic documentation explicitly warns you against importing a table from your existing code.

Note it’s a recommended practice to at least ensure the definition of a table is self-contained within the migration script, rather than imported from a module that may break compatibility with older migrations.

Example:

from alembic import op
import sqlalchemy as sa

...

account = sa.table("account", sa.column("name", sa.String))


def upgrade():
    op.bulk_insert(
        account,
        [
            {"name": "John Smith"},
            {"name": "Ed Williams"},
            {"name": "Wendy Jones"},
        ],
    )


def downgrade():
    op.execute(account.delete())

References:

Roman Imankulov

Hey, I'm Roman, a full-stack Python web developer.

If you like what you read and want to work with me, drop me a line to roman.imankulov@gmail.com.

About me