Application code is stateless. You can tear down a container and spin up a new one in milliseconds without losing data. Databases are stateful. When you deploy new application logic that requires a new column, an index, or a table, you must transition the physical storage schema from state A to state B without destroying the underlying data or locking the system.

This process is a database migration. Working with databases is the kind of thing I will gladly skip dinner for (:

The Road to Raw SQL

My obsession with relational databases started in 2018 while building my first Django app in college. Django’s ORM is a masterpiece of abstraction, and it was through its lens that I first learned about ACID properties, Normal Forms, and the uncompromising logic of foreign keys.

In the years since, I’ve worked across the spectrum, including production environments and toy projects alike, using .NET, PHP, Python, and Node. Each ecosystem has its own way of shielding the developer from the database. Each tries to pretend that the disk doesn’t exist.

But as I’ve moved deeper into Go and PostgreSQL, my preference has shifted. I no longer want the abstraction; I want the raw SQL. I want to see the DDL. I want to own the migration. This isn’t just about the language. I just want to know exactly what is happening to the data.

Executing migrations manually via SQL clients is an operational liability. It lacks version control, it cannot be integrated into CI/CD pipelines, and it is prone to human error. Migrations must be treated as code: versioned, reviewed, and executed deterministically.

This blog details the mechanics of database migrations and provides a strict implementation guide using PostgreSQL for storage, Go for execution, and Goose as the migration engine.

The Mechanics of Schema State

A migration engine requires three components to operate safely:

1. Immutable Migration Files: Ordered scripts containing the Data Definition Language (DDL) required to transition the schema.
2. Directionality: Explicit Up (apply) and Down (rollback) logic.
3. State Tracking: An internal table within the target database recording which migration files have been successfully executed.

When the migration engine runs, it compares the local directory of migration files against the internal state table. It computes the delta and applies the pending Up scripts sequentially.

The Stack: PostgreSQL, Go, and Goose

The Go ecosystem has several migration tools. Goose is my favorite choice because it treats SQL as the primary interface. Unlike ORM-based migration tools that abstract DDL into application code, Goose uses raw .sql files. This lets you use PostgreSQL-specific features (like concurrent index creation or custom data types) that abstraction layers often block.

Goose also supports go:embed, allowing you to compile your SQL migration files directly into your Go binary. This produces a single, self-contained executable that can self-migrate before starting the HTTP server.

How to Implement

We will follow a strict implementation workflow to build the migration binary.

1. Project Architecture

Create the directory structure for the migrations. We keep the SQL files isolated from the application logic.

1mkdir -p db/migrations

2. Defining the SQL Migrations

Goose uses a specific comment syntax to separate Up and Down migrations within a single file.

Create the initial schema in db/migrations/001_initialize_users.sql.

1-- +goose Up
2CREATE TABLE users (
3    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4    email VARCHAR(255) UNIQUE NOT NULL,
5    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
6);
7
8-- +goose Down
9DROP TABLE users;

3. The Zero-Downtime Migration Pattern

Standard DDL commands in PostgreSQL, such as ALTER TABLE or CREATE INDEX, acquire an AccessExclusiveLock. This lock blocks all read and write operations on the table until the migration completes. On a table with millions of rows, this causes an immediate production outage.

To bypass this, PostgreSQL provides CREATE INDEX CONCURRENTLY. However, this command cannot be run inside a transaction block. Goose wraps migrations in transactions by default. We must use the -- +goose NO TRANSACTION directive to execute zero-downtime migrations safely.

Create db/migrations/002_add_status_index.sql.

1-- +goose NO TRANSACTION
2-- +goose Up
3CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at);
4
5-- +goose Down
6DROP INDEX CONCURRENTLY IF EXISTS idx_users_created_at;

4. Go Implementation

We will now implement the Go code to execute these migrations. Following the staged implementation workflow, we isolate the migration logic into its own package.

 1package database
 2
 3import (
 4	"database/sql"
 5	"embed"
 6	"fmt"
 7
 8	"github.com/pressly/goose/v3"
 9	_ "github.com/jackc/pgx/v5/stdlib"
10)
11
12//go:embed migrations/*.sql
13var embedMigrations embed.FS
14
15func MigrateDatabase(dbUrl string) error {
16	db, err := sql.Open("pgx", dbUrl)
17	if err != nil {
18		return fmt.Errorf("failed to open db: %w", err)
19	}
20	defer db.Close()
21
22	goose.SetBaseFS(embedMigrations)
23
24	if err := goose.SetDialect("postgres"); err != nil {
25		return fmt.Errorf("failed to set dialect: %w", err)
26	}
27
28	if err := goose.Up(db, "migrations"); err != nil {
29		return fmt.Errorf("migration failed: %w", err)
30	}
31
32	return nil
33}

5. Execution and State Verification

When MigrateDatabase is called from your main.go, Goose will connect to PostgreSQL and execute the pending .sql files.

You can verify the state by querying the internal tracking table created by Goose.

1SELECT id, version_id, is_applied, tstamp 
2FROM goose_db_version 
3ORDER BY id DESC;

The output will confirm the exact timestamp each migration was applied. The database state is now deterministic, version-controlled, and mechanically linked to the application binary.

6. Local Development with the Goose CLI

While embedding migrations in the Go binary is perfect for production deployments, you need the Goose CLI for day-to-day local development.

Install the CLI via Go:

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

To avoid typing the driver and connection string every time, I export them in my .env or .envrc file:

1export GOOSE_DRIVER=postgres
2export GOOSE_DBSTRING="postgres://user:pass@localhost:5432/app?sslmode=disable"
3export GOOSE_MIGRATION_DIR="db/migrations"

Once those are set, these are the core commands you will use:

Creating a new migration file: This generates a timestamped .sql file with the correct Up and Down annotations.

1goose create add_users_table sql

Checking migration status: See exactly which migrations are pending and which have been applied to your local database.

1goose status

Applying pending migrations: Run all unapplied Up scripts sequentially.

1goose up

Rolling back a migration: Run the Down script for the most recently applied migration. If you break your local schema, this is how you step backwards safely.

1goose down

7. Automating in CI/CD

Running goose up from your laptop against the production database is a rite of passage, but it is also how you take down the company. Migrations belong in your deployment pipeline.

Because Goose relies on standard SQL files and a single binary, integrating it into CI/CD is just a matter of running the CLI against your production database before you deploy the new application code.

Here is what that looks like in a standard GitHub Actions workflow:

 1name: Production Migrations
 2on:
 3  push:
 4    branches:
 5      - main
 6jobs:
 7  migrate:
 8    runs-on: ubuntu-latest
 9    steps:
10      - name: Checkout code
11        uses: actions/checkout@v4
12        
13      - name: Install Goose
14        run: curl -fsSL https://raw.githubusercontent.com/pressly/goose/master/install.sh | sh
15        
16      - name: Execute Migrations
17        env:
18          GOOSE_DRIVER: postgres
19          GOOSE_DBSTRING: ${{ secrets.PROD_DB_URL }}
20        run: goose -dir db/migrations up

For the legacy enterprise folks still riding the Jenkins train, the mechanics are exactly the same. You just drop the curl install and the goose up command into a sh block inside your Jenkinsfile deployment stage.

By keeping this in CI/CD, the pipeline becomes the only entity with the credentials and authority to alter the production schema. It is deterministic, auditable, and entirely mechanical.