Offline mode: how to make Alembic use a single (project_name, version_num) table instead of alembic_version?

[jacksonpradolima]

Summary

I run Alembic in offline mode (as_sql=True) and execute the generated SQL with a custom adapter (targets PostgreSQL and DuckDB). I need Alembic to store and read the current revision from a single table that tracks multiple projects:

CREATE TABLE IF NOT EXISTS project_versions (
  project_name TEXT PRIMARY KEY,
  version_num  TEXT,
  updated_at   TIMESTAMP/TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

I want Alembic to use this table instead of the default alembic_version, so that each run specifies a project_name and Alembic performs DELETE … WHERE project_name = :p + optional INSERT … (project_name, version_num) for that project, and also reads the current revision for that project in offline mode.

What I tried (and why it isn’t enough)

1. on_version_apply mirror
   I used on_version_apply to create/update project_versions. This mirrors the step, but Alembic still writes to alembic_version (e.g., INSERT INTO alembic_version (...)). That means the authoritative store remains alembic_version, which doesn’t support multiple projects and causes collisions.

2. version_table=
   Passing a custom name to env.configure(version_table=...) changes the table name, but it still expects Alembic’s default single-column schema (version_num PK). I need a different schema and DML (keyed by project_name), not just a different table name.

3. version_table_impl
   It looks oriented to dialect authors for DDL differences, not for changing the semantics of how Alembic stores/reads the current revision (e.g., custom SELECT/DELETE/INSERT keyed by project_name). I couldn’t find a documented way to implement “get/set current revision for a given project” here—especially in offline mode.

4. Post-run stamping
   As a workaround, after generating and executing the migration SQL, I run my own SQL to upsert project_versions. This works operationally but does not make Alembic itself use project_versions; Alembic still touches alembic_version during the run.

Desired behavior

In offline mode, for a given project_name (passed via env.configure(...)), I’d like Alembic to:

• Read the current revision for that project from project_versions (or accept a provided starting_rev safely).

• Emit only SQL against project_versions for version bookkeeping, e.g.:

  • DELETE FROM project_versions WHERE project_name = :p;
  • (optional) INSERT INTO project_versions(project_name, version_num) VALUES (:p, :v);

• Never emit DDL/DML for alembic_version during that run.

In other words, I need a single shared table with (project_name, version_num) that is the authoritative version store across multiple projects.

Questions

1. Is there a supported public API to replace Alembic’s version storage with a custom model (like project_versions)—including both read (current revision) and write (stamp) paths—in offline mode?
2. Can version_table_impl be used for this purpose, or is it strictly for DDL shape and not for altering the CRUD semantics of the version store?
3. Is there a way to disable writes to alembic_version entirely for a run, and route all version bookkeeping to a custom implementation?
4. If there isn’t a current API, would you consider an extension point (e.g., a “version store” interface with get_current_revision() / stamp_revision() callbacks that works in offline and online modes)?

Minimal (redacted) sketch

buf = io.StringIO()
env = EnvironmentContext(cfg, script, as_sql=True, output_buffer=buf)

with env:
    env.configure(
        url=None,
        target_metadata=None,
        include_schemas=True,
        dialect_name=("postgresql" if is_pg else "sqlite"),
        literal_binds=True,
        # We can pass starting_rev manually in offline mode:
        starting_rev=current_for_project_or_base,

        # What I’d like to do (conceptually):
        #   - tell Alembic: "use project_versions(project_name, version_num)"
        #   - provide project_name for this run
        #   - have Alembic emit DELETE/INSERT against that table only
        #   - have Alembic read 'current revision' for that project in offline mode
        #
        # There doesn’t seem to be a documented way to do this today.
    )
    with env.begin_transaction():
        env.run_migrations()

sql = buf.getvalue()
# -> Currently includes INSERT/DELETE on 'alembic_version'; I need it to target 'project_versions' keyed by :project_name instead.

Why this matters

• We have multiple independent projects sharing the same DB/schema. A single alembic_version table can’t represent multiple project histories.
• Using separate per-project alembic_version_* tables is a different design (multiple tables). Our requirement is one table with an explicit project_name key.
• Offline mode is critical for our runner, so the solution must work without a SQLAlchemy Engine/Connection.

Thanks for any guidance on the correct, supported way to accomplish this, or on whether a new extension point would be acceptable.

[zzzeek]

In other words, I need a single shared table with (project_name, version_num) that is the authoritative version store across multiple projects.

Questions

1. Is there a **supported** public API to replace Alembic’s version storage with a custom model (like `project_versions`)—including both **read** (current revision) and **write** (stamp) paths—in **offline** mode?

no there's not (although it is python so maybe with patching), but since you are using a static SQL script you can edit the SQL script to do whatever you want

2. Can `version_table_impl` be used for this purpose, or is it strictly for DDL shape and not for altering the **CRUD semantics** of the version store?

you can use version_table_impl to provide for a Table that has other columns that you want. But the INSERT statement that's generated by migration.py is hardcoded in an object called HeadMaintainer, and there's not a hook to override how HeadMaintainer works. You can maybe use execution event hooks to intercept the INSERT and rewrite it but im not sure that would work with --sql mode.

3. Is there a way to **disable** writes to `alembic_version` entirely for a run, and route all version bookkeeping to a custom implementation?

the API for operations is open, so it's in theory possible sure but it would be a lot of work to achieve since you'd need to make your own migration runner that steps through the migration files and runs the commands in the way that you want.

4. If there isn’t a current API, would you consider an extension point (e.g., a “version store” interface with `get_current_revision()` / `stamp_revision()` callbacks that works in **offline** and **online** modes)?

maybe, but the way information is stored in alembic_version is pretty idiosyncratic. it currently stores a list of heads (having just one head revision is the most common, but just a specific case). there are commands for insert version, delete version, update version. but if I made an extension point out of this, it would be like "you tell us the name of the table, and how to write insert/update/delete", it would be much more abstracted than that, where you could store and manipulate these heads in a file or whatever you want.

with env:
env.configure(
url=None,
target_metadata=None,
include_schemas=True,
dialect_name=("postgresql" if is_pg else "sqlite"),
literal_binds=True,
# We can pass starting_rev manually in offline mode:
starting_rev=current_for_project_or_base,

    # What I’d like to do (conceptually):
    #   - tell Alembic: "use project_versions(project_name, version_num)"
    #   - provide project_name for this run
    #   - have Alembic emit DELETE/INSERT against that table only
    #   - have Alembic read 'current revision' for that project in offline mode
    #
    # There doesn’t seem to be a documented way to do this today.
)

right if I'm adding API for "custom version store" it would not assume it's even using SQL, so sure, it's a thing we can add but it's the kind of API taht has to be designed carefully, and would need to be integration tested for at least two cases: 1. using an alternative version table format with custom DML 2. using a plain file. both scenarios would have to assert that there is no alembic_version table created at all as well as that version_table-specific elements like version_table_name, version_table_impl are all specific to the default implementation. From there, it follows that alembic_version itself would be the default implementation so this would be a refactor in migration.py that entirely pulls everything to do with alembic_version, from receiving arguments to creating the table to manipulating rows, into the default "version store" plugin. Consider the hierarchy to look like: VersionStore, then SQLVersionStore(VersionStore), then DefaultVersionStore(SQLVersionStore).

* We have multiple independent projects sharing the same DB/schema. A single `alembic_version` table can’t represent multiple project histories.

the usual way to do this is to have multiple alembic_version tables, one for each project.

* Using separate per-project `alembic_version_*` tables is a different design (multiple tables). Our requirement is **one** table with an explicit `project_name` key.

Sure but what is the technical reason for that.

[ImmortalLotus]

Sure but what is the technical reason for that.

What if you had 4 projects residing on the same schema, how would you know which project owns each migration? I'm facing similar issues on my project and found this discussion

[zzzeek]

you'd have four separate alembic version tables that track those projects individually

you use version_table to set this per project

this is quite simple and you dont have to really change anything in the code or anything like that

[ImmortalLotus]

I'd like to propose this as a major change instead to the default alembic_versions table. In almost all other migration tools, there is a table that follows this structure, so we should have a table like this as well, as it is clearly better for multiple reasons:

• migration-hash
• when the migration was created
• when the migration was applied to the database (timestamp)
• some tools have a project name as well

Currently, there is a date alongside the migration hash, which 1: is horrible for performance in long term projects that may have multiple migrations, and 2: does not cover the need for knowing when the migration was applied vs when it was created. For multiple compliance reasons this is bad, specially when working with fragmented teams:

If there is a production issue because someone applied the wrong migration at a given time, currently alembic makes troubleshooting this very hard.

[zzzeek]

when the migration was created
when the migration was applied to the database (timestamp)
some tools have a project name as well

OK this is all a completely different issue than sharing a single alembic_version table among projects, and we have a comprehensive solution for this already. This is #309, which was resolved by creating the on_version_apply which allows auditing of any kind you'd like, see https://github.com/jpassaro/Audit-Alembic as an example.

None of this is appropriate for the alembic_version table, the alembic_version table stores only current heads, not a whole log. it deletes rows for changesets that are no longer the current head. Having a permanent audit table is a supported use case, however having alembic use it as the source of truth for current heads would be a complicated thing to implement since you'd need mutable columns that refer to "what's the current change", or some other scheme to locate the current heads.

Currently, there is a date alongside the migration hash, which

what is this in reference to? the alembic_version table does not store a date right now

If there is a production issue because someone applied the wrong migration at a given time, currently alembic makes troubleshooting this very hard.

I appreciate the need for troubleshooting, though Alembic applies migrations linearly, using an ordering that's fixed in how the files are organized. so im not sure how someone would apply the "wrong" migration unless people are migrating from multiple versions of the same software at the same time ( a bad idea)

2: does not cover the need for knowing when the migration was applied vs when it was created.
For multiple compliance reasons this is bad, specially when working with fragmented teams:

You can, as discussed above, store all the info you need in an auditing table of your choosing using the on_version_apply hook.

It doesnt matter to me but I dont see the "creation date of the migration" is at all useful for any of that, if I'm diagnosing a production failure and want to see the source of a migration, I would need the git commit hash so that I could view the full change and its context, not just a date. so id recommend you store that instead.

[CaselIT]

In short:

• it's not currently supported
• what's supported is having different migration tables
  • you could transform your project_versions in a view that's the union of the single migration tables

What could be accepted:

• an api that allows the implementation of an external store for migrations. It would completely abstract alembic from the version table. This store would have api like
  • create / remove / exists the store
  • get current migrations
  • add / update / delete migration