How to Execute Database Migrations using Go Migrate

A database migration, also known as a schema migration, is a set of changes to be made to a structure of objects within a relational database 😏.

The process of migration helps to change the database schema from its current state to a new desired state, whether it involves adding tables and columns, removing elements, splitting fields, or changing types and constraints.

By managing these changes in a programmatic way, it becomes easier to maintain consistency and accuracy in the database, as well as keep track of the history of modifications made to it.

Setup and Installation

migrate is a CLI tool that you can use to run migrations. You can easily install it on various operating systems such as Linux, Mac and Windows by using package managers like curl, brew, and scoop, respectively.

For more information on how to install and use the tool, you can refer to the official documentation.

To install the migrate CLI tool using scoop on Windows, you can follow these steps:

$ scoop install migrate

To install the migrate CLI tool using curl on Linux, you can follow these steps:

$ curl -L https://packagecloud.io/golang-migrate/migrate/gpgkey| apt-key add - 
$ echo "deb 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

To install the migrate CLI tool using on Mac, you can follow these steps:

$ brew install golang-migrate

How to Create a New Migration

Create a folder like database/migration to store all the migration files.

Migrate gives us several commands:

The first one is create, which we can use to create new migration files.
The 2nd one is goto, which will migrate the schema to a specific version.
Then the up or down commands to apply all or N up or down migrations.

Next, create migration files using the following command:

migrate create -ext sql -dir database/migration/ -seq init_mg

You use -seq to generate a sequential version and init_mg is the name of the migration.

A migration typically consists of two distinct files, one for moving the database to a new state (referred to as "up") and another for reverting the changes made to the previous state (referred to as "down").

The "up" file is used to implement the desired changes to the database, while the "down" file is used to undo those changes and return the database to its previous state.

The format of those files for SQL are:

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

When you create migration files, they will be empty by default. To implement the changes you want, you will need to fill them with the appropriate SQL queries.

How to Run Migration Up

In order to execute the SQL statements in the migration files, migrate requires a valid connection to a Postgres database.
To accomplish this, you will need to provide a connection string in the proper format.

$ migrate -path database/migration/ -database "postgresql://username:secretkey@localhost:5432/database_name?sslmode=disable" -verbose up

Now in your Postgres shell, you can check newly created tables by using the following commands:

\d+
 \d+ table_name DESCRIBE TABLE

How to Rollback Migrations

If you want to revert back the migration, you can do that using the following down tag:

$ migrate -path database/migration/ -database "postgresql://username:secretkey@localhost:5432/database_name?sslmode=disable" -verbose down

It will delete the email column from both tables as mentioned in the 000002_init_mg.up.sql file.
Now, let's check the database and see if email has been deleted or not:

How to Resolve Migration Errors

If a migration contains an error and is executed, migrate will prevent any further migrations from being run on the same database.

An error message like Dirty database version 1. Fix and force version will be displayed, even after the error in the migration is fixed. This indicates that the database is "dirty" and needs to be investigated.

It is necessary to determine if the migration was applied partially or not at all. Once you've determined this, the database version should be forced to reflect its true state using the force command.

$ migrate -path database/migration/ -database "postgresql://username:secretkey@localhost:5432/database_name?sslmode=disable" force <VERSION>

How to Add Commands in a Makefile : 

migration_up: migrate -path database/migration/ -database "postgresql://username:secretkey@localhost:5432/database_name?sslmode=disable" -verbose up
migration_down: migrate -path database/migration/ -database "postgresql://username:secretkey@localhost:5432/database_name?sslmode=disable" -verbose down 
migration_fix: migrate -path database/migration/ -database "postgresql://username:secretkey@localhost:5432/database_name?sslmode=disable" force VERSION

Now, you can run $ make migration_up for 'up', $ make migration_down for 'down', and $ make migration_fix to fix the migration issue.

Before running the makefile, ensure that the correct version number is included in the migration_fix command.

MySQL

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

URL Query	WithInstance Config	Description
x-migrations-table	MigrationsTable	Name of the migrations table
x-no-lock	NoLock	Set true to skip GET_LOCK/RELEASE_LOCK statements.Useful for multi-master MySQL flavors. Only run migrations from one host when this is enabled.
x-statement-timeout	StatementTimeout	Abort any statement that takes more than the specified number of milliseconds, functionally similar to Server-side SELECT statement timeouts but enforced by the client. Available for all versions of MySQL, not just >=5.7.
dbname	DatabaseName	The name of the database to connect to
user		The user to sign in as
password		The user's password
host		The host to connect to.

Cassandra

cassandra://host:port/keyspace?param1=value&param2=value2

URL Query	Default value	Description
x-migrations-table	schema_migrations	Name of the migrations table
x-multi-statement	false	Enable multiple statements to be ran in a single migration (See note above)
port	9042	The port to bind to
consistency	ALL	Migration consistency
protocol		Cassandra protocol version (3 or 4)
timeout	1 minute	Migration timeout
connect-timeout 600ms		Initial connection timeout to the cluster
username	nil	Username to use when authenticating.
password	nil	Password to use when authenticating.
sslcert		Cert file location. The file must contain PEM encoded data.
sslkey		Key file location. The file must contain PEM encoded data.
sslrootcert		The location of the root certificate file. The file must contain PEM encoded data.
sslmode		Whether or not to use SSL (disable|require|verify-ca|verify-full)
disable-host-lookup	false	Disable initial host lookup.

MongoDB

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

URL Query
WithInstance Config
Description
x-migrations-collection
MigrationsCollection
Name of the migrations collection
x-transaction-mode
TransactionMode
If set to true wrap commands in transaction. Available
only for replica set. Driver is using strconv.ParseBool for parsing
x-advisory-locking
true
Feature flag for advisory locking, if set to false disable
advisory locking
x-advisory-lock-collection
migrate_advisory_lock
The name of the collection to use for advisory locking.
x-advisory-lock-timeout
15
The max time in seconds that migrate will wait to
acquire a lock before failing.
x-advisory-lock-timeout-interval
10
The max time in seconds between attempts to acquire the advisory lock the 
lock is attempted to be acquired using an exponential backoff algorithm.
dbname
DatabaseName
The name of the database to connect to
user
The user to sign in as. Can be omitted
password
The user's password. Can be omitted
host
The host to connect to
port
The port to bind to

sqlite

sqlite://path/to/database?query

URL Query
WithInstance Config
Description
x-migrations-table
MigrationsTable
Name of the migrations table. Defaults to schema_migrations.
x-no-tx-wrap
NoTxWrap
Disable implicit transactions when true. 
Migrations may and should, contain explicit BEGIN and COMMIT statements.

neo4j

neo4j://user:password@host:port/

URL Query
 WithInstance Config
 Description
x-multi-statement
MultiStatement
Enable multiple statements to be ran in a single 
migration (See note above)
user
 Contained within AuthConfig
The user to sign in as
password
Contained within AuthConfig
The user's password
host
The host to connect to. Values that start with / are 
for unix domain sockets. (default is localhost)
port
The port to bind to. (default is 7687)
MigrationsLabel
Name of the migrations node label