Migrations in a deploy

In 1 minute

A migration is a SQL file that changes the database’s tables, for example adding a column. New code often needs it, so it must run BEFORE the new code serves requests. MiniTickets runs the new image’s migrations as a one-off ECS task, between the two phases of a deploy. If the migration fails, the deploy stops, and the running app was never touched.

Picture it

flowchart TB
  A["phase A: migration task<br/>definition → NEW image"] --> R["run-migrations.sh<br/>one task, FARGATE"]
  R --> M["node dist/migrate.js"]
  M -->|"pending SQL files,<br/>one transaction"| DB[("RDS Postgres")]
  M -->|"exit 0"| B["phase B: services roll"]
  M -->|"exit ≠ 0"| S["deploy stops<br/>old app keeps running"]

How it works

1. Its own task definition. minitickets-dev-migrate has its own image setting, so phase A can point it at the NEW image while the services keep the current one.
2. One task. scripts/run-migrations.sh starts it with launch type FARGATE, not Spot: a Spot interruption must never stop a migration halfway. It runs in the api’s subnets with the api security group, which the database allows on TCP 5432.
3. Apply. node dist/migrate.js runs every SQL file that has not run yet. Drizzle records the finished ones in a table, and applies all pending files inside ONE transaction.
4. Report. The script waits for the task to stop, prints its last 20 log lines, and exits with the container’s exit code. No exit code at all means the container never ran; the task’s stopped reason says why.

In MiniTickets

The task uses only the database settings (DB_HOST, DB_PORT, DB_NAME, and the user and password from the RDS master secret). It borrows the api execution role to read that secret and has no task role: a migration calls no AWS API. Its logs go to /minitickets/dev/api with the stream prefix migrate. The same script runs by hand as pnpm infra:migrate.

Because Postgres can change tables inside a transaction, a failing SQL file rolls back every file of that run. Infrastructure changes from phase A stay applied.

From → to	What happens	What allows it
Migration task definition → RDS Postgresonly when computeEnabled = true	runs node dist/migrate.js against the database	sg-rule the migration task runs with api-sg, which db-sg allows on TCP 5432run-migrations.sh secret DB_USER and DB_PASSWORD from the RDS master secretcreateMigrationTask
Migration task definition → API task execution roleonly when computeEnabled = true	the migration borrows the api execution role (no task role)	iam trust policy: ecs-tasks.amazonaws.com may sts:AssumeRole, only when aws:SourceAccount is our accountcreateIamRoles
CD deploy job → Migration task definitiononly when computeEnabled = true	runs the migration task (scripts/run-migrations.sh) with launch type FARGATE	iam deploy role StackServices ecs:* and PassOurRolesToEcsTasksAndLambda: iam:PassRole on minitickets-dev-* to ecs-tasks.amazonaws.comcreateCicd

See the migration task on the map →

See it in AWS

Console

ECS → Task definitions → minitickets-dev-migrate; the run’s lines in CloudWatch → /minitickets/dev/api, streams starting with migrate/.

CLI

aws ecs describe-task-definition --task-definition minitickets-dev-migrate

Code

infra/src/compute/migration-task.ts → createMigrationTask and scripts/run-migrations.sh.

Break it

Symptom	Cause	Where to look
CD stops at “Run the database migration”; the old app still runs	A SQL statement failed; phase B never ran	The step’s last 20 log lines
The task stops with no exit code	The image could not be pulled, or the secret not read	The task’s stopped reason
Someone proposes Spot for migrations	An interruption could stop it halfway	run-migrations.sh uses FARGATE on purpose

Say it in an interview

Say it like this (20-30 seconds)

Migrations run between the two phases of a deploy. Phase A points a separate migration task definition at the new image, then a script runs it as one Fargate task, not Spot, in the api’s security group. It applies all pending SQL files in one transaction and exits with its code. A failure stops the deploy before the services roll, so the old version keeps serving. That only works if every migration stays compatible with the old code.

Follow-ups

Why must migrations be backward compatible?

The old code runs against the new schema until phase B finishes, and a later app rollback runs old code against it too. So add first (expand), switch the code, and remove later (contract).

Why not run migrations when the api starts?

Several api tasks starting together would all migrate at once, and a failed migration would crash every new task instead of stopping the deploy cleanly.

What cannot run inside the transaction?

Some statements, such as CREATE INDEX CONCURRENTLY, refuse to run in a transaction. They would need their own migration step, planned separately.

Advanced: Details: why the api execution role

Reusing the api execution role means the migration could also read the Redis and revalidate secrets, which it does not need. The trade-off was accepted to avoid one more role. The task asks for 0.25 vCPU and 512 MB, on ARM64.

Checkpoint

Answer these without scrolling up. If one feels hard, reread that section.

1. Why does the migration have its own task definition?
2. Why FARGATE and not Spot?
3. What happens to the running app when a migration fails?
4. How many transactions does one migration run use?
5. Why must a migration stay compatible with the old code?

Redraw it from memory: phase A (migration task def → new image) → run-migrations.sh (FARGATE, api SG) → migrate.js → Postgres → exit 0: phase B / else: stop

Try this: on the map, select the migration task definition and find which security group lets it reach Postgres

Mark this page as read