miglite

miglite - Lite database schema migration tool by Go

👉 中文说明

miglite is a minimalist database schema migration tool implemented in Golang.

• Easy to use with minimal dependencies
• Developed based on database/sql without adding any driver dependencies by default
• Migration SQL is executed within transactions to ensure data consistency
• Uses raw SQL files as migration files
  • SQL filename format: YYYYMMDD-NNNNNN-{migration-name}.sql
  • The YYYYMMDD date must be valid; the next 6 digits only need to be comparable numbers
• By default, all SQL files (including subdirectories) in the migration directory are recursively searched
  • Directories starting with _ (eg. _backup/xx.sql) are ignored when looking for SQL files
  • Migration directories support the use of environment variables (eg .migrations/${MODULE_NAME})
  • Migration directories support adding multiple paths using comma , splitting
• Can run migrations with zero configuration via environment variables (e.g., DATABASE_URL, MIGRATIONS_PATH)
  • Automatically attempts to load .env file in the directory(Optional)
  • Automatically loads default configuration file ./miglite.yaml(Optional)
• Built-in support for executing SQL statements via miglite exec, convenient for debugging and testing
• Supports mysql, sqlite, postgres databases
  • When used as a library, you need to add your own DB driver dependencies
  • When using the miglite command-line tool directly, driver dependencies are already included

Installation

Using the miglite command-line tool:

# install it by go
go install github.com/gookit/miglite/cmd/miglite@latest

Using as a Go dependency library:

go get github.com/gookit/miglite

# import "github.com/gookit/miglite"

Direct CLI Usage

Using the miglite command-line tool directly.

Commands:

  create, new                 Create new migration SQL files
  down, rollback              Rollback the most recent migration
  exec, execute, run-sql      Execute SQL statement or SQL file directly
  init                        Initialize the migration schema on database
  show, info, describe        Show database information like tables or table schema
  skip, ignore                Manual skip one or multi migration file(s)
  status, st                  Show the status of migrations
  up, migrate, run            Execute pending migrations
  help                        Display application help

Configuration

miglite supports configuration via miglite.yaml file or environment variables.

• Can work without a configuration file, using the environment variable DATABASE_URL directly
• Configuration file defaults to ./miglite.yaml, but can be specified via the --config parameter

miglite.yaml Example

database:
  driver: sqlite  # or mysql, postgresql
  dsn: ./miglite.db  # or connection string for other databases
migrations:
  path: ./migrations

Environment Variables

• MIGRATIONS_PATH: Migration files directory path (default: ./migrations)
  • Supports using environment variables (eg ./migrations/${MODULE_NAME})
  • Supports adding multiple paths using comma , splitting
• DATABASE_URL: Database connection URL (e.g., sqlite://path/to/your.db, mysql://user:pass@tcp(host:port)/dbname)
• MIGLITE_ENV_PREFIX: Environment variable prefix, default is empty string
  • After setting, environment variables will be prefixed with the prefix, eg. MIGLITE_ENV_PREFIX=MY_, then read MY_DATABASE_URL instead of DATABASE_URL
  • All environment variables will be affected by the prefix setting

Examples:

MIGRATIONS_PATH = "./migrations"
# sqlite
DATABASE_URL="sqlite://path/to/your.db"
# mysql
DATABASE_URL="mysql://user:passwd@tcp(127.0.0.1:3306)/local_test?charset=utf8mb4&parseTime=True&loc=Local"
# postgresql
DATABASE_URL="postgres://host=localhost port=5432 user=username password=password dbname=dbname sslmode=disable"

NOTE: mysql DSNs must be tagged with the 'tcp(...)' protocol. Otherwise, it will throw an error.

Creating Migrations

miglite create add-users-table

This will create an SQL file named with the current timestamp in the ./migrations/ directory, with the format YYYYMMDD-HHMMSS-add-users-table.sql.

./migrations/20251105-102325-create-users-table.sql

SQL file content includes a template:

-- Migrate:UP
-- Add migration SQL here

-- Migrate:DOWN
-- Add rollback SQL here (optional)

Example migration file:

-- Migrate:UP
CREATE TABLE post (
  id int NOT NULL,
  title text,
  body text,
  PRIMARY KEY(id)
);

-- Migrate:DOWN
DROP TABLE post;

Running Migrations

# Initialize the migrations schema table
miglite init

# Apply all pending migrations
miglite up
# Execute immediately without confirmation
miglite up --yes

# Rollback the most recent migration
miglite down
# Rollback multiple migrations
miglite down --number 3

# View migration status
miglite status

View migration status:

Using as a Library

miglite does not depend on any third-party DB driver libraries by itself, so you can use it as a library with your current database driver library.

• Sqlite drivers:
  • modernc.org/sqlite CGO-free driver
  • github.com/glebarez/go-sqlite Based on modernc.org/sqlite
  • github.com/ncruces/go-sqlite3 CGO-free Based on Wasm(wazero)
  • github.com/mattn/go-sqlite3 NEED cgo
• MySQL driver:
  • github.com/go-sql-driver/mysql
• Postgres driver:
  • github.com/lib/pq
  • github.com/jackc/pgx/v5
• MSSQL driver:
  • github.com/microsoft/go-mssqldb

More drivers see: https://go.dev/wiki/SQLDrivers

package main

import (
  "github.com/gookit/miglite"

  // add your database driver
  _ "github.com/go-sql-driver/mysql"
  // _ "github.com/lib/pq"
  // _ "modernc.org/sqlite"
)

func main() {
  mig, err := miglite.NewAuto(func(cfg *config.Config) {
    // update config options
  })
  goutil.PanicIfErr(err) // handle error

  // run up migrations
  err = mig.Up(command.UpOption{
    Yes: true, // dont confirm
    // ... options
  })
  goutil.PanicIfErr(err) // handle error

  // run down migrations ...
}

Building Your Own Command Tool

You can directly use the miglite library to quickly build your own migration command tool, allowing you to register only the database drivers you need.

package main

import (
	"github.com/gookit/miglite"
	"github.com/gookit/miglite/pkg/command"

	// add your database driver
	_ "github.com/go-sql-driver/mysql"
	// _ "github.com/lib/pq"
	// _ "modernc.org/sqlite"
)

var Version = "0.1.0"

func main() {
	// Optional: Information needs to be specified at build time via ldflags
	// command.SetBuildInfo(Version, GoVersion, BuildTime, GitCommit)

	// Create the CLI application
	app := command.NewApp("miglite", Version, "Lite database schema migration tool by Go")

	// Run the application
	app.Run()
}

NOTE: If you want to further customize the CLI application, you can freely choose other CLI libraries, parse options, and then call the handleXXX() methods under command to execute the logic.

Related Projects

• golang-migrate
• pressly/goose
• amacneil/dbmate