A general purpose migration tool for managing MySQL updates
Project description
mygrations
A general purpose migration tool for managing MySQL updates. Written in python but intended for use in other systems as well, especially PHP.
About
mygrations
manages database migrations with a declarative approach. Rather than having many migration files, each table in your database has one SQL file which contains a CREATE TABLE
command that gives the table definition. This file defines the table structure that you want your table to have (potentially with records as well). When it comes time to migrate your database, mygrations
parses the SQL in these files to determine what the structure of your database should be. It then compares this to the actual table structure in MySQL to determine all of the changes that need to happen to bring the database up to spec. Finally, it generates any necessary ALTER TABLE
, CREATE TABLE
, or DROP TABLE
commands to update the database.
When you need to change a table structure you don't generate additional migration files, but instead simply edit the CREATE TABLE
command inside the original table definition file. When migrating multiple tables related via foreign key constraints you simply have to define your foreign keys in the CREATE TABLE
command normally. mygrations
will take note of the foreign keys and and automatically calculate and resolve dependencies while migrating.
Installation
- Requires Python3
- Requires MySQLdb For python3. (For ubuntu:
sudo apt-get install python3-mysqldb
)
To install mygrations
:
pip install mygrations
Then you just need to download and install the mygrations runner. Something like this works:
wget 'https://raw.githubusercontent.com/cmancone/mygrations/master/mygrate.py'
chmod a+x mygrate.py
sudo mv mygrate.py /usr/local/bin/mygrate.py
Your mileage may vary.
Setup
The calling sequence of the mygration runner is not yet very flexible, requiring you to have your environment setup in a particular way. Right now it assumes:
- Your application already has a
.env
file somewhere, containing database credentials - You place a
mygrate.conf
file in the same directory as the.env
file - You have a folder (somewhere) in your application that has all your
*.sql
files: ideally one for every table in your system
When you run mygrate.py
it will read the mygrate.conf
file in your current directory. It will also read the contents of your .env
file, where it expects to find key-value pairs, in particular the connection details to your MySQL
database. It is as flexible as possible when reading the syntax of the .env
file, with the goal that you should not have to make any adjustments to it in order to support mygrate.py
. Two pieces of information are pulled out of the mygrate.conf
file: where to find your database credentials in the .env
file, and where to find the *.sql
files that it will be migrating your database to match.
/var/www/example.com/.env
/var/www/example.com/mygrate.conf
/var/www/example.com/public/
/var/www/example.com/database/*.sql
Your .env
file presumably already exists for your application to use, and may look like this:
DB_HOSTNAME = "localhost"
DB_USERNAME = "app"
DB_PASSWORD = "[Your password here]"
DB_DATABASE = "app"
ANOTHER_CONFIG = "SomeValue"
Your mygrate.conf
file would then look like this:
hostname_key = "DB_HOSTNAME"
username_key = "DB_USERNAME"
password_key = "DB_PASSWORD"
database_key = "DB_DATABASE"
files_directory = "database/"
To be clear, you don't put your database credentials in your mygrate.conf
file: instead you simply tell it which keys to grab the database credentials out of from your .env
file. This way you can just have one mygrate.conf
file that works in all environments. The files directory tells it where to find your *.sql
files. You simply specify the location of the directory containing those files, relative to the mygrate.conf
file. It will automatically read any *.sql
files in that directory and use the structure in those files to determine the "truth" of what your database should look like.
Usage
Currently the system supports 3 modes:
Mode | Action |
---|---|
version (default) | Display the version and license information and exit |
apply | Update the database! |
check | Read all *.sql file and report any SQL errors or MySQL 1215 errors |
plan | Dump a list of MySQL commands that will bring the database up-to-spec with the *.sql files |
plan_export | Dump a list of data showing how to update the *.sql files to match the database |
Each should be executed by running the mygration command with the desired mode as the first parameter, in the same directory as your mygrate.conf
file:
mygrate.py [mode]
The typical use case would be to run mygrate.py plan
and inspect the results. If things seem reasonable then simply mygrate.py execute
Advantages
There are plenty of migration tools out there, and many frameworks come with their own. So why would I write another, and why would anyone setup a new tool if one comes out-of-the-box with their framework of choice? Because the declarative approach taken by mygrations
has a number of concrete advantages.
1. Database structure trackable by version control
In mygrations
each database is defined by a single CREATE TABLE
command living in one file. Adjusting a table's structure means adjusting the CREATE TABLE
command in the table's definition file. As a result, if two developers attempt to change the same table in conflicting ways, the conflict will be picked up immediately at merge time by your version control system. Because normal migration systems put each database change in its own file version control cannot pick up any conflicts. Instead, conflicting table definitions are not found until after a merge when the next migration is run and an SQL error is generated. This way, potential conflicts are found much sooner.
2. MySQL Linting
Unlike normal migration systems which simply apply developer-provided transformations to update your tables, mygrations
is an intelligent tool that understands the actual structure you are trying to create. As a result it is possible to perform checks to find easy-to-miss syntax errors, as well as enforce project standards. There are many possibilities:
- Generate warnings about syntax errors in your SQL files
- Enforce system-wide naming conventions on column names
- Verify that all foreign key columns actually have foreign key constraints
- Set rules to determine what column types should/should not be used
- ... more when I think of it
Mainly though, my intention is to make a linting system that is fully configurable by the end user.
3. Better foreign key errors
Does this look familiar?
ERROR 1215 (HY000): Cannot add foreign key constraint
I've seen developers at all skill levels waste many hours while creating foreign key constraints due to the above error from MySQL, which gives absolutely no hints as to what the problem actually is. Because mygrations
understands what the database is supposed to look like, it can detect the actual conditions that cause this error and provide a specific and actionable error message to the developer.
4. Migration plans
Again, because mygrations
operates with knowledge of both the current database and the target database, it can present an actual migration plan before making any changes. This makes it easy for the developer to have one last spot check before making changes, if desired.
5. One table, one file
Standard migration systems dedicate a file to each change of a database table. As a result, it is very difficult to figure out what the database structure should be simply by looking at the contents of the migration directory. Having one table per file makes it easy to spot check your migrations and make sure nothing has been missed.
6. No Migration table
Since mygrations
works directly with the database structure it doesn't need to keep a history of which migrations it has run. Instead, it brings your database up-to-spec no matter what state it is in: no more hassle if your migration table somehow gets out of sync with your migration files.
7. Automatic migration builder
Since the migration files are just simple CREATE TABLE
commands, mygrations
can create the migration files for you from your database. Although you probably won't have to do that very often because the migrations files are very easy to build anyway. A simple SHOW CREATE TABLE
command from MySQL is all you need.
Roadmap to 1.0
This is a brand new venture that is a long way from complete. To give some guidance, here is my target feature list for when version 1.0 will officially be released:
- Parsing of
CREATE TABLE
andINSERT
commands and using those as migration definitions (Done) - Automatic foreign key dependency calculation (Sidelined)
- Detailed foreign key error notices (Done)
- Ability to migrate database to match definitions from any state (Done)
- Generation of migration commands (Done)
- Generation of migration files (Done)
Currently the system has reached a complete enough state that it is being tested in our real-world systems.
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.