Preamble
Database Migration is basically a process that should be initialized and started before starting up a service that need database. In this moment I would share my experience about Database Migration, that helps me for moving or duplicating a database which already good to go.
I will show you how to create an simple program for performing database migration using Golang Migrate. Before we proceed to next step make sure you already install its binary on your system, here is the release list with various operating system option, Golang Migrate Releases.
By creating this program, you can easily setup database design and its sample data. And I have repository you can clone for this here is it, while reading this post you will understanding how it works. And I recommend you to have docker installed in your pocket for this tutorial.
Database Migration Directory
I already have the repository for this one, inside the project there is package named database and inside it there is migration package where you put all generated migration files. Here is the structure of it.
Database Server Setup
For this moment you need to run mysql database server in your machine, if you are already have installed one, you can just go with it. If you want to use docker here is the command to spin up mysql server in simple way.
run -d -p 3306:3306 --name mysql-db -e MYSQL_ROOT_PASSWORD=root mysql:5.7.44
Install golang-migrate
To install golang-migrate you can choose which operating system did you use. Extract migrate binary file and register it your environment path. You can download migrate from its releases page golang migrate download.
Generate Migration Files
Once already download migrate binary file and register it to your environment path, you can check it by running this command.
migrate --version
It will show you which migrate version did you use.
For generate migration files you can use this command
migrate create -ext sql -dir path/to/migration/dir -seq migration_file_name
Explanation
-
-extThis arg is to tellmigrateto usesqlextension -
-dirThis arg is to tellmigratewhere migration files at -
-seqThis arg is to tellmigrateto use sequential numbering for every migration file you generate this is the sample000001_create_users_table.up.sqland000001_create_users_table.down.sql
And we need to generate 2 migration files each of it will be have up and down version. So there will be 4 files. Let's generate migration file for users and profiles table. And make sure your current working directory is on the root of the project.
migrate create -ext sql -dir database/migration -seq create_users_table
migrate create -ext sql -dir database/migration -seq create_profiles_table
When you take a look in the project it will be looks like this
What is difference between up and down migration file?
-
upThis migration file will does construction for your database -
downThis migration file will does deconstruction for your database
Or can be simplified up migration file will contains creation and modification action. In the other hand down will contains drop or removal action.
And here is the SQL Query that will performed when migration process running.
Users Table Migration Files
000001_create_users_table.up.sql
CREATE TABLE users
(
id SERIAL PRIMARY KEY, -- Auto-incremented primary key
email VARCHAR(255) NOT NULL UNIQUE, -- Email column with unique constraint
password VARCHAR(255) NOT NULL, -- Password column
username VARCHAR(50) NOT NULL UNIQUE, -- Username column with unique constraint
deleted_at TIMESTAMP NULL, -- Timestamp for soft deletion
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for last update
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- Timestamp for creation
);
-- Adding index for columns that might be frequently searched or filtered
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_username ON users (username);
-- Index for deleted_at to quickly filter non-deleted users
CREATE INDEX idx_users_deleted_at ON users (deleted_at);
000001_create_users_table.down.sql
DROP TABLE users;
Profiles Table Migration Files
000001_create_profiles_table.up.sql
CREATE TABLE profiles
(
id SERIAL PRIMARY KEY, -- Auto-incremented primary key
user_id INTEGER NOT NULL, -- Assuming user_id will link to users table but without any constraint
first_name VARCHAR(50) NOT NULL, -- First name of the user
last_name VARCHAR(50) NOT NULL, -- Last name of the user
bio TEXT, -- A short bio of the user
profile_picture VARCHAR(255), -- URL to the profile picture
deleted_at TIMESTAMP NULL, -- Timestamp for soft deletion
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- Timestamp for creation
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- Timestamp for last update
);
-- Adding index for columns that might be frequently searched or filtered
CREATE INDEX idx_profiles_user_id ON profiles (user_id);
CREATE INDEX idx_profiles_last_name ON profiles (last_name);
000001_create_profiles_table.down.sql
DROP TABLE profiles;
Implementation
In this section we jump to coding and how to use as it Standalone Program or Embedded routine inside your application. Inside repository project there are 3 golang file for database migration connector.go, mlog.go and migration.go.
connector.go
In this file you can add your own database connection function template for your desired database by copy paste existing sample and adjust based on your need. In this sample will be 2 MySQL and Postgres.
package database
import (
"database/sql"
"fmt"
"github.com/golang-migrate/migrate/v4"
"github.com/golang-migrate/migrate/v4/database/mysql"
"github.com/golang-migrate/migrate/v4/database/postgres"
)
// connect database connector using builtin go sql library
func connect(dialect, connStr string) (*sql.DB, error) {
db, err := sql.Open(dialect, connStr)
if err != nil {
return nil, err
}
err = db.Ping()
if err != nil {
return nil, err
}
return db, nil
}
// MySQLBuilder decorator function for constructing mysql connection string
func MySQLBuilder(cfg Config) (*migrate.Migrate, error) {
// construct connection string and connect
const formatString = "%s:%s@tcp(%s:%s)/%s?parseTime=true&multiStatements=true"
credentials := []any{
cfg.DatabaseUser, cfg.DatabasePasw, cfg.DatabaseHost,
cfg.DatabasePort, cfg.DatabaseName,
}
finalCsf := fmt.Sprintf(formatString, credentials...)
db, err := connect("mysql", finalCsf)
if err != nil {
return nil, err
}
// create migrate instance from connected database client
driver, err := mysql.WithInstance(db, &mysql.Config{})
if err != nil {
return nil, err
}
filePath := fmt.Sprintf("file://%s", cfg.DatabaseMdir)
mgrt, err := migrate.NewWithDatabaseInstance(filePath, cfg.DatabaseDrvr, driver)
if err != nil {
return nil, err
}
return mgrt, nil
}
// PostgresBuilder decorator function for constructing mysql connection string
func PostgresBuilder(cfg Config) (*migrate.Migrate, error) {
// construct connection string and connect
const formatString = "postgres://%s:%s@%s:%d/%s?sslmode=disable"
credentials := []any{
cfg.DatabaseUser, cfg.DatabasePasw, cfg.DatabaseHost,
cfg.DatabasePort, cfg.DatabaseName,
}
finalCsf := fmt.Sprintf(formatString, credentials...)
db, err := connect("postgres", finalCsf)
if err != nil {
return nil, err
}
// create migrate instance from connected database client
driver, err := postgres.WithInstance(db, &postgres.Config{})
if err != nil {
return nil, err
}
filePath := fmt.Sprintf("file://%s", cfg.DatabaseMdir)
mgrt, err := migrate.NewWithDatabaseInstance(filePath, cfg.DatabaseDrvr, driver)
if err != nil {
return nil, err
}
return mgrt, nil
}
mlog.go
This file contains logger implementation for migrate to log execution process. This log will be used inside migration.go file.
package database
import "log"
type (
Log interface {
Printf(format string, v ...interface{})
Verbose() bool
}
// logImpl implements the golang-migrate Logger interface
logImpl struct {
logger *log.Logger
}
)
func NewLog(logger *log.Logger) Log {
return &logImpl{logger: logger}
}
func (l *logImpl) Printf(format string, v ...interface{}) {
l.logger.Printf(format, v...)
}
func (l *logImpl) Verbose() bool {
return true // or false, depending on whether you want verbose logging
}
migration.go
This file contains logger implementation for migrate to log execution process. This log will be used inside migration.go file.
package database
import (
"errors"
_ "github.com/go-sql-driver/mysql"
"github.com/golang-migrate/migrate/v4"
_ "github.com/golang-migrate/migrate/v4"
_ "github.com/golang-migrate/migrate/v4/database/mysql"
_ "github.com/golang-migrate/migrate/v4/database/postgres"
_ "github.com/golang-migrate/migrate/v4/source/file"
_ "github.com/lib/pq"
"log"
)
type (
// ConnectionFunc decorator function for constructing connection string
ConnectionFunc func(cfg Config) (*migrate.Migrate, error)
// Config database configuration detail for connecting to desired database server
Config struct {
DatabaseHost string // database host
DatabasePort string // database port
DatabaseName string // database name
DatabaseUser string // database user
DatabasePasw string // database password
DatabaseDrvr string // database driver
DatabaseMdir string // database migration dir
}
// Migration interface for migration
Migration interface {
Action(name string) error
down() error
up() error
}
// migrationImpl implementation struct
migrationImpl struct {
m *migrate.Migrate
}
)
func NewMigration(cf ConnectionFunc, cfg Config) (Migration, error) {
// initialize migration instance
m, err := cf(cfg)
if err != nil {
return nil, err
}
m.Log = NewLog(
log.New(log.Writer(),
"migration: ",
log.LstdFlags|log.Lshortfile,
),
)
mg := &migrationImpl{m: m}
return mg, nil
}
// Action migrating desired migration version start constructing database
func (mgr *migrationImpl) Action(name string) error {
mgr.m.Log.Printf("migaration.Action: %s", "starting to constructing database")
switch name {
case "UP":
return mgr.up()
case "DOWN":
return mgr.down()
default:
mgr.m.Log.Printf("migaration.Action: %s (%s)", "unknown action name", name)
return errors.New("unknown action")
}
}
// down performing all SQL inside `down` migration files
func (mgr *migrationImpl) down() error {
err := mgr.m.Down()
if err != nil {
if errors.Is(err, migrate.ErrNoChange) {
mgr.m.Log.Printf("migaration.down: %s (%s)", "deconstructing database done", err.Error())
return nil
}
mgr.m.Log.Printf("migaration.down: %s (%s)", "failed to deconstructing database", err.Error())
return err
}
return nil
}
// up performing all SQL inside `up` migration files
func (mgr *migrationImpl) up() error {
mgr.m.Log.Verbose()
err := mgr.m.Up()
if err != nil {
if errors.Is(err, migrate.ErrNoChange) {
mgr.m.Log.Printf("migaration.down: %s (%s)", "constructing database done", err.Error())
return nil
}
mgr.m.Log.Printf("migaration.down: %s (%s)", "failed to constructing database", err.Error())
return err
}
return nil
}
How to Use
We will use Migration implementation inside main.go file, and I will show you how to use it as Standalone Program or Embeded Routine for your application.
Standalone Program
We will compile this into executable binary. Here is the main.go content for that.
package main
import (
"flag"
"github.com/xoxoist/dm-tutor/database"
)
func main() {
action := flag.String("action", "UP", "desired action for your database migration")
flag.Parse()
mg, err := database.NewMigration(database.MySQLBuilder, database.Config{
DatabaseHost: "localhost",
DatabasePort: "3306",
DatabaseName: "md_tutor",
DatabaseUser: "root",
DatabasePasw: "root",
DatabaseDrvr: "mysql",
DatabaseMdir: "database/migration",
})
if err != nil {
panic(err)
}
err = mg.Action(*action)
if err != nil {
panic(err)
}
}
And let's compile it into executable binary named migrator
go build -o migrator main.go
Here is how to use that complied executable binary. And make sure you are on same current working directory with migrator
UP
./migrator -action UP
Response for UP action
migration: 2024/05/26 21:19:45 mlog.go:22: migaration.Action: starting database migration
migration: 2024/05/26 21:19:45 mlog.go:22: Start buffering 1/u create_users_table
migration: 2024/05/26 21:19:45 mlog.go:22: Start buffering 2/u create_profiles_table
migration: 2024/05/26 21:19:45 mlog.go:22: Read and execute 1/u create_users_table
migration: 2024/05/26 21:19:45 mlog.go:22: Finished 1/u create_users_table (read 932.759µs, ran 14.400196ms)
migration: 2024/05/26 21:19:45 mlog.go:22: Read and execute 2/u create_profiles_table
migration: 2024/05/26 21:19:45 mlog.go:22: Finished 2/u create_profiles_table (read 16.819281ms, ran 10.786267ms)
DOWN
./migrator -action DOWN
Response for DOWN action
migration: 2024/05/26 21:20:07 mlog.go:22: migaration.Action: starting database migration
migration: 2024/05/26 21:20:07 mlog.go:22: Start buffering 2/d create_profiles_table
migration: 2024/05/26 21:20:07 mlog.go:22: Start buffering 1/d create_users_table
migration: 2024/05/26 21:20:07 mlog.go:22: Read and execute 2/d create_profiles_table
migration: 2024/05/26 21:20:07 mlog.go:22: Finished 2/d create_profiles_table (read 28.291992ms, ran 11.132556ms)
migration: 2024/05/26 21:20:07 mlog.go:22: Read and execute 1/d create_users_table
migration: 2024/05/26 21:20:07 mlog.go:22: Finished 1/d create_users_table (read 41.51291ms, ran 8.649414ms)
Embedded to Application
How to use still remain the same with Standalone Program, but you just need this err = mg.Action(*action) into err = mg.Action("UP") or err = mg.Action("DOWN"). based on your need. and put this code on initialization phase of your application
mg, err := database.NewMigration(database.MySQLBuilder, database.Config{
DatabaseHost: "localhost",
DatabasePort: "3306",
DatabaseName: "md_tutor",
DatabaseUser: "root",
DatabasePasw: "root",
DatabaseDrvr: "mysql",
DatabaseMdir: "database/migration",
})
if err != nil {
panic(err)
}
//err = mg.Action("DOWN")
err = mg.Action("UP")
if err != nil {
panic(err)
}
Behavior
This migration has behavior to be known before you use it. When it started up your migration it will execute all your SQL query on your migration files, also automatically create table named schema_migrations to track where migration version sequence at, and is it dirty? if dirty it means there is something wrong in your SQL query syntax and need to be fixed before it moves to next version sequence of your migration file. Here is the schema_migrations table structure.
Schema Migration Table
Your defined tables inside migration files
For this case current version sequence of your migration files is 2, try to look column version.
When you try to down the migration process all table dropped and leave you schema_migrations table without any rows.
When there is SQL syntax error while up or down your migration. You need manually edit version to which version sequence before version sequence that has SQL syntax error, and reset dirty to 0 again on schema_migrations table.
If column dirty is 0 means there is no syntax error on your migration files
If column dirty is 1 means there is syntax error on your migration files.
Conclusion
Database Migration helps you construct your database design in a complete new environment, and you also could add new table or modify table by generate new migration file and write SQL query for that and re-run the program.
You can also put this program inside your application so when the application start it will runs the migration process and ignore the migration process when there is no changes in migration files.
You have 2 option make this program as standalone program or you can embed it inside your application code.
Its up to you and based on your needs, for my personal I would make it as standalone program, where I can keep track database changes and no distraction from others, so when ever someone need a new changes, I just ask the final query for that changes.
If I embed it inside application, people who contribute in that application development will freely add changes and I think it is not so good, every addition and modification of database structure should be discussed before it applied. So again back to your needs. Make it standalone or embed it inside application.
My Thanks
Thank you for visiting! I hope you found it useful and enjoyable. Don't hesitate to reach out if you have any questions or feedback. Happy reading!
Top comments (0)