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.

• Link to the full series playlist on Youtube
• And its Github repository

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

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.

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

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

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.

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.

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.

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.

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';

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;

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

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

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

Now we see our postgres container with status exited.

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

docker start postgres12

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

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

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

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

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

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

dropdb simple_bank

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

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

docker exec -it postgres12 psql -U root simple_bank

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

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

docker stop postgres12

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

docker rm postgres12

Now when we run

make postgres

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

make createdb

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.

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

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.

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

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.

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

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

make migratedown

And go back to TablePlus and refresh.

All tables are gone, except for the schema_migrations table.

OK, now let’s run:

make migrateup

Then refresh TablePlus.

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.