migra

Schema migrations are without doubt the most cumbersome and annoying part of working with SQL databases. So much so that some people think that schemas themselves are bad!

But schemas are actually good. Enforcing data consistency and structure is a good thing. It’s the migration tooling that is bad, because it’s harder to use than it should be. migra is an attempt to change that, and make migrations easy, safe, and reliable instead of something to dread.

Migra supports PostgreSQL >= 9.4. Known issues exist with earlier versions.

Full documentation

Official documentation is at migra.readthedocs.io

How it Works

Think of migra as a diff tool for schemas. Suppose database A and database B have similar but slightly different schemas. migra will detect the differences and output the SQL needed to transform A to B.

This includes changes to tables, views, functions, indexes, constraints, enums, sequences, and installed extensions.

You can use migra as a library to build your own migration scripts, tools, etc. Installing migra also installs the migra command, so you can use it as follows:

$ migra postgresql:///a postgresql:///b
alter table "public"."products" add column newcolumn text;

alter table "public"."products" add constraint "x" CHECK ((price > (0)::numeric));

If b is the target schema, then a new column and constraint needs to be applied to a to make it match b’s schema. Once we’ve reviewed the autogenerated SQL and we’re happy with it, we can apply these changes as easily as:

$ migra --unsafe postgresql:///a postgresql:///b > migration_script.sql
# Then after careful review (obviously)...
$ psql a --single-transaction -f migration_script.sql

Migration complete!

IMPORTANT: Practice safe migrations

Migrations can never be fully automatic. As noted above ALWAYS REVIEW MIGRATION SCRIPTS CAREFULLY, ESPECIALLY WHEN DROPPING TABLES IS INVOLVED.

Best practice is to run your migrations against a copy of your production database first. This helps verify correctness and spot any performance issues before they cause interruptions and downtime on your production database.

migra will deliberately throw an error if any generated statements feature the word “drop”. This safety feature is by no means idiot-proof, but might prevent a few obvious blunders.

If you want to generate “drop …” statements, you need to use the --unsafe flag if using the command, or if using the python package directly, set_safety( to false on your Migration object.

Python Code

Here’s how the migra command is implemented under the hood (with a few irrelevant lines removed).

As you can see, it’s pretty simple (S here is a context manager that creates a database session from a database URL).

from migra import Migration
from sqlbag import S

with S(args.dburl_from) as s0, S(args.dburl_target) as s1:
    m = Migration(s0, s1)

    if args.unsafe:
        m.set_safety(False)

    m.add_all_changes()
    print(m.sql)

Here the code just opens connections to both databases for the Migration object to analyse. m.add_all_changes() generates the SQL statements for the changes required, and adds to the migration object’s list of pending changes. The necessary SQL is now available as a property.

Documentation

migra is in early alpha and documentation is scarce so far. We are working on remedying this. Watch this space.

Features and Limitations

migra plays nicely with extensions. Schema contents belonging to extensions will be ignored and left to the extension to manage.

New: migra now plays nicely with view dependencies too, and will drop/create them in the correct order.

Only SQL/PLPGSQL functions are confirmed to work so far. migra ignores functions that use other languages.

Installation

Assuming you have pip installed, all you need to do is install as follows:

$ pip install migra

If you don’t have psycopg2 (the PostgreSQL driver) installed yet, you can install this at the same time with:

$ pip install migra[pg]

Metadata

Release files for migra 1.0.1494928440

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for migra 1.0.1494928440
File	Size	Uploaded
migra-1.0.1494928440.tar.gz	6.6 kB	May 16, 2017	Details

Built distribution (wheel)

Table of built distributions (wheels) for migra 1.0.1494928440
File	Interpreter	ABI	Platform	Reset
migra-1.0.1494928440-py2.py3-none-any.whl 11.0 kB May 16, 2017	Python 2, Python 3	none	any	Details

Total release size: 17.6 kB

Release files / migra-1.0.1494928440.tar.gz

Download URL	migra-1.0.1494928440.tar.gz
Size	6.6 kB
Tags	Source
SHA-256 checksum How to use checksums	`1f70ea67f440d3e65b5b0cf9b3ed4e0f0ce50b3b920a229bd51b2442e6f9234a`
BLAKE2b-256 checksum How to use checksums	`35c3e4eae6a5f03f61d09d6726388b9e75a3b338d7594f7393cba8b4785d7be4`
Upload date	May 16, 2017
Uploaded using Trusted Publishing? What is trusted publishing?	No

Release files / migra-1.0.1494928440-py2.py3-none-any.whl

Download URL	migra-1.0.1494928440-py2.py3-none-any.whl
Size	11.0 kB
Tags	Python 2 Python 3
SHA-256 checksum How to use checksums	`95a71ce2327ec17526389e9577713226c0dd752fc5a60ced4152df1d0d039b32`
BLAKE2b-256 checksum How to use checksums	`6855e40cef0080d5de6938e43dc7a6ace2b48e902751305327244b70d2aeb6e5`
Upload date	May 16, 2017
Uploaded using Trusted Publishing? What is trusted publishing?	No