Can't create migration for views that depend on other objects in the migration

[mjpieters]

When generating a migration for a view that depends on a new table, alembic_utils triggers a Postgres error as the dependency doesn't exist yet. This is caused by the code in alembic_utils/simulate.py trying to create the views to see if it can resolve a specific creation order.

E.g. I have an ORM table definition:

class _Foo(Model):
    __table_name__ = "foo"

    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, default=func.now())
    bar_id = Column(db.Integer, ForeignKey("bar.id"), nullable=False)

and a view that references that table:

from sqlalchemy.sql import select, func

numbered_foo = (
    select(_Foo).add_columns(
        func.row_number()
        .over(
            partition_by=_Foo.bar_id,
            order_by=[_Foo.created_at, _Foo.id],
        )
        .label("number")
    }
)

# registration with PGView("public", "numbered_foo", str(numbered_foo))

where both the table and the view are entirely new to the project.

Instead, we are forced to create two separate migration steps, one that creates the table, the other with the view, after which we can merge the two scripts.

Is there a hook that I can implement to at least create any missing entities inside of the nested transaction, or a way to register what entities the view depends on?

As you can see, I'm defining the view from a SQLAlchemy Select object, if PGView could accept SQLAlchemy selectables directly, dependency resolution could be simpler, based on introspection.

[alexander-jacob]

I have the same Issue. In env.py I create my views based on metadata with

from alembic_utils.replaceable_entity import register_entities
views = genereate_views_from_metadata()
register_entities(views)

I have no connection in env.py so I cannot check in genereate_views_from_metadata if my dependent tables are already created in the database or not.

[olirice]

I have no connection in env.py so I cannot check in genereate_views_from_metadata if my dependent tables are already created in the database or not.

For --autogenerate support the view's table dependencies must have been created in a prior migration. So long as that is the case, you should not need a connection to the database in env.py

---

but if you do need a connection for some reason, you can connect to the DB from anywhere the same way alembic does

# env.py

from sqlalchemy import create_engine

url = config.get_main_option("sqlalchemy.url")
engine = create_engine(url)

[Tommy-Dimitrio]

I have the same issues and downgrading alembic-utils to "0.2.15" solved the problem.

[olirice]

0.2.15 is significantly out of date. I wouldn't recommend going back that far
This was never supported by alembic_utils so if its incidentally working on an old release there's no guarantee that it'll be stable

[Tommy-Dimitrio]

Quite right.

To use the latest library version i had to implement two step migrations:

# env.py
...
# I will register replaceable entities only if x_argument run_replaceable == "true"
if context.get_x_argument(as_dictionary=True).get("run_replaceable") == 'true':
    register_entities(replaceable_entities_list)

def run_table_migrations():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)
        with context.begin_transaction():
            context.run_migrations()


run_table_migrations()

and then in the automigration script:

# migrate.sh
alembic -x run_replaceable="false" revision --autogenerate
alembic upgrade head
alembic -x run_replaceable="true" revision --autogenerate
alembic upgrade head