Golang MySQL数据库迁移与架构管理插件库migrator的使用

简介

MySQL数据库迁移工具migrator是一个用于运行数据库迁移和管理数据库架构更新的Go库，兼容最新的MySQL v8。

安装

要安装migrator包，您需要先安装Go并设置Go工作区。

1. 首先需要安装Go（需要1.13+版本），然后可以使用以下Go命令安装migrator：

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

1. 在代码中导入：

import "github.com/larapulse/migrator"

快速开始

初始化带有迁移条目的migrator：

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

第一次迁移运行后，将创建migrations表：

+----+-------------------------------------+-------+----------------------------+
| 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 |
+----+-------------------------------------+-------+----------------------------+

如果想使用其他名称作为迁移表，可以在运行迁移前更改Migrator：

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

事务性迁移

如果在一个迁移中有多个命令，并且您希望确保它被正确迁移，可以启用每个迁移的事务执行：

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

回滚和恢复

如果需要回滚部署和数据库，可以回滚最后迁移的批次：

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

要恢复所有迁移的项目，您需要在migrator上调用Revert()：

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

自定义查询

您可以向数据库添加任何列定义，只需确保实现columnType接口：

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

同样的逻辑适用于向Schema添加自定义命令以进行迁移或恢复，只需确保实现command接口：

type customCommand string

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

var s migrator.Schema

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