tern is a powerful standalone SQL migration tool, which I’ve used quite a bit in the past.

I’ve always treated the migration as a separate step - deploy the application and then perform the migration. This requires me to potentially move the schema migrations files around, or run the migration via an ssh tunnel or similar.

One of the advantages of golang is being able to ship a single “kitchen sink” binary, with everything we need in it. Why not do that here?

Luckily, tern (or rather, the tern migration package) can be embedded in your application, though there is no included example. But it’s not too difficult. Let’s walk through it.

We need to:

If you’d like to TL;DR this, you might want to just check out the complete example.

Let’s start at the top end, with our main func - just so we can see what it will look like to call this:

func main() {
	// create a migrator, connecting to the postgresql
	// database defined by the environment variable
	migrator, err := migrations.NewMigrator(os.Getenv("DB_DNS"))
	if err != nil {

	// get the current migration status
	now, exp, info, err := migrator.Info()
	if err != nil {
	if now < exp {
		// migration is required, dump out the current state
		// and perform the migration
		println("migration needed, current state:")

		err = migrator.Migrate()
		if err != nil {
		println("migration successful!")
	} else {
		println("no database migration needed")


So - we will instantiate a “migrator” (which is our wrapper around the tern migrator) with the connection string for a postgresql database pulled from our environment. You can find the connection string syntax documented here.

Now we have a few methods available to us. The Info() method tells us whether or not the database needs migrating, as well as a textual representation for that current migration state.

The Migrate() method performs the actual migration, bringing the database up to the most recent schema version.

Now, let’s look at the migration package itself. The directory structure also includes the schema migration files. So in this example:


Going through migrations.go in sections:

package migrations

import (


We are importing pgx for the low-level database connection, and the tern library. Note, we are using the recently-released v2 of the latter, as it uses the fs.FS interface, making this implementation much simpler.

const versionTable = "db_version"

type Migrator struct {
	migrator *migrate.Migrator

//go:embed data/*.sql
var migrationFiles embed.FS

versionTable is an arbitrary string, tern will use this as a table name to store the database migration state in.

The Migrator struct is what we are going to return in the following constuctor.

The migrationFiles var uses the Go embed package to automatically embed the files matching data/*.sql (relative to the directory this package is in) into your binary, making them available to your application at runtime.

func NewMigrator(dbDNS string) (Migrator, error) {

	conn, err := pgx.Connect(context.Background(), dbDNS)
	if err != nil {
		return Migrator{}, err
	migrator, err := migrate.NewMigratorEx(
		context.Background(), conn, versionTable,
			DisableTx: false,
	if err != nil {
		return Migrator{}, err

	migrationRoot, _ := fs.Sub(migrationFiles, "data")

	err = migrator.LoadMigrations(migrationRoot)
	if err != nil {
		return Migrator{}, err

	return Migrator{
		migrator: migrator,
	}, nil

This is our constructor. It’s relatively straightforward:

func (m Migrator) Info() (int32, int32, string, error) {

	version, err := m.migrator.GetCurrentVersion(context.Background())
	if err != nil {
		return 0, 0, "", err
	info := ""

	var last int32
	for _, thisMigration := range m.migrator.Migrations {
		last = thisMigration.Sequence

		cur := version == thisMigration.Sequence
		indicator := "  "
		if cur {
			indicator = "->"
		info = info + fmt.Sprintf(
			"%2s %3d %s\n", 
			thisMigration.Sequence, thisMigration.Name)

	return version, last, info, nil

Now we can implement the Info() function. It is using the tern functions to list the migrations, and work out if we are up to date. Along the way it constructs a handy textual representation of the migration state.

// Migrate migrates the DB to the most recent version of the schema.
func (m Migrator) Migrate() error {
	err := m.migrator.Migrate(context.Background())
	return err

// MigrateTo migrates to a specific version of the schema. Use '0' to
// undo all migrations.
func (m Migrator) MigrateTo(ver int32) error {
	err := m.migrator.MigrateTo(context.Background(), ver)
	return err

Lastly, the actual migration code - which is very simple, as it is just calling the functionality provided by tern.

There is Migrate() which we saw used in main() but also MigrateTo() which is useful if you need to support migrating to a specific version for testing or for deployment rollbacks.

Lastly, we need to look at the actual migration files in the data subdirectory. There is not a lot to say, they are already well documented here. Ensure they are numbered correctly. Here’s the first one (001_init.sql) that I am using in this example, to create a new table to store jokes:

    joke_text TEXT NOT NULL

---- create above / drop below ----


That’s it! Let’s see it in action. Firstly, running when the database is blank, none of our migrations have ever been run:

$ go run .
migration needed, current state:
     1 001_init.sql
     2 002_ts.sql
     3 003_nsfw.sql
     4 004_votes.sql

migration successful!

Looking at the database, we can confirm the migration has occurred succesfully:

db=# \d
             List of relations
 Schema |     Name     |   Type   | Owner
 public | db_version   | table    | owner
 public | jokes        | table    | owner
 public | jokes_id_seq | sequence | owner
(3 rows)

db=# select * from db_version;
(1 row)

Time: 0.807 ms
db=# \d jokes
                                       Table "public.jokes"
   Column   |           Type           | Collation | Nullable |              Default
 id         | integer                  |           | not null | nextval('jokes_id_seq'::regclass)
 joke_text  | text                     |           | not null |
 ts         | timestamp with time zone |           | not null | now()
 nsfw       | boolean                  |           | not null | false
 up_votes   | integer                  |           | not null | 0
 down_votes | integer                  |           | not null | 0
    "jokes_pkey" PRIMARY KEY, btree (id)

Running it again immediately, will show, as expected, nothing needs doing:

$ go run .
no database migration needed

If we were rolling out a new version of our code, and two new migrations had been added since the last deployment, you might see:

$ go run .
migration needed, current state:
     1 001_init.sql
->   2 002_ts.sql
     3 003_nsfw.sql
     4 004_votes.sql

migration successful!

All the above code is available in a self-contained example on github.

Tags: tern  golang  migration  sql  postgresql  schema