Skip to main content

Lightweight SQL schema migration tool, based on Snaql query builder

Project description

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'

Note: of course, you could describe several apps with different migrations location.

And then just:

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

Available commands

Command Action
show Shows all configured apps and migrations
apply all Applies all available migrations in all configured apps
apply users_app/002-update-users Applies all migrations up to 002-update-users in users_app (inclusive)
revert users_app/002-update-users Reverts all migrations down to 002-update-users in users_app (inclusive)

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).

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for snaql-migration, version 0.1.0
Filename, size File type Python version Upload date Hashes
Filename, size snaql_migration-0.1.0-py3-none-any.whl (7.7 kB) File type Wheel Python version py3 Upload date Hashes View
Filename, size snaql-migration-0.1.0.tar.gz (6.2 kB) File type Source Python version None Upload date Hashes View

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring DigiCert DigiCert EV certificate Facebook / Instagram Facebook / Instagram PSF Sponsor Fastly Fastly CDN Google Google Object Storage and Download Analytics Pingdom Pingdom Monitoring Salesforce Salesforce PSF Sponsor Sentry Sentry Error logging StatusPage StatusPage Status page