Can't get alembic api working

[rohitsodhia]

I'm trying to get alembic to run on my test database in a pytest fixture:

@pytest.fixture(scope="session", autouse=True)
def setup_database():
    database_url = f"postgresql+psycopg://{configs.DATABASE_USER}:{configs.DATABASE_PASSWORD}@{configs.DATABASE_HOST}"
    engine: Engine = create_engine(database_url)
    with engine.connect().execution_options(isolation_level="AUTOCOMMIT") as connection:
        connection.execute(
            text(f"DROP DATABASE IF EXISTS {configs.DATABASE_DATABASE}_test")
        )
        connection.execute(text(f"CREATE DATABASE {configs.DATABASE_DATABASE}_test"))

        alembic_cfg = AlembicConfig()
        alembic_cfg.attributes["connection"] = connection
        alembic_cfg.set_main_option("script_location", "/app/src/alembic")
        alembic_cfg.set_main_option(
            "sqlalchemy.url", f"{database_url}/{configs.DATABASE_DATABASE}_test"
        )
        alembic_command.upgrade(alembic_cfg, "head")

        yield

        connection.execute(text(f"DROP DATABASE {configs.DATABASE_DATABASE}_test"))

But when I run tests, I get the error that the relation I'm testing against doesn't exist, which seems to indicate alembic never ran? Also, I don't love that this will modify my logging settings. I'm not sure if there's a better way to address this problem.

I've moved the alembic code to a separate script, to just test it on it's own, but while the script takes a second or two to run, there's no output, and no changes to my db.

I was originally looking at how to get alembic to run against multiple databases at once (or choose the database) via the cli, but no luck there.

[byigorduvanov]

I think the migrations ran, just in a different database. The fixture passes a connection to the server's default database while pointing sqlalchemy.url at _test:

alembic_cfg.attributes["connection"] = connection  # connected to the default db
alembic_cfg.set_main_option("sqlalchemy.url", f".../{db}_test")

What happens next depends on your env.py. I reproduced both cases on PostgreSQL 16 with Alembic 1.20.1:

env.py	Tables created in _test	Tables created in default db
Uses config.attributes["connection"] (cookbook recipe)	No	Yes
Stock generic template, which ignores it	Yes	No

So look for alembic_version in your default database. Since you see no changes in _test, I'd expect it there. If it isn't, check whether env.py overrides sqlalchemy.url from your settings, because that would send migrations to your main database. I can't tell that without seeing your env.py.

The fix is to keep the admin connection only for CREATE/DROP, and let Alembic connect to the test database:

admin = create_engine(database_url)
with admin.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
    conn.execute(text(f"DROP DATABASE IF EXISTS {db}_test"))
    conn.execute(text(f"CREATE DATABASE {db}_test"))

test_engine = create_engine(f"{database_url}/{db}_test")
with test_engine.begin() as connection:
    cfg = AlembicConfig()
    cfg.set_main_option("script_location", "/app/src/alembic")
    cfg.attributes["connection"] = connection
    alembic_command.upgrade(cfg, "head")
test_engine.dispose()

yield

with admin.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
    conn.execute(text(f"DROP DATABASE {db}_test"))

This assumes your env.py reads attributes["connection"]. Otherwise, set sqlalchemy.url instead, escaping any % as %%. I haven't run this exact fixture, only the scenarios in the table.

On logging, the stock env.py only calls fileConfig when config.config_file_name is set. AlembicConfig() without a file doesn't set it, so your logging settings stay untouched. That's probably also why your script printed nothing: Alembic isn't logging anywhere in that setup.