migrator

README

Migrator

Production-ready database migration tool for Go with shadow database testing and transaction safety.

Features

• 🔒 Transaction-Safe: All migrations run in transactions with automatic rollback on failure
• 🧪 Shadow Database Testing: Test migrations on a shadow database before applying to production
• ✅ Validation: Ensures migration consistency between database and filesystem
• 📊 PostgreSQL Support: Built specifically for PostgreSQL with proper connection handling
• 🎯 Simple API: Clean, easy-to-use interface
• 📝 Detailed Logging: Clear output showing migration progress
• 🔄 Idempotent: Safe to run multiple times
• ⚡ Context Support: Proper context handling with timeout support
• 🧩 Modular Architecture: Clean internal package structure

Installation

# Latest stable version (recommended)
go get github.com/hasirciogluhq/migrator@latest

# Specific version
go get github.com/hasirciogluhq/migrator@v1.0.0

Version Policy:

• 🤖 Fully automated releases - Every push to main triggers CI/CD
• ✅ Test-protected - Only releases if all tests pass
• 📦 Semantic versioning - Auto-calculated from commit messages
• 🔄 Always safe - Failed tests = no release, use previous version
• 📖 See RELEASE.md for details

🚀 Automatic Releases

This package uses fully automated CI/CD. Just push to main:

# Bug fix (v1.0.0 → v1.0.1)
git commit -m "[*] Fixed migration bug"
git push origin main

# New feature (v1.0.0 → v1.1.0)  
git commit -m "[+] Added rollback support"
git push origin main

# Breaking change (v1.0.0 → v2.0.0)
git commit -m "[MAJOR] Changed API signature"
git push origin main

✅ Tests pass → Automatic release
❌ Tests fail → No release, safe!

See RELEASE.md for commit message format.

Quick Start

1. Create your migrations directory

mkdir migrations

2. Create migration files

Create SQL files in your migrations directory. Files are executed in alphabetical order.

migrations/001_create_users.sql:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

migrations/002_create_posts.sql:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

3. Run migrations in your application

package main

import (
    "context"
    "database/sql"
    "log"
    
    _ "github.com/lib/pq"
    "github.com/hasirciogluhq/migrator"
)

func main() {
    // Connect to your database
    db, err := sql.Open("postgres", 
        "postgres://user:password@localhost:5432/mydb?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create migrator
    m := migrator.New(db)

    // Run migrations
    if err := m.Migrate(context.Background()); err != nil {
        log.Fatal(err)
    }

    log.Println("Migrations completed successfully!")
}

Configuration

Environment Variables

• DATABASE_URL: PostgreSQL connection string (optional, fallback for shadow database operations)
• MIGRATIONS_PATH: Path to migrations directory (default: ./migrations)

Custom Configuration

m := migrator.NewWithOptions(db, migrator.Options{
    MigrationsPath: "./db/migrations",
    DatabaseURL:    "postgres://user:pass@localhost:5432/mydb", // Optional, for shadow DB testing
    SkipShadowDB:   false, // Set to true to skip shadow database testing
})

Shadow Database Testing: Shadow database testing requires a database URL to create temporary test databases. You can provide it in two ways:

1. Recommended (Production): Pass it in Options.DatabaseURL
2. Fallback: Set DATABASE_URL environment variable

If neither is provided, shadow database testing will be skipped with a warning.

How It Works

The migrator follows a robust, multi-step process:

1. Ensure Tracking Table: Creates _go_migrations table if it doesn't exist
2. Validate Existing Migrations: Verifies all applied migrations still exist in filesystem
3. Load Migration Files: Reads all .sql files from migrations directory
4. Shadow Database Testing:
   • Creates a temporary shadow database
   • Applies existing migrations to shadow database
   • Tests new migrations on shadow database
   • Drops shadow database after testing
5. Apply to Production: Applies pending migrations to production database
6. Cleanup: Ensures shadow database is removed

Transaction Safety

Each migration runs in its own transaction:

• ✅ If successful: Changes are committed and migration is recorded
• ❌ If failed: Changes are rolled back and migration is not recorded

Shadow Database Testing

Before applying to production, new migrations are tested on a shadow database:

your_database           → Production database (untouched during testing)
your_database_gi_mig_shadow_db → Temporary shadow database (created, tested, dropped)

This ensures:

• Syntax errors are caught before production
• Migrations are compatible with existing schema
• No surprises in production deployment

API Reference

Core Functions

New(db *sql.DB) *Migrator

Creates a new migrator with default options.

m := migrator.New(db)

NewWithOptions(db *sql.DB, opts Options) *Migrator

Creates a new migrator with custom options.

m := migrator.NewWithOptions(db, migrator.Options{
    MigrationsPath: "./custom/path",
    SkipShadowDB:   false,
})

Migrate(ctx context.Context) error

Runs the complete migration process.

if err := m.Migrate(context.Background()); err != nil {
    log.Fatal(err)
}

GetAppliedMigrations(ctx context.Context) ([]string, error)

Returns a list of all applied migration names.

applied, err := m.GetAppliedMigrations(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Println("Applied migrations:", applied)

GetPendingMigrations(ctx context.Context) ([]*validator.MigrationFile, error)

Returns a list of migrations that haven't been applied yet.

pending, err := m.GetPendingMigrations(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Pending migrations: %d\n", len(pending))

Examples

Basic Usage

See examples/basic/main.go for a complete example.

Custom Migrations Path

os.Setenv("MIGRATIONS_PATH", "./db/migrations")
m := migrator.New(db)
m.Migrate(context.Background())

With Context Timeout

ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
defer cancel()

if err := m.Migrate(ctx); err != nil {
    log.Fatal(err)
}

Check Migration Status

m := migrator.New(db)

// Get applied migrations
applied, err := m.GetAppliedMigrations(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Applied: %v\n", applied)

// Get pending migrations
pending, err := m.GetPendingMigrations(context.Background())
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Pending: %d migrations\n", len(pending))

Best Practices

Migration File Naming

Use a consistent naming convention:

001_create_users.sql
002_create_posts.sql
003_add_user_avatar.sql

Migration Content

1. Be Explicit: Always specify column types, constraints, and defaults
2. Use Transactions: Each file runs in a transaction, so keep related changes together
3. Add Indexes: Create indexes in the same migration as the table
4. Handle Data: You can include INSERT/UPDATE statements for seed data

Production Deployment

1. Test Locally: Always test migrations locally first
2. Backup: Take database backups before running migrations in production
3. Monitor: Watch logs during migration execution
4. Rollback Plan: Have a rollback strategy for each migration

Do's and Don'ts

✅ Do:

• Keep migrations small and focused
• Test migrations thoroughly
• Use descriptive file names
• Version control your migrations
• Run migrations during deployment automation

❌ Don't:

• Modify already-applied migrations
• Delete migration files that have been applied
• Include DROP statements without careful consideration
• Skip shadow database testing in production

Testing

The package includes comprehensive tests with 11 test scenarios covering:

• ✅ Basic migration flow
• ✅ Idempotent migrations
• ✅ Transaction rollback on failure
• ✅ Incremental migrations
• ✅ Missing file detection
• ✅ Complex multi-statement migrations
• ✅ Concurrent migrations
• ✅ Context cancellation

Quick Testing with Make

The easiest way to run tests:

# Run tests with Docker (automatically starts/stops PostgreSQL)
make test-docker

# Run tests with existing PostgreSQL
make test

# Run tests with coverage report
make test-coverage

# See all available commands
make help

Local Testing

Option 1: Using Docker (Recommended)

# Start PostgreSQL
docker run -d --name migrator-test \
  -e POSTGRES_PASSWORD=postgres \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=postgres \
  -p 5432:5432 \
  postgres

# Wait for PostgreSQL to be ready
sleep 3

# Set connection string and run tests
export DATABASE_URL="postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
go test -v ./...

# Cleanup
docker stop migrator-test && docker rm migrator-test

Option 2: Using Existing PostgreSQL

# Set your PostgreSQL connection string
export DATABASE_URL="postgres://YOUR_USER:YOUR_PASSWORD@localhost:5432/postgres?sslmode=disable"

# Run tests
go test -v ./...

Run with Coverage:

go test -v -race -coverprofile=coverage.txt -covermode=atomic ./...
go tool cover -html=coverage.txt

CI/CD Testing

Tests automatically run on GitHub Actions on every push/PR. The workflow:

1. Spins up PostgreSQL 15 service
2. Runs go vet for code quality
3. Executes all tests with race detector
4. Uploads coverage to Codecov

See .github/workflows/test.yml for details.

Architecture

migrator/
├── migrator.go              # Public API
├── internal/
│   ├── tracker/             # Migration tracking & database operations
│   │   └── tracker.go
│   ├── validator/           # Migration validation & file handling
│   │   └── validator.go
│   └── shadowdb/           # Shadow database management
│       └── shadowdb.go
├── migrator_test.go        # Comprehensive test suite
└── examples/               # Usage examples

Troubleshooting

"DATABASE_URL environment variable not set" or Shadow DB Warning

If you see a warning about DATABASE_URL not being provided, you have two options:

Option 1 (Recommended): Pass the database URL explicitly in options:

m := migrator.NewWithOptions(db, migrator.Options{
    DatabaseURL: "postgres://user:password@localhost:5432/mydb",
})

Option 2: Set the DATABASE_URL environment variable:

export DATABASE_URL="postgres://user:password@localhost:5432/mydb"

Note: Shadow database testing will be skipped if no database URL is provided.

"Migration validation failed: X migrations missing from filesystem"

This means migrations that were previously applied have been deleted from your migrations directory. This is a safety check to prevent inconsistencies. You need to restore the missing migration files.

"Failed to drop shadow database"

The shadow database cleanup failed. You can manually drop it:

DROP DATABASE IF EXISTS your_database_gi_mig_shadow_db;

Migration hangs or times out

Each migration has a 5-minute timeout by default. If your migration needs more time, consider:

1. Breaking it into smaller migrations
2. Optimizing the SQL queries
3. Running heavy operations outside migration system

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

• Inspired by database migration tools like Flyway and golang-migrate
• Built with production reliability and developer experience in mind

Support

• 📫 Issues: GitHub Issues
• 💬 Discussions: GitHub Discussions

---

Made with ❤️ for the Go community

Documentation

Overview

Package migrator provides a production-ready database migration tool for PostgreSQL with shadow database testing.

Migrator is designed for production use with a unique approach: it tests your migrations on a temporary shadow database before applying them to production, catching errors before they affect your production system.

Core Features

• 🧪 Shadow Database Testing: Tests migrations on a throwaway database first
• 🔒 Transaction-Safe: All migrations run in transactions with automatic rollback
• ✅ Validation: Ensures migration consistency between database and filesystem
• 📊 PostgreSQL-Only: Built specifically for PostgreSQL, not a one-size-fits-all
• ⚡ Context Support: Proper context handling with timeout support (5min default)
• 🔄 Idempotent: Safe to run multiple times, skips already-applied migrations
• 🧩 Modular Architecture: Clean internal package structure (tracker/validator/shadowdb)
• 📝 Comprehensive Tests: 11 test scenarios covering edge cases

Quick Start

Create a migrations directory with SQL files:

migrations/
├── 001_create_users.sql
├── 002_create_posts.sql
└── 003_add_indexes.sql

Basic usage:

package main

import (
	"context"
	"database/sql"
	"log"

	_ "github.com/lib/pq"
	"github.com/hasirciogluhq/migrator"
)

func main() {
	db, _ := sql.Open("postgres", "postgres://user:pass@localhost/mydb")
	defer db.Close()

	// Simple approach (uses env vars for shadow DB if available)
	m := migrator.New(db)

	if err := m.Migrate(context.Background()); err != nil {
		log.Fatal(err)
	}

	log.Println("✅ Migrations completed successfully")
}

Production-Recommended Configuration

For production, explicitly pass the database URL rather than relying on environment variables. This makes configuration visible and testable:

m := migrator.NewWithOptions(db, migrator.Options{
	MigrationsPath: "./migrations",
	DatabaseURL:    "postgres://user:pass@localhost:5432/mydb",
})

// Run with timeout
ctx, cancel := context.WithTimeout(context.Background(), 10*time.Minute)
defer cancel()

if err := m.Migrate(ctx); err != nil {
	log.Fatal(err)
}

How Shadow Database Testing Works

This is what makes migrator unique. When you run Migrate():

1. Creates migrations tracking table (_go_migrations) if needed 2. Validates all previously applied migrations still exist in filesystem 3. Loads all migration files from the migrations directory 4. Identifies new (unapplied) migrations 5. Creates temporary shadow database (yourdb_gi_mig_shadow_db) 6. Applies ALL existing migrations to shadow database 7. Tests NEW migrations on shadow database 8. If shadow tests pass → applies new migrations to production 9. If shadow tests fail → aborts, alerts, cleans up shadow database 10. Cleans up shadow database after successful migration

This catches syntax errors, constraint violations, and type mismatches BEFORE they touch your production database. Even with transaction rollback, testing first prevents production error logs and potential downtime.

Example shadow database flow:

Your Production DB: myapp_production
Shadow Database:    myapp_production_gi_mig_shadow_db (temporary)

1. Shadow DB created
2. Existing migrations (001-005) applied to shadow
3. New migrations (006-007) tested on shadow
4. ✅ Tests pass → apply 006-007 to production
5. Shadow DB dropped automatically

Configuration Options

The Options struct provides flexible configuration:

type Options struct {
	// Path to directory containing .sql migration files
	// Default: "./migrations" or MIGRATIONS_PATH env var
	MigrationsPath string

	// PostgreSQL connection string for shadow database operations
	// Falls back to DATABASE_URL env var if not provided
	// Shadow DB testing is skipped if neither is available
	DatabaseURL string

	// Set to true to skip shadow database testing entirely
	// Not recommended for production deployments
	SkipShadowDB bool
}

Using NewWithOptions (recommended for production):

m := migrator.NewWithOptions(db, migrator.Options{
	MigrationsPath: "./db/migrations",
	DatabaseURL:    os.Getenv("DATABASE_URL"), // Explicit configuration
})

Using New (simple approach):

// Relies on MIGRATIONS_PATH and DATABASE_URL env vars
m := migrator.New(db)

Graceful Fallback: If DatabaseURL is not provided (neither in Options nor env var), the migrator will skip shadow database testing and log a warning. Migrations will still be applied to production, but without pre-testing.

Migration Files

Migration files are plain SQL. They should:

• Be named with numeric prefix for ordering (001_*.sql, 002_*.sql, etc.)
• Contain valid PostgreSQL SQL statements
• Be focused and atomic (one logical change per file)
• Never be modified or deleted after being applied to production

Example migration file (001_create_users.sql):

CREATE TABLE users (
	id SERIAL PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	email VARCHAR(255) UNIQUE NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

-- You can include data migrations too
INSERT INTO users (name, email) VALUES
	('System', 'system@example.com');

Transaction Safety

Each migration file runs in its own transaction with READ COMMITTED isolation:

BEGIN TRANSACTION;
  -- Execute all SQL in migration file
  -- Record migration in _go_migrations table
COMMIT;

If ANY statement fails, the entire migration is rolled back and NOT recorded as applied. The next time you run Migrate(), it will retry from the failed migration.

Checking Migration Status

Get applied migrations:

applied, err := m.GetAppliedMigrations(context.Background())
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Applied: %v\n", applied)

Get pending migrations:

pending, err := m.GetPendingMigrations(context.Background())
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Pending: %d migrations\n", len(pending))

Internal Architecture

The package is organized into focused internal modules:

• internal/tracker: Manages _go_migrations table and transactions
• internal/validator: Validates migration files and filesystem consistency
• internal/shadowdb: Creates, tests, and cleans up shadow databases

This separation ensures:

• Single responsibility per module
• Easy testing (11 comprehensive test scenarios)
• Clear error messages with proper context
• Maintainable codebase

Known Limitations

PostgreSQL enum operations require careful ordering. This is NOT a bug in migrator, but a PostgreSQL constraint that affects ALL migration tools:

-- ❌ WRONG ORDER - Will fail in production
ALTER TYPE status_enum DROP VALUE 'old_value';
UPDATE users SET status = 'new_value' WHERE status = 'old_value';

-- ✅ CORRECT ORDER
UPDATE users SET status = 'new_value' WHERE status = 'old_value';
ALTER TYPE status_enum DROP VALUE 'old_value';

Shadow database testing might pass the wrong order (no data in shadow DB), but production will fail (has rows with 'old_value'). This is true for Prisma, golang-migrate, and all migration tools. The solution: know your SQL, especially with enum operations.

Performance Considerations

Shadow database creation/destruction adds overhead (typically 1-3 seconds). This is negligible for production deployments but might matter in CI pipelines that run hundreds of times daily.

To skip shadow DB testing in specific environments:

m := migrator.NewWithOptions(db, migrator.Options{
	MigrationsPath: "./migrations",
	DatabaseURL:    "", // Empty = skips shadow DB testing
})

Not recommended for production deployments.

Testing

The package includes 11 comprehensive test scenarios:

• Basic migration flow
• Idempotent migrations (running multiple times safely)
• Transaction rollback on failure
• Incremental migrations
• Missing migration file detection
• Complex multi-statement migrations
• Concurrent migration attempts
• Context cancellation handling
• Shadow database testing with various scenarios
• Explicit configuration via Options
• Graceful fallback when DATABASE_URL not provided

All tests use real PostgreSQL databases (no mocks). If tests pass, it works in production.

Run tests:

make test-docker  # Starts PostgreSQL in Docker automatically
make test         # Uses existing PostgreSQL connection
make test-coverage # Generates coverage report

Best Practices

Migration File Naming:

• Use numeric prefix: 001_initial_schema.sql, 002_add_users.sql
• Keep names descriptive
• Never reorder or renumber after applying to production

Migration Content:

• Keep migrations small and focused
• Test locally before deploying
• Include both schema AND data changes if needed
• Add indexes in the same migration as table creation
• Be explicit with column types and constraints

Production Deployment:

• Always take database backup before migrations
• Use explicit DatabaseURL in Options (don't rely on env vars)
• Monitor logs during migration execution
• Have rollback plan ready (though transaction safety helps)
• Test migrations on staging environment first

Never Do:

• Modify migrations after applying to production
• Delete migration files that have been applied
• Skip shadow database testing in production
• Run migrations without proper timeout context

Automatic Releases

This package uses fully automated CI/CD. Every push to main:

• Runs all 11 test scenarios with PostgreSQL
• Calculates version from commit message ([*] = patch, [+] = minor, [MAJOR] = major)
• Creates git tag and GitHub release automatically
• Notifies pkg.go.dev for documentation updates

See RELEASE.md for details on commit message format.

Examples and Documentation

For complete examples, see:

• examples/basic/main.go - Full working example
• examples/basic/migrations/ - Sample migration files
• README.md - Comprehensive documentation
• RELEASE.md - Automated release process

For API documentation:

• https://pkg.go.dev/github.com/hasirciogluhq/migrator

For source code and issues:

• https://github.com/hasirciogluhq/migrator

License

MIT License - see LICENSE file for details.

Package migrator provides a production-ready database migration tool with shadow database testing.

Features:

• Transaction-safe migrations with automatic rollback
• Shadow database testing before production deployment
• Migration consistency validation
• PostgreSQL support with proper connection handling
• Detailed logging and error reporting

Usage:

import (
	"context"
	"database/sql"
	_ "github.com/lib/pq"
	"github.com/hasirciogluhq/migrator"
)

db, _ := sql.Open("postgres", "postgres://user:pass@localhost/dbname")
m := migrator.New(db)
if err := m.Migrate(context.Background()); err != nil {
	log.Fatal(err)
}

Index

• type Migrator
  • func New(db *sql.DB) *Migrator
  • func NewWithOptions(db *sql.DB, opts Options) *Migrator
  • func (m *Migrator) GetAppliedMigrations(ctx context.Context) ([]string, error)
  • func (m *Migrator) GetPendingMigrations(ctx context.Context) ([]*validator.MigrationFile, error)
  • func (m *Migrator) Migrate(ctx context.Context) error
• type Options

Types

type Migrator

type Migrator struct {
	// contains filtered or unexported fields
}

Migrator handles database migrations with shadow database testing.

func New

func New(db *sql.DB) *Migrator

New creates a new Migrator instance with default options.

The database connection should be properly configured and tested before passing it to the migrator. The migrator will use this connection for all migration operations.

func NewWithOptions

func NewWithOptions(db *sql.DB, opts Options) *Migrator

NewWithOptions creates a new Migrator instance with custom options.

func (*Migrator) GetAppliedMigrations

func (m *Migrator) GetAppliedMigrations(ctx context.Context) ([]string, error)

GetAppliedMigrations returns a list of all applied migration names. This is useful for debugging and verification purposes.

func (*Migrator) GetPendingMigrations

func (m *Migrator) GetPendingMigrations(ctx context.Context) ([]*validator.MigrationFile, error)

GetPendingMigrations returns a list of migrations that haven't been applied yet.

func (*Migrator) Migrate

func (m *Migrator) Migrate(ctx context.Context) error

Migrate runs the complete migration process with shadow database testing.

Process:

1. Ensure migrations tracking table exists
2. Validate existing migrations
3. Get all migration files
4. Test new migrations on shadow database
5. Apply pending migrations to production
6. Clean up shadow database

Returns an error if any step fails. All migrations are applied in transactions with automatic rollback on failure.

type Options

type Options struct {
	// MigrationsPath is the directory containing SQL migration files.
	// If empty, defaults to "./migrations" or MIGRATIONS_PATH env var.
	MigrationsPath string

	// DatabaseURL is the PostgreSQL connection string used for shadow database operations.
	// If empty, falls back to DATABASE_URL env var.
	// Required for shadow database testing feature.
	DatabaseURL string

	// SkipShadowDB disables shadow database testing.
	// Not recommended for production use.
	SkipShadowDB bool
}

Options configures the Migrator behavior.