mgrt is a simple tool for managing revisions across SQL databases. It takes SQL scripts, runs them against the database, and keeps a log of them.
To install mgrt, clone the repository and run the ./make.sh script,
$ git clone https://github.com/andrewpillar/mgrt
$ cd mgrt
$ ./make.sh
to build mgrt with SQLite3 support add sqlite3 to the TAGS environment
variable,
$ TAGS="sqlite3" ./make.sh
this will produce a binary at bin/mgrt, add this to your PATH.
Once installed you can start using mgrt right away, there is nothing to
initialize. To begin writing revisions simply invoke mgrt add,
$ mgrt add "My first revision"
this will create a new revision file in the revisions directory, and open
it up for editting with the revision to write,
/*
Revision: 20060102150405
Author: Andrew Pillar <me@andrewpillar.com>
My first revision
*/
CREATE TABLE users (
id INT NOT NULL UNIQUE
);
once you've saved the revision and quit the editor, you will see the revision ID printed out,
$ mgrt add "My first revision"
revision created 20060102150405
local revisions can be viewed with mgrt ls. This will display the ID, the
author of the revision, and its comment, if any,
$ mgrt ls
20060102150405: Andrew Pillar <me@andrewpillar.com> - My first revision
revisions can be applied to the database via mgrt run. This command takes two
flags, -type and -dsn to specify the type of database to run the revision
against, and the data source for that database. Let's run our revision against
an SQLite3 database,
$ mgrt run -type sqlite3 -dsn acme.db
revisions can only be performed on a database once, and cannot be undone. We can
view the revisions that have been run against the database with mgrt log. Just
like mgrt run, we use the -type and -dsn flags to specify the database to
connect to,
$ mgrt log -type sqlite3 -dsn acme.db
revision 20060102150405
Author: Andrew Pillar <me@andrewpillar.com>
Performed: Mon Jan 6 15:04:05 2006
My first revision
CREATE TABLE users (
id INT NOT NULL UNIQUE
);
this will list out the revisions that have been performed, along with the SQL code that was executed as part of that revision.
mgrt also offers the ability to sync the revisions that have been performed on
a database against what you have locally. This is achieved with mgrt sync, and
just like before, this also takes the -type and -dsn flags. Lets delete the
revisions directory that was created for us and do a mgrt sync.
$ rm -rf revisions
$ mgrt ls
$ mgrt sync -type sqlite3 -dsn acme.db
$ mgrt ls
20060102150405: Andrew Pillar <me@andrewpillar.com> - My first revision
with mgrt sync you can easily view the revisions that have been run against
different databases.
Database connections for mgrt can be managed via the mgrt db command. This
allows you to set aliases for the different databases you can connect to,
for example,
$ mgrt db set local-db postgresql "host=localhost port=5432 dbname=dev user=admin password=secret"
this can then be used via the -db flag for the commands that require a
database connection.
The mgrt db set command expects the type of the database, and the DSN for
connecting to the database. The type will be one of,
- mysql
- postgresql
- sqlite3
the DSN will vary depending on the type of database being used. The mysql and postgresql you can use the URI connection string, such as,
type://[user[:password]@][host]:[port][,...][/dbname][?param1=value1&...]
where type would either be mysql or postgresql. The postgresql type also allows for the DSN string such as,
host=localhost port=5432 dbname=mydb connect_timeout=10
sqlite3 however will accept a filepath.
You can also specify the -type and -dsn flags too. These take the same
arguments as above. The -db flag however is more convenient to use.
Revisions are SQL scripts that are performed against the given database. Each revision can only be performed once, and cannot be undone. If you wish to undo a revision, then it is recommended to write another revision that does the inverse of the prior.
Revisions are stored in the revisions directory from where the mgrt add
command was run. Each revision file is prefixed with a comment block header
that contains metadata about the revision itself, such as the ID, the author and
a short comment about the revision.
Revisions can be organized into categories via the command line. This is done
by passing the -c flag to the mgrt add command and specifying the category
for that revision. This will create a sub-directory in the revisions directory
containing that revision. Revisions in a category will only be performed when
the -c flag for that category is given to the mgrt run command.
Organizing revisions into categories can be useful if you want to keep certain revision logic separate from other revision logic. For example, if you want to separate table creation from permission granting, you could do something like,
$ mgrt add -c schema "Create users table"
$ mgrt add -c perms "Grant permissions on users table"
then, to perform the above revisions you would,
$ mgrt run -c schema -db prod
$ mgrt run -c perms -db prod
Each time a revision is performed, a log will be made of that revision. This log
is stored in the database, in the mgrt_revisions table. This will contain the
ID, the author, the comment (if any), and the SQL code itself, along with the
time of execution.
The revisions performed against a database can be viewed with mgrt log,
$ mgrt log -db local-dev
revision 20060102150405
Author: Andrew Pillar <me@andrewpillar.com>
Performed: Mon Jan 6 15:04:05 2006
My first revision
Local revisions can be viewed with mgrt cat. This simply takes a list of
revision IDs to view.
$ mgrt cat 20060102150405
/*
Revision: 20060102150405
Author: Andrew Pillar <me@andrewpillar.com>
My first revision
*/
CREATE TABLE users (
id INT NOT NULL UNIQUE
);
The -sql flag can be passed to the command too to only display the SQL portion
of the revision,
$ mgrt cat -sql 20060102150405
CREATE TABLE users (
id INT NOT NULL UNIQUE
);
performed revisions can also be seen with mgrt show. You can pass a revision
ID to mgrt show to view an individual revision. If no revision ID is given,
then the latest revision is shown.
$ mgrt show -db local-dev 20060102150405
revision 20060102150405
Author: Andrew Pillar <me@andrewpillar.com>
Performed: Mon Jan 6 15:04:05 2006
My first revision
CREATE TABLE users (
id INT NOT NULL UNIQUE
);
As well as a CLI application, mgrt can be used as a library should you want to be able to have revisions performed directly in your application. To start using it just import the repository into your code,
import "github.com/andrewpillar/mgrt"
from here you will be able to start creating revisions and performing them against any pre-existing database connection you may have,
// mgrt.Open will wrap sql.Open from the stdlib, and initialize the database
// for performing revisions.
db, err := mgrt.Open("sqlite3", "acme.db")
if err != nil {
panic(err) // maybe acceptable here
}
rev := mgrt.NewRevision("Andrew", "This is being done from Go.")
if err := rev.Perform(db); err != nil {
if !errors.Is(err, mgrt.ErrPerformed) {
panic(err) // not best practice
}
}
all pre-existing revisions can be retrieved via GetRevisions,
revs, err := mgrt.GetRevisions(db)
if err != nil {
panic(err) // don't actually do this
}
more information about using mgrt as a library can be found in the Go doc itself for mgrt.