Database Migration: Take Care of Your Database Changes

Nightsilver Academy - May 27 - - Dev Community

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.

Migration Directory

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
Enter fullscreen mode Exit fullscreen mode

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.

Download Options

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

migrate create -ext sql -dir database/migration -seq create_users_table
migrate create -ext sql -dir database/migration -seq create_profiles_table
Enter fullscreen mode Exit fullscreen mode

When you take a look in the project it will be looks like this
Migration Directory

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 

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);
Enter fullscreen mode Exit fullscreen mode

000001_create_users_table.down.sql 

DROP TABLE users;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

000001_create_profiles_table.down.sql 

DROP TABLE profiles;
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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)
    }
}
Enter fullscreen mode Exit fullscreen mode

And let's compile it into executable binary named migrator

go build -o migrator main.go
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
DOWN 
./migrator -action DOWN
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
    }
Enter fullscreen mode Exit fullscreen mode

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

Your defined tables inside migration files
Created Users and Profiles Table

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

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!

Image
Are The Advances In Technology Making Attorney For Asbestos Better Or Worse?
Image
Part 4 - Using Cursor and Claude to Create Automated Tests with Playwright

python, cursor, playwright, testing
Image
10 Places To Find Asbestos Litigation Online
Image
Introduction to the event "TinyGo Keeb Tour 2024"

go, tinygo, keyboard
Image
What Experts Say You Should Know
Image
10 Unexpected Asbestos Lawsuit Tips
Image
10 Asbestos Exposure Lawsuit Settlements Tricks All Experts Recommend
Image
mendalami house edge dalam sebuah slots
Image
spesifikasi penting di website slots online memiliki kualitas
Image
What Is How To Install Ghost Immobiliser And How To Utilize What Is How To Install Ghost Immobiliser And How To Use
Image
🏗️ Building a Simple Load Balancer with Spring Boot: A Step-by-Step Guide
Image
Pedoman Pasang Taruhan Judi Online Amat Ringan
Image
Installing the best terminal emulator on Pop!_OS 22.04: Terminator

ubuntu, popos, linux
Image
Single Oven Under Counter
Image
Best Single Oven For Energy Efficient Cooking
Image
In che modo la tua esperienza personale può creare nuove possibilità professionali
Image
A Productive Rant Concerning Electric Wall Mounted Fire Place
Image
tehnik menunjuk mesin slots online uang asli
Image
Why No One Cares About Ghost Immobiliser Installation
. . . . . . .