Running SQL Migrations Before Booting Docker Compose Services

01.10.2020 — Docker, SQL, Micro-Services — 3 min read

Having a great local development experience is critical to happy engineers. Developers are happy to come into a codebase and start hacking away at problems, as opposed to dreading picking up their laptops. As services become more and more separated into separate "micro-services", new problems arise. When I look back on my short career, one problem that has induced me a lot of pain are SQL Migrations and how they should work in Docker Compose. Ideally, they should run before your web apps boot so they have access to a well-structured database. Your strategy to make this happen may be different for each environment, in this post I'll cover your local experience which easily extends to CI and I'll also touch how to do this in production.

A Bit of Background

I ran into this problem when we started to adopt Apollo Federation into our stack. One gateway service lives in front of our other GraphQL services (which have their own databases). This gateway is what the client-side apps ("Web app" in diagram) queries to get its data. For clients to start using the gateway API every dependent service must be up and healthy.

My goal is to start the entire set of services in a deterministic manner, have each service wait until its dependent service is running and healthy. I'll focus on the database and migrations in this post but the concepts here extend to any service having dependencies on another service.

A typical Docker Compose file which runs the products service with it's migrations will look like this:

1version: "3.7"
2
3postgres:
4  image: yourorg/postgres
5  command: postgres -c 'max_connections=1024'
6  expose:
7    - "5432"
8  environment:
9    POSTGRES_USER: ${DATABASE_USERNAME}
10    POSTGRES_PASSWORD: ${DATABASE_PASSWORD}
11  volumes:
12    - ${YOUR_ORG_INSTALL_PATH}/volumes/services/postgres-data:/var/lib/postgresql/data:delegated
13
14products-run-migrations:
15  build:
16    context: ./apps/products/migrations/.
17  image: yourorg/products-run-migrations:${IMAGE_TAG:-latest}
18  env_file:
19    - ./apps/products/products.env
20  depends_on:
21    - postgres
22
23products:
24  build:
25    context: ./apps/products/.
26  image: yourorg/products:${IMAGE_TAG:-latest}
27  env_file:
28    - ./apps/products/products.env
29  depends_on:
30    - postgres
31    - products-run-migrations

A few issues with this setup, focusing on depends_on:

The products-run-migrations script will run right when postgres starts to run, not when it's healthy and ready to accept connections. There is a 99% chance that the postgres container will not be healthy when the migrations begin to run, causing them to fail.
Similar situation with the products service, it will run right when products-run-migrations starts to run. If you query the products service when it's healthy, there is a good chance the migrations have not yet completed.

What We Want

This is currently not what we want, ideally developers should be able to start the products service and when it's healthy, know that the migrations ran successfully and they can query its API.

This required me to do some research.

I found out there is a conditions argument to depends_on I could use to achieve the implementation we wanted. But the next thing I found out is where things went haywire.

We needed to downgrade our docker-compose version.

Turns out, Docker Compose 3.x versions are meant to be used for Docker Swarm and Kubernetes environments where services are not strictly dependent on each other. This promotes a more fault-tolerant environment where services run independently.

What I saw people suggesting was to switch to Docker Compose 2.4 and use a port waiting script like wait-for-it.

Our new Docker Compose 2.4 file:

1version: "2.4"
2
3postgres:
4  image: yourorg/postgres
5  command: postgres -c 'max_connections=1024'
6  expose:
7    - "5432"
8  environment:
9    POSTGRES_USER: ${DATABASE_USERNAME}
10    POSTGRES_PASSWORD: ${DATABASE_PASSWORD}
11  volumes:
12    - ${YOUR_ORG_INSTALL_PATH}/volumes/services/postgres-data:/var/lib/postgresql/data:delegated
13  healthcheck:
14    test: ["CMD-SHELL", "pg_isready -U root"]
15    interval: 60s
16
17products-run-migrations:
18  build:
19    context: ./apps/products/migrations/.
20  image: yourorg/products-run-migrations:${IMAGE_TAG:-latest}
21  entrypoint:
22    - /bin/bash
23    - -c
24    - "wait-for-it $$DATABASE_HOSTNAME:5432 -s -t 60 -- npm run db-migrate:products"
25  restart: on-failure:5
26  env_file:
27    - ./apps/products/products.env
28  depends_on:
29    postgres:
30      condition: service_healthy
31
32products:
33  build:
34    context: ./apps/products/.
35  image: yourorg/products:${IMAGE_TAG:-latest}
36  env_file:
37    - ./apps/products/products.env
38  restart: on-failure:5
39  depends_on:
40    postgres:
41      condition: service_healthy
42    products-run-migrations:
43      condition: service_started

A few new additions:

Added a healthcheck to the postgres container. This made it so migration scripts like products-run-migrations can start to run only when the database is ready to accept connections.
We added an entrypoint along with the condition arg to depends_on to our migration images. This made sure that not only the database is running but that the port is returning a 200 response code.
Added condition args to both depends_on in the products service definition.

It's not perfect but works much better than before. The issue still remains of the app not waiting for the migrations to be fully finished before booting up. If they run relatively quickly, local developers may not run into problems. In CI, we have full control over the environment so we can add an extra command to skirt around this issue.

1# .circleci/config.yml
2test-products:
3  executor: node-docker
4  steps:
5    - test-project:
6        project-name: products
7        pre-test-command: docker-compose run -T products-run-migrations
8        tests:
9          - run-project-test:
10              project-name: products

The Issues That Still Remain

What I didn't touch on is what our production environment looks like in terms of Docker Compose. We decided to maintain two versions of our Docker Compose files, one in 2.4 and one in 3.7. This is because we want to adopt Kubernetes easily in the future. You may stick with one or the other but for a great local development experience, we decided to always have a 2.4 file going.