Go Migrate Tutorial

1. Overview of Go Migrate

Go Migrate is a tool for managing database migration written in Go language. It can be used as a command-line interface (CLI) or imported as a library for Go projects. Go Migrate can read migration files from different sources and apply them to the database in the correct order. It supports various database drivers and migration sources.

2. Versions

Go Migrate supports multiple versions, including:

  • Master: The latest version, including new features and bug fixes
  • v4: Stable version, suitable for production environments
  • v3: No longer supported and should not be used

3. Installation

To use Go Migrate, you need to install the Go package. Run the following command for installation:

go get -u -d github.com/golang-migrate/migrate/v4
cd $GOPATH/src/github.com/golang-migrate/migrate/v4

3. Using Go Migrate

Go Migrate can be used through the CLI or as a library in the Go project.

3.0. Database Connection URLs

Whether using the CLI or Go code, database URLs need to be configured for connecting to the database.

URL format:

dbdriver://username:password@host:port/dbname?param1=true&param2=false

This URL is a database connection URL used to connect to the database and specify connection parameters. Here is an explanation for each part:

  1. dbdriver://: This is the protocol identifier for the database driver used to specify which database driver to use, e.g., mysql.
  2. username:password: This is the username and password used for authentication. Typically, the username and password are separated by a colon (:).
  3. @: This symbol is used to separate the username and password from the hostname and port.
  4. host:port: This is the hostname and port number of the database server. The hostname is the IP address or domain name of the database server, and the port number is the port the database server is listening on.
  5. /dbname: This is the name of the database to connect to.
  6. ?param1=true&param2=false: This part is the query parameters used to specify additional connection parameters. In this example, there are two parameters.

These query parameters can be set according to the specific requirements of the database driver and the database server to configure specific attributes or behaviors of the connection.

Postgres Connection Parameters

postgres://postgres:password@localhost:5432/example?sslmode=disable

SQLite Connection Parameters

sqlite3://path/to/database?query

MongoDB Connection Parameters

mongodb://user:password@host:port/dbname?query

3.1 CLI Usage

3.1.1 Basic Usage

To use the CLI, run the following command:

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

This command applies migrations from the specified source to the given database. The number "2" indicates the number of migrations to apply.

3.1.2 Docker Usage

Go Migrate can also be used with Docker. Run the following command:

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

This command runs Go Migrate in a Docker container and applies migrations from the specified source to the given database.

3.2 Using in Your Go Project

To use Go Migrate in your Go project, you need to import the required packages and libraries. Here is an example:

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)
}

This code initializes Go Migrate with the specified source and database, then applies 2 migrations using the Steps method.

If you want to use an existing database client, please refer to the following example:

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()
}

This code imports the required packages and initializes the database client with the sql package. Then it creates a new Go Migrate instance using the NewWithDatabaseInstance method, specifying the source and database driver. Finally, it applies the migration using the Up method.

4. Supported Database Drivers

Go Migrate supports multiple database drivers including:

  • PostgreSQL
  • PGX v4
  • PGX v5
  • Redshift
  • Ql
  • Cassandra
  • SQLite
  • SQLite3
  • SQLCipher
  • MySQL/MariaDB
  • Neo4j
  • MongoDB
  • CrateDB
  • Shell
  • Google Cloud Spanner
  • CockroachDB
  • YugabyteDB
  • ClickHouse
  • Firebird
  • MS SQL Server

5. Supported Migration Sources

Go Migrate supports multiple migration sources including:

  • File System: Read migrations from the local file system.
  • io/fs: Read migrations using the Go io/fs package.
  • Go-Bindata: Read migrations from embedded binary data using the jteeuwen/go-bindata package.
  • pkger: Read migrations from embedded binary data using the markbates/pkger package.
  • GitHub: Read migrations from a remote GitHub repository.
  • GitHub Enterprise: Read migrations from a remote GitHub Enterprise repository.
  • Bitbucket: Read migrations from a remote Bitbucket repository.
  • Gitlab: Read migrations from a remote Gitlab repository.
  • AWS S3: Read migrations from Amazon Web Services S3.
  • Google Cloud Storage: Read migrations from Google Cloud Platform Storage.

Depending on your project requirements, you can store the database migration files in the supported file storage sources mentioned above.

6. Migration Files

Migration files in Go Migrate have specific file name and content formats.

6.1 Migration File Name Format

Each migration consists of an "up" migration file and a corresponding "down" migration file. The migration file names should adhere to the following format:

{version}_{title}.up.{extension}
{version}_{title}.down.{extension}

version is a unique number representing the order in which the migrations should be applied. title is an optional description of the migration. extension depends on the database system being used (e.g., for SQL variants, use .sql).

6.2 Migration Content Format

The content of migration files varies depending on the database system, usually it involves directly writing SQL.

For example, the following are two migration files:

  • 000001_create_users_table.up.sql
  • 000001_create_users_table.down.sql

Migration file 000001_create_users_table.up.sql

CREATE TABLE IF NOT EXISTS users(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (300) UNIQUE NOT NULL
);

Migration file 000001_create_users_table.down.sql, the down file is mainly used to undo the previous operations, typically for rollback purposes.

DROP TABLE IF EXISTS users;

6.3 Reversibility of Migration

Writing reversible migrations is considered best practice. This means that the up and down migrations should be able to run to any version, effectively recreating and cleaning up the database state.

To ensure reversibility, each migration should have a corresponding "up" and "down" migration file. The "up" migration file contains the operations to apply the migration, while the "down" migration file contains the operations to undo the migration.

7. Usage of MySQL

Go Migrate provides support for MySQL databases. To connect to a MySQL database, the database URL needs to be in the following format:

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

The URL can include query parameters, such as the migration table name, TLS parameters, etc. For a complete list of query parameters, please refer to the Go Migrate MySQL documentation.

7.1 URL Query Parameters

  • x-migrations-table: The name of the migration table.
  • x-no-lock: Set to true to skip the GET_LOCK/RELEASE_LOCK statements. Useful for multi-master MySQL versions.
  • x-statement-timeout: Aborts any statement that exceeds the specified number of milliseconds.
  • dbname: The name of the database to connect to.
  • user: The user to log in as.
  • password: The user's password.
  • host: The host to connect to.
  • port: The port to bind to.
  • tls: TLS/SSL encryption connection parameters.
  • x-tls-ca: The location of the CA (Certificate Authority) file.
  • x-tls-cert: The location of the client certificate file.
  • x-tls-key: The location of the private key file.
  • x-tls-insecure-skip-verify: Whether to use SSL.

7.2 Using with Existing Clients

If you intend to use Go Migrate with an existing MySQL client, make sure to use the multiStatements=true parameter when creating the client. Here's an example:

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)
}

This code imports the required packages, creates a MySQL database client, and initializes Go Migrate with the database instance. It then applies two migrations using the Steps method.