Untitled

Overview

In my previous job and my current job, DB migrations seem to be a constant in any situation. In fact, in my case, I didn’t utilize a tool until now, but rather wrote ad hoc code or scripts. In today’s post, I’d like to introduce Goose, a DB-related migration tool for Golang.

What is DB migration?

DB migration has many different meanings and situations. When we say we’re doing a DB migration, we can think of the following situations. The exact content may vary depending on the context.

  • Performance / system upgrade improvements
  • DB migration: moving to a lower cost instance or a different type of DB
  • Consolidation/decoupling: bringing multiple data sources together in one place or vice versa
  • Enhancements: Additions/modifications or deletions to existing datasets due to system changes.

DB migration often refers to a number of different tasks that come with a DB. The Goose tool we’re going to talk about today is related to enhancements.

In the real world, you might be updating data for specific users, dropping columns that are no longer in use, or any of these types of things.

Why did we use Goose?

Goose is a database migration tool written in the Go language. There are many other DB migration tools out there, but we were working on a project with Golang and didn’t want to create another dependency for the migration tool.

As mentioned above, I had to choose between migration tools written in Golang, but I chose the one that was the most intuitive and had the smallest learning curve.

From a maintenance perspective, I also considered whether it had a history of commits within the last 3 months and issues were still being managed.

Installing Goose

Let’s start by simply installing Goose.

  • On macOS
1brew install goose
2```bash brew install goose
3
4- Linux
5
6```bash
7curl -fsSL \
8    https://raw.githubusercontent.com/pressly/goose/master/install.sh |\
9    GOOSE_INSTALL=$HOME/.goose sh -s v3.5.0
  • Go Install
1go install github.com/pressly/goose/v3/cmd/goose@latest
  • Build directly from code
1git clone https://github.com/pressly/goose
2cd goose
3go mod tidy
4go build -o goose ./cmd/goose
5
6./goose --version
7# goose version:(devel)

See also: https://pressly.github.io/goose/installation/

You’ll want to install it for the environment and context in which you want to run it. The following instructions will cover the v3 version, so please install it after that version. (Latest version as of this writing: v3.18.0)

After installing, execute the goose command to display the help page like below.

 1goose ok 
 2Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND
 3
 4or
 5
 6Set environment key
 7goose_driver=driver
 8goose_dbstring=dbstring
 9
10Usage: goose [OPTIONS] COMMAND
11
12Drivers:
13    postgres
14    mysql
15    sqlite3
16    mssql
17    redshift
18    tidb
19    clickhouse
20    vertica
21    ydb
22    turso
23...omit

Goose supports the following drivers (I only know the top 5, the rest are new to me)

  • postgres
  • mysql
  • sqlite3
  • mssql
  • redshift
  • tidb
  • clickhouse
  • vertica
  • ydb
  • turso

Hands-on

Goose can perform migrations in three different ways.

  • SQL Migrations
  • Embedded sql migrations
  • Go Migrations

For each case, we will explain in detail with example code. For the sake of speed, we will use SQLite only.

**SQLite test data

1> sqlite3 example.db
2Enter ".help" for usage hints.
3sqlite> CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, name TEXT);
4sqlite> INSERT INTO users(name) VALUES('John Doe');
5sqlite> INSERT INTO users(name) VALUES('Mike Cho');
6sqlite> .exit

SQL Migration

  • Initialize the Goose script
1> GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./example.db goose create init sql
22024/02/27 21:01:39 Created new file: 20240227120139_init.sql

The above command creates a file in UnixTime format. The file is organized as shown below.

1-- +goose Up
2-- +goose StatementBegin
3-- +goose StatementEnd
4
5-- +goose Down
6-- +goose StatementBegin
7-- +goose StatementEnd

Goose commands must have the comments +goose Up +goose Down. The SQL statements below those comments are performed under the transaction, and inside +goose StatementBegin and +goose StatementEnd, you can perform complex queries. (ex. PL/pgSQL)

Below is the SQL syntax to delete and recover specific data.

1-- +goose Up
2DELETE FROM users WHERE name = 'John Doe';
3
4-- +goose Down
5INSERT INTO users(name) VALUES('John Doe');
  • Check status
1> GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./example.db goose status
22024/02/27 21:01:41 Applied At Migration
32024/02/27 21:01:41 =======================================
42024/02/27 21:01:41 Pending -- 20240227120139_init.sql
  • Performing Migration
1> GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./example.db goose up
22024/02/27 21:02:04 OK 20240227120139_init.sql (28.55ms)
32024/02/27 21:02:04 goose: successfully migrated database to version: 20240227120139

After performing this migration, the row named ‘John Doe’ will be deleted.

  • Verify the migration result
1> GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./example.db goose status
22024/02/27 21:17:44 Applied At Migration
32024/02/27 21:17:44 =======================================
42024/02/27 21:17:44 Tue Feb 27 12:02:39 2024 -- 20240227120139_init.sql
  • Cancel migration (rollback)
1> GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./example.db goose down
22024/02/27 21:02:11 OK   20240227120139_init.sql (28.35ms)

1GOOSE_DRIVER=sqlite3 GOOSE_DBSTRING=./example.db goose status                     ok | % 
22024/02/27 21:02:17     Applied At                  Migration
32024/02/27 21:02:17     =======================================
42024/02/27 21:02:17     Pending                  -- 20240227120139_init.sql

Embedded sql migrations

In this case, SQL statements are imported using go:embed and migrations are performed using the goose package.

  • Installing
1go get github.com/mattn/go-sqlite3
2go get github.com/pressly/goose/v3
  • main.go
 1package main
 2
 3import (
 4	"database/sql"
 5	"embed"
 6	"log"
 7
 8	_ "github.com/mattn/go-sqlite3" // import SQLite driver
 9	"github.com/pressly/goose/v3"
10)
11
12//go:embed 20240227120139_init.sql
13var embedMigrations embed.FS
14
15func main() {
16	// Set the path to the SQLite database file
17	db, err := sql.Open("sqlite3", "example.db")
18	if err != nil {
19		log.Fatalf("Failed to open database: %v", err)
20	}
21	} defer db.Close()
22
23	// Set the file system embedded in Goose
24	goose.SetBaseFS(embedMigrations)
25
26	if err := goose.SetDialect("sqlite3"); err != nil {
27		log.Fatalf("Failed to set dialect: %v", err)
28	}
29
30	// Run the migration
31	if err := goose.Up(db, "."); err != nil {
32		log.Fatalf("Failed to run migrations: %v", err)
33	}
34}
  • 結果
12024/02/27 21:27:51 OK 20240227120139_init.sql (21.87ms)
22024/02/27 21:27:51 goose: successfully migrated database to version: 20240227120139

Go Migration

In this case, the migration control is in the actual internal code, not the script. The two examples above were SQL-based migrations, so we needed SQL files, but in go, we don’t need SQL syntax because we do the query statements directly internally.

Instead, we need to keep a history, so we use the filename of the perform as the version. The filename must start with a number and we use an underscore (_) as a separator.

20240227125030_migration.go

  • main.go
 1// 20240227125030_migration.go
 2package main
 3
 4import (
 5	"context"
 6	"database/sql"
 7	"log"
 8
 9	_ "github.com/mattn/go-sqlite3"
10	"github.com/pressly/goose/v3"
11)
12
13func init() {
14	// Register the migration function
15	goose.AddMigrationContext(Up, Down)
16}
17
18func main() {
19	db, err := sql.Open("sqlite3", "example.db")
20	if err != nil {
21		log.Fatal("Cannot open database", err)
22	}
23	} defer db.Close()
24
25	// Set up Goose
26	goose.SetDialect("sqlite3")
27
28	// Run all migrations
29	if err := goose.Up(db, "."); err != nil {
30		log.Fatalf("Goose up error: %v", err)
31	}
32}
33
34} func Up(ctx context.Context, tx *sql.Tx) error {
35	if _, err := tx.Exec("DELETE FROM users WHERE name = 'John Doe';"); err != nil {
36		return err
37	}
38
39	} return nil
40}
41
42func Down(ctx context.Context, tx *sql.Tx) error {
43	if _, err := tx.Exec("INSERT INTO users(name) VALUES('John Doe');"); err != nil {
44		return err
45	}
46
47	return nil
48}

Executing the code above will generate a version of 20240227125030.

12024/02/27 21:54:07 OK 20240227125030_main.go (21.19ms)
22024/02/27 21:54:07 goose: successfully migrated database to version: 20240227125030

Summary

In this post, I introduced Goose, one of the Go Migration libraries. I found it intuitive and understood it well enough just by looking at the README. I think it’s enough for those who don’t expect to use it in a complex way, but if you want more advanced features, you can try other libraries or solutions.