Create table and view in the same migration in SQLAlchemy

Question

I'm trying to create a table and a view based on this table in a SQLAlchemy/alembic/PostgreSQL environment, with alembic revision --autogenerate. I could properly setup things so that alembic identifies the table and the view to be created, following the structure proposed here.

However, since I'm trying to create a table and the view based on this table on the same migration, problems arise. When it is time to create the view it fails saying that the table does not yet exist.

It works fine if I comment out the register_entities([my_view]) from my migrations/env.py file, run the migration once (so it creates the table), then undo the comment and run the migration again (which creates the view). However, I would like to solve this issue so there is no need for any shady process for deploy or for new people working in the repo.

I'm not sure if it's trying to create the view before the table or what. Any tips on how to solve this?

Here are the full logs from running alembic revision --autogenerate:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'my_table'
INFO  [alembic_utils.depends] Resolving entities with no dependencies
INFO  [alembic_utils.depends] Resolving entities with dependencies. This may take a minute
INFO  [alembic_utils.replaceable_entity] Detecting required migration op PGView PGView: public.my_view
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: view "my_view" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/simulate.py", line 47, in simulate_entity
    sess.execute(entity.to_sql_statement_drop(cascade=True))
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2197, in _execute_internal
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) view "my_view" does not exist

[SQL: DROP VIEW "public"."my_view" cascade]
(Background on this error at: https://sqlalche.me/e/20/f405)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UndefinedTable: relation "my_table" does not exist
LINE 2: FROM my_table;
             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/usr/local/lib/python3.11/site-packages/alembic/config.py", line 630, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.11/site-packages/alembic/config.py", line 624, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.11/site-packages/alembic/config.py", line 601, in run_cmd
    fn(
  File "/usr/local/lib/python3.11/site-packages/alembic/command.py", line 234, in revision
    script_directory.run_env()
  File "/usr/local/lib/python3.11/site-packages/alembic/script/base.py", line 579, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 940, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/workspaces/api/app/migrations/env.py", line 99, in <module>
    run_migrations_online()
  File "/workspaces/api/app/migrations/env.py", line 93, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.11/site-packages/alembic/runtime/environment.py", line 938, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.11/site-packages/alembic/runtime/migration.py", line 612, in run_migrations
    for step in self._migrations_fn(heads, self):
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/alembic/command.py", line 210, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 567, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/api.py", line 614, in _run_environment
    compare._populate_migration_script(
  File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/compare.py", line 59, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/usr/local/lib/python3.11/site-packages/alembic/autogenerate/compare.py", line 92, in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
  File "/usr/local/lib/python3.11/site-packages/alembic/util/langhelpers.py", line 268, in go
    fn(*arg, **kw)
  File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 328, in compare_registered_entities
    maybe_op = entity.get_required_migration_op(sess, dependencies=has_create_or_update_op)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 163, in get_required_migration_op
    db_def = self.get_database_definition(sess, dependencies=dependencies)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/replaceable_entity.py", line 102, in get_database_definition
    with simulate_entity(sess, self, dependencies) as sess:
  File "/usr/local/lib/python3.11/contextlib.py", line 137, in __enter__
    return next(self.gen)
           ^^^^^^^^^^^^^^
  File "/home/vscode/.local/lib/python3.11/site-packages/alembic_utils/simulate.py", line 62, in simulate_entity
    sess.execute(entity.to_sql_statement_create())
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2306, in execute
    return self._execute_internal(
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2197, in _execute_internal
    result = conn.execute(
             ^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
           ^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1639, in _execute_clauseelement
    ret = self._execute_context(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1848, in _execute_context
    return self._exec_single_context(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1988, in _exec_single_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2343, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) relation "my_table" does not exist
LINE 2: FROM my_table;
             ^

[SQL: CREATE VIEW "public"."my_view" AS SELECT my_table.id AS id, my_table.field1 AS field1, my_table.field2 AS field2 
FROM my_table;]

Versions:

• psycopg2~=2.9
• sqlalchemy~=2.0
• sqlalchemy-utils~=0.41.1
• alembic~=1.10
• alembic_utils~=0.8.2

Answer 1

Unfortunately, I had a similar experience when creating triggers and functions using alembic_utils based on newly created tables. When I tried to --autogenerate it complained in trying to create the function because the table did not exist.

I traced this back to a function that alembic_utils runs called simulate_entity which creates the entity in a transaction so the postgres rendered definition can be retrieved. This cannot occur without the tables already existing in the database. So unfortunately, unless there is an update to alembic_utils that changes the way they determine how a view/function/trigger should be created, we are stuck with the workaround of;

1. Generate the table migration first
2. Add in the view, function, etc. and generate the migration for that
3. Either combine or leave seperate

Relevant Versions;

alembic = "1.13.1"
GeoAlchemy2 = "0.14.1"
SQLAlchemy-Utils = "0.41.1"
alembic_utils = "0.8.2"
sqlalchemy = "2.0.19"