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+
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
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
- PostgreSQL through
Psycopg2
- MySQL through
PyMySQL
Note: Necessary database driver must be installed separately
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).