Language and framework agnostic database migrations.
Tags: database, docker, tutorial, showdev
Published: 2022-09-13 12:16:12

It doesn't matter how simple your app is. If it needs a database, you will probably need a database (SQL) schema migration manager. I know other languages and frameworks bring schema management with the ORM, but what happens if you want to use plain SQL? I usually don't work with ORM.

We use [Migrate](https://github.com/golang-migrate/migrate) because Go is our default language in Typeform. The following tutorial could work for any language or framework because the CLI works with SQL files.

## The tool: Migrate

[Migrate](https://github.com/golang-migrate/migrate) is a CLI and Golang library to manage migrations. One key feature is that migrations are plain SQL files.

The defined principles of the library follow the [KISS](https://people.apache.org/~fhanik/kiss.html):

- Migrate reads migrations from sources and applies them in the correct order to a database.
- Drivers are "dumb" migrate glues everything together and makes sure the logic is bulletproof. (Keeps the drivers lightweight, too.)
- Database drivers don't assume things or try to correct user input. When in doubt, fail.

As it stands by the quoted principles, we will have:
- A folder with SQL files.
- Each migration is composed of two files: `up` and `down`.
- Migrate CLI (`migrate`), among others, have two main commands: `up` and `down`.

## The Setup: Your first migration

`migrate` the CLI tool brings a `create` command that will generate migrations files following a sequence of numbers.

```
docker run -v $PWD:/db migrate/migrate:v4.15.2 create -ext sql -dir db/migrations -seq create_users_table
```

The previous command will generate two SQL files inside `db/migrations` directory prefixed by `00001`.

```
> ls db/migrations
000001_create_users_table.down.sql
000001_create_users_table.up.sql
```

You can run it multiple times to see how the prefix increases:

```
> ls db/migrations
000002_create_users_table.down.sql
000002_create_users_table.up.sql
```

But if you delete existing files, it will generate `0001` again. Of course, you won't manually delete migration files, but **what happens if two dev are working at the same time and need a migration?**. They will generate conflict migrations (same sequence number). 

To mitigate this, although it doesn't fix it 100%, I generate migration files using epoch time as sequence number:

```
touch migrations/$(shell date '+%s')_$(name).up.sql
touch migrations/$(shell date '+%s')_$(name).down.sql
```

You now have a `db` folder with a `migrations` subfolder with two SQL files for one migration. To run the migration, you need to call `migrate`, but how do you make it reproducible for all environments?

## Delivery migrations: Docker

[Migrate projects builds its docker image](https://github.com/golang-migrate/migrate#docker-usage), so delivering migration is nuts, we create `db/Dockerfile`:

```
FROM migrate/migrate:v4.15.2

COPY . .

ENTRYPOINT ["/run.sh"]
```
1. Start from their image, fixed version.
2. Copy all content of the `db` folder to the image
3. Set `run.sh` as the default entrypoint

The `run.sh` is a handy script to grab DB connection data from the environment and run any migrate command.

```
#!/usr/bin/env sh

set -e

# Grab DB config

DB_HOST=${DB_HOST:?}
DB_NAME=${DB_NAME:?}
DB_PASSWORD=${DB_PASSWORD:?}
DB_PORT=${DB_PORT:?}
DB_TIMEOUT=${DB_TIMEOUT:-10}
DB_USER=${DB_USER:?}

# wait for the database to be ready
for i in $(seq $DB_TIMEOUT) ; do
  echo "I am waiting for db to be read => $DB_HOST:$DB_PORT"
  if ! nc -z $DB_HOST $DB_PORT > /dev/null 2>&1; then
    sleep 1
  else
    break
  fi
done

echo "Running migrations"

# For this example I am using PostgreSQL
DB_URL="postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}?sslmode=disable"

# Run the migrate cli include inside parent image.
migrate -verbose -path=./migrations -database=$DB_URL "$@"
```

Let's recap, inside the `db` directory, you have:
- `migrations` with two SQL files with a dummy create table
- `Dockerfile`, which defines an image starting from `migrate` and embedding `migrations`.
- `run.sh` is a handy script to wrap `migrate`.

## Run it: Docker Compose

Now that there is a Dockerfile, you can create a docker image to run anywhere. I will follow up with an example for a local setup using docker-compose.

```
version: '3'
services:
  database:
    image: 'postgres:14-alpine'
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: password
      POSTGRES_DB: localdb
    volumes:
      - database:/var/lib/postgresql/data

  migrations:
    build:
      context: .
    command:
      - "up"
    environment:
      - DB_HOST=database
      - DB_NAME=localdb
      - DB_PORT=5432
      - DB_USER=username
      - DB_PASSWORD=password
    depends_on:
      - database


volumes:
  database:
```

Notice how we set the environment for `migrations` with DB parameters that will be read by `run.sh`.

So, with all setup to run your migration, you just need to run:

```
docker-compose run migrations up
```

Now you can check that the migration is applied:

```
> psql -U username -W -h localhost localdb
Password: 
psql (14.4, server 14.5)
Type "help" for help.

localdb=# select * from schema_migrations;
 version | dirty 
---------+-------
       1 | f
(1 row)
```

You can also test down command or any other `migrate` command running:
```
docker-compose run migrations down 1
```

It will apply down file and clean `schema_migrations`.

## Recap

Migrate is an agnostic, easy-to-use yet powerful SQL schema migrations manager tool. You can set up the full flow with:
- A folder with the SQL files
- A docker image package with SQL with a proper entry point
- Config to run the docker image.

You can find all code in my github: https://github.com/jdvr/migrate-template

I hope you find useful this small tutorial. How do you usually manage your migrations?




[Photo by vishnudeep  dixit from Pexels](https://www.pexels.com/photo/flock-of-birds-on-water-1646139/)