Database Migrations in Go: Overview of Tools and Practices

Database Migrations in Go (Golang): A Complete Guide

When developing web applications in Go, sooner or later you will need to change the database structure: add new tables, modify existing columns, or create indexes. Manually executing SQL scripts in different environments (development, staging, production) leads to errors and desynchronization. The solution to this problem is database migrations.

In Go, there are several mature libraries for managing migrations. In this article, we will take a detailed look at the two most popular ones: golang-migrate/migrate and pressly/goose, and provide examples of their use.

What are migrations and why are they needed?

A migration is a versioned set of SQL commands (or Go code) that is applied to the database sequentially. Each migration has a unique identifier (usually a timestamp or number) and can be applied (up) or rolled back (down).

Main advantages:

• Automation of database schema changes
• Reproducibility — the same result in any environment
• Ability to roll back changes
• Storing the change history in code (VCS-friendly)

1. The golang-migrate/migrate Library

This is the most popular library for migrations in Go. It supports PostgreSQL, MySQL, SQLite, MongoDB, and other DBMS. Migrations can be written in pure SQL or in Go.

Installation

go get -u github.com/golang-migrate/migrate/v4

Migration File Structure

Files are located in the migrations/ folder and are named according to the template:

{timestamp}_{title}.up.sql{timestamp}_{title}.down.sql

Example:

migrations/  000001_create_users_table.up.sql  000001_create_users_table.down.sql  000002_add_email_to_users.up.sql  000002_add_email_to_users.down.sql

Example SQL Migration

File 000001_create_users_table.up.sql:

CREATE TABLE IF NOT EXISTS users (    id SERIAL PRIMARY KEY,    name VARCHAR(255) NOT NULL,    created_at TIMESTAMP DEFAULT NOW());

File 000001_create_users_table.down.sql:

DROP TABLE IF EXISTS users;

Applying Migrations from Go Code

package main

import (    "github.com/golang-migrate/migrate/v4"    _ "github.com/golang-migrate/migrate/v4/database/postgres"    _ "github.com/golang-migrate/migrate/v4/source/file")

func main() {    m, err := migrate.New(        "file://migrations",        "postgres://user:password@localhost:5432/dbname?sslmode=disable",    )    if err != nil {        panic(err)    }

    // Apply all available migrations (Up)    if err := m.Up(); err != nil && err != migrate.ErrNoChange {        panic(err)    }

    // Roll back the last migration (Down)    // if err := m.Steps(-1); err != nil {    //     panic(err)    // }}

CLI Tool migrate

A command-line utility is also available:

migrate create -ext sql -dir migrations -seq create_users_tablemigrate -path migrations -database "postgres://..." upmigrate -path migrations -database "postgres://..." down 1

2. The pressly/goose Library

Goose is another popular tool, originally created as an analogue of ActiveRecord migrations from Ruby on Rails. It supports SQL and Go migrations, as well as a built-in CLI.

Installation

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

File Structure

Files are named as {timestamp}_{title}.sql (without up/down separation — everything in one file via comments):

migrations/  20240101120000_create_users_table.sql  20240102150000_add_email_to_users.sql

Example SQL Migr