migrate Documentation

File: README.md Copy Chapter

migrate

Database migrations written in Go. Use as CLI or import as library.

• Migrate reads migrations from sources
  and applies them in correct order to a database.
• Drivers are "dumb", migrate glues everything together and makes sure the logic is bulletproof.
  (Keeps the drivers lightweight, too.)
• Database drivers don't assume things or try to correct user input. When in doubt, fail.

Forked from mattes/migrate

Databases Copy Chapter

Database drivers run migrations. Add a new database?

• PostgreSQL
• PGX v4
• PGX v5
• Redshift
• Ql
• Cassandra / ScyllaDB
• SQLite
• SQLite3 (todo #165)
• SQLCipher
• MySQL / MariaDB
• Neo4j
• MongoDB
• CrateDB (todo #170)
• Shell (todo #171)
• Google Cloud Spanner
• CockroachDB
• YugabyteDB
• ClickHouse
• Firebird
• MS SQL Server
• rqlite

Database URLs Copy Section

Database connection strings are specified via URLs. The URL format is driver dependent but generally has the form: dbdriver://username:password@host:port/dbname?param1=true&param2=false

Any reserved URL characters need to be escaped. Note, the % character also needs to be escaped

Explicitly, the following characters need to be escaped:
!, #, $, %, &, ', (, ), *, +, ,, /, :, ;, =, ?, @, [, ]

It's easiest to always run the URL parts of your DB connection URL (e.g. username, password, etc) through an URL encoder. See the example Python snippets below:

$ python3 -c 'import urllib.parse; print(urllib.parse.quote(input("String to encode: "), ""))'
String to encode: FAKEpassword!#$%&'()*+,/:;=?@[]
FAKEpassword%21%23%24%25%26%27%28%29%2A%2B%2C%2F%3A%3B%3D%3F%40%5B%5D
$ python2 -c 'import urllib; print urllib.quote(raw_input("String to encode: "), "")'
String to encode: FAKEpassword!#$%&'()*+,/:;=?@[]
FAKEpassword%21%23%24%25%26%27%28%29%2A%2B%2C%2F%3A%3B%3D%3F%40%5B%5D
$

Migration Sources Copy Chapter

Source drivers read migrations from local or remote sources. Add a new source?

• Filesystem - read from filesystem
• io/fs - read from a Go io/fs
• Go-Bindata - read from embedded binary data (jteeuwen/go-bindata)
• pkger - read from embedded binary data (markbates/pkger)
• GitHub - read from remote GitHub repositories
• GitHub Enterprise - read from remote GitHub Enterprise repositories
• Bitbucket - read from remote Bitbucket repositories
• Gitlab - read from remote Gitlab repositories
• AWS S3 - read from Amazon Web Services S3
• Google Cloud Storage - read from Google Cloud Platform Storage

CLI usage Copy Chapter

• Simple wrapper around this library.
• Handles ctrl+c (SIGINT) gracefully.
• No config search paths, no config files, no magic ENV var injections.

CLI Documentation (includes CLI install instructions)

Basic usage Copy Section

$ migrate -source file://path/to/migrations -database postgres://localhost:5432/database up 2

Docker usage Copy Section

$ docker run -v {{ migration dir }}:/migrations --network host migrate/migrate
    -path=/migrations/ -database postgres://localhost:5432/database up 2

Use in your Go project Copy Chapter

• API is stable and frozen for this release (v3 & v4).
• Uses Go modules to manage dependencies.
• To help prevent database corruptions, it supports graceful stops via GracefulStop chan bool.
• Bring your own logger.
• Uses io.Reader streams internally for low memory overhead.
• Thread-safe and no goroutine leaks.

Go Documentation

import (
    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/github"
)

func main() {
    m, err := migrate.New(
        "github://mattes:personal-access-token@mattes/migrate_test",
        "postgres://localhost:5432/database?sslmode=enable")
    m.Steps(2)
}

Want to use an existing database client?

import (
    "database/sql"
    _ "github.com/lib/pq"
    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    db, err := sql.Open("postgres", "postgres://localhost:5432/database?sslmode=enable")
    driver, err := postgres.WithInstance(db, &postgres.Config{})
    m, err := migrate.NewWithDatabaseInstance(
        "file:///migrations",
        "postgres", driver)
    m.Up() // or m.Steps(2) if you want to explicitly set the number of migrations to run
}

Getting started Copy Chapter

Go to getting started

Tutorials Copy Chapter

• CockroachDB
• PostgreSQL

(more tutorials to come)

Migration files Copy Chapter

Each migration has an up and down migration. Why?

1481574547_create_users_table.up.sql
1481574547_create_users_table.down.sql

Best practices: How to write migrations.

Coming from another db migration tool? Copy Chapter

Check out migradaptor.
Note: migradaptor is not affiliated or supported by this project

Versions Copy Chapter

Development and Contributing Copy Chapter

Yes, please! Makefile is your friend,
read the development guide.

Also have a look at the FAQ.

Looking for alternatives? https://awesome-go.com/#database.

File: cmd/migrate/README.md Copy Chapter

migrate CLI

Installation Copy Chapter

Download pre-built binary (Windows, MacOS, or Linux) Copy Section

Release Downloads

$ curl -L https://github.com/golang-migrate/migrate/releases/download/$version/migrate.$os-$arch.tar.gz | tar xvz

MacOS Copy Section

$ brew install golang-migrate

Windows Copy Section

Using scoop

$ scoop install migrate

Linux (*.deb package) Copy Section

$ curl -fsSL https://packagecloud.io/golang-migrate/migrate/gpgkey | sudo gpg --dearmor -o /etc/apt/keyrings/migrate.gpg
$ echo "deb [signed-by=/etc/apt/keyrings/migrate.gpg] https://packagecloud.io/golang-migrate/migrate/ubuntu/ $(lsb_release -sc) main" > /etc/apt/sources.list.d/migrate.list
$ apt-get update
$ apt-get install -y migrate

With Go toolchain Copy Section

Versioned

$ go get -u -d github.com/golang-migrate/migrate/cmd/migrate
$ cd $GOPATH/src/github.com/golang-migrate/migrate/cmd/migrate
$ git checkout $TAG  # e.g. v4.1.0
$ # Go 1.15 and below
$ go build -tags 'postgres' -ldflags="-X main.Version=$(git describe --tags)" -o $GOPATH/bin/migrate $GOPATH/src/github.com/golang-migrate/migrate/cmd/migrate
$ # Go 1.16+
$ go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@$TAG

Unversioned

$ # Go 1.15 and below
$ go get -tags 'postgres' -u github.com/golang-migrate/migrate/cmd/migrate
$ # Go 1.16+
$ go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

Notes

1. Requires a version of Go that supports modules. e.g. Go 1.11+
2. These examples build the cli which will only work with postgres. In order
   to build the cli for use with other databases, replace the postgres build tag
   with the appropriate database tag(s) for the databases desired. The tags
   correspond to the names of the sub-packages underneath the
   database package.
3. Similarly to the database build tags, if you need to support other sources, use the appropriate build tag(s).
4. Support for build constraints will be removed in the future: https://github.com/golang-migrate/migrate/issues/60
5. For versions of Go 1.15 and lower, make sure you're not installing the migrate CLI from a module. e.g. there should not be any go.mod files in your current directory or any directory from your current directory to the root

Usage Copy Chapter

$ migrate -help
Usage: migrate OPTIONS COMMAND [arg...]
       migrate [ -version | -help ]

Options:
  -source          Location of the migrations (driver://url)
  -path            Shorthand for -source=file://path
  -database        Run migrations against this database (driver://url)
  -prefetch N      Number of migrations to load in advance before executing (default 10)
  -lock-timeout N  Allow N seconds to acquire database lock (default 15)
  -verbose         Print verbose logging
  -version         Print version
  -help            Print usage

Commands:
  create [-ext E] [-dir D] [-seq] [-digits N] [-format] [-tz] NAME
           Create a set of timestamped up/down migrations titled NAME, in directory D with extension E.
           Use -seq option to generate sequential up/down migrations with N digits.
           Use -format option to specify a Go time format string. Note: migrations with the same time cause "duplicate migration version" error.
           Use -tz option to specify the timezone that will be used when generating non-sequential migrations (defaults: UTC).

  goto V       Migrate to version V
  up [N]       Apply all or N up migrations
  down [N] [-all]    Apply all or N down migrations
        Use -all to apply all down migrations
  drop [-f]    Drop everything inside database
        Use -f to bypass confirmation
  force V      Set version V but don't run migration (ignores dirty state)
  version      Print current migration version

So let's say you want to run the first two migrations

$ migrate -source file://path/to/migrations -database postgres://localhost:5432/database up 2

If your migrations are hosted on github

$ migrate -source github://mattes:personal-access-token@mattes/migrate_test \
    -database postgres://localhost:5432/database down 2

The CLI will gracefully stop at a safe point when SIGINT (ctrl+c) is received.
Send SIGKILL for immediate halt.

Reading CLI arguments from somewhere else Copy Chapter

ENV variables Copy Section

$ migrate -database "$MY_MIGRATE_DATABASE"

JSON files Copy Section

Check out https://stedolan.github.io/jq/

$ migrate -database "$(cat config.json | jq -r '.database')"

YAML files Copy Section

$ migrate -database "$(cat config/database.yml | ruby -ryaml -e "print YAML.load(STDIN.read)['database']")"
$ migrate -database "$(cat config/database.yml | python -c 'import yaml,sys;print yaml.safe_load(sys.stdin)["database"]')"

File: database/cockroachdb/README.md Copy Chapter

cockroachdb

cockroachdb://user:password@host:port/dbname?query (cockroach://, and crdb-postgres:// work, too)

File: database/clickhouse/README.md Copy Chapter

ClickHouse

clickhouse://host:port?username=user&password=password&database=clicks&x-multi-statement=true

Notes Copy Chapter

• The Clickhouse driver does not natively support executing multiple statements in a single query. To allow for multiple statements in a single migration, you can use the x-multi-statement param. There are two important caveats:
  • This mode splits the migration text into separately-executed statements by a semi-colon ;. Thus x-multi-statement cannot be used when a statement in the migration contains a string with a semi-colon.
  • The queries are not executed in any sort of transaction/batch, meaning you are responsible for fixing partial migrations.
• Using the default TinyLog table engine for the schema_versions table prevents backing up the table if using the clickhouse-backup tool. If backing up the database with make sure the migrations are run with x-migrations-table-engine=MergeTree.
• Clickhouse cluster mode is not officially supported, since it's not tested right now, but you can try enabling schema_migrations table replication by specifying a x-cluster-name:
  • When x-cluster-name is specified, x-migrations-table-engine also should be specified. See the docs regarding replicated table engines.
  • When x-cluster-name is specified, only the schema_migrations table is replicated across the cluster. You still need to write your migrations so that the application tables are replicated within the cluster.
• If you want to create database inside the migration, you should know, that table which will manage migrations schema-migrations table will be in default table, so you can't use USE <database_name> inside migration. In this case you may not specify the database in the connection string (example you can find here)

File: database/mongodb/README.md Copy Chapter

MongoDB

• Driver work with mongo through db.runCommands
• Migrations support json format. It contains array of commands for db.runCommand. Every command is executed in separate request to database
• All keys have to be in quotes "
• Examples

Usage

mongodb://user:password@host:port/dbname?query (mongodb+srv:// also works, but behaves a bit differently. See docs for more information)

File: database/mysql/README.md Copy Chapter

MySQL

mysql://user:password@tcp(host:port)/dbname?query

Use with existing client Copy Chapter

If you use the MySQL driver with existing database client, you must create the client with parameter multiStatements=true:

package main

import (
    "database/sql"
    
    _ "github.com/go-sql-driver/mysql"
    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/mysql"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    db, _ := sql.Open("mysql", "user:password@tcp(host:port)/dbname?multiStatements=true")
    driver, _ := mysql.WithInstance(db, &mysql.Config{})
    m, _ := migrate.NewWithDatabaseInstance(
        "file:///migrations",
        "mysql", 
        driver,
    )
    
    m.Steps(2)
}

Upgrading from v1 Copy Chapter

1. Write down the current migration version from schema_migrations
2. DROP TABLE schema_migrations
3. Wrap your existing migrations in transactions (BEGIN/COMMIT) if you use multiple statements within one migration.
4. Download and install the latest migrate version.
5. Force the current migration version with migrate force <current_version>.