alembic_version Table Not Persisting Revision After Programmatic Migration

[snoopiedoom]

I’m running Alembic migrations programmatically using EnvironmentContext because alembic.commands don't return control to my script (it appears to exit the process). While schema changes (e.g., table creation) apply correctly, the alembic_version table isn’t updated persistently. During the migration, checks confirm the revision is present, but after the script finishes, the table is empty. On subsequent runs, the database is treated as having no revision.

Details:
Database: MariaDB (mariadb+pymysql)
Alembic Version: [1.15.1]
Python Version: [3.13.1]

What Works:
Schema changes are applied successfully.

During the migration, both a direct SQL query and MigrationContext.get_current_revision() show the revision in alembic_version.

What Doesn’t Work:
After the script completes, the alembic_version table is empty when queried manually.

On the next run, MigrationContext.get_current_revision() returns None, indicating the revision wasn’t persisted.

Key Observations:
Logs show the revision in alembic_version during the transaction:

2025-03-31 12:56:07 - alembic_version after migration: [('6230a50fc074',)]

After the script ends, querying the table shows it’s empty:

MariaDB [your_db]> SELECT * FROM alembic_version;
Empty set (0.000 sec)

Alembic logs "Will assume non-transactional DDL," but alembic_version updates are DML (INSERT), which should be transactional.

Code Snippet:
Here’s the core of my upgrade function:

def alembic_upgrade():
    global prod_url, config
    log.info("Attempting database upgrade")
    engine = create_engine(prod_url)
    try:
        with engine.connect() as conn:
            script = ScriptDirectory.from_config(config)
            mc = MigrationContext.configure(conn)
            head_rev = script.get_current_head()
            current_rev = mc.get_current_revision()
            if current_rev == head_rev:
                log.info("Database is up to date, skipping upgrade")
                return

            def do_upgrade(revision, context):
                log.info(f"do_upgrade revision: {revision}")
                log.info(f"do_upgrade head_revision: {head_rev}")
                return script._upgrade_revs("head", revision)

            env = EnvironmentContext(config, script)
            env.configure(
                connection=conn,
                target_metadata=target_metadata,  # Defined elsewhere
                fn=do_upgrade,
                dialect_opts={"paramstyle": "named"}
            )
            with env.begin_transaction():
                env.run_migrations()

            # aiiiiii
            with conn.execute(text("SELECT version_num FROM alembic_version")) as result:
                rows = result.fetchall()
                log.info(f"alembic_version after migration: {rows}")

            # iiiiiia
            after_rev = MigrationContext.configure(conn).get_current_revision()
            log.info(f"Database upgraded from {current_rev} to {after_rev}")

    except Exception as e:
        log.error(f"Error during database upgrade: {e}")
        raise e
    finally:
        engine.dispose()

Logs:

2025-03-31 12:56:07 - Running upgrade  -> 6230a50fc074, empty message
2025-03-31 12:56:07 - alembic_version after migration: [('6230a50fc074',)]
2025-03-31 12:56:07 - Database upgraded from None to 6230a50fc074
2025-03-31 12:56:07 - Database migrated successfully

Questions:
Why is alembic_version empty after the script finishesMrIA completes, despite showing the revision during the migration?

Is there a better way to run migrations programmatically that retains script control post-migration, avoiding EnvironmentContext?

Could this relate to MariaDB transaction handling, given the "non-transactional DDL" assumption?

Additional Notes:
Manually stamping the revision (context.stamp(script, head_rev)) causes a primary key conflict, suggesting it’s present during the migration.

Schema changes persist, but alembic_version updates do not.

Any help or suggestions would be greatly appreciated!

[zzzeek]

yes add a conn.commit() call after all the database work is done , otherwise transaction is not committed here

[snoopiedoom]

thanks man, that was it

[CaselIT]

Is there a better way to run migrations programmatically that retains script control post-migration, avoiding EnvironmentContext?

alembic.revision.upgrade() can be used programmatically. It does not exit after execution. https://alembic.sqlalchemy.org/en/latest/api/commands.html#alembic.command.upgrade

[snoopiedoom]

You're right T.T, the only thing not working is my logging, hence i didnt test further, I just need to change alembic logger handle.

    log.info("Attempting database upgrade")
    
    command.upgrade(config, "head")

    with open(os.path.join(wd, 'banana'), 'w') as f:
        f.write('krneki')
    
    print("wawawawa")
    log.info("Great success")

2025-04-01 09:35:37 - Attempting database upgrade
Running migrations online
wawawawa

[CaselIT]

You likely need to customise your env.py to avoid changing the logging configuration, or to do that only when the logging is not already configured