JezK
Edit File: tutorial.html
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="generator" content="Docutils 0.17.1: http://docutils.sourceforge.net/" /> <title>Tutorial — 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="Auto Generating Migrations" href="autogenerate.html" /> <link rel="prev" title="Front Matter" href="front.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="autogenerate.html" title="Auto Generating Migrations" accesskey="N">next</a> |</li> <li class="right" > <a href="front.html" title="Front Matter" accesskey="P">previous</a> |</li> <li class="nav-item nav-item-0"><a href="index.html">Alembic 1.8.1 documentation</a> »</li> <li class="nav-item nav-item-this"><a href="">Tutorial</a></li> </ul> </div> <div class="document"> <div class="documentwrapper"> <div class="bodywrapper"> <div class="body" role="main"> <section id="tutorial"> <h1>Tutorial<a class="headerlink" href="#tutorial" title="Permalink to this headline">¶</a></h1> <p>Alembic provides for the creation, management, and invocation of <em>change management</em> scripts for a relational database, using SQLAlchemy as the underlying engine. This tutorial will provide a full introduction to the theory and usage of this tool.</p> <p>To begin, make sure Alembic is installed as described at <a class="reference internal" href="front.html#installation"><span class="std std-ref">Installation</span></a>. As stated in the linked document, it is usually preferable that Alembic is installed in the <strong>same module / Python path as that of the target project</strong>, usually using a <a class="reference external" href="https://docs.python.org/3/tutorial/venv.html">Python virtual environment</a>, so that when the <code class="docutils literal notranslate"><span class="pre">alembic</span></code> command is run, the Python script which is invoked by <code class="docutils literal notranslate"><span class="pre">alembic</span></code>, namely your project’s <code class="docutils literal notranslate"><span class="pre">env.py</span></code> script, will have access to your application’s models. This is not strictly necessary in all cases, however in the vast majority of cases is usually preferred.</p> <p>The tutorial below assumes the <code class="docutils literal notranslate"><span class="pre">alembic</span></code> command line utility is present in the local path and when invoked, will have access to the same Python module environment as that of the target project.</p> <section id="the-migration-environment"> <h2>The Migration Environment<a class="headerlink" href="#the-migration-environment" title="Permalink to this headline">¶</a></h2> <p>Usage of Alembic starts with creation of the <em>Migration Environment</em>. This is a directory of scripts that is specific to a particular application. The migration environment is created just once, and is then maintained along with the application’s source code itself. The environment is created using the <code class="docutils literal notranslate"><span class="pre">init</span></code> command of Alembic, and is then customizable to suit the specific needs of the application.</p> <p>The structure of this environment, including some generated migration scripts, looks like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">yourproject</span><span class="o">/</span> <span class="n">alembic</span><span class="o">/</span> <span class="n">env</span><span class="o">.</span><span class="n">py</span> <span class="n">README</span> <span class="n">script</span><span class="o">.</span><span class="n">py</span><span class="o">.</span><span class="n">mako</span> <span class="n">versions</span><span class="o">/</span> <span class="mi">3512</span><span class="n">b954651e_add_account</span><span class="o">.</span><span class="n">py</span> <span class="mi">2</span><span class="n">b1ae634e5cd_add_order_id</span><span class="o">.</span><span class="n">py</span> <span class="mi">3</span><span class="n">adcc9a56557_rename_username_field</span><span class="o">.</span><span class="n">py</span> </pre></div> </div> <p>The directory includes these directories/files:</p> <ul> <li><p><code class="docutils literal notranslate"><span class="pre">yourproject</span></code> - this is the root of your application’s source code, or some directory within it.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">alembic</span></code> - this directory lives within your application’s source tree and is the home of the migration environment. It can be named anything, and a project that uses multiple databases may even have more than one.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">env.py</span></code> - This is a Python script that is run whenever the alembic migration tool is invoked. At the very least, it contains instructions to configure and generate a SQLAlchemy engine, procure a connection from that engine along with a transaction, and then invoke the migration engine, using the connection as a source of database connectivity.</p> <p>The <code class="docutils literal notranslate"><span class="pre">env.py</span></code> script is part of the generated environment so that the way migrations run is entirely customizable. The exact specifics of how to connect are here, as well as the specifics of how the migration environment are invoked. The script can be modified so that multiple engines can be operated upon, custom arguments can be passed into the migration environment, application-specific libraries and models can be loaded in and made available.</p> <p>Alembic includes a set of initialization templates which feature different varieties of <code class="docutils literal notranslate"><span class="pre">env.py</span></code> for different use cases.</p> </li> <li><p><code class="docutils literal notranslate"><span class="pre">README</span></code> - included with the various environment templates, should have something informative.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">script.py.mako</span></code> - This is a <a class="reference external" href="http://www.makotemplates.org">Mako</a> template file which is used to generate new migration scripts. Whatever is here is used to generate new files within <code class="docutils literal notranslate"><span class="pre">versions/</span></code>. This is scriptable so that the structure of each migration file can be controlled, including standard imports to be within each, as well as changes to the structure of the <code class="docutils literal notranslate"><span class="pre">upgrade()</span></code> and <code class="docutils literal notranslate"><span class="pre">downgrade()</span></code> functions. For example, the <code class="docutils literal notranslate"><span class="pre">multidb</span></code> environment allows for multiple functions to be generated using a naming scheme <code class="docutils literal notranslate"><span class="pre">upgrade_engine1()</span></code>, <code class="docutils literal notranslate"><span class="pre">upgrade_engine2()</span></code>.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">versions/</span></code> - This directory holds the individual version scripts. Users of other migration tools may notice that the files here don’t use ascending integers, and instead use a partial GUID approach. In Alembic, the ordering of version scripts is relative to directives within the scripts themselves, and it is theoretically possible to “splice” version files in between others, allowing migration sequences from different branches to be merged, albeit carefully by hand.</p></li> </ul> </section> <section id="creating-an-environment"> <h2>Creating an Environment<a class="headerlink" href="#creating-an-environment" title="Permalink to this headline">¶</a></h2> <p>With a basic understanding of what the environment is, we can create one using <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">init</span></code>. This will create an environment using the “generic” template:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ cd /path/to/yourproject $ source /path/to/yourproject/.venv/bin/activate # assuming a local virtualenv $ alembic init alembic </pre></div> </div> <p>Where above, the <code class="docutils literal notranslate"><span class="pre">init</span></code> command was called to generate a migrations directory called <code class="docutils literal notranslate"><span class="pre">alembic</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Creating</span> <span class="n">directory</span> <span class="o">/</span><span class="n">path</span><span class="o">/</span><span class="n">to</span><span class="o">/</span><span class="n">yourproject</span><span class="o">/</span><span class="n">alembic</span><span class="o">...</span><span class="n">done</span> <span class="n">Creating</span> <span class="n">directory</span> <span class="o">/</span><span class="n">path</span><span class="o">/</span><span class="n">to</span><span class="o">/</span><span class="n">yourproject</span><span class="o">/</span><span class="n">alembic</span><span class="o">/</span><span class="n">versions</span><span class="o">...</span><span class="n">done</span> <span class="n">Generating</span> <span class="o">/</span><span class="n">path</span><span class="o">/</span><span class="n">to</span><span class="o">/</span><span class="n">yourproject</span><span class="o">/</span><span class="n">alembic</span><span class="o">.</span><span class="n">ini</span><span class="o">...</span><span class="n">done</span> <span class="n">Generating</span> <span class="o">/</span><span class="n">path</span><span class="o">/</span><span class="n">to</span><span class="o">/</span><span class="n">yourproject</span><span class="o">/</span><span class="n">alembic</span><span class="o">/</span><span class="n">env</span><span class="o">.</span><span class="n">py</span><span class="o">...</span><span class="n">done</span> <span class="n">Generating</span> <span class="o">/</span><span class="n">path</span><span class="o">/</span><span class="n">to</span><span class="o">/</span><span class="n">yourproject</span><span class="o">/</span><span class="n">alembic</span><span class="o">/</span><span class="n">README</span><span class="o">...</span><span class="n">done</span> <span class="n">Generating</span> <span class="o">/</span><span class="n">path</span><span class="o">/</span><span class="n">to</span><span class="o">/</span><span class="n">yourproject</span><span class="o">/</span><span class="n">alembic</span><span class="o">/</span><span class="n">script</span><span class="o">.</span><span class="n">py</span><span class="o">.</span><span class="n">mako</span><span class="o">...</span><span class="n">done</span> <span class="n">Please</span> <span class="n">edit</span> <span class="n">configuration</span><span class="o">/</span><span class="n">connection</span><span class="o">/</span><span class="n">logging</span> <span class="n">settings</span> <span class="ow">in</span> <span class="s1">'/path/to/yourproject/alembic.ini'</span> <span class="n">before</span> <span class="n">proceeding</span><span class="o">.</span> </pre></div> </div> <p>Alembic also includes other environment templates. These can be listed out using the <code class="docutils literal notranslate"><span class="pre">list_templates</span></code> command:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic list_templates Available templates: generic - Generic single-database configuration. async - Generic single-database configuration with an async dbapi. multidb - Rudimentary multi-database configuration. Templates are used via the 'init' command, e.g.: alembic init --template generic ./scripts </pre></div> </div> <div class="versionchanged"> <p><span class="versionmodified changed">Changed in version 1.8: </span>The “pylons” environment template has been removed.</p> </div> </section> <section id="editing-the-ini-file"> <h2>Editing the .ini File<a class="headerlink" href="#editing-the-ini-file" title="Permalink to this headline">¶</a></h2> <p>Alembic placed a file <code class="docutils literal notranslate"><span class="pre">alembic.ini</span></code> into the current directory. This is a file that the <code class="docutils literal notranslate"><span class="pre">alembic</span></code> script looks for when invoked. This file can exist in a different directory, with the location to it specified by either the <code class="docutils literal notranslate"><span class="pre">--config</span></code> option for the <code class="docutils literal notranslate"><span class="pre">alembic</span></code> runner or the <code class="docutils literal notranslate"><span class="pre">ALEMBIC_CONFIG</span></code> environment variable (the former takes precedence).</p> <p>The file generated with the “generic” configuration looks like:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span># A generic, single database configuration. [alembic] # path to migration scripts script_location = alembic # template used to generate migration file names; The default value is %%(rev)s_%%(slug)s # Uncomment the line below if you want the files to be prepended with date and time # file_template = %%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d%%(minute).2d-%%(rev)s_%%(slug)s # sys.path path, will be prepended to sys.path if present. # defaults to the current working directory. # (new in 1.5.5) prepend_sys_path = . # timezone to use when rendering the date within the migration file # as well as the filename. # If specified, requires the python-dateutil library that can be # installed by adding `alembic[tz]` to the pip requirements # string value is passed to dateutil.tz.gettz() # leave blank for localtime # timezone = # max length of characters to apply to the # "slug" field # truncate_slug_length = 40 # set to 'true' to run the environment during # the 'revision' command, regardless of autogenerate # revision_environment = false # set to 'true' to allow .pyc and .pyo files without # a source .py file to be detected as revisions in the # versions/ directory # sourceless = false # version location specification; This defaults # to ${script_location}/versions. When using multiple version # directories, initial revisions must be specified with --version-path. # The path separator used here should be the separator specified by "version_path_separator" below. # version_locations = %(here)s/bar:%(here)s/bat:${script_location}/versions # version path separator; As mentioned above, this is the character used to split # version_locations. The default within new alembic.ini files is "os", which uses os.pathsep. # If this key is omitted entirely, it falls back to the legacy behavior of splitting on spaces and/or commas. # Valid values for version_path_separator are: # # version_path_separator = : # version_path_separator = ; # version_path_separator = space version_path_separator = os # Use os.pathsep. Default configuration used for new projects. # the output encoding used when revision files # are written from script.py.mako # output_encoding = utf-8 sqlalchemy.url = driver://user:pass@localhost/dbname # [post_write_hooks] # This section defines scripts or Python functions that are run # on newly generated revision scripts. See the documentation for further # detail and examples # format using "black" - use the console_scripts runner, # against the "black" entrypoint # hooks = black # black.type = console_scripts # black.entrypoint = black # black.options = -l 79 REVISION_SCRIPT_FILENAME # Logging configuration [loggers] keys = root,sqlalchemy,alembic [handlers] keys = console [formatters] keys = generic [logger_root] level = WARN handlers = console qualname = [logger_sqlalchemy] level = WARN handlers = qualname = sqlalchemy.engine [logger_alembic] level = INFO handlers = qualname = alembic [handler_console] class = StreamHandler args = (sys.stderr,) level = NOTSET formatter = generic [formatter_generic] format = %(levelname)-5.5s [%(name)s] %(message)s datefmt = %H:%M:%S </pre></div> </div> <p>The file is read using Python’s <code class="xref py py-class docutils literal notranslate"><span class="pre">ConfigParser.SafeConfigParser</span></code> object. The <code class="docutils literal notranslate"><span class="pre">%(here)s</span></code> variable is provided as a substitution variable, which can be used to produce absolute pathnames to directories and files, as we do above with the path to the Alembic script location.</p> <p>This file contains the following features:</p> <ul> <li><p><code class="docutils literal notranslate"><span class="pre">[alembic]</span></code> - this is the section read by Alembic to determine configuration. Alembic’s core implementation does not directly read any other areas of the file, not including additional directives that may be consumed from the end-user-customizable <code class="docutils literal notranslate"><span class="pre">env.py</span></code> file (see note below). The name “alembic” can be customized using the <code class="docutils literal notranslate"><span class="pre">--name</span></code> commandline flag; see <a class="reference internal" href="cookbook.html#multiple-environments"><span class="std std-ref">Run Multiple Alembic Environments from one .ini file</span></a> for a basic example of this.</p> <div class="admonition note"> <p class="admonition-title">Note</p> <p>The default <code class="docutils literal notranslate"><span class="pre">env.py</span></code> file included with Alembic’s environment templates will also read from the logging sections <code class="docutils literal notranslate"><span class="pre">[logging]</span></code>, <code class="docutils literal notranslate"><span class="pre">[handlers]</span></code> etc. If the configuration file in use does not contain logging directives, please remove the <code class="docutils literal notranslate"><span class="pre">fileConfig()</span></code> directive within the generated <code class="docutils literal notranslate"><span class="pre">env.py</span></code> file to prevent it from attempting to configure logging.</p> </div> </li> <li><p><code class="docutils literal notranslate"><span class="pre">script_location</span></code> - this is the location of the Alembic environment. It is normally specified as a filesystem location, either relative or absolute. If the location is a relative path, it’s interpreted as relative to the current directory.</p> <p>This is the only key required by Alembic in all cases. The generation of the .ini file by the command <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">init</span> <span class="pre">alembic</span></code> automatically placed the directory name <code class="docutils literal notranslate"><span class="pre">alembic</span></code> here. The special variable <code class="docutils literal notranslate"><span class="pre">%(here)s</span></code> can also be used, as in <code class="docutils literal notranslate"><span class="pre">%(here)s/alembic</span></code>.</p> <p>For support of applications that package themselves into .egg files, the value can also be specified as a <a class="reference external" href="https://setuptools.readthedocs.io/en/latest/pkg_resources.html">package resource</a>, in which case <code class="docutils literal notranslate"><span class="pre">resource_filename()</span></code> is used to find the file (new in 0.2.2). Any non-absolute URI which contains colons is interpreted here as a resource name, rather than a straight filename.</p> </li> <li><p><code class="docutils literal notranslate"><span class="pre">file_template</span></code> - this is the naming scheme used to generate new migration files. Uncomment the presented value if you would like the migration files to be prepended with date and time, so that they are listed in chronological order. The default value is <code class="docutils literal notranslate"><span class="pre">%%(rev)s_%%(slug)s</span></code>. Tokens available include:</p> <blockquote> <div><ul class="simple"> <li><p><code class="docutils literal notranslate"><span class="pre">%%(rev)s</span></code> - revision id</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">%%(slug)s</span></code> - a truncated string derived from the revision message</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">%%(epoch)s</span></code> - epoch timestamp based on the create date; this makes use of the Python <code class="docutils literal notranslate"><span class="pre">datetime.timestamp()</span></code> method to produce an epoch value.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">%%(year)d</span></code>, <code class="docutils literal notranslate"><span class="pre">%%(month).2d</span></code>, <code class="docutils literal notranslate"><span class="pre">%%(day).2d</span></code>, <code class="docutils literal notranslate"><span class="pre">%%(hour).2d</span></code>, <code class="docutils literal notranslate"><span class="pre">%%(minute).2d</span></code>, <code class="docutils literal notranslate"><span class="pre">%%(second).2d</span></code> - components of the create date, by default <code class="docutils literal notranslate"><span class="pre">datetime.datetime.now()</span></code> unless the <code class="docutils literal notranslate"><span class="pre">timezone</span></code> configuration option is also used.</p></li> </ul> </div></blockquote> <div class="versionadded"> <p><span class="versionmodified added">New in version 1.8: </span>added ‘epoch’</p> </div> </li> <li><p><code class="docutils literal notranslate"><span class="pre">timezone</span></code> - an optional timezone name (e.g. <code class="docutils literal notranslate"><span class="pre">UTC</span></code>, <code class="docutils literal notranslate"><span class="pre">EST5EDT</span></code>, etc.) that will be applied to the timestamp which renders inside the migration file’s comment as well as within the filename. This option requires installing the <code class="docutils literal notranslate"><span class="pre">python-dateutil</span></code> library. If <code class="docutils literal notranslate"><span class="pre">timezone</span></code> is specified, the create date object is no longer derived from <code class="docutils literal notranslate"><span class="pre">datetime.datetime.now()</span></code> and is instead generated as:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">datetime</span><span class="o">.</span><span class="n">datetime</span><span class="o">.</span><span class="n">utcnow</span><span class="p">()</span><span class="o">.</span><span class="n">replace</span><span class="p">(</span> <span class="n">tzinfo</span><span class="o">=</span><span class="n">dateutil</span><span class="o">.</span><span class="n">tz</span><span class="o">.</span><span class="n">tzutc</span><span class="p">()</span> <span class="p">)</span><span class="o">.</span><span class="n">astimezone</span><span class="p">(</span> <span class="n">dateutil</span><span class="o">.</span><span class="n">tz</span><span class="o">.</span><span class="n">gettz</span><span class="p">(</span><span class="o"><</span><span class="n">timezone</span><span class="o">></span><span class="p">)</span> <span class="p">)</span> </pre></div> </div> </li> <li><p><code class="docutils literal notranslate"><span class="pre">truncate_slug_length</span></code> - defaults to 40, the max number of characters to include in the “slug” field.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">sqlalchemy.url</span></code> - A URL to connect to the database via SQLAlchemy. This configuration value is only used if the <code class="docutils literal notranslate"><span class="pre">env.py</span></code> file calls upon them; in the “generic” template, the call to <code class="docutils literal notranslate"><span class="pre">config.get_main_option("sqlalchemy.url")</span></code> in the <code class="docutils literal notranslate"><span class="pre">run_migrations_offline()</span></code> function and the call to <code class="docutils literal notranslate"><span class="pre">engine_from_config(prefix="sqlalchemy.")</span></code> in the <code class="docutils literal notranslate"><span class="pre">run_migrations_online()</span></code> function are where this key is referenced. If the SQLAlchemy URL should come from some other source, such as from environment variables or a global registry, or if the migration environment makes use of multiple database URLs, the developer is encouraged to alter the <code class="docutils literal notranslate"><span class="pre">env.py</span></code> file to use whatever methods are appropriate in order to acquire the database URL or URLs.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">revision_environment</span></code> - this is a flag which when set to the value ‘true’, will indicate that the migration environment script <code class="docutils literal notranslate"><span class="pre">env.py</span></code> should be run unconditionally when generating new revision files, as well as when running the <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">history</span></code> command.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">sourceless</span></code> - when set to ‘true’, revision files that only exist as .pyc or .pyo files in the versions directory will be used as versions, allowing “sourceless” versioning folders. When left at the default of ‘false’, only .py files are consumed as version files.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">version_locations</span></code> - an optional list of revision file locations, to allow revisions to exist in multiple directories simultaneously. See <a class="reference internal" href="branches.html#multiple-bases"><span class="std std-ref">Working with Multiple Bases</span></a> for examples.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">version_path_separator</span></code> - a separator of <code class="docutils literal notranslate"><span class="pre">version_locations</span></code> paths. It should be defined if multiple <code class="docutils literal notranslate"><span class="pre">version_locations</span></code> is used. See <a class="reference internal" href="branches.html#multiple-bases"><span class="std std-ref">Working with Multiple Bases</span></a> for examples.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">output_encoding</span></code> - the encoding to use when Alembic writes the <code class="docutils literal notranslate"><span class="pre">script.py.mako</span></code> file into a new migration file. Defaults to <code class="docutils literal notranslate"><span class="pre">'utf-8'</span></code>.</p></li> <li><p><code class="docutils literal notranslate"><span class="pre">[loggers]</span></code>, <code class="docutils literal notranslate"><span class="pre">[handlers]</span></code>, <code class="docutils literal notranslate"><span class="pre">[formatters]</span></code>, <code class="docutils literal notranslate"><span class="pre">[logger_*]</span></code>, <code class="docutils literal notranslate"><span class="pre">[handler_*]</span></code>, <code class="docutils literal notranslate"><span class="pre">[formatter_*]</span></code> - these sections are all part of Python’s standard logging configuration, the mechanics of which are documented at <a class="reference external" href="http://docs.python.org/library/logging.config.html#configuration-file-format">Configuration File Format</a>. As is the case with the database connection, these directives are used directly as the result of the <code class="docutils literal notranslate"><span class="pre">logging.config.fileConfig()</span></code> call present in the <code class="docutils literal notranslate"><span class="pre">env.py</span></code> script, which you’re free to modify.</p></li> </ul> <p>For starting up with just a single database and the generic configuration, setting up the SQLAlchemy URL is all that’s needed:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">sqlalchemy</span><span class="o">.</span><span class="n">url</span> <span class="o">=</span> <span class="n">postgresql</span><span class="p">:</span><span class="o">//</span><span class="n">scott</span><span class="p">:</span><span class="n">tiger</span><span class="nd">@localhost</span><span class="o">/</span><span class="n">test</span> </pre></div> </div> </section> <section id="create-a-migration-script"> <span id="create-migration"></span><h2>Create a Migration Script<a class="headerlink" href="#create-a-migration-script" title="Permalink to this headline">¶</a></h2> <p>With the environment in place we can create a new revision, using <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">revision</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic revision -m "create account table" Generating /path/to/yourproject/alembic/versions/1975ea83b712_create_accoun t_table.py...done </pre></div> </div> <p>A new file <code class="docutils literal notranslate"><span class="pre">1975ea83b712_create_account_table.py</span></code> is generated. Looking inside the file:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="sd">"""create account table</span> <span class="sd">Revision ID: 1975ea83b712</span> <span class="sd">Revises:</span> <span class="sd">Create Date: 2011-11-08 11:40:27.089406</span> <span class="sd">"""</span> <span class="c1"># revision identifiers, used by Alembic.</span> <span class="n">revision</span> <span class="o">=</span> <span class="s1">'1975ea83b712'</span> <span class="n">down_revision</span> <span class="o">=</span> <span class="kc">None</span> <span class="n">branch_labels</span> <span class="o">=</span> <span class="kc">None</span> <span class="kn">from</span> <span class="nn">alembic</span> <span class="kn">import</span> <span class="n">op</span> <span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span> <span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span> <span class="k">pass</span> <span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span> <span class="k">pass</span> </pre></div> </div> <p>The file contains some header information, identifiers for the current revision and a “downgrade” revision, an import of basic Alembic directives, and empty <code class="docutils literal notranslate"><span class="pre">upgrade()</span></code> and <code class="docutils literal notranslate"><span class="pre">downgrade()</span></code> functions. Our job here is to populate the <code class="docutils literal notranslate"><span class="pre">upgrade()</span></code> and <code class="docutils literal notranslate"><span class="pre">downgrade()</span></code> functions with directives that will apply a set of changes to our database. Typically, <code class="docutils literal notranslate"><span class="pre">upgrade()</span></code> is required while <code class="docutils literal notranslate"><span class="pre">downgrade()</span></code> is only needed if down-revision capability is desired, though it’s probably a good idea.</p> <p>Another thing to notice is the <code class="docutils literal notranslate"><span class="pre">down_revision</span></code> variable. This is how Alembic knows the correct order in which to apply migrations. When we create the next revision, the new file’s <code class="docutils literal notranslate"><span class="pre">down_revision</span></code> identifier would point to this one:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># revision identifiers, used by Alembic.</span> <span class="n">revision</span> <span class="o">=</span> <span class="s1">'ae1027a6acf'</span> <span class="n">down_revision</span> <span class="o">=</span> <span class="s1">'1975ea83b712'</span> </pre></div> </div> <p>Every time Alembic runs an operation against the <code class="docutils literal notranslate"><span class="pre">versions/</span></code> directory, it reads all the files in, and composes a list based on how the <code class="docutils literal notranslate"><span class="pre">down_revision</span></code> identifiers link together, with the <code class="docutils literal notranslate"><span class="pre">down_revision</span></code> of <code class="docutils literal notranslate"><span class="pre">None</span></code> representing the first file. In theory, if a migration environment had thousands of migrations, this could begin to add some latency to startup, but in practice a project should probably prune old migrations anyway (see the section <a class="reference internal" href="cookbook.html#building-uptodate"><span class="std std-ref">Building an Up to Date Database from Scratch</span></a> for a description on how to do this, while maintaining the ability to build the current database fully).</p> <p>We can then add some directives to our script, suppose adding a new table <code class="docutils literal notranslate"><span class="pre">account</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span> <span class="n">op</span><span class="o">.</span><span class="n">create_table</span><span class="p">(</span> <span class="s1">'account'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'name'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="kc">False</span><span class="p">),</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'description'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Unicode</span><span class="p">(</span><span class="mi">200</span><span class="p">)),</span> <span class="p">)</span> <span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span> <span class="n">op</span><span class="o">.</span><span class="n">drop_table</span><span class="p">(</span><span class="s1">'account'</span><span class="p">)</span> </pre></div> </div> <p><a class="reference internal" href="ops.html#alembic.operations.Operations.create_table" title="alembic.operations.Operations.create_table"><code class="xref py py-meth docutils literal notranslate"><span class="pre">create_table()</span></code></a> and <a class="reference internal" href="ops.html#alembic.operations.Operations.drop_table" title="alembic.operations.Operations.drop_table"><code class="xref py py-meth docutils literal notranslate"><span class="pre">drop_table()</span></code></a> are Alembic directives. Alembic provides all the basic database migration operations via these directives, which are designed to be as simple and minimalistic as possible; there’s no reliance upon existing table metadata for most of these directives. They draw upon a global “context” that indicates how to get at a database connection (if any; migrations can dump SQL/DDL directives to files as well) in order to invoke the command. This global context is set up, like everything else, in the <code class="docutils literal notranslate"><span class="pre">env.py</span></code> script.</p> <p>An overview of all Alembic directives is at <a class="reference internal" href="ops.html#ops"><span class="std std-ref">Operation Reference</span></a>.</p> </section> <section id="running-our-first-migration"> <h2>Running our First Migration<a class="headerlink" href="#running-our-first-migration" title="Permalink to this headline">¶</a></h2> <p>We now want to run our migration. Assuming our database is totally clean, it’s as yet unversioned. The <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">upgrade</span></code> command will run upgrade operations, proceeding from the current database revision, in this example <code class="docutils literal notranslate"><span class="pre">None</span></code>, to the given target revision. We can specify <code class="docutils literal notranslate"><span class="pre">1975ea83b712</span></code> as the revision we’d like to upgrade to, but it’s easier in most cases just to tell it “the most recent”, in this case <code class="docutils literal notranslate"><span class="pre">head</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic upgrade head INFO [alembic.context] Context class PostgresqlContext. INFO [alembic.context] Will assume transactional DDL. INFO [alembic.context] Running upgrade None -> 1975ea83b712 </pre></div> </div> <p>Wow that rocked! Note that the information we see on the screen is the result of the logging configuration set up in <code class="docutils literal notranslate"><span class="pre">alembic.ini</span></code> - logging the <code class="docutils literal notranslate"><span class="pre">alembic</span></code> stream to the console (standard error, specifically).</p> <p>The process which occurred here included that Alembic first checked if the database had a table called <code class="docutils literal notranslate"><span class="pre">alembic_version</span></code>, and if not, created it. It looks in this table for the current version, if any, and then calculates the path from this version to the version requested, in this case <code class="docutils literal notranslate"><span class="pre">head</span></code>, which is known to be <code class="docutils literal notranslate"><span class="pre">1975ea83b712</span></code>. It then invokes the <code class="docutils literal notranslate"><span class="pre">upgrade()</span></code> method in each file to get to the target revision.</p> </section> <section id="running-our-second-migration"> <h2>Running our Second Migration<a class="headerlink" href="#running-our-second-migration" title="Permalink to this headline">¶</a></h2> <p>Let’s do another one so we have some things to play with. We again create a revision file:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic revision -m "Add a column" Generating /path/to/yourapp/alembic/versions/ae1027a6acf_add_a_column.py... done </pre></div> </div> <p>Let’s edit this file and add a new column to the <code class="docutils literal notranslate"><span class="pre">account</span></code> table:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="sd">"""Add a column</span> <span class="sd">Revision ID: ae1027a6acf</span> <span class="sd">Revises: 1975ea83b712</span> <span class="sd">Create Date: 2011-11-08 12:37:36.714947</span> <span class="sd">"""</span> <span class="c1"># revision identifiers, used by Alembic.</span> <span class="n">revision</span> <span class="o">=</span> <span class="s1">'ae1027a6acf'</span> <span class="n">down_revision</span> <span class="o">=</span> <span class="s1">'1975ea83b712'</span> <span class="kn">from</span> <span class="nn">alembic</span> <span class="kn">import</span> <span class="n">op</span> <span class="kn">import</span> <span class="nn">sqlalchemy</span> <span class="k">as</span> <span class="nn">sa</span> <span class="k">def</span> <span class="nf">upgrade</span><span class="p">():</span> <span class="n">op</span><span class="o">.</span><span class="n">add_column</span><span class="p">(</span><span class="s1">'account'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">Column</span><span class="p">(</span><span class="s1">'last_transaction_date'</span><span class="p">,</span> <span class="n">sa</span><span class="o">.</span><span class="n">DateTime</span><span class="p">))</span> <span class="k">def</span> <span class="nf">downgrade</span><span class="p">():</span> <span class="n">op</span><span class="o">.</span><span class="n">drop_column</span><span class="p">(</span><span class="s1">'account'</span><span class="p">,</span> <span class="s1">'last_transaction_date'</span><span class="p">)</span> </pre></div> </div> <p>Running again to <code class="docutils literal notranslate"><span class="pre">head</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic upgrade head INFO [alembic.context] Context class PostgresqlContext. INFO [alembic.context] Will assume transactional DDL. INFO [alembic.context] Running upgrade 1975ea83b712 -> ae1027a6acf </pre></div> </div> <p>We’ve now added the <code class="docutils literal notranslate"><span class="pre">last_transaction_date</span></code> column to the database.</p> </section> <section id="partial-revision-identifiers"> <h2>Partial Revision Identifiers<a class="headerlink" href="#partial-revision-identifiers" title="Permalink to this headline">¶</a></h2> <p>Any time we need to refer to a revision number explicitly, we have the option to use a partial number. As long as this number uniquely identifies the version, it may be used in any command in any place that version numbers are accepted:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic upgrade ae1 </pre></div> </div> <p>Above, we use <code class="docutils literal notranslate"><span class="pre">ae1</span></code> to refer to revision <code class="docutils literal notranslate"><span class="pre">ae1027a6acf</span></code>. Alembic will stop and let you know if more than one version starts with that prefix.</p> </section> <section id="relative-migration-identifiers"> <span id="relative-migrations"></span><h2>Relative Migration Identifiers<a class="headerlink" href="#relative-migration-identifiers" title="Permalink to this headline">¶</a></h2> <p>Relative upgrades/downgrades are also supported. To move two versions from the current, a decimal value “+N” can be supplied:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic upgrade +2 </pre></div> </div> <p>Negative values are accepted for downgrades:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic downgrade -1 </pre></div> </div> <p>Relative identifiers may also be in terms of a specific revision. For example, to upgrade to revision <code class="docutils literal notranslate"><span class="pre">ae1027a6acf</span></code> plus two additional steps:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic upgrade ae10+2 </pre></div> </div> </section> <section id="getting-information"> <h2>Getting Information<a class="headerlink" href="#getting-information" title="Permalink to this headline">¶</a></h2> <p>With a few revisions present we can get some information about the state of things.</p> <p>First we can view the current revision:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic current INFO [alembic.context] Context class PostgresqlContext. INFO [alembic.context] Will assume transactional DDL. Current revision for postgresql://scott:XXXXX@localhost/test: 1975ea83b712 -> ae1027a6acf (head), Add a column </pre></div> </div> <p><code class="docutils literal notranslate"><span class="pre">head</span></code> is displayed only if the revision identifier for this database matches the head revision.</p> <p>We can also view history with <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">history</span></code>; the <code class="docutils literal notranslate"><span class="pre">--verbose</span></code> option (accepted by several commands, including <code class="docutils literal notranslate"><span class="pre">history</span></code>, <code class="docutils literal notranslate"><span class="pre">current</span></code>, <code class="docutils literal notranslate"><span class="pre">heads</span></code> and <code class="docutils literal notranslate"><span class="pre">branches</span></code>) will show us full information about each revision:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic history --verbose Rev: ae1027a6acf (head) Parent: 1975ea83b712 Path: /path/to/yourproject/alembic/versions/ae1027a6acf_add_a_column.py add a column Revision ID: ae1027a6acf Revises: 1975ea83b712 Create Date: 2014-11-20 13:02:54.849677 Rev: 1975ea83b712 Parent: <base> Path: /path/to/yourproject/alembic/versions/1975ea83b712_add_account_table.py create account table Revision ID: 1975ea83b712 Revises: Create Date: 2014-11-20 13:02:46.257104 </pre></div> </div> <section id="viewing-history-ranges"> <h3>Viewing History Ranges<a class="headerlink" href="#viewing-history-ranges" title="Permalink to this headline">¶</a></h3> <p>Using the <code class="docutils literal notranslate"><span class="pre">-r</span></code> option to <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">history</span></code>, we can also view various slices of history. The <code class="docutils literal notranslate"><span class="pre">-r</span></code> argument accepts an argument <code class="docutils literal notranslate"><span class="pre">[start]:[end]</span></code>, where either may be a revision number, symbols like <code class="docutils literal notranslate"><span class="pre">head</span></code>, <code class="docutils literal notranslate"><span class="pre">heads</span></code> or <code class="docutils literal notranslate"><span class="pre">base</span></code>, <code class="docutils literal notranslate"><span class="pre">current</span></code> to specify the current revision(s), as well as negative relative ranges for <code class="docutils literal notranslate"><span class="pre">[start]</span></code> and positive relative ranges for <code class="docutils literal notranslate"><span class="pre">[end]</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic history -r1975ea:ae1027 </pre></div> </div> <p>A relative range starting from three revs ago up to current migration, which will invoke the migration environment against the database to get the current migration:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic history -r-3:current </pre></div> </div> <div class="admonition note"> <p class="admonition-title">Note</p> <p>As illustrated above, to use ranges that start with a negative number (i.e. a dash), due to a <a class="reference external" href="https://github.com/python/cpython/issues/53580">bug in argparse</a> , either the syntax <code class="docutils literal notranslate"><span class="pre">-r-<base>:<head></span></code>, without any space, must be used as above:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic history -r-3:current </pre></div> </div> <p>or if using <code class="docutils literal notranslate"><span class="pre">--rev-range</span></code>, an equals sign must be used:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic history --rev-range=-3:current </pre></div> </div> <p>Using quotes or escape symbols will not work if there’s a space after the argument name.</p> </div> <p>View all revisions from 1975 to the head:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic history -r1975ea: </pre></div> </div> </section> </section> <section id="downgrading"> <h2>Downgrading<a class="headerlink" href="#downgrading" title="Permalink to this headline">¶</a></h2> <p>We can illustrate a downgrade back to nothing, by calling <code class="docutils literal notranslate"><span class="pre">alembic</span> <span class="pre">downgrade</span></code> back to the beginning, which in Alembic is called <code class="docutils literal notranslate"><span class="pre">base</span></code>:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic downgrade base INFO [alembic.context] Context class PostgresqlContext. INFO [alembic.context] Will assume transactional DDL. INFO [alembic.context] Running downgrade ae1027a6acf -> 1975ea83b712 INFO [alembic.context] Running downgrade 1975ea83b712 -> None </pre></div> </div> <p>Back to nothing - and up again:</p> <div class="highlight-default notranslate"><div class="highlight"><pre><span></span>$ alembic upgrade head INFO [alembic.context] Context class PostgresqlContext. INFO [alembic.context] Will assume transactional DDL. INFO [alembic.context] Running upgrade None -> 1975ea83b712 INFO [alembic.context] Running upgrade 1975ea83b712 -> ae1027a6acf </pre></div> </div> </section> <section id="next-steps"> <h2>Next Steps<a class="headerlink" href="#next-steps" title="Permalink to this headline">¶</a></h2> <p>The vast majority of Alembic environments make heavy use of the “autogenerate” feature. Continue onto the next section, <a class="reference internal" href="autogenerate.html"><span class="doc">Auto Generating Migrations</span></a>.</p> </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="#">Tutorial</a><ul> <li><a class="reference internal" href="#the-migration-environment">The Migration Environment</a></li> <li><a class="reference internal" href="#creating-an-environment">Creating an Environment</a></li> <li><a class="reference internal" href="#editing-the-ini-file">Editing the .ini File</a></li> <li><a class="reference internal" href="#create-a-migration-script">Create a Migration Script</a></li> <li><a class="reference internal" href="#running-our-first-migration">Running our First Migration</a></li> <li><a class="reference internal" href="#running-our-second-migration">Running our Second Migration</a></li> <li><a class="reference internal" href="#partial-revision-identifiers">Partial Revision Identifiers</a></li> <li><a class="reference internal" href="#relative-migration-identifiers">Relative Migration Identifiers</a></li> <li><a class="reference internal" href="#getting-information">Getting Information</a><ul> <li><a class="reference internal" href="#viewing-history-ranges">Viewing History Ranges</a></li> </ul> </li> <li><a class="reference internal" href="#downgrading">Downgrading</a></li> <li><a class="reference internal" href="#next-steps">Next Steps</a></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> <p class="topless"><a href="front.html" title="previous chapter">Front Matter</a></p> </div> <div> <h4>Next topic</h4> <p class="topless"><a href="autogenerate.html" title="next chapter">Auto Generating Migrations</a></p> </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="autogenerate.html" title="Auto Generating Migrations" >next</a> |</li> <li class="right" > <a href="front.html" title="Front Matter" >previous</a> |</li> <li class="nav-item nav-item-0"><a href="index.html">Alembic 1.8.1 documentation</a> »</li> <li class="nav-item nav-item-this"><a href="">Tutorial</a></li> </ul> </div> <div class="footer" role="contentinfo"> © Copyright 2010-2022, Mike Bayer. Created using <a href="https://www.sphinx-doc.org/">Sphinx</a> 4.5.0. </div> </body> </html>