Start DB Schema Migration with "goose"

#sql #postgres #mysql #rdb

This is the post assigned as "LOB advent calender" 12 day, have fun !

Today, let me share a small tool "goose". It is a schema migration tool and it brings the management of schema migrations into the project. To tell the truth, I wasn't familiar with schema migration until I know this tool. In this post, I'll explain that "goose" is what tool, and also denote what is schema migration for newbies like past me.

What is "Schema Migration" ?

In the world, there are a lot of online services for web, messaging, finance, advertising and so on, and much of them depend on database system. Also, much of them faces with the need to change the schema in the service lifetime, for example creating a table, altering table to add/del columns and so on. It is called "schema migration" to change the schema specifically for online database. Also, not only that, it means to manage such changes to keep traceability. rails migrate is very famous tool to provide schema migration. But it is the command embedded on Rails framework, so the non-Rails project will welcome the more portable tool.

What is "goose" ?

Goose is one of the schema migration tools, and it is small but very usual. It is written in Go, and you can install with "go get" or bringing a prebuilt binary.

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

By the way, there are two variations of goose. Of the following two, the second is original and the first is forked from the original. The tool I introduce today is the first one, because I think the forked one is superior in terms of some features have been added.

github.com/pressly/goose (forked from the original)
bitbucket.org/liamstask/goose (original)

Demonstration

First of all, we need to create a migration file. we can generate it with goose create:

goose DRIVER DBSTRING create NAME TYPE

- DRIVER: A name of driver to query db.
- DBSTRING: A string passed to the driver as parameter.
- NAME: A name of the generated migration file.
- TYPE: "sql" or "go" as the type whose the generated migration file.

And, you can use as following:

$ goose sqlite3 ~/database.db create 00001_create_first_table sql
2018/12/11 18:55:01 Created new file: 20181211185501_00001_create_first_table.sql

It means that the tool creates new migration file "<timestamp>_00001_create_first_table" where we write the steps to change schema for sqlite3 database that is located on "~/database.db". And we need to update the migration file. The migration file is generated as following:

-- +goose Up
-- SQL in this section is executed when the migration is applied.

-- +goose Down
-- SQL in this section is executed when the migration is rolled back.

The migration file has two sections. The first is "+goose Up", the area where we can write the migration steps. The second is "+goose Down", the area where we can write the step to invert the changes for "+goose Up" steps. The primaly example is as following:

-- +goose Up
CREATE TABLE post (
    id int NOT NULL,
    title text,
    body text,
    PRIMARY KEY(id)
);

-- +goose Down
DROP TABLE post;

We can write migration steps even in Go, not only SQL. But if using Go, we need to make custom "goose" binary built with migration step Go sources. So it takes a little effort.

package main

import (
    "database/sql"

    "github.com/pressly/goose"
)

func init() {
    goose.AddMigration(Up00002, Down00002)
}

func Up00002(tx *sql.Tx) error {
    _, err := tx.Exec("UPDATE users SET username='admin' WHERE username='root';")
    if err != nil {
        return err
    }
    return nil
}

func Down00002(tx *sql.Tx) error {
    _, err := tx.Exec("UPDATE users SET username='root' WHERE username='admin';")
    if err != nil {
        return err
    }
    return nil
}

Note that we create migration files every time to change schema, and it is recommended to be placed on the same directory. Do you think that then many migration files are accumulated ? That's right, and it is very expected because many migration files just stand for migration history. So you can set up a database with either latest schema and the schema at the specified point.

$ ls migrations
20181211185501_00001_create_tables.sql
20190506082527_00002_add_columns_for_new_feature.sql
20191020225109_00003_alter_field_type_for_bugfix.sql

Actually, to apply the database changes that are written in migration files use goose up command:

// apply changes up to latest
goose up

// apply changes up to the specified point
goose up-to 20181211102811

And you always revert the schema changes with goose down command unless the steps of "goose Down" on each files works correctly:

// revert previous change
goose down

// revert changes up to the specified point
goose down-to 20181211102811

Conclusion

Goose is a schema migration tool.
Goose changes schema of DB and keeps tracability of that.
Goose is portable, not depending on the project's framework.
We can write even in Go, but it takes a little effort.

Finally, this is my debut post on dev.to, and also my debut post written in English. You might find the strange expression in this post, but then please forgive me and point it out to me for English learning if you can ;)

WE ARE HIRING: We are "LOB, inc", Japanese ad-tech start-up company in Rakuten Group and building the new global ad platform ! You don't need to hesitate even if you don't speak Japanese. In Rakuten Group the common language is English and many foreign engineer are working. If you are interested, please contact me or apply an entry from the following recruit page.