SQLite batch migrations: missing operations for index

[sqlalchemy-bot]

Migrated issue, originally created by Thomas Tanner (@ttanner)

I'm trying to use batch migrations with SQLite.
Here's a very simple example:

...
class Example(Base):
    uuid = Column(String(36), unique=True, index=True, primary_key=True)
   ...

In env.py I have added render_as_batch=True and set the metadata

...
        for name, rec in engines.items():
            logger.info("Migrating database %s" % name)
            context.configure(
                        connection=rec['connection'],
                        upgrade_token="%s_upgrades" % name,
                        downgrade_token="%s_downgrades" % name,
                        target_metadata=target_metadata.get(name),
                        render_as_batch=True,
                    )
            context.run_migrations(engine_name=name)

alembic revision --autogenerate creates:

...
def upgrade_example():
   op.create_table('example',
    sa.Column('uuid', sa.String(length=36), nullable=False),
    sa.PrimaryKeyConstraint('uuid'),
  ...
  )
   with op.batch_alter_table('example', schema=None) as batch_op:
        batch_op.create_index(batch_op.f('ix_example_uuid'), ['uuid'], unique=True)

Then "alembic upgrade head" fails with:

#!

INFO  [alembic.env] Migrating database example
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade  -> initial, empty message
Traceback (most recent call last):
  File "<path>/bin/alembic", line 9, in <module>
    load_entry_point('alembic==0.7.5.post2', 'console_scripts', 'alembic')()
  File "<path>/lib/python/site-packages/alembic/config.py", line 439, in main
    CommandLine(prog=prog).main(argv=argv)
  File "<path>/lib/python/site-packages/alembic/config.py", line 433, in main
    self.run_cmd(cfg, options)
  File "<path>/lib/python/site-packages/alembic/config.py", line 416, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "<path>/lib/python/site-packages/alembic/command.py", line 165, in upgrade
    script.run_env()
  File "<path>/lib/python/site-packages/alembic/script.py", line 390, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "<path>/lib/python/site-packages/alembic/util.py", line 243, in load_python_file
    module = load_module_py(module_id, path)
  File "<path>/lib/python/site-packages/alembic/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 173, in <module>
    run_migrations_online()
  File "alembic/env.py", line 153, in run_migrations_online
    context.run_migrations(engine_name=name)
  File "<string>", line 7, in run_migrations
  File "<path>/lib/python/site-packages/alembic/environment.py", line 742, in run_migrations
    self.get_context().run_migrations(**kw)
  File "<path>/lib/python/site-packages/alembic/migration.py", line 309, in run_migrations
    step.migration_fn(**kw)
  File "<proj>/alembic/versions/initial.py", line 23, in upgrade
    globals()["upgrade_%s" % engine_name]()
  File "<proj>/alembic/versions/initial.py", line 56, in upgrade_uuid
    batch_op.create_index(batch_op.f('ix_example_uuid'), ['uuid'], unique=True)
  File "<path>/lib/python2.7/contextlib.py", line 24, in __exit__
    self.gen.next()
  File "<path>/lib/python/site-packages/alembic/operations.py", line 320, in batch_alter_table
    impl.flush()
  File "<path>/lib/python/site-packages/alembic/batch.py", line 71, in flush
    fn = getattr(batch_impl, opname)
AttributeError: 'ApplyBatchImpl' object has no attribute 'create_index'

"alembic upgrade head --sql" fails with:

#!

INFO  [alembic.env] Migrating database example
INFO  [alembic.env] Writing output to example.sql
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Generating static SQL
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade  -> initial, empty message
Traceback (most recent call last):
  File "<path>/bin/alembic", line 9, in <module>
    load_entry_point('alembic==0.7.5.post2', 'console_scripts', 'alembic')()
  File "<path>/lib/python/site-packages/alembic/config.py", line 439, in main
    CommandLine(prog=prog).main(argv=argv)
  File "<path>/lib/python/site-packages/alembic/config.py", line 433, in main
    self.run_cmd(cfg, options)
  File "<path>/lib/python/site-packages/alembic/config.py", line 416, in run_cmd
    **dict((k, getattr(options, k)) for k in kwarg)
  File "<path>/lib/python/site-packages/alembic/command.py", line 165, in upgrade
    script.run_env()
  File "<path>/lib/python/site-packages/alembic/script.py", line 390, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "<path>/lib/python/site-packages/alembic/util.py", line 243, in load_python_file
    module = load_module_py(module_id, path)
  File "<path>/lib/python/site-packages/alembic/compat.py", line 79, in load_module_py
    mod = imp.load_source(module_id, path, fp)
  File "alembic/env.py", line 171, in <module>
    run_migrations_offline()
  File "alembic/env.py", line 112, in run_migrations_offline
    context.run_migrations(engine_name=name)
  File "<string>", line 7, in run_migrations
  File "<path>/lib/python/site-packages/alembic/environment.py", line 742, in run_migrations
    self.get_context().run_migrations(**kw)
  File "<path>/lib/python/site-packages/alembic/migration.py", line 309, in run_migrations
    step.migration_fn(**kw)
  File "<proj>/alembic/versions/initial.py", line 23, in upgrade
    globals()["upgrade_%s" % engine_name]()
  File "<proj>/alembic/versions/initial.py", line 56, in upgrade_example
    batch_op.create_index(batch_op.f('ix_example_uuid'), ['uuid'], unique=True)
  File "<path>/lib/python2.7/contextlib.py", line 24, in __exit__
    self.gen.next()
  File "<path>/lib/python/site-packages/alembic/operations.py", line 320, in batch_alter_table
    impl.flush()
  File "<path>/lib/python/site-packages/alembic/batch.py", line 66, in flush
    *self.reflect_args, **self.reflect_kwargs)
  File "<path>/lib/python/site-packages/sqlalchemy/sql/schema.py", line 406, in __new__
    table._init(name, metadata, *args, **kw)
  File "<path>/lib/python/site-packages/sqlalchemy/sql/schema.py", line 479, in _init
    self._autoload(metadata, autoload_with, include_columns)
  File "<path>/lib/python/site-packages/sqlalchemy/sql/schema.py", line 489, in _autoload
    autoload_with.run_callable(
AttributeError: 'MockConnection' object has no attribute 'run_callable'

sqlalchemy 0.9.9, alembic 0.7.5post2, Python 2.7.9

[sqlalchemy-bot]

Changes by Thomas Tanner (@ttanner):

• edited description

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

what's unusual here is that the "create index" is in the batch context at all. SQLite supports CREATE INDEX and DROP INDEX. moving it out of "batch" would resolve. Though it looks like in source code that index moves were intended to be within the batch for that table in any case, and the method is just named incorrectly. which means the bug is that a test wasn't added for this.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

no bug in the --sql case, offline mode with batch requires additional, documented steps that aren't present in your test case.

[sqlalchemy-bot]

Thomas Tanner (@ttanner) wrote:

agreed. I just think a more useful error message would be helpful in the offline case.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

separate issue then.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Repaired support for the :meth:.BatchOperations.create_index
  directive, which was mis-named internally such that the operation
  within a batch context could not proceed.
  fixes SQLite batch migrations: missing operations for index #287

→ 2af6e43

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed