Skip to main content

[pkg] golang-migrate 筆記

TL;DR#

# 建立 migration 檔
$ migrate create -ext sql -dir db/migrations create_users_table
# 定義變數 POSTGRESQL_URL
export POSTGRESQL_URL="postgres://postgres:postgres@localhost:5432/blog_service?sslmode=disable"
# 執行 migration 檔
$ migrate -source file://internal/dao/migrations -database "postgres://postgres:postgres@localhost:5432/blog_service?sslmode=disable" up
$ migrate -database ${POSTGRESQL_URL} -path db/migrations up
# force
$ migrate -source file://internal/dao/migrations -database "postgres://postgres:postgres@localhost:5432/blog_service?sslmode=disable" force 20201211154328

migrate CLI @ golang migrate

PostgreSQL tutorial for beginners#

使用 CLI 執行 migration#

  1. 建立 migration 檔案

    $ migrate create -ext sql -dir database/migrations create_users_table
  2. 在 migration 檔中填入要執行的 SQL

    up:

    -- 20201125101852_create_users_table.up.sql
    CREATE TABLE IF NOT EXISTS users(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (300) UNIQUE NOT NULL
    );

    down:

    -- 20201125101852_create_users_table.down.sql
    DROP TABLE IF EXISTS users;
  3. 在 Terminal 中設定環境變數

    $ export POSTGRESQL_URL='postgres://postgres:password@localhost:5432/example? sslmode=disable'
  4. 執行 migration 檔案

    $ migrate -database ${POSTGRESQL_URL} -path db/migrations up
    $ migrate -database ${POSTGRESQL_URL} -path db/migrations down

在 Golang 中執行 migration#

Down

// cmd/migrate/down/down.go
package main
import (
log "github.com/sirupsen/logrus"
"github.com/golang-migrate/migrate/v4"
_ "github.com/golang-migrate/migrate/v4/database/postgres"
_ "github.com/golang-migrate/migrate/v4/source/file"
)
func main() {
m, err := migrate.New(
"file://internal/dao/migrations",
"postgres://postgres:postgres@localhost:5432/blog_service?sslmode=disable",
)
if err != nil {
log.Warn("internal/dao/migrations - migrate.New")
log.Fatal(err)
}
if err := m.Down(); err != nil {
log.Warn("internal/dao/migrations - m.Down()")
log.Fatal(err)
}
}

up

// cmd/migrate/up/up.go
package main
import (
log "github.com/sirupsen/logrus"
"github.com/golang-migrate/migrate/v4"
_ "github.com/golang-migrate/migrate/v4/database/postgres"
_ "github.com/golang-migrate/migrate/v4/source/file"
)
func main() {
m, err := migrate.New(
"file://internal/dao/migrations",
"postgres://postgres:postgres@localhost:5432/blog_service?sslmode=disable",
)
if err != nil {
log.Warn("internal/dao/migrations - migrate.New")
log.Fatal(err)
}
if err := m.Up(); err != nil {
log.Warn("internal/dao/migrations - m.Up()")
log.Fatal(err)
}
}

使用 Makefile#

POSTGRESQL_URL="postgres://postgres:postgres@localhost:5432/blog_service?sslmode=disable"
db-migrate-up:
migrate -source file://internal/dao/migrations -database ${POSTGRESQL_URL} up
db-migrate-down:
migrate -source file://internal/dao/migrations -database ${POSTGRESQL_URL} down

Migration Files#

使用 transaction#

Migration Up:

-- up
BEGIN;
CREATE TYPE enum_mood AS ENUM (
'happy',
'sad',
'neutral'
);
ALTER TABLE users ADD COLUMN mood enum_mood;
COMMIT;

Migration Down:

BEGIN;
ALTER TABLE users DROP COLUMN mood;
DROP TYPE enum_mood;
COMMIT;

Install CLI#

migrate CLI

$ brew install golang-migrate

常見問題#

Forcing your database version#

Last updated on