How to Run Postgres Migrations in a Go API

Every Go service with a Postgres database needs a way to change the schema over time, and the common answer is numbered SQL files applied in order. Writing the SQL is the easy part. The failures come from timing: a migration that runs after the new code is already serving, a half-applied migration that blocks every later deploy, or a column rename that breaks the instances still running the old binary.

This guide adds a sku column and a unique index to a products table in a Go API. It starts with Encore (a tool that works alongside your framework), which applies migrations as part of every run and deploy, then compares golang-migrate and goose, and ends with the patterns that keep schema changes safe with any tool.

Go migration tools at a glance

Each option is covered below, starting with Encore.

Keeping migrations next to the database declaration with Encore

Encore is a tool that works alongside your framework for TypeScript and Go, with a platform that provisions and deploys into your own AWS or GCP account. A database is declared in the service that owns it, with a path to its migrations:

// catalog/catalog.go
package catalog

import (
	"context"

	"encore.dev/storage/sqldb"
)

// Cloud SQL on GCP, RDS on AWS, a local Postgres under encore run.
var db = sqldb.NewDatabase("catalog", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

type CreateParams struct {
	Name       string  `json:"name"`
	PriceCents int     `json:"priceCents"`
	SKU        *string `json:"sku"`
}

type Product struct {
	ID         int64   `json:"id"`
	Name       string  `json:"name"`
	PriceCents int     `json:"priceCents"`
	SKU        *string `json:"sku"`
}

//encore:api public method=POST path=/products
func Create(ctx context.Context, p *CreateParams) (*Product, error) {
	prod := &Product{Name: p.Name, PriceCents: p.PriceCents, SKU: p.SKU}
	err := db.QueryRow(ctx, `
		INSERT INTO products (name, price_cents, sku) VALUES ($1, $2, $3)
		RETURNING id`, p.Name, p.PriceCents, p.SKU).Scan(&prod.ID)
	return prod, err
}

The migrations directory holds plain SQL. The first file creates the table:

-- catalog/migrations/1_create_products.up.sql
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price_cents INT NOT NULL
);

Adding the SKU is a second file with the next number. The column is nullable, so code that does not know about it keeps working:

-- catalog/migrations/2_add_sku.up.sql
ALTER TABLE products ADD COLUMN sku TEXT;
CREATE UNIQUE INDEX products_sku_idx ON products (sku);

encore run starts Postgres locally and applies any new files before the app starts, and encore db shell catalog opens psql against the local database to inspect the result. On deploy, Encore runs new migrations before the new code starts. If one fails, for example because existing rows violate the unique index, Encore rolls it back and aborts the deploy, so the previous version keeps serving. Applied versions are tracked in a schema_migrations table, as described in the database docs.

When an agent changes the schema, the migration and the Go code that depends on it arrive in the same diff, and encore run applies the migration to a local database before anyone reviews it.

The connect your cloud account and deployment guides cover how migrations run as part of a deploy to your own AWS or GCP account.

golang-migrate

golang-migrate is the most widely used standalone tool. Files come in pairs and are numbered by the CLI:

migrate create -ext sql -dir migrations -seq add_sku
# migrations/000002_add_sku.up.sql
# migrations/000002_add_sku.down.sql

migrate -path ./migrations -database "$DATABASE_URL" up

The up command goes in your deploy pipeline before the new container rolls out, with network access to the production database and its credentials. When a migration fails partway, golang-migrate marks the version as dirty and refuses to continue until you inspect the database and run migrate force <version>. That is safe, but it means someone has to be there to unblock the next deploy.

goose

goose keeps the up and down steps in one annotated file and also accepts migrations written as Go functions, which helps for data backfills that are awkward in SQL:

-- +goose Up
ALTER TABLE products ADD COLUMN sku TEXT;
CREATE UNIQUE INDEX products_sku_idx ON products (sku);

-- +goose Down
DROP INDEX products_sku_idx;
ALTER TABLE products DROP COLUMN sku;

You run it with goose -dir migrations postgres "$DATABASE_URL" up, or embed the files and call the library at startup. Running migrations at startup is convenient, but with several replicas starting at once, each one tries to migrate, so most teams keep it as a separate step.

Schema changes that do not break running code

Whatever the tool, migrations run while the previous version of the code is still serving traffic. A few patterns follow from that:

• Add columns as nullable or with a default. Old code that inserts without the column keeps working.
• Build indexes on large tables with CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks writes for the duration. Concurrent builds cannot run inside a transaction, so check how your tool wraps each file.
• Rename in steps. Add the new column, write to both, backfill, switch reads, and drop the old column in a later deploy.
• Never edit an applied migration. Write a new one; the tracking table assumes old files do not change.

How to choose

Pick Encore when you want the database, its migrations and the code that uses them in one place, applied the same way locally and on every deploy to your own AWS or GCP account, with failed migrations aborting the deploy.

Pick golang-migrate when the database is managed outside the application and you want a widely used CLI in your pipeline, and goose when some migrations need to be Go code, such as backfills that call application logic.

Related guides

• How to add a database to a Go API
• Best Go backend frameworks
• Go ORMs
• How to connect Cloud Run to Cloud SQL