migrate for Go: complete guide with examples

\n\n

The migrate library is a powerful and flexible tool for managing database migrations in the Go ecosystem. It allows developers to version database schema changes, automatically apply and roll back migrations, and integrate with various DBMS, including PostgreSQL, MySQL, SQLite, MongoDB, CockroachDB, and others. In this article, we will thoroughly examine how to use migrate in Go, starting from installation and ending with advanced techniques.

\n\n

What it is and why it is needed

\n\n

During the development of any application that works with a database, sooner or later the need arises to change the table structure: add a new column, create an index, change a data type, or rename a table. If you work in a team or deploy an application across multiple environments (development, staging, production), manually executing SQL scripts becomes a source of errors and inconsistencies.

\n\n

The migrate library solves this problem by providing a simple and declarative way to describe schema changes. Each migration is a pair of files: one for applying changes (up), another for rolling back (down). The library tracks which migrations have already been applied and allows you to safely move forward or backward through the change history.

\n\n

Main advantages of using migrate:

\n

\n
• Versioning: each migration has a unique number or timestamp, allowing you to know exactly which version of the schema is currently in use.
\n
• Reproducibility: you can set up a database from scratch by applying all migrations sequentially.
\n
• Safety: the presence of down-migrations allows you to roll back changes in case of an error.
\n
• Language independence: migrations are written in pure SQL, making them portable between projects in different languages.
\n

\n\n

Installation

\n\n

To install the migrate library in Go, use the standard go get command. It is recommended to install it as a dependency of your project:

\n\n

go get -u github.com/golang-migrate/migrate/v4

\n\n

You will also need to install a driver for your database. For example, for PostgreSQL:

\n\n

go get -u github.com/golang-migrate/migrate/v4/database/postgres

\n\n

For MySQL:

\n\n

go get -u github.com/golang-migrate/migrate/v4/database/mysql

\n\n

For SQLite:

\n\n

go get -u github.com/golang-migrate/migrate/v4/database/sqlite3

\n\n

Additionally, you will need a driver for the migration source. The file system is most commonly used:

\n\n

go get -u github.com/golang-migrate/migrate/v4/source/file

\n\n

Drivers for io/fs, github, gitlab, s3, and other sources are also available.

\n\n

Quick start — minimal working example

\n\n

Let's create a simple application that connects to an SQLite database and applies migrations.

\n\n

First, create the folder structure for migrations:

\n\n

mkdir -p migrations\ncd migrations

\n\n

Create the first migration. The file name must follow the pattern: {version}_{name}.{direction}.sql. The version is usually a timestamp or a sequential number.

\n\n

File: migrations/000001_create_users_table.up.sql

\n

CREATE TABLE IF NOT EXISTS users (\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\n    name TEXT NOT NULL,\n    email TEXT NOT NULL UNIQUE,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);

\n\n

File: migrations/000001_create_users_t