golangtutorial

SQL Migrations in a Golang Project

Managing database schemas can feel like walking a tightrope. As your application grows and user demands change, your database needs to evolve too. You'll add tables, modify columns, and refine relationships. The trick is doing this without causing data loss, breaking existing features, or making deployment a nightmare. This is exactly where database migrations save the day.

Migrations provide a structured, version-controlled way to apply incremental and, crucially, reversible changes to your database schema. They ensure that your development, staging, and production environments all have the correct database structure at any given time.

In the Go ecosystem, you have a few excellent choices for migration tools, such as github.com/golang-migrate/migrate and github.com/pressly/goose. For this guide, we'll dive into Goose, a robust and straightforward tool for writing, managing, and applying SQL database migrations.

We'll cover everything you need to get started:

  • Installing Goose
  • Creating migration scripts
  • Running migrations against your database
  • Embedding migrations directly into your Go application binary

Installation

Getting Goose up and running is as simple as a single Go command. Open your terminal and execute:

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

This command downloads and installs the goose command-line interface (CLI) tool to your GOPATH/bin directory, making it accessible from your terminal.

Creating Migration Scripts

Goose uses SQL files to define your database changes. Each migration script has two parts: an "Up" section for applying changes and a "Down" section for reverting them.

To generate a new migration script, use the goose create command:

goose -dir migrations create initial_schema sql

Let's break down this command:

  • goose: Calls the Goose CLI tool.
  • -dir migrations: Tells Goose to create the migration file inside a directory named migrations. If this directory doesn't exist, Goose will create it for you.
  • create: The Goose command to generate a new migration file.
  • initial_schema: This is the descriptive name you're giving to your migration. Goose will prepend a timestamp to this name (e.g., 20240521103000_initial_schema.sql).
  • sql: Specifies that you want a .sql file for your migration. Alternatively, you could use go to generate a .go file for programmatic migrations, but we'll stick to SQL here for simplicity.

After running this, you'll find a new SQL file in your migrations directory with the following structure:

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

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

Now, let's replace the placeholder SELECT statements with actual SQL queries to define our initial database schema. We'll create a simple users table:

-- 20240521103000_initial_schema.sql (the actual timestamp will vary)

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

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

Key takeaways for migration scripts:

  • -- +goose Up: Contains the SQL commands to apply the migration (e.g., CREATE TABLE, ALTER TABLE, ADD COLUMN).
  • -- +goose Down: Contains the SQL commands to revert the migration (e.g., DROP TABLE, DROP COLUMN). It's crucial to define both Up and Down migrations to allow for easy rollbacks and prevent schema inconsistencies.
  • -- +goose StatementBegin / -- +goose StatementEnd: These directives wrap individual SQL statements. This is particularly useful when you have multiple statements in one section, ensuring Goose executes them correctly.
  • IF NOT EXISTS / IF EXISTS: Using these clauses (CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS) makes your migrations more robust, preventing errors if you try to create a table that already exists or drop one that doesn't.

Running Migration Scripts

Once your migration script is ready, you can apply it to your database using the goose CLI. You'll need to specify the database driver and the connection string.

For our SQLite example, if your database file is named db.sqlite in the current directory:

goose -dir migrations sqlite3 ./db.sqlite up

Let's break down this command:

  • -dir migrations: Again, specifies the directory where your migration files are located.
  • sqlite3: This is the chosen database driver. Goose supports various drivers. If you were using PostgreSQL, this would be postgres.
  • ./db.sqlite: This is the connection string to your database. For SQLite, it's the file path. For other databases, it would be a full connection string, such as "host=localhost port=5432 user=postgres password=mysecretpassword dbname=mydb sslmode=disable".
  • up: This is the direction of migration.
    • up: Migrates your database schema forward to the latest version. Goose tracks which migrations have been applied, so it only runs the new ones.
    • down: Reverts the last applied migration, effectively moving your schema back by one version.
    • status: Shows the current status of all migrations (applied or pending).
    • version: Shows the current database version.

Using Environment Variables

For convenience and security, especially in production environments, you can define the database driver and connection string using environment variables:

export GOOSE_DRIVER=sqlite3
export GOOSE_DBSTRING=./db.sqlite
goose -dir migrations up # Now you don't need to specify driver and dbstring every time

This approach keeps sensitive connection details out of your shell history and allows for easier configuration management.

Embedding Migrations into Your Go Project

A powerful feature of Go (since version 1.16) is the embed package, which lets you bundle static files directly into your application's binary. This means you don't need to distribute your migration SQL files separately with your compiled Go application. Goose seamlessly integrates with this.

First, within your main.go file (or any other file where you'll manage migrations), declare an embed.FS variable:

// main.go
package main

import "embed" // Don't forget to import the embed package

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

The //go:embed migrations/*.sql directive tells the Go compiler to embed all files ending with .sql inside the migrations directory into the MigrationsFS variable, which will act as a virtual file system.

Next, you can use this embedded file system with the goose package directly in your application's code. First, ensure you've installed the Goose library itself (not just the CLI):

go get -u github.com/pressly/goose/v3

Now, modify your main function (or a dedicated database initialization function) to apply migrations programmatically:

// main.go (continued)
package main

import (
	"database/sql" // Don't forget to import database/sql
	"embed"
	"log" // For logging fatal errors

	"github.com/pressly/goose/v3" // Import the goose library
	_ "github.com/mattn/go-sqlite3" // Import your database driver (e.g., for SQLite)
)

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

func main() {
	// Open your database connection
	db, err := sql.Open("sqlite3", "file:./your_app_database.sqlite")
	if err != nil {
		log.Fatal("failed to open database:", err)
	}
	defer db.Close() // Ensure the database connection is closed when main exits

	// Set the embedded file system as Goose's base file system for migrations
	goose.SetBaseFS(MigrationsFS)

	// Set the database dialect (e.g., "sqlite3", "postgres", "mysql")
	if err := goose.SetDialect("sqlite3"); err != nil {
		log.Fatal("failed to set goose dialect:", err)
	}

	// Run all pending "up" migrations to bring the database schema to the latest version
	if err := goose.Up(db, "migrations"); err != nil {
		log.Fatal("failed to run migrations:", err)
	}

	log.Println("Database migrations applied successfully!")

	// Your application's main logic goes here, e.g., starting an HTTP server
	// http.ListenAndServe(":8080", nil)
}

By embedding and running migrations this way, every time your application starts, it will automatically check for and apply any pending database schema changes. This creates a self-healing and always-up-to-date database, streamlining your deployment process and ensuring consistency across all environments. It's a common and highly recommended pattern for Go applications.

What database drivers are you planning to use with Goose in your projects?