Alembic & Postgres: how to switch to another schema

Question

I have a postgres database with default public schema, which hosts some tables, including alembic migrations table.

I want a postgres database with an app schema, with all the tables (including alembic migrations) moved there.

How can I do this using Alembic migrations in concert with sqlalchemy and whatever settings change?

I found some info in related questions: SQLAlchemy + alembic: create schema migration and How to specific table schema for alembic_version when using flask-migrate and Create an alembic migration to create schema for version_table_schema, but I'm strugginling to put them together in one (or two) neat migration + code changes.

I think it would be nice to have a recipe here, and I intend to post it myself in a day or so, if no one saves me from this nuisance :)

Answer 1

All right, here it goes. The tricky part is to migrate the alembic_version table, as it is read before the migration and written to after. Also there are couple of places things need to be configured.

First commit

First commit is for preparation. Wherever is you Base defined, change it to

base.py

from sqlalchemy import MetaData
from sqlalchemy.orm import declarative_base

Base = declarative_base(metadata=MetaData(schema='public'))

Then modify the alembic env (thanks h4!) to match this:

env.py

[...]

context.configure(
    connection=connection,
    target_metadata=target_metadata,
    version_table_schema=target_metadata.schema,
    include_schemas=True,
)

with context.begin_transaction():
    context.execute(f'create schema if not exists {target_metadata.schema};')
    context.execute(f'set search_path to {target_metadata.schema}')
    context.run_migrations()

[...]

Second commit

Create a new alembic migration

2d8cec1e2a62_rename_schema.py

from alembic import op
import sqlalchemy as sa

[...]

def upgrade():
    if 'app' not in sa.inspect(op.get_bind()).get_schema_names():
        op.execute('alter schema public rename to app')
        op.execute('create schema public')
        op.execute('create table public.alembic_version AS (select * from app.alembic_version)')
    else:
        op.execute('drop table public.alembic_version')


def downgrade():
    pass

Commit and run migration!

Third commit

Change the schema in base.py to 'app':

base.py

from sqlalchemy import MetaData
from sqlalchemy.orm import declarative_base

Base = declarative_base(metadata=MetaData(schema='app'))

Commit and run migration.