dikang123/schemalex

Generate difference sql of two mysql schema

schemalex

Generate the difference of two mysql schema

SYNOPSIS

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

SYNOPSIS (Using the library)

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;
}

SEE ALSO

• http://rspace.googlecode.com/hg/slide/lex.html#landing-slide
• http://blog.gopheracademy.com/advent-2014/parsers-lexers/
• https://github.com/soh335/git-schemalex

LICENSE

MIT