SQL Migrations in a Golang Project

Managing database schemas is a critical aspect of software development that can often make or break a project. As applications grow and requirements change, we must adapt our database schemas accordingly without compromising data integrity or disrupting service. This is where database migrations come into play: a structured way to handle incremental, reversible changes to our database schema.

Within Golang there are a few options, such as github.com/golang-migrate/migrate and github.com/pressly/goose. Here we will use goose, which offers a straightforward approach to writing, managing, and applying database migrations.

This article will guide you through the process of working with SQL database migrations using Golang and Goose. We will cover: - installing goose - creating migrations scripts - applying migrations in a Golang project - embedding migrations

Installation

Run the following command in the terminal

go install github.com/pressly/goose/v3/cmd/goose@latest

Creating migration scripts

In order to create a migration script, we will use goose CLI’s command create:

goose -dir migrations create initial sql

goose is the command to call the CLI
-dir migrations is an argument to specify the directory in which migration scripts should be created
create is the goose command we want to run
initial is the name we want our migration file to have
sql is the type of migration file goose should create, in this case a .sql file, another option is to use go which will create a .go file instead

Running this command will create a .sql file with the following contents inside the migrations folder:

-- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd

To get started, we replace the SELECT statements with SQL queries to create our database schema:

-- +goose Up
-- +goose StatementBegin
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email VARCHAR(100)
)
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE users;
-- +goose StatementEnd

Here is a simple migration script that creates a users table with two columns, id and email what the up command is run, and drops the users table when a down command is run. Up migrations are used to modify the existing database schema, while down migrations are used to revert the up migration. In case of creating a table, the reverse operation is dropping the table. Similarly the reverse operation of adding a column to a table is dropping the column from the table. Do be sure to always remember to define both the up and down migrations to prevent any confusion upon running migrations.

Running migration scripts

In order to run our migration script against our chosen database, we need to run the following command:

goose -dir migrations sqlite3 ./db.sqlite up

sqlite3 is the chosen database driver, which could also be e.g. postgres
./db.sqlite is the sqlite filepath, or in other words the connection string to our database, i.e. when using postgres, the path would be replaced by a connection string such as "dname=postgres user=postgres password=postgres sslmode=disable"
up is what defines the direction to migrate, up migrates up to the latest version from our current schema version, while down would migrate down by a single version from our current schema version

We can also use environment variables to define the database driver and connection string: GOOSE_DRIVER=sqlite3 and GOOSE_DBSTRING=./db.sqlite.

Embedding migrations

Golang has a neat feature that allows us to embed static files directly into the binary of our application. This can be achieved through the embed package introduced to Go in 1.16.

In order to embed our migrations into our application binary, we need to create a embedded filesystem as follows:

package main

import "embed"

//go:embed migrations/*sql
var MigrationsFS embed.FS

This creates an embedded filesystem that contains all of the .sql files within the migrations folder. In order to use the embedded filesystem with goose migrations, we can add the following lines inside our main function:

package main

import (
    "embed"

    "github.com/pressly/goose/v3"
)


//go:embed migrations/*sql
var MigrationsFS embed.FS

func main() {
    goose.SetBaseFS(MigrationsFS)
	if err := goose.SetDialect("sqlite3"); err != nil {
		log.Fatal(err)
	}
	if err := goose.Up(db, "migrations"); err != nil {
		log.Fatal(err)
	}
}

Here we are using the goose package directly in our code, which means it needs to be installed using the go get command first: go get -u github.com/pressly/goose/v3. We set the dialect to sqlite3 and the migrations folder to migrations, and we run the goose.Up function. Now every time the application is started, migration scripts are run to the newest schema version. This can be a nice way to keep your database schema up to date while requiring modifications to only be done by creating new migration scripts using the goose CLI.