One of the core benefits of PlanetScale database clusters is the workflow enabling zero downtime schema migrations, which are made possible by database branching.
A branch in PlanetScale is technically its own independent MySQL cluster. Deploy requests are used to apply the schema changes from one branch to another. As part of this workflow, we heavily utilize the schemadiff command provided by Vitess to calculate the schema differences between branches, determine the order of changes (including the potential for migration concurrency), and participate in three-way merge logic to apply changes to an upstream database branch.
Today we are releasing the schemadiff command line tool, a thin wrapper around Vitess's schemadiff library.
Getting started with schemadiff
The schemadiff command line tool makes it easy to validate, normalize, and diff schemas, loaded from the standard input, the file system, or from your MySQL database.
To try out schemadiff yourself, check out the schemadiff repository's releases page for the latest binaries available for Linux and Mac. The README also contains additional information on how to use the tool, when you might want to use it, and how to build it from source if you want to target another platform.
Let's take a look at a few use cases.
Describe a database
The load can be used with a source to show what's your existing database:
schemadiff load --source 'myuser:mypass@tcp(127.0.0.1:3306)/test'
-- Output:
CREATE TABLE `t` (
`id` int,
PRIMARY KEY (`id`)
);
CREATE TABLE `t2` (
`id` int,
`name` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
It can also accept a string to prettify and normalize it, which can make a schema much easier to read:
echo "create table t (id int(11) unsigned primary key)" | schemadiff load
-- Output:
CREATE TABLE `t` (
`id` int unsigned,
PRIMARY KEY (`id`)
);
Attempting to load an incorrectly formatted SQL file will result in an error. This can be extremely useful as part of a CI/CD pipeline to validate change scripts before they are applied to your database:
schemadiff load --source mydb.sql > /dev/null || echo "FAIL"
Showing changes
The diff command can compare two schemas and write the necessary DDL to execute to get them in sync. This is how we generate our change statements when merging branches in PlanetScale.
schemadiff diff --source 'myuser:mypass@tcp(127.0.0.1:3306)/test' --target /path/to/repo/source/code/schema
DROP VIEW `v`;
ALTER TABLE `t` MODIFY COLUMN `id` bigint;
CREATE TABLE `t2` (
`id` int,
`name` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
For more use cases, be sure to review the README for this project. The schemadiff command line tool supports MySQL 8 syntax and is released under Apache 2.0 license.
We hope you find it useful!