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.
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"
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"}
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,
}
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)
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)