Go migrator for MySQL databases

Intention

A few months ago I started working on one of my pet projects powered by Go using MySQL as persistent data storage. After some research to find the best solution to migrate my database schema, the most popular decisions suggested for me were auto-migrator by GORM and golang-migrate utility. Unfortunately, they did not meet my expectations.

Let me explain why...

First of all, I did not want to stick to the one specific ORM and moreover, I didn't want to use ORM in my project at all. I do not want to debate whether GORM is good or not, it was just that in my case it was much more convenient and easier to use the standard library database/sql.

I also wanted to have a long-term solution that would allow me to programmatically write code to the database without delving into sql queries. Therefore, the solution using pure sql did not suit me either.

Progress

Since none of the solutions I found worked for me, I decided to write a code that would fully meet my requirements and expectations.

At first, the solution I wrote was integrated into my migration files, but I immediately noticed that the code that is used to make changes to the database can be split into a separate package. This package was still an integral part of my project, but nevertheless began to grow into something more.

After a few weeks of using my package, I decided that it proved to be quite stable and can be published in the public domain for general use. So it became part of the open-source community and my first public Go project on GitHub 💪

larapulse / migrator

MySQL database migrator

MySQL database migrator

MySQL database migrator designed to run migrations to your features and manage database schema update with intuitive go code. It is compatible with the latest MySQL v8.

Installation

To install migrator package, you need to install Go and set your Go workspace first.

1. The first need Go installed (version 1.13+ is required), then you can use the below Go command to install migrator.

$ go get -u github.com/larapulse/migrator

2. Import it in your code:

import "github.com/larapulse/migrator"

Quick start

Initialize migrator with migration entries:

var migrations = []migrator.Migration{
    {
        Name: "19700101_0001_create_posts_table"
        Up: func() migrator.Schema {
            var s migrator.Schema
            posts := migrator.Table{Name: "posts"}

            posts.UniqueID("id")
            posts.Varchar("title", 64)
            posts.Text("content", false)
            posts.Timestamps()

            s.CreateTable(posts

…

View on GitHub

Since this was my first project to Go open-source packages, I didn't know a few things.

1. How to publish my package to pkg.go.dev? The solution turned out to be quite simple and unusual. There is no need to upload your package anywhere, you just need to request it either with the help of searching for packages, or using the command line.

2. How to write good documentation for your package? The package manager independently reads the comments in front of functions and variables, and then generates documentation for your package. So be careful what and how you write in the comment before the function. Try to describe in as much detail as possible what exactly is happening and for what exactly and how this or that part of your package is used. Unfortunately, there are currently no mechanisms for editing public documentation, so you cannot change the display order or formatting to your liking. Documentation is a visual display of your comments in front of exported functions and variables. 🤷‍♂️

3. go.mod file really matters. While the unexported package can be called whatever you like (fe. module migrator), published module should be named the same as the place where it is actually located (fe. module github.com/larapulse/migrator)

Features

• Migrate, Rollback and Revert your migration:

m := migrator.Migrator{Pool: migrations}
migrated, err = m.Migrate(db)

if err != nil {
    log.Errorf("Could not migrate: %v", err)
    os.Exit(1)
}

if len(migrated) == 0 {
    log.Print("Nothing were migrated.")
}

for _, m := range migrated {
    log.Printf("Migration: %s was migrated ✅", m)
}

log.Print("Migration did run successfully")

While Migrate runs all unexecuted migrations from the pool, you can also Rollback last executed migration batch, or Rollback whole migration list.

• It creates migrations table and tracks there all executed migrations. You can set any name you like if you want.

m := migrator.Migrator{TableName: "_my_app_migrations"}

• Defining migrations with schema commands. You can create, drop or alter tables:

var migration = migrator.Migration{
    Name: "19700101_0001_create_posts_table",
    Up: func() migrator.Schema {
        var s migrator.Schema
        posts := migrator.Table{Name: "posts"}

        posts.UniqueID("id")
        posts.Varchar("title", 64)
        posts.Text("content", false)
        posts.Timestamps()

        s.CreateTable(posts)

        return s
    },
    Down: func() migrator.Schema {
        var s migrator.Schema

        s.DropTableIfExists("posts")

        return s
    },
}

• Using transactional migrations. In case you have multiple commands within one migration and you want to be sure it is migrated properly, you might enable transactional execution per migration:

var migration = migrator.Migration{
    Name: "19700101_0003_rename_foreign_key",
    Up: func() migrator.Schema {
        var s migrator.Schema

        keyName := migrator.BuildForeignNameOnTable("comments", "post_id")
        newKeyName := migrator.BuildForeignNameOnTable("comments", "article_id")

        s.AlterTable("comments", migrator.TableCommands{
            migrator.DropForeignCommand(keyName),
            migrator.DropIndexCommand(keyName),
            migrator.RenameColumnCommand{"post_id", "article_id"},
            migrator.AddIndexCommand{newKeyName, []string{"article_id"}},
            migrator.AddForeignCommand{migrator.Foreign{
                Key:       newKeyName,
                Column:    "article_id",
                Reference: "id",
                On:        "posts",
            }},
        })

        return s
    },
    Down: func() migrator.Schema {
        var s migrator.Schema

        keyName := migrator.BuildForeignNameOnTable("comments", "article_id")
        newKeyName := migrator.BuildForeignNameOnTable("comments", "post_id")

        s.AlterTable("comments", migrator.TableCommands{
            migrator.DropForeignCommand(keyName),
            migrator.DropIndexCommand(keyName),
            migrator.RenameColumnCommand{"article_id", "post_id"},
            migrator.AddIndexCommand{newKeyName, []string{"post_id"}},
            migrator.AddForeignCommand{migrator.Foreign{
                Key:       newKeyName,
                Column:    "post_id",
                Reference: "id",
                On:        "posts",
            }},
        })

        return s
    },
    Transaction: true,
}

• Custom queries on schema and tables.

You may add any column definition to the database on your own, just be sure you implement migrator.columnType interface:

type customType string

func (ct customType) buildRow() string {
    return string(ct)
}

posts := migrator.Table{Name: "posts"}
posts.UniqueID("id")
posts.Column("data", customType("json not null"))
posts.Timestamps()

The same logic is for adding custom commands to the schema to be migrated or reverted, just be sure you implement command interface:

type customCommand string

func (cc customCommand) toSQL() string {
    return string(cc)
}

var s migrator.Schema

c := customCommand("DROP PROCEDURE abc")
s.CustomCommand(c)

Pending features

At the time of this writing, the latest stable version is v1.2.0. As the next steps, it is planned to add more commands for interacting with the schema.

If necessary, in the next major versions drivers can be added for Postgres, SQLite, Microsoft SQL Server etc.

Result

At the moment, this package is a part of the go open source community and is versatile enough to be used in various projects. I really hope that it will also be useful to you when working with the database schema.

It is not popular yet, but I am really proud of it and believe it may grow fast with your support and contributions!