diff options
Diffstat (limited to 'docs/build/batch.rst')
| -rw-r--r-- | docs/build/batch.rst | 206 |
1 files changed, 206 insertions, 0 deletions
diff --git a/docs/build/batch.rst b/docs/build/batch.rst new file mode 100644 index 0000000..338ab8a --- /dev/null +++ b/docs/build/batch.rst @@ -0,0 +1,206 @@ +.. _batch_migrations: + +Running "Batch" Migrations for SQLite and Other Databases +========================================================= + +.. note:: "Batch mode" for SQLite and other databases is a new and intricate + feature within the 0.7.0 series of Alembic, and should be + considered as "beta" for the next several releases. + +.. versionadded:: 0.7.0 + +The SQLite database presents a challenge to migration tools +in that it has almost no support for the ALTER statement upon 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, and in order to accommodate it +in a way that is reasonably predictable, while also remaining compatible +with other databases, Alembic provides the **batch** operations context. + +Within this context, a relational table is named, and then a series of +mutation operations to that table alone are specified within +the block. When the context is complete, a process begins whereby the +"move and copy" procedure begins; the existing table structure is reflected +from the database, a new version of this table is created with the given +changes, data is copied from the +old table to the new table using "INSERT from SELECT", and finally the old +table is dropped and the new one renamed to the original name. + +The :meth:`.Operations.batch_alter_table` method provides the gateway to this +process:: + + with op.batch_alter_table("some_table") as batch_op: + batch_op.add_column(Column('foo', Integer)) + batch_op.drop_column('bar') + +When the above directives are invoked within a migration script, on a +SQLite backend we would see SQL like: + +.. sourcecode:: sql + + CREATE TABLE _alembic_batch_temp ( + id INTEGER NOT NULL, + foo INTEGER, + PRIMARY KEY (id) + ); + INSERT INTO _alembic_batch_temp (id) SELECT some_table.id FROM some_table; + DROP TABLE some_table; + ALTER TABLE _alembic_batch_temp RENAME TO some_table; + +On other backends, we'd see the usual ``ALTER`` statements done as though +there were no batch directive - the batch context by default only does +the "move and copy" process if SQLite is in use, and if there are +migration directives other than :meth:`.Operations.add_column` present, +which is the one kind of column-level ALTER statement that SQLite supports. +:meth:`.Operations.batch_alter_table` can be configured +to run "move and copy" unconditionally in all cases, including on databases +other than SQLite; more on this is below. + + +Dealing with Constraints +------------------------ + +One area of difficulty with "move and copy" is that of constraints. If +the SQLite database is enforcing referential integrity with +``PRAGMA FOREIGN KEYS``, this pragma may need to be disabled when the workflow +mode proceeds, else remote constraints which refer to this table may prevent +it from being dropped; additionally, for referential integrity to be +re-enabled, it may be necessary to recreate the +foreign keys on those remote tables to refer again to the new table (this +is definitely the case on other databases, at least). SQLite is normally used +without referential integrity enabled so this won't be a problem for most +users. + +"Move and copy" also currently does not account for CHECK constraints, assuming +table reflection is used. If the table being recreated has any CHECK +constraints, they need to be specified explicitly, such as using +:paramref:`.Operations.batch_alter_table.table_args`:: + + with op.batch_alter_table("some_table", table_args=[ + CheckConstraint('x > 5') + ]) as batch_op: + batch_op.add_column(Column('foo', Integer)) + batch_op.drop_column('bar') + +For UNIQUE constraints, SQLite unlike any other database supports the concept +of a UNIQUE constraint that has no name at all; all other backends always +assign a name of some kind to all constraints that are otherwise not named +when they are created. In SQLAlchemy, an unnamed UNIQUE constraint is +implicit when the ``unique=True`` flag is present on a +:class:`~sqlalchemy.schema.Column`, so on SQLite these constraints will +remain unnamed. + +The issue here is that SQLAlchemy until version 1.0 does not report on these +SQLite-only unnamed constraints when the table is reflected. So to support +the recreation of unnamed UNIQUE constraints, either they should be named +in the first place, or again specified within +:paramref:`.Operations.batch_alter_table.table_args`. + +Working in Offline Mode +----------------------- + +Another big limitation of "move and copy" is that in order to make a copy +of a table, the structure of that table must be known. +:meth:`.Operations.batch_alter_table` by default will use reflection to +get this information, which means that "online" mode is required; the +``--sql`` flag **cannot** be used without extra steps. + +To support offline mode, the system must work without table reflection +present, which means the full table as it intends to be created must be +passed to :meth:`.Operations.batch_alter_table` using +:paramref:`.Operations.batch_alter_table.copy_from`:: + + meta = MetaData() + some_table = Table( + 'some_table', meta, + Column('id', Integer, primary_key=True), + Column('bar', String(50)) + ) + + with op.batch_alter_table("some_table", copy_from=some_table) as batch_op: + batch_op.add_column(Column('foo', Integer)) + batch_op.drop_column('bar') + +The above use pattern is pretty tedious and quite far off from Alembic's +preferred style of working; however, if one needs to do SQLite-compatible +"move and copy" migrations and need them to generate flat SQL files in +"offline" mode, there's not much alternative. + + +Batch mode with Autogenerate +---------------------------- + +The syntax of batch mode is essentially that :meth:`.Operations.batch_alter_table` +is used to enter a batch block, and the returned :class:`.BatchOperations` context +works just like the regular :class:`.Operations` context, except that +the "table name" and "schema name" arguments are omitted. + +To support rendering of migration commands in batch mode for autogenerate, +configure the :paramref:`.EnvironmentContext.configure.render_as_batch` +flag in ``env.py``:: + + context.configure( + connection=connection, + target_metadata=target_metadata, + render_as_batch=True + ) + +Autogenerate will now generate along the lines of:: + + def upgrade(): + ### commands auto generated by Alembic - please adjust! ### + with op.batch_alter_table('address', schema=None) as batch_op: + batch_op.add_column(sa.Column('street', sa.String(length=50), nullable=True)) + +This mode is safe to use in all cases, as the :meth:`.Operations.batch_alter_table` +directive by default only takes place for SQLite; other backends will +behave just as they normally do in the absense of the batch directives. + +Note that autogenerate support does not include "offline" mode, where +the :paramref:`.Operations.batch_alter_table.copy_from` parameter is used. +The table definition here would need to be entered into migration files +manually if this is needed. + +Batch mode with databases other than SQLite +-------------------------------------------- + +There's an odd use case some shops have, where the "move and copy" style +of migration is useful in some cases for databases that do already support +ALTER. There's some cases where an ALTER operation may block access to the +table for a long time, which might not be acceptable. "move and copy" can +be made to work on other backends, though with a few extra caveats. + +The batch mode directive will run the "recreate" system regardless of +backend if the flag ``recreate='always'`` is passed:: + + with op.batch_alter_table("some_table", recreate='always') as batch_op: + batch_op.add_column(Column('foo', Integer)) + +The issues that arise in this mode are mostly to do with constraints. +Databases such as Postgresql and MySQL with InnoDB will enforce referential +integrity (e.g. via foreign keys) in all cases. Unlike SQLite, it's not +as simple to turn off referential integrity across the board (nor would it +be desirable). Since a new table is replacing the old one, existing +foreign key constraints which refer to the target table will need to be +unconditionally dropped before the batch operation, and re-created to refer +to the new table afterwards. Batch mode currently does not provide any +automation for this. + +The Postgresql database and possibly others also have the behavior such +that when the new table is created, a naming conflict occurs with the +named constraints of the new table, in that they match those of the old +table, and on Postgresql, these names need to be unique across all tables. +The Postgresql dialect will therefore emit a "DROP CONSTRAINT" directive +for all constraints on the old table before the new one is created; this is +"safe" in case of a failed operation because Postgresql also supports +transactional DDL. + +Note that also as is the case with SQLite, CHECK constraints need to be +moved over between old and new table manually using the +:paramref:`.Operations.batch_alter_table.table_args` parameter. + |