Using Database Migrations with Golang

When I recently started my new job, I was amazed of all the testing infrastructure the team had built in order to assure that their applications work successfully. This was a big change for me since I was not accustomed to working in such a “test driven” manner.

One topic that came up when they were explaining their data-layer testing was something called Database Migrations. I have used databases during all my career as a software engineer and yet, I found myself asking them: “and what are these Database Migrations?”.

In this article I will talk about how you can use Database Migrations in your services written in Golang.

What are Database Migrations?

According to the prisma.io definition:

Database migrations, also known as schema migrations, database schema migrations, or simply migrations, are controlled sets of changes developed to modify the structure of the objects within a relational database. Migrations help transition database schemas from their current state to a new desired state, whether that involves adding tables and columns, removing elements, splitting fields, or changing types and constraints.

In this article I sometimes refer to Database Migrations as SQL migrations because I will focus on SQL databases like PostgreSQL or MySQL but, just like the definition mentioned, this can be applied to many different databases.

The usage of Database Migrations has the advantage of making the database more easy to evolve as the application and service requirements change. Also, by having different migrations per changes, it’s easier to track and log the changes performed to the database and relate them to the required service change.

However, this does not come without its drawbacks. When adding a new migration, one has to be very careful not to generate an incompatibility between the new database version and the service itself. This could mean accidentally removing a column, changing it’s name, dropping a table that is being used, etc.. Also, there is the possibility to cause some data loss when adding migrations. For example, when removing a column from a table that had information in it, you should be assured that this information will not be needed in the future.

How are SQL Migrations written?

There is no big secret to writing SQL migrations, they are just SQL statements written in the order they want to be applied. For example, a SQL migration can be the following:

CREATE TABLE books (
   id UUID,
   name character varying (255),
   description text
);

ALTER TABLE books ADD PRIMARY KEY (id);

Imagine that you apply this migration, deploy your service and realize that you forgot to add an index you wanted to add, well you can just write another SQL statement in another migration like the following:

CREATE INDEX idx_book_name 
ON books(name);

Now, the IMPORTANT thing is the order in which these migrations are applied. You cannot run the second one first since the table referenced is not yet created. We will get into this in the following section.

How to use SQL Migrations in Go?

Luckily for us, Go never disappoints. There is a library called golang-migrate, that can be used to perform the SQL migrations. This is a very handy library that can be used for a great variety of databases.

The library (it can be used with it’s CLI tool as well) allows us to run migrations from various data sources:

• A list of .sql files
• Files stored in Google Storage or AWS Cloud
• Files in Github or Gitlab

In our use case, we will load the migrations from a specific folder in our project that will contain the .sql migrations files. Now comes the important part. I mentioned before that the order is important to assure that the migrations are “correctly” executed. Well, this is done in the file’s naming pattern. The package owners have a detailed explanation on the naming pattern here so I will just give you the short version 😅

The files have the following naming patter:

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

The “version” indicates the order of which the migration will be applied. For example if we have:

1_innit_database.up.sql
2_alter_database.up.sql

then the first line migration will be applied first. The “title” is just for readability and description and does not serve any additional purpose.

Now regarding the up/down. The up method is used to add new tables, columns, or indexes to your database, while the down method should reverse the operations performed by the up method.

Now that we know how to write migration files. Let’s see how can we apply them. I have written a small Migrator struct in Go that has the following definition:

package migrator

import (
 "database/sql"
 "embed"
 "errors"
 "fmt"

 "github.com/golang-migrate/migrate/v4"
 "github.com/golang-migrate/migrate/v4/database/postgres"
 "github.com/golang-migrate/migrate/v4/source"
 "github.com/golang-migrate/migrate/v4/source/iofs"
)

type Migrator struct {
 srcDriver source.Driver
}

func MustGetNewMigrator(sqlFiles embed.FS, dirName string) *Migrator {
 d, err := iofs.New(sqlFiles, dirName)
 if err != nil {
  panic(err)
 }
 return &Migrator{
  srcDriver: d,
 }
}

func (m *Migrator) ApplyMigrations(db *sql.DB) error {
 driver, err := postgres.WithInstance(db, &postgres.Config{})
 if err != nil {
  return fmt.Errorf("unable to create db instance: %v", err)
 }

 migrator, err := migrate.NewWithInstance("migration_embeded_sql_files", m.srcDriver, "psql_db", driver)
 if err != nil {
  return fmt.Errorf("unable to create migration: %v", err)
 }

 defer func() {
  migrator.Close()
 }()

 if err = migrator.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
  return fmt.Errorf("unable to apply migrations %v", err)
 }

 return nil
}

When we create the Migrator, we pass the path where all the migrations files are. We also provide the embedded file system (for more info on the Go embedding check here). With this, we create a source driver that contains the loaded migration files.

Regarding the ApplyMigrations method. This is the one that runs the migrations to the provided DB instance. We use the source file drive the Migrator has defined and we create a migrate instance using the library providing the database instance. Then we simply run the Up (or Down) function and the migrations are applied.

I have also written a small main.go where I create a Migrator instance and apply it to a local DB instance in Docker.

package main

import (
 "database/sql"
 "embed"
 "fmt"
 "psql_migrations/internal/migrator"
)

const migrationsDir = "migrations"

//go:embed migrations/*.sql
var MigrationsFS embed.FS

func main() {
 // --- (1) ----
 // Recover Migrator
 migrator := migrator.MustGetNewMigrator(MigrationsFS, migrationsDir)

 // --- (2) ----
 // Get the DB instance
 connectionStr := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
 conn, err := sql.Open("postgres", connectionStr)
 if err != nil {
  panic(err)
 }

 defer conn.Close()

 // --- (2) ----
 // Apply migrations
 err = migrator.ApplyMigrations(conn)
 if err != nil {
  panic(err)
 }

 fmt.Printf("Migrations applied!!")
}

This will read all the migration files inside the migrations folder and create the migrator with its contents. Then we create a DB instance to our local database and apply the migrations to it.

Conclusions

This was a really interesting topic to write about. I enjoy database management and database migrations was something I was unaware of.

I think Database Migrations are a very useful tool, not just for testing but for better control and versioning of your databases. It’s not without its drawbacks of course since a small mistake in a migration definition can cause problems for your service (and other services if your database and tables are shared between them).

Also, I was very impressed with the go-migrate library. It’s Github page has very detailed explanations on usage, common mistakes, FAQ, etc.. It’s very complete and makes it practically trivial to use. I highly recommend checking it out!!

As always, you can find the complete project described in this article in my GitHub account here.

Happy coding :)

Want to Connect?

As always, feel free to contact me if you need some help with something 
I have mentioned or if you have a suggestion on where I can improve this 
article. You can DM me on my Twitter any time.

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn, and YouTube.
• Visit Stackademic.com to find out more about how we are democratizing free programming education around the world.