It is common for web application having a database and using concept of migration already considered as best practice. Instead of creating database schema directly to database, migration script created so we could control how we manage the schema programmatically and also would get benefit on CI/CD process.

There are several migration tools for Go but now let’s learn how to use Golang Migrate to manage migration in Go.

Preparation

• Database server in this post, I am using PostgreSQL

• For Windows, make sure it already installed Scoop

• Go

• Terminal

CLI Installation

Make sure Scoop already been installed and In your Windows terminal run this command to install migrate

scoop install migrate

Once it done, run command migrate in Windows terminal. If everything is good, then it will indicated CLI correctly installed.

For other OS installation can be check on the instruction detail https://github.com/golang-migrate/migrate/tree/master/cmd/migrate

Create Migration

We need a directory for storing the migration files. Create folder migrations in your Go project directory. You may create folder database/migrations or db/migrations depends on your preferred structure.

Go to your terminal and run the migrate command to start create migration files

 migrate create -ext sql -dir db/migrations -seq create_urls_table

• create option is to create migration files

• -ext sql is to create migration files with .sql extension

• -dir db/migrations path for migrations file will be stored

• -seq create_shortens_table will generate up/down migrations for your table schema file sequentially

You may adjust the command and arguments based on your project structure

Generated files should looks like this

Now you just need to write SQL statement to create / altering / drop the table schema. Here’s sample of mine for PostgreSQL:

// 000001_create_urls_table.up.sql
CREATE TABLE IF NOT EXISTS urls(
    id BIGSERIAL PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE,
    original_url VARCHAR(255) NOT NULL,
    user_id BIGINT NULL,
    visit_count INT DEFAULT 0 NULL,
    created_at timestamp DEFAULT current_timestamp,
    updated_at timestamp NULL,
    deleted_at timestamp NULL
);

// 000001_create_urls_table.down.sql
DROP TABLE IF EXISTS urls;

To maintain idempotent, always check if the schema exists or not

Running Migration

Migration files up/down already created meaning you will able to create the table or drop the table using recent migration files. Before running the migration, make sure you already setup the database because it will need database URL to run.

My URL for PostgreSQL is like below

postgres://postgres:securepassword@localhost:5432/mydatabase?sslmode=disable

// convention
driver://username:pass@host:port/dbname?sslmode=disable

Then we can now run up migration to create new table schema

migrate -database postgres://postgres:securepassword@localhost:5432/mydatabase?sslmode=disable -source file://db/migrations up

• -database tells migrate to use particular database server

• -source locates migration files path which is in db/migrations directory

• up tells migrate to run 000001_create_urls_table.up.sql

When we need to revert the migration, we can use down option to drop the table schema

migrate -database postgres://postgres:securepassword@localhost:5432/mydatabase?sslmode=disable -source file://db/migrations down

Other command can be referred to this https://github.com/golang-migrate/migrate/tree/master/cmd/migrate

Getting Database Value From YAML File

Our project often uses config file to store the database configuration. In my case, it stored on YAML file config.yml. In Migrate’s Github there’s example to do that.

First, install Python and pip. Then install pyyaml library for reading the yaml file.

 pip install pyyaml

Now we can modify our migration command to

migrate -database "$(cat config.yml | python -c "import yaml,sys; print(yaml.safe_load(sys.stdin)['database'])")" -source file://db/migrations up

It basically using same structure as previous command but now we read yaml file and parsing the database to get the URL value.

That’s it. Happy coding!