Database migrations in Golang

Implementing migrations in Go the sane way

A topic that keeps on coming up in r/reddit is How do I solve database migrations in Go ? Most people including myself came from other languages such as PHP and Ruby where database migrations are a problem that have been solved. Rails from the Ruby world and Laravel from the PHP world as an example. But how do I replicate such functionality in Go ? Also considering the fact that frameworks are an anti-pattern in Go.

In both Rails and Laravel for example, you run a command bin/rails db:migrate or php artisan migrate. It’d be fairly easy to run that command as a step in your deployment pipeline but how can we replicate that functionality in a Go app.

To solve this problem in Go, a lot of libraries have been created but I have had the most success with the migrate library. I will be building a tiny application - only package main - that shows this process along with how you can build any Go web app with automatic database migration on it’s startup and how you can deal with some intrincasies as per deployment. I will also explain how this ends up in the real world.

A sample application

The migrate library requires some convention as per the migration files. This is expected as it is a matter of convention over configuration. The migration files have to be named 1_create_XXX.up.sql and 1_create_XXX.down.sql. So basically, each migration should have an up.sql and down.sql file. The up.sql file will be executed on actually running the migration while down.sql will execute when a rollback is attempted.

You can use the migrate cli tool to create the migrations though. migrate create -ext sql create_users_table

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
// ... code
package main

import (
	"database/sql"
	"flag"
	"fmt"
	"log"
	"os"

	_ "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() {

	var migrationDir = flag.String("migration.files", "./migrations", "Directory where the migration files are located ?")
	var mysqlDSN = flag.String("mysql.dsn", os.Getenv("MYSQL_DSN"), "Mysql DSN")

	flag.Parse()

	db, err := sql.Open("mysql", *mysqlDSN)
	if err != nil {
		log.Fatalf("could not connect to the MySQL database... %v", err)
	}

	if err := db.Ping(); err != nil {
		log.Fatalf("could not ping DB... %v", err)
	}

	// Run migrations
	driver, err := mysql.WithInstance(db, &mysql.Config{})
	if err != nil {
		log.Fatalf("could not start sql migration... %v", err)
	}

	m, err := migrate.NewWithDatabaseInstance(
		fmt.Sprintf("file://%s", *migrationDir), // file://path/to/directory
		"mysql", driver)

	if err != nil {
		log.Fatalf("migration failed... %v", err)
	}

	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
		log.Fatalf("An error occurred while syncing the database.. %v", err)
	}

	log.Println("Database migrated")
	// actual logic to start your application
	os.Exit(0)
}

That above my friend is the easiest way to do database migration in Go. You can go ahead to download the following files from this repo and place them in the migrations directory or wherever you deem fit. After which you will need to run it with the following command:

$ go run main.go -mysql.dsn "root:@tcp(localhost)/xyz"

If all goes well, you should see a “Database migrated” printed on standard output.

While this is ridicously easy to set up, it does bring a dependency on the filesystem - the migration files have to be present in order to for the migration to be possible. This is also easy to solve. There are 3 ways to solve this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
FROM golang:1.11 as build-env

WORKDIR /go/src/github.com/adelowo/project
ADD . /go/src/github.com/adelowo/project

ENV GO111MODULE=on

RUN go mod download
RUN go mod verify
RUN go install ./cmd

## A better scratch
FROM gcr.io/distroless/base
COPY --from=build-env /go/bin/cmd /
COPY --from=build-env /go/src/github.com/adelowo/project/path/to/migrations /migrations
CMD ["/cmd"]

I haven’t actually done this but it does look like a viable option.

Update: this has become my most preffered method of doing this. Use go-bindata to embed the files and capice

comments powered by Disqus

My Newsletter

I send out an email every 2 weeks or there about often to inform you of articles I have written or cool stuff I'm working on and/or launching. Sounds like fun? go ahead and sign up

    We won't send you spam. Unsubscribe at any time.

    Powered By ConvertKit