Migrate alembic_version table from Public to tenant schema

[Brian Hill]

Is there a way to move the alembic_version table in the Public schema (postgres) to a specific schema (mult-tenant) as part of a migration?

I want alembic to read the inital version from Public.alembic_version and then write the new upgraded version to the specific schema.alembic_version.

The only way I can get it to work is executing raw sql after the migration, and this won't work for future migrations.

with context.begin_transaction():

context.run_migrations()

context.execute(f'alter table Public.alembic_version set schema {DB_SCHEMA}')

Do I have to flatten/rebase my versions after this?

Thanks,

Brian

[Mike Bayer]

On Tue, Feb 18, 2020, at 1:17 PM, Brian Hill wrote:

Is there a way to move the alembic_version table in the Public schema (postgres) to a specific schema (mult-tenant) as part of a migration?

I highly doubt this is possible in the general case without the raw SQL in your env.py,  because as the migration runs, the environment needs to update the table.  if it moves schemas, then it won't be updating the correct table anymore.    You would have to manipulate the internal state of the MigrationContext within the migration script so that this doesnt happen, however, the migration is also occurring inside of a transaction so it's very likely that you wouldn't actually be able to fully drop the old table in every case and there could be other side effects of this as well,  and I cant guarantee this internal manipulation will always work for new Alembic releases.

I want alembic to read the inital version from Public.alembic_version and then write the new upgraded version to the specific schema.alembic_version.

The only way I can get it to work is executing raw sql after the migration, and this won't work for future migrations.

with context.begin_transaction():

context.run_migrations()

context.execute(f'alter table Public.alembic_version set schema {DB_SCHEMA}')

I think what you can do here is check for alembic_version in the old schema and in the new one and then move the table after the migrations but only if it needs to be moved.       Basically have the migrations always run with the schema whereever it is to start with, then move it after the fact.

you'd have to detect which schema to use before you configure the context, however, since you need to pass it the schema for the alembic_version table.

Do I have to flatten/rebase my versions after this?

Thanks,

Brian

--

You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/9ceb8556-e2c3-4631-84cf-8ba636c31a24%40googlegroups.com.

[Brian Hill]

That works. I check if the version table exists in the schema and set version_table_schema if it does. Then after run_migrations if the version table didn't exist in the schema I move it to the schema.

Thanks!

Brian

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsub...@googlegroups.com.

[jens.t...@gmail.com]

Thank you Brian and Mike, just the conversation I was looking for.

One follow-up question — is this a one-time step, and can I point all subsequently generated/applied migrations to the new schema and version_table?

I think, Brian, your question regards a permanent migration of the table public.alembic_version to bla.alembic_version, and then henceforth continue with the bla schema?

Much thanks!

Jens