/usr/share/doc/alembic/html/cookbook.html is in alembic 0.9.3-2ubuntu1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 | <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Cookbook — Alembic 0.9.3 documentation</title>
<link rel="stylesheet" href="_static/nature_override.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="_static/changelog.css" type="text/css" />
<link rel="stylesheet" href="_static/sphinx_paramlinks.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: './',
VERSION: '0.9.3',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true,
SOURCELINK_SUFFIX: '.txt'
};
</script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<link rel="index" title="Index" href="genindex.html" />
<link rel="search" title="Search" href="search.html" />
<link rel="next" title="API Details" href="api/index.html" />
<link rel="prev" title="Operation Reference" href="ops.html" />
</head>
<body>
<div class="related" role="navigation" aria-label="related navigation">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="py-modindex.html" title="Python Module Index"
>modules</a> |</li>
<li class="right" >
<a href="api/index.html" title="API Details"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="ops.html" title="Operation Reference"
accesskey="P">previous</a> |</li>
<li class="nav-item nav-item-0"><a href="index.html">Alembic 0.9.3 documentation</a> »</li>
</ul>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body" role="main">
<div class="section" id="cookbook">
<h1>Cookbook<a class="headerlink" href="#cookbook" title="Permalink to this headline">¶</a></h1>
<p>A collection of “How-Tos” highlighting popular ways to extend
Alembic.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">This is a new section where we catalogue various “how-tos”
based on user requests. It is often the case that users
will request a feature only to learn it can be provided with
a simple customization.</p>
</div>
<div class="section" id="building-an-up-to-date-database-from-scratch">
<span id="building-uptodate"></span><h2>Building an Up to Date Database from Scratch<a class="headerlink" href="#building-an-up-to-date-database-from-scratch" title="Permalink to this headline">¶</a></h2>
<p>There’s a theory of database migrations that says that the revisions in existence for a database should be
able to go from an entirely blank schema to the finished product, and back again. Alembic can roll
this way. Though we think it’s kind of overkill, considering that SQLAlchemy itself can emit
the full CREATE statements for any given model using <code class="xref py py-meth docutils literal"><span class="pre">create_all()</span></code>. If you check out
a copy of an application, running this will give you the entire database in one shot, without the need
to run through all those migration files, which are instead tailored towards applying incremental
changes to an existing database.</p>
<p>Alembic can integrate with a <code class="xref py py-meth docutils literal"><span class="pre">create_all()</span></code> script quite easily. After running the
create operation, tell Alembic to create a new version table, and to stamp it with the most recent
revision (i.e. <code class="docutils literal"><span class="pre">head</span></code>):</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="c1"># inside of a "create the database" script, first create</span>
<span class="c1"># tables:</span>
<span class="n">my_metadata</span><span class="o">.</span><span class="n">create_all</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="c1"># then, load the Alembic configuration and generate the</span>
<span class="c1"># version table, "stamping" it with the most recent rev:</span>
<span class="kn">from</span> <span class="nn">alembic.config</span> <span class="k">import</span> <span class="n">Config</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">command</span>
<span class="n">alembic_cfg</span> <span class="o">=</span> <span class="n">Config</span><span class="p">(</span><span class="s2">"/path/to/yourapp/alembic.ini"</span><span class="p">)</span>
<span class="n">command</span><span class="o">.</span><span class="n">stamp</span><span class="p">(</span><span class="n">alembic_cfg</span><span class="p">,</span> <span class="s2">"head"</span><span class="p">)</span>
</pre></div>
</div>
<p>When this approach is used, the application can generate the database using normal SQLAlchemy
techniques instead of iterating through hundreds of migration scripts. Now, the purpose of the
migration scripts is relegated just to movement between versions on out-of-date databases, not
<em>new</em> databases. You can now remove old migration files that are no longer represented
on any existing environments.</p>
<p>To prune old migration files, simply delete the files. Then, in the earliest, still-remaining
migration file, set <code class="docutils literal"><span class="pre">down_revision</span></code> to <code class="docutils literal"><span class="pre">None</span></code>:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="c1"># replace this:</span>
<span class="c1">#down_revision = '290696571ad2'</span>
<span class="c1"># with this:</span>
<span class="n">down_revision</span> <span class="o">=</span> <span class="kc">None</span>
</pre></div>
</div>
<p>That file now becomes the “base” of the migration series.</p>
</div>
<div class="section" id="conditional-migration-elements">
<h2>Conditional Migration Elements<a class="headerlink" href="#conditional-migration-elements" title="Permalink to this headline">¶</a></h2>
<p>This example features the basic idea of a common need, that of affecting
how a migration runs based on command line switches.</p>
<p>The technique to use here is simple; within a migration script, inspect
the <a class="reference internal" href="api/runtime.html#alembic.runtime.environment.EnvironmentContext.get_x_argument" title="alembic.runtime.environment.EnvironmentContext.get_x_argument"><code class="xref py py-meth docutils literal"><span class="pre">EnvironmentContext.get_x_argument()</span></code></a> collection for any additional,
user-defined parameters. Then take action based on the presence of those
arguments.</p>
<p>To make it such that the logic to inspect these flags is easy to use and
modify, we modify our <code class="docutils literal"><span class="pre">script.py.mako</span></code> template to make this feature
available in all new revision files:</p>
<div class="highlight-mako"><div class="highlight"><pre><span></span><span class="x">"""</span><span class="cp">${</span><span class="n">message</span><span class="cp">}</span><span class="x"></span>
<span class="x">Revision ID: </span><span class="cp">${</span><span class="n">up_revision</span><span class="cp">}</span><span class="x"></span>
<span class="x">Revises: </span><span class="cp">${</span><span class="n">down_revision</span><span class="cp">}</span><span class="x"></span>
<span class="x">Create Date: </span><span class="cp">${</span><span class="n">create_date</span><span class="cp">}</span><span class="x"></span>
<span class="x">"""</span>
<span class="x"># revision identifiers, used by Alembic.</span>
<span class="x">revision = </span><span class="cp">${</span><span class="nb">repr</span><span class="p">(</span><span class="n">up_revision</span><span class="p">)</span><span class="cp">}</span><span class="x"></span>
<span class="x">down_revision = </span><span class="cp">${</span><span class="nb">repr</span><span class="p">(</span><span class="n">down_revision</span><span class="p">)</span><span class="cp">}</span><span class="x"></span>
<span class="x">from alembic import op</span>
<span class="x">import sqlalchemy as sa</span>
<span class="cp">${</span><span class="n">imports</span> <span class="k">if</span> <span class="n">imports</span> <span class="k">else</span> <span class="s2">""</span><span class="cp">}</span><span class="x"></span>
<span class="x">from alembic import context</span>
<span class="x">def upgrade():</span>
<span class="x"> schema_upgrades()</span>
<span class="x"> if context.get_x_argument(as_dictionary=True).get('data', None):</span>
<span class="x"> data_upgrades()</span>
<span class="x">def downgrade():</span>
<span class="x"> if context.get_x_argument(as_dictionary=True).get('data', None):</span>
<span class="x"> data_downgrades()</span>
<span class="x"> schema_downgrades()</span>
<span class="x">def schema_upgrades():</span>
<span class="x"> """schema upgrade migrations go here."""</span>
<span class="x"> </span><span class="cp">${</span><span class="n">upgrades</span> <span class="k">if</span> <span class="n">upgrades</span> <span class="k">else</span> <span class="s2">"pass"</span><span class="cp">}</span><span class="x"></span>
<span class="x">def schema_downgrades():</span>
<span class="x"> """schema downgrade migrations go here."""</span>
<span class="x"> </span><span class="cp">${</span><span class="n">downgrades</span> <span class="k">if</span> <span class="n">downgrades</span> <span class="k">else</span> <span class="s2">"pass"</span><span class="cp">}</span><span class="x"></span>
<span class="x">def data_upgrades():</span>
<span class="x"> """Add any optional data upgrade migrations here!"""</span>
<span class="x"> pass</span>
<span class="x">def data_downgrades():</span>
<span class="x"> """Add any optional data downgrade migrations here!"""</span>
<span class="x"> pass</span>
</pre></div>
</div>
<p>Now, when we create a new migration file, the <code class="docutils literal"><span class="pre">data_upgrades()</span></code> and <code class="docutils literal"><span class="pre">data_downgrades()</span></code>
placeholders will be available, where we can add optional data migrations:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="sd">"""rev one</span>
<span class="sd">Revision ID: 3ba2b522d10d</span>
<span class="sd">Revises: None</span>
<span class="sd">Create Date: 2014-03-04 18:05:36.992867</span>
<span class="sd">"""</span>
<span class="c1"># revision identifiers, used by Alembic.</span>
<span class="n">revision</span> <span class="o">=</span> <span class="s1">'3ba2b522d10d'</span>
<span class="n">down_revision</span> <span class="o">=</span> <span class="kc">None</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">op</span>
<span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
<span class="kn">from</span> <span class="nn">sqlalchemy.sql</span> <span class="k">import</span> <span class="n">table</span><span class="p">,</span> <span class="n">column</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">context</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span>
<span class="n">schema_upgrades</span><span class="p">()</span>
<span class="k">if</span> <span class="n">context</span><span class="o">.</span><span class="n">get_x_argument</span><span class="p">(</span><span class="n">as_dictionary</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s1">'data'</span><span class="p">,</span> <span class="kc">None</span><span class="p">):</span>
<span class="n">data_upgrades</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span>
<span class="k">if</span> <span class="n">context</span><span class="o">.</span><span class="n">get_x_argument</span><span class="p">(</span><span class="n">as_dictionary</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s1">'data'</span><span class="p">,</span> <span class="kc">None</span><span class="p">):</span>
<span class="n">data_downgrades</span><span class="p">()</span>
<span class="n">schema_downgrades</span><span class="p">()</span>
<span class="k">def</span> <span class="nf">schema_upgrades</span><span class="p">():</span>
<span class="sd">"""schema upgrade migrations go here."""</span>
<span class="n">op</span><span class="o">.</span><span class="n">create_table</span><span class="p">(</span><span class="s2">"my_table"</span><span class="p">,</span> <span class="n">Column</span><span class="p">(</span><span class="s1">'data'</span><span class="p">,</span> <span class="n">String</span><span class="p">))</span>
<span class="k">def</span> <span class="nf">schema_downgrades</span><span class="p">():</span>
<span class="sd">"""schema downgrade migrations go here."""</span>
<span class="n">op</span><span class="o">.</span><span class="n">drop_table</span><span class="p">(</span><span class="s2">"my_table"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">data_upgrades</span><span class="p">():</span>
<span class="sd">"""Add any optional data upgrade migrations here!"""</span>
<span class="n">my_table</span> <span class="o">=</span> <span class="n">table</span><span class="p">(</span><span class="s1">'my_table'</span><span class="p">,</span>
<span class="n">column</span><span class="p">(</span><span class="s1">'data'</span><span class="p">,</span> <span class="n">String</span><span class="p">),</span>
<span class="p">)</span>
<span class="n">op</span><span class="o">.</span><span class="n">bulk_insert</span><span class="p">(</span><span class="n">my_table</span><span class="p">,</span>
<span class="p">[</span>
<span class="p">{</span><span class="s1">'data'</span><span class="p">:</span> <span class="s1">'data 1'</span><span class="p">},</span>
<span class="p">{</span><span class="s1">'data'</span><span class="p">:</span> <span class="s1">'data 2'</span><span class="p">},</span>
<span class="p">{</span><span class="s1">'data'</span><span class="p">:</span> <span class="s1">'data 3'</span><span class="p">},</span>
<span class="p">]</span>
<span class="p">)</span>
<span class="k">def</span> <span class="nf">data_downgrades</span><span class="p">():</span>
<span class="sd">"""Add any optional data downgrade migrations here!"""</span>
<span class="n">op</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"delete from my_table"</span><span class="p">)</span>
</pre></div>
</div>
<p>To invoke our migrations with data included, we use the <code class="docutils literal"><span class="pre">-x</span></code> flag:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="n">alembic</span> <span class="o">-</span><span class="n">x</span> <span class="n">data</span><span class="o">=</span><span class="n">true</span> <span class="n">upgrade</span> <span class="n">head</span>
</pre></div>
</div>
<p>The <a class="reference internal" href="api/runtime.html#alembic.runtime.environment.EnvironmentContext.get_x_argument" title="alembic.runtime.environment.EnvironmentContext.get_x_argument"><code class="xref py py-meth docutils literal"><span class="pre">EnvironmentContext.get_x_argument()</span></code></a> is an easy way to support
new commandline options within environment and migration scripts.</p>
</div>
<div class="section" id="sharing-a-connection-with-a-series-of-migration-commands-and-environments">
<span id="connection-sharing"></span><h2>Sharing a Connection with a Series of Migration Commands and Environments<a class="headerlink" href="#sharing-a-connection-with-a-series-of-migration-commands-and-environments" title="Permalink to this headline">¶</a></h2>
<p>It is often the case that an application will need to call upon a series
of commands within <a class="reference internal" href="api/commands.html#alembic-command-toplevel"><span class="std std-ref">Commands</span></a>, where it would be advantageous
for all operations to proceed along a single transaction. The connectivity
for a migration is typically solely determined within the <code class="docutils literal"><span class="pre">env.py</span></code> script
of a migration environment, which is called within the scope of a command.</p>
<p>The steps to take here are:</p>
<ol class="arabic simple">
<li>Produce the <code class="xref py py-class docutils literal"><span class="pre">Connection</span></code> object to use.</li>
<li>Place it somewhere that <code class="docutils literal"><span class="pre">env.py</span></code> will be able to access it. This
can be either a. a module-level global somewhere, or b.
an attribute which we place into the <a class="reference internal" href="api/config.html#alembic.config.Config.attributes" title="alembic.config.Config.attributes"><code class="xref py py-attr docutils literal"><span class="pre">Config.attributes</span></code></a>
dictionary (if we are on an older Alembic version, we may also attach
an attribute directly to the <a class="reference internal" href="api/config.html#alembic.config.Config" title="alembic.config.Config"><code class="xref py py-class docutils literal"><span class="pre">Config</span></code></a> object).</li>
<li>The <code class="docutils literal"><span class="pre">env.py</span></code> script is modified such that it looks for this
<code class="xref py py-class docutils literal"><span class="pre">Connection</span></code> and makes use of it, in lieu
of building up its own <code class="xref py py-class docutils literal"><span class="pre">Engine</span></code> instance.</li>
</ol>
<p>We illustrate using <a class="reference internal" href="api/config.html#alembic.config.Config.attributes" title="alembic.config.Config.attributes"><code class="xref py py-attr docutils literal"><span class="pre">Config.attributes</span></code></a>:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">command</span><span class="p">,</span> <span class="n">config</span>
<span class="n">cfg</span> <span class="o">=</span> <span class="n">config</span><span class="o">.</span><span class="n">Config</span><span class="p">(</span><span class="s2">"/path/to/yourapp/alembic.ini"</span><span class="p">)</span>
<span class="k">with</span> <span class="n">engine</span><span class="o">.</span><span class="n">begin</span><span class="p">()</span> <span class="k">as</span> <span class="n">connection</span><span class="p">:</span>
<span class="n">cfg</span><span class="o">.</span><span class="n">attributes</span><span class="p">[</span><span class="s1">'connection'</span><span class="p">]</span> <span class="o">=</span> <span class="n">connection</span>
<span class="n">command</span><span class="o">.</span><span class="n">upgrade</span><span class="p">(</span><span class="n">cfg</span><span class="p">,</span> <span class="s2">"head"</span><span class="p">)</span>
</pre></div>
</div>
<p>Then in <code class="docutils literal"><span class="pre">env.py</span></code>:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">run_migrations_online</span><span class="p">():</span>
<span class="n">connectable</span> <span class="o">=</span> <span class="n">config</span><span class="o">.</span><span class="n">attributes</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s1">'connection'</span><span class="p">,</span> <span class="kc">None</span><span class="p">)</span>
<span class="k">if</span> <span class="n">connectable</span> <span class="ow">is</span> <span class="kc">None</span><span class="p">:</span>
<span class="c1"># only create Engine if we don't have a Connection</span>
<span class="c1"># from the outside</span>
<span class="n">connectable</span> <span class="o">=</span> <span class="n">engine_from_config</span><span class="p">(</span>
<span class="n">config</span><span class="o">.</span><span class="n">get_section</span><span class="p">(</span><span class="n">config</span><span class="o">.</span><span class="n">config_ini_section</span><span class="p">),</span>
<span class="n">prefix</span><span class="o">=</span><span class="s1">'sqlalchemy.'</span><span class="p">,</span>
<span class="n">poolclass</span><span class="o">=</span><span class="n">pool</span><span class="o">.</span><span class="n">NullPool</span><span class="p">)</span>
<span class="c1"># when connectable is already a Connection object, calling</span>
<span class="c1"># connect() gives us a *branched connection*.</span>
<span class="k">with</span> <span class="n">connectable</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">connection</span><span class="p">:</span>
<span class="n">context</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span>
<span class="n">connection</span><span class="o">=</span><span class="n">connection</span><span class="p">,</span>
<span class="n">target_metadata</span><span class="o">=</span><span class="n">target_metadata</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">context</span><span class="o">.</span><span class="n">begin_transaction</span><span class="p">():</span>
<span class="n">context</span><span class="o">.</span><span class="n">run_migrations</span><span class="p">()</span>
</pre></div>
</div>
<div class="topic">
<p class="topic-title first">Branched Connections</p>
<p>Note that we are calling the <code class="docutils literal"><span class="pre">connect()</span></code> method, <strong>even if we are
using a</strong> <code class="xref py py-class docutils literal"><span class="pre">Connection</span></code> <strong>object to start with</strong>.
The effect this has when calling <code class="xref py py-meth docutils literal"><span class="pre">connect()</span></code>
is that SQLAlchemy passes us a <strong>branch</strong> of the original connection; it
is in every way the same as the <code class="xref py py-class docutils literal"><span class="pre">Connection</span></code>
we started with, except it provides <strong>nested scope</strong>; the
context we have here as well as the
<code class="xref py py-meth docutils literal"><span class="pre">close()</span></code> method of this branched
connection doesn’t actually close the outer connection, which stays
active for continued use.</p>
</div>
<div class="versionadded">
<p><span class="versionmodified">New in version 0.7.5: </span>Added <a class="reference internal" href="api/config.html#alembic.config.Config.attributes" title="alembic.config.Config.attributes"><code class="xref py py-attr docutils literal"><span class="pre">Config.attributes</span></code></a>.</p>
</div>
</div>
<div class="section" id="replaceable-objects">
<span id="id1"></span><h2>Replaceable Objects<a class="headerlink" href="#replaceable-objects" title="Permalink to this headline">¶</a></h2>
<p>This recipe proposes a hypothetical way of dealing with
what we might call a <em>replaceable</em> schema object. A replaceable object
is a schema object that needs to be created and dropped all at once.
Examples of such objects include views, stored procedures, and triggers.</p>
<p>Replaceable objects present a problem in that in order to make incremental
changes to them, we have to refer to the whole definition at once.
If we need to add a new column to a view, for example, we have to drop
it entirely and recreate it fresh with the extra column added, referring to
the whole structure; but to make it even tougher, if we wish to support
downgrade operarations in our migration scripts,
we need to refer to the <em>previous</em> version of that
construct fully, and we’d much rather not have to type out the whole
definition in multiple places.</p>
<p>This recipe proposes that we may refer to the older version of a
replaceable construct by directly naming the migration version in
which it was created, and having a migration refer to that previous
file as migrations run. We will also demonstrate how to integrate this
logic within the <a class="reference internal" href="api/operations.html#operation-plugins"><span class="std std-ref">Operation Plugins</span></a> feature introduced in
Alembic 0.8. It may be very helpful to review
this section first to get an overview of this API.</p>
<div class="section" id="the-replaceable-object-structure">
<h3>The Replaceable Object Structure<a class="headerlink" href="#the-replaceable-object-structure" title="Permalink to this headline">¶</a></h3>
<p>We first need to devise a simple format that represents the “CREATE XYZ” /
“DROP XYZ” aspect of what it is we’re building. We will work with an object
that represents a textual definition; while a SQL view is an object that we can define
using a <a class="reference external" href="https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views">table-metadata-like system</a>,
this is not so much the case for things like stored procedures, where
we pretty much need to have a full string definition written down somewhere.
We’ll use a simple value object called <code class="docutils literal"><span class="pre">ReplaceableObject</span></code> that can
represent any named set of SQL text to send to a “CREATE” statement of
some kind:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="k">class</span> <span class="nc">ReplaceableObject</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">sqltext</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="bp">self</span><span class="o">.</span><span class="n">sqltext</span> <span class="o">=</span> <span class="n">sqltext</span>
</pre></div>
</div>
<p>Using this object in a migration script, assuming a Postgresql-style
syntax, looks like:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="n">customer_view</span> <span class="o">=</span> <span class="n">ReplaceableObject</span><span class="p">(</span>
<span class="s2">"customer_view"</span><span class="p">,</span>
<span class="s2">"SELECT name, order_count FROM customer WHERE order_count > 0"</span>
<span class="p">)</span>
<span class="n">add_customer_sp</span> <span class="o">=</span> <span class="n">ReplaceableObject</span><span class="p">(</span>
<span class="s2">"add_customer_sp(name varchar, order_count integer)"</span><span class="p">,</span>
<span class="sd">"""</span>
<span class="sd"> RETURNS integer AS $$</span>
<span class="sd"> BEGIN</span>
<span class="sd"> insert into customer (name, order_count)</span>
<span class="sd"> VALUES (in_name, in_order_count);</span>
<span class="sd"> END;</span>
<span class="sd"> $$ LANGUAGE plpgsql;</span>
<span class="sd"> """</span>
<span class="p">)</span>
</pre></div>
</div>
<p>The <code class="docutils literal"><span class="pre">ReplaceableObject</span></code> class is only one very simplistic way to do this.
The structure of how we represent our schema objects
is not too important for the purposes of this example; we can just
as well put strings inside of tuples or dictionaries, as well as
that we could define any kind of series of fields and class structures we want.
The only important part is that below we will illustrate how organize the
code that can consume the structure we create here.</p>
</div>
<div class="section" id="create-operations-for-the-target-objects">
<h3>Create Operations for the Target Objects<a class="headerlink" href="#create-operations-for-the-target-objects" title="Permalink to this headline">¶</a></h3>
<p>We’ll use the <a class="reference internal" href="ops.html#alembic.operations.Operations" title="alembic.operations.Operations"><code class="xref py py-class docutils literal"><span class="pre">Operations</span></code></a> extension API to make new operations
for create, drop, and replace of views and stored procedures. Using this
API is also optional; we can just as well make any kind of Python
function that we would invoke from our migration scripts.
However, using this API gives us operations
built directly into the Alembic <code class="docutils literal"><span class="pre">op.*</span></code> namespace very nicely.</p>
<p>The most intricate class is below. This is the base of our “replaceable”
operation, which includes not just a base operation for emitting
CREATE and DROP instructions on a <code class="docutils literal"><span class="pre">ReplaceableObject</span></code>, it also assumes
a certain model of “reversibility” which makes use of references to
other migration files in order to refer to the “previous” version
of an object:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">alembic.operations</span> <span class="k">import</span> <span class="n">Operations</span><span class="p">,</span> <span class="n">MigrateOperation</span>
<span class="k">class</span> <span class="nc">ReversibleOp</span><span class="p">(</span><span class="n">MigrateOperation</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">target</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">target</span> <span class="o">=</span> <span class="n">target</span>
<span class="nd">@classmethod</span>
<span class="k">def</span> <span class="nf">invoke_for_target</span><span class="p">(</span><span class="bp">cls</span><span class="p">,</span> <span class="n">operations</span><span class="p">,</span> <span class="n">target</span><span class="p">):</span>
<span class="n">op</span> <span class="o">=</span> <span class="bp">cls</span><span class="p">(</span><span class="n">target</span><span class="p">)</span>
<span class="k">return</span> <span class="n">operations</span><span class="o">.</span><span class="n">invoke</span><span class="p">(</span><span class="n">op</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">reverse</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">raise</span> <span class="ne">NotImplementedError</span><span class="p">()</span>
<span class="nd">@classmethod</span>
<span class="k">def</span> <span class="nf">_get_object_from_version</span><span class="p">(</span><span class="bp">cls</span><span class="p">,</span> <span class="n">operations</span><span class="p">,</span> <span class="n">ident</span><span class="p">):</span>
<span class="n">version</span><span class="p">,</span> <span class="n">objname</span> <span class="o">=</span> <span class="n">ident</span><span class="o">.</span><span class="n">split</span><span class="p">(</span><span class="s2">"."</span><span class="p">)</span>
<span class="n">module</span> <span class="o">=</span> <span class="n">operations</span><span class="o">.</span><span class="n">get_context</span><span class="p">()</span><span class="o">.</span><span class="n">script</span><span class="o">.</span><span class="n">get_revision</span><span class="p">(</span><span class="n">version</span><span class="p">)</span><span class="o">.</span><span class="n">module</span>
<span class="n">obj</span> <span class="o">=</span> <span class="nb">getattr</span><span class="p">(</span><span class="n">module</span><span class="p">,</span> <span class="n">objname</span><span class="p">)</span>
<span class="k">return</span> <span class="n">obj</span>
<span class="nd">@classmethod</span>
<span class="k">def</span> <span class="nf">replace</span><span class="p">(</span><span class="bp">cls</span><span class="p">,</span> <span class="n">operations</span><span class="p">,</span> <span class="n">target</span><span class="p">,</span> <span class="n">replaces</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span> <span class="n">replace_with</span><span class="o">=</span><span class="kc">None</span><span class="p">):</span>
<span class="k">if</span> <span class="n">replaces</span><span class="p">:</span>
<span class="n">old_obj</span> <span class="o">=</span> <span class="bp">cls</span><span class="o">.</span><span class="n">_get_object_from_version</span><span class="p">(</span><span class="n">operations</span><span class="p">,</span> <span class="n">replaces</span><span class="p">)</span>
<span class="n">drop_old</span> <span class="o">=</span> <span class="bp">cls</span><span class="p">(</span><span class="n">old_obj</span><span class="p">)</span><span class="o">.</span><span class="n">reverse</span><span class="p">()</span>
<span class="n">create_new</span> <span class="o">=</span> <span class="bp">cls</span><span class="p">(</span><span class="n">target</span><span class="p">)</span>
<span class="k">elif</span> <span class="n">replace_with</span><span class="p">:</span>
<span class="n">old_obj</span> <span class="o">=</span> <span class="bp">cls</span><span class="o">.</span><span class="n">_get_object_from_version</span><span class="p">(</span><span class="n">operations</span><span class="p">,</span> <span class="n">replace_with</span><span class="p">)</span>
<span class="n">drop_old</span> <span class="o">=</span> <span class="bp">cls</span><span class="p">(</span><span class="n">target</span><span class="p">)</span><span class="o">.</span><span class="n">reverse</span><span class="p">()</span>
<span class="n">create_new</span> <span class="o">=</span> <span class="bp">cls</span><span class="p">(</span><span class="n">old_obj</span><span class="p">)</span>
<span class="k">else</span><span class="p">:</span>
<span class="k">raise</span> <span class="ne">TypeError</span><span class="p">(</span><span class="s2">"replaces or replace_with is required"</span><span class="p">)</span>
<span class="n">operations</span><span class="o">.</span><span class="n">invoke</span><span class="p">(</span><span class="n">drop_old</span><span class="p">)</span>
<span class="n">operations</span><span class="o">.</span><span class="n">invoke</span><span class="p">(</span><span class="n">create_new</span><span class="p">)</span>
</pre></div>
</div>
<p>The workings of this class should become clear as we walk through the
example. To create usable operations from this base, we will build
a series of stub classes and use <a class="reference internal" href="ops.html#alembic.operations.Operations.register_operation" title="alembic.operations.Operations.register_operation"><code class="xref py py-meth docutils literal"><span class="pre">Operations.register_operation()</span></code></a>
to make them part of the <code class="docutils literal"><span class="pre">op.*</span></code> namespace:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="nd">@Operations</span><span class="o">.</span><span class="n">register_operation</span><span class="p">(</span><span class="s2">"create_view"</span><span class="p">,</span> <span class="s2">"invoke_for_target"</span><span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">register_operation</span><span class="p">(</span><span class="s2">"replace_view"</span><span class="p">,</span> <span class="s2">"replace"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">CreateViewOp</span><span class="p">(</span><span class="n">ReversibleOp</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">reverse</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">DropViewOp</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">target</span><span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">register_operation</span><span class="p">(</span><span class="s2">"drop_view"</span><span class="p">,</span> <span class="s2">"invoke_for_target"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">DropViewOp</span><span class="p">(</span><span class="n">ReversibleOp</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">reverse</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">CreateViewOp</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">view</span><span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">register_operation</span><span class="p">(</span><span class="s2">"create_sp"</span><span class="p">,</span> <span class="s2">"invoke_for_target"</span><span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">register_operation</span><span class="p">(</span><span class="s2">"replace_sp"</span><span class="p">,</span> <span class="s2">"replace"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">CreateSPOp</span><span class="p">(</span><span class="n">ReversibleOp</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">reverse</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">DropSPOp</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">target</span><span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">register_operation</span><span class="p">(</span><span class="s2">"drop_sp"</span><span class="p">,</span> <span class="s2">"invoke_for_target"</span><span class="p">)</span>
<span class="k">class</span> <span class="nc">DropSPOp</span><span class="p">(</span><span class="n">ReversibleOp</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">reverse</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="n">CreateSPOp</span><span class="p">(</span><span class="bp">self</span><span class="o">.</span><span class="n">target</span><span class="p">)</span>
</pre></div>
</div>
<p>To actually run the SQL like “CREATE VIEW” and “DROP SEQUENCE”, we’ll provide
implementations using <a class="reference internal" href="ops.html#alembic.operations.Operations.implementation_for" title="alembic.operations.Operations.implementation_for"><code class="xref py py-meth docutils literal"><span class="pre">Operations.implementation_for()</span></code></a>
that run straight into <a class="reference internal" href="ops.html#alembic.operations.Operations.execute" title="alembic.operations.Operations.execute"><code class="xref py py-meth docutils literal"><span class="pre">Operations.execute()</span></code></a>:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="nd">@Operations</span><span class="o">.</span><span class="n">implementation_for</span><span class="p">(</span><span class="n">CreateViewOp</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">create_view</span><span class="p">(</span><span class="n">operations</span><span class="p">,</span> <span class="n">operation</span><span class="p">):</span>
<span class="n">operations</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"CREATE VIEW </span><span class="si">%s</span><span class="s2"> AS </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span>
<span class="n">operation</span><span class="o">.</span><span class="n">target</span><span class="o">.</span><span class="n">name</span><span class="p">,</span>
<span class="n">operation</span><span class="o">.</span><span class="n">target</span><span class="o">.</span><span class="n">sqltext</span>
<span class="p">))</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">implementation_for</span><span class="p">(</span><span class="n">DropViewOp</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">drop_view</span><span class="p">(</span><span class="n">operations</span><span class="p">,</span> <span class="n">operation</span><span class="p">):</span>
<span class="n">operations</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"DROP VIEW </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">operation</span><span class="o">.</span><span class="n">target</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">implementation_for</span><span class="p">(</span><span class="n">CreateSPOp</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">create_sp</span><span class="p">(</span><span class="n">operations</span><span class="p">,</span> <span class="n">operation</span><span class="p">):</span>
<span class="n">operations</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="s2">"CREATE FUNCTION </span><span class="si">%s</span><span class="s2"> </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="p">(</span>
<span class="n">operation</span><span class="o">.</span><span class="n">target</span><span class="o">.</span><span class="n">name</span><span class="p">,</span> <span class="n">operation</span><span class="o">.</span><span class="n">target</span><span class="o">.</span><span class="n">sqltext</span>
<span class="p">)</span>
<span class="p">)</span>
<span class="nd">@Operations</span><span class="o">.</span><span class="n">implementation_for</span><span class="p">(</span><span class="n">DropSPOp</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">drop_sp</span><span class="p">(</span><span class="n">operations</span><span class="p">,</span> <span class="n">operation</span><span class="p">):</span>
<span class="n">operations</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="s2">"DROP FUNCTION </span><span class="si">%s</span><span class="s2">"</span> <span class="o">%</span> <span class="n">operation</span><span class="o">.</span><span class="n">target</span><span class="o">.</span><span class="n">name</span><span class="p">)</span>
</pre></div>
</div>
<p>All of the above code can be present anywhere within an application’s
source tree; the only requirement is that when the <code class="docutils literal"><span class="pre">env.py</span></code> script is
invoked, it includes imports that ultimately call upon these classes
as well as the <a class="reference internal" href="ops.html#alembic.operations.Operations.register_operation" title="alembic.operations.Operations.register_operation"><code class="xref py py-meth docutils literal"><span class="pre">Operations.register_operation()</span></code></a> and
<a class="reference internal" href="ops.html#alembic.operations.Operations.implementation_for" title="alembic.operations.Operations.implementation_for"><code class="xref py py-meth docutils literal"><span class="pre">Operations.implementation_for()</span></code></a> sequences.</p>
</div>
<div class="section" id="create-initial-migrations">
<h3>Create Initial Migrations<a class="headerlink" href="#create-initial-migrations" title="Permalink to this headline">¶</a></h3>
<p>We can now illustrate how these objects look during use. For the first step,
we’ll create a new migration to create a “customer” table:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic revision -m "create table"
</pre></div>
</div>
<p>We build the first revision as follows:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="sd">"""create table</span>
<span class="sd">Revision ID: 3ab8b2dfb055</span>
<span class="sd">Revises:</span>
<span class="sd">Create Date: 2015-07-27 16:22:44.918507</span>
<span class="sd">"""</span>
<span class="c1"># revision identifiers, used by Alembic.</span>
<span class="n">revision</span> <span class="o">=</span> <span class="s1">'3ab8b2dfb055'</span>
<span class="n">down_revision</span> <span class="o">=</span> <span class="kc">None</span>
<span class="n">branch_labels</span> <span class="o">=</span> <span class="kc">None</span>
<span class="n">depends_on</span> <span class="o">=</span> <span class="kc">None</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">op</span>
<span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">create_table</span><span class="p">(</span>
<span class="s2">"customer"</span><span class="p">,</span>
<span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">),</span>
<span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'order_count'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Integer</span><span class="p">),</span>
<span class="p">)</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">drop_table</span><span class="p">(</span><span class="s1">'customer'</span><span class="p">)</span>
</pre></div>
</div>
<p>For the second migration, we will create a view and a stored procedure
which act upon this table:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic revision -m "create views/sp"
</pre></div>
</div>
<p>This migration will use the new directives:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="sd">"""create views/sp</span>
<span class="sd">Revision ID: 28af9800143f</span>
<span class="sd">Revises: 3ab8b2dfb055</span>
<span class="sd">Create Date: 2015-07-27 16:24:03.589867</span>
<span class="sd">"""</span>
<span class="c1"># revision identifiers, used by Alembic.</span>
<span class="n">revision</span> <span class="o">=</span> <span class="s1">'28af9800143f'</span>
<span class="n">down_revision</span> <span class="o">=</span> <span class="s1">'3ab8b2dfb055'</span>
<span class="n">branch_labels</span> <span class="o">=</span> <span class="kc">None</span>
<span class="n">depends_on</span> <span class="o">=</span> <span class="kc">None</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">op</span>
<span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span>
<span class="kn">from</span> <span class="nn">foo</span> <span class="k">import</span> <span class="n">ReplaceableObject</span>
<span class="n">customer_view</span> <span class="o">=</span> <span class="n">ReplaceableObject</span><span class="p">(</span>
<span class="s2">"customer_view"</span><span class="p">,</span>
<span class="s2">"SELECT name, order_count FROM customer WHERE order_count > 0"</span>
<span class="p">)</span>
<span class="n">add_customer_sp</span> <span class="o">=</span> <span class="n">ReplaceableObject</span><span class="p">(</span>
<span class="s2">"add_customer_sp(name varchar, order_count integer)"</span><span class="p">,</span>
<span class="sd">"""</span>
<span class="sd"> RETURNS integer AS $$</span>
<span class="sd"> BEGIN</span>
<span class="sd"> insert into customer (name, order_count)</span>
<span class="sd"> VALUES (in_name, in_order_count);</span>
<span class="sd"> END;</span>
<span class="sd"> $$ LANGUAGE plpgsql;</span>
<span class="sd"> """</span>
<span class="p">)</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">create_view</span><span class="p">(</span><span class="n">customer_view</span><span class="p">)</span>
<span class="n">op</span><span class="o">.</span><span class="n">create_sp</span><span class="p">(</span><span class="n">add_customer_sp</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">drop_view</span><span class="p">(</span><span class="n">customer_view</span><span class="p">)</span>
<span class="n">op</span><span class="o">.</span><span class="n">drop_sp</span><span class="p">(</span><span class="n">add_customer_sp</span><span class="p">)</span>
</pre></div>
</div>
<p>We see the use of our new <code class="docutils literal"><span class="pre">create_view()</span></code>, <code class="docutils literal"><span class="pre">create_sp()</span></code>,
<code class="docutils literal"><span class="pre">drop_view()</span></code>, and <code class="docutils literal"><span class="pre">drop_sp()</span></code> directives. Running these to “head”
we get the following (this includes an edited view of SQL emitted):</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic upgrade 28af9800143
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [alembic.runtime.migration] Running upgrade -> 3ab8b2dfb055, create table
INFO [sqlalchemy.engine.base.Engine]
CREATE TABLE customer (
id SERIAL NOT NULL,
name VARCHAR,
order_count INTEGER,
PRIMARY KEY (id)
)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('3ab8b2dfb055')
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running upgrade 3ab8b2dfb055 -> 28af9800143f, create views/sp
INFO [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count FROM customer WHERE order_count > 0
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer)
RETURNS integer AS $$
BEGIN
insert into customer (name, order_count)
VALUES (in_name, in_order_count);
END;
$$ LANGUAGE plpgsql;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='28af9800143f' WHERE alembic_version.version_num = '3ab8b2dfb055'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
</pre></div>
</div>
<p>We see that our CREATE TABLE proceeded as well as the CREATE VIEW and CREATE
FUNCTION operations produced by our new directives.</p>
</div>
<div class="section" id="create-revision-migrations">
<h3>Create Revision Migrations<a class="headerlink" href="#create-revision-migrations" title="Permalink to this headline">¶</a></h3>
<p>Finally, we can illustrate how we would “revise” these objects.
Let’s consider we added a new column <code class="docutils literal"><span class="pre">email</span></code> to our <code class="docutils literal"><span class="pre">customer</span></code> table:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic revision -m "add email col"
</pre></div>
</div>
<p>The migration is:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="sd">"""add email col</span>
<span class="sd">Revision ID: 191a2d20b025</span>
<span class="sd">Revises: 28af9800143f</span>
<span class="sd">Create Date: 2015-07-27 16:25:59.277326</span>
<span class="sd">"""</span>
<span class="c1"># revision identifiers, used by Alembic.</span>
<span class="n">revision</span> <span class="o">=</span> <span class="s1">'191a2d20b025'</span>
<span class="n">down_revision</span> <span class="o">=</span> <span class="s1">'28af9800143f'</span>
<span class="n">branch_labels</span> <span class="o">=</span> <span class="kc">None</span>
<span class="n">depends_on</span> <span class="o">=</span> <span class="kc">None</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">op</span>
<span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">add_column</span><span class="p">(</span><span class="s2">"customer"</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s2">"email"</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">()))</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">drop_column</span><span class="p">(</span><span class="s2">"customer"</span><span class="p">,</span> <span class="s2">"email"</span><span class="p">)</span>
</pre></div>
</div>
<p>We now need to recreate the <code class="docutils literal"><span class="pre">customer_view</span></code> view and the
<code class="docutils literal"><span class="pre">add_customer_sp</span></code> function. To include downgrade capability, we will
need to refer to the <strong>previous</strong> version of the construct; the
<code class="docutils literal"><span class="pre">replace_view()</span></code> and <code class="docutils literal"><span class="pre">replace_sp()</span></code> operations we’ve created make
this possible, by allowing us to refer to a specific, previous revision.
the <code class="docutils literal"><span class="pre">replaces</span></code> and <code class="docutils literal"><span class="pre">replace_with</span></code> arguments accept a dot-separated
string, which refers to a revision number and an object name, such
as <code class="docutils literal"><span class="pre">"28af9800143f.customer_view"</span></code>. The <code class="docutils literal"><span class="pre">ReversibleOp</span></code> class makes use
of the <a class="reference internal" href="ops.html#alembic.operations.Operations.get_context" title="alembic.operations.Operations.get_context"><code class="xref py py-meth docutils literal"><span class="pre">Operations.get_context()</span></code></a> method to locate the version file
we refer to:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic revision -m "update views/sp"
</pre></div>
</div>
<p>The migration:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="sd">"""update views/sp</span>
<span class="sd">Revision ID: 199028bf9856</span>
<span class="sd">Revises: 191a2d20b025</span>
<span class="sd">Create Date: 2015-07-27 16:26:31.344504</span>
<span class="sd">"""</span>
<span class="c1"># revision identifiers, used by Alembic.</span>
<span class="n">revision</span> <span class="o">=</span> <span class="s1">'199028bf9856'</span>
<span class="n">down_revision</span> <span class="o">=</span> <span class="s1">'191a2d20b025'</span>
<span class="n">branch_labels</span> <span class="o">=</span> <span class="kc">None</span>
<span class="n">depends_on</span> <span class="o">=</span> <span class="kc">None</span>
<span class="kn">from</span> <span class="nn">alembic</span> <span class="k">import</span> <span class="n">op</span>
<span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span>
<span class="kn">from</span> <span class="nn">foo</span> <span class="k">import</span> <span class="n">ReplaceableObject</span>
<span class="n">customer_view</span> <span class="o">=</span> <span class="n">ReplaceableObject</span><span class="p">(</span>
<span class="s2">"customer_view"</span><span class="p">,</span>
<span class="s2">"SELECT name, order_count, email "</span>
<span class="s2">"FROM customer WHERE order_count > 0"</span>
<span class="p">)</span>
<span class="n">add_customer_sp</span> <span class="o">=</span> <span class="n">ReplaceableObject</span><span class="p">(</span>
<span class="s2">"add_customer_sp(name varchar, order_count integer, email varchar)"</span><span class="p">,</span>
<span class="sd">"""</span>
<span class="sd"> RETURNS integer AS $$</span>
<span class="sd"> BEGIN</span>
<span class="sd"> insert into customer (name, order_count, email)</span>
<span class="sd"> VALUES (in_name, in_order_count, email);</span>
<span class="sd"> END;</span>
<span class="sd"> $$ LANGUAGE plpgsql;</span>
<span class="sd"> """</span>
<span class="p">)</span>
<span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">replace_view</span><span class="p">(</span><span class="n">customer_view</span><span class="p">,</span> <span class="n">replaces</span><span class="o">=</span><span class="s2">"28af9800143f.customer_view"</span><span class="p">)</span>
<span class="n">op</span><span class="o">.</span><span class="n">replace_sp</span><span class="p">(</span><span class="n">add_customer_sp</span><span class="p">,</span> <span class="n">replaces</span><span class="o">=</span><span class="s2">"28af9800143f.add_customer_sp"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span>
<span class="n">op</span><span class="o">.</span><span class="n">replace_view</span><span class="p">(</span><span class="n">customer_view</span><span class="p">,</span> <span class="n">replace_with</span><span class="o">=</span><span class="s2">"28af9800143f.customer_view"</span><span class="p">)</span>
<span class="n">op</span><span class="o">.</span><span class="n">replace_sp</span><span class="p">(</span><span class="n">add_customer_sp</span><span class="p">,</span> <span class="n">replace_with</span><span class="o">=</span><span class="s2">"28af9800143f.add_customer_sp"</span><span class="p">)</span>
</pre></div>
</div>
<p>Above, instead of using <code class="docutils literal"><span class="pre">create_view()</span></code>, <code class="docutils literal"><span class="pre">create_sp()</span></code>,
<code class="docutils literal"><span class="pre">drop_view()</span></code>, and <code class="docutils literal"><span class="pre">drop_sp()</span></code> methods, we now use <code class="docutils literal"><span class="pre">replace_view()</span></code> and
<code class="docutils literal"><span class="pre">replace_sp()</span></code>. The replace operation we’ve built always runs a DROP <em>and</em>
a CREATE. Running an upgrade to head we see:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic upgrade head
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running upgrade 28af9800143f -> 191a2d20b025, add email col
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE customer ADD COLUMN email VARCHAR
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='191a2d20b025' WHERE alembic_version.version_num = '28af9800143f'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running upgrade 191a2d20b025 -> 199028bf9856, update views/sp
INFO [sqlalchemy.engine.base.Engine] DROP VIEW customer_view
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count, email FROM customer WHERE order_count > 0
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] DROP FUNCTION add_customer_sp(name varchar, order_count integer)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer, email varchar)
RETURNS integer AS $$
BEGIN
insert into customer (name, order_count, email)
VALUES (in_name, in_order_count, email);
END;
$$ LANGUAGE plpgsql;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='199028bf9856' WHERE alembic_version.version_num = '191a2d20b025'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
</pre></div>
</div>
<p>After adding our new <code class="docutils literal"><span class="pre">email</span></code> column, we see that both <code class="docutils literal"><span class="pre">customer_view</span></code>
and <code class="docutils literal"><span class="pre">add_customer_sp()</span></code> are dropped before the new version is created.
If we downgrade back to the old version, we see the old version of these
recreated again within the downgrade for this migration:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span>$ alembic downgrade 28af9800143
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running downgrade 199028bf9856 -> 191a2d20b025, update views/sp
INFO [sqlalchemy.engine.base.Engine] DROP VIEW customer_view
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count FROM customer WHERE order_count > 0
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] DROP FUNCTION add_customer_sp(name varchar, order_count integer, email varchar)
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer)
RETURNS integer AS $$
BEGIN
insert into customer (name, order_count)
VALUES (in_name, in_order_count);
END;
$$ LANGUAGE plpgsql;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='191a2d20b025' WHERE alembic_version.version_num = '199028bf9856'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running downgrade 191a2d20b025 -> 28af9800143f, add email col
INFO [sqlalchemy.engine.base.Engine] ALTER TABLE customer DROP COLUMN email
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='28af9800143f' WHERE alembic_version.version_num = '191a2d20b025'
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
</pre></div>
</div>
</div>
</div>
<div class="section" id="don-t-generate-empty-migrations-with-autogenerate">
<h2>Don’t Generate Empty Migrations with Autogenerate<a class="headerlink" href="#don-t-generate-empty-migrations-with-autogenerate" title="Permalink to this headline">¶</a></h2>
<p>A common request is to have the <code class="docutils literal"><span class="pre">alembic</span> <span class="pre">revision</span> <span class="pre">--autogenerate</span></code> command not
actually generate a revision file if no changes to the schema is detected. Using
the <a class="reference internal" href="api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.process_revision_directives" title="alembic.runtime.environment.EnvironmentContext.configure"><code class="xref py py-paramref docutils literal"><span class="pre">EnvironmentContext.configure.process_revision_directives</span></code></a>
hook, this is straightforward; place a <code class="docutils literal"><span class="pre">process_revision_directives</span></code>
hook in <a class="reference internal" href="api/runtime.html#alembic.runtime.migration.MigrationContext.configure" title="alembic.runtime.migration.MigrationContext.configure"><code class="xref py py-meth docutils literal"><span class="pre">MigrationContext.configure()</span></code></a> which removes the
single <a class="reference internal" href="api/operations.html#alembic.operations.ops.MigrationScript" title="alembic.operations.ops.MigrationScript"><code class="xref py py-class docutils literal"><span class="pre">MigrationScript</span></code></a> directive if it is empty of
any operations:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">run_migrations_online</span><span class="p">():</span>
<span class="c1"># ...</span>
<span class="k">def</span> <span class="nf">process_revision_directives</span><span class="p">(</span><span class="n">context</span><span class="p">,</span> <span class="n">revision</span><span class="p">,</span> <span class="n">directives</span><span class="p">):</span>
<span class="k">if</span> <span class="n">config</span><span class="o">.</span><span class="n">cmd_opts</span><span class="o">.</span><span class="n">autogenerate</span><span class="p">:</span>
<span class="n">script</span> <span class="o">=</span> <span class="n">directives</span><span class="p">[</span><span class="mi">0</span><span class="p">]</span>
<span class="k">if</span> <span class="n">script</span><span class="o">.</span><span class="n">upgrade_ops</span><span class="o">.</span><span class="n">is_empty</span><span class="p">():</span>
<span class="n">directives</span><span class="p">[:]</span> <span class="o">=</span> <span class="p">[]</span>
<span class="c1"># connectable = ...</span>
<span class="k">with</span> <span class="n">connectable</span><span class="o">.</span><span class="n">connect</span><span class="p">()</span> <span class="k">as</span> <span class="n">connection</span><span class="p">:</span>
<span class="n">context</span><span class="o">.</span><span class="n">configure</span><span class="p">(</span>
<span class="n">connection</span><span class="o">=</span><span class="n">connection</span><span class="p">,</span>
<span class="n">target_metadata</span><span class="o">=</span><span class="n">target_metadata</span><span class="p">,</span>
<span class="n">process_revision_directives</span><span class="o">=</span><span class="n">process_revision_directives</span>
<span class="p">)</span>
<span class="k">with</span> <span class="n">context</span><span class="o">.</span><span class="n">begin_transaction</span><span class="p">():</span>
<span class="n">context</span><span class="o">.</span><span class="n">run_migrations</span><span class="p">()</span>
</pre></div>
</div>
</div>
<div class="section" id="don-t-emit-create-table-statements-for-views">
<h2>Don’t emit CREATE TABLE statements for Views<a class="headerlink" href="#don-t-emit-create-table-statements-for-views" title="Permalink to this headline">¶</a></h2>
<p>It is sometimes convenient to create <code class="xref py py-class docutils literal"><span class="pre">Table</span></code> instances for views
so that they can be queried using normal SQLAlchemy techniques. Unfortunately this
causes Alembic to treat them as tables in need of creation and to generate spurious
<code class="docutils literal"><span class="pre">create_table()</span></code> operations. This is easily fixable by flagging such Tables and using the
<a class="reference internal" href="api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_object" title="alembic.runtime.environment.EnvironmentContext.configure"><code class="xref py py-paramref docutils literal"><span class="pre">include_object</span></code></a> hook to exclude them:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="n">my_view</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'my_view'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span> <span class="n">autoload</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span> <span class="n">info</span><span class="o">=</span><span class="nb">dict</span><span class="p">(</span><span class="n">is_view</span><span class="o">=</span><span class="kc">True</span><span class="p">))</span> <span class="c1"># Flag this as a view</span>
</pre></div>
</div>
<p>Then define <code class="docutils literal"><span class="pre">include_object</span></code> as:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">include_object</span><span class="p">(</span><span class="nb">object</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">type_</span><span class="p">,</span> <span class="n">reflected</span><span class="p">,</span> <span class="n">compare_to</span><span class="p">):</span>
<span class="sd">"""</span>
<span class="sd"> Exclude views from Alembic's consideration.</span>
<span class="sd"> """</span>
<span class="k">return</span> <span class="ow">not</span> <span class="nb">object</span><span class="o">.</span><span class="n">info</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s1">'is_view'</span><span class="p">,</span> <span class="kc">False</span><span class="p">)</span>
</pre></div>
</div>
<p>Finally, in <code class="docutils literal"><span class="pre">env.py</span></code> pass your <code class="docutils literal"><span class="pre">include_object</span></code> as a keyword argument to <a class="reference internal" href="api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure" title="alembic.runtime.environment.EnvironmentContext.configure"><code class="xref py py-meth docutils literal"><span class="pre">EnvironmentContext.configure()</span></code></a>.</p>
</div>
<div class="section" id="run-multiple-alembic-environments-from-one-ini-file">
<span id="multiple-environments"></span><h2>Run Multiple Alembic Environments from one .ini file<a class="headerlink" href="#run-multiple-alembic-environments-from-one-ini-file" title="Permalink to this headline">¶</a></h2>
<p>Long before Alembic had the “multiple bases” feature described in <a class="reference internal" href="branches.html#multiple-bases"><span class="std std-ref">Working with Multiple Bases</span></a>,
projects had a need to maintain more than one Alembic version history in a single
project, where these version histories are completely independent of each other
and each refer to their own alembic_version table, either across multiple databases,
schemas, or namespaces. A simple approach was added to support this, the
<code class="docutils literal"><span class="pre">--name</span></code> flag on the commandline.</p>
<p>First, one would create an alembic.ini file of this form:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="p">[</span><span class="n">DEFAULT</span><span class="p">]</span>
<span class="c1"># all defaults shared between environments go here</span>
<span class="n">sqlalchemy</span><span class="o">.</span><span class="n">url</span> <span class="o">=</span> <span class="n">postgresql</span><span class="p">:</span><span class="o">//</span><span class="n">scott</span><span class="p">:</span><span class="n">tiger</span><span class="nd">@hostname</span><span class="o">/</span><span class="n">mydatabase</span>
<span class="p">[</span><span class="n">schema1</span><span class="p">]</span>
<span class="c1"># path to env.py and migration scripts for schema1</span>
<span class="n">script_location</span> <span class="o">=</span> <span class="n">myproject</span><span class="o">/</span><span class="n">revisions</span><span class="o">/</span><span class="n">schema1</span>
<span class="p">[</span><span class="n">schema2</span><span class="p">]</span>
<span class="c1"># path to env.py and migration scripts for schema2</span>
<span class="n">script_location</span> <span class="o">=</span> <span class="n">myproject</span><span class="o">/</span><span class="n">revisions</span><span class="o">/</span><span class="n">schema2</span>
<span class="p">[</span><span class="n">schema3</span><span class="p">]</span>
<span class="c1"># path to env.py and migration scripts for schema3</span>
<span class="n">script_location</span> <span class="o">=</span> <span class="n">myproject</span><span class="o">/</span><span class="n">revisions</span><span class="o">/</span><span class="n">db2</span>
<span class="c1"># this schema uses a different database URL as well</span>
<span class="n">sqlalchemy</span><span class="o">.</span><span class="n">url</span> <span class="o">=</span> <span class="n">postgresql</span><span class="p">:</span><span class="o">//</span><span class="n">scott</span><span class="p">:</span><span class="n">tiger</span><span class="nd">@hostname</span><span class="o">/</span><span class="n">myotherdatabase</span>
</pre></div>
</div>
<p>Above, in the <code class="docutils literal"><span class="pre">[DEFAULT]</span></code> section we set up a default database URL.
Then we create three sections corresponding to different revision lineages
in our project. Each of these directories would have its own <code class="docutils literal"><span class="pre">env.py</span></code>
and set of versioning files. Then when we run the <code class="docutils literal"><span class="pre">alembic</span></code> command,
we simply give it the name of the configuration we want to use:</p>
<div class="highlight-default"><div class="highlight"><pre><span></span><span class="n">alembic</span> <span class="o">--</span><span class="n">name</span> <span class="n">schema2</span> <span class="n">revision</span> <span class="o">-</span><span class="n">m</span> <span class="s2">"new rev for schema 2"</span> <span class="o">--</span><span class="n">autogenerate</span>
</pre></div>
</div>
<p>Above, the <code class="docutils literal"><span class="pre">alembic</span></code> command makes use of the configuration in <code class="docutils literal"><span class="pre">[schema2]</span></code>,
populated with defaults from the <code class="docutils literal"><span class="pre">[DEFAULT]</span></code> section.</p>
<p>The above approach can be automated by creating a custom front-end to the
Alembic commandline as well.</p>
</div>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar" role="navigation" aria-label="main navigation">
<div class="sphinxsidebarwrapper">
<h3><a href="index.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Cookbook</a><ul>
<li><a class="reference internal" href="#building-an-up-to-date-database-from-scratch">Building an Up to Date Database from Scratch</a></li>
<li><a class="reference internal" href="#conditional-migration-elements">Conditional Migration Elements</a></li>
<li><a class="reference internal" href="#sharing-a-connection-with-a-series-of-migration-commands-and-environments">Sharing a Connection with a Series of Migration Commands and Environments</a></li>
<li><a class="reference internal" href="#replaceable-objects">Replaceable Objects</a><ul>
<li><a class="reference internal" href="#the-replaceable-object-structure">The Replaceable Object Structure</a></li>
<li><a class="reference internal" href="#create-operations-for-the-target-objects">Create Operations for the Target Objects</a></li>
<li><a class="reference internal" href="#create-initial-migrations">Create Initial Migrations</a></li>
<li><a class="reference internal" href="#create-revision-migrations">Create Revision Migrations</a></li>
</ul>
</li>
<li><a class="reference internal" href="#don-t-generate-empty-migrations-with-autogenerate">Don’t Generate Empty Migrations with Autogenerate</a></li>
<li><a class="reference internal" href="#don-t-emit-create-table-statements-for-views">Don’t emit CREATE TABLE statements for Views</a></li>
<li><a class="reference internal" href="#run-multiple-alembic-environments-from-one-ini-file">Run Multiple Alembic Environments from one .ini file</a></li>
</ul>
</li>
</ul>
<div id="searchbox" style="display: none" role="search">
<h3>Quick search</h3>
<form class="search" action="search.html" method="get">
<div><input type="text" name="q" /></div>
<div><input type="submit" value="Go" /></div>
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
<h4>Previous topic</h4>
<p class="topless"><a href="ops.html"
title="previous chapter">Operation Reference</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="api/index.html"
title="next chapter">API Details</a></p>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="related" role="navigation" aria-label="related navigation">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
>index</a></li>
<li class="right" >
<a href="py-modindex.html" title="Python Module Index"
>modules</a> |</li>
<li class="right" >
<a href="api/index.html" title="API Details"
>next</a> |</li>
<li class="right" >
<a href="ops.html" title="Operation Reference"
>previous</a> |</li>
<li class="nav-item nav-item-0"><a href="index.html">Alembic 0.9.3 documentation</a> »</li>
</ul>
</div>
<div class="footer" role="contentinfo">
© Copyright 2010-2017, Mike Bayer.
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.6.5.
</div>
</body>
</html>
|