Database schema migration is the management of incremental, reversible changes and version control to relational database schemas. The migrations basically track granular changes to your database schema which are reflected as separate scripted files.
Golang Goose is a database migration tool. It allows you to manage your database schema by creating incremental SQL changes or Go functions.
Installation
If golang is installed locally, use this command to install goose:
1
| go install github.com/pressly/goose/v3/cmd/goose@latest
|
This will install the goose binary to your $GOPATH/bin directory.
For a lite version of the binary without DB connection dependent commands, clone the code then use the exclusive build tags:
1
2
3
| git clone https://github.com/pressly/goose.git
cd goose
go build -tags='no_postgres no_mysql no_sqlite3' -i -o goose ./cmd/goose
|
Or go the releases page here https://github.com/pressly/goose/releases and download the latest binary for your OS then move to executable $PATH.
For macOS users goose is available as a Homebrew Formulae:
Once installed, verify version
1
2
3
| $ goose -version
goose version: v3.17.0
|
Using Goose
Goose provides functionality to Create migrations, apply them, undo migrations and check the status of the migration. In this guide we are going to focus on SQL migrations
Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND
or
Set environment key
1
2
3
| GOOSE_DRIVER=DRIVER
GOOSE_DBSTRING=DBSTRING
GOOSE_MIGRATION_DIR=MIGRATION_DIR
|
Usage: goose [OPTIONS] COMMAND
Creating migration
To create new file for SQL migration, use this command
1
2
3
| $ goose create add_users_table sql
2021/10/18 21:52:11 Created new file: 20211018215211_add_users_table.sql
|
This will create migrations in the current directory. If you want to create the migrations in a separate directory please supply the -dir migrations argument.
Edit the newly created file to define the behavior of your migration. Let us add content to create a users table in our case. For the action up, we are going to create a users table and add indexes for uniqueue and indexed fields.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| -- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
-- +goose StatementEnd
create table users (
id bigserial primary key,
username varchar(255) not null,
names varchar(225) not null,
email varchar(255) not null,
password_hash varchar(255) not null,
created_at timestamptz not null default clock_timestamp(),
updated_at timestamptz
);
create unique index users_email_uniq_idx ON users(LOWER(email));
create unique index users_username_uniq_idx ON users(LOWER(username));
create index users_email_idx ON users(LOWER(email));
create index users_username_idx ON users(LOWER(username));
-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd
drop index if exists users_username_idx;
drop index if exists users_email_idx;
drop index if exists users_username_uniq_idx;
drop index if exists users_email_uniq_idx;
drop table if exists users;
|
Let’s create another migration file to add posts
1
2
| $ goose create add_posts_table sql
2021/10/18 22:04:33 Created new file: 20211018220433_add_posts_table.sql
|
Then add the content for up and down
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
| -- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
-- +goose StatementEnd
create table posts (
id bigserial primary key,
user_id bigint not null references users(id),
slug varchar(255) not null,
title varchar(100) not null,
description varchar(255),
content text not null,
created_at timestamptz not null default clock_timestamp(),
updated_at timestamptz
);
create index posts_user_id_idx ON posts(user_id);
-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd
drop index if exists posts_user_id_idx;
drop table if exists posts;
|
Now we have to migration files
1
2
3
4
| $ ls
20211018215211_add_users_table.sql
20211018220433_add_posts_table.sql
|
Applying migrations
Exporting variables
Before proceeding, we have to declare the database to use and the conection string to the database. As of goose version 3.2.0, the variable GOOSE_DRIVER and GOOSE_DBSTRING are expected for the database driver and database connection string respectively. Use these export commands to define
1
2
| export GOOSE_DRIVER=postgres
export GOOSE_DBSTRING="user=citizix_user password=pcDFBXo5yX host=10.2.11.10 dbname=citizix_app sslmode=disable"
|
status
Now that we have some files, we can check the state of our migrations using the goose status command:
1
2
3
4
5
6
| $ goose status
2021/10/19 21:45:30 Applied At Migration
2021/10/19 21:45:30 =======================================
2021/10/19 21:45:31 Pending -- 20211018215211_add_users_table.sql
2021/10/19 21:45:32 Pending -- 20211018220433_add_posts_table.sql
|
up
Use the command goose up to apply all available migrations.
1
2
3
4
5
| $ goose up
2021/10/19 21:50:36 OK 20211018215211_add_users_table.sql
2021/10/19 21:50:40 OK 20211018220433_add_posts_table.sql
2021/10/19 21:50:40 goose: no migrations to run. current version: 20211018220433
|
Confirm by checking status
1
2
3
4
5
6
| $ goose status
2021/10/19 21:51:20 Applied At Migration
2021/10/19 21:51:20 =======================================
2021/10/19 21:51:21 Tue Oct 19 18:50:33 2021 -- 20211018215211_add_users_table.sql
2021/10/19 21:51:22 Tue Oct 19 18:50:37 2021 -- 20211018220433_add_posts_table.sql
|
up-to
Migrate up to a specific version (including that version).
1
2
3
4
5
| $ goose up-to 20211018220433
2021/10/19 21:56:50 OK 20211018215211_add_users_table.sql
2021/10/19 21:56:54 OK 20211018220433_add_posts_table.sql
2021/10/19 21:56:54 goose: no migrations to run. current version: 20211018220433
|
up-by-one
Migrate up a single migration from the current version
1
2
3
| $ goose up-by-one
2021/10/19 21:58:47 OK 20211018215211_add_users_table.sql
|
down
Roll back a single migration from the current version.
1
2
3
| $ goose down
2021/10/19 21:52:40 OK 20211018220433_add_posts_table.sql
|
down-to
Roll back migrations to a specific version (will not include that version).
1
2
3
4
| $ goose down-to 20211018215211
2021/10/19 21:53:54 OK 20211018220433_add_posts_table.sql
2021/10/19 21:53:55 goose: no migrations to run. current version: 20211018215211
|
redo
Roll back the most recently applied migration, then run it again.
1
2
3
4
| $ goose redo
2021/10/19 21:55:04 OK 20211018215211_add_users_table.sql
2021/10/19 21:55:08 OK 20211018215211_add_users_table.sql
|
reset
Clears all migrations
1
2
3
4
| $ goose reset
2021/10/19 21:58:04 OK 20211018220433_add_posts_table.sql
2021/10/19 21:58:08 OK 20211018215211_add_users_table.sql
|
Conclusion
In this guide we managed to explore how to use golang goose to manage database migrations