Database Migration

後端開發中DB migration是很重要且經常性的操作，因此這個Tasks主要是學習如何使用golang migrate library

Install golang migrate CLI (MacOS)

brew install golang-migrate

Usage

• create : 產生新的migration files
• goto: migrate schema到指定的版本
• up : apply all or N migrations
• down: Revert all or N migrations

$ migrate -help
Usage: migrate OPTIONS COMMAND [arg...]
       migrate [ -version | -help ]

Options:
  -source          Location of the migrations (driver://url)
  -path            Shorthand for -source=file://path
  -database        Run migrations against this database (driver://url)
  -prefetch N      Number of migrations to load in advance before executing (default 10)
  -lock-timeout N  Allow N seconds to acquire database lock (default 15)
  -verbose         Print verbose logging
  -version         Print version
  -help            Print usage

Commands:
  create [-ext E] [-dir D] [-seq] [-digits N] [-format] NAME
               Create a set of timestamped up/down migrations titled NAME, in directory D with extension E.
               Use -seq option to generate sequential up/down migrations with N digits.
               Use -format option to specify a Go time format string.
  goto V       Migrate to version V
  up [N]       Apply all or N up migrations
  down [N]     Apply all or N down migrations
  drop         Drop everything inside database
  force V      Set version V but don't run migration (ignores dirty state)
  version      Print current migration version

Create migrate files folder

cd Workplace/go/src/simple-bank
mkdir -p Workplace/go/src/simple-bank/db/migration

Generate migration files

• -ext : extension file’s type
• -dir : path to store migration files
• -seq : generate a sequential version number of the migration file

migrate create -ext sql -dir db/migration -seq init-schema

/Users/avery_yang/Workplace/go/src/simple-bank/db/migration/000001_init-schema.up.sql
/Users/avery_yang/Workplace/go/src/simple-bank/db/migration/000001_init-schema.down.sql

UP / Down Migration

Copy diagram.sql into init_schema.up.sql

Simple_Back_diagram.sql

CREATE TABLE "accounts" (
  "id" bigserial PRIMARY KEY,
  "owner" varchar NOT NULL,
  "balance" bigint NOT NULL,
  "currency" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "entries" (
  "id" bigserial PRIMARY KEY,
  "account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "transfers" (
  "id" bigserial PRIMARY KEY,
  "from_account_id" bigint NOT NULL,
  "to_account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE INDEX ON "accounts" ("owner");

CREATE INDEX ON "entries" ("account_id");

CREATE INDEX ON "transfers" ("from_account_id");

CREATE INDEX ON "transfers" ("to_account_id");

CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");

COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive';

COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';

ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");

Set up init_schema.down.sql

• And here we drop entries and transfers table before dropping the accounts table because there’s a foreign key constraint in entries and transfers that references accounts records.

DROP TABLE IF EXISTS entries;
DROP TABLE IF EXISTS transfers;
DROP TABLE IF EXISTS accounts;

Use Makefile to create Database simple_bank

postgres:
					docker stop postgres
					docker rm postgres
					docker run --name postgres -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:14-alpinepostgres:
					docker run --name postgres -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:14-alpine

createdb:
					docker exec -it postgres createdb --username=root --owner=root simple_bank

dropdb:
					docker exec -it postgres dropdb simple_bank

.PHONY: postgres createdb dropdb

Migration up into simple_bank

• We don't enable PostgreSQL's SSL, so we need to set sslmode to "disable" in the URL.

migrate --path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up

Setup migrate command into Makefile

DB_URL=postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable

postgres:
					docker stop postgres
					docker rm postgres
					docker run --name postgres -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:14-alpine

createdb:
	docker exec -it postgres createdb --username=root --owner=root simple_bank

dropdb:
	docker exec -it postgres dropdb simple_bank

migrateup:
	migrate -path db/migration -database "$(DB_URL)" -verbose up

migratedown:
	migrate -path db/migration -database "$(DB_URL)" -verbose down

.PHONY: network postgres createdb dropdb migrateup migratedown