Golang migrate for database change management

Recently I’ve been using golang-migrate for database change management, and according to the official tutorial, I need to download a local binary first and generate the change file on the command line.

1
2
3

$ migrate create -ext sql -dir db/migrations -seq create_article_table
...项目路径/db/migrations/000011_create_article_table.up.sql
...项目路径/db/migrations/000011_create_article_table.down.sql

This will generate a bunch of .sql files under . /db/migrations, where 000011 is the number, incremented from 000001, create_article_table is the incoming file name, .up.sql represents the SQL executed when changing forward, and .down.sql represents the SQL executed when rolling back. SQL.

We can change the database locally like this after editing.

1
2
3

$ export POSTGRESQL_URL='postgres://postgres:密码@localhost:5432/dbname?sslmode=disable'
$ migrate -database ${POSTGRESQL_URL} -path db/migrations up
no change

But in practice, after we publish the project, if we have to go online to change it every time, it will be more troublesome, can we let the code run itself? Of course you can.

Automatic code execution of changes

migrate can be invoked both from the command line and as a library. migrate supports a variety of sources such as iofs , github , gitlab , s3 and so on.

The one I want to use is the official embed, which migrate wraps as iofs.

package main

import (
    "context"
    "embed"
    "time"

    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    "github.com/sirupsen/logrus"
)

var (
    //go:embed db/migrations/*.sql
    fs embed.FS
)

func initDB(config *Config) {
    var err error
    uri := fmt.Sprintf(
        "postgres://%s:%s@%s:%s/%s?sslmode=disable",
        config.DBUser, config.DBPassword, config.DBHost, config.DBPort, config.DBName,
    )
    ctx := context.Background()
    db, err = pgxpool.Connect(ctx, uri)
    if err != nil {
        logrus.Fatalf("connect to db failed: %v", err)
    }

    if err = db.Ping(ctx); err != nil {
        logrus.Fatalf("could not connect to database: %v", err)
    }

    d, err := iofs.New(fs, "db/migrations")
    if err != nil {
        logrus.Fatalf("could not open migrations: %v", err)
    }
    m, err := migrate.NewWithSourceInstance("iofs", d, uri)
    if err != nil {
        logrus.Fatalf("could not init migrate: %v", err)
    }
    err = m.Up()
    if err != nil {
        logrus.Errorf("migrate up error: %v", err)
    }
}

func main() {
    config := GetConfig()
    initDB(config)
}

That is, immediately after the application is started, the database is initialized and database changes are started immediately after connecting to the database. Look at the top var fs embed.FS and the comment on the top line. This is the official Go way of packaging files into binary, essentially reading the contents of the file at compile time, compiling it, and assigning it to the fs variable.

This allows you to make database changes automatically after each startup before you start executing the code. However, it’s worth noting that it’s best to deploy only one copy of the program that runs the database changes, otherwise there will be competition problems.

Summary

This article has documented the way I use migrate, which is more convenient than executing all operations at the command line and eliminates the need to synchronize sql files to the server, but the downside is that the binary file will become larger. There are pros and cons, but I prefer this approach.

Table of Contents

Automatic code execution of changes

Summary