Managing Database migrations with Golang goose using incremental SQL changes

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, use this:

go get -u github.com/pressly/goose/v3/cmd/goose

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:

$ 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.

Once installed, verify version

➜ goose -version
goose version:v3.2.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

Creating migration

To create new file for SQL migration, use this command

➜ 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.

-- +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

➜ 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

-- +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

➜ 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

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:

➜ 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.

➜ 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

➜ 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).

➜ 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

➜ 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.

➜ 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).

➜ 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.

➜ 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

➜ 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