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 !