Awesome

schemadiff

Command line tool for declarative MySQL schema validation, normalization and diffing, based on Vitess' schemadiff library.

Overview

The schemadiff command line tool is a thin wrapper around the Vitess schemadiff library, which offers declarative schema analysis, validation, normalization, diffing and manipulation. Read more about the schemadiff library on the Vitess blog.

The command line tool makes schema normalization, validation, and diffing accessible in testing, automation and scripting environments. You may load or compare schemas from SQL files, directories, standard input, or as read from a MySQL server.

schemadiff is declarative, which means it does not need a MySQL server to operate. schemadiff works by parsing the schema's CREATE TABLE|VIEW statements and by applying MySQL compatible analysis and validation to those statements. For convenience, the schemadiff command line tool supports reading a schema from a MySQL server. schemadiff applies its own normalization of table/view definitions, resulting in consistent and as compact as possible representations of the schema.

The schemadiff executable supports these operations:

• load: read a table, view or full schema, validate, normalize, and output the normalized form.
• diff: given two schemas, source and target, output the DDL (CREATE, ALTER, DROP) statements that when applied to the source schema, result in target schema. The output is empty when the two schemas are identical.
• ordered-diff: similar to diff but stricter, output the DDL in a sequential-applicable order, or fail if such order cannot be found. This operation resolves dependencies between the diffs themselves, such as changes made to both tables and views that depend on those tables, or tables involved in a foreign key relationships.
• diff-table: given two table definitions, source and target, output the ALTER TABLE statement that would convert the source table into target. The two tables may have different names. The output is empty when the two tables are identical.
• diff-view: given two view definitions, source and target, output the ALTER VIEW statement that would convert the source view into target. The two views may have different names. The output is empty when the two tables are identical.

schemadiff diffs according to a pre-defined set of hints. For example, schemadiff will completely ignore AUTO_INCREMENT values of compared tables. At this time these hints are not configurable.

schemadiff supports:

• MySQL 8.0 dialect.
• TABLE and VIEW definitions. Stored routines (procedures/functions/triggers/events) are unsupported.
• Nested views, view table and column validation.
• Check constraints, virtual columns, expressions.
• Foreign keys, nested foreign keys. Self-referencing tables are supported, otherwise cyclic foreign keys are not.
• Partitions: non-optimal diffs, in most situations rebuilding the entire partitioning scheme.

Usage and examples

load

• Read from standard output, validate and normalize:

$ echo "create table t (id int(11) unsigned primary key)" | schemadiff load

CREATE TABLE `t` (
	`id` int unsigned,
	PRIMARY KEY (`id`)
);

• Read an invalid definition:

$ echo "create table t (id int unsigned primary key, key name_idx (name))" | schemadiff load
invalid column `name` referenced by key `name_idx` in table `t`

• Read a definition with invalid view dependencies:

$ echo "create table t (id int primary key); create view v as select id from some_table" | schemadiff load
view `v` has unresolved/loop dependencies

• Read schema from file:

$ echo "create table t (id int primary key); create view v as select id from t" > /tmp/schema.sql
$ schemadiff load --source /tmp/schema.sql

CREATE TABLE `t` (
	`id` int,
	PRIMARY KEY (`id`)
);
CREATE VIEW `v` AS SELECT `id` FROM `t`;

• Read schema from directory. schemadiff reads all .sql files in given path. Each file is expected to contain a single statement:

$ schema_dir=$(mktemp -d)
$ echo "create table t (id int primary key)" > $schema_dir/t.sql
$ echo "create table t2 (id int primary key, name varchar(128) not null default '')" > $schema_dir/t2.sql
$ schemadiff load --source $schema_dir

CREATE TABLE `t` (
	`id` int,
	PRIMARY KEY (`id`)
);
CREATE TABLE `t2` (
	`id` int,
	`name` varchar(128) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
);

• Read a full schema from a running MySQL server. schemadiff reads the SHOW CREATE TABLE statements for all tables and views in the given schema. Provide a valid DSN in go-sql-driver format:

$ schemadiff load --source 'myuser:mypass@tcp(127.0.0.1:3306)/test'

CREATE TABLE `t` (
	`id` int,
	PRIMARY KEY (`id`)
);
CREATE TABLE `t2` (
	`id` int,
	`name` varchar(128) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
);

• Read a specific table from a running MySQL server. Syntax is a valid DSN with table indicated as comment:

$ schemadiff load --source 'myuser:mypass@tcp(127.0.0.1:3306)/test?#t2'

CREATE TABLE `t2` (
	`id` int,
	`name` varchar(128) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
);

diff

• Diff two schemas:

$ echo "create table t (id int primary key); create view v as select id from t" > /tmp/schema_v1.sql
$ echo "create table t (id bigint primary key); create table t2 (id int primary key, name varchar(128) not null default '')" > /tmp/schema_v2.sql
$ schemadiff diff --source /tmp/schema_v1.sql --target /tmp/schema_v2.sql

DROP VIEW `v`;
ALTER TABLE `t` MODIFY COLUMN `id` bigint;
CREATE TABLE `t2` (
	`id` int,
	`name` varchar(128) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`)
);

• Reverse the above diff. Show what it takes to convert schema_v2 to schema_v1:

$ schemadiff diff --source /tmp/schema_v2.sql --target /tmp/schema_v1.sql

DROP TABLE `t2`;
ALTER TABLE `t` MODIFY COLUMN `id` int;
CREATE VIEW `v` AS SELECT `id` FROM `t`;

• Compare a running MySQL server's schema with schema found in a directory's .sql files (each file expected to contain a single CREATE statement):

$ schemadiff diff --source 'myuser:mypass@tcp(127.0.0.1:3306)/test' --target /path/to/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`)
);

• Generate a valid schema destruction sequence:

$ echo "create table t (id int primary key); create view v as select id from t" > /tmp/schema.sql
$ echo "" | schemadiff diff --source /tmp/schema.sql

DROP VIEW `v`;
DROP TABLE `t`;

• Same as above, alternative method:

$ echo > /tmp/empty_schema.sql
$ echo "create table t (id int primary key); create view v as select id from t" > /tmp/schema.sql
$ schemadiff diff --source /tmp/schema.sql --target /tmp/empty_schema.sql

DROP VIEW `v`;
DROP TABLE `t`;

ordered-diff

• Generate a diff that has a strict ordering dependency:

$ echo "create table parent (id int primary key, uuid varchar(32) charset ascii); create table child (id int primary key, parent_uuid varchar(32) charset ascii)" > /tmp/schema_v1.sql
$ echo "create table parent (id int primary key, uuid varchar(32) charset ascii, unique key uuid_idx (uuid)); create table child (id int primary key, parent_uuid varchar(32) charset ascii, foreign key (parent_uuid) references parent (uuid))" > /tmp/schema_v2.sql
$ schemadiff ordered-diff --source /tmp/schema_v1.sql --target /tmp/schema_v2.sql

ALTER TABLE `parent` ADD UNIQUE KEY `uuid_idx` (`uuid`);
ALTER TABLE `child` ADD KEY `parent_uuid` (`parent_uuid`), ADD CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_uuid`) REFERENCES `parent` (`uuid`);

Note that in the above the change on child cannot take place before the change on parent, because a MySQL foreign key requires an index on the referenced table column(s). ordered-diff returns an error if there is no serial sequence of steps which maintains validity at each step.

diff-table

• Compare two tables. Note they may have different names; schemadiff will use the source table name:

$ echo "create table t1 (id int primary key)" > /tmp/t1.sql
$ echo "create table t2 (id bigint unsigned primary key, ranking int not null default 0)" > /tmp/t2.sql
$ schemadiff diff-table --source /tmp/t1.sql --target /tmp/t2.sql

ALTER TABLE `t1` MODIFY COLUMN `id` bigint unsigned, ADD COLUMN `ranking` int NOT NULL DEFAULT 0;

Consider that running schemadiff diff on the same tables above yields with DROP TABLE for t1 and CREATE TABLE for t2.

• Compare two tables, one from standard input, the other from the database:

$ echo "create table t1 (id int primary key)" | schemadiff diff-table --target 'myuser:mypass@tcp(127.0.0.1:3306)/test?#t2'

ALTER TABLE `t1` MODIFY COLUMN `id` bigint unsigned NOT NULL, ADD COLUMN `ranking` int NOT NULL DEFAULT '0', ENGINE InnoDB CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

diff-view

• Compare two views. Note they may have different names; schemadiff will use the source view name:

$ echo "create view v1 as select id from t1" > /tmp/v1.sql
$ echo "create view v2 as select id, name from t1" > /tmp/v2.sql
$ schemadiff diff-view --source /tmp/v1.sql --target /tmp/v2.sql

ALTER VIEW `v1` AS SELECT `id`, `name` FROM `t1`;

Consider that running schemadiff diff on the same views above results with validation error, because the referenced table t1 does not appear in the schema definition. diff-view does not attempt to resolve dependencies.

Textual diff format output

You may add --textual flag to get a diff-format output rather than semantic SQL output:

echo "create table t (id int primary key, i int); create view v as select id from t" > /tmp/schema_v1.sql
echo "create table t (id bigint primary key, i int, key (i)); create table t2 (id int primary key, name varchar(128) not null default '')" > /tmp/schema_v2.sql
schemadiff diff --source /tmp/schema_v1.sql --target /tmp/schema_v2.sql --textual

-CREATE VIEW `v` AS SELECT `id` FROM `t`;
 CREATE TABLE `t` (
-	`id` int,
+	`id` bigint,
 	`i` int,
 	PRIMARY KEY (`id`)
+	KEY `i` (`i`)
 );
+CREATE TABLE `t2` (
+	`id` int,
+	`name` varchar(128) NOT NULL DEFAULT '',
+	PRIMARY KEY (`id`)
+);

The textual diff still works semantically under the hood, and it will ignore trailing comma changes, index reordering, cosntraint name changes, etc.

Binaries

Binaries for linux/amd64 and for darwin/arm64 are available in Releases.

The CI action builds a Linux/amd64 schemadiff binary as artifact. See Actions

Build

To build schemadiff, run:

$ make all

Or, directly invoke:

$ go build -trimpath -o bin/schemadiff ./cmd/schemadiff/main.go

schemadiff was built with go1.20.

License

schemadiff command line tool is released under Apache 2.0 license