DEV Community

Cover image for Mastering Database Migrations in Go with golang-migrate and SQLite
Ouma Godwin
Ouma Godwin

Posted on

3

Mastering Database Migrations in Go with golang-migrate and SQLite

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
Enter fullscreen mode Exit fullscreen mode

Install golang-migrate

macOS:

brew install golang-migrate
Enter fullscreen mode Exit fullscreen mode

Windows (Scoop):

scoop install migrate
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Directory Structure

go-sqlite-migrations/
├── db/
│   └── migrations/
├── go.mod
└── main.go
Enter fullscreen mode Exit fullscreen mode

Creating Migrations

migrate create -ext sql -dir db/migrations -seq create_users_table
migrate create -ext sql -dir db/migrations -seq create_posts_table
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Running Migrations

Initialize your database:

touch blog.db
Enter fullscreen mode Exit fullscreen mode

Apply migrations:

migrate -database "sqlite3://blog.db" -path db/migrations up
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
    }
}
Enter fullscreen mode Exit fullscreen mode

Install required packages:

go get -u github.com/golang-migrate/migrate/v4
go get -u github.com/mattn/go-sqlite3
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Common Issues

Dirty Database State

migrate -database "sqlite3://blog.db" -path db/migrations force VERSION
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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()
}
Enter fullscreen mode Exit fullscreen mode

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!

Heroku

Built for developers, by developers.

Whether you're building a simple prototype or a business-critical product, Heroku's fully-managed platform gives you the simplest path to delivering apps quickly — using the tools and languages you already love!

Learn More

Top comments (0)

Billboard image

Try REST API Generation for MS SQL Server.

DevOps for Private APIs. With DreamFactory API Generation, you get:

  • Auto-generated live APIs mapped from database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Please consider leaving a ❤️ or a friendly comment if you found this post helpful!

Okay