[pkg] golang-migrate 筆記

• Database Migrations in Go @ calhoun
• golang-migrate @ github

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

• PostgreSQL tutorial for beginners
• migrate/database/postgres

使用 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

• forcing your database version
• Dirty database version 1. Fix and force version