Apply all migrations from scratch on the test database

[Timelessprod]

Hello,

I am using alembic in a project to handle the database migration and I am having lot of problems with it when running tests.

My project is running 2 Docker containers while testing: a postgres database (without volume i.e. it's dropped between runs) and the python testsuite. The testsuite first run alembic ensure_migration to generate the migration table as it doesn't exists, then run alembic upgrade head using all the version files and finally launch pytest.

However it seems the alembic ensure_migration is working fine because after its execution, the database has the table in the public schema (checked with DBeaver) but the second command alembic upgrade head fails with the following error:

+ alembic ensure_version
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
+ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 46329a8f829d, 1st version
INFO  [alembic.runtime.migration] Running upgrade 46329a8f829d -> 4e9f22bec02e, 2nd version
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py", line 1969, in _exec_single_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/default.py", line 922, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.9/dist-packages/psycopg/cursor.py", line 737, in execute
    raise ex.with_traceback(None)
psycopg.errors.UndefinedTable: relation "public.alembic_version" does not exist
LINE 1: UPDATE public.alembic_version SET version_num='4e9f22bec02e'...
               ^

So why is Alembic not finding the table it has just generated ? I just want Alembic to create his version table and iteratively apply all migrations one by one to the postgres DB so I can run the tests.

It was running fine when I did the first migration (46329a8f829d). The alembic.ici file is here, as well as the env.py which has the proper db url config. If I run a docker-compose up and run the alembic commands on another terminal it gives the same results.

[Timelessprod]

I find that Alembic has added by itself a op.drop_table('alembic_version') in the upgrade function of the 2nd version file. Is this supposed to be there ? If not why did it added this by itself ?

[zzzeek]

hi -

this is incredibly scant detail but based on the message "relation "public.alembic_version"" it seems like you have something weird going on with your configured search_path and/or how you have configured your tables and/or migration context, or perhaps you have set version_table_schema to "public" which would probably cause problems.

I would advise you set your PostgreSQL search_path to 'public' and remove any references to the name "public" in the "schema" for tables or in your metadata config including version_table_schema.

[Timelessprod]

I created all my tables in the public schema (I tried to put the in another specific schema but it became a nightmare for Alembic so I switched back to public) using __table_args__: dict = {'schema': 'public'}. In my main Python folder where the alembic command is being run (locally or in the container), there're the alembic.ini and a migration directory with the versions, env.py etc. inside. Prepend sys path is set to . as default. I did not specify the schema for the version table, Alembic put it in public by default I presume.

[zzzeek]

I created all my tables in the public schema

that's the default for PostgreSQL, if you omit the schema entirely (which is what I recommend), "public" is where they will go.

(I tried to put the in another specific schema but it became a nightmare for Alembic so I switched back to public)

Alembic supports this but let's start small.

using __table_args__: dict = {'schema': 'public'}.

OK remove that. public is the default schema. re-stating it confuses SQLAlchemy and Alembic. See https://docs.sqlalchemy.org/en/20/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path .

Overall, if your PG search path is left at its default of "public", and you omit the word "public" from any of your Python code regarding table schemas, everything should just work.

[Timelessprod]

Ok I'll do that thanks. And what is the cause of Alembic putting a drop on its own version table in the generated version files ?

[zzzeek]

not sure, it must think that this table is not the one that's supposed to be used for "alembic version". this still suggests either the postgresql search_path might be something, or some directives are in your env.py that shouldnt be there regarding version_table_name or version_table_schema. also set include_schemas=False if you have that set.