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)

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

			s.DropTableIfExists("posts")

			return s
		},
	},
	{
		Name: "19700101_0002_create_comments_table",
		Up: func() migrator.Schema {
			var s migrator.Schema
			comments := migrator.Table{Name: "comments"}

			comments.UniqueID("id")
			comments.UUID("post_id", "", false)
			comments.Varchar("name", 64)
			comments.Column("email", migrator.String{Default: "<nil>"})
			comments.Text("content", false)
			comments.Timestamps()

			comments.Foreign("post_id", "id", "posts", "RESTRICT", "RESTRICT")

			s.CreateTable(comments)

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

			s.DropTableIfExists("comments")

			return s
		},
	},
	{
		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
	},
}

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")

After the first migration run, migrations table will be created:

+----+-------------------------------------+-------+----------------------------+
| id | name                                | batch | applied_at                 |
+----+-------------------------------------+-------+----------------------------+
|  1 | 19700101_0001_create_posts_table    |     1 | 2020-06-27 00:00:00.000000 |
|  2 | 19700101_0002_create_comments_table |     1 | 2020-06-27 00:00:00.000000 |
|  3 | 19700101_0003_rename_foreign_key    |     1 | 2020-06-27 00:00:00.000000 |
+----+-------------------------------------+-------+----------------------------+

If you want to use another name for migration table, change it Migrator before running migrations:

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

Transactional migration

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_0001_create_posts_and_users_tables",
	Up: func() migrator.Schema {
		var s migrator.Schema
		posts := migrator.Table{Name: "posts"}
		posts.UniqueID("id")
		posts.Timestamps()

		users := migrator.Table{Name: "users"}
		users.UniqueID("id")
		users.Timestamps()

		s.CreateTable(posts)
		s.CreateTable(users)

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

		s.DropTableIfExists("users")
		s.DropTableIfExists("posts")

		return s
	},
	Transaction: true,
}

Rollback and revert

In case you need to revert your deploy and DB, you can revert last migrated batch:

m := migrator.Migrator{Pool: migrations}
reverted, err := m.Rollback(db)

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

if len(reverted) == 0 {
	log.Print("Nothing were rolled back.")
}

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

To revert all migrated items back, you have to call Revert() on your migrator:

m := migrator.Migrator{Pool: migrations}
reverted, err := m.Revert(db)

Customize queries

You may add any column definition to the database on your own, just be sure you implement 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)