Alembic autogenerate from current database?

Question

I have a database with a few tables in schema_a and views of these tables in schema_b, and want to use alembic revision --autogenerate to run have alembic detect the views i've created.

When I run alembic revision --autogenerate either using metadata with models.py generated from sqlacodegen my views are represented as tables in the resultant code. Ideally I would like to create a migration that represents the current state of the database, as it was initialized using handmade SQL for all the views and tables. When I look into the autogenerated migrations, i see discrepancies between schemas and views/tables as mentioned. Is there anyway to get a MetaData() object for a running PSQL database instead of relying on models.py metadata object (which doesnt seem to be correct)?

score 0 · Answer 1 · answered Jul 07 '23 at 12:36

I seem to be able to, in my env.py file create a metadata from the running database configuration:

from sqlalchemy import MetaData

def run_migrations_online() -> None:
    target_metadata = MetaData()
    #Connect to running db:
    engine = create_engine(url)
    target_metadata.reflect(bind=engine, views=True, schema="myschema")
    with engine.connect() as connection:
          # set up context and run migrations

Using alembic_utils, I am able to detect the views correctly.

Alembic autogenerate from current database?

1 Answers1