Last week we open sourced morphism, a tool for working with MySQL databases. This was previously an internal tool but is now available for everyone to use.
This tool has two main areas of functionality:
- parsing, extracting and diffing MySQL dump files.
- easily migrating schema structure from one version to another.
You can install it using Composer:
$ composer require graze/morphism
We were previously using an internally developed database migration tool which relied on 'up' and 'down' scripts to manage migration between schema versions. This had a number of issues however. In particular it assumes that schema evolution is linear - you can only ever move forward in time to a newer version, or back to an older version. In practice, modern development is such that you may be working on several different possible versions of the future state of the schema in parallel as you switch between different development branches.
We decided to develop a tool that would allow you to store the complete database schema in the repository. When a branch requires a schema update to work properly, you should edit your checked out schema files and run the new tool to figure out the necessary
DROP statements to run, and apply them. For example:
-- Apply this change? [y]es [n]o [a]ll [q]uit: y ALTER TABLE `product` MODIFY COLUMN `pr_name` varchar(255) NOT NULL, MODIFY COLUMN `pr_description` text NOT NULL, ADD COLUMN `pr_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `pr_description`;
Similarly, when switching branches you can simply run the tool and it will apply the necessary changes automatically. The README contains an example workflow.
This has the additional benefit that the complete history of the schema is stored under version control. If more than one party changes the same table, git will merge the changes automatically, or generate a conflict for manual merging where it cannot. All the usual git tools become useful for analysing changes.
If you clone the repository, you will have access to a MySQL database in a Docker container that you can use to test out the various morphism commands and behaviour. Have a go and let us know what you think. We also welcome code contributions.