非你网

习惯了 Laravel 的迁移和 seeder，来使用 go 语言。如果没有这些，是不是很不爽。好的就需要模仿和借鉴，当然，有现成省心的更好。网上已经有一些这样的轮子了，这里找一个比较接近 Laravel 的迁移的实现。

github: https://github.com/praveen001/go-db-migration

origin：https://techinscribed.com/create-db-migrations-tool-in-go-from-scratch/

learnku: https://learnku.com/go/t/51228

操作一波

先上个图，证明这个是可以玩起来的，并且和 laravel 中的操作很相似。

第一步： 先建立一个 migrations/migrator.go 文件，这个文件用来执行初始化，创建迁移，执行迁移的直接逻辑。

package migrations

import (
    "bytes"
    "context"
    "database/sql"
    "errors"
    "fmt"
    "html/template"
    "os"
    "time"
)

// Migration ..
type Migration struct {
    Version string
    Up      func(*sql.Tx) error
    Down    func(*sql.Tx) error

    done bool
}

// Migrator ..
type Migrator struct {
    db         *sql.DB
    Versions   []string
    Migrations map[string]*Migration
}

var migrator = &Migrator{
    Versions:   []string{},
    Migrations: map[string]*Migration{},
}

// AddMigration ..
func (m *Migrator) AddMigration(mg *Migration) {
    // Add the migration to the hash with version as key
    m.Migrations[mg.Version] = mg

    // Insert version into versions array using insertion sort
    index := 0
    for index < len(m.Versions) {
        if m.Versions[index] > mg.Version {
            break
        }
        index++
    }

    m.Versions = append(m.Versions, mg.Version)
    copy(m.Versions[index+1:], m.Versions[index:])
    m.Versions[index] = mg.Version
}

// Init ..
func Init(db *sql.DB) (*Migrator, error) {
    migrator.db = db

    // Create `schema_migrations` table to remember which migrations were executed.
    if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
        version varchar(255)
    );`); err != nil {
        fmt.Println("Unable to create `schema_migrations` table", err)
        return migrator, err
    }

    // Find out all the executed migrations
    rows, err := db.Query("SELECT version FROM `schema_migrations`;")
    if err != nil {
        return migrator, err
    }

    defer rows.Close()

    // Mark the migrations as Done if it is already executed
    for rows.Next() {
        var version string
        err := rows.Scan(&version)
        if err != nil {
            return migrator, err
        }

        if migrator.Migrations[version] != nil {
            migrator.Migrations[version].done = true
        }
    }

    return migrator, err
}

// Up ..
func (m *Migrator) Up(step int) error {
    tx, err := m.db.BeginTx(context.TODO(), &sql.TxOptions{})
    if err != nil {
        return err
    }

    count := 0
    for _, v := range m.Versions {
        if step > 0 && count == step {
            break
        }

        mg := m.Migrations[v]

        if mg.done {
            continue
        }

        fmt.Println("Running migration", mg.Version)
        if err := mg.Up(tx); err != nil {
            tx.Rollback()
            return err
        }

        if _, err := tx.Exec("INSERT INTO `schema_migrations` VALUES(?)", mg.Version); err != nil {
            tx.Rollback()
            return err
        }
        fmt.Println("Finished running migration", mg.Version)

        count++
    }

    tx.Commit()

    return nil
}

// Down ..
func (m *Migrator) Down(step int) error {
    tx, err := m.db.BeginTx(context.TODO(), &sql.TxOptions{})
    if err != nil {
        return err
    }

    count := 0
    for _, v := range reverse(m.Versions) {
        if step > 0 && count == step {
            break
        }

        mg := m.Migrations[v]

        if !mg.done {
            continue
        }

        fmt.Println("Reverting Migration", mg.Version)
        if err := mg.Down(tx); err != nil {
            tx.Rollback()
            return err
        }

        if _, err := tx.Exec("DELETE FROM `schema_migrations` WHERE version = ?", mg.Version); err != nil {
            tx.Rollback()
            return err
        }
        fmt.Println("Finished reverting migration", mg.Version)

        count++
    }

    tx.Commit()

    return nil
}

// Status .
func (m *Migrator) MigrationStatus() error {
    for _, v := range m.Versions {
        mg := m.Migrations[v]

        if mg.done {
            fmt.Println(fmt.Sprintf("Migration %s... completed", v))
        } else {
            fmt.Println(fmt.Sprintf("Migration %s... pending", v))
        }
    }

    return nil
}

// Create ..
func Create(name string) error {
    version := time.Now().Format("20060102150405")

    in := struct {
        Version string
        Name    string
    }{
        Version: version,
        Name:    name,
    }

    var out bytes.Buffer

    t := template.Must(template.ParseFiles("./migrations/template.txt"))
    err := t.Execute(&out, in)
    if err != nil {
        return errors.New("Unable to execute template:" + err.Error())
    }

    f, err := os.Create(fmt.Sprintf("./migrations/%s_%s.go", version, name))
    if err != nil {
        return errors.New("Unable to create migration file:" + err.Error())
    }
    defer f.Close()

    if _, err := f.WriteString(out.String()); err != nil {
        return errors.New("Unable to write to migration file:" + err.Error())
    }

    fmt.Println("Generated new migration files...", f.Name())
    return nil
}

func reverse(arr []string) []string {
    for i := 0; i < len(arr)/2; i++ {
        j := len(arr) - i - 1
        arr[i], arr[j] = arr[j], arr[i]
    }
    return arr
}

第二步：创建模板文件，这个是创建迁移的基础。创建的迁移，是以模板文件为基础的。

模板文件， migrations/template.txt

package migrations

import "database/sql"

func init() {
    migrator.AddMigration(&Migration{
        Version: "{{.Version}}",
        Up:      mig_{{.Version}}_{{.Name}}_up,
        Down:    mig_{{.Version}}_{{.Name}}_down,
    })
}

func mig_{{.Version}}_{{.Name}}_up(tx *sql.Tx) error {
    _, err := tx.Exec("Create Table If Not Exists xxx")
    if err != nil {
        return err
    }
    return nil
}

func mig_{{.Version}}_{{.Name}}_down(tx *sql.Tx) error {
    _, err := tx.Exec("DROP TABLE IF EXISTS xxx")
    if err != nil {
        return err
    }
    return nil
}

migrations 中就这两个文件。

第三步：建立 cmd 文件。 一个 cmd/cmd.go，还有一个 cmd/migrate.go 。

# cmd/cmd.go
package cmd

import (
    "log"

    "github.com/spf13/cobra"
)

var rootCmd = &cobra.Command{
    Use:   "app",
    Short: "Application Description",
}

// Execute ..
func Execute() {
    if err := rootCmd.Execute(); err != nil {
        log.Fatalln(err.Error())
    }
}

还有

# cmd/migrate.go
package cmd

import (
    "fmt"
    "nm/app"
    "nm/migrations"
    "github.com/spf13/cobra"
)

var migrateCmd = &cobra.Command{
    Use:   "migrate",
    Short: "database migrations tool",
    Run: func(cmd *cobra.Command, args []string) {

    },
}

var migrateCreateCmd = &cobra.Command{
    Use:   "create",
    Short: "create a new empty migrations file",
    Run: func(cmd *cobra.Command, args []string) {
        name, err := cmd.Flags().GetString("name")
        if err != nil {
            fmt.Println("Unable to read flag `name`", err.Error())
            return
        }

        if err := migrations.Create(name); err != nil {
            fmt.Println("Unable to create migration", err.Error())
            return
        }
    },
}

var migrateUpCmd = &cobra.Command{
    Use:   "up",
    Short: "run up migrations",
    Run: func(cmd *cobra.Command, args []string) {

        step, err := cmd.Flags().GetInt("step")
        if err != nil {
            fmt.Println("Unable to read flag `step`")
            return
        }

        db := app.NewDB()

        migrator, err := migrations.Init(db)
        if err != nil {
            fmt.Println("Unable to fetch migrator")
            return
        }

        err = migrator.Up(step)
        if err != nil {
            fmt.Println("Unable to run `up` migrations")
            return
        }

    },
}

var migrateDownCmd = &cobra.Command{
    Use:   "down",
    Short: "run down migrations",
    Run: func(cmd *cobra.Command, args []string) {

        step, err := cmd.Flags().GetInt("step")
        if err != nil {
            fmt.Println("Unable to read flag `step`")
            return
        }

        db := app.NewDB()

        migrator, err := migrations.Init(db)
        if err != nil {
            fmt.Println("Unable to fetch migrator")
            return
        }

        err = migrator.Down(step)
        if err != nil {
            fmt.Println("Unable to run `down` migrations")
            return
        }
    },
}

var migrateStatusCmd = &cobra.Command{
    Use:   "status",
    Short: "display status of each migrations",
    Run: func(cmd *cobra.Command, args []string) {
        db := app.NewDB()

        migrator, err := migrations.Init(db)
        if err != nil {
            fmt.Println("Unable to fetch migrator")
            return
        }

        if err := migrator.MigrationStatus(); err != nil {
            fmt.Println("Unable to fetch migration status")
            return
        }

        return
    },
}

func init() {
    // Add "--name" flag to "create" command
    migrateCreateCmd.Flags().StringP("name", "n", "", "Name for the migration")

    // Add "--step" flag to both "up" and "down" command
    migrateUpCmd.Flags().IntP("step", "s", 0, "Number of migrations to execute")
    migrateDownCmd.Flags().IntP("step", "s", 0, "Number of migrations to execute")

    // Add "create", "up" and "down" commands to the "migrate" command
    migrateCmd.AddCommand(migrateUpCmd, migrateDownCmd, migrateCreateCmd, migrateStatusCmd)

    // Add "migrate" command to the root command
    rootCmd.AddCommand(migrateCmd)
}

第四步：生成 sql.DB 的引用。

# app/db.go

package app

import (
    "database/sql"
    "fmt"
    "github.com/go-sql-driver/mysql"
)

// NewDB .
func NewDB() *sql.DB {
    fmt.Println("Connecting to MySQL database...")

    config := mysql.Config{
        User:                 "homestead",
        Passwd:               "secret",
        Addr:                 "127.0.0.1:33060",
        Net:                  "tcp",
        DBName:               "goblog",
        AllowNativePasswords: true,
    }
    fmt.Println(config.FormatDSN())

    // 准备数据库连接池
    db, err := sql.Open("mysql", config.FormatDSN())

    if err != nil {
        fmt.Println("Unable to connect to database", err.Error())
        return nil
    }

    if err := db.Ping(); err != nil {
        fmt.Println("Unable to connect to database", err.Error())
        return nil
    }

    fmt.Println("Database connected!")

    return db
}

第五步：main.go

package main

import (
    "nm/cmd"
)

func main() {
    cmd.Execute()
}

到此，所有文件已经齐全。

执行下边命令，可以让你开心的迁移。

# 创建一个 User 的迁移， 文件名如 20201127135416_User.go
go run main.go migrate create -n User 

# 执行迁移 
go run main.go migrate up

# 回滚迁移
go run main.go migrate down

# 查看状态
go run main.go migrate status

虽然和 laravel 很相似，可和 laravel 相比，相差的太多了。不过，有过总比没有的好。如果乐意，还可以添加 seeder 进来。让你美滋滋。

这里生了一个 user 的迁移文件：

package migrations

import "database/sql"

func init() {
    migrator.AddMigration(&Migration{
        Version: "20201127160251",
        Up:      mig_20201127160251_User_up,
        Down:    mig_20201127160251_User_down,
    })
}

func mig_20201127160251_User_up(tx *sql.Tx) error {
    sql := `Create Table If Not Exists users (
              id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
              name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
              email varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
              email_verified_at timestamp NULL DEFAULT NULL,
              password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
              remember_token varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
              created_at timestamp NULL DEFAULT NULL,
              updated_at timestamp NULL DEFAULT NULL,
              PRIMARY KEY (id),
              UNIQUE KEY users_email_unique (email)
            ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`

    _, err := tx.Exec(sql)
    if err != nil {
        return err
    }
    return nil
}

func mig_20201127160251_User_down(tx *sql.Tx) error {
    _, err := tx.Exec("DROP TABLE IF EXISTS users")
    if err != nil {
        return err
    }
    return nil
}