GAE/Go + Database Migration

When developing an API, it's common to use an RDB (relational database) like MySQL or PostgreSQL as the data store.

Some frameworks come with convenient migration tools for editing an RDB schema, and since I personally got into application development through Ruby on Rails, I've used what was given to me without thinking too deeply about it.

• https://guides.rubyonrails.org/active_record_migrations.html

However, in situations where you're developing with a variety of technology stacks, it's not uncommon to find that a Rails-like migration tool simply isn't available.

Here, I've thought through how to operate database migrations when using GAE/Go with PostgreSQL as the data store.

When deciding on the operational approach for database migrations, I selected and set up a tool while considering the following points:

• The basic operations needed for migration should be simple
• I'm not particular about how migrations are written
• I want the schema to be applied automatically at deploy time

With that in mind, the tool I decided to use is:

• sql-migrate

I won't go into detail on how to use sql-migrate here, since it's covered in the README and there are articles introducing it in Japanese as well, but I've set up migration tasks in a Makefile to bundle the commands I want to run before and after migration (I plan to write about this in a separate article).

Migration steps

Migrating in the local environment

The way sql-migrate is used is simple enough that it hardly needs explaining, but basically, you run it in the local environment following this flow:

• new: generate a new migration file
• up: apply the migration
• down: roll back the migration

If you've written the migration file correctly, the schema should be updated as soon as you run sql-migrate up.

Applying migrations at deploy time

What should you do after applying migrations in the local environment? As an example, let's consider the case of using PostgreSQL set up on Cloud SQL from a GAE/Go server.

This is also covered in the README, but I'll introduce it as a concrete example with an excerpt of the code.

FileMigrationSource specifies the location of the migration files. I manage mine under app/db/migrations, so I've specified that with Dir. (I'm using sqlx to connect to the database.)

package db

import (
	"github.com/jmoiron/sqlx"
	migrate "github.com/rubenv/sql-migrate"
)

var (
	migrations = &migrate.FileMigrationSource{
		Dir: "app/db/migrations",
	}
)

func ExecMigrations(postgresURL string) error {
	pg, err := sqlx.Connect("postgres", postgresURL)
	if err != nil {
		return err
	}
	defer pg.Close()

	appliedCount, err := migrate.Exec(pg.DB, sqlDriver, migrations, migrate.Up)
	if err != nil {
		return err
	}
	log.Printf("Applied %v migrations", appliedCount)
	return nil
}

ExecMigration is a function for applying migrations, and calling it from main runs the migration at startup. Whether to run the migration or not is probably best controlled via an environment variable or similar.

package main

func main() {
	// Flag for whether to apply migrations at startup
	if applyMigration == True {
		err := db.ExecMigrations(postgresURL)
		if err != nil {
			log.Fatal(err)
		}
	}
	// omitted
}

It's not much code, but this alone is enough to update the PostgreSQL schema at the same time as deploying to GAE. If the migration ran successfully, you should see "Applied x migrations" in the logs.

sql-migrate is a simple, easy-to-use tool that isn't limited to Golang, so it seems like it could be useful when developing with other technology stacks as well.