Embedding migrations

Go continues to be boring while sprinkling quality of life features. One of the recent additions was the ability to embed files at compile time. Click here for go1.16 release notes.

Sine many users compile goose themselves, this new embed feature paves the way for embedding SQL files directly into the goose binary. This was already possible with existing tools, however, now that embedding is part of the standard library it's never been easier to offer this feature.

But why?

We'll save "why would I compile goose myself?" for another post, instead we'll focus on why embedding files is an improvement to existing workflows.

A typical workflow looks something like this:

Developer introduces new SQL migration file
File gets merged to main and a goose binary is built
The binary along with SQL files is copied into a docker container
The docker container is run as a singleton against the database before the application starts up

One of the cumbersome things about this workflow is that the goose binary and the migration files need to be shipped together and the directory structure has to be maintained.

But now that goose natively supports embedding files it simplifies the workflow. A goose binary is shipped without any file dependencies, i.e., the migration files are baked into the binary itself.

Gotchas

We did not implement this in a backwards-compatible way, i.e., the feature is not guarded with build tags. Which means starting with v3.1.0 you must be on go1.16 and up.

For older goose versions you may still pin v3.0.1.

Try it out!

Remember, the files to be embedded must be relative to the source file(s). Here is what our directory structure might look like:

.
├── embed_example.sql
├── go.mod
├── go.sum
└── internal
    └── goose
        ├── main.go
        └── migrations
            └── 00001_create_users_table.sql

Here is a fully working example using an in-memory database (SQLite).

package main

import (
    "database/sql"
    "embed"
    "log"

    _ "github.com/mattn/go-sqlite3"
    "github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var embedMigrations embed.FS // (1)

func main() {
    log.SetFlags(0)
    db, err := sql.Open("sqlite3", "embed_example.sql")
    if err != nil {
        log.Fatal(err)
    }
    goose.SetDialect("sqlite3")
    goose.SetBaseFS(embedMigrations) // (2)

    if err := goose.Up(db, "migrations"); err != nil { // (3)
        panic(err)
    }
    if err := goose.Version(db, "migrations"); err != nil {
        log.Fatal(err)
    }
    rows, err := db.Query(`SELECT * FROM users`)
    if err != nil {
        log.Fatal(err)
    }
    var user struct {
        ID       int
        Username string
    }
    for rows.Next() {
        if err := rows.Scan(&user.ID, &user.Username); err != nil {
            log.Fatal(err)
        }
        log.Println(user.ID, user.Username)
    }
}

This //go:embed is a special directive that tells the Go tooling to read files from the package directory or subdirectories at compile time and stores them in the a variable of type embed.FS.

The embed.FS will store a read-only collection of *.sql files.
Pass the embed.FS variable to goose. This instructs goose to use the embedded filesystem instead of opening files from the underlying os.
You still have to tell goose which directory contains the .sql files. This implementation allowed us to keep existing functions without having to change the function signature or add new functions.

It is a drop-in feature that enables the caller to either use the os (as before) or use embedded filesystem without changing parts of their existing programs.

A sample repo can be found at mfridmn/goose-demo

From the root of the directory you can build the binary, and to prove it has no dependencies move it to your home directory and run the binary. This will create a embed_example.sql file for sqlite database. Cool right?!

go build -o goosey internal/goose/main.go
mv goosey $HOME
cd $HOME
./goosey

Output:

OK    00001_create.sql
goose: no migrations to run. current version: 1
goose: version 1
0 root
1 goosey