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:
migrate packageIf 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 {
panic(err)
}
// get the current migration status
now, exp, info, err := migrator.Info()
if err != nil {
panic(err)
}
if now < exp {
// migration is required, dump out the current state
// and perform the migration
println("migration needed, current state:")
println(info)
err = migrator.Migrate()
if err != nil {
panic(err)
}
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:
migrations/migrations.go
migrations/data/001_init.sql
migrations/data/002_ts.sql
migrations/data/003_nsfw.sql
migrations/data/004_votes.sql
Going through migrations.go in sections:
package migrations
import (
"context"
"embed"
"fmt"
"io/fs"
"github.com/jackc/pgx/v5"
"github.com/jackc/tern/v2/migrate"
)
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,
&migrate.MigratorOptions{
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:
pgx.Connectmigrate.Migrator struct.data/*.sql and LoadMigration wants the files to be in the root of the filesystem)LoadMigrationsfs.Sub :-)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",
indicator,
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:
CREATE TABLE jokes (
id SERIAL NOT NULL PRIMARY KEY,
joke_text TEXT NOT NULL
);
---- create above / drop below ----
DROP TABLE jokes;
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;
version
---------
4
(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
Indexes:
"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.