Database Migration: Take Care of Your Database Changes
Tags: database, tutorial, go, architecture
Published: 2024-05-27 03:47:38

### 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](https://github.com/golang-migrate/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](https://github.com/golang-migrate/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](https://github.com/xoxoist/dm-tutor), 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.

![Migration Directory](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/81myavfiaa2tg46lkwa9.png)

---

### 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.

```bash
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](https://github.com/golang-migrate/migrate/releases/tag/v4.17.1).

![Download Options](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/br5ei711uqlf04m0do3p.png)

---

### Generate Migration Files
Once already download `migrate` binary file and register it to your environment path, you can check it by running this command.
```bash
migrate --version
```
It will show you which `migrate` version did you use.

For generate migration files you can use this command
```bash
migrate create -ext sql -dir path/to/migration/dir -seq migration_file_name
```
Explanation
- `-ext` This arg is to tell `migrate` to use `sql` extension
- `-dir` This arg is to tell `migrate` where migration files at
- `-seq` This arg is to tell `migrate` to use sequential numbering for every migration file you generate this is the sample `000001_create_users_table.up.sql` and `000001_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.
```bash
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
![Migration Directory](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/81myavfiaa2tg46lkwa9.png)

What is difference between `up` and `down` migration file?
- `up` This migration file will does construction for your database
- `down` This 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`
```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`
```sql
DROP TABLE users;
```

#### Profiles Table Migration Files
#### `000001_create_profiles_table.up.sql`
```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`
```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.
```go
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.
```go
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.
```go
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.
```go
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`
```bash
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
```bash
./migrator -action UP
```
Response for UP action
```bash
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
```bash
./migrator -action DOWN
```
Response for DOWN action
```bash
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
```go
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
![schema_migrations structure table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xirv2m433jhw7oq0vo6j.png)

Your defined tables inside migration files
![Created Users and Profiles Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0awmqmidnub4l4pvs3f0.png)

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.

![Down Migration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vkwjiygzse0t5rocadjx.png)

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!