SQLAlchemy and Alembic work on top of an abstraction layer and not directly on your database. This may lead to a situation in which a common feature in SQL is not implemented by your specific database system. Let us look what we must do before we can work with constraints in SQLite.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
The problem
If you want to add constraints in SQLAlchemy ORM and try to apply them with Alembic to your SQLite database, you may get an exception like this one:
|
1 |
alembic upgrade head |
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 347126fc065d -> 6af8c1fdf679, make ISBN unique
..
NotImplementedError: No support for ALTER of constraints in SQLite dialect Please refer to the batch mode feature which allows for SQLite migrations using a copy-and-move strategy.
The solution
Alembic has a batch mode that allows it to overcome the shortcomings of the SQLite database:
The SQLite database presents a challenge to migration tools in that it has almost no support for the ALTER statement which relational schema migrations rely upon. The rationale for this stems from philosophical and architectural concerns within SQLite, and they are unlikely to be changed.
Migration tools are instead expected to produce copies of SQLite tables that correspond to the new structure, transfer the data from the existing table to the new one, then drop the old table. For our purposes here we’ll call this “move and copy” workflow
To turn the batch mode on, we need to add render_as_batch=True to two places in the file alembic/env.py:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
... def run_migrations_offline(): ... url = config.get_main_option("sqlalchemy.url") context.configure( url=url, target_metadata=target_metadata, literal_binds=True, dialect_opts={"paramstyle": "named"}, render_as_batch=True ) with context.begin_transaction(): context.run_migrations() def run_migrations_online(): """Run migrations in 'online' mode. In this scenario we need to create an Engine and associate a connection with the context. """ connectable = engine_from_config( config.get_section(config.config_ini_section), prefix="sqlalchemy.", poolclass=pool.NullPool, ) with connectable.connect() as connection: context.configure( connection=connection, target_metadata=target_metadata, render_as_batch=True ) with context.begin_transaction(): context.run_migrations() ... |
You need to delete the migration that did not work and recreate it. Alembic will now use the batch mode for your migration that works with SQLite:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
... def upgrade(): # ### commands auto generated by Alembic - please adjust! ### with op.batch_alter_table('Book', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_Book_ISBN'), ['ISBN'], unique=True) # ### end Alembic commands ### def downgrade(): # ### commands auto generated by Alembic - please adjust! ### with op.batch_alter_table('Book', schema=None) as batch_op: batch_op.drop_index(batch_op.f('ix_Book_ISBN')) # ### end Alembic commands ### |
|
1 |
alembic upgrade head |
INFO [alembic.runtime.migration] Context impl SQLiteImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade 347126fc065d -> e7be9204aa67, make ISBN unique
Next
With the batch mode we can now make changes to existing tables in SQLite. Next week we look how we can create an index and use constraints to enforce data integrity.
2 thoughts on “Python Friday #88: Activate Batch Mode in Alembic to Work With Constraints in SQLite”