In this post we will do a comparsion of various Go schema migration tools. This post is written in 2017, so things might have changed since then.

Are you learning Go? Have you read The Go Programming Language & Go in Action books? These books have greatly helped when I was just starting with Go. If you like to learn by example, definitely get the Go in Action.

Let’s begin, We will compare the following tools:

• https://github.com/markbates/pop/soda
• https://github.com/GuiaBolso/darwin
• https://bitbucket.org/liamstask/goose
• https://github.com/go-gormigrate/gormigrate
• https://github.com/mattes/migrate
• https://github.com/pravasan/pravasan
• https://github.com/rubenv/sql-migrate
• https://github.com/BurntSushi/migration
• https://github.com/DavidHuie/gomigrate
• https://github.com/tanel/dbmigrate
• https://github.com/pressly/goose

Summary

TL;DR If your looking for schema migration tool you can use:

mattes/migrate, SQL defined schema migrations, with a well defined and documented API, large database support and a useful CLI tool. This tool is actively maintained, has a lot of stars and an A+ from goreport.

rubenv/sql-migrate, go struct based or SQL defined schema migrations, with a config file, migration history, prod-dev-test environments. The only drawback is that it got B from goreport.

markbates/pop, use this if you are looking for an ORM like library. It has awesome model generation capabilities and a custom DSL language for writing schema changes.

go-gormigrate/gormigrate, use this if you are using GORM, this helper adds proper schema versioning and rollback capabilities.

Why these tools? Read below:

Comparing by Stars

Note: I put goose into 4th place because it has a lot of watchers.

I use Github stars as a metric to see how widely project is used. Because it is almost impossible to see how many users actually use it. I bolded out the tools that win in this category.

Comparing by last activity

Note: I scrapped this info on Wednesday, February 15, 2017 3:00 pm EET. 

More often than not you want to use projects that are maintained. So last activity can be seen as a measure of project’s maintainability. It is important because if there is a bug you want to have an ability to submit a PR or create an issue, which hopefully would get resolved. Bolded out tools win in this category.

Comparing by goreportcard

Note: I scrapped this info on Wednesday, February 15, 2017 3:00 pm EET. 

Goreportcard allows you to check the code quality of any open source project written in go and gives an overall score (A+, A, B,..). I bolded out the tools that win in this category.

Comparing by usability

In this comparison I will try out some of the tools and give my opinion.

I will be playing around with a table MyGuests, which looks like this:

CREATE TABLE MyGuests (
   id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   firstname VARCHAR(30) NOT NULL,
   lastname VARCHAR(30) NOT NULL,
   email VARCHAR(50),
   reg_date TIMESTAMP
)

mattes/migrate

This is a simple tool, which does migrations based on files. It comes with a Go library and a CLI tool, which helps you to create SQL migration files and manages schema version. Let’s take a look at the example usage of CLI tool below:

$ migrate -url mysql://root@tcp(127.0.0.1:3306)/mattes -path ./migrations 
 create initial_users_table

creates 2 files and a table called schema_migrations:

Version 1487240220 migration files created in ./migrations:
1487240220_initial_users_table.up.sql
1487240220_initial_users_table.down.sql

I added CREATE TABLE MyGuests … statement in file called *up.sql and DROP TABLE MyGuests.. statement in *down.sql

Running migration using CLI is simple:

$ migrate -url mysql://root@tcp(127.0.0.1:3306)/mattes 
  -path ./migrations up

This creates a table and sets a row in schema_migrations table:

mysql> select * from schema_migrations;
+------------+
| version |
+------------+
| 1487240220 |
+------------+
1 row in set (0.00 sec)

Here is an example of running  a “down” migration, which drops the table and removes a row from schema_migrations:

$ migrate -url mysql://root@tcp(127.0.0.1:3306)/mattes 
  -path ./migrations down

CLI tool also allows going to specific schema version,  rollbacking previous n migrations, etc.

The provided go library is also pretty simple, it allows you to run migrations from your code and provides you with synchronous and asynchronous implementations. Probably you will only be using UpSync function from your code. Take a look at the example below:

package main

import (
   "fmt"
   _ "github.com/mattes/migrate/driver/mysql"
   "github.com/mattes/migrate/migrate"
)

func main() {
   fmt.Println("Hello")
   allErrors, ok := migrate.UpSync("mysql://root@tcp(127.0.0.1:3306)/mattes", "./migrations")
   if !ok {
      fmt.Println(allErrors)
   }
}

I like this library for it’s simplicity. It supports PostgreSQL, Cassandra, SQLite, MySQL, Neo4j, Ql, MongoDB, CrateDb. But it has a caveat: MySQL support is only experimental.

liamstask/goose

Playing around with this library was a bit painful for me. For about 20 minutes I couldn’t figure out what was wrong with my connection info. I was continuously getting Invalid DBConf errors, with no explanations:

2017/02/16 13:14:54 Invalid DBConf: 
   {mysql  root@tcp(127.0.0.1:3306)/goose  }

It appears to me now that I had left a space after specifying database type!

So in goose you have to create a dir called db and add a file called dbconf.yaml , which contains connection information. This is how my file looked:

development: 
  driver: mysql
  open: root@tcp(127.0.0.1:3306)/goose

In this config you are also allowed to choose your SQL dialect and import a different db driver.

Creating a migration with goose is easy:

goose create initial_users_table

which creates a  file called 20170216132820_initial_users_table.go, which contains 2 go functions:

func Up_20170216132820(txn *sql.Tx) {

}

func Down_20170216132820(txn *sql.Tx) {

}

Here is how I filled these functions:

// Up is executed when this migration is applied
func Up_20170216132820(txn *sql.Tx) {
   res, err := txn.Exec(`CREATE TABLE MyGuests (
      id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      firstname VARCHAR(30) NOT NULL,
      lastname VARCHAR(30) NOT NULL,
      email VARCHAR(50),
      reg_date TIMESTAMP
   )`)
  fmt.Println(res)
  fmt.Println(err)
}

// Down is executed when this migration is rolled back
func Down_20170216132820(txn *sql.Tx) {
   res, err := txn.Exec("DROP TABLE MyGuests;")
   fmt.Println(res)
   fmt.Println(err)
}

Executing up/down migrations is also easy:

goose up

goose down

Internally goose maintains a table called goose_db_version:

 mysql> select * from goose_db_version;
+----+----------------+------------+---------------------+
| id | version_id | is_applied | tstamp |
+----+----------------+------------+---------------------+
| 1 | 0 | 1 | 2017-02-16 13:37:20 |
| 2 | 20170216132820 | 1 | 2017-02-16 13:37:47 |
| 3 | 20170216132820 | 0 | 2017-02-16 13:39:32 |
| 4 | 20170216132820 | 1 | 2017-02-16 13:40:04 |
| 5 | 20170216134743 | 1 | 2017-02-16 13:51:30 |
| 6 | 20170216134743 | 0 | 2017-02-16 13:51:34 |
+----+----------------+------------+---------------------+
6 rows in set (0.00 sec)

This tools also allows you to specify migration using SQL files, by default this tool supports postgres, mysql, sqlite3 and has a go library. Mostly I liked that you specify connection info in a config file, which simplifies your work with the CLI. Also, writing db migrations as go code looks interesting! Overall, not a bad tool.

markbates/pop

pop is more like an “ORM”, which helps you to create models and sql schema for you. pop also comes with migration capabilities in a CLI tool called soda and a DSL for specifying migrations called fizz.

At the start you have to specify database connection config in a database.yaml file. Mine looked like this:

development:
  dialect: "mysql"
  database: "pop"
  host: "localhost"
  port: "3306"
  user: "root"
  password: ""

Then you can create/drop a database using CLI tool:

soda create -e development
soda drop -e development

Generating a model with it’s migration script based on fizz DSL is simple:

soda generate model MyGuest firstname:text lastname:text email:text 
  reg_date:timestamp

Generated DSL looks like this:

create_table("my_guests", func(t) {
   t.Column("id", "uuid", {"primary": true})
   t.Column("firstname", "text", {})
   t.Column("lastname", "text", {})
   t.Column("email", "text", {})
   t.Column("reg_date", "timestamp", {})
})

and model:

type MyGuest struct {
        ID uuid.UUID `json:"id" db:"id"`
        CreatedAt time.Time `json:"created_at" db:"created_at"`
        UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
        Firstname string `json:"firstname" db:"firstname"`
        Lastname string `json:"lastname" db:"lastname"`
        Email string `json:"email" db:"email"`
        RegDate time.Time `json:"reg_date" db:"reg_date"`
}

Migrate up/down:

soda migrate up 
soda migrate down

On migration Fizz DSL produced the following schema:

mysql> desc my_guests;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| id | char(36) | NO | PRI | NULL | |
| firstname | text | NO | | NULL | |
| lastname | text | NO | | NULL | |
| email | text | NO | | NULL | |
| reg_date | datetime | NO | | NULL | |
+------------+----------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Internally this tool maintains a table called schema_migration, which holds schema version number.

I liked this library a lot, but it feel like you should only use this then you are looking for “ORM” like library. Generating models and migrations looks cool! Also, bonus points for Fizz DSL, which looks a lot like go 🙂

One drawback is that pop supports only : PostgreSQL (>= 9.3), MySQL (>= 5.7) and SQLite (>= 3.x).

go-gormigrate/gormigrate

Gormigrate is a migration helper for GORM library. This helper adds proper schema versioning and rollback cababilities. I like schema versioning + schema migration definition in a list of structs. This is how this looks with MyGuests example:

func main() {
        db, err := gorm.Open("mysql",
    "root@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local")
        if err != nil {
                panic("failed to connect database")
        }

        if err = db.DB().Ping(); err != nil {
                log.Fatal(err)
        }

        db.LogMode(true)

        defer db.Close()

        m := gormigrate.New(db, gormigrate.DefaultOptions, 
             []*gormigrate.Migration{
                {
                        ID: "201702200906",
                        Migrate: func(tx *gorm.DB) error {
                                type MyGuest struct {
                                        gorm.Model
                                        Firstname string
                                        Lastname  string
                                        Email     string
                                        RegDate   time.Time
                                }
                                return tx.AutoMigrate(&MyGuest{}).Error
                        },
                        Rollback: func(tx *gorm.DB) error {
                                return tx.DropTable("MyGuest").Error
                        },
                },
        })

        if err = m.Migrate(); err != nil {
                log.Fatalf("Could not migrate: %v", err)
        }
}

This migration creates a table, with the following schema:

mysql> desc my_guests;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
| deleted_at | timestamp        | YES  | MUL | NULL    |                |
| firstname  | varchar(255)     | YES  |     | NULL    |                |
| lastname   | varchar(255)     | YES  |     | NULL    |                |
| email      | varchar(255)     | YES  |     | NULL    |                |
| reg_date   | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

I would definitely use this with GORM.

rubenv/sql-migrate

sql-migrate is a look like goose, you specify connection info in a yaml file, migrations are written in SQL files. It has a CLI, which generates a template for your migration:

$ sql-migrate new MyGuests

My schema change looked like this:

-- +migrate Up
CREATE TABLE MyGuests (
           id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
           firstname VARCHAR(30) NOT NULL,
           lastname VARCHAR(30) NOT NULL,
           email VARCHAR(50),
           reg_date TIMESTAMP
);

-- +migrate Down
DROP TABLE MyGuests;

Applying migration with CLI tool is pretty straightforward:

$ sql-migrate up
Applied 1 migration

It also has nice looking library, with well defined API, which supports having migrations in a struct or in a directory. The only drawback I can think of is that goreport card gave a B for this library.

DavidHuie/gomigrate

Is a really simple toolkit, which only allows you to run migrations from go code:

err := migrator.Migrate()
err := migrator.Rollback()

migration are defined in sql files named { id }}{{ name }}{{ “up” or “down” }}.sql, which you have to manage yourself, because it’s only a library. mattes/migrate seems to cover the same functionality and add much more, so I would prefer to use it over this library.

GuiaBolso/darwin

It’s a library, which tracks schema changes in a struct and only allows up migrations. I love the idea of storing all migrations in a slice:

var (
   migrations = []darwin.Migration{
   {
   Version: 1,
   Description: "Creating table MyGuests",
   Script: `CREATE TABLE MyGuests (
              id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
              firstname VARCHAR(30) NOT NULL,
              lastname VARCHAR(30) NOT NULL,
              email VARCHAR(50),
              reg_date TIMESTAMP
            )`,
   },
   }
)

func main() {
   database, err := sql.Open("mysql", "root@tcp(127.0.0.1:3306)/darwin")

   if err != nil {
     log.Fatal(err)
   }

   driver := darwin.NewGenericDriver(database, darwin.MySQLDialect{})

   d := darwin.New(driver, migrations, nil)
   err = d.Migrate()

   if err != nil {
     log.Println(err)
   }
}

I don’t think there is more to say about this library, but I guess it’s a good thing.

tanel/dbmigrate

Simple library for PostgreSQL or Cassandra. Runs migrations (.sql or .cql files) sorted using their file name. mattes/migrate seems to cover the same functionality and add much more, so I would prefer to use it over this.

pressly/goose

It’s a fork of goose, which drops support for config files and custom drivers. Migrations can be run with any driver that is compatible with database/sql. This tool looks like liamstask/goose and mattes/migrate had a baby 🙂 It takes good things from both projects: good CLI, no configuration, write migrations using .sql or .go files, store history of migrations in a goose_db_version table. But there are some things that this tools lacks: it doesn’t support Cassandra or any other non SQL database, CLI can migrate SQL files only.

Then trying to use it I had problems with this tool:

I created SQL based migration:

$ goose mysql "root@tcp(127.0.0.1:3306)/pressly" create initial_users_table sql
Created sql migration at 20170301085637_initial_users_table.sql

Filled 20170301085637_initial_users_table.sql file with:

-- +goose Up
-- SQL in section 'Up' is executed when this migration is applied

CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)


-- +goose Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE MyGuests;

$ goose mysql "root@tcp(127.0.0.1:3306)/pressly" up
2017/03/01 08:57:41 WARNING: Unexpected unfinished SQL query: -- +goose Up
-- SQL in section 'Up' is executed when this migration is applied

CREATE TABLE MyGuests (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
). Missing a semicolon?
OK    20170301085637_initial_users_table.sql
goose: no migrations to run. current version: 20170301085637
$ goose mysql "root@tcp(127.0.0.1:3306)/pressly" down
2017/03/01 08:57:47 FAIL 20170301085637_initial_users_table.sql (Error 1051: Unknown table 'pressly.myguests'), quitting migration.
$ goose mysql "root@tcp(127.0.0.1:3306)/pressly" up
goose: no migrations to run. current version: 20170301085637

I did miss the semicolon, but this tool wrote into it’s table that migration ran successfully, so I was left at state where table doesn’t exist, but tool thinks it’s exist.

mysql> select * from goose_db_version;
+----+----------------+------------+---------------------+
| id | version_id     | is_applied | tstamp              |
+----+----------------+------------+---------------------+
|  1 |              0 |          1 | 2017-03-01 08:44:45 |
|  2 | 20170301085637 |          1 | 2017-03-01 08:57:41 |
+----+----------------+------------+---------------------+

I had to manually delete the row in goose_db_version the table and restart the migration…
After that everything worked normally:

$ pressly goose mysql "root@tcp(127.0.0.1:3306)/pressly" up
OK    20170301085637_initial_users_table.sql
goose: no migrations to run. current version: 20170301085637
$ pressly goose mysql "root@tcp(127.0.0.1:3306)/pressly" down
OK    20170301085637_initial_users_table.sql

Also I tried to run migration based on go code, but wasn’t successful:
I created the migration and compiled the example cmd file provided in the repo for running migrations:

$ goose mysql "root@tcp(127.0.0.1:3306)/pressly" create initial_users_table
 Created go migration at 20170301083810_initial_users_table.go

and filled with my migration code:

package migration

import (
    "database/sql"
    "fmt"

    "github.com/pressly/goose"
)

func init() {
    goose.AddMigration(Up_20170301083810, Down_20170301083810)
}

func Up_20170301083810(tx *sql.Tx) error {
    res, err := tx.Exec(`CREATE TABLE MyGuests (
           id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
           firstname VARCHAR(30) NOT NULL,
           lastname VARCHAR(30) NOT NULL,
           email VARCHAR(50),
           reg_date TIMESTAMP
       )`)
    fmt.Println(res)
    return err
}

func Down_20170301083810(tx *sql.Tx) error {
    res, err := txn.Exec("DROP TABLE MyGuests;")
    fmt.Println(res)
    return err
}

Then I tried to run:

./pressly --dir=migrations/ mysql "root@tcp(127.0.0.1:3306)/pressly" up
2017/03/01 09:02:24 FAIL 00002_rename_root.go 
(Error 1146: Table 'pressly.users' doesn't exist), quitting migration.

my migrations directory contains only 1 file called 20170301083810_initial_users_table.go, there is no file called 00002_rename_root.go, so I don’t know what the hell this tool is doing, but I really don’t like that it tries to run file called rename_root.go, which I didn’t write and don’t know nothing about.

So be careful with this tool!