Single Schema Postgres Alembic Autogenerate Migration

Question

I have a Postgres server which we have one database in. This one database is our data warehouse. We have a schema per software application in this database.

I'm working on a new project which I'm using sqlalchemy's alembic to create the schema migrations. However, because of the way my DB is setup... it looks like the --autogenerate option of the revision generator is scanning all the schemas in the database.

I can't find an option to restrict the inspection to only one schema. The only option I found was to create a function to pass into the inclue_object parameter in the alembic context. So alembic will scan all the schemas but will only use the schema/tables if that function returns true. This is less than ideal because I have hundreds of tables... so this process is slow.

def include_object(object, name, type_, reflected, compare_to):
    print(object, name, type_, reflected, compare_to)
    if type_ == 'table' and object.schema != 'leads_manager':
        print('returning false')
        return False
    else:
        print('returning true')
        return True

def run_migrations_offline():
    url = get_db_uri()
    context.configure(
        url=url,
        target_metadata=target_metadata,
        include_object=include_object,
    )

    with context.begin_transaction():
        context.execute('SET search_path TO leads_manager')
        context.run_migrations()

Anyone know how to restrict alembic autogenerate to only one schema in postgres?

This might be helpful https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path — snakecharmerb, Aug 15 '20 at 16:23
It's crazy how HUGE sqlalchemy is in terms of functionality, features, and settings — Alex Luis Arias, Aug 17 '20 at 12:52

Gabriel Medina Braga · Answer 1 · 2023-01-11T19:29:39.487

My solution was to make the target_metadata argument point to the schema I want, and remove the include_schemas parameter from the context configuration in the env.py file.

For example, my target_metadata was defined as:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import MetaData

target_metadata = declarative_base(metadata=MetaData(schema='my_schema'))

And in my env.py file I had:

def include_name(name, type_, parent_names):
    if type_ == "schema":
        return name in ["my_schema"]
    else:
        return True

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

PS.: I had many problemas by having a typo "include_schema" instead of "include_schemas".

Do you mind clarifying your explanation and your code snippets? > "and remove the include_schemas parameter from the context configuration in the env.py file." But in your snippet, you still have `include_schemas=True,` passed in. — lalilulelo, Apr 23 '23 at 16:24

score 0 · Accepted Answer · answered Aug 15 '20 at 12:59

0

I ended up creating a user that only has access to my schema of interest. Alembic --autogenerate then only inspects that schema since it doesn't have access to any others.

answered Aug 15 '20 at 12:59

Alex Luis Arias

1,313
1
14
27

Single Schema Postgres Alembic Autogenerate Migration

2 Answers2