Skip to main content

Manage your database versions with pure SQL

Project description

MAT - Migration Assistant Tool

Manage your database versions with pure SQL

MAT allows you to write your app migrations, rollbacks and data seeding scripts in pure sql, no matter which languages or frameworks your application is build on

Supported databases

Currently following databases are supported:

  • MySQL

Compatibility for following databases is in development

  • SQLite
  • Postgres
  • SQL Server


Python PIP

You can use pip:

pip install mat

MAT depends on python 3, in some systems you would want to use 'pip3' to install programs under python3 environment

Usage through docker

You don't know pip/python? No problem, you can use 'mat' through docker.

I have created a wrapper script that will invoke mat inside docker whenever you need it, just download it inside your migrations directory and use it the same way you would do with mat command

Get script

chmod +x matw # Grant exec permissions to wrapper

Then you can use the wrapper the same way as mat command

./matw status
./matw migrate -s 2

Check 'Getting started' section to understand what above commands means

Behind scenes the script will:

  1. Create a docker container ready to run 'mat'
  2. Mount current path '$PWD' as a volume in /mat container path
  3. Invoke 'mat' command inside container by forwarding arguments

How it works?

  • You write your migration scripts in multiple sql files (One per migration)
  • You write rollback migration scripts (One per migration)
  • You setup database connection and scripts location in a configuration file
  • Run mat to apply/unapply/list your migrations

Getting started

Prepare migrations directory

Create a directory to hold all your database migrations

Directory location is up to you, but usually you'll want to place it inside your main project or as an independent repository in cases where multiple applications will access same database

Your directory should have a structure like this:

|- |- migrations.yml 
|- |- up/
|- |- - v1_create_tables.sql
|- |- - v2_create_credentials_table.sql
|- |- down/
|- |- - v1_undo_create_tables.sql
|- |- - v2_destroy_credentials_table.sql


  • The up/ directory will hold all your migrations scripts
  • The down/ directory will hold all your rollback scripts
  • The migrations.yml will be used for setup to specify how to access database

You can use whatever name you want for your migration directories as long as you specify the right paths in the config file

Create your migrations

Place your first migration inside up/ directory in a sql file, ensure to name the file like: V<version_number>_<snake_case_name>.sql

Follow naming convention is essential in order to run migrations in appropriate order and shows right name to you

Let's say you have following file named v1_create_tables.sql (which will be parsed as: Version: V1, name: Create tables)

    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(500) NOT NULL

    name VARCHAR(255) NOT NULL,
    user_id INTEGER NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY (user_id)
        REFERENCES user(id)

Create your rollbacks

Place your rollback inside down/ directory in a sql file, following same name conventions as per migrations: V<version_number>_snake_case_name>.sql

Version number must match exactly with version in migration file, however you can use whatever you want for the name part

Let's say you have: v1_undo_create_tables.sql:


Setup database connection and settings

Place the database connection settings into migrations.yml file:

  username: developer
  password: d3v3lopm3nt
  database: cars

  up: up/
  down: down/

And now let 'mat' do it's magic ;)

Using mat

cd into migrations directory and run one of following commands

mat status        # Will list all your migrations

mat migrate       # Will apply your migrations
mat migrate -s 2  # Will apply only '2' non applied migrations

mat rollback      # Will run you rollback scripts
mat rollback -s 2 # Will run rollback scripts for latest '2' applied migrations

Project details

Download files

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

Source Distribution

mat-1.0.2.tar.gz (11.1 kB view hashes)

Uploaded source

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page