如何安裝及使用 goose 管理資料庫版本
安裝goose
linux
記得要有sudo 權限的帳號
curl -fsSL https://raw.githubusercontent.com/pressly/goose/master/install.sh |sh指令
- 看 help
goose --help- 新增一次版號 [sql]
goose create init sql 就會產生一組時間和檔案名稱的sql檔案
2026/02/11 08:00:10 Created new file: 20260211080010_init.sql內容會如下
-- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd主要 goose Up 當 up 時 執行 goose StatementBegin 與 goose StatementEnd 之前的 sql
主要 goose Down 當 down 時 執行 goose StatementBegin 與 goose StatementEnd 之前的 sql
- 新增一次版號 [go]
goose create init go就會產生一組時間和檔案名稱的go檔案
2026/02/11 08:00:10 Created new file: 20260211080010_init.go內容如下
package migrations
import (
"context"
"database/sql"
"github.com/pressly/goose/v3"
)
func init() {
goose.AddMigrationContext(upInit, downInit)
}
func upInit(ctx context.Context, tx *sql.Tx) error {
// This code is executed when the migration is applied.
return nil
}
func downInit(ctx context.Context, tx *sql.Tx) error {
// This code is executed when the migration is rolled back.
return nil
}使用範例
建立一組 up
- 示範 ddl
CREATE TABLE public.flower_prod_day_trans (
trans_date date NOT NULL,
trans_date_roc text NULL,
query_time timestamp NULL,
query_time_roc text NULL,
market text NOT NULL,
product text NOT NULL,
max_price numeric NULL,
high_price numeric NULL,
mid_price numeric NULL,
low_price numeric NULL,
avg_price numeric NULL,
price_change_pct numeric NULL,
volume int4 NULL,
volume_change_pct numeric NULL,
residual_volume int4 NULL,
result_title text NULL,
source_url text NULL,
raw jsonb NULL,
inserted_at timestamptz DEFAULT now() NOT NULL,
updated_at timestamptz DEFAULT now() NOT NULL,
CONSTRAINT flower_prod_day_trans_pkey PRIMARY KEY (trans_date, market, product)
);- 寫入 up 範圍內
-- +goose Up
-- +goose StatementBegin
CREATE TABLE public.flower_prod_day_trans (
trans_date date NOT NULL,
trans_date_roc text NULL,
query_time timestamp NULL,
query_time_roc text NULL,
market text NOT NULL,
product text NOT NULL,
max_price numeric NULL,
high_price numeric NULL,
mid_price numeric NULL,
low_price numeric NULL,
avg_price numeric NULL,
price_change_pct numeric NULL,
volume int4 NULL,
volume_change_pct numeric NULL,
residual_volume int4 NULL,
result_title text NULL,
source_url text NULL,
raw jsonb NULL,
inserted_at timestamptz DEFAULT now() NOT NULL,
updated_at timestamptz DEFAULT now() NOT NULL,
CONSTRAINT flower_prod_day_trans_pkey PRIMARY KEY (trans_date, market, product)
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd建立 down ( 因為如果是 要回復上步的話 就是把表刪除)
DROP TABLE IF EXISTS flower_prod_day_trans;寫入後就會是
-- +goose Up
-- +goose StatementBegin
CREATE TABLE public.flower_prod_day_trans (
trans_date date NOT NULL,
trans_date_roc text NULL,
query_time timestamp NULL,
query_time_roc text NULL,
market text NOT NULL,
product text NOT NULL,
max_price numeric NULL,
high_price numeric NULL,
mid_price numeric NULL,
low_price numeric NULL,
avg_price numeric NULL,
price_change_pct numeric NULL,
volume int4 NULL,
volume_change_pct numeric NULL,
residual_volume int4 NULL,
result_title text NULL,
source_url text NULL,
raw jsonb NULL,
inserted_at timestamptz DEFAULT now() NOT NULL,
updated_at timestamptz DEFAULT now() NOT NULL,
CONSTRAINT flower_prod_day_trans_pkey PRIMARY KEY (trans_date, market, product)
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS flower_prod_day_trans;
-- +goose StatementEnd執行
up
我是使用 postgrsql 且有使用 pgbouncer post 會是 6432
連線字串會是
postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres- 實際執行是
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" up結果
2026/02/11 08:10:52 OK 20260211080725_init.sql (14.5ms)
2026/02/11 08:10:52 goose: successfully migrated database to version: 20260211080725可以看到資料庫多一張表 goose_db_version
down
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" down2026/02/11 08:22:25 OK 20260211080725_init.sql (10.18ms)就會看到表被刪除了
可以再 up嗎?
可以
可以觀察到 再次執行 up 後 goose_db_version 這張表 會看到
| id | version_id | is_applied | tstamp |
|---|---|---|---|
| 1 | 0 | true | 2026-02-11 07:49:17.643 |
| 3 | 20260211080725 | true | 2026-02-11 08:23:28.866 |
有version_id號的資料
再次 down 的話 這條資料就會不見
模擬 如果是 需求 會一直迭代的話
新增 account 表
goose create account sqlCREATE TABLE public.account (
account_id varchar(255) NOT NULL,
created_date timestamp NOT NULL,
last_modified_date timestamp NULL,
created_by varchar(50) NOT NULL,
last_modified_by varchar(50) NULL,
first_name varchar(50) NULL,
last_name varchar(50) NULL,
"password" varchar(100) NULL,
CONSTRAINT accountpk PRIMARY KEY (account_id),
);- 編輯檔案
vi 202602111630_account.sql-- +goose Up
-- +goose StatementBegin
CREATE TABLE public.account (
account_id varchar(255) NOT NULL,
created_date timestamp NOT NULL,
last_modified_date timestamp NULL,
created_by varchar(50) NOT NULL,
last_modified_by varchar(50) NULL,
first_name varchar(50) NULL,
last_name varchar(50) NULL,
"password" varchar(100) NULL,
CONSTRAINT accountpk PRIMARY KEY (account_id)
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS account;
-- +goose StatementEnd新增 country 表
goose create account scountrylCREATE TABLE public.country (
country varchar(255) NOT NULL,
currency_symbol varchar(255) DEFAULT '$'::character varying NOT NULL,
currency_code varchar(255) DEFAULT 'USD'::character varying NOT NULL,
CONSTRAINT countrypk PRIMARY KEY (country)
);- 編輯檔案
vi 202602111630_country.sql-- +goose Up
-- +goose StatementBegin
CREATE TABLE public.country (
country varchar(255) NOT NULL,
currency_symbol varchar(255) DEFAULT '$'::character varying NOT NULL,
currency_code varchar(255) DEFAULT 'USD'::character varying NOT NULL,
CONSTRAINT countrypk PRIMARY KEY (country)
);
-- +goose StatementEnd
-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS country;
-- +goose StatementEnd
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" up可以執行 看狀況
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" status 2026/02/11 08:40:19 Applied At Migration 2026/02/11 08:40:19 ======================================= 2026/02/11 08:40:19 Wed Feb 11 08:40:14 2026 — 20260211080725_init.sql 2026/02/11 08:40:19 Wed Feb 11 08:40:14 2026 — 20260211083030_account.sql 2026/02/11 08:40:19 Wed Feb 11 08:40:14 2026 — 20260211083301_country.sql
|id|version_id|is_applied|tstamp|
|--|----------|----------|------|
|1|0|true|2026-02-11 08:25:30.284|
|3|20260211080725|true|2026-02-11 08:33:38.961|
|4|20260211083030|true|2026-02-11 08:33:57.637|
|5|20260211083301|true|2026-02-11 08:34:28.999|
就會一次執行這新的兩張表
再次down
: goose postgres “postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres” down 2026/02/11 08:36:51 OK 20260211083301_country.sql (10.63ms) : goose postgres “postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres” down 2026/02/11 08:36:52 OK 20260211083030_account.sql (6.38ms) : goose postgres “postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres” down 2026/02/11 08:36:53 OK 20260211080725_init.sql (7.5ms)
他會逐次 慢慢往 更舊的版本退
單退掉某版 down-to
退到某個版本：
goose postgres “postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres” down-to 20260211080725
2026/02/11 08:42:25 Applied At Migration 2026/02/11 08:42:25 ======================================= 2026/02/11 08:42:25 Wed Feb 11 08:40:14 2026 — 20260211080725_init.sql 2026/02/11 08:42:25 Wed Feb 11 08:40:14 2026 — 20260211083030_account.sql 2026/02/11 08:42:25 Pending — 20260211083301_country.sql
清空所有 reset
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" reset所有 透過 goose 管理的 表 就會乾乾淨淨
重新 洗表 最近一次的表 redo
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" redo到某一個版本 up to
2026/02/11 08:49:12 Applied At Migration
2026/02/11 08:49:12 =======================================
2026/02/11 08:49:12 Wed Feb 11 08:46:58 2026 -- 20260211080725_init.sql
2026/02/11 08:49:12 Pending -- 20260211083030_account.sql
2026/02/11 08:49:12 Pending -- 20260211083301_country.sql
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" up-to 20260211083301執行完後
再執行
goose postgres "postgres://{{account}}:{{passwrod}}@127.0.0.1:6432/postgres" status2026/02/11 08:49:32 Applied At Migration
2026/02/11 08:49:32 =======================================
2026/02/11 08:49:32 Wed Feb 11 08:46:58 2026 -- 20260211080725_init.sql
2026/02/11 08:49:32 Wed Feb 11 08:49:29 2026 -- 20260211083030_account.sql
2026/02/11 08:49:32 Wed Feb 11 08:49:29 2026 -- 20260211083301_country.sql他就直接跳版本了
下次就會了