JezK

Edit File: cookbook.html

<!DOCTYPE html>

<title>Cookbook &#8212; Alembic 1.8.1 documentation</title>
 <link rel="stylesheet" type="text/css" href="_static/pygments.css" />
 <link rel="stylesheet" type="text/css" href="_static/nature_override.css" />
 <link rel="stylesheet" type="text/css" href="_static/copybutton.css" />
 <link rel="stylesheet" type="text/css" href="_static/changelog.css" />
 <link rel="stylesheet" type="text/css" href="_static/sphinx_paramlinks.css" />
 <script data-url_root="./" id="documentation_options" src="_static/documentation_options.js"></script>
 <script src="_static/jquery.js"></script>
 <script src="_static/underscore.js"></script>
 <script src="_static/doctools.js"></script>
 <script src="_static/clipboard.min.js"></script>
 <script src="_static/copybutton.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 1.8.1 documentation</a> &#187;</li>
 <li class="nav-item nav-item-this"><a href="">Cookbook</a></li> 
 </ul>
 </div>

<div class="document">
 <div class="documentwrapper">
 <div class="bodywrapper">
 <div class="body" role="main">
 
 <section id="cookbook">
<h1>Cookbook<a class="headerlink" href="#cookbook" title="Permalink to this headline">¶</a></h1>
A collection of “How-Tos” highlighting popular ways to extend
Alembic.
<div class="admonition note">
Note
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.
</div>
<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’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 notranslate">create_all()</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.
Alembic can integrate with a <code class="xref py py-meth docutils literal notranslate">create_all()</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 notranslate">head</code>):
<div class="highlight-default notranslate"><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 notranslate">down_revision</code> to <code class="docutils literal notranslate">None</code>:
<div class="highlight-default notranslate"><div class="highlight"><pre># replace this:
#down_revision = &#39;290696571ad2&#39;

# with this:
down_revision = None
</pre></div>
</div>
That file now becomes the “base” of the migration series.
</section>
<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 notranslate">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 notranslate">script.py.mako</code> template to make this feature
available in all new revision files:
<div class="highlight-mako notranslate"><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 notranslate">data_upgrades()</code> and <code class="docutils literal notranslate">data_downgrades()</code>
placeholders will be available, where we can add optional data migrations:
<div class="highlight-default notranslate"><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 notranslate">-x</code> flag:
<div class="highlight-default notranslate"><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 notranslate">EnvironmentContext.get_x_argument()</code></a> is an easy way to support
new commandline options within environment and migration scripts.
</section>
<section id="sharing-a-connection-across-one-or-more-programmatic-migration-commands">
<h2>Sharing a Connection across one or more programmatic migration commands<a class="headerlink" href="#sharing-a-connection-across-one-or-more-programmatic-migration-commands" 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 notranslate">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 <code class="xref py py-class docutils literal notranslate">Connection</code> object to use.</li>
<li>Place it somewhere that <code class="docutils literal notranslate">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 notranslate">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 notranslate">Config</code></a> object).</li>
<li>The <code class="docutils literal notranslate">env.py</code> script is modified such that it looks for this
<code class="xref py py-class docutils literal notranslate">Connection</code> and makes use of it, in lieu
of building up its own <code class="xref py py-class docutils literal notranslate">Engine</code> 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 notranslate">Config.attributes</code></a> a script that will run
the <a class="reference internal" href="api/commands.html#alembic.command.upgrade" title="alembic.command.upgrade"><code class="xref py py-func docutils literal notranslate">command.upgrade()</code></a> command programmatically within a
transaction declared in a Python file:
<div class="highlight-default notranslate"><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 notranslate">env.py</code>:
<div class="highlight-default notranslate"><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)

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

with context.begin_transaction():
 context.run_migrations()
</pre></div>
</div>
</section>
<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.
<div class="admonition seealso">
See also
The Replaceable Object concept has been integrated by the
<a class="reference external" href="https://github.com/olirice/alembic_utils">Alembic Utils</a> project,
which provides autogenerate and migration
support for PostgreSQL functions and views. See
Alembic Utils at <a class="reference external" href="https://github.com/olirice/alembic_utils">https://github.com/olirice/alembic_utils</a> .
</div>
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’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.
<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 “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://github.com/sqlalchemy/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 notranslate">ReplaceableObject</code> that can
represent any named set of SQL text to send to a “CREATE” statement of
some kind:
<div class="highlight-default notranslate"><div class="highlight"><pre>class ReplaceableObject:
 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-default notranslate"><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 notranslate">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.
</section>
<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’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 notranslate">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 notranslate">op.*</code> namespace very nicely.
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 notranslate">ReplaceableObject</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:
<div class="highlight-default notranslate"><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 notranslate">Operations.register_operation()</code></a>
to make them part of the <code class="docutils literal notranslate">op.*</code> namespace:
<div class="highlight-default notranslate"><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.target)

@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 “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 notranslate">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 notranslate">Operations.execute()</code></a>:
<div class="highlight-default notranslate"><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’s
source tree; the only requirement is that when the <code class="docutils literal notranslate">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 notranslate">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 notranslate">Operations.implementation_for()</code></a> sequences.
</section>
<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’ll create a new migration to create a “customer” table:
<div class="highlight-default notranslate"><div class="highlight"><pre>$ alembic revision -m &quot;create table&quot;
</pre></div>
</div>
We build the first revision as follows:
<div class="highlight-default notranslate"><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-default notranslate"><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-default notranslate"><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 notranslate">create_view()</code>, <code class="docutils literal notranslate">create_sp()</code>,
<code class="docutils literal notranslate">drop_view()</code>, and <code class="docutils literal notranslate">drop_sp()</code> directives. Running these to “head”
we get the following (this includes an edited view of SQL emitted):
<div class="highlight-default notranslate"><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.
</section>
<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 “revise” these objects.
Let’s consider we added a new column <code class="docutils literal notranslate">email</code> to our <code class="docutils literal notranslate">customer</code> table:
<div class="highlight-default notranslate"><div class="highlight"><pre>$ alembic revision -m &quot;add email col&quot;
</pre></div>
</div>
The migration is:
<div class="highlight-default notranslate"><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 notranslate">customer_view</code> view and the
<code class="docutils literal notranslate">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 notranslate">replace_view()</code> and <code class="docutils literal notranslate">replace_sp()</code> operations we’ve created make
this possible, by allowing us to refer to a specific, previous revision.
the <code class="docutils literal notranslate">replaces</code> and <code class="docutils literal notranslate">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 notranslate">&quot;28af9800143f.customer_view&quot;</code>. The <code class="docutils literal notranslate">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 notranslate">Operations.get_context()</code></a> method to locate the version file
we refer to:
<div class="highlight-default notranslate"><div class="highlight"><pre>$ alembic revision -m &quot;update views/sp&quot;
</pre></div>
</div>
The migration:
<div class="highlight-default notranslate"><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 notranslate">create_view()</code>, <code class="docutils literal notranslate">create_sp()</code>,
<code class="docutils literal notranslate">drop_view()</code>, and <code class="docutils literal notranslate">drop_sp()</code> methods, we now use <code class="docutils literal notranslate">replace_view()</code> and
<code class="docutils literal notranslate">replace_sp()</code>. The replace operation we’ve built always runs a DROP and
a CREATE. Running an upgrade to head we see:
<div class="highlight-default notranslate"><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 notranslate">email</code> column, we see that both <code class="docutils literal notranslate">customer_view</code>
and <code class="docutils literal notranslate">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-default notranslate"><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>
</section>
</section>
<section id="rudimental-schema-level-multi-tenancy-for-postgresql-databases">
<h2>Rudimental Schema-Level Multi Tenancy for PostgreSQL Databases<a class="headerlink" href="#rudimental-schema-level-multi-tenancy-for-postgresql-databases" title="Permalink to this headline">¶</a></h2>
Multi tenancy refers to an application that accommodates for many
clients simultaneously. Within the scope of a database migrations tool,
multi-tenancy typically refers to the practice of maintaining multiple,
identical databases where each database is assigned to one client.
Alembic does not currently have explicit multi-tenant support; typically,
the approach must involve running Alembic multiple times against different
database URLs.
One common approach to multi-tenancy, particularly on the PostgreSQL database,
is to install tenants within individual PostgreSQL schemas. When using
PostgreSQL’s schemas, a special variable <code class="docutils literal notranslate">search_path</code> is offered that is
intended to assist with targeting of different schemas.
<div class="admonition note">
Note
SQLAlchemy includes a system of directing a common set of
<code class="docutils literal notranslate">Table</code> metadata to many schemas called <a class="reference external" href="https://docs.sqlalchemy.org/core/connections.html#translation-of-schema-names">schema_translate_map</a>. Alembic at the time
of this writing lacks adequate support for this feature. The recipe below
should be considered interim until Alembic has more first-class support
for schema-level multi-tenancy.
</div>
The recipe below can be altered for flexibility. The primary purpose of this
recipe is to illustrate how to point the Alembic process towards one PostgreSQL
schema or another.
<ol class="arabic">
<li>The model metadata used as the target for autogenerate must not include any
schema name for tables; the schema must be non-present or set to <code class="docutils literal notranslate">None</code>.
Otherwise, Alembic autogenerate will still attempt
to compare and render tables in terms of this schema:
<div class="highlight-default notranslate"><div class="highlight"><pre>class A(Base):
 __tablename__ = &#39;a&#39;

id = Column(Integer, primary_key=True)
 data = Column(UnicodeText())
 foo = Column(Integer)

__table_args__ = {
 &quot;schema&quot;: None
 }
</pre></div>
</div>
</li>
<li>The <a class="reference internal" href="api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_schemas" title="alembic.runtime.environment.EnvironmentContext.configure"><code class="xref py py-paramref docutils literal notranslate">EnvironmentContext.configure.include_schemas</code></a> flag must
also be False or not included.</li>
<li>The “tenant” will be a schema name passed to Alembic using the “-x” flag.
In <code class="docutils literal notranslate">env.py</code> an approach like the following allows <code class="docutils literal notranslate">-xtenant=some_schema</code>
to be supported by making use of <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 notranslate">EnvironmentContext.get_x_argument()</code></a>:
<div class="highlight-default notranslate"><div class="highlight"><pre>def run_migrations_online():
 connectable = engine_from_config(
 config.get_section(config.config_ini_section),
 prefix=&quot;sqlalchemy.&quot;,
 poolclass=pool.NullPool,
 )

current_tenant = context.get_x_argument(as_dictionary=True).get(&quot;tenant&quot;)
 with connectable.connect() as connection:

# set search path on the connection, which ensures that
 # PostgreSQL will emit all CREATE / ALTER / DROP statements
 # in terms of this schema by default
 connection.execute(&quot;set search_path to %s&quot; % current_tenant)

# make use of non-supported SQLAlchemy attribute to ensure
 # the dialect reflects tables in terms of the current tenant name
 connection.dialect.default_schema_name = current_tenant

context.configure(
 connection=connection,
 target_metadata=target_metadata,
 )

with context.begin_transaction():
 context.run_migrations()
</pre></div>
</div>
The current tenant is set using the PostgreSQL <code class="docutils literal notranslate">search_path</code> variable on
the connection. Note above we must employ a non-supported SQLAlchemy
workaround at the moment which is to hardcode the SQLAlchemy dialect’s
default schema name to our target schema.
It is also important to note that the above changes remain on the connection
permanently unless reversed explicitly. If the alembic application simply
exits above, there is no issue. However if the application attempts to
continue using the above connection for other purposes, it may be necessary
to reset these variables back to the default, which for PostgreSQL is usually
the name “public” however may be different based on configuration.
</li>
<li>Alembic operations will now proceed in terms of whichever schema we pass
on the command line. All logged SQL will show no schema, except for
reflection operations which will make use of the <code class="docutils literal notranslate">default_schema_name</code>
attribute:
<div class="highlight-default notranslate"><div class="highlight"><pre>[]$ alembic -x tenant=some_schema revision -m &quot;rev1&quot; --autogenerate
</pre></div>
</div>
</li>
<li>Since all schemas are to be maintained in sync, autogenerate should be run
against only one schema, generating new Alembic migration files.
Autogenerate migratin operations are then run against all schemas.</li>
</ol>
</section>
<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>
A common request is to have the <code class="docutils literal notranslate">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 notranslate">EnvironmentContext.configure.process_revision_directives</code></a>
hook, this is straightforward; place a <code class="docutils literal notranslate">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 notranslate">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 notranslate">MigrationScript</code></a> directive if it is empty of
any operations:
<div class="highlight-default notranslate"><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 = ...

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

# ...

from alembic.operations import ops

# process both &quot;def upgrade()&quot;, &quot;def downgrade()&quot;
 for directive in (script.upgrade_ops, script.downgrade_ops):

# make a set of tables that are being dropped within
 # the migration function
 tables_dropped = set()
 for op in directive.ops:
 if isinstance(op, ops.DropTableOp):
 tables_dropped.add((op.table_name, op.schema))

# now rewrite the list of &quot;ops&quot; such that DropIndexOp
 # is removed for those tables. Needs a recursive function.
 directive.ops = list(
 _filter_drop_indexes(directive.ops, tables_dropped)
 )

def _filter_drop_indexes(directives, tables_dropped):
 # given a set of (tablename, schemaname) to be dropped, filter
 # out DropIndexOp from the list of directives and yield the result.

for directive in directives:
 # ModifyTableOps is a container of ALTER TABLE types of
 # commands. process those in place recursively.
 if isinstance(directive, ops.ModifyTableOps) and \
 (directive.table_name, directive.schema) in tables_dropped:
 directive.ops = list(
 _filter_drop_indexes(directive.ops, tables_dropped)
 )

# if we emptied out the directives, then skip the
 # container altogether.
 if not directive.ops:
 continue
 elif isinstance(directive, ops.DropIndexOp) and \
 (directive.table_name, directive.schema) in tables_dropped:
 # we found a target DropIndexOp. keep looping
 continue

# otherwise if not filtered, yield out the directive
 yield directive

# connectable = ...

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

with context.begin_transaction():
 context.run_migrations()
</pre></div>
</div>
Whereas autogenerate, when dropping two tables with a foreign key and
an index, would previously generate something like:
<div class="highlight-default notranslate"><div class="highlight"><pre>def downgrade():
 # ### commands auto generated by Alembic - please adjust! ###
 op.drop_index(op.f(&#39;ix_b_aid&#39;), table_name=&#39;b&#39;)
 op.drop_table(&#39;b&#39;)
 op.drop_table(&#39;a&#39;)
 # ### end Alembic commands ###
</pre></div>
</div>
With the above rewriter, it generates as:
<div class="highlight-default notranslate"><div class="highlight"><pre>def downgrade():
 # ### commands auto generated by Alembic - please adjust! ###
 op.drop_table(&#39;b&#39;)
 op.drop_table(&#39;a&#39;)
 # ### end Alembic commands ###
</pre></div>
</div>
</section>
<section id="don-t-generate-any-drop-table-directives-with-autogenerate">
<h2>Don’t generate any DROP TABLE directives with autogenerate<a class="headerlink" href="#don-t-generate-any-drop-table-directives-with-autogenerate" title="Permalink to this headline">¶</a></h2>
When running autogenerate against a database that has existing tables outside
of the application’s autogenerated metadata, it may be desirable to prevent
autogenerate from considering any of those existing tables to be dropped.
This will prevent autogenerate from detecting tables removed from the
local metadata as well however this is only a small caveat.
The most direct way to achieve this 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 notranslate">EnvironmentContext.configure.include_object</code></a> hook. There is no
need to hardcode a fixed “whitelist” of table names; the hook gives enough
information in the given arguments to determine if a particular table name is
not part of the local <code class="xref py py-class docutils literal notranslate">MetaData</code> being autogenerated, by checking first
that the type of object is <code class="docutils literal notranslate">&quot;table&quot;</code>, then that <code class="docutils literal notranslate">reflected</code> is <code class="docutils literal notranslate">True</code>,
indicating this table name is from the local database connection, not the
<code class="xref py py-class docutils literal notranslate">MetaData</code>, and finally that <code class="docutils literal notranslate">compare_to</code> is <code class="docutils literal notranslate">None</code>, indicating
autogenerate is not comparing this <code class="xref py py-class docutils literal notranslate">Table</code> to any <code class="xref py py-class docutils literal notranslate">Table</code> in
the local <code class="xref py py-class docutils literal notranslate">MetaData</code> collection:
<div class="highlight-default notranslate"><div class="highlight"><pre># in env.py

def include_object(object, name, type_, reflected, compare_to):
 if type_ == &quot;table&quot; and reflected and compare_to is None:
 return False
 else:
 return True

context.configure(
 # ...
 include_object = include_object
)
</pre></div>
</div>
</section>
<section id="apply-custom-sorting-to-table-columns-within-create-table">
<h2>Apply Custom Sorting to Table Columns within CREATE TABLE<a class="headerlink" href="#apply-custom-sorting-to-table-columns-within-create-table" title="Permalink to this headline">¶</a></h2>
This example illustrates use of the <a class="reference internal" href="api/autogenerate.html#alembic.autogenerate.rewriter.Rewriter" title="alembic.autogenerate.rewriter.Rewriter"><code class="xref py py-class docutils literal notranslate">Rewriter</code></a> object introduced
at <a class="reference internal" href="api/autogenerate.html#autogen-rewriter">Fine-Grained Autogenerate Generation with Rewriters</a>. While the rewriter grants access to the
individual <a class="reference internal" href="api/operations.html#alembic.operations.ops.MigrateOperation" title="alembic.operations.ops.MigrateOperation"><code class="xref py py-class docutils literal notranslate">ops.MigrateOperation</code></a> objects, there are sometimes some
special techniques required to get around some structural limitations that
are present.
One is when trying to reorganize the order of columns in a
table within a <a class="reference internal" href="api/operations.html#alembic.operations.ops.CreateTableOp" title="alembic.operations.ops.CreateTableOp"><code class="xref py py-class docutils literal notranslate">ops.CreateTableOp</code></a> directive. This directive, when
generated by autogenerate, actually holds onto the original <code class="xref py py-class docutils literal notranslate">Table</code>
object as the source of its information, so attempting to reorder the
<code class="xref py py-attr docutils literal notranslate">ops.CreateTableOp.columns</code> collection will usually have no effect.
Instead, a new <a class="reference internal" href="api/operations.html#alembic.operations.ops.CreateTableOp" title="alembic.operations.ops.CreateTableOp"><code class="xref py py-class docutils literal notranslate">ops.CreateTableOp</code></a> object may be constructed with the
new ordering. However, a second issue is that the <code class="xref py py-class docutils literal notranslate">Column</code> objects
inside will already be associated with the <code class="xref py py-class docutils literal notranslate">Table</code> that is from the
model being autogenerated, meaning they can’t be reassigned directly to a new
<code class="xref py py-class docutils literal notranslate">Table</code>. To get around this, we can copy all the columns and constraints
using methods like <code class="xref py py-meth docutils literal notranslate">Column.copy()</code>.
Below we use <a class="reference internal" href="api/autogenerate.html#alembic.autogenerate.rewriter.Rewriter" title="alembic.autogenerate.rewriter.Rewriter"><code class="xref py py-class docutils literal notranslate">Rewriter</code></a> to create a new <a class="reference internal" href="api/operations.html#alembic.operations.ops.CreateTableOp" title="alembic.operations.ops.CreateTableOp"><code class="xref py py-class docutils literal notranslate">ops.CreateTableOp</code></a>
directive and to copy the <code class="xref py py-class docutils literal notranslate">Column</code> objects from one into another,
copying each column or constraint object and applying a new sorting scheme:
<div class="highlight-default notranslate"><div class="highlight"><pre># in env.py

from alembic.operations import ops
from alembic.autogenerate import rewriter

writer = rewriter.Rewriter()

@writer.rewrites(ops.CreateTableOp)
def order_columns(context, revision, op):

special_names = {&quot;id&quot;: -100, &quot;created_at&quot;: 1001, &quot;updated_at&quot;: 1002}

cols_by_key = [
 (
 special_names.get(col.key, index)
 if isinstance(col, Column)
 else 2000,
 col.copy(),
 )
 for index, col in enumerate(op.columns)
 ]

columns = [
 col for idx, col in sorted(cols_by_key, key=lambda entry: entry[0])
 ]
 return ops.CreateTableOp(
 op.table_name, columns, schema=op.schema, **op.kw)

# ...

context.configure(
 # ...
 process_revision_directives=writer
)
</pre></div>
</div>
Above, when we apply the <code class="docutils literal notranslate">writer</code> to a table such as:
<div class="highlight-default notranslate"><div class="highlight"><pre>Table(
 &quot;my_table&quot;,
 m,
 Column(&quot;data&quot;, String(50)),
 Column(&quot;created_at&quot;, DateTime),
 Column(&quot;id&quot;, Integer, primary_key=True),
 Column(&quot;updated_at&quot;, DateTime),
 UniqueConstraint(&quot;data&quot;, name=&quot;uq_data&quot;)
)
</pre></div>
</div>
This will render in the autogenerated file as:
<div class="highlight-default notranslate"><div class="highlight"><pre>def upgrade():
 # ### commands auto generated by Alembic - please adjust! ###
 op.create_table(
 &quot;my_table&quot;,
 sa.Column(&quot;id&quot;, sa.Integer(), nullable=False),
 sa.Column(&quot;data&quot;, sa.String(length=50), nullable=True),
 sa.Column(&quot;created_at&quot;, sa.DateTime(), nullable=True),
 sa.Column(&quot;updated_at&quot;, sa.DateTime(), nullable=True),
 sa.PrimaryKeyConstraint(&quot;id&quot;),
 sa.UniqueConstraint(&quot;data&quot;, name=&quot;uq_data&quot;),
 )
 # ### end Alembic commands ###
</pre></div>
</div>
</section>
<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>
It is sometimes convenient to create <code class="xref py py-class docutils literal notranslate">Table</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 notranslate">create_table()</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 notranslate">include_object</code></a> hook to exclude them:
<div class="highlight-default notranslate"><div class="highlight"><pre>my_view = Table(&#39;my_view&#39;, metadata, autoload=True, info=dict(is_view=True)) # Flag this as a view
</pre></div>
</div>
Or, if you use declarative tables:
<div class="highlight-default notranslate"><div class="highlight"><pre>class MyView(Base):
 __tablename__ = &#39;my_view&#39;
 __table_args__ = {&#39;info&#39;: {&#39;is_view&#39;: True}} # Flag this as a view
</pre></div>
</div>
Then define <code class="docutils literal notranslate">include_object</code> as:
<div class="highlight-default notranslate"><div class="highlight"><pre>def include_object(object, name, type_, reflected, compare_to):
 &quot;&quot;&quot;
 Exclude views from Alembic&#39;s consideration.
 &quot;&quot;&quot;

return not object.info.get(&#39;is_view&#39;, False)
</pre></div>
</div>
Finally, in <code class="docutils literal notranslate">env.py</code> pass your <code class="docutils literal notranslate">include_object</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 notranslate">EnvironmentContext.configure()</code></a>.
</section>
<section id="run-multiple-alembic-environments-from-one-ini-file">
<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>
Long before Alembic had the “multiple bases” feature described in <a class="reference internal" href="branches.html#multiple-bases">Working with Multiple Bases</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 notranslate">--name</code> flag on the commandline.
First, one would create an alembic.ini file of this form:
<div class="highlight-default notranslate"><div class="highlight"><pre>[DEFAULT]
# all defaults shared between environments go here

sqlalchemy.url = postgresql://scott:tiger@hostname/mydatabase

[schema1]
# path to env.py and migration scripts for schema1
script_location = myproject/revisions/schema1

[schema2]
# path to env.py and migration scripts for schema2
script_location = myproject/revisions/schema2

[schema3]
# path to env.py and migration scripts for schema3
script_location = myproject/revisions/db2

# this schema uses a different database URL as well
sqlalchemy.url = postgresql://scott:tiger@hostname/myotherdatabase
</pre></div>
</div>
Above, in the <code class="docutils literal notranslate">[DEFAULT]</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 notranslate">env.py</code>
and set of versioning files. Then when we run the <code class="docutils literal notranslate">alembic</code> command,
we simply give it the name of the configuration we want to use:
<div class="highlight-default notranslate"><div class="highlight"><pre>alembic --name schema2 revision -m &quot;new rev for schema 2&quot; --autogenerate
</pre></div>
</div>
Above, the <code class="docutils literal notranslate">alembic</code> command makes use of the configuration in <code class="docutils literal notranslate">[schema2]</code>,
populated with defaults from the <code class="docutils literal notranslate">[DEFAULT]</code> section.
The above approach can be automated by creating a custom front-end to the
Alembic commandline as well.
</section>
<section id="print-python-code-to-generate-particular-database-tables">
<h2>Print Python Code to Generate Particular Database Tables<a class="headerlink" href="#print-python-code-to-generate-particular-database-tables" title="Permalink to this headline">¶</a></h2>
Suppose you have a database already, and want to generate some
<code class="docutils literal notranslate">op.create_table()</code> and other directives that you’d have in a migration file.
How can we automate generating that code?
Suppose the database schema looks like (assume MySQL):
<div class="highlight-default notranslate"><div class="highlight"><pre>CREATE TABLE IF NOT EXISTS `users` (
 `id` int(11) NOT NULL,
 KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `user_properties` (
 `users_id` int(11) NOT NULL,
 `property_name` varchar(255) NOT NULL,
 `property_value` mediumtext NOT NULL,
 UNIQUE KEY `property_name_users_id` (`property_name`,`users_id`),
 KEY `users_id` (`users_id`),
 CONSTRAINT `user_properties_ibfk_1` FOREIGN KEY (`users_id`)
 REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</pre></div>
</div>
Using <a class="reference internal" href="api/operations.html#alembic.operations.ops.UpgradeOps" title="alembic.operations.ops.UpgradeOps"><code class="xref py py-class docutils literal notranslate">ops.UpgradeOps</code></a>, <a class="reference internal" href="api/operations.html#alembic.operations.ops.CreateTableOp" title="alembic.operations.ops.CreateTableOp"><code class="xref py py-class docutils literal notranslate">ops.CreateTableOp</code></a>, and
<a class="reference internal" href="api/operations.html#alembic.operations.ops.CreateIndexOp" title="alembic.operations.ops.CreateIndexOp"><code class="xref py py-class docutils literal notranslate">ops.CreateIndexOp</code></a>, we create a migration file structure,
using <code class="xref py py-class docutils literal notranslate">Table</code> objects that we get from SQLAlchemy reflection.
The structure is passed to <a class="reference internal" href="api/autogenerate.html#alembic.autogenerate.render_python_code" title="alembic.autogenerate.render_python_code"><code class="xref py py-func docutils literal notranslate">autogenerate.render_python_code()</code></a> to
produce the Python code for a migration file:
<div class="highlight-default notranslate"><div class="highlight"><pre>from sqlalchemy import create_engine
from sqlalchemy import MetaData, Table
from alembic import autogenerate
from alembic.operations import ops

e = create_engine(&quot;mysql://scott:tiger@localhost/test&quot;)

with e.connect() as conn:
 m = MetaData()
 user_table = Table(&#39;users&#39;, m, autoload_with=conn)
 user_property_table = Table(&#39;user_properties&#39;, m, autoload_with=conn)

print(autogenerate.render_python_code(
 ops.UpgradeOps(
 ops=[
 ops.CreateTableOp.from_table(table) for table in m.tables.values()
 ] + [
 ops.CreateIndexOp.from_index(idx) for table in m.tables.values()
 for idx in table.indexes
 ]
 ))
)
</pre></div>
</div>
Output:
<div class="highlight-default notranslate"><div class="highlight"><pre># ### commands auto generated by Alembic - please adjust! ###
op.create_table(&#39;users&#39;,
sa.Column(&#39;id&#39;, mysql.INTEGER(display_width=11), autoincrement=False, nullable=False),
mysql_default_charset=&#39;latin1&#39;,
mysql_engine=&#39;InnoDB&#39;
)
op.create_table(&#39;user_properties&#39;,
sa.Column(&#39;users_id&#39;, mysql.INTEGER(display_width=11), autoincrement=False, nullable=False),
sa.Column(&#39;property_name&#39;, mysql.VARCHAR(length=255), nullable=False),
sa.Column(&#39;property_value&#39;, mysql.MEDIUMTEXT(), nullable=False),
sa.ForeignKeyConstraint([&#39;users_id&#39;], [&#39;users.id&#39;], name=&#39;user_properties_ibfk_1&#39;, ondelete=&#39;CASCADE&#39;),
mysql_comment=&#39;user properties&#39;,
mysql_default_charset=&#39;utf8&#39;,
mysql_engine=&#39;InnoDB&#39;
)
op.create_index(&#39;id&#39;, &#39;users&#39;, [&#39;id&#39;], unique=False)
op.create_index(&#39;users_id&#39;, &#39;user_properties&#39;, [&#39;users_id&#39;], unique=False)
op.create_index(&#39;property_name_users_id&#39;, &#39;user_properties&#39;, [&#39;property_name&#39;, &#39;users_id&#39;], unique=True)
# ### end Alembic commands ###
</pre></div>
</div>
</section>
<section id="run-alembic-operation-objects-directly-as-in-from-autogenerate">
<h2>Run Alembic Operation Objects Directly (as in from autogenerate)<a class="headerlink" href="#run-alembic-operation-objects-directly-as-in-from-autogenerate" title="Permalink to this headline">¶</a></h2>
The <a class="reference internal" href="ops.html#alembic.operations.Operations" title="alembic.operations.Operations"><code class="xref py py-class docutils literal notranslate">Operations</code></a> object has a method known as
<a class="reference internal" href="ops.html#alembic.operations.Operations.invoke" title="alembic.operations.Operations.invoke"><code class="xref py py-meth docutils literal notranslate">Operations.invoke()</code></a> that will generically invoke a particular operation
object. We can therefore use the <a class="reference internal" href="api/autogenerate.html#alembic.autogenerate.produce_migrations" title="alembic.autogenerate.produce_migrations"><code class="xref py py-func docutils literal notranslate">autogenerate.produce_migrations()</code></a>
function to run an autogenerate comparison, get back a
<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 notranslate">ops.MigrationScript</code></a> structure representing the changes, and with a
little bit of insider information we can invoke them directly.
The traversal through the <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 notranslate">ops.MigrationScript</code></a> structure is as
follows:
<div class="highlight-default notranslate"><div class="highlight"><pre>use_batch = engine.name == &quot;sqlite&quot;

stack = [migrations.upgrade_ops]
while stack:
 elem = stack.pop(0)

if use_batch and isinstance(elem, ModifyTableOps):
 with operations.batch_alter_table(
 elem.table_name, schema=elem.schema
 ) as batch_ops:
 for table_elem in elem.ops:
 # work around Alembic issue #753 (fixed in 1.5.0)
 if hasattr(table_elem, &quot;column&quot;):
 table_elem.column = table_elem.column.copy()
 batch_ops.invoke(table_elem)

elif hasattr(elem, &quot;ops&quot;):
 stack.extend(elem.ops)
 else:
 # work around Alembic issue #753 (fixed in 1.5.0)
 if hasattr(elem, &quot;column&quot;):
 elem.column = elem.column.copy()
 operations.invoke(elem)
</pre></div>
</div>
Above, we detect elements that have a collection of operations by looking
for the <code class="docutils literal notranslate">.ops</code> attribute. A check for <a class="reference internal" href="api/operations.html#alembic.operations.ops.ModifyTableOps" title="alembic.operations.ops.ModifyTableOps"><code class="xref py py-class docutils literal notranslate">ModifyTableOps</code></a> allows
us to use a batch context if we are supporting that. Finally there’s a
workaround for an Alembic issue that exists for SQLAlchemy 1.3.20 and greater
combined with Alembic older than 1.5.
A full example follows. The overall setup here is copied from the example
at <a class="reference internal" href="api/autogenerate.html#alembic.autogenerate.compare_metadata" title="alembic.autogenerate.compare_metadata"><code class="xref py py-func docutils literal notranslate">autogenerate.compare_metadata()</code></a>:
<div class="highlight-default notranslate"><div class="highlight"><pre>from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table

from alembic.autogenerate import produce_migrations
from alembic.migration import MigrationContext
from alembic.operations import Operations
from alembic.operations.ops import ModifyTableOps

engine = create_engine(&quot;sqlite://&quot;, echo=True)

with engine.connect() as conn:
 conn.execute(
 &quot;&quot;&quot;
 create table foo (
 id integer not null primary key,
 old_data varchar(50),
 x integer
 )&quot;&quot;&quot;
 )

conn.execute(
 &quot;&quot;&quot;
 create table bar (
 data varchar(50)
 )&quot;&quot;&quot;
 )

metadata = MetaData()
Table(
 &quot;foo&quot;,
 metadata,
 Column(&quot;id&quot;, Integer, primary_key=True),
 Column(&quot;data&quot;, Integer),
 Column(&quot;x&quot;, Integer, nullable=False),
)
Table(&quot;bat&quot;, metadata, Column(&quot;info&quot;, String(100)))

mc = MigrationContext.configure(engine.connect())

migrations = produce_migrations(mc, metadata)

operations = Operations(mc)

use_batch = engine.name == &quot;sqlite&quot;

elif hasattr(elem, &quot;ops&quot;):
 stack.extend(elem.ops)
 else:
 # work around Alembic issue #753 (fixed in 1.5.0)
 if hasattr(elem, &quot;column&quot;):
 elem.column = elem.column.copy()
 operations.invoke(elem)
</pre></div>
</div>
</section>
<section id="test-current-database-revision-is-at-head-s">
<h2>Test current database revision is at head(s)<a class="headerlink" href="#test-current-database-revision-is-at-head-s" title="Permalink to this headline">¶</a></h2>
A recipe to determine if a database schema is up to date in terms of applying
Alembic migrations. May be useful for test or installation suites to
determine if the target database is up to date. Makes use of the
<a class="reference internal" href="api/runtime.html#alembic.runtime.migration.MigrationContext.get_current_heads" title="alembic.runtime.migration.MigrationContext.get_current_heads"><code class="xref py py-meth docutils literal notranslate">MigrationContext.get_current_heads()</code></a> as well as
<a class="reference internal" href="api/script.html#alembic.script.ScriptDirectory.get_heads" title="alembic.script.ScriptDirectory.get_heads"><code class="xref py py-meth docutils literal notranslate">ScriptDirectory.get_heads()</code></a> methods so that it accommodates for a
branched revision tree:
<div class="highlight-default notranslate"><div class="highlight"><pre>from alembic import config, script
from alembic.runtime import migration
from sqlalchemy import engine

def check_current_head(alembic_cfg, connectable):
 # type: (config.Config, engine.Engine) -&gt; bool
 directory = script.ScriptDirectory.from_config(alembic_cfg)
 with connectable.begin() as connection:
 context = migration.MigrationContext.configure(connection)
 return set(context.get_current_heads()) == set(directory.get_heads())

e = engine.create_engine(&quot;mysql://scott:tiger@localhost/test&quot;, echo=True)
cfg = config.Config(&quot;alembic.ini&quot;)
print(check_current_head(cfg, e))
</pre></div>
</div>
<div class="admonition seealso">
See also
<a class="reference internal" href="api/runtime.html#alembic.runtime.migration.MigrationContext.get_current_heads" title="alembic.runtime.migration.MigrationContext.get_current_heads"><code class="xref py py-meth docutils literal notranslate">MigrationContext.get_current_heads()</code></a>
<a class="reference internal" href="api/script.html#alembic.script.ScriptDirectory.get_heads" title="alembic.script.ScriptDirectory.get_heads"><code class="xref py py-meth docutils literal notranslate">ScriptDirectory.get_heads()</code></a>
</div>
</section>
<section id="using-asyncio-with-alembic">
<h2>Using Asyncio with Alembic<a class="headerlink" href="#using-asyncio-with-alembic" title="Permalink to this headline">¶</a></h2>
SQLAlchemy version 1.4 introduced experimental support for asyncio, allowing
use of most of its interface from async applications. Alembic currently does
not provide an async api directly, but it can use an use SQLAlchemy Async
engine to run the migrations and autogenerate.
New configurations can use the template “async” to bootstrap an environment which
can be used with async DBAPI like asyncpg, running the command:
<div class="highlight-default notranslate"><div class="highlight"><pre>alembic init -t async &lt;script_directory_here&gt;
</pre></div>
</div>
Existing configurations can be updated to use an async DBAPI by updating the <code class="docutils literal notranslate">env.py</code>
file that’s used by Alembic to start its operations. In particular only
<code class="docutils literal notranslate">run_migrations_online</code> will need to be updated to be something like the example below:
<div class="highlight-default notranslate"><div class="highlight"><pre>import asyncio

from sqlalchemy.ext.asyncio import async_engine_from_config

# ... no change required to the rest of the code

def do_run_migrations(connection):
 context.configure(connection=connection, target_metadata=target_metadata)

async def run_migrations_online():
 &quot;&quot;&quot;Run migrations in &#39;online&#39; mode.

In this scenario we need to create an Engine
 and associate a connection with the context.

&quot;&quot;&quot;
 connectable = async_engine_from_config(
 config.get_section(config.config_ini_section),
 prefix=&quot;sqlalchemy.&quot;,
 poolclass=pool.NullPool,
 )

async with connectable.connect() as connection:
 await connection.run_sync(do_run_migrations)

await connectable.dispose()

if context.is_offline_mode():
 run_migrations_offline()
else:
 asyncio.run(run_migrations_online())
</pre></div>
</div>
An async application can also interact with the Alembic api directly by using
the SQLAlchemy <code class="docutils literal notranslate">run_sync</code> method to adapt the non-async api of Alembic to
an async consumer.
<section id="programmatic-api-use-connection-sharing-with-asyncio">
<h3>Programmatic API use (connection sharing) With Asyncio<a class="headerlink" href="#programmatic-api-use-connection-sharing-with-asyncio" title="Permalink to this headline">¶</a></h3>
Combining the examples of <a class="reference internal" href="#connection-sharing">Sharing a Connection across one or more programmatic migration commands</a> with <a class="reference internal" href="#asyncio-recipe">Using Asyncio with Alembic</a>
together, and <code class="docutils literal notranslate">env.py</code> as follows works:
<div class="highlight-default notranslate"><div class="highlight"><pre>import asyncio

from sqlalchemy.ext.asyncio import async_engine_from_config

# ... no change required to the rest of the code

async def run_async_migrations():
 connectable = async_engine_from_config(
 config.get_section(config.config_ini_section),
 prefix=&quot;sqlalchemy.&quot;,
 poolclass=pool.NullPool,
 )

await connectable.dispose()

def run_migrations_online():
 &quot;&quot;&quot;Run migrations in &#39;online&#39; mode.

In this scenario we need to create an Engine
 and associate a connection with the context.

&quot;&quot;&quot;

connectable = config.attributes.get(&quot;connection&quot;, None)

if connectable is None:
 asyncio.run(run_async_migrations())
 else:
 do_run_migrations(connectable)
</pre></div>
</div>
Above, using an asyncio database URL in <code class="docutils literal notranslate">alembic.ini</code> one can run
commands such as <code class="docutils literal notranslate">alembic upgrade</code> from the command line. Programmatically,
the same <code class="docutils literal notranslate">env.py</code> file can be invoked using asyncio as:
<div class="highlight-default notranslate"><div class="highlight"><pre>import asyncio
from sqlalchemy.ext.asyncio import create_async_engine

from alembic import command, config

def run_upgrade(connection, cfg):
 cfg.attributes[&quot;connection&quot;] = connection
 command.upgrade(cfg, &quot;head&quot;)

async def run_async_upgrade():
 async_engine = create_async_engine(&quot;sqlite+aiosqlite://&quot;, echo=True)
 async with async_engine.begin() as conn:
 await conn.run_sync(run_upgrade, config.Config(&quot;alembic.ini&quot;))

asyncio.run(run_async_upgrade())
</pre></div>
</div>
</section>
</section>
</section>

<div class="clearer"></div>
 </div>
 </div>
 </div>
 <div class="sphinxsidebar" role="navigation" aria-label="main navigation">
 <div class="sphinxsidebarwrapper">
 <div>
 <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-across-one-or-more-programmatic-migration-commands">Sharing a Connection across one or more programmatic migration commands</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="#rudimental-schema-level-multi-tenancy-for-postgresql-databases">Rudimental Schema-Level Multi Tenancy for PostgreSQL Databases</a></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-drop-index-when-the-table-is-to-be-dropped-as-well">Don’t emit DROP INDEX when the table is to be dropped as well</a></li>
<li><a class="reference internal" href="#don-t-generate-any-drop-table-directives-with-autogenerate">Don’t generate any DROP TABLE directives with autogenerate</a></li>
<li><a class="reference internal" href="#apply-custom-sorting-to-table-columns-within-create-table">Apply Custom Sorting to Table Columns within CREATE TABLE</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>
<li><a class="reference internal" href="#print-python-code-to-generate-particular-database-tables">Print Python Code to Generate Particular Database Tables</a></li>
<li><a class="reference internal" href="#run-alembic-operation-objects-directly-as-in-from-autogenerate">Run Alembic Operation Objects Directly (as in from autogenerate)</a></li>
<li><a class="reference internal" href="#test-current-database-revision-is-at-head-s">Test current database revision is at head(s)</a></li>
<li><a class="reference internal" href="#using-asyncio-with-alembic">Using Asyncio with Alembic</a><ul>
<li><a class="reference internal" href="#programmatic-api-use-connection-sharing-with-asyncio">Programmatic API use (connection sharing) With Asyncio</a></li>
</ul>
</li>
</ul>
</li>
</ul>

</div>
<div id="searchbox" style="display: none" role="search">
 <h3 id="searchlabel">Quick search</h3>
 <div class="searchformwrapper">
 <form class="search" action="search.html" method="get">
 <input type="text" name="q" aria-labelledby="searchlabel" autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false"/>
 <input type="submit" value="Go" />
 </form>
 </div>
</div>
<script>$('#searchbox').show(0);</script>
 <div>
 <h4>Previous topic</h4>
 <a href="ops.html"
 title="previous chapter">Operation Reference</a>
 </div>
 <div>
 <h4>Next topic</h4>
 <a href="api/index.html"
 title="next chapter">API Details</a>
 </div>
 </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 1.8.1 documentation</a> &#187;</li>
 <li class="nav-item nav-item-this"><a href="">Cookbook</a></li> 
 </ul>
 </div>
 <div class="footer" role="contentinfo">
 &#169; Copyright 2010-2022, Mike Bayer.
 Created using <a href="https://www.sphinx-doc.org/">Sphinx</a> 4.5.0.
 </div>
 </body>
</html>

Fairnetzt-Hochrhein | Vernetzung . Nachhaltigkeit . Gemeinschaft

VERNETZUNG

NACHHALTIGKEIT

GEMEINSCHAFT

VERANTWORTUNG

Willkommen auf Fairnetzt-Hochrhein,

Auf beiden Seiten der Rheingrenze gibt es viele Initiativen, Organisationen, Vereine und auch engagierte Einzelpersonen, welche sich für eine nachhaltige, lebenswerte Zukunft in unserer Region und darüber hinaus einsetzen.

Mit Fairnetzt-Hochrhein.de wollen wir eine Plattform bieten, welche auf lebendige und unkomplizierte Weise es Gruppen aus dem Landkreis Waldshut und aus den Kantonen Aargau und Schaffhausen ermöglicht, Veranstaltungen zu veröffentlichen und andererseits interessierte Menschen erfahren können, was wo stattfindet.

Wir wünschen Euch und uns allen
Ein Gutes Leben und Freude am Abenteuer der Mitgestaltung unserer eigenen Zukunft.

Das Redaktionsteam von Fairnetzt Hochrhein

Direktlink zu: Ihre Veranstaltungen einstellen? oder Anmeldung zum Newsletter?

Veranstaltungen

Dritter Nachhaltigkeitsstammtisch in Dogern

Ziel der Treffen ist, miteinander und voneinander zu lernen, wie man sich in verschiedenen Alltagsbereichen nachhaltiger verhalten kann:
• Einkauf von Dingen des täglichen und langfristigen Bedarfs
• Essen
• Energie sparen und erzeugen
• Unterwegs sein und Reisen, Mobilität
• Informationsaustausch
• …………
Jede*r ist willkommen, sich mit seinen und ihren Fragen und Erfahrungen einzubringen.
Grundidee: wir sind alle in bestimmten Lebensbereichen „Expert*innen“ und können so andere mit unserem Wissen bereichern und bei der Umsetzung im Alltag unterstützen.