How to write & run database migration in Golang
Tags: go, database, tutorial, beginners
Published: 2020-07-10 17:16:39

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](https://github.com/golang-migrate/migrate) library.

{% youtube 0CYkrGIJkpw %}

- Link to the [full series playlist on Youtube](https://bit.ly/backendmaster)
- And its [Github repository](https://github.com/techschool/simplebank)

## Install golang-migrate

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

Let’s open [this CLI documentation](https://github.com/golang-migrate/migrate/tree/master/cmd/migrate) to see how to install it. I’m on a mac, so I will use Homebrew.

```bash
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.

![migrate-help](https://dev-to-uploads.s3.amazonaws.com/i/zlc7u7javz1fcvja5a4j.png)

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

```bash
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`.

```bash
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.

![migrate-create](https://dev-to-uploads.s3.amazonaws.com/i/k6q2ptxpxdwtk4oyewof.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/7a1ail02bmla1uf51j4s.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/si2z26vqmo62miregkk6.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/9v5vsgabledu35oc3rg7.png)

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.

```sql
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.

```sql
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:

```bash
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.

```bash
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:

```bash
docker ps -a
```

Now we see our `postgres` container with status `exited`. 

![docker-ps-a](https://dev-to-uploads.s3.amazonaws.com/i/x9us9i1722tgfv1lhk3b.png)

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

```bash
docker start postgres12
```

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

![docker-start](https://dev-to-uploads.s3.amazonaws.com/i/i9537ji1i0r1k0jj6pcg.png)

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

```bash
docker exec -it postgres12 /bin/sh
```

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

![access-shell](https://dev-to-uploads.s3.amazonaws.com/i/uampgev7odq2t67fw9me.png)

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:

```bash
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. 

![access-db-console](https://dev-to-uploads.s3.amazonaws.com/i/j32zemncvf89zsgmqscs.png)

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

```bash
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.

```bash
docker exec -it postgres12 createdb --username=root --owner=root simple_bank
```

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

```bash
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.

```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

.PHONY: postgres createdb dropdb
```

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

```bash
docker stop postgres12
```

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

```bash
docker rm postgres12
```

Now when we run

```bash
make postgres
```

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

```bash
make createdb
```

to create the `simple_bank` database.

## View database with TablePlus

OK, the database is created. Let’s connect to it using [TablePlus](https://tableplus.com/).

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](https://dev-to-uploads.s3.amazonaws.com/i/ts68d13wwjxggrt83rjc.png)

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

```bash
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.

![ssl-error](https://dev-to-uploads.s3.amazonaws.com/i/pvuoxe9s61pndkpdg67s.png)

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:

```bash
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`.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/o9ws42vgqlpsxj9vf0lu.png)

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:

```bash
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:

```bash
make migratedown
```

And go back to TablePlus and refresh.

![1-table](https://dev-to-uploads.s3.amazonaws.com/i/ki2n6fl83bjim2ppdi46.png)

All tables are gone, except for the `schema_migrations` table.

OK, now let’s run:

```bash
make migrateup
```

Then refresh TablePlus.

![all-tables](https://dev-to-uploads.s3.amazonaws.com/i/6hansa2xyfp9is4wjgcp.png)

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](http://bit.ly/techschoolguru) and [follow us on Twitter](https://twitter.com/techschoolguru) 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](https://bit.ly/voodoojobs). Remote or onsite in Paris/Amsterdam/London/Berlin/Barcelona with visa sponsorship.