Go: database migrations made easy - an example using MySQL

In my article about database migrations, I’ve shown how database migrations are done on Ruby On Rails and brought it to the Java world. It’s time to see how it works in Go.

Meet Migrate

I’ve been actively developing applications using Go, and I needed to find a good solution for database migrations.

Migrate is a robust and simple tool to use for that. It can be used as CLI or as library

In this article, we’ll focus on CLI usage.

Main commands

We’ll explore each of the following CLI commands:

Migration files

A single logical migration is represented as two separate migration files, one to migrate “up” to the specified version from the previous version, and a second to migrate back “down” to the previous version.

The ordering and direction of the migration files are determined by the filenames used for them. The migrate command expects the filenames of migrations to have the format:

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

The title of each migration is unused and is only for readability. Similarly, the extension of the migration files is not checked by the library and should be an appropriate format for the database in use (.sql for SQL variants, for instance).

Versions of migrations may be represented as any 64-bit unsigned integer. All migrations are applied upward in order of increasing version number, and downward by decreasing version number.

Common versioning schemes include incrementing integers:

1_initialize_schema.down.sql
1_initialize_schema.up.sql
2_add_table.down.sql
2_add_table.up.sql

...

Or timestamps at an appropriate resolution:

1500360784_initialize_schema.down.sql
1500360784_initialize_schema.up.sql
1500445949_add_table.down.sql
1500445949_add_table.up.sql

...

But any scheme resulting in distinct, incrementing integers as versions is valid.

The domain model

This is our initial domain model that will be evolved during this article:

The ‘ Library’ table has a One To Many relationship with ‘ Book’ table.

The Go application

This is our final directory structure:

To ease the setup, I’m going to use docker-compose to launch our MySQL instance. I’m also using Adminer, which is a lightweight database management tool.

version: '3'
services:
  db:
    image: mysql:5.7
    restart: always
    container_name: mysql_database
    ports:
      - "5432:3306"
    volumes:
      - data:/var/lib/mysql
    environment:
      - MYSQL_USER=tutorial
      - MYSQL_PASSWORD=tutorialpasswd
      - MYSQL_ROOT_PASSWORD=Mysql2019!
      - MYSQL_DATABASE=migrations_tutorial
      - MYSQL_HOST_NAME=mysql_db
    networks:
      - app-network

  adminer:
    image: adminer
    container_name: adminer
    ports:
      - 8080:8080
    networks:
      - app-network

volumes:
  data:
    driver: local

networks:
  app-network:
    driver: bridge

If you are new to Docker and/or docker-compose, whenever you want to use an image, you can visit Docker Hub to check for available images.

Some details:

• db: I’m using mysql:5.7 image. This container name is set to ‘mysql_database’, I’m exposing port 5432 to clients and it will be reachable by other containers by joining a custom network named ‘app-network’.
• adminer: I’m using adminer image. This container name is set to ‘adminer’, I’m exposing port 8080 to clients and it will be reachable by other containers by joining a custom network named ‘app-network’.
• networks: I’m creating a custom network called ‘app-network’ of type ‘ bridge’.

This is our Go script that connects to the database and displays all tables with their columns. It’ll be used to show the effects of migrations:

//  This Go script displays all tables in a given database
//  along with their respective columns.
//
//  author: Tiago Melo (tiagoharris@gmail.com)

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"strings"
)

// helper function to handle errors
func checkError(message string, err error) {
	if err != nil {
		log.Fatal(message, err)
	}
}

func showTablesWithColumns() {
	// sql.Open does not return a connection. It just returns a handle to the database.
	// In a real world scenario, those db credentials could be environment variables and we could use a package like github.com/kelseyhightower/envconfig to read them.
	db, err := sql.Open("mysql", "root@tcp(127.0.0.1:5432)/migrations_tutorial")

	// A defer statement pushes a function call onto a list.
	// The list of saved calls is executed after the surrounding function returns.
	// Defer is commonly used to simplify functions that perform various clean-up actions.
	defer db.Close()

	checkError("Error getting a handle to the database", err)

	// Now it's time to validate the Data Source Name (DSN) to check if the connection
	// can be correctly established.
	err = db.Ping()

	checkError("Error establishing a connection to the database", err)

	showTablesQuery, err := db.Query("SHOW TABLES")

	defer showTablesQuery.Close()

	checkError("Error creating the query", err)

	for showTablesQuery.Next() {
		var tableName string

		// Get table name
		err = showTablesQuery.Scan(&tableName)

		checkError("Error querying tables", err)

		selectQuery, err := db.Query(fmt.Sprintf("SELECT * FROM %s", tableName))

		defer selectQuery.Close()

		checkError("Error creating the query", err)

		// Get column names from the given table
		columns, err := selectQuery.Columns()
		if err != nil {
			checkError(fmt.Sprintf("Error getting columns from table %s", tableName), err)
		}

		fmt.Printf("table name: %s -- columns: %v\n", tableName, strings.Join(columns, ", "))
	}
}

func main() {
	showTablesWithColumns()
}

When it comes to Go development, a common tool to use is our good old friend Make. This is the Makefile that I’ve written to facilitate issuing migration commands:

# Author: Tiago Melo (tiagoharris@gmail.com)

# Version - this is optionally used on goto command
V?=

# Number of migrations - this is optionally used on up and down commands
N?=

# In a real world scenario, these environment variables
# would be injected by your build tool, like Drone for example (https://drone.io/)
MYSQL_USER ?= tutorial
MYSQL_PASSWORD ?= tutorialpasswd
MYSQL_HOST ?= 127.0.0.1
MYSQL_DATABASE ?= migrations_tutorial
MYSQL_PORT ?= 5432

MYSQL_DSN ?= $(MYSQL_USER):$(MYSQL_PASSWORD)@tcp($(MYSQL_HOST):$(MYSQL_PORT))/$(MYSQL_DATABASE)

local-db:
	@ docker-compose up -d

	@ until mysql --host=$(MYSQL_HOST) --port=$(MYSQL_PORT) --user=$(MYSQL_USER) -p$(MYSQL_PASSWORD) --protocol=tcp -e 'SELECT 1' >/dev/null 2>&1 && exit 0; do \
	  >&2 echo "MySQL is unavailable - sleeping"; \
	  sleep 5 ; \
	done

	@ echo "MySQL is up and running!"

migrate-setup:
	@if [ -z "$$(which migrate)" ]; then echo "Installing migrate command..."; go install -tags 'mysql' github.com/golang-migrate/migrate/v4/cmd/migrate; fi

migrate-up: migrate-setup
	@ migrate -database 'mysql://$(MYSQL_DSN)?multiStatements=true' -path migrations up $(N)

migrate-down: migrate-setup
	@ migrate -database 'mysql://$(MYSQL_DSN)?multiStatements=true' -path migrations down $(N)

migrate-to-version: migrate-setup
	@ migrate -database 'mysql://$(MYSQL_DSN)?multiStatements=true' -path migrations goto $(V)

drop-db: migrate-setup
	@ migrate -database 'mysql://$(MYSQL_DSN)?multiStatements=true' -path migrations drop

force-version: migrate-setup
	@ migrate -database 'mysql://$(MYSQL_DSN)?multiStatements=true' -path migrations force $(V)

migration-version: migrate-setup
	@ migrate -database 'mysql://$(MYSQL_DSN)?multiStatements=true' -path migrations version

build:
	@ go build inspect_database.go

run: build
	@ ./inspect_database

It’s showtime!

Let’s first set up our database:

The local-db target invokes docker-compose up -d command and waits for MySQL to be ready to accept connections.

Now let’s check if the containers were successfully created:

Ok. And then we check that the volume ‘data’ was created as well:

And then, if we run the app, since we don’t have any tables, there’s no output:

Create Library and Book tables

Let’s write the migrations and put them under migrations folder.

0001_create_library_table.up.sql

CREATE TABLE IF NOT EXISTS `library` (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL UNIQUE
);

0001_create_library_table.down.sql

DROP TABLE IF EXISTS `library`;

0002_create_book_table.up.sql

CREATE TABLE IF NOT EXISTS `book` (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(100) NOT NULL UNIQUE,
	library_id INTEGER,
	FOREIGN KEY(library_id) REFERENCES library(id)
);

0002_create_book_table.down.sql

DROP TABLE IF EXISTS `book`;

Now it’s time to run the pending migrations:

Cool. Let’s run the app:

The schema_migrations table is created by Migrate tool to keep track of migrations. We’ll see it in a minute.

Another way to check the recently created tables is through Adminer. Just point your browser to localhost:8080 and use the credentials defined in docker-compose.yaml:

Add columns to ‘Book’ table

Now let’s add two columns: isbn and publisher, by adding the corresponding ‘up’ and ‘down’ migration files into migrations folder.

0003_add_isbn_and_publisher_to_book.up.sql

BEGIN;

ALTER TABLE `book`

ADD COLUMN `isbn` varchar(13) NOT NULL,
ADD COLUMN `publisher` varchar(20) NOT NULL;

COMMIT;

0003_add_isbn_and_publisher_to_book.down.sql

BEGIN;

ALTER TABLE `book` DROP COLUMN `isbn`;
ALTER TABLE `book` DROP COLUMN `publisher`;

COMMIT;

Note that this time we are using BEGIN and COMMIT to make those changes into a single transaction.

Let’s run the pending migrations:

Now we should see the new columns when running the app:

You could check that through Adminer if you want.

Drop ‘publisher’ column on ‘Book’ table

What if ‘publisher’ column is not necessary anymore? Add the migration files into migrations folder.

0004_drop_publisher_on_book.up.sql

ALTER TABLE `book` DROP COLUMN `publisher`;

0004_drop_publisher_on_book.down.sql

ALTER TABLE `book` ADD COLUMN `publisher` varchar(20) NOT NULL;

Let’s run the pending migrations:

Now we should see that ‘publisher’ is not on ‘Book’ table anymore when running the app:

Playing around

Now that we have all of the tables in place with the desired changes, it’s time to know some other options that we have.

Given that we have 4 migrations (each one with the corresponding ‘up’ and ‘down’ files), let’s see how schema_migrations table looks like. Let’s connect to our MySQL Docker container:

Print current migration version

Dropping the database

Applying N up migrations

Since we dropped the database above, let’s just create ‘Library’ and ‘Book’ tables. This can be accomplished by passing N=2:

Applying N down migrations

Let’s rollback migration #2 which creates ‘Book’ table. To do this, let’s pass N=1:

Migrate to a given version

Right now we have only ‘Library’ table. What if we want to jump up to migration #3? Just pass V=3:

Force a given version

This command sets the desired version but does not run the migration. This is useful when dealing with legacy databases.

For example: suppose that ‘Library’ and ‘Book’ tables already exist, and you want to use Migrate from now on. Let’s drop the database and create the tables manually:

Ok. Now we want to tell to Migrate that the database is currently on version #2 (with ‘Library’ and ‘Book’ tables in place, but ‘Book’ does not have ‘isbn’ and ‘publisher’ columns):

Let’s check the migration version - it will read from schema_migrations table:

Now, if we ask to run all pending migrations, since we forced it to version #2, only migration files #3 and #4 will run:

Now let’s check the tables by running the app:

Cool, isn’t it?

Conclusion

Versioning database changes is as important as versioning source code, and tools like Migrate makes it possible to do it in a safe and manageable way.

Through this simple example, we learned how we can easily evolve a database in a Go application.

Download the source

Here: https://bitbucket.org/tiagoharris/migrations_tutorial/src/master/