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