Effortless Database Migrations in Go 🚀

Hellowww.

In this article we are gonna learn how to update database schema in Go programming language using golang-migrate library.

Why database schema versioning ?

Managing database schemas and migrations can be a difficult task, especially as the project grows. However, with the right tools and techniques, this process can be streamlined and even enjoyable.

In this guide, we'll explore how to leverage the power of the Golang-Migrate library to handle database migrations seamlessly, taking as an example MySQL databases migration.

Why Golang-Migrate?

Golang-Migrate is a robust and feature-rich library for managing database migrations in Go projects.

It offers support for various database systems, including MySQL, PostgreSQL, SQLite, and more. With Golang-Migrate, you can version-control your database schema changes, apply migrations programmatically, and ensure consistency across different environments.

For every update that you will make to the database schema you will have to create to sql files, one for updating the schema and one for rollbacking it.

the migration filename structure is : <migration_version>_<description>.up.sql

the migration rolleback filename structure is the same by down instead of up : <migration_version>_<description>.down.sql

For example if you want to have a migration to create a users tables, you should create two files named something like this:

0000001_create_users_table.up.sql

0000001_create_users_table.down.sql

Let's write some code

Go environment setup

If you don't have the go environment ready, you can follow the steps in the following blog post to setup your environment.

Project structure

Our project structure will be like this:

• main.go: Serves as the entry point of the application.
• go.mod: Specifies the project's module and its dependencies.

go-app-with-db-migrations   
├── main.go         
├── go.mod   
├── migrations/   
├────── 000001_create_users_table.up.sql   
├────── 000001_create_users_table.down.sql    
├────── 000002_add_column_to_users_table.up.sql  
└────── 000002_add_column_to_users_table.down.sql         

In go.mod we'll define the module name and the required Go version for the project.

We will need the golang-migrate library and go-sql-driver

module main

go 1.22.1

require (
  github.com/go-sql-driver/mysql v1.8.1
  github.com/golang-migrate/migrate/v4 v4.17.0
)

The content of our main.go

package main

import (
	"database/sql"
	"github.com/golang-migrate/migrate/v4"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/golang-migrate/migrate/v4/database/mysql"
	_ "github.com/golang-migrate/migrate/v4/source/file"
)

// InitDb initializes the database connection
func InitDb() *sql.DB {
	var err error

	// Connect to MariaDB assuming username/password is user
	Db, err := sql.Open("mysql", "user:user@tcp(localhost:3306)/our_database_name")
	if err != nil {
		log.Fatal("Error opening database:", err)
	}
	return Db
}

// migrates the database to create tables
func UpdateDatabaseSchema() {
	// Initialize the database connection
	Db := InitDb()
	defer Db.Close() // Close the database connection when done

	// Create a new MySQL driver
	driver, _ := mysql.WithInstance(Db, &mysql.Config{})

	// Create a new migration instance
	m, _ := migrate.NewWithDatabaseInstance(
		"file://migrations", // Migration files location
		"our_database_name", // Current migration version
		driver,              // MySQL driver
	)

	// Run the migrations
	m.Up()
}

func main() {
	UpdateDatabaseSchema()
}

Install the dependencies

Now download the project dependencies that we added to go.mod using this simple command:

go mod tidy

You will notice a new file generated go.sum and you will notice the content of go.mod now looks something like this, you don't need to change that, that's how it is supposed to be.

module main

go 1.22.1

require (
	github.com/go-sql-driver/mysql v1.8.1
	github.com/golang-migrate/migrate/v4 v4.17.0
)

require (
	filippo.io/edwards25519 v1.1.0 // indirect
	github.com/hashicorp/errwrap v1.1.0 // indirect
	github.com/hashicorp/go-multierror v1.1.1 // indirect
	go.uber.org/atomic v1.7.0 // indirect
)

Let's create our first migration

Create users table migration file

create a file in migrations folder

The content of 000001_create_users_table.up.sql

CREATE TABLE IF NOT EXISTS users
(
    user_id         VARCHAR(255) PRIMARY KEY,
    name             VARCHAR(255),
    phone_number     VARCHAR(20)
);

Create users table migration rollback file

The content of 000001_create_users_table.down.sql

This can be users to rollback the table creation a table users

DROP TABLE IF EXISTS users;

Add a new column migration

Let's say we want to add a new column to the database, let's add a field created_at to save the user's creation date.

The content of 000002_add_column_to_users_table.up.sql

ALTER TABLE users ADD COLUMN created_at;

Add a new column migration rollback

The content of 000002_add_column_to_users_table.down.sql

ALTER TABLE users DROP COLUMN created_at;

Now we should have 4 files in the migrations folder, two for creating the users table and two for adding a new column.

That's it.

Now whenever you run the application, golang-migrate will check the migrations folder for new files and update your database schema on the fly.

Run the application using:

go run .

Troubleshooting

I have this error when running the app:

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x2 addr=0x28 pc=0x1009c1f44]

You probably didn't create the database, the username/password of the database is wrong, or you created the database but you did not update the database name/user/password in the source code ?

Drop a comment and i will help you figure it out 😉.

How to rollback a migration ?

Using the CLI

In some cases, you may need to roll back a migration due to errors or changes in requirements. Golang-Migrate makes this process straightforward.

In order to rollback a migration, you must do it manually by running the migrate down command.

Setup Golang-Migrate

Let's set up Golang-Migrate CLI. Start by installing the library using go get:

go get -u -d github.com/golang-migrate/migrate/cmd/migrate

Once installed, you can verify the installation by running:

migrate -version

Rolling Back Migrations

To roll back the last migration, from the root of the project run:

migrate -database "mysql://user:user@tcp(localhost:3306)/our_database_name" -path migrations down

This command will revert the last applied migration, undoing the corresponding schema changes.

Source Code

https://github.com/camelcodes/effortless-database-migrations-in-go-with-golang-migrate

Happy Coding !