# How to Handle PostgreSQL Database Migrations in Go with Goose > **Source:** [https://lorbic.com/how-to-handle-postgresql-database-migrations-in-go-with-goose/](https://lorbic.com/how-to-handle-postgresql-database-migrations-in-go-with-goose/) > **Author:** [Vikash Patel](https://vikashpatel.net) > **Published:** May 08, 2026 > **Reading Time:** 7 min > > *This is the raw Markdown source of the article from the [Lorbic Technical Journal](https://lorbic.com/).* --- 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 (: ```mermaid graph LR A[State A: Initial Schema] -->|Migration 001| B[State B: Tables Created] B -->|Migration 002| C[State C: Indexes Added] C -->|Rollback 002| B class A state-initial class C state-final ``` ![Goose Migration Terminal Status](goose-migration-header.svg) ## 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. ```mermaid sequenceDiagram participant Dev as Developer participant Git as Version Control participant CI as CI/CD Pipeline participant DB as Production Database Dev->>Git: Push SQL Migration Git->>CI: Trigger Build CI->>CI: Run Tests/Lint CI->>DB: Apply Migration (Goose) DB-->>CI: Success/Version Updated CI-->>Dev: Deployment Successful ``` 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. ```mermaid flowchart TD subgraph Local Files F1[001_init.sql] F2[002_add_index.sql] F3[003_add_column.sql] end subgraph DB State Table S1[001_init] S2[002_add_index] end F1 --- S1 F2 --- S2 F3 -->|Delta Found| Engine{Goose Engine} Engine -->|Apply| DB[(PostgreSQL)] DB -->|Update| S3[003_add_column] ``` ## 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. ```mermaid graph TD subgraph Compile Time SQL[.sql files] --> Embed[go:embed] Embed --> Bin[Binary Executable] end subgraph Deployment Bin -->|1. Migrate| DB[(Postgres)] Bin -->|2. Start| Srv[HTTP Server] end ``` ## 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. ```bash mkdir -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`. ```sql -- +goose Up CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); -- +goose Down DROP 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. ```mermaid stateDiagram-v2 [*] --> Idle Idle --> MigrationStarted: Run ALTER TABLE state MigrationStarted { [*] --> LockAcquired: AccessExclusiveLock LockAcquired --> WriteBlocked: App Writes Waiting... WriteBlocked --> ReadBlocked: App Reads Waiting... ReadBlocked --> Outage: Queues Full / Timeouts } Outage --> Idle: Migration Finished / Lock Released ``` 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`. ```sql -- +goose NO TRANSACTION -- +goose Up CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at); -- +goose Down DROP 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. ```go package database import ( "database/sql" "embed" "fmt" "github.com/pressly/goose/v3" _ "github.com/jackc/pgx/v5/stdlib" ) //go:embed migrations/*.sql var embedMigrations embed.FS func MigrateDatabase(dbUrl string) error { db, err := sql.Open("pgx", dbUrl) if err != nil { return fmt.Errorf("failed to open db: %w", err) } defer db.Close() goose.SetBaseFS(embedMigrations) if err := goose.SetDialect("postgres"); err != nil { return fmt.Errorf("failed to set dialect: %w", err) } if err := goose.Up(db, "migrations"); err != nil { return fmt.Errorf("migration failed: %w", err) } return nil } ``` ### 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. ```sql SELECT id, version_id, is_applied, tstamp FROM goose_db_version ORDER 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: ```bash go 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: ```bash export GOOSE_DRIVER=postgres export GOOSE_DBSTRING="postgres://user:pass@localhost:5432/app?sslmode=disable" export 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. ```bash goose create add_users_table sql ``` **Checking migration status:** See exactly which migrations are pending and which have been applied to your local database. ```bash goose status ``` **Applying pending migrations:** Run all unapplied `Up` scripts sequentially. ```bash goose 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. ```bash goose 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: ```yaml name: Production Migrations on: push: branches: - main jobs: migrate: runs-on: ubuntu-latest steps: - name: Checkout code uses: actions/checkout@v4 - name: Install Goose run: curl -fsSL https://raw.githubusercontent.com/pressly/goose/master/install.sh | sh - name: Execute Migrations env: GOOSE_DRIVER: postgres GOOSE_DBSTRING: ${{ secrets.PROD_DB_URL }} 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.