6

I want to use alembic to manage my database in the scope of a single PostgreSQL schema named foo. For automatic deployment of new environments I want alembic to create the schema:

op.execute("create schema foo")

I also want to have the alembic_version table in this schema using version_table_schema='foo' for context.configure in env.py. However I run into a bit of a catch 22 situation as the schema is not yet created and alembic cant check what version its in.

Is there no way to use the version_table_schema kwarg without manual intervention? do i need to have a separate env.py and versions folder to create the schema?

moshevi
  • 4,999
  • 5
  • 33
  • 50

1 Answers1

8

you can create the schema in env.py like this:

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_schemas=True,
            version_table_schema='foo'
        )

        connection.execute('CREATE SCHEMA IF NOT EXISTS foo')

        with context.begin_transaction():
            context.run_migrations()
metersk
  • 11,803
  • 21
  • 63
  • 100
  • 2
    Helpful but this only works with 'online' mode. If I wanted to generate the SQL script with `alembic revision update head --sql`, I would need to manually ensure the schema exists before running the script. – Greg Brown Jan 11 '21 at 15:15
  • I had to use the following code to make it work: `connection.execute(CreateSchema('alembic', if_not_exists=True))` And the schema is imported like this: `from sqlalchemy.schema import CreateSchema` – dlebech Jul 13 '23 at 14:55