1.. _alembic.autogenerate.toplevel: 2 3============== 4Autogeneration 5============== 6 7.. note:: this section discusses the **internal API of Alembic** 8 as regards the autogeneration feature of the ``alembic revision`` 9 command. 10 This section is only useful for developers who wish to extend the 11 capabilities of Alembic. For general documentation on the autogenerate 12 feature, please see :doc:`/autogenerate`. 13 14The autogeneration system has a wide degree of public API, including 15the following areas: 16 171. The ability to do a "diff" of a :class:`~sqlalchemy.schema.MetaData` object against 18 a database, and receive a data structure back. This structure 19 is available either as a rudimentary list of changes, or as 20 a :class:`.MigrateOperation` structure. 21 222. The ability to alter how the ``alembic revision`` command generates 23 revision scripts, including support for multiple revision scripts 24 generated in one pass. 25 263. The ability to add new operation directives to autogeneration, including 27 custom schema/model comparison functions and revision script rendering. 28 29Getting Diffs 30============== 31 32The simplest API autogenerate provides is the "schema comparison" API; 33these are simple functions that will run all registered "comparison" functions 34between a :class:`~sqlalchemy.schema.MetaData` object and a database 35backend to produce a structure showing how they differ. The two 36functions provided are :func:`.compare_metadata`, which is more of the 37"legacy" function that produces diff tuples, and :func:`.produce_migrations`, 38which produces a structure consisting of operation directives detailed in 39:ref:`alembic.operations.toplevel`. 40 41 42.. autofunction:: alembic.autogenerate.compare_metadata 43 44.. autofunction:: alembic.autogenerate.produce_migrations 45 46.. _customizing_revision: 47 48Customizing Revision Generation 49========================================== 50 51The ``alembic revision`` command, also available programmatically 52via :func:`.command.revision`, essentially produces a single migration 53script after being run. Whether or not the ``--autogenerate`` option 54was specified basically determines if this script is a blank revision 55script with empty ``upgrade()`` and ``downgrade()`` functions, or was 56produced with alembic operation directives as the result of autogenerate. 57 58In either case, the system creates a full plan of what is to be done 59in the form of a :class:`.MigrateOperation` structure, which is then 60used to produce the script. 61 62For example, suppose we ran ``alembic revision --autogenerate``, and the 63end result was that it produced a new revision ``'eced083f5df'`` 64with the following contents:: 65 66 """create the organization table.""" 67 68 # revision identifiers, used by Alembic. 69 revision = 'eced083f5df' 70 down_revision = 'beafc7d709f' 71 72 from alembic import op 73 import sqlalchemy as sa 74 75 76 def upgrade(): 77 op.create_table( 78 'organization', 79 sa.Column('id', sa.Integer(), primary_key=True), 80 sa.Column('name', sa.String(50), nullable=False) 81 ) 82 op.add_column( 83 'user', 84 sa.Column('organization_id', sa.Integer()) 85 ) 86 op.create_foreign_key( 87 'org_fk', 'user', 'organization', ['organization_id'], ['id'] 88 ) 89 90 def downgrade(): 91 op.drop_constraint('org_fk', 'user') 92 op.drop_column('user', 'organization_id') 93 op.drop_table('organization') 94 95The above script is generated by a :class:`.MigrateOperation` structure 96that looks like this:: 97 98 from alembic.operations import ops 99 import sqlalchemy as sa 100 101 migration_script = ops.MigrationScript( 102 'eced083f5df', 103 ops.UpgradeOps( 104 ops=[ 105 ops.CreateTableOp( 106 'organization', 107 [ 108 sa.Column('id', sa.Integer(), primary_key=True), 109 sa.Column('name', sa.String(50), nullable=False) 110 ] 111 ), 112 ops.ModifyTableOps( 113 'user', 114 ops=[ 115 ops.AddColumnOp( 116 'user', 117 sa.Column('organization_id', sa.Integer()) 118 ), 119 ops.CreateForeignKeyOp( 120 'org_fk', 'user', 'organization', 121 ['organization_id'], ['id'] 122 ) 123 ] 124 ) 125 ] 126 ), 127 ops.DowngradeOps( 128 ops=[ 129 ops.ModifyTableOps( 130 'user', 131 ops=[ 132 ops.DropConstraintOp('org_fk', 'user'), 133 ops.DropColumnOp('user', 'organization_id') 134 ] 135 ), 136 ops.DropTableOp('organization') 137 ] 138 ), 139 message='create the organization table.' 140 ) 141 142When we deal with a :class:`.MigrationScript` structure, we can render 143the upgrade/downgrade sections into strings for debugging purposes 144using the :func:`.render_python_code` helper function:: 145 146 from alembic.autogenerate import render_python_code 147 print(render_python_code(migration_script.upgrade_ops)) 148 149Renders:: 150 151 ### commands auto generated by Alembic - please adjust! ### 152 op.create_table('organization', 153 sa.Column('id', sa.Integer(), nullable=False), 154 sa.Column('name', sa.String(length=50), nullable=False), 155 sa.PrimaryKeyConstraint('id') 156 ) 157 op.add_column('user', sa.Column('organization_id', sa.Integer(), nullable=True)) 158 op.create_foreign_key('org_fk', 'user', 'organization', ['organization_id'], ['id']) 159 ### end Alembic commands ### 160 161Given that structures like the above are used to generate new revision 162files, and that we'd like to be able to alter these as they are created, 163we then need a system to access this structure when the 164:func:`.command.revision` command is used. The 165:paramref:`.EnvironmentContext.configure.process_revision_directives` 166parameter gives us a way to alter this. This is a function that 167is passed the above structure as generated by Alembic, giving us a chance 168to alter it. 169For example, if we wanted to put all the "upgrade" operations into 170a certain branch, and we wanted our script to not have any "downgrade" 171operations at all, we could build an extension as follows, illustrated 172within an ``env.py`` script:: 173 174 def process_revision_directives(context, revision, directives): 175 script = directives[0] 176 177 # set specific branch 178 script.head = "mybranch@head" 179 180 # erase downgrade operations 181 script.downgrade_ops.ops[:] = [] 182 183 # ... 184 185 def run_migrations_online(): 186 187 # ... 188 with engine.connect() as connection: 189 190 context.configure( 191 connection=connection, 192 target_metadata=target_metadata, 193 process_revision_directives=process_revision_directives) 194 195 with context.begin_transaction(): 196 context.run_migrations() 197 198Above, the ``directives`` argument is a Python list. We may alter the 199given structure within this list in-place, or replace it with a new 200structure consisting of zero or more :class:`.MigrationScript` directives. 201The :func:`.command.revision` command will then produce scripts corresponding 202to whatever is in this list. 203 204.. seealso:: 205 206 More examples of using :paramref:`.EnvironmentContext.configure.process_revision_directives` 207 208 :ref:`cookbook_no_empty_migrations` 209 210 :ref:`cookbook_dont_emit_drop_index` 211 212 :ref:`cookbook_custom_sorting_create_table` 213 214.. autofunction:: alembic.autogenerate.render_python_code 215 216.. _autogen_rewriter: 217 218Fine-Grained Autogenerate Generation with Rewriters 219--------------------------------------------------- 220 221The preceding example illustrated how we can make a simple change to the 222structure of the operation directives to produce new autogenerate output. 223For the case where we want to affect very specific parts of the autogenerate 224stream, we can make a function for 225:paramref:`.EnvironmentContext.configure.process_revision_directives` 226which traverses through the whole :class:`.MigrationScript` structure, locates 227the elements we care about and modifies them in-place as needed. However, 228to reduce the boilerplate associated with this task, we can use the 229:class:`.Rewriter` object to make this easier. :class:`.Rewriter` gives 230us an object that we can pass directly to 231:paramref:`.EnvironmentContext.configure.process_revision_directives` which 232we can also attach handler functions onto, keyed to specific types of 233constructs. 234 235Below is an example where we rewrite :class:`.ops.AddColumnOp` directives; 236based on whether or not the new column is "nullable", we either return 237the existing directive, or we return the existing directive with 238the nullable flag changed, inside of a list with a second directive 239to alter the nullable flag in a second step:: 240 241 # ... fragmented env.py script .... 242 243 from alembic.autogenerate import rewriter 244 from alembic.operations import ops 245 246 writer = rewriter.Rewriter() 247 248 @writer.rewrites(ops.AddColumnOp) 249 def add_column(context, revision, op): 250 if op.column.nullable: 251 return op 252 else: 253 op.column.nullable = True 254 return [ 255 op, 256 ops.AlterColumnOp( 257 op.table_name, 258 op.column.name, 259 modify_nullable=False, 260 existing_type=op.column.type, 261 ) 262 ] 263 264 # ... later ... 265 266 def run_migrations_online(): 267 # ... 268 269 with connectable.connect() as connection: 270 context.configure( 271 connection=connection, 272 target_metadata=target_metadata, 273 process_revision_directives=writer 274 ) 275 276 with context.begin_transaction(): 277 context.run_migrations() 278 279Above, in a full :class:`.ops.MigrationScript` structure, the 280:class:`.AddColumn` directives would be present within 281the paths ``MigrationScript->UpgradeOps->ModifyTableOps`` 282and ``MigrationScript->DowngradeOps->ModifyTableOps``. The 283:class:`.Rewriter` handles traversing into these structures as well 284as rewriting them as needed so that we only need to code for the specific 285object we care about. 286 287 288.. autoclass:: alembic.autogenerate.rewriter.Rewriter 289 :members: 290 291.. _autogen_customizing_multiengine_revision: 292 293Revision Generation with Multiple Engines / ``run_migrations()`` calls 294---------------------------------------------------------------------- 295 296A lesser-used technique which allows autogenerated migrations to run 297against multiple database backends at once, generating changes into 298a single migration script, is illustrated in the 299provided ``multidb`` template. This template features a special ``env.py`` 300which iterates through multiple :class:`~sqlalchemy.engine.Engine` instances 301and calls upon :meth:`.MigrationContext.run_migrations` for each:: 302 303 for name, rec in engines.items(): 304 logger.info("Migrating database %s" % name) 305 context.configure( 306 connection=rec['connection'], 307 upgrade_token="%s_upgrades" % name, 308 downgrade_token="%s_downgrades" % name, 309 target_metadata=target_metadata.get(name) 310 ) 311 context.run_migrations(engine_name=name) 312 313Above, :meth:`.MigrationContext.run_migrations` is run multiple times, 314once for each engine. Within the context of autogeneration, each time 315the method is called the :paramref:`~.EnvironmentContext.configure.upgrade_token` 316and :paramref:`~.EnvironmentContext.configure.downgrade_token` parameters 317are changed, so that the collection of template variables gains distinct 318entries for each engine, which are then referred to explicitly 319within ``script.py.mako``. 320 321In terms of the 322:paramref:`.EnvironmentContext.configure.process_revision_directives` hook, 323the behavior here is that the ``process_revision_directives`` hook 324is invoked **multiple times, once for each call to 325context.run_migrations()**. This means that if 326a multi-``run_migrations()`` approach is to be combined with the 327``process_revision_directives`` hook, care must be taken to use the 328hook appropriately. 329 330The first point to note is that when a **second** call to 331``run_migrations()`` occurs, the ``.upgrade_ops`` and ``.downgrade_ops`` 332attributes are **converted into Python lists**, and new 333:class:`.UpgradeOps` and :class:`.DowngradeOps` objects are appended 334to these lists. Each :class:`.UpgradeOps` and :class:`.DowngradeOps` 335object maintains an ``.upgrade_token`` and a ``.downgrade_token`` attribute 336respectively, which serves to render their contents into the appropriate 337template token. 338 339For example, a multi-engine run that has the engine names ``engine1`` 340and ``engine2`` will generate tokens of ``engine1_upgrades``, 341``engine1_downgrades``, ``engine2_upgrades`` and ``engine2_downgrades`` as 342it runs. The resulting migration structure would look like this:: 343 344 from alembic.operations import ops 345 import sqlalchemy as sa 346 347 migration_script = ops.MigrationScript( 348 'eced083f5df', 349 [ 350 ops.UpgradeOps( 351 ops=[ 352 # upgrade operations for "engine1" 353 ], 354 upgrade_token="engine1_upgrades" 355 ), 356 ops.UpgradeOps( 357 ops=[ 358 # upgrade operations for "engine2" 359 ], 360 upgrade_token="engine2_upgrades" 361 ), 362 ], 363 [ 364 ops.DowngradeOps( 365 ops=[ 366 # downgrade operations for "engine1" 367 ], 368 downgrade_token="engine1_downgrades" 369 ), 370 ops.DowngradeOps( 371 ops=[ 372 # downgrade operations for "engine2" 373 ], 374 downgrade_token="engine2_downgrades" 375 ) 376 ], 377 message='migration message' 378 ) 379 380 381Given the above, the following guidelines should be considered when 382the ``env.py`` script calls upon :meth:`.MigrationContext.run_migrations` 383multiple times when running autogenerate: 384 385* If the ``process_revision_directives`` hook aims to **add elements 386 based on inspection of the current database / 387 connection**, it should do its operation **on each iteration**. This is 388 so that each time the hook runs, the database is available. 389 390* Alternatively, if the ``process_revision_directives`` hook aims to 391 **modify the list of migration directives in place**, this should 392 be called **only on the last iteration**. This is so that the hook 393 isn't being given an ever-growing structure each time which it has already 394 modified previously. 395 396* The :class:`.Rewriter` object, if used, should be called **only on the 397 last iteration**, because it will always deliver all directives every time, 398 so again to avoid double/triple/etc. processing of directives it should 399 be called only when the structure is complete. 400 401* The :attr:`.MigrationScript.upgrade_ops_list` and 402 :attr:`.MigrationScript.downgrade_ops_list` attributes should be consulted 403 when referring to the collection of :class:`.UpgradeOps` and 404 :class:`.DowngradeOps` objects. 405 406.. _autogen_custom_ops: 407 408Autogenerating Custom Operation Directives 409========================================== 410 411In the section :ref:`operation_plugins`, we talked about adding new 412subclasses of :class:`.MigrateOperation` in order to add new ``op.`` 413directives. In the preceding section :ref:`customizing_revision`, we 414also learned that these same :class:`.MigrateOperation` structures are at 415the base of how the autogenerate system knows what Python code to render. 416Using this knowledge, we can create additional functions that plug into 417the autogenerate system so that our new operations can be generated 418into migration scripts when ``alembic revision --autogenerate`` is run. 419 420The following sections will detail an example of this using the 421the ``CreateSequenceOp`` and ``DropSequenceOp`` directives 422we created in :ref:`operation_plugins`, which correspond to the 423SQLAlchemy :class:`~sqlalchemy.schema.Sequence` construct. 424 425Tracking our Object with the Model 426---------------------------------- 427 428The basic job of an autogenerate comparison function is to inspect 429a series of objects in the database and compare them against a series 430of objects defined in our model. By "in our model", we mean anything 431defined in Python code that we want to track, however most commonly 432we're talking about a series of :class:`~sqlalchemy.schema.Table` 433objects present in a :class:`~sqlalchemy.schema.MetaData` collection. 434 435Let's propose a simple way of seeing what :class:`~sqlalchemy.schema.Sequence` 436objects we want to ensure exist in the database when autogenerate 437runs. While these objects do have some integrations with 438:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.MetaData` 439already, let's assume they don't, as the example here intends to illustrate 440how we would do this for most any kind of custom construct. We 441associate the object with the :attr:`~sqlalchemy.schema.MetaData.info` 442collection of :class:`~sqlalchemy.schema.MetaData`, which is a dictionary 443we can use for anything, which we also know will be passed to the autogenerate 444process:: 445 446 from sqlalchemy.schema import Sequence 447 448 def add_sequence_to_model(sequence, metadata): 449 metadata.info.setdefault("sequences", set()).add( 450 (sequence.schema, sequence.name) 451 ) 452 453 my_seq = Sequence("my_sequence") 454 add_sequence_to_model(my_seq, model_metadata) 455 456The :attr:`~sqlalchemy.schema.MetaData.info` 457dictionary is a good place to put things that we want our autogeneration 458routines to be able to locate, which can include any object such as 459custom DDL objects representing views, triggers, special constraints, 460or anything else we want to support. 461 462 463Registering a Comparison Function 464--------------------------------- 465 466We now need to register a comparison hook, which will be used 467to compare the database to our model and produce ``CreateSequenceOp`` 468and ``DropSequenceOp`` directives to be included in our migration 469script. Note that we are assuming a 470Postgresql backend:: 471 472 from alembic.autogenerate import comparators 473 474 @comparators.dispatch_for("schema") 475 def compare_sequences(autogen_context, upgrade_ops, schemas): 476 all_conn_sequences = set() 477 478 for sch in schemas: 479 480 all_conn_sequences.update([ 481 (sch, row[0]) for row in 482 autogen_context.connection.execute( 483 "SELECT relname FROM pg_class c join " 484 "pg_namespace n on n.oid=c.relnamespace where " 485 "relkind='S' and n.nspname=%(nspname)s", 486 487 # note that we consider a schema of 'None' in our 488 # model to be the "default" name in the PG database; 489 # this usually is the name 'public' 490 nspname=autogen_context.dialect.default_schema_name 491 if sch is None else sch 492 ) 493 ]) 494 495 # get the collection of Sequence objects we're storing with 496 # our MetaData 497 metadata_sequences = autogen_context.metadata.info.setdefault( 498 "sequences", set()) 499 500 # for new names, produce CreateSequenceOp directives 501 for sch, name in metadata_sequences.difference(all_conn_sequences): 502 upgrade_ops.ops.append( 503 CreateSequenceOp(name, schema=sch) 504 ) 505 506 # for names that are going away, produce DropSequenceOp 507 # directives 508 for sch, name in all_conn_sequences.difference(metadata_sequences): 509 upgrade_ops.ops.append( 510 DropSequenceOp(name, schema=sch) 511 ) 512 513Above, we've built a new function ``compare_sequences()`` and registered 514it as a "schema" level comparison function with autogenerate. The 515job that it performs is that it compares the list of sequence names 516present in each database schema with that of a list of sequence names 517that we are maintaining in our :class:`~sqlalchemy.schema.MetaData` object. 518 519When autogenerate completes, it will have a series of 520``CreateSequenceOp`` and ``DropSequenceOp`` directives in the list of 521"upgrade" operations; the list of "downgrade" operations is generated 522directly from these using the 523``CreateSequenceOp.reverse()`` and ``DropSequenceOp.reverse()`` methods 524that we've implemented on these objects. 525 526The registration of our function at the scope of "schema" means our 527autogenerate comparison function is called outside of the context 528of any specific table or column. The three available scopes 529are "schema", "table", and "column", summarized as follows: 530 531* **Schema level** - these hooks are passed a :class:`.AutogenContext`, 532 an :class:`.UpgradeOps` collection, and a collection of string schema 533 names to be operated upon. If the 534 :class:`.UpgradeOps` collection contains changes after all 535 hooks are run, it is included in the migration script: 536 537 :: 538 539 @comparators.dispatch_for("schema") 540 def compare_schema_level(autogen_context, upgrade_ops, schemas): 541 pass 542 543* **Table level** - these hooks are passed a :class:`.AutogenContext`, 544 a :class:`.ModifyTableOps` collection, a schema name, table name, 545 a :class:`~sqlalchemy.schema.Table` reflected from the database if any 546 or ``None``, and a :class:`~sqlalchemy.schema.Table` present in the 547 local :class:`~sqlalchemy.schema.MetaData`. If the 548 :class:`.ModifyTableOps` collection contains changes after all 549 hooks are run, it is included in the migration script: 550 551 :: 552 553 @comparators.dispatch_for("table") 554 def compare_table_level(autogen_context, modify_ops, 555 schemaname, tablename, conn_table, metadata_table): 556 pass 557 558* **Column level** - these hooks are passed a :class:`.AutogenContext`, 559 an :class:`.AlterColumnOp` object, a schema name, table name, 560 column name, a :class:`~sqlalchemy.schema.Column` reflected from the 561 database and a :class:`~sqlalchemy.schema.Column` present in the 562 local table. If the :class:`.AlterColumnOp` contains changes after 563 all hooks are run, it is included in the migration script; 564 a "change" is considered to be present if any of the ``modify_`` attributes 565 are set to a non-default value, or there are any keys 566 in the ``.kw`` collection with the prefix ``"modify_"``: 567 568 :: 569 570 @comparators.dispatch_for("column") 571 def compare_column_level(autogen_context, alter_column_op, 572 schemaname, tname, cname, conn_col, metadata_col): 573 pass 574 575The :class:`.AutogenContext` passed to these hooks is documented below. 576 577.. autoclass:: alembic.autogenerate.api.AutogenContext 578 :members: 579 580Creating a Render Function 581-------------------------- 582 583The second autogenerate integration hook is to provide a "render" function; 584since the autogenerate 585system renders Python code, we need to build a function that renders 586the correct "op" instructions for our directive:: 587 588 from alembic.autogenerate import renderers 589 590 @renderers.dispatch_for(CreateSequenceOp) 591 def render_create_sequence(autogen_context, op): 592 return "op.create_sequence(%r, **%r)" % ( 593 op.sequence_name, 594 {"schema": op.schema} 595 ) 596 597 598 @renderers.dispatch_for(DropSequenceOp) 599 def render_drop_sequence(autogen_context, op): 600 return "op.drop_sequence(%r, **%r)" % ( 601 op.sequence_name, 602 {"schema": op.schema} 603 ) 604 605The above functions will render Python code corresponding to the 606presence of ``CreateSequenceOp`` and ``DropSequenceOp`` instructions 607in the list that our comparison function generates. 608 609Running It 610---------- 611 612All the above code can be organized however the developer sees fit; 613the only thing that needs to make it work is that when the 614Alembic environment ``env.py`` is invoked, it either imports modules 615which contain all the above routines, or they are locally present, 616or some combination thereof. 617 618If we then have code in our model (which of course also needs to be invoked 619when ``env.py`` runs!) like this:: 620 621 from sqlalchemy.schema import Sequence 622 623 my_seq_1 = Sequence("my_sequence_1") 624 add_sequence_to_model(my_seq_1, target_metadata) 625 626When we first run ``alembic revision --autogenerate``, we'll see this 627in our migration file:: 628 629 def upgrade(): 630 ### commands auto generated by Alembic - please adjust! ### 631 op.create_sequence('my_sequence_1', **{'schema': None}) 632 ### end Alembic commands ### 633 634 635 def downgrade(): 636 ### commands auto generated by Alembic - please adjust! ### 637 op.drop_sequence('my_sequence_1', **{'schema': None}) 638 ### end Alembic commands ### 639 640These are our custom directives that will invoke when ``alembic upgrade`` 641or ``alembic downgrade`` is run. 642 643