Yoyo database migrations

Yoyo is a database schema migration tool using plain SQL and python's builtin DB-API.

.. image:: https://drone.io/bitbucket.org/ollyc/yoyo/status.png

What does yoyo-migrations do?

As database applications evolve, changes to the database schema are often required. These can usually be written as one-off SQL scripts containing CREATE/ALTER table statements (although any SQL or python script may be used with yoyo).

Yoyo provides a command line tool for reading a directory of such scripts and applying them to your database as required.

Installation

Install from the PyPI with the command::

pip install yoyo-migrations

Database support

PostgreSQL, MySQL and SQLite databases are supported. An ODBC backend is also available, but is unsupported (patches welcome!)

Usage

Yoyo is usually invoked as a command line script.

Start a new migration::

yoyo new ./migrations -m "Add column to foo"

Apply migrations from directory migrations to a PostgreSQL database::

yoyo apply --database postgresql://scott:tiger@localhost/db ./migrations

Rollback migrations previously applied to a MySQL database::

yoyo rollback --database mysql://scott:tiger@localhost/database ./migrations

Reapply (ie rollback then apply again) migrations to a SQLite database at location /home/sheila/important.db::

yoyo reapply --database sqlite:////home/sheila/important.db ./migrations

By default, yoyo-migrations starts in an interactive mode, prompting you for each migration file before applying it, making it easy to preview which migrations to apply and rollback.

The migrations directory should contain a series of migration scripts. Each migration script is a python file (.py) containing a series of steps. Each step should comprise a migration query and (optionally) a rollback query. For example::

#
# file: migrations/0001.create-foo.py
#
from yoyo import step
step(
    "CREATE TABLE foo (id INT, bar VARCHAR(20), PRIMARY KEY (id))",
    "DROP TABLE foo",
)

Migrations may also declare dependencies on previous migrations via the __depends__ attribute::

step(
    "CREATE TABLE foo (id INT, bar VARCHAR(20), PRIMARY KEY (id))",
    "DROP TABLE foo",
)

The filename of each file (without the .py extension) is used as the identifier for each migration. In the absence of a __depends__ attribute, migrations are applied in filename order, so it's useful to name your files using a date (eg '20090115-xyz.py') or some other incrementing number.

yoyo creates a table in your target database, _yoyo_migration, to track which migrations have been applied.

Steps may also take an optional argument ignore_errors, which must be one of apply, rollback, or all. If in the previous example the table foo might have already been created by another means, we could add ignore_errors='apply' to the step to allow the migrations to continue regardless::

#
# file: migrations/0001.create-foo.py
#
from yoyo import step
step(
    "CREATE TABLE foo (id INT, bar VARCHAR(20), PRIMARY KEY (id))",
    "DROP TABLE foo",
    ignore_errors='apply',
)

Steps can also be python callable objects that take a database connection as their single argument. For example::

#
# file: migrations/0002.update-keys.py
#
from yoyo import step
def do_step(conn):
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO sysinfo "
        " (osname, hostname, release, version, arch)"
        " VALUES (%s, %s, %s, %s, %s %s)",
        os.uname()
    )

step(do_step)

Configuration file

Yoyo looks for a configuration file named yoyo.ini in the current working directory or any ancestor directory. This can contain the following options::

[DEFAULT]

# List of migration source directories. "%(here)s" is expanded to the # full path of the directory containing this ini file. sources = %(here)s/migrations %(here)s/lib/module/migrations

# Target database database = postgresql://scott:tiger@localhost/mydb

# Verbosity level. Goes from 0 (least verbose) to 3 (most verbose) verbosity = 3

# Disable interactive features batch_mode = on

# Editor to use when starting new migrations # "{}" is expanded to the filename of the new migration editor = /usr/local/bin/vim -f {}

# An arbitrary command to run after a migration has been created # "{}" is expanded to the filename of the new migration postcreatecommand = hg add {}

# A prefix to use for generated migration filenames prefix = myproject_

Config file inheritance may be used to customize configuration per site::

# # file: yoyo-defaults.ini # [DEFAULT] sources = %(here)s/migrations

# # file: yoyo.ini # [DEFAULT]

; Inherit settings from yoyo-defaults.ini %inherit = %(here)s/yoyo-defaults.ini

; Use '?' to avoid raising an error if the file does not exist %inherit = ?%(here)s/yoyo-defaults.ini

database = sqlite:///%(here)s/mydb.sqlite

Transactions

Each migration is run in a separate transaction and savepoints are used to isolate steps within each migration.

If an error occurs during a step and the step has ignore_errors set, then that individual step will be rolled back and execution will pick up from the next step. If ignore_errors is not set then the entire migration will be rolled back and execution stopped.

Note that some databases (eg MySQL) do not support rollback on DDL statements (eg CREATE ... and ALTER ... statements). For these databases you may need to manually intervene to reset the database state should errors occur during your migration.

Using group allows you to nest steps, giving you control of where rollbacks happen. For example::

group([
  step("ALTER TABLE employees ADD tax_code TEXT"),
  step("CREATE INDEX tax_code_idx ON employees (tax_code)")
], ignore_errors='all')
step("UPDATE employees SET tax_code='C' WHERE pay_grade < 4")
step("UPDATE employees SET tax_code='B' WHERE pay_grade >= 6")
step("UPDATE employees SET tax_code='A' WHERE pay_grade >= 8")

Disabling transactions ~~~~~~~~~~~~~~~~~~~~~~

In PostgreSQL it is an error to run certain statements inside a transaction block. These include:

.. code::sql

CREATE TABLE <foo>
ALTER TYPE <enum> ADD ...

Migrations containing such statements should set __transactional__ = False, eg:

.. code::python

__transactional__ = False

step("CREATE DATABASE mydb", "DROP DATABASE mydb")

Note that this feature is implemented for the PostgreSQL backend only.

Post-apply hook

It can be useful to have a script that's run after successful migrations. For example you could use this to update database permissions or re-create views. To do this, create a migration file called post-apply.py. This file should have the same format as any other migration file.

Password security

You normally specify your database username and password as part of the database connection string on the command line. On a multi-user machine, other users could view your database password in the process list.

The -p or --prompt-password flag causes yoyo to prompt for a password, ignoring any password specified in the connection string. This password will not be available to other users via the system's process list.

Configuration file

Yoyo looks for a configuration file called yoyo.ini, in the current working directory or any ancestor directory.

If no configuration file is found yoyo will prompt you to create one, popuplated with the current command line args.

Using a configuration file saves typing, avoids your database username and password showing in process listings and lessens the risk of accidentally running yoyo on the wrong database (ie by re-running an earlier yoyo entry in your command history when you have moved to a different directory).

If you do not want this config file to be used, add the --no-config parameter to the command line options.

Connections

Database connections are specified using a URI. Examples:

SQLite ~~~~~~

::

# Use 4 slashes for an absolute database path on unix like platforms database = sqlite:////home/user/mydb.sqlite

# Absolute path on Windows. database = sqlite:///c:\home\user\mydb.sqlite

# Use 3 slashes for a relative path database = sqlite:///mydb.sqlite

MySQL ~~~~~

::

# Network database connection database = mysql://scott:tiger@localhost/mydatabase

# Connect via a unix socket database = mysql://scott:tiger@/mydatabase?unix_socket=/tmp/mysql.sock

MySQL with MySQLdb ~~~~~~~~~~~~~~~~~~

::

# Use the MySQLdb driver instead of pymysql database = mysql+mysqldb://scott:tiger@localhost/mydatabase

PostgreSQL ~~~~~~~~~~

::

# Network database connection database = postgresql://scott:tiger@localhost/mydatabase

# Omit the host to use a socket connection database = postgresql://scott:tiger@/mydatabase

Using yoyo from python code

The following example shows how to apply migrations from inside python code::

from yoyo import read_migrations, get_backend

backend = get_backend('postgres://myuser@localhost/mydatabase')
migrations = read_migrations('path/to/migrations')
backend.apply_migrations(backend.to_apply(migrations))

.. :vim:sw=4:et

CHANGELOG

5.0.5 (released 2017-01-12) ~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Added support for a __transactional__ = False flag in migration files, allowing migrations to run commands in PostgreSQL that raise errors if run inside a transaction block (eg "CREATE DATABASE")

• Bugfix: fix the unix_socket option for mysql connections

5.0.4 (released 2016-09-04) ~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Bugfix: fixed crash when mutliple migrations have the same dependency (thanks to smotko for the report)

5.0.3 (released 2016-07-03) ~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Bugfix: fixed exception when creating a new migration interactively with yoyo new

5.0.2 (released 2016-06-21) ~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Added DatabaseBackend.apply_migrations_only and run_post_hooks methods. This allows python code that interfaces with yoyo to run migrations and post_hooks separately if required (thanks to Robi Wan for reporting this and discussing possible fixes)
• Bugfix: fix duplicate key error when using post-apply hooks (thanks to Robi Wan for the report)
• Bugfix: migration steps are no longer loaded multiple times if read_migrations is called more than once (thanks to Kyle McChesney for the report)
• Bugfix: make sure that the migration_table option is read from the config file (thanks to Frederik Holljen for the report and Manolo Micozzi for the fix)

5.0.1 (released 2015-11-13) ~~~~~~~~~~~~~~~~~~~~~~~~~~~

• Bugfix: migration files are now sequentially named when using the prefix option (thanks to Igor Tsarev)

5.0.0 (released 2015-11-13) ~~~~~~~~~~~~~~~~~~~~~~~~~~~

This version introduces backwards incompatible changes. Please read this file carefully before upgrading.

• The configuration file is now stored per-project, not per-migrations source directory. This makes it possible to share a migrations source directory across multiple projects.
• The api for calling yoyo programmatically has changed. Refer to the README for an up to date example of calling yoyo from python code.
• Improved url parsing
• Allow database uris containing usernames with the symbol '@'
• The command line option --no-cache has been renamed to --no-config-file. The old name is retained as an alias for backwards compatibility
• The database must now be supplied using the --database/-d command line flag. This makes it possible to change the database when calling yoyo without needing to respecify the migration directories.
• Added a --revision command line option. In the case of apply, this causes the specified migration to be applied, plus any dependencies. In the case of rollback, this removes the specified revision and any other migrations that depend upon it.
• Added 'mark' and 'unmark' commands to allow migrations to be marked in the database without actually running them
• Transaction handling has changed. Each migration now always runs in a single transaction, with individual steps running in nested transactions (using savepoints). The transaction() function is still available for backwards compatibility, but now creates a savepoint rather than a full transaction.
• The default MySQL driver has been changed to PyMySQL, for Python 3 compatbility reasons. MySQLdb can be used by specifying the 'mysql+mysqldb://' scheme.
• Errors encountered while creating the yoyomigrations table are now raised rather than being silently ignored (thanks to James Socol).

Version 4.2.5 ~~~~~~~~~~~~~

• Fix for pyscopg2 driver versions >=2.6
• Faster loading of migration scripts
• Dependencies between migrations can be added via the __depends__ attribute
• Dropped support for python 2.6

Version 4.2.4

• Fix for mismanaged 4.2.3 release

Version 4.2.3

• Migrations are now datestamped with a UTC date (thanks to robi wan)

• Fixes for installation and use under python 3

Version 4.2.2

• Migration scripts can start with from yoyo import step, transaction. This prevents linters (eg flake8) throwing errors over undefined names.

• Bugfix: functions declared in a migration file can access the script's global namespace

Version 4.2.1

• Bugfix for previous release, which omitted critical files

Version 4.2.0

• Removed yoyo.migrate namespace package. Any code that uses the yoyo api directly needs have any imports modified, eg this::

  from yoyo.migrate import read_migrations from yoyo.migrate.connections import connect

  Should be changed to this::

  from yoyo import read_migrations from yoyo.connections import connect

• Migrated from darcs to mercurial. Code is now hosted at https://bitbucket.org/ollyc/yoyo

• Bugfix: the migrationtable option was not being passed to readmigrations, causing the value to be ignored

Version 4.1.6

• Added windows support (thanks to Peter Shinners)

Version 4.1.5

• Configure logging handlers so that the -v switch causes output to go to the console (thanks to Andrew Nelis).

• -v command line switch no longer takes an argument but may be specified multiple times instead (ie use -vvv instead of -v3). --verbosity retains the old behaviour.

Version 4.1.4

• Bugfix for post apply hooks

Version 4.1.3

• Changed default migration table name back to 'yoyomigration'

Version 4.1.2

• Bugfix for error when running in interactive mode

Version 4.1.1

• Introduced configuration option for migration table name

Version 4.1.0

• Introduced ability to run steps within a transaction (thanks to Ryan Williams for suggesting this functionality along with assorted bug fixes.)

• "post-apply" migrations can be run after every successful upward migration

• Other minor bugfixes and improvements

• Switched to . version numbering convention

Version 4

• Fixed problem installing due to missing manifest entry

Version 3

• Use the consolescripts entrypoint in preference to scripts=[] in setup.py, this provides better interoperability with buildout

Version 2

• Fixed error when reading dburi from config file

Version 1

• Initial release