sqlmigrator

SQLMigrator

A simple and intelligent SQL migration library for Go that makes database migrations effortless.

Introduction

Why SQLMigrator?

Traditional migration tools require you to write both UP and DOWN migrations, manage complex file naming conventions, and deal with verbose migration descriptions. SQLMigrator simplifies this process by:

• Smart Comment Removal: Automatically extracts clean SQL commands from your migration files, ignoring comments
• No UP/DOWN Required: Just write your SQL and go! No need for separate rollback files unless you want them
• Intelligent Descriptions: Automatically generates clean, readable migration descriptions
• Database Agnostic: Works with MySQL, PostgreSQL, SQLite, and any database supported by the darwin library
• Embed.FS Support: Perfect integration with Go 1.16+ embed.FS for bundling migrations into your binary

Getting Started

Installation

go get github.com/diegoclair/sqlmigrator

Basic Usage

1. Create your migration files:

-- 000001_create_users_table.sql
-- Create users table for authentication
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. Embed your migrations:

package main

import (
    "embed"
    "database/sql"
    "log"
    
    "github.com/diegoclair/sqlmigrator"
    "github.com/GuiaBolso/darwin"
    _ "github.com/go-sql-driver/mysql"
)

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

func main() {
    // Connect to your database
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create migrator and run migrations
    migrator := sqlmigrator.New(db, darwin.MySQLDialect{})
    
    err = migrator.Migrate(migrationFiles, "migrations")
    if err != nil {
        log.Fatal("Migration failed:", err)
    }

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

That's it! No complex setup, no UP/DOWN files required.

Features

🧠 Smart Comment Processing

SQLMigrator intelligently removes SQL comments while preserving your actual SQL commands:

-- This comment will be ignored
-- Another comment line
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
); -- This comment is also ignored

Result: Clean migration description: CREATE TABLE users

🎯 Intelligent Descriptions

Instead of generic descriptions, SQLMigrator extracts meaningful information:

🔧 Flexible Configuration with Options Pattern

// Basic usage
err := migrator.Migrate(migrationFiles, "migrations")

// With custom description processor
err := migrator.Migrate(migrationFiles, "migrations",
    sqlmigrator.WithDescriptionProcessor(func(filename, instruction string) string {
        return "Custom: " + sqlmigrator.ExtractActionDescription(instruction)
    }),
)

🗄️ Multiple Database Support

// MySQL
migrator := sqlmigrator.New(mysqlDB, darwin.MySQLDialect{})

// PostgreSQL  
migrator := sqlmigrator.New(postgresDB, darwin.PostgresDialect{})

// SQLite
migrator := sqlmigrator.New(sqliteDB, darwin.SqliteDialect{})

📁 Flexible File Organization

your-project/
├── migrations/
│   ├── 000001_create_users.sql
│   ├── 000002_add_user_roles.sql
│   └── 000003_create_posts.sql
├── main.go
└── go.mod

Advanced Examples

Custom Processing with Options

migrator := sqlmigrator.New(db, darwin.MySQLDialect{})

// Multiple options can be chained
err := migrator.Migrate(migrationFiles, "migrations",
    sqlmigrator.WithDescriptionProcessor(func(filename, instruction string) string {
        // Extract just the command type
        parts := strings.Fields(sqlmigrator.ExtractActionDescription(instruction))
        if len(parts) >= 2 {
            return parts[0] + " " + parts[1] // e.g., "CREATE TABLE"
        }
        return parts[0] // e.g., "INSERT"
    }),
    // Future options can be added here
    // sqlmigrator.WithTimeout(30*time.Second),
    // sqlmigrator.WithRetry(3),
)

Error Handling

migrator := sqlmigrator.New(db, darwin.MySQLDialect{})

err := migrator.Migrate(migrationFiles, "migrations")
if err != nil {
    if strings.Contains(err.Error(), "syntax error") {
        log.Fatal("SQL syntax error in migration files")
    }
    log.Fatal("Migration failed:", err)
}

API Reference

New(db *sql.DB, dialect darwin.Dialect) *Migrator

Creates a new migrator instance.

Migrate(sqlFiles embed.FS, sqlDir string, opts ...Option) error

Executes migrations with optional configuration using the options pattern.

WithDescriptionProcessor(processor func(filename, instruction string) string) Option

Option to customize how migration descriptions are generated from SQL instructions.

ExtractActionDescription(instruction string) string

Utility function to extract clean SQL command descriptions (exported for custom processors).

Contributing

We welcome contributions! Here's how you can help:

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

Development Setup

git clone https://github.com/diegoclair/sqlmigrator
cd sqlmigrator
go mod tidy
go test ./...

Running Tests

# Run all tests
go test ./...

# Run tests with coverage
go test -cover ./...

# Run tests with verbose output
go test -v ./...

TODO

• Add support for rollback migrations
• CLI tool for migration generation
• Integration with popular ORM libraries
• Migration status reporting
• Dry-run mode
• Schema versioning
• Migration dependencies

License

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

Acknowledgments

• Built on top of the excellent darwin migration library
• Inspired by the need for simpler, more intelligent database migrations
• Thanks to the Go community for feedback and contributions

Made with ❤️ by Diego Clair