Dropping `alembic_version` table inside migration script fails #998

baileythegreen · 2022-03-05T02:36:42Z

baileythegreen
Mar 5, 2022

Hi, I'm in the process of writing a wrapper around alembic for a tool I maintain, which uses an SQLite database. At the moment I'm working on writing tests, and I want to test using my wrapper to upgrade and downgrade databases; to this end, I have one set of targets—both before, and after the migration—and one set of golden copies that get copied to create the databases for testing.

The target I have that represents the base at the moment, does not contain the alembic_version table, so I am trying to ensure that table is dropped in the migration.

from alembic import op

•••

def downgrade():

    with op.batch_alter_table("comparisons") as batch_op:
        batch_op.drop_column("kmersize")
        batch_op.drop_column("minmatch")
        batch_op.drop_constraint("fastani_reqs")
        batch_op.create_unique_constraint(
            "base_reqs",
            ["query_id", "subject_id", "program", "version", "fragsize", "maxmatch"],
        )

    op.drop_table("alembic_version")

With this version, I get the traceback and error shown below. This is similar to what I have gotten before when I tried to remove constrained elements improperly, so I think it may have to do with that.

Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: no such table: alembic_version
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/config.py", line 588, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/config.py", line 582, in main
    self.run_cmd(cfg, options)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/config.py", line 559, in run_cmd
    fn(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/command.py", line 366, in downgrade
    script.run_env()
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 104, in <module>
    run_migrations_online()
  File "alembic/env.py", line 79, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/runtime/migration.py", line 627, in run_migrations
    head_maintainer.update_to_step(step)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/runtime/migration.py", line 812, in update_to_step
    self._delete_version(vers)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/runtime/migration.py", line 765, in _delete_version
    ret = self.context.impl._exec(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/ddl/impl.py", line 197, in _exec
    return conn.execute(construct, multiparams)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
    ret = self._execute_context(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: alembic_version
[SQL: DELETE FROM alembic_version WHERE alembic_version.version_num = '92f7f6b1626e']
(Background on this error at: https://sqlalche.me/e/14/e3q8)

However, I also alter a constraint in another table in this migration (see above code), and I tried using the commented-out lines below to recreate what I did there, to no avail.

from alembic import op

•••

def downgrade():

    with op.batch_alter_table("comparisons") as batch_op:
        batch_op.drop_column("kmersize")
        batch_op.drop_column("minmatch")
        batch_op.drop_constraint("fastani_reqs")
        batch_op.create_unique_constraint(
            "base_reqs",
            ["query_id", "subject_id", "program", "version", "fragsize", "maxmatch"],
        )

    # with op.batch_alter_table("alembic_version") as batch_op:
    #     batch_op.drop_column("version_num")
    #     batch_op.drop_constraint("alembic_version_pkc")

    op.drop_table("alembic_version")

This, instead, gives me a different error:

Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: near ")": syntax error
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/config.py", line 588, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/config.py", line 582, in main
    self.run_cmd(cfg, options)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/config.py", line 559, in run_cmd
    fn(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/command.py", line 366, in downgrade
    script.run_env()
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/script/base.py", line 563, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 92, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/util/pyfiles.py", line 108, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 104, in <module>
    run_migrations_online()
  File "alembic/env.py", line 79, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/runtime/migration.py", line 620, in run_migrations
    step.migration_fn(**kw)
  File "/Users/baileythegreen/Software/pyani/alembic/versions/92f7f6b1626e_add_fastani_columns.py", line 126, in downgrade
    batch_op.drop_constraint("alembic_version_pkc")
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/contextlib.py", line 120, in __exit__
    next(self.gen)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/operations/base.py", line 374, in batch_alter_table
    impl.flush()
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/operations/batch.py", line 140, in flush
    batch_impl._create(self.impl)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/operations/batch.py", line 411, in _create
    op_impl.create_table(self.new_table)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/ddl/impl.py", line 356, in create_table
    self._exec(schema.CreateTable(table))
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/alembic/ddl/impl.py", line 197, in _exec
    return conn.execute(construct, multiparams)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1289, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_ddl
    ret = self._execute_context(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1802, in _execute_context
    self.dialect.do_execute(
  File "/Users/baileythegreen/Software/miniconda3/envs/pyani_dev/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near ")": syntax error
[SQL: 
CREATE TABLE _alembic_tmp_alembic_version (
)
]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

It may be relevant to note that I have modified the ORM (see below) used to generate new databases so that the alembic_version table is created initially; however, I have been able to achieve the same schema that Alembic generated when this was not done, so I am not sure why that would cause an issue.

class Alembic(Base):
    __tablename__ = "alembic_version"
    __table_args__ = (PrimaryKeyConstraint("version_num", name="alembic_version_pkc"),)

    version_num = Column(types.VARCHAR(32), primary_key=True, nullable=False)

Answered by zzzeek

Mar 5, 2022

Alembic relies upon the configured alembic_version table in order to track what migrations it needs to run. It's not possible to have a migration that deletes the table itself.

For Alembic's management of the "alembic_version" table, on a clean database Alembic will create this table first if it does not exist, before it runs migrations. Then, if you run a downgrade operation all the way to the "base", Alembic automatically deletes the table. So if you were to downgrade a datbase all the way down, the table is deleted for you.

Beyond that, the problem being solved isn't clear to me (specifically "The target I have that represents the base at the moment, does not contain the alembic_versio…

View full answer

zzzeek · 2022-03-05T14:14:42Z

zzzeek
Mar 5, 2022
Maintainer

Alembic relies upon the configured alembic_version table in order to track what migrations it needs to run. It's not possible to have a migration that deletes the table itself.

For Alembic's management of the "alembic_version" table, on a clean database Alembic will create this table first if it does not exist, before it runs migrations. Then, if you run a downgrade operation all the way to the "base", Alembic automatically deletes the table. So if you were to downgrade a datbase all the way down, the table is deleted for you.

Beyond that, the problem being solved isn't clear to me (specifically "The target I have that represents the base at the moment, does not contain the alembic_version table, so I am trying to ensure that table is dropped in the migration." - if you are running Alembic, it will automatically drop the alembic_version table when the database is downgraded all the way. I'm assuming these are downgrade operations).

If this is a test suite that aims to have a clean database when the suite finishes, I would add a "drop" phase to the test suite's teardown, external to the migrations that are being tested. This could be either a direct drop of the alembic_version table or for a more comprehensive approach you could use a recipe like dropalltables that will locate all tables within a schema and drop them.

4 replies

baileythegreen Mar 5, 2022
Author

Hi, @zzzeek thanks for replying.

(I typed this out once, then accidentally clicked on the link to my profile, and lost what I'd typed, so hopefully I haven't left out something important in retyping this.)

Alembic relies upon the configured alembic_version table in order to track what migrations it needs to run. It's not possible to have a migration that deletes the table itself.

This makes sense, and I figured that was probably why I hadn't managed to get it working. That said...

Then, if you run a downgrade operation all the way to the "base", Alembic automatically deletes the table. So if you were to downgrade a database all the way down, the table is deleted for you.

the issue is that this is not happening. What is happening, is that the single entry in the alembic_version table is deleted; but the table is still there, empty. The rest of the downgrade works as expected—with no error messages, if I remove everything that tried to drop the alembic_version table explicitly—so I thought the empty table was left behind by design.

I've included what I see with the alembic_version in sqlite both before, and after the downgrade below, as well as the migration script. I tried this again after reading your reply, to see if I'd missed something.

I could add a direct call to SQL to drop the table, but since the purpose of the tests I'm writing is to make sure the migration runs correctly, and you say dropping the table should be happening automatically, it seems worthwhile to pursue the fact that it is still there.

Before running downgrade:

! sqlite3 scratch/alembic/new_db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .schema alembic_version
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL, 
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);
sqlite> SELECT * FROM alembic_version;
92f7f6b1626e
sqlite> .quit

After running downgrade:

! sqlite3 scratch/alembic/new_db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .table
alembic_version   comparisons       labels            runs_comparisons
blastdbs          genomes           runs              runs_genomes    
sqlite> .schema alembic_version
CREATE TABLE alembic_version (
	version_num VARCHAR(32) NOT NULL, 
	CONSTRAINT alembic_version_pkc PRIMARY KEY (version_num)
);
sqlite> SELECT * FROM alembic_version;
sqlite> 
sqlite> .quit

Migration script:

"""add fastani columns

Revision ID: 92f7f6b1626e
Revises:
Create Date: 2022-02-07 22:57:35.779356

"""
from alembic import op
import sqlalchemy as sa

from sqlalchemy import (
    Column,
    Integer,
    Float,
    UniqueConstraint,
)

# revision identifiers, used by Alembic.
revision = "92f7f6b1626e"
down_revision = None
branch_labels = None
depends_on = None


meta = MetaData()


def upgrade():

    with op.batch_alter_table("comparisons") as batch_op:
        batch_op.add_column(sa.Column("kmersize", sa.Integer))
        batch_op.add_column(sa.Column("minmatch", sa.Float))
        batch_op.create_unique_constraint(
            "fastani_reqs",
            [
                "query_id",
                "subject_id",
                "program",
                "version",
                "fragsize",
                "maxmatch",
                "kmersize",
                "minmatch",
            ],
        )


def downgrade():

    with op.batch_alter_table("comparisons") as batch_op:
        batch_op.drop_column("kmersize")
        batch_op.drop_column("minmatch")
        batch_op.drop_constraint("fastani_reqs")
        batch_op.create_unique_constraint(
            "base_reqs",
            ["query_id", "subject_id", "program", "version", "fragsize", "maxmatch"],
        )

zzzeek Mar 7, 2022
Maintainer

my apologies, I just checked the source code, the automatic "drop" of the alembic version table seems only to apply to --sql mode, where it's generating a fixed SQL script and not actually dropping any tables. So it is expected for a live migration that alembic_version table will remain present after all downgrades have completed. So I would add an explicit step to drop this table to your test suite's teardown process.

baileythegreen Mar 11, 2022
Author

I keep meaning to reply to this.

Thanks for looking into that; I was under the impression that the SQL script generated by --sql was supposed to exactly reproduce what would be done the in the online mode. Dropping the table with an external call works, though I think I'm going to change the test strategy a bit to account for the fact that presence or absence of that table is not what we actually care about.

zzzeek Mar 11, 2022
Maintainer

I dont really remember why I felt that --sql mode should have a different behavior in this area, though i do recall it seemed important at the time :) that's what I got, glad you can work around it :)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Dropping `alembic_version` table inside migration script fails #998

{{title}}

Replies: 1 comment 4 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Dropping alembic_version table inside migration script fails #998

baileythegreen Mar 5, 2022

Replies: 1 comment · 4 replies

zzzeek Mar 5, 2022 Maintainer

baileythegreen Mar 5, 2022 Author

zzzeek Mar 7, 2022 Maintainer

baileythegreen Mar 11, 2022 Author

zzzeek Mar 11, 2022 Maintainer

Dropping `alembic_version` table inside migration script fails #998

baileythegreen
Mar 5, 2022

Replies: 1 comment 4 replies

zzzeek
Mar 5, 2022
Maintainer

baileythegreen Mar 5, 2022
Author

zzzeek Mar 7, 2022
Maintainer

baileythegreen Mar 11, 2022
Author

zzzeek Mar 11, 2022
Maintainer