Database Migrations

With the database running, we need to create the schema. We'll manage migrations using migrate, running it as a Docker Compose service so it starts automatically and only runs after the database is ready.

Two new migration files and an updated compose.yml:

doable/
├── compose.yml                    # adds healthcheck and migrate service
└── migrations/
    ├── 1_create_tasks.up.sql      # creates the tasks table
    └── 1_create_tasks.down.sql    # drops the tasks table

Migration Files

Create the migrations/ directory at the project root and add the first migration:

migrations/1_create_tasks.up.sql

-- migrations/1_create_tasks.up.sql

CREATE TABLE tasks (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR NOT NULL,
  description TEXT NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

migrations/1_create_tasks.down.sql

-- migrations/1_create_tasks.down.sql

DROP TABLE tasks;

Each migration is a pair of files — .up.sql to apply the change and .down.sql to roll it back. The file names follow migrate's official naming convention: {version}_{title}.{direction}.sql. The numeric prefix determines the order migrations run in.

Updating Docker Compose

The migrate service needs to wait until Postgres is actually ready to accept connections — not just started. Add a healthcheck to the db service and a new migrate service to compose.yml^[1]:

# compose.yml

name: doable-dev

services:
  db:
    image: postgres:18-alpine
    restart: unless-stopped
    shm_size: 128mb
    environment:
      POSTGRES_PORT: ${PGPORT}
      POSTGRES_USER: ${PGUSER}
      POSTGRES_PASSWORD: ${PGPASSWORD}
      POSTGRES_DB: ${PGDATABASE}
    ports:
      - ${PGPORT}:${PGPORT}
    volumes:
      - data:/var/lib/postgresql
    healthcheck:
      test: "pg_isready -U ${PGUSER} -d ${PGDATABASE}"
      interval: 1s
      timeout: 2s
      retries: 10

  migrate:
    image: migrate/migrate
    volumes:
      - ./migrations:/migrations
    command: >
      -path /migrations
      -database postgres://${PGUSER}:${PGPASSWORD}@${PGHOST}:${PGPORT}/${PGDATABASE}?sslmode=disable
      up
    depends_on:
      db:
        condition: service_healthy

volumes:
  data:
    name: doable-dev-data

networks:
  default:
    name: doable-dev-network

A few things worth noting:

• pg_isready — the healthcheck uses the Postgres built-in utility to probe the TCP port and verify the server is accepting connections.
• depends_on: condition: service_healthy — makes migrate wait until the db healthcheck passes, not just until the container is running.
• migrate/migrate — the official Docker image for golang-migrate; no installation needed.
• ./migrations:/migrations — mounts the local migrations directory into the container so the tool can find the SQL files.
• sslmode=disable — local dev doesn't use TLS; this tells the Postgres driver not to require it.

Running Migrations

Restart Docker Compose to pick up the changes:

docker compose up -d
# [+] up 4/4
#  ✔ Network doable-dev-network     Created
#  ✔ Volume doable-dev-data         Created
#  ✔ Container doable-dev-db-1      Healthy
#  ✔ Container doable-dev-migrate-1 Started

The migrate service will run, apply 1_create_tasks.up.sql, and exit. You can check its output with:

docker compose logs migrate
# migrate  | 1/u create_tasks (Xms)   # first run — migration applied
# or
# migrate  | no change                # subsequent runs — nothing to do

The migrate service is not configured with restart: unless-stopped, so it runs once and stops; docker compose up will start it again, but reruns are safe.

If migrate exits with an error

The healthcheck only applies to new containers. If your db container was already running from chapter 2, the healthcheck isn't attached yet and migrate may race it. Run docker compose down && docker compose up -d to recreate both containers with the new config.

docker ps -a
# CONTAINER ID   IMAGE                COMMAND                  CREATED          STATUS                      PORTS                                         NAMES
# aecf90bc4b8d   migrate/migrate      "migrate -path /migr…"   17 seconds ago   Exited (0) 14 seconds ago                                                 doable-dev-migrate-1
# ea3d96689376   postgres:18-alpine   "docker-entrypoint.s…"   17 seconds ago   Up 16 seconds (healthy)     0.0.0.0:5432->5432/tcp, [::]:5432->5432/tcp   doable-dev-db-1

INFO

To stop the stack, run docker compose down or if you want to start fresh — for example to re-run the init script — add -v to also remove the named volume and wipe the data:

docker compose down     # stop containers, keep data
# [+] down 3/3
#  ✔ Container doable-dev-migrate-1 Removed
#  ✔ Container doable-dev-db-1      Removed
#  ✔ Network doable-dev-network     Removed
# or
docker compose down -v  # stop containers, delete volume (data is lost)
# [+] down 4/4
#  ✔ Container doable-dev-migrate-1 Removed
#  ✔ Container doable-dev-db-1      Removed
#  ✔ Network doable-dev-network     Removed
#  ✔ Volume doable-dev-data         Removed

Verifying the Schema

Connect to the database:

docker compose exec db psql -U doable-user-dev -d doable-dev

Insert a task, mark it as completed, then delete it:

INSERT INTO tasks (name, description)
VALUES ('Buy groceries', 'Milk, eggs, bread')
RETURNING *;

 id |     name      |    description    | completed |         created_at         |         updated_at
----+---------------+-------------------+-----------+----------------------------+----------------------------
  1 | Buy groceries | Milk, eggs, bread | f         | 2026-04-02 10:00:00.000000 | 2026-04-02 10:00:00.000000
(1 row)

UPDATE tasks SET completed = TRUE WHERE id = 1 RETURNING *;

 id |     name      |    description    | completed |         created_at         |         updated_at
----+---------------+-------------------+-----------+----------------------------+----------------------------
  1 | Buy groceries | Milk, eggs, bread | t         | 2026-04-02 10:00:00.000000 | 2026-04-02 10:00:00.000000
(1 row)

DELETE FROM tasks WHERE id = 1;

DELETE 1

Type \q to exit.

What's Next

The tasks table exists, but nothing in Gleam knows how to query it yet. Next, we'll write plain .sql files and let Squirrel generate type-safe Gleam functions from them — no ORM, no string concatenation.

---

1. See commit 0e3c9ae on GitHub ↩︎