repository·master·Indexed 25 days ago
https://github.com/rubenv/sql-migrateA SQL schema migration tool for Go that supports multiple database dialects including SQLite, PostgreSQL, MySQL, MSSQL, Oracle (oci8, godror), and Snowflake. It can be used as a standalone CLI or integrated into Go applications as a library. Features include support for Go embed, custom MigrationSource implementations, and compatibility with sqlx.
The sql-migrate library is compatible with sqlx. To use it, pass the underlying *sql.DB instance from your *sqlx.DB object to the migration functions by dereferencing the DB field.
n, err := migrate.Exec(db.DB, "sqlite3", migrations, migrate.Up)
// ^^^ <-- Here db is a *sqlx.DB, the db.DB field is the plain sql.DB
if err != nil {
// Handle errors!
}Migrations are SQL files using special comments to define Up and Down blocks. Files are sorted by name (use timestamps or increasing numbers).
Basic Syntax:
-- +migrate Up
CREATE TABLE people (id int);
-- +migrate Down
DROP TABLE people;Complex Statements:
Use -- +migrate StatementBegin and -- +migrate StatementEnd to wrap complex blocks containing semicolons.
Non-transactional Migrations:
To run a migration outside of a transaction (e.g., for CREATE INDEX CONCURRENTLY in Postgres), use the notransaction option:
-- +migrate Up notransaction
CREATE UNIQUE INDEX CONCURRENTLY people_unique_id_idx ON people (id);-- +migrate Up
CREATE TABLE people (id int);
-- +migrate StatementBegin
CREATE OR REPLACE FUNCTION do_something()
returns void AS $$
DECLARE
create_query text;
BEGIN
-- Do something here
END;
$$
language plpgsql;
-- +migrate StatementEnd
-- +migrate Down
DROP FUNCTION do_something();
DROP TABLE people;To create a self-contained binary, use the embed package to include migration files in your application. Use migrate.EmbedFileSystemMigrationSource to load them.
import (
"embed"
"github.com/rubenv/sql-migrate"
)
//go:embed migrations/*
var dbMigrations embed.FS
func setupMigrations() *migrate.EmbedFileSystemMigrationSource {
return &migrate.EmbedFileSystemMigrationSource{
FileSystem: dbMigrations,
Root: "migrations",
}
}Oracle support requires specific drivers and the Oracle Instant Client.
For oci8 driver:
go get -tags oracle -v github.com/rubenv/sql-migrate/...For godror driver:
go get -tags godror -v github.com/rubenv/sql-migrate/...LD_LIBRARY_PATH to your Instant Client path.# oci8
go get -tags oracle -v github.com/rubenv/sql-migrate/...
# godror
go get -tags godror -v github.com/rubenv/sql-migrate/...
# Example LD_LIBRARY_PATH setup
export LD_LIBRARY_PATH=your_oracle_office_path/instantclient_19_3go get. For Go version 1.18 or later, use go install to get the latest version.The configuration file defines environments with database connection details. You can use ${VAR} syntax in the datasource field to expand environment variables.
Configuration Keys:
dialect: The database driver (e.g., sqlite3, postgres, mysql, oci8, godror).datasource: Connection string.dir: Directory containing migration files.table: (Optional) The table used to track migrations. Defaults to gorp_migrations.development:
dialect: sqlite3
datasource: test.db
dir: migrations/sqlite3
production:
dialect: postgres
datasource: host=prodhost dbname=proddb user=${DB_USER} password=${DB_PASSWORD} sslmode=require
dir: migrations
table: migrationssql-migrate in your Go code, import the package, define a MigrationSource, and call migrate.Exec.You can extend sql-migrate by implementing the MigrationSource interface. This allows you to load migrations from any custom location (e.g., network, custom archive).
type MigrationSource interface {
FindMigrations() ([]*Migration, error)
}Use MigrationSet to define how migrations are stored and managed in the database. You can configure these settings globally using package-level functions or by using a MigrationSet instance for specific executions.
Key configuration options:
TableName: The name of the table used to store migration history (defaults to gorp_migrations).SchemaName: The database schema where the migration table resides.IgnoreUnknown: If true, skips the safety check that ensures all migrations present in the database are also present in your MigrationSource.DisableCreateTable: If true, prevents the library from attempting to create the migration table automatically.When using the mysql dialect, you must append ?parseTime=true to your datasource string to ensure proper time handling.
production:
dialect: mysql
datasource: root@/dbname?parseTime=true
dir: migrations/mysql
table: migrationsThe sql-migrate CLI allows you to manage database migrations. Each command requires a configuration file (defaults to dbconfig.yml, or use -config). You can specify the environment using the -env flag (defaults to development).
Available Commands:
up: Migrates the database to the most recent version.down: Undoes a migration (one by default).redo: Unapplies the last migration and reapplies it.new: Creates a new empty migration template (<current time>-<name>.sql).status: Shows the state of applied migrations.Common Flags for up:
-config=dbconfig.yml: Path to config file.-env="development": The environment to use.-limit=0: Limit number of migrations (0 = unlimited).-version: Run up to a specific version number.-dryrun: Print migrations without applying them.$ sql-migrate up --helpTo apply migrations to a database, use the Exec family of functions. These functions require a *sql.DB connection, a dialect string (e.g., "postgres", "mysql", "sqlite3"), a MigrationSource, and a MigrationDirection (Up or Down).
Available execution methods:
Exec(...): Applies all pending migrations.ExecContext(ctx, ...): Applies migrations using a provided context.Context.ExecMax(..., max int): Applies at most max migrations (use 0 for no limit).ExecVersion(..., version int64): Applies migrations up to a specific target version.Note: For MySQL, ensure the parseTime option is enabled in your DSN to avoid errors when mapping time columns.