alembic_version creation fails when in different new schema (Postgres)

[Jules Olléon]

Hello,

I have an issue with schema creation when the alembic_version table is not in the default (public) schema on Postgres.

We have a setup where multiple services use the same Postgres DB (to simplify ops work) but we are trying to isolate each service in its own Postgres schema.

The `public` schema is already used by another service with its own alembic_version table, so I used the `version_table_schema` parameter to put alembic_version in the right schema (the one that belongs to my service). I ran into some interesting issues (but figured it out, see https://stackoverflow.com/questions/40577640/flask-migrate-using-different-postgres-schemas-table-args-schema-te) but eventually got pretty much everything working *assuming the schema already exists*.

I'm now trying to setup tests that run all migrations on a fresh DB part on our CI/CD pipeline for additional confidence, and ran into this issue: for a fresh DB my schema doesn't exist yet, and migrations are thus failing. How to create the schema initially?

I was hoping to use alembic for this and modified my initial migration to do:

    op.execute('CREATE SCHEMA IF NOT EXISTS data')

but this is not sufficient since the alembic_version table itself is stored in that schema ('data'), and alembic tries to create the version table before running the initial migration, which fails since the schema doesn't exist:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.

INFO  [alembic.runtime.migration] Will assume transactional DDL.

Traceback (most recent call last):

  File "/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context

    context)

  File "/usr/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute

    cursor.execute(statement, parameters)

psycopg2.ProgrammingError: schema "data" does not exist

LINE 2: CREATE TABLE data.alembic_version (

                     ^

I'd love to keep all DB setup within Alembic, any idea how to fix this? Otherwise I'll need to create the schema separately before running alembic migrations :(.

Thanks,

Jules

[Mike Bayer]

so if you are using the "version_table_schema" parameter to set this
schema, that means you know what this schema is inside of your env.py.
So put "connection.execute("CREATE SCHEMA IF NOT EXISTS data")" right
there, in your env.py when you first get the connection and before you
call "context.begin_transaction()".

>
> INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
> INFO [alembic.runtime.migration] Will assume transactional DDL.
> Traceback (most recent call last):
> File "/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line
> 1182, in _execute_context
> context)
> File "/usr/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line
> 470, in do_execute
> cursor.execute(statement, parameters)
> psycopg2.ProgrammingError: schema "data" does not exist
> LINE 2: CREATE TABLE data.alembic_version (
> ^
>
> I'd love to keep all DB setup within Alembic, any idea how to fix this?
> Otherwise I'll need to create the schema separately before running alembic
> migrations :(.
>
> Thanks,
> Jules
>

> --
> 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.
> For more options, visit https://groups.google.com/d/optout.