DEV Community

Cover image for How to write & run database migration in Golang
TECH SCHOOL
TECH SCHOOL

Posted on • Updated on

How to write & run database migration in Golang

When working with database, schema migration is one important task that we often have to do throughout the application lifetime to adapt to new business requirements.

In this lecture, we will learn how to write and run database schema migration in Golang using golang-migrate library.

Install golang-migrate

Golang-migrate works with many different database engines like postgres, mysql, mongo, cockroach, etc.

Let’s open this CLI documentation to see how to install it. I’m on a mac, so I will use Homebrew.

brew install golang-migrate
Enter fullscreen mode Exit fullscreen mode

Migrate gives us several commands:

  • The first one is create, which we can use to create new migration files.
  • The 2nd one is goto, which will migrate the schema to a specific version.
  • Then the up or down commands to apply all or N up or down migrations.

There are several more commands, but in most of the time, we will work with create, up, and down.

Alright, migrate is successfully installed. We can run migrate -help to read its manual.

migrate-help

Create a new migration

OK, now I’m gonna create a new folder for our Simple Bank project. And inside, I will create a new folder db/migration to store all of our migration files.

cd ~/Projects/techschool
mkdir simple_bank
cd simple_bank
mkdir -p db/migration
Enter fullscreen mode Exit fullscreen mode

Then let’s create the 1st migration file to initialise our Simple Bank’s database schema.

Start with migrate create. Then the extension of the file will be sql, and the directory to store it is db/migration.

migrate create -ext sql -dir db/migration -seq init_schema
Enter fullscreen mode Exit fullscreen mode

We use the -seq flag to generate a sequential version number for the migration file. And finally the name of the migration, which is init_schema in this case.

migrate-create

As you can see, 2 migration files have been generated for us. They both have version 1 in the file name’s prefix, but their suffixes are different: 1 file is up and the other is down. Why?

Up/down migration

Well, basically it’s a best practice when writing database migration. The up script is run to make a forward change to the schema. And the down script is run if we want to revert the change made by the up script.

up/down migrations

So when we run migrate up command, The up script files inside db/migration folder will be run sequentially by the order of their prefix version.

migrate-up

On the contrary, when we run migrate down command, the down-script files inside db/migration folder will be run sequentially by the reverse order of their prefix version.

migrate-down

Aright, now let’s open the simple_bank.sql file that we’ve generated in previous lectures. I’m gonna copy all content of this file and paste it to the init_schema.up.sql file.

CREATE TABLE "accounts" (
  "id" bigserial PRIMARY KEY,
  "owner" varchar NOT NULL,
  "balance" bigint NOT NULL,
  "currency" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "entries" (
  "id" bigserial PRIMARY KEY,
  "account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "transfers" (
  "id" bigserial PRIMARY KEY,
  "from_account_id" bigint NOT NULL,
  "to_account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");

CREATE INDEX ON "accounts" ("owner");

CREATE INDEX ON "entries" ("account_id");

CREATE INDEX ON "transfers" ("from_account_id");

CREATE INDEX ON "transfers" ("to_account_id");

CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");

COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive';

COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';
Enter fullscreen mode Exit fullscreen mode

For the init_schema.down.sql file, we should revert the changes made by the up script. In this case, the up script creates 3 tables: accounts, transfers, and entries. So the down script should remove all of them. We use the DROP TABLE query for this purpose.

DROP TABLE IF EXISTS entries;
DROP TABLE IF EXISTS transfers;
DROP TABLE IF EXISTS accounts;
Enter fullscreen mode Exit fullscreen mode

Here we drop entries and transfers table before dropping the accounts table because there’s a foreign key constraint in entries and transfers that references accounts records.

OK, so now our migration scripts are ready. Let’s try to run them.

Check postgres container status

But before that, we should check if our postgres container is still running or not:

docker ps
Enter fullscreen mode Exit fullscreen mode

By the way, I’m gonna show you some more docker commands to work with containers. If we want to stop a running container, we use docker stop with the container name or ID.

docker stop postgres12
Enter fullscreen mode Exit fullscreen mode

After this, if we run docker ps, we won't see the postgres container anymore because it’s not running. To list all containers, regardless of their running status, we can run:

docker ps -a
Enter fullscreen mode Exit fullscreen mode

Now we see our postgres container with status exited.

docker-ps-a

To turn it back on, We just need to run: docker start and pass in the container name or ID.

docker start postgres12
Enter fullscreen mode Exit fullscreen mode

Then here we go, the postgres12 container is now up and running.

docker-start

Access postgres container shell

We can access its shell with the docker exec command. As we’re using postgres alpine image, we don’t have /bin/bash shell as in ubuntu, so we use /bin/sh shell instead:

docker exec -it postgres12 /bin/sh
Enter fullscreen mode Exit fullscreen mode

Inside the shell, we have access to all standard linux commands.

access-shell

And since this is a postgres container, it also gives us some CLI commands to interact with postgres server directly from the shell.

Create/drop database inside postgres container

We have to create the database before we can run the 1st migration.

So let’s run the createdb command inside the postgres container's shell to create a new database for our Simple Bank:

createdb --username=root --owner=root simple_bank
Enter fullscreen mode Exit fullscreen mode
  • We use the --username option to say that we’re connecting as root user.
  • And the --owner option to say that the database we’re going to create will belong to root user as well.
  • The last argument is the database name: simple_bank.

OK, the database is created. We can access its console with the psql command.

access-db-console

We can also delete the database using dropdb command and pass in the name of the database.

dropdb simple_bank
Enter fullscreen mode Exit fullscreen mode

We use exit command to get out of the container shell.

Create/drop database outside postgres container

Now from outside of the container, we can also run createdb directly with the docker exec command.

docker exec -it postgres12 createdb --username=root --owner=root simple_bank
Enter fullscreen mode Exit fullscreen mode

And access the database console without going through the container shell.

docker exec -it postgres12 psql -U root simple_bank
Enter fullscreen mode Exit fullscreen mode

Write Makefile

OK, now I’m gonna create a Makefile in our project, then add a createdb command to create the simple bank database, and a dropdb command to delete it.

When working in a team, these commands will be useful for your team mates to easily setup the project on their local machine for development.

Let’s add the command that we used to start postgres container in the previous lecture to the Makefile as well.

postgres:
    docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:12-alpine

createdb:
    docker exec -it postgres12 createdb --username=root --owner=root simple_bank

dropdb:
    docker exec -it postgres12 dropdb simple_bank

.PHONY: postgres createdb dropdb
Enter fullscreen mode Exit fullscreen mode

Alright, Now let’s stop the current postgres container.

docker stop postgres12
Enter fullscreen mode Exit fullscreen mode

The container is stopped. I’m gonna remove it completely using docker rm command.

docker rm postgres12
Enter fullscreen mode Exit fullscreen mode

Now when we run

make postgres
Enter fullscreen mode Exit fullscreen mode

A new postgres container will be started. And we can run

make createdb
Enter fullscreen mode Exit fullscreen mode

to create the simple_bank database.

View database with TablePlus

OK, the database is created. Let’s connect to it using TablePlus.

The connection that we’ve setup in the previous lecture will bring us to the root database. We can click on the database icon to open our new simple_bank database.

table-plus-2db

Alright, now you can see 2 databases here: root and simple_bank. For now the simple_bank database is empty. So let’s go back to the terminal and run the first migration.

Run the migration

Start with migrate. Then we use the -path option to specify the folder contains our migration files, which is db/migration.

migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank" -verbose up
Enter fullscreen mode Exit fullscreen mode

The -database option is used to specify the URL to the database server.

  • We’re using postgres, so the driver name is postgresql.
  • Then the username is root
  • The password is secret
  • The address is localhost, port 5432.
  • And the database name is simple_bank.

We use -verbose option to ask migrate to print verbose logging.

And finally we use the up argument to tell migrate to run migrate up command.

ssl-error

Oh, we’ve got an error: SSL is not enabled on the server. That’s because our postgres container doesn’t enable SSL by default.

So we should add sslmode=disable parameter to the database URL. Now run this command:

migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up
Enter fullscreen mode Exit fullscreen mode

And the migration is successful!

If we refresh the simple bank database in TablePlus, we can now see 4 tables: accounts, entries, transfers, and schema_migrations.

Alt Text

The schema_migrations table stores the latest applied migration version, which in our case is version 1, because we have run only 1 single migration file.

The dirty column tells us if the last migration has failed or not. If it fails, we must manually fix the issues to make the database state clean before trying to run any other migration versions.

Add migrate up/down to the Makefile

OK, now I’m gonna add the migrate up and migrate down command to the Makefile:

postgres:
    docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:12-alpine

createdb:
    docker exec -it postgres12 createdb --username=root --owner=root simple_bank

dropdb:
    docker exec -it postgres12 dropdb simple_bank

migrateup:
    migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up

migratedown:
    migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose down

.PHONY: postgres createdb dropdb migrateup migratedown
Enter fullscreen mode Exit fullscreen mode

Now let’s try them in the terminal! First I will run:

make migratedown
Enter fullscreen mode Exit fullscreen mode

And go back to TablePlus and refresh.

1-table

All tables are gone, except for the schema_migrations table.

OK, now let’s run:

make migrateup
Enter fullscreen mode Exit fullscreen mode

Then refresh TablePlus.

all-tables

All tables are back again. Excellent!

So that wraps up today’s lecture about database migration. Thank you for reading and see you in the next lecture!


If you like the article, please subscribe to our Youtube channel and follow us on Twitter for more tutorials in the future.


If you want to join me on my current amazing team at Voodoo, check out our job openings here. Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.

Top comments (14)

Collapse
 
lil5 profile image
Lucian I. Last

Please use go install github.com/golang-migrate/migrate/v4 and add go’s bin directory to your PATH. Using brew for a go package doesn’t sound like a good idea, considering version differences

Collapse
 
thewackyindian profile image
Raj Karan Singh

I am on windows and did this, like ran this command on the terminal, it ran and installed it and said package is not a main package
then i ran the migrate --version command
and its not able to find it like not installed or something, confused with gobin and gopath.

Collapse
 
lil5 profile image
Lucian I. Last • Edited

I think your first problem is that you’re running Windows but further you might want to add your ~/go/bin directory to your PATH env in bashrc

Collapse
 
athulmuralidhar profile image
Athul Muralidhar

cant stress this one enough

Collapse
 
sergeypodgornyy profile image
Sergey Podgornyy

You can also try using migrator package to write migrations using Go

GitHub logo larapulse / migrator

MySQL database migrator

MySQL database migrator

Build Status Software License codecov Go Report Card GoDoc Mentioned in Awesome Go Release TODOs

MySQL database migrator designed to run migrations to your features and manage database schema update with intuitive go code. It is compatible with the latest MySQL v8.

Installation

To install migrator package, you need to install Go and set your Go workspace first.

  1. The first need Go installed (version 1.13+ is required), then you can use the below Go command to install migrator.
$ go get -u github.com/larapulse/migrator
Enter fullscreen mode Exit fullscreen mode
  1. Import it in your code:
import "github.com/larapulse/migrator"
Enter fullscreen mode Exit fullscreen mode

Quick start

Initialize migrator with migration entries:

var migrations = []migrator.Migration{
    {
        Name: "19700101_0001_create_posts_table"
        Up: func() migrator.Schema {
            var s migrator.Schema
            posts := migrator.Table{Name: "posts"}

            posts.UniqueID("id")
            posts.Varchar("title", 64)
            posts.Text("content", false)
            posts.Timestamps()

            s.CreateTable(posts
Enter fullscreen mode Exit fullscreen mode
Collapse
 
techschoolguru profile image
TECH SCHOOL

Thanks for sharing, Sergey!

Collapse
 
acetorscode profile image
Apoorv Mishra

Getting this error while migratedown

"Dirty database version -1. Fix and force version."

can anyone help with a solution! it will be a great help if you can.

Collapse
 
mquanit profile image
Mohammad Quanit

Hi Apoorv, try this cmd first

migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" force 1

and then run makefile cmd

make migrateup

it should solve this issue. Here's the docs for it
github.com/golang-migrate/migrate/...

Collapse
 
covrom profile image
Roman Сovanyan
Collapse
 
ridhs193 profile image
Ridham Modh

when i type run make migratedown in terminal i am getting following error:
xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/CommandLineTools/usr/bin/xcrun

I am not getting the issue!

Collapse
 
utamori profile image
森 優太 mori

How do I seed the DB in this example?

Collapse
 
techschoolguru profile image
TECH SCHOOL

Hi Mori, please check out lecture 4 and 5 of this series. You will see how to generate codes to insert data to the database, and also how to generate random data for seeding or testing.

Collapse
 
utamori profile image
森 優太 mori

Thank you

Collapse
 
abramq profile image
abramq • Edited

There is probably error on the orange picture "MIGRATE DOWN" - files shown there should be ...down.sql not ...up.sql
Right?