Batch op: rename column then create index on it

[anentropic]

I am trying to use batch ops to get some degree of atomicity in my migrations on a sqlite db

Should this work?

def upgrade() -> None:
    with op.batch_alter_table("turn") as batch_op:
        batch_op.drop_index("turn_game_id")
        batch_op.alter_column("game_id", new_column_name="gamesession_id")
        batch_op.create_index("turn_gamesession_id", ["gamesession_id"])

Currently I get an error on the last op:

  File "site-packages/alembic/operations/batch.py", line 161, in flush
    batch_impl._create(self.impl)
  File "site-packages/alembic/operations/batch.py", line 477, in _create
    for idx in self._gather_indexes_from_both_tables():
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "site-packages/alembic/operations/batch.py", line 398, in _gather_indexes_from_both_tables
    *[self.new_table.c[col] for col in index.columns.keys()],
     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "site-packages/alembic/operations/batch.py", line 398, in <listcomp>
    *[self.new_table.c[col] for col in index.columns.keys()],
      ~~~~~~~~~~~~~~~~^^^^^
  File "site-packages/sqlalchemy/sql/base.py", line 1214, in __getitem__
    return self._index[key]
           ~~~~~~~~~~~^^^^^
KeyError: 'gamesession_id'

[anentropic]

and then the temp table gets left behind so next attempt to migrate gives:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table _alembic_tmp_turn already exists

[zzzeek]

probably not, because the existing table being reflected does not have the "gamesession_id" column yet. it should have a nicer error message.

I would do the index drop/add stuff outside of the batch op because SQLite supports CREATE INDEX and DROP INDEX directly.

re: "atomiticy", SQLite supports transactional DDL. Follow the steps at https://docs.sqlalchemy.org/en/20/dialects/sqlite.html#pysqlite-serializable to work around driver issues.

[anentropic]

Oh, great!

So if I add those event handlers to my env.py for Alembic it should mean that each upgrade/downgrade will run in a transaction and rollback on errors?

And I then only need to use batch ops for the alter column stuff that SQLite doesn't support directly

[zzzeek]

yes. Also for alembic, set the transactional_ddl parameter to True in your env.py when you set up SQLite to work this way, since the SQLite backend defaults to assuming DDL is not transactional.