Database and migrations

👨‍🏫 Before we start…

We’ll run a Postgres database via docker-compose.

There are a few popular database migration tools in the Go ecosystem, like golang-migrate/migrate, pressly/goose, GORM migrations, etc. We selected pressly/goose due to its simplicity, lesser resource usage, and customizability. But, instead of using its prebuilt binaries, we’ll build a custom binary with static drivers, settings, and more simplified commands.

⭐ We’ll store all SQL database migration files in the migrations folder in the project root.

Database Design

In this article series, we are building a RESTful CRUD API for a simple bookshelf. We’ll use the Postgres database and a table called “books” to store the data, which has the following columns.

Column Name	Datatype	Not Null	Primary Key
id	UUID	✅	✅
title	TEXT	✅
author	TEXT	✅
published_date	DATE	✅
image_url	TEXT
description	TEXT
created_at	TIMESTAMP	✅
updated_at	TIMESTAMP	✅
deleted_at	TIMESTAMP

Development Database

We’ll use the official Postgres Alpine Docker image to build our development database.

Let’s update the docker-compose.yml file,

version: '3'
services:

  app:
    build: .
    ports:
      - "8080:8080"
    depends_on:
      - db

  db:
    image: postgres:alpine
    environment:
      - POSTGRES_DB=myapp_db
      - POSTGRES_USER=myapp_user
      - POSTGRES_PASSWORD=myapp_pass
    ports:
      - "5432:5432"
    restart: always

Run docker-compose down and docker-compose up to rerun the application with the database. Or, you can run docker-compose up db to run only the database.

We can use docker-compose ps commands to see more info about the running containers.

NAME                COMMAND                  SERVICE             STATUS              PORTS
myapp-app-1         "/myapp/bin/api"         app                 running             0.0.0.0:8080->8080/tcp
myapp-db-1          "docker-entrypoint.s…"   db                  running             0.0.0.0:5432->5432/tcp

You can use any PostgreSQL client like DBeaver, Beekeeper studio to connect to the database. But still it’s just an empty database, because we haven’t add any database migrations so far.

`migrate` app

1. The `migrations` folder

As mentioned earlier, we store all sql database migration files in the migrations folder in the project root. So, let’s create the migrations in project root.

mkdir migrations

2. Download and install the packages and dependencies

Let’s run the following commands to download and install pressly/goose and the dependency jackc/pgx packages.

go get github.com/pressly/goose/v3
go get github.com/jackc/pgx/v5

The go get command updates the go.mod and go.sum files to store the direct and indirect dependencies of our application. You should see above packages as indirect dependencies on the go.mod file because still we haven’t used them in our code.

module myapp

go 1.19

require (
    github.com/jackc/pgpassfile v1.0.0 // indirect
    github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
    github.com/jackc/pgx/v5 v5.2.0 // indirect
    github.com/pressly/goose/v3 v3.8.0 // indirect
    golang.org/x/crypto v0.5.0 // indirect
    golang.org/x/text v0.6.0 // indirect
)

This is the first time we install a third party package via the go get command. You should see newly created go.sum file with the cryptographic hashes of the above package versions.

3. `cmd/migrate/main.go`

The examples folder in the Goose repository shows how to create a custom Goose binary. But we change the code a bit to tweak the CLI to make the database drivers, connection settings and migrations folder fixed and make the commands more user-friendly.

package main

import (
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

const (
	dialect  = "pgx"
	dbString = "host=localhost user=myapp_user password=myapp_pass dbname=myapp_db port=5432 sslmode=disable"
)

var (
	flags = flag.NewFlagSet("migrate", flag.ExitOnError)
	dir   = flags.String("dir", "migrations", "directory with migration files")
)

func main() {
	flags.Usage = usage
	flags.Parse(os.Args[1:])

	args := flags.Args()
	if len(args) == 0 || args[0] == "-h" || args[0] == "--help" {
		flags.Usage()
		return
	}

	command := args[0]

	db, err := goose.OpenDBWithDriver(dialect, dbString)
	if err != nil {
		log.Fatalf(err.Error())
	}

	defer func() {
		if err := db.Close(); err != nil {
			log.Fatalf(err.Error())
		}
	}()

	if err := goose.Run(command, db, *dir, args[1:]...); err != nil {
		log.Fatalf("migrate %v: %v", command, err)
	}
}

func usage() {
	fmt.Println(usagePrefix)
	flags.PrintDefaults()
	fmt.Println(usageCommands)
}

var (
	usagePrefix = `Usage: migrate COMMAND
Examples:
    migrate status
`

	usageCommands = `
Commands:
    up                   Migrate the DB to the most recent version available
    up-by-one            Migrate the DB up by 1
    up-to VERSION        Migrate the DB to a specific VERSION
    down                 Roll back the version by 1
    down-to VERSION      Roll back to a specific VERSION
    redo                 Re-run the latest migration
    reset                Roll back all migrations
    status               Dump the migration status for the current DB
    version              Print the current version of the database
    create NAME [sql|go] Creates new migration file with the current timestamp
    fix                  Apply sequential ordering to migrations`
)

4. Run `go mod tidy`

📖 go mod tidy ensures that the go.mod file matches the source code in the module. It adds any missing module requirements necessary to build the current module’s packages and dependencies, and it removes requirements on modules that don’t provide any relevant packages. It also adds any missing entries to go.sum and removes unnecessary entries.

After running go mod tidy, the jackc/pgx and pressly/goose packages should appear as direct dependencies in the go.mod file, because we now use them in cmd/migrate/main.go file.

module myapp

go 1.19

require (
	github.com/jackc/pgx/v5 v5.2.0
	github.com/pressly/goose/v3 v3.8.0
)

require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect
	golang.org/x/crypto v0.5.0 // indirect
	golang.org/x/text v0.6.0 // indirect
)

5. Update `Dockerfile`

FROM golang:alpine

WORKDIR /myapp
COPY go.mod go.sum ./
RUN go mod download

COPY . .

RUN go build -o ./bin/api ./cmd/api \
    && go build -o ./bin/migrate ./cmd/migrate

CMD ["/myapp/bin/api"]
EXPOSE 8080

Few important things here,

We run go mod download with the local module cache and then copy to the docker image.
We build the migrate app binary inside the bin folder.

`migrate` commands

⭐️ If you try to run ./bin/migrate up inside the docker image, it will give the following error; migrate up: dial tcp 127.0.0.1:3306: connect: connection refused. This is because we hardcoded const dbString = "host=localhost ..." in the cmd/migrate/main.go but the Docker network assigns a different IP address to each container (💡Use docker exec myapp-db-1 cat /etc/hosts to check the IP of the Postgres Docker container).

⭐ As a result, we can’t test migrate commands other than ./bin/migrate -h inside the docker image until we configure our applications in the next article. Until then, let’s run migrate commands directly, via go run ./cmd/migrate.

`migrate -h`

We can use go run ./cmd/migrate -h to test it locally. You should see the list of commands it supports.

Usage: migrate COMMAND
Examples:
    migrate status

  -dir string
        directory with migration files (default "migrations")

Commands:
    up                   Migrate the DB to the most recent version available
    up-by-one            Migrate the DB up by 1
    up-to VERSION        Migrate the DB to a specific VERSION
    down                 Roll back the version by 1
    down-to VERSION      Roll back to a specific VERSION
    redo                 Re-run the latest migration
    reset                Roll back all migrations
    status               Dump the migration status for the current DB
    version              Print the current version of the database
    create NAME [sql|go] Creates new migration file with the current timestamp
    fix                  Apply sequential ordering to migrations

`migrate version`

Database migrations are like version controls for the database. When you run go run ./cmd/migrate version, you should see the timestamp of your current time and goose: version 0 text. If you checked the myapp_db database, you should see the new table goose_db_version with a version_id: 0 record.

`migrate status`

With go run ./cmd/migrate status, we can see more detailed list of migrations. But, because we haven’t added any migrations so far, it will be an empty list with the current timestamp and headings.

2019/08/05 09:00:00     Applied At                  Migration
2019/08/05 09:00:00     =======================================

`migrate create`

Let’s create our first migration file by running go run ./cmd/migrate create create_books_table sql. This will create the create_books_table.sql file with the prefix of current timestamp (ex. 20190805170000_create_books_table.sql) inside the migrations folder with the following content.

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

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

`migrate fix`

We can keep migration files/ version numbers either in timestamps or simpler numeric values like 1, 2, 3, etc. By running go run ./cmd/migrate fix, our first migration file will be renamed to 00001_create_books_table.sql.

RENAMED 20190805170000_create_books_table.sql => 00001_create_books_table.sql

Other commands

💡We will discuss the remaining commands like migrate up, migrate down, migrate reset, after update our migration file with actual SQL.

SQL migrations

Let’s update our 00001_create_books_table.sql file with the actual SQL. In the +goose Up section, we should add the CREATE TABLE script and in the +goose Down section, we should add the DROP TABLE script.

-- +goose Up
-- SQL in this section is executed when the migration is applied.
CREATE TABLE IF NOT EXISTS books
(
    id             UUID PRIMARY KEY,
    title          TEXT      NOT NULL,
    author         TEXT      NOT NULL,
    published_date DATE      NOT NULL,
    image_url      TEXT      NULL,
    description    TEXT      NULL,
    created_at     TIMESTAMP NOT NULL,
    updated_at     TIMESTAMP NOT NULL,
    deleted_at     TIMESTAMP NULL
);

-- +goose Down
-- SQL in this section is executed when the migration is rolled back.
DROP TABLE IF EXISTS books;

With the newly added SQL migration, when you run go run ./cmd/migrate status, you should see the Pending migration now.

2019/08/05 17:00:00     Applied At                  Migration
2019/08/05 17:00:00     =======================================
2019/08/05 17:00:00     Pending                  -- 00001_create_books_table.sql

Running migrations

`migrate up`

To apply our migration, let’s run go run ./cmd/migrate up. Now, you should see new table books and new record on goose_db_version with id: 2 and version_id : 1.

Also, with go run ./cmd/migrate status, now you should the details of the applied migration.

2019/08/05 17:00:00     Applied At                  Migration
2019/08/05 17:00:00     =======================================
2019/08/05 17:00:00     Mon Aug  5 17:00:00 2019 -- 00001_create_books_table.sql

💡As you can see with the go run ./cmd/migrate -h,

migrate up-by-one Migrate the DB up by 1

migrate up-to VERSION Migrate the DB to a specific VERSION

`migrate down`

If you run, go run ./cmd/migrate down, the last migration will be rolled back and you will see the Pending migration with the migrate status command.

💡As you can see with the go run ./cmd/migrate -h,

migrate down Roll back the version by 1

migrate down-to VERSION Roll back to a specific VERSION

migrate redo Re-run the latest migration

migrate reset Roll back all migrations

📁 Final project structure

myapp
├── cmd
│  ├── api
│  │  └── main.go
│  └── migrate
│     └── main.go
│
├── migrations
│  └── 00001_create_books_table.sql
│
├── go.mod
├── go.sum
│
├── docker-compose.yml
└── Dockerfile

👨‍🏫 What’s next…

In the next article, we’ll configure our application and run database migrations on application start.

Database Design

Development Database

migrate app

1. The migrations folder

2. Download and install the packages and dependencies

3. cmd/migrate/main.go

4. Run go mod tidy

5. Update Dockerfile

migrate commands

migrate -h

migrate version

migrate status

migrate create

migrate fix