Supporting Async Migrations

[benwah]

I'm using SqlAlchemy with a Fast API application.

This application has a service layer, here's a simplified example:

from my_app.services import delete_user

I may use this service as follows:

@app.delete("/api/users/{id}")
async def delete_user(id: UUID):
    await delete_user(id)

Now my problem is that I would really love to use these services inside my alembic migrations, to perform "data migrations". Unfortunately, alembic doesn't support an async upgrade / downgrade implementation.

bad_user_ids = [
    ...
]

async def upgrade():
    for user_id in bad_user_ids:
        await delete_user(user_id)

I've already configured alembic to use the async driver but this doesn't solve the problem of being able to call asynchronous code from within the upgrade functions.

I've tried a few different approaches to overcome this, for instance, using nest-asyncio and spawning a "nested async I/O event loop" from inside def upgrade(), but I didn't like this approach because:

1. This creates a new connection and transaction instead of sharing my existing transaction.
2. I didn't like depending on nest-asyncio because it does some unholy things.

It seems like for this to work, alembic would need to expose an async API via MigrationContext, perhaps a separate, async version of context.run_migrations.

Questions:

1. Does anyone know of a decent workaround to achieve this?
2. If I implemented support for this feature, would this feature be accepted in alembic?

[CaselIT]

Hi,

1. Does anyone know of a decent workaround to achieve this?

kinda, you can use the semi private sqlalchemy.util.await_only in the context of upgrade.

2. If I implemented support for this feature, would this feature be accepted in alembic?

I'm personally -1 on doing the async def upgrade/downgrade since it will probably add a fail quantity of complexity for a not popular usage.
What I think may make more sense and be easier to implement is having a op.run_async that could be available in alembic when running on an async dialect. Thoughts @zzzeek ?

[benwah]

I just tested the sqlalchemy.util.await_only workaround and it seems to work! I'm okay using this workaround for the time being.

[benwah]

Alright! I accept this workaround, works well... hopefully sqlalchemy.util.await_only is there to stay :)

[CaselIT]

Well the async part of sqlalchemy depends on it so it's unlikely to go anywhere.

I think it may make sense to expose it as an op method though