dbmigrator

README

Go Database Migrator

Simple database migration system for Go.

Usage

Expected migration file structure

• Must contain a -- +up comment to indicate the SQL below should run when applying a migration.
• May contain a -- +down comment to indicate the SQL below should run when reverting a migration.
• Comments behind -- +up and -- +down are allowed.

-- +up  <- SQL below runs when applying a migration
CREATE TABLE demo_guestbook (
    id serial PRIMARY KEY,
    name varchar(255) NOT NULL,
    message text NOT NULL,
    created_at timestamp NOT NULL DEFAULT NOW()
);

-- +down <- SQL below runs when reverting a migration
DROP TABLE demo_guestbook;

Expected project structure

Your migration files must be named in the format 0001_initial_migration.sql where 0001 is the migration number and initial_migration is the name of the migration.

|-- main.go
|-- migrations
|   |-- 0001_initial_migration.sql
|   |-- 0002_second_migration.sql

Apply and Revert Migrations

import (
    "github.com/NotCoffee418/dbmigrator"
)

//go:embed all:migrations
var migrationFS embed.FS // `embed.FS` recommended but any `fs.FS` will work

func main() {
    // Define the query set for your database. Defaults to MySQL.
    // Must be called before any other dbmigrator functions.
    dbmigrator.SetDatabaseType(dbmigrator.PostgreSQL)
    // Or
    //dbmigrator.SetDatabaseType(dbmigrator.MySQL)
    //dbmigrator.SetDatabaseType(dbmigrator.SQLite)
    //dbmigrator.SetDatabaseType(dbmigrator.SQLServer)
    // Or define your own based on dbmigrator.MigrationQueryDefinition

    // Directory to migrations inside the FS
    migrationsDir := "migrations"

    // Migrations CLI (optional)
    if len(os.Args > 1) {
        // help           - Display this help message.
        // migrate up     - Apply all new database migrations.
        // migrate down   - Rollback a single database migration.`
        dbmigrator.HandleMigratorCommand(
            db *sql.DB, 
            migrationFS embed.FS,
            migrationsDir string, // Path to migrations dir in your fs 
            os.Args[1:] ...string)


    // Manage migrations programatically
    } else {
        // Apply all new migrations
        doneUp := <-dbmigrator.MigrateUpCh(
            db *sql.DB,
            migrations embed.FS,
            migrationsDir string)

        // Revert Single Migration
        doneDown := <-dbmigrator.MigrateDownCh(
            db *sql.DB,
            migrations embed.FS,
            migrationsDir string)
    }
}

Documentation

Index

• Variables
• func EnsureMigrationTableExistsCh(db *sql.DB) chan bool
• func GetHelpString() string
• func GetLiveMigrationInfoCh(db *sql.DB, migrationFs fs.FS, migrationDir string) chan MigrationState
• func HandleMigratorCommand(db *sql.DB, migrationFS fs.FS, migrationDir string, args ...string) bool
• func ListAvailableMigrationsCh(migrationFs fs.FS, path string) chan []migrationFileInfo
• func MigrateDownCh(db *sql.DB, migrationFs fs.FS, migrationDir string) chan bool
• func MigrateUpCh(db *sql.DB, migrationFs fs.FS, migrationDir string) chan bool
• func SetDatabaseType(querySet *MigrationQueryDefinition)
• type MigrationQueryDefinition
• type MigrationState
• type MigrationsTable

Variables

var MySQL = &MigrationQueryDefinition{
	CheckTableExists:       "SELECT EXISTS (SELECT * FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'migrations')",
	CreateMigrationsTable:  "CREATE TABLE migrations (version INT NOT NULL, installed_at TIMESTAMP NOT NULL)",
	InsertMigration:        "INSERT INTO migrations (version, installed_at) VALUES (?, ?)",
	DeleteMigration:        "DELETE FROM migrations WHERE version = ?",
	SelectInstalledVersion: "SELECT version FROM migrations ORDER BY version DESC LIMIT 1",
}

var PostgreSQL = &MigrationQueryDefinition{
	CheckTableExists:       "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'migrations')",
	CreateMigrationsTable:  "CREATE TABLE migrations (version INT NOT NULL, installed_at TIMESTAMP NOT NULL)",
	InsertMigration:        "INSERT INTO migrations (version, installed_at) VALUES ($1, $2)",
	DeleteMigration:        "DELETE FROM migrations WHERE version = $1",
	SelectInstalledVersion: "SELECT version FROM migrations ORDER BY version DESC LIMIT 1",
}

var SQLServer = &MigrationQueryDefinition{
	CheckTableExists:       "SELECT CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'migrations') THEN 1 ELSE 0 END",
	CreateMigrationsTable:  "CREATE TABLE migrations (version INT NOT NULL, installed_at DATETIME NOT NULL)",
	InsertMigration:        "INSERT INTO migrations (version, installed_at) VALUES (@p1, @p2)",
	DeleteMigration:        "DELETE FROM migrations WHERE version = @p1",
	SelectInstalledVersion: "SELECT TOP 1 version FROM migrations ORDER BY version DESC",
}

var SQLite = &MigrationQueryDefinition{
	CheckTableExists:       "SELECT EXISTS (SELECT name FROM sqlite_master WHERE type='table' AND name='migrations')",
	CreateMigrationsTable:  "CREATE TABLE migrations (version INT NOT NULL, installed_at TIMESTAMP NOT NULL)",
	InsertMigration:        "INSERT INTO migrations (version, installed_at) VALUES (?, ?)",
	DeleteMigration:        "DELETE FROM migrations WHERE version = ?",
	SelectInstalledVersion: "SELECT version FROM migrations ORDER BY version DESC LIMIT 1",
}

Functions

func EnsureMigrationTableExistsCh

func EnsureMigrationTableExistsCh(db *sql.DB) chan bool

func GetHelpString

func GetHelpString() string

func GetLiveMigrationInfoCh

func GetLiveMigrationInfoCh(db *sql.DB, migrationFs fs.FS, migrationDir string) chan MigrationState

GetLiveMigrationInfoCh returns the latest migration version and the installed migration version

func HandleMigratorCommand

func HandleMigratorCommand(
	db *sql.DB,
	migrationFS fs.FS,
	migrationDir string,
	args ...string) bool

HandleMigratorCommand is intended to be hooked into main.go to display help and migrate based on args for manual migrattions. This function is optional but can be used to as part of a CLI interface.

Param: db - database connection using sqlx

Param: migrationFS - ideally embed.FS based on a `migrations` with migration files structured as described in the documentation.

Param: migrationDir - directory to use for migrations inside the FS. 'migrations' is recommended.

Param: args - os.Args[1:] from main.go

Returns: boolean indicating if a command was actionable Not indicative of success or failure.

func ListAvailableMigrationsCh

func ListAvailableMigrationsCh(migrationFs fs.FS, path string) chan []migrationFileInfo

ListAvailableMigrationsCh returns a slice of all migration files in the migrations directory

func MigrateDownCh

func MigrateDownCh(db *sql.DB, migrationFs fs.FS, migrationDir string) chan bool

MigrateDownCh migrates the database down to the previous version

func MigrateUpCh

func MigrateUpCh(db *sql.DB, migrationFs fs.FS, migrationDir string) chan bool

MigrateUpCh migrates the database up to the latest version Returns: channel that will be closed when the migration is complete

func SetDatabaseType

func SetDatabaseType(querySet *MigrationQueryDefinition)

SetDatabaseType sets the active query set to use for migrations. This should be called before other dbmigrator functions when used, but is optional and defaults to MySQL.

MigrationQueries describes the queries used by the migrator. You can set up your own or use one of the defaults. Usage: dbmigrator.SetDatabaseType(dbmigrator.Postgres)

Types

type MigrationQueryDefinition

type MigrationQueryDefinition struct {
	CheckTableExists       string // Expect booly result
	CreateMigrationsTable  string
	InsertMigration        string
	DeleteMigration        string
	SelectInstalledVersion string
}

MigrationQueries describes the queries used by the migrator. These can be overridden if you want to use a different DB or table name.

type MigrationState

type MigrationState struct {
	AvailableVersion int
	InstalledVersion int
	Migrations       []migrationFileInfo
}

type MigrationsTable

type MigrationsTable struct {
	Version     int       `db:"version"`
	InstalledAt time.Time `db:"installed_at"`
}