snaql-migration

Lightweight SQL schema migration tool, based on Snaql query builder.

The main idea is to provide ability of describing migrations in raw SQL – every migration is a couple of files: 001-some-migration.apply.sql and 001-some-migration.revert.sql

Suitable for both Python 2.7 and 3.3+

Basic Usage

Install with pip:

$ pip install snaql-migration

Create some migration files. Let's say you have an app to deal with users:

/apps/users/migrations
    001-create-users.apply.sql
    001-create-users.revert.sql
    002-update-users.apply.sql
    002-update-users.revert.sql
    003-create-index.apply.sql
    003-create-index.revert.sql

Notes:

• migrations are sorted in ANSI order, so make sure you are numbering them with lead zeros
• *.apply.sql and *.revert.sql of the same migration must have equal name

Every migration is just a Snaql queries container.

001-create-users.apply.sql:

{% sql 'create_roles' %}
  CREATE TABLE roles (
    id INT NOT NULL,
    title VARCHAR(100),
    PRIMARY KEY (id)
  )
{% endsql %}

{% sql 'create_users', depends_on=['create_roles'] %}
  CREATE TABLE users (
    id INT NOT NULL,
    role_id INT NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id),
    FOREIGN KEY(role_id) REFERENCES roles (id)
  )
{% endsql %}

001-create-users.revert.sql:

{% sql 'revert_users' %}
  DROP TABLE users;
{% endsql %}

{% sql 'revert_roles', depends_on=['revert_users'] %}
  DROP TABLE roles;
{% endsql %}

Then create a simple YAML config file with database connection info and migrations locations:

db_uri: 'postgres://test:@localhost/test'

migrations:
  users_app: 'apps/users/migrations'
  other_app: 'apps/other_app/migrations'

And then just:

$ snaql-migration --config=config.yml apply all    # applies all available migrations in all configured apps

If you need to migrate only one app, you could use --db-uri/--migrations/--app parameters instead of --config:

$ snaql-migration --db-uri=postgres://test:@localhost/test --app=users_app --migrations=apps/users/migrations apply all

Available commands

Note: any command will automatically create snaql_migrations table in your database

Supported databases

• PostgreSQL through Psycopg2
• MySQL through PyMySQL

Note: Necessary database driver must be installed separately

Unit-testing

At first, valid PostgreSQL database connection url must be provided in tests/db_uri.yml. After that everything could be run as usual (with tox, for example).