Managing database changes is critical for any software project. This guide walks you through using golang-migrate with SQLite in Go to handle migrations cleanly and consistently.

What are Database Migrations?

Database migrations are like version control for your database schema. They represent incremental, reversible changes to your database structure. Just as Git tracks changes to your code, migrations track changes to your database schema, making it possible to:

  • Version control database changes
  • Roll back to previous states
  • Maintain consistent database states across different environments
  • Collaborate effectively with team members

Why Use golang-migrate?

golang-migrate is a solid, production-grade tool for schema migrations in Go. Highlights:

  • Supports many databases: PostgreSQL, MySQL, SQLite, MongoDB, and more
  • Dual usage: CLI tool and Go library
  • Version-friendly: Migration files are numbered and clear
  • Bi-directional: Supports both applying (up) and rolling back (down) changes
  • Trusted in production: Used by many real-world systems

In this tutorial, we'll explore how to use golang-migrate with SQLite, a lightweight, file-based database perfect for development and small applications.

Prerequisites

Before diving in, you’ll need:

  • Go (1.16+)
  • Basic SQL skills
  • Familiarity with the command line

Migration Best Practices

Following best practices ensures clean and manageable migrations:

1. Naming Conventions

  • Use sequential numbering: 001, 002, etc.
  • Be descriptive: create_users_table
  • Use lowercase and underscores: 001_create_users_table.up.sql

2. Keep Migrations Focused

  • One logical change per migration
  • Keep files small and maintainable
  • Always provide both up and down scripts
  • Make them idempotent (safe to re-run)

3. Version Control

  • Always commit migrations
  • Never change migrations already applied in production
  • Create new migrations for every change

4. Testing

  • Test both directions: up and down
  • Use realistic sample data
  • Check constraints and indexes

5. Deployment Tips

  • Back up the database before applying
  • Apply migrations during low-traffic periods
  • Always test in staging before production

Project Setup 

mkdir go-sqlite-migrations
cd go-sqlite-migrations
go mod init example.com/go-sqlite-migrations

Install golang-migrate

macOS:

brew install golang-migrate

Windows (Scoop):

scoop install migrate

Linux:

curl -L https://github.com/golang-migrate/migrate/releases/download/v4.16.2/migrate.linux-amd64.tar.gz | tar xvz
mv migrate.linux-amd64 /usr/local/bin/migrate

Directory Structure 

go-sqlite-migrations/
├── db/
│   └── migrations/
├── go.mod
└── main.go

Creating Migrations 

migrate create -ext sql -dir db/migrations -seq create_users_table
migrate create -ext sql -dir db/migrations -seq create_posts_table

This creates:

  • 000001_create_users_table.up.sql
  • 000001_create_users_table.down.sql
  • 000002_create_posts_table.up.sql
  • 000002_create_posts_table.down.sql

Example SQL 

-- 000001_create_users_table.up.sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 000001_create_users_table.down.sql
DROP TABLE users;

-- 000002_create_posts_table.up.sql
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 000002_create_posts_table.down.sql
DROP TABLE posts;

Running Migrations

Initialize your database:

touch blog.db

Apply migrations:

migrate -database "sqlite3://blog.db" -path db/migrations up

Rollback:

# Roll back one migration
migrate -database "sqlite3://blog.db" -path db/migrations down 1

# Roll back all migrations
migrate -database "sqlite3://blog.db" -path db/migrations down

Programmatic Migration in Go 

package main

import (
    "database/sql"
    "log"

    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/sqlite3"
    _ "github.com/golang-migrate/migrate/v4/source/file"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    db, err := sql.Open("sqlite3", "blog.db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    driver, err := sqlite3.WithInstance(db, &sqlite3.Config{})
    if err != nil {
        log.Fatal(err)
    }

    m, err := migrate.NewWithDatabaseInstance("file://db/migrations", "sqlite3", driver)
    if err != nil {
        log.Fatal(err)
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        log.Fatal(err)
    }
}

Install required packages:

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

Useful Commands 

# Create a new migration
migrate create -ext sql -dir db/migrations -seq migration_name

# Apply all migrations
migrate -database "sqlite3://blog.db" -path db/migrations up

# Roll back
migrate -database "sqlite3://blog.db" -path db/migrations down 1

# Force a specific version
migrate -database "sqlite3://blog.db" -path db/migrations force VERSION

# Check current version
migrate -database "sqlite3://blog.db" -path db/migrations version

Common Issues

Dirty Database State 

migrate -database "sqlite3://blog.db" -path db/migrations force VERSION

Failed Migrations

  1. Inspect the error
  2. Fix the issue
  3. Force the correct version
  4. Retry the migration

Advanced Tips

Custom Driver Example 

type CustomDriver struct {
    db *sql.DB
}

func (d *CustomDriver) Up(migration string) error {
    // Implement your own logic
    return nil
}

Transactions 

func (d *CustomDriver) WithTransaction(fn func(*sql.Tx) error) error {
    tx, err := d.db.Begin()
    if err != nil {
        return err
    }

    if err := fn(tx); err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit()
}

Final Thoughts

Migrations bring stability and structure to database changes. golang-migrate with SQLite is a great way to start managing migrations effectively in Go.

Key Takeaways:

  • Write clean, versioned migrations
  • Test everything before deployment
  • Keep changes atomic and reversible
  • Automate where possible
  • Use version control and backups

Resources

Was this helpful? Follow for more practical Go tutorials and tips!