EVOLUTION-NINJA

Edit File: cookbook.html

<!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 &mdash; Alembic 0.8.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.8.3',
 COLLAPSE_INDEX: false,
 FILE_SUFFIX: '.html',
 HAS_SOURCE: true
 };
 </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="top" title="Alembic 0.8.3 documentation" href="index.html" />
 <link rel="next" title="API Details" href="api/index.html" />
 <link rel="prev" title="Operation Reference" href="ops.html" /> 
 </head>
 <body role="document">
 <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.8.3 documentation</a> &raquo;</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>
A collection of &#8220;How-Tos&#8221;, highlighting various ways to extend
Alembic.
<div class="admonition note">
Note
This is a new section where we hope to start cataloguing various &#8220;how-tos&#8221;
we come up with based on user requests. It is often the case that users
will request a feature only to learn that simple customization can
provide the same thing. There&#8217;s only one recipe at the moment but
we hope to get more soon!
</div>
<div class="section" id="building-an-up-to-date-database-from-scratch">
<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>
There&#8217;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&#8217;s kind of overkill, considering that SQLAlchemy itself can emit
the full CREATE statements for any given model using <a class="reference external" href="http://www.sqlalchemy.org/docs/core/metadata.html#sqlalchemy.schema.MetaData.create_all" title="(in SQLAlchemy v1.0)"><code class="xref py py-meth docutils literal">create_all()</code></a>. 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.
Alembic can integrate with a <a class="reference external" href="http://www.sqlalchemy.org/docs/core/metadata.html#sqlalchemy.schema.MetaData.create_all" title="(in SQLAlchemy v1.0)"><code class="xref py py-meth docutils literal">create_all()</code></a> 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">head</code>):
<div class="highlight-python"><div class="highlight"><pre># inside of a &quot;create the database&quot; script, first create
# tables:
my_metadata.create_all(engine)

# then, load the Alembic configuration and generate the
# version table, &quot;stamping&quot; it with the most recent rev:
from alembic.config import Config
from alembic import command
alembic_cfg = Config(&quot;/path/to/yourapp/alembic.ini&quot;)
command.stamp(alembic_cfg, &quot;head&quot;)
</pre></div>
</div>
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
new databases. You can now remove old migration files that are no longer represented
on any existing environments.
To prune old migration files, simply delete the files. Then, in the earliest, still-remaining
migration file, set <code class="docutils literal">down_revision</code> to <code class="docutils literal">None</code>:
<div class="highlight-python"><div class="highlight"><pre># replace this:
#down_revision = &#39;290696571ad2&#39;

# with this:
down_revision = None
</pre></div>
</div>
That file now becomes the &#8220;base&#8221; of the migration series.
</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>
This example features the basic idea of a common need, that of affecting
how a migration runs based on command line switches.
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">EnvironmentContext.get_x_argument()</code></a> collection for any additional,
user-defined parameters. Then take action based on the presence of those
arguments.
To make it such that the logic to inspect these flags is easy to use and
modify, we modify our <code class="docutils literal">script.py.mako</code> template to make this feature
available in all new revision files:
<div class="highlight-mako"><div class="highlight"><pre>&quot;&quot;&quot;${message}

Revision ID: ${up_revision}
Revises: ${down_revision}
Create Date: ${create_date}

&quot;&quot;&quot;

# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}

from alembic import op
import sqlalchemy as sa
${imports if imports else &quot;&quot;}

from alembic import context

def upgrade():
 schema_upgrades()
 if context.get_x_argument(as_dictionary=True).get(&#39;data&#39;, None):
 data_upgrades()

def downgrade():
 if context.get_x_argument(as_dictionary=True).get(&#39;data&#39;, None):
 data_downgrades()
 schema_downgrades()

def schema_upgrades():
 &quot;&quot;&quot;schema upgrade migrations go here.&quot;&quot;&quot;
 ${upgrades if upgrades else &quot;pass&quot;}

def schema_downgrades():
 &quot;&quot;&quot;schema downgrade migrations go here.&quot;&quot;&quot;
 ${downgrades if downgrades else &quot;pass&quot;}

def data_upgrades():
 &quot;&quot;&quot;Add any optional data upgrade migrations here!&quot;&quot;&quot;
 pass

def data_downgrades():
 &quot;&quot;&quot;Add any optional data downgrade migrations here!&quot;&quot;&quot;
 pass
</pre></div>
</div>
Now, when we create a new migration file, the <code class="docutils literal">data_upgrades()</code> and <code class="docutils literal">data_downgrades()</code>
placeholders will be available, where we can add optional data migrations:
<div class="highlight-python"><div class="highlight"><pre>&quot;&quot;&quot;rev one

Revision ID: 3ba2b522d10d
Revises: None
Create Date: 2014-03-04 18:05:36.992867

&quot;&quot;&quot;

# revision identifiers, used by Alembic.
revision = &#39;3ba2b522d10d&#39;
down_revision = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy import String, Column
from sqlalchemy.sql import table, column

from alembic import context

def upgrade():
 schema_upgrades()
 if context.get_x_argument(as_dictionary=True).get(&#39;data&#39;, None):
 data_upgrades()

def downgrade():
 if context.get_x_argument(as_dictionary=True).get(&#39;data&#39;, None):
 data_downgrades()
 schema_downgrades()

def schema_upgrades():
 &quot;&quot;&quot;schema upgrade migrations go here.&quot;&quot;&quot;
 op.create_table(&quot;my_table&quot;, Column(&#39;data&#39;, String))

def schema_downgrades():
 &quot;&quot;&quot;schema downgrade migrations go here.&quot;&quot;&quot;
 op.drop_table(&quot;my_table&quot;)

def data_upgrades():
 &quot;&quot;&quot;Add any optional data upgrade migrations here!&quot;&quot;&quot;

my_table = table(&#39;my_table&#39;,
 column(&#39;data&#39;, String),
 )

op.bulk_insert(my_table,
 [
 {&#39;data&#39;: &#39;data 1&#39;},
 {&#39;data&#39;: &#39;data 2&#39;},
 {&#39;data&#39;: &#39;data 3&#39;},
 ]
 )

def data_downgrades():
 &quot;&quot;&quot;Add any optional data downgrade migrations here!&quot;&quot;&quot;

op.execute(&quot;delete from my_table&quot;)
</pre></div>
</div>
To invoke our migrations with data included, we use the <code class="docutils literal">-x</code> flag:
<div class="highlight-python"><div class="highlight"><pre>alembic -x data=true upgrade head
</pre></div>
</div>
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">EnvironmentContext.get_x_argument()</code></a> is an easy way to support
new commandline options within environment and migration scripts.
</div>
<div class="section" id="sharing-a-connection-with-a-series-of-migration-commands-and-environments">
<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>
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">Commands</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">env.py</code> script
of a migration environment, which is called within the scope of a command.
The steps to take here are:
<ol class="arabic simple">
<li>Produce the <a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Connection" title="(in SQLAlchemy v1.0)"><code class="xref py py-class docutils literal">Connection</code></a> object to use.</li>
<li>Place it somewhere that <code class="docutils literal">env.py</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">Config.attributes</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">Config</code></a> object).</li>
<li>The <code class="docutils literal">env.py</code> script is modified such that it looks for this
<a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Connection" title="(in SQLAlchemy v1.0)"><code class="xref py py-class docutils literal">Connection</code></a> and makes use of it, in lieu
of building up its own <a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Engine" title="(in SQLAlchemy v1.0)"><code class="xref py py-class docutils literal">Engine</code></a> instance.</li>
</ol>
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">Config.attributes</code></a>:
<div class="highlight-python"><div class="highlight"><pre>from alembic import command, config

cfg = config.Config(&quot;/path/to/yourapp/alembic.ini&quot;)
with engine.begin() as connection:
 cfg.attributes[&#39;connection&#39;] = connection
 command.upgrade(cfg, &quot;head&quot;)
</pre></div>
</div>
Then in <code class="docutils literal">env.py</code>:
<div class="highlight-python"><div class="highlight"><pre>def run_migrations_online():
 connectable = config.attributes.get(&#39;connection&#39;, None)

if connectable is None:
 # only create Engine if we don&#39;t have a Connection
 # from the outside
 connectable = engine_from_config(
 config.get_section(config.config_ini_section),
 prefix=&#39;sqlalchemy.&#39;,
 poolclass=pool.NullPool)

# when connectable is already a Connection object, calling
 # connect() gives us a *branched connection*.

with connectable.connect() as connection:
 context.configure(
 connection=connection,
 target_metadata=target_metadata
 )

with context.begin_transaction():
 context.run_migrations()
</pre></div>
</div>
<div class="topic">
Branched Connections
Note that we are calling the <code class="docutils literal">connect()</code> method, even if we are
using a <a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Connection" title="(in SQLAlchemy v1.0)"><code class="xref py py-class docutils literal">Connection</code></a> object to start with.
The effect this has when calling <a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Connection.connect" title="(in SQLAlchemy v1.0)"><code class="xref py py-meth docutils literal">connect()</code></a>
is that SQLAlchemy passes us a branch of the original connection; it
is in every way the same as the <a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Connection" title="(in SQLAlchemy v1.0)"><code class="xref py py-class docutils literal">Connection</code></a>
we started with, except it provides nested scope; the
context we have here as well as the
<a class="reference external" href="http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.Connection.close" title="(in SQLAlchemy v1.0)"><code class="xref py py-meth docutils literal">close()</code></a> method of this branched
connection doesn&#8217;t actually close the outer connection, which stays
active for continued use.
</div>
<div class="versionadded">
New in version 0.7.5: 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">Config.attributes</code></a>.
</div>
</div>
<div class="section" id="replaceable-objects">
<h2>Replaceable Objects<a class="headerlink" href="#replaceable-objects" title="Permalink to this headline">¶</a></h2>
This recipe proposes a hypothetical way of dealing with
what we might call a replaceable 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.
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 previous version of that
construct fully, and we&#8217;d much rather not have to type out the whole
definition in multiple places.
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">Operation Plugins</a> feature introduced in
Alembic 0.8. It may be very helpful to review
this section first to get an overview of this API.
<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>
We first need to devise a simple format that represents the &#8220;CREATE XYZ&#8221; /
&#8220;DROP XYZ&#8221; aspect of what it is we&#8217;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&#8217;ll use a simple value object called <code class="docutils literal">ReplaceableObject</code> that can
represent any named set of SQL text to send to a &#8220;CREATE&#8221; statement of
some kind:
<div class="highlight-python"><div class="highlight"><pre>class ReplaceableObject(object):
 def __init__(self, name, sqltext):
 self.name = name
 self.sqltext = sqltext
</pre></div>
</div>
Using this object in a migration script, assuming a Postgresql-style
syntax, looks like:
<div class="highlight-python"><div class="highlight"><pre>customer_view = ReplaceableObject(
 &quot;customer_view&quot;,
 &quot;SELECT name, order_count FROM customer WHERE order_count &gt; 0&quot;
)

add_customer_sp = ReplaceableObject(
 &quot;add_customer_sp(name varchar, order_count integer)&quot;,
 &quot;&quot;&quot;
 RETURNS integer AS $$
 BEGIN
 insert into customer (name, order_count)
 VALUES (in_name, in_order_count);
 END;
 $$ LANGUAGE plpgsql;
 &quot;&quot;&quot;
)
</pre></div>
</div>
The <code class="docutils literal">ReplaceableObject</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.
</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>
We&#8217;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">Operations</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">op.*</code> namespace very nicely.
The most intricate class is below. This is the base of our &#8220;replaceable&#8221;
operation, which includes not just a base operation for emitting
CREATE and DROP instructions on a <code class="docutils literal">ReplaceableObject</code>, it also assumes
a certain model of &#8220;reversibility&#8221; which makes use of references to
other migration files in order to refer to the &#8220;previous&#8221; version
of an object:
<div class="highlight-python"><div class="highlight"><pre>from alembic.operations import Operations, MigrateOperation

class ReversibleOp(MigrateOperation):
 def __init__(self, target):
 self.target = target

@classmethod
 def invoke_for_target(cls, operations, target):
 op = cls(target)
 return operations.invoke(op)

def reverse(self):
 raise NotImplementedError()

@classmethod
 def _get_object_from_version(cls, operations, ident):
 version, objname = ident.split(&quot;.&quot;)

module = operations.get_context().script.get_revision(version).module
 obj = getattr(module, objname)
 return obj

@classmethod
 def replace(cls, operations, target, replaces=None, replace_with=None):

if replaces:
 old_obj = cls._get_object_from_version(operations, replaces)
 drop_old = cls(old_obj).reverse()
 create_new = cls(target)
 elif replace_with:
 old_obj = cls._get_object_from_version(operations, replace_with)
 drop_old = cls(target).reverse()
 create_new = cls(old_obj)
 else:
 raise TypeError(&quot;replaces or replace_with is required&quot;)

operations.invoke(drop_old)
 operations.invoke(create_new)
</pre></div>
</div>
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">Operations.register_operation()</code></a>
to make them part of the <code class="docutils literal">op.*</code> namespace:
<div class="highlight-python"><div class="highlight"><pre>@Operations.register_operation(&quot;create_view&quot;, &quot;invoke_for_target&quot;)
@Operations.register_operation(&quot;replace_view&quot;, &quot;replace&quot;)
class CreateViewOp(ReversibleOp):
 def reverse(self):
 return DropViewOp(self.target)

@Operations.register_operation(&quot;drop_view&quot;, &quot;invoke_for_target&quot;)
class DropViewOp(ReversibleOp):
 def reverse(self):
 return CreateViewOp(self.view)

@Operations.register_operation(&quot;create_sp&quot;, &quot;invoke_for_target&quot;)
@Operations.register_operation(&quot;replace_sp&quot;, &quot;replace&quot;)
class CreateSPOp(ReversibleOp):
 def reverse(self):
 return DropSPOp(self.target)

@Operations.register_operation(&quot;drop_sp&quot;, &quot;invoke_for_target&quot;)
class DropSPOp(ReversibleOp):
 def reverse(self):
 return CreateSPOp(self.target)
</pre></div>
</div>
To actually run the SQL like &#8220;CREATE VIEW&#8221; and &#8220;DROP SEQUENCE&#8221;, we&#8217;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">Operations.implementation_for()</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">Operations.execute()</code></a>:
<div class="highlight-python"><div class="highlight"><pre>@Operations.implementation_for(CreateViewOp)
def create_view(operations, operation):
 operations.execute(&quot;CREATE VIEW %s AS %s&quot; % (
 operation.target.name,
 operation.target.sqltext
 ))

@Operations.implementation_for(DropViewOp)
def drop_view(operations, operation):
 operations.execute(&quot;DROP VIEW %s&quot; % operation.target.name)

@Operations.implementation_for(CreateSPOp)
def create_sp(operations, operation):
 operations.execute(
 &quot;CREATE FUNCTION %s %s&quot; % (
 operation.target.name, operation.target.sqltext
 )
 )

@Operations.implementation_for(DropSPOp)
def drop_sp(operations, operation):
 operations.execute(&quot;DROP FUNCTION %s&quot; % operation.target.name)
</pre></div>
</div>
All of the above code can be present anywhere within an application&#8217;s
source tree; the only requirement is that when the <code class="docutils literal">env.py</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">Operations.register_operation()</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">Operations.implementation_for()</code></a> sequences.
</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>
We can now illustrate how these objects look during use. For the first step,
we&#8217;ll create a new migration to create a &#8220;customer&#8221; table:
<div class="highlight-python"><div class="highlight"><pre>$ alembic revision -m &quot;create table&quot;
</pre></div>
</div>
We build the first revision as follows:
<div class="highlight-python"><div class="highlight"><pre>&quot;&quot;&quot;create table

Revision ID: 3ab8b2dfb055
Revises:
Create Date: 2015-07-27 16:22:44.918507

&quot;&quot;&quot;

# revision identifiers, used by Alembic.
revision = &#39;3ab8b2dfb055&#39;
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
 op.create_table(
 &quot;customer&quot;,
 sa.Column(&#39;id&#39;, sa.Integer, primary_key=True),
 sa.Column(&#39;name&#39;, sa.String),
 sa.Column(&#39;order_count&#39;, sa.Integer),
 )

def downgrade():
 op.drop_table(&#39;customer&#39;)
</pre></div>
</div>
For the second migration, we will create a view and a stored procedure
which act upon this table:
<div class="highlight-python"><div class="highlight"><pre>$ alembic revision -m &quot;create views/sp&quot;
</pre></div>
</div>
This migration will use the new directives:
<div class="highlight-python"><div class="highlight"><pre>&quot;&quot;&quot;create views/sp

Revision ID: 28af9800143f
Revises: 3ab8b2dfb055
Create Date: 2015-07-27 16:24:03.589867

&quot;&quot;&quot;

# revision identifiers, used by Alembic.
revision = &#39;28af9800143f&#39;
down_revision = &#39;3ab8b2dfb055&#39;
branch_labels = None
depends_on = None

from foo import ReplaceableObject

customer_view = ReplaceableObject(
 &quot;customer_view&quot;,
 &quot;SELECT name, order_count FROM customer WHERE order_count &gt; 0&quot;
)

def upgrade():
 op.create_view(customer_view)
 op.create_sp(add_customer_sp)

def downgrade():
 op.drop_view(customer_view)
 op.drop_sp(add_customer_sp)
</pre></div>
</div>
We see the use of our new <code class="docutils literal">create_view()</code>, <code class="docutils literal">create_sp()</code>,
<code class="docutils literal">drop_view()</code>, and <code class="docutils literal">drop_sp()</code> directives. Running these to &#8220;head&#8221;
we get the following (this includes an edited view of SQL emitted):
<div class="highlight-python"><div class="highlight"><pre>$ 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] {&#39;name&#39;: u&#39;alembic_version&#39;}
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] {&#39;name&#39;: u&#39;alembic_version&#39;}
INFO [alembic.runtime.migration] Running upgrade -&gt; 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 (&#39;3ab8b2dfb055&#39;)
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [alembic.runtime.migration] Running upgrade 3ab8b2dfb055 -&gt; 28af9800143f, create views/sp
INFO  [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count FROM customer WHERE order_count &gt; 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=&#39;28af9800143f&#39; WHERE alembic_version.version_num = &#39;3ab8b2dfb055&#39;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
</pre></div>
</div>
We see that our CREATE TABLE proceeded as well as the CREATE VIEW and CREATE
FUNCTION operations produced by our new directives.
</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>
Finally, we can illustrate how we would &#8220;revise&#8221; these objects.
Let&#8217;s consider we added a new column <code class="docutils literal">email</code> to our <code class="docutils literal">customer</code> table:
<div class="highlight-python"><div class="highlight"><pre>$ alembic revision -m &quot;add email col&quot;
</pre></div>
</div>
The migration is:
<div class="highlight-python"><div class="highlight"><pre>&quot;&quot;&quot;add email col

Revision ID: 191a2d20b025
Revises: 28af9800143f
Create Date: 2015-07-27 16:25:59.277326

&quot;&quot;&quot;

# revision identifiers, used by Alembic.
revision = &#39;191a2d20b025&#39;
down_revision = &#39;28af9800143f&#39;
branch_labels = None
depends_on = None

def upgrade():
 op.add_column(&quot;customer&quot;, sa.Column(&quot;email&quot;, sa.String()))

def downgrade():
 op.drop_column(&quot;customer&quot;, &quot;email&quot;)
</pre></div>
</div>
We now need to recreate the <code class="docutils literal">customer_view</code> view and the
<code class="docutils literal">add_customer_sp</code> function. To include downgrade capability, we will
need to refer to the previous version of the construct; the
<code class="docutils literal">replace_view()</code> and <code class="docutils literal">replace_sp()</code> operations we&#8217;ve created make
this possible, by allowing us to refer to a specific, previous revision.
the <code class="docutils literal">replaces</code> and <code class="docutils literal">replace_with</code> arguments accept a dot-separated
string, which refers to a revision number and an object name, such
as <code class="docutils literal">&quot;28af9800143f.customer_view&quot;</code>. The <code class="docutils literal">ReversibleOp</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">Operations.get_context()</code></a> method to locate the version file
we refer to:
<div class="highlight-python"><div class="highlight"><pre>$ alembic revision -m &quot;update views/sp&quot;
</pre></div>
</div>
The migration:
<div class="highlight-python"><div class="highlight"><pre>&quot;&quot;&quot;update views/sp

Revision ID: 199028bf9856
Revises: 191a2d20b025
Create Date: 2015-07-27 16:26:31.344504

&quot;&quot;&quot;

# revision identifiers, used by Alembic.
revision = &#39;199028bf9856&#39;
down_revision = &#39;191a2d20b025&#39;
branch_labels = None
depends_on = None

from foo import ReplaceableObject

customer_view = ReplaceableObject(
 &quot;customer_view&quot;,
 &quot;SELECT name, order_count, email &quot;
 &quot;FROM customer WHERE order_count &gt; 0&quot;
)

def upgrade():
 op.replace_view(customer_view, replaces=&quot;28af9800143f.customer_view&quot;)
 op.replace_sp(add_customer_sp, replaces=&quot;28af9800143f.add_customer_sp&quot;)

def downgrade():
 op.replace_view(customer_view, replace_with=&quot;28af9800143f.customer_view&quot;)
 op.replace_sp(add_customer_sp, replace_with=&quot;28af9800143f.add_customer_sp&quot;)
</pre></div>
</div>
Above, instead of using <code class="docutils literal">create_view()</code>, <code class="docutils literal">create_sp()</code>,
<code class="docutils literal">drop_view()</code>, and <code class="docutils literal">drop_sp()</code> methods, we now use <code class="docutils literal">replace_view()</code> and
<code class="docutils literal">replace_sp()</code>. The replace operation we&#8217;ve built always runs a DROP and
a CREATE. Running an upgrade to head we see:
<div class="highlight-python"><div class="highlight"><pre>$ 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] {&#39;name&#39;: u&#39;alembic_version&#39;}
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 -&gt; 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=&#39;191a2d20b025&#39; WHERE alembic_version.version_num = &#39;28af9800143f&#39;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running upgrade 191a2d20b025 -&gt; 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 &gt; 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=&#39;199028bf9856&#39; WHERE alembic_version.version_num = &#39;191a2d20b025&#39;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [sqlalchemy.engine.base.Engine] COMMIT
</pre></div>
</div>
After adding our new <code class="docutils literal">email</code> column, we see that both <code class="docutils literal">customer_view</code>
and <code class="docutils literal">add_customer_sp()</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:
<div class="highlight-python"><div class="highlight"><pre>$ 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] {&#39;name&#39;: u&#39;alembic_version&#39;}
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 -&gt; 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 &gt; 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=&#39;191a2d20b025&#39; WHERE alembic_version.version_num = &#39;199028bf9856&#39;
INFO [sqlalchemy.engine.base.Engine] {}
INFO [alembic.runtime.migration] Running downgrade 191a2d20b025 -&gt; 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=&#39;28af9800143f&#39; WHERE alembic_version.version_num = &#39;191a2d20b025&#39;
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&#8217;t Generate Empty Migrations with Autogenerate<a class="headerlink" href="#don-t-generate-empty-migrations-with-autogenerate" title="Permalink to this headline">¶</a></h2>
A common request is to have the <code class="docutils literal">alembic revision --autogenerate</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">EnvironmentContext.configure.process_revision_directives</code></a>
hook, this is straightforward; place a <code class="docutils literal">process_revision_directives</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">MigrationContext.configure()</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">MigrationScript</code></a> directive if it is empty of
any operations:
<div class="highlight-python"><div class="highlight"><pre>def run_migrations_online():

# ...

def process_revision_directives(context, revision, directives):
 if config.cmd_opts.autogenerate:
 script = directives[0]
 if script.upgrade_ops.is_empty():
 directives[:] = []

# connectable = ...

</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&#8217;t Generate Empty Migrations with Autogenerate</a></li>
</ul>
</li>
</ul>

<h4>Previous topic</h4>
 <a href="ops.html"
 title="previous chapter">Operation Reference</a>
 <h4>Next topic</h4>
 <a href="api/index.html"
 title="next chapter">API Details</a>
 <div role="note" aria-label="source link">
 <h3>This Page</h3>
 <ul class="this-page-menu">
 <li><a href="_sources/cookbook.txt"
 rel="nofollow">Show Source</a></li>
 </ul>
 </div>
<div id="searchbox" style="display: none" role="search">
 <h3>Quick search</h3>
 <form class="search" action="search.html" method="get">
 <input type="text" name="q" />
 <input type="submit" value="Go" />
 <input type="hidden" name="check_keywords" value="yes" />
 <input type="hidden" name="area" value="default" />
 </form>
 
 Enter search terms or a module, class or function name.
 
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
 </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.8.3 documentation</a> &raquo;</li> 
 </ul>
 </div>
 <div class="footer" role="contentinfo">
 &copy; Copyright 2010-2015, Mike Bayer.
 Created using <a href="http://sphinx-doc.org/">Sphinx</a> 1.3.1.
 </div>
 </body>
</html>