Using Database Migrations in Go

Recently we were faced with the need to find a library for convenient work with databases. In our project, the team decided not to use ORM (Object-Relational Mapping), but instead use migrations. Since I only worked with ORMs, I, like the author of the article, was little familiar with the concept of database migrations. While searching for information about migrations and popular solutions, I came across this article. I have left the translation of the article below. Perhaps it will be useful to you. I'd be grateful if you could share the libraries you use.

Using Database Migrations in Go

I recently started a new job and I was amazed by the testing infrastructure the team had built. This “test” approach was new to me.

One of the topics we covered during our discussion of testing the database layer was database migrations. I've been using databases throughout my entire development career, and yet I had to ask the question: “What are database migrations?”

In this article, I will talk about how you can use database migrations in your services written in Golang.

What is Database Migration?

According to definition:

Database migrations, also known as schema migrations or database schema migrations, are controlled sets of changes that allow you to modify the structure of objects in relational database. They help move database schemas from the current state to the new desired state, including adding tables and columnsremoving elements, changing types and restrictionsas well as field separation.

In this article, I sometimes refer to database migrations as SQL migrations because I will focus on SQL databases such as PostgreSQL or MySQL, but as mentioned in the definition, it applies to many different databases.

Using database migrations has the advantage of making it easier to evolve the database as application and service requirements change. Additionally, by having different migrations per change, it is easier to track and record changes and associate them with the required service change.

However, this process is not without its drawbacks. When adding a new migration, you need to be careful not to create incompatibility between the new version of the database and the service itself. For example, accidentally deleting a column, changing its name, deleting a table in use, and so on. Additionally, there is a risk of data loss when adding migrations. For example, if you delete a column from a table that contains important information, you need to ensure that this information is not needed in the future.

How are SQL migrations written?

There is nothing difficult about writing SQL migrations. They are simply SQL queries that are executed in a specific order. For example, an SQL migration might look like this:

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

ALTER TABLE books ADD PRIMARY KEY (id);

Let's say you applied this migration, deployed your service, and discovered that you forgot to add the index you wanted to add. In this case, you can simply write another SQL query as part of another migration, for example:

CREATE INDEX idx_book_name 
ON books(name);

Now that we understand how migrations work, it is important to understand how critical the order in which they are executed is. You will not be able to run the second migration because the reference table has not yet been created. We will look at this in the next section.

How to use SQL migrations in GO?

Luckily Go never disappoints. There is a library called golang-migrate, which can be used to perform SQL migrations. This is a very convenient library that supports most databases.

The library (which can also be used using a command line tool) allows us to perform migrations from various data sources: List of SQL files Files stored in Google Cloud Storage or AWS Cloud Files in GitHub or GitLab In our case, we will download migrations from a specific folder to our project, which will contain SQL migration files. Now the important part. I already mentioned that order is important to ensure that migrations are done “correctly”. Well, this is done with naming pattern. It's covered in quite a bit of detail, so I'll just give you a quick overview.

The files have the following naming pattern:

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

“version” specifies the order in which the migration will be applied. For example, if we have:

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

then `1_innit_database.up.sql` will be applied first. “title” is for ease of reading and description only and serves no additional purpose.

Now relatively up/down. Method up is used to add new tables, columns or indexes to the database, while the method down must cancel the operations performed by the method up.

Now that we know how to write migration files. Let's see how we can apply them. I wrote a small Migrator structure:

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"
)

// Migrator структура для применения миграций.
type Migrator struct {
 srcDriver source.Driver // Драйвер источника миграций.
}

// MustGetNewMigrator создает новый экземпляр Migrator с встроенными SQL-файлами миграций.
// В случае ошибки вызывает panic.
func MustGetNewMigrator(sqlFiles embed.FS, dirName string) *Migrator {
 // Создаем новый драйвер источника миграций с встроенными SQL-файлами.
 d, err := iofs.New(sqlFiles, dirName)
 if err != nil {
  panic(err)
 }
 return &Migrator{
  srcDriver: d,
 }
}

// ApplyMigrations применяет миграции к базе данных.
func (m *Migrator) ApplyMigrations(db *sql.DB) error {
 // Создаем экземпляр драйвера базы данных для PostgreSQL.
 driver, err := postgres.WithInstance(db, &postgres.Config{})
 if err != nil {
  return fmt.Errorf("unable to create db instance: %v", err)
 }

 // Создаем новый экземпляр мигратора с использованием драйвера источника и драйвера базы данных PostgreSQL.
 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 in the path where all the migration files are located. We also provide a built-in file system (for more information on implementing Go, see Here). With this we create a source driver that contains the downloaded migration files.

Method ApplyMigrations Performs the migration process on the provided database instance. We use the source file driver specified in the Migrator structure and create a migration instance using the library and specifying the database instance. After that we simply call the function Up (or Down), and migrations are applied.

I also wrote a small file main.goin which I create an instance of Migrator and apply it to a local database 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 a migrator with its contents. We then create a DB instance in our local database and apply migrations to it.

conclusions

I really like managing databases. I didn't know about migration. This was an interesting topic to write about.

I think database migration is a very useful tool not only for testing but also for better control and versioning of your databases. Of course, this is not ideal, since a small mistake in defining a migration can cause problems for your service (and other services if your database and tables are shared).

Also, I was very impressed with the library go-migrate. Its Github page has very detailed usage explanations, common errors, FAQs, etc. It is very complete and makes it almost easy to use. I highly recommend checking her out!!

As always, you can find the full project described in this article on my GitHub account Here.