Generate the difference of two mysql schema
This tool can be used to generate the difference, or more precisely, the statements required to migrate from/to, between two MySQL schema.
Suppose you have an existing SQL schema like the following:
CREATE TABLE hoge ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) );And you want "upgrade" your schema to the following:
CREATE TABLE hoge ( id INTEGER NOT NULL AUTO_INCREMENT, c VARCHAR (20) NOT NULL DEFAULT "hoge", PRIMARY KEY (id) ); CREATE TABLE fuga ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) );Using schemalex you can generate a set of commands to perform the migration:
schemalex old.sql new.sql SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE `fuga` ( `id` INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ); ALTER TABLE `hoge` ADD COLUMN `c` VARCHAR (20) NOT NULL DEFAULT "hoge"; SET FOREIGN_KEY_CHECKS = 1; COMMIT; You can also use URI formatted strings as the sources to compare, which allow you to compare local files against online schema, a version committed to your git repository against another version, etc.
Please see the help command for a list
schemalex -version schemalex [options...] before after -v Print out the version and exit -o file Output the result to the specified file (default: stdout) -t[=true] Enable/Disable transaction in the output (default: true) "before" and "after" may be a file path, or a URI. Special URI schemes "mysql" and "local-git" are supported on top of "file". If the special path "-" is used, it is treated as stdin Examples: * Compare local files schemalex /path/to/file /another/path/to/file schemalex file:///path/to/file /another/path/to/file * Compare local file against online mysql schema schemalex /path/to/file "mysql://user:password@tcp(host:port)/dbname?option=value" * Compare file in local git repository against local file schemalex "local-git:///path/to/repo?file=foo.sql&commitish=deadbeaf" /path/to/file * Compare schema from stdin against local file .... | schemalex - /path/to/file Below is the equivalent of the previous SYNOPSIS.
package schemalex_test import ( "os" "github.com/schemalex/schemalex/diff" ) func Example() { const sql1 = `CREATE TABLE hoge ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) );` const sql2 = `CREATE TABLE hoge ( id INTEGER NOT NULL AUTO_INCREMENT, c VARCHAR (20) NOT NULL DEFAULT "hoge", PRIMARY KEY (id) ); CREATE TABLE fuga ( id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) );` diff.Strings(os.Stdout, sql1, sql2, diff.WithTransaction(true)) // OUTPUT: // BEGIN; // // SET FOREIGN_KEY_CHECKS = 0; // // CREATE TABLE `fuga` ( // `id` INTEGER NOT NULL AUTO_INCREMENT, // PRIMARY KEY (`id`) // ); // // ALTER TABLE `hoge` ADD COLUMN `c` VARCHAR (20) NOT NULL DEFAULT "hoge"; // // SET FOREIGN_KEY_CHECKS = 1; // // COMMIT; } MIT