Schema migrations on Aurora DSQL: every gap is yours

September 25, 2026 Aurora DSQLNext.jsAWSServerless

MCA Benches has 173 schema migrations. The first one went in on June 3 and the latest on September 17, every one of them ran against a production Aurora DSQL cluster, and CDK ran none of them. They run from my machine, with one command, once the code is ready.

This was supposed to be a post about wiring migrations into a CDK deploy. Halfway through writing it I noticed the wiring came to about forty lines, and the rest of my notes were about something else: the stretches of time when the schema and the code talking to it disagree. Automating the migration leaves every one of those in place. You just stop watching them happen. So the gaps come first, and CDK comes at the end.

A migration and a deploy never happen at the same moment

On Vercel, or anywhere a deploy feels atomic, “ship the schema change” and “ship the code that uses it” blur into one event. AWS shows you the seam. The migration runs at one point. The Lambda code gets replaced at another, requests already in flight on the old code finish on the old code, and CloudFront keeps handing out cached pages built by the old code until their cache lifetime runs out.

For some window, then, old code runs against the new schema. Migrate first and that window is every request until the new code is live. Deploy first and you get the reverse, new code against the old schema, which usually hurts more because new code is exactly what reads the column the migration hasn’t added yet.

The schema-change literature calls the way out expand and contract:

Expand: add the new column, table or index in a way the old code won’t notice, and deploy the migration before the code.
Migrate the data: backfill whatever the new code expects to find.
Switch: deploy the code that uses it.
Contract: in a later deploy, once nothing reads the old thing, remove it.

On AWS this is the only order that works, and an ORM like Drizzle makes it stricter than it looks. Drizzle’s select() names every column in your schema explicitly instead of asking for *, so the old code asks for a dropped column by name and the query fails. Drop a column the old code knows about and you have an outage that lasts as long as the old code runs.

On DSQL, a migration isn’t atomic with itself

Single-node Postgres cushions all of this with transactional DDL. Put three ALTER TABLEs in one transaction and they land together or not at all.

Aurora DSQL has no such cushion. A transaction can contain only one DDL statement, and DDL and DML can’t share a transaction. The first time this bit me, local testing gave no warning at all. A migration file with two ALTER TABLEs ran fine against the Postgres in Docker, then failed against the cluster with multiple ddl statements not supported in a transaction, because pg wraps a multi-statement query in an implicit transaction.

So the runner splits every file into statements and sends them one at a time:

const statements = splitStatements(sql)
for (const [i, statement] of statements.entries()) {
  try {
    await client.query(statement)
  } catch (err) {
    console.error(
      `FAILED ${file} at statement ${i + 1}/${statements.length}. ` +
      `${i} statement(s) before it are applied and NOT recorded - ` +
      `resolve by hand, then rerun.`
    )
    throw err
  }
}
await client.query('INSERT INTO _migrations (name) VALUES ($1)', [file])

That error message says what a DSQL migration really is. Each statement commits on its own. If a file fails at statement three, statements one and two are applied, the ledger has no row for the file, and the rerun starts from the top and hits statement one again. The splitter is quote-aware too, since a ; or -- inside a string literal is neither a separator nor a comment. No migration depended on that yet, which made it the cheapest possible moment to get it right.

The practical rule is to write every migration statement so it can run twice, with IF NOT EXISTS on every CREATE and a WHERE guard on every data update. Any migration system benefits from that. On DSQL, a half-applied file is what a failed migration normally looks like.

The constraints that shape every expand step

A few DSQL rules decide what an expand step can look like in the first place.

A new column has to be nullable, with no default. ADD COLUMN takes a name and a type and nothing else. No NOT NULL, no DEFAULT. Every new column starts out null on every existing row, and the codebase has to agree on what that null means. In MCA Benches, null means “written before this migration”, and each column’s migration says so in a comment. Making the app multi-tenant was a run of exactly these steps: add the column nullable, backfill every existing row to the one guild that existed back then, and from that point on always write a tenant from the application, so a null is a bug with no meaning attached.

The contract step can’t tighten a column. There’s no SET NOT NULL, so a nullable column keeps accepting nulls for good. DSQL does let you add a CHECK constraint with ALTER TABLE ... ADD CONSTRAINT ... NOT VALID and validate it afterwards with ALTER TABLE ASYNC ... VALIDATE CONSTRAINT. That should give you a CHECK (tenant_id IS NOT NULL) enforced on new rows right away and on old ones once the validation job passes. I haven’t tried it. It’s the first thing I’ll reach for on the next column that should never be null.

Dropping a column arrived late, and it still doesn’t delete anything. When I started, DSQL rejected DROP COLUMN outright. The one time I needed it, the goal was getting megabyte-sized log blobs out of the rows. DSQL is index-organised, and every scan was dragging them along. So the contract step turned into UPDATE bench SET dps_report_raw = NULL, which won back the read cost and left an empty column behind. DROP COLUMN works now, though it only hides the column. The space comes back as rows get rewritten, and the dropped column still counts toward a lifetime limit of 1,600 columns per table.

`ASYNC` means the migration finished before the index did

DSQL builds indexes with CREATE INDEX ASYNC, which returns a job and does the building in the background. For a distributed database that’s the right call. It also means “Migrations complete.” and “the index exists” are two separate claims.

With a normal index the difference is performance. A unique index turns it into a correctness problem. MCA Benches has one that stops a player from being drafted onto two teams, and until it finishes building, it quietly allows the exact duplicate it exists to prevent.

So a second script runs straight after every production migration. It checks that every migration file on disk has a row in the ledger, that the columns this deploy’s code reads are there, and that the unique index is both indisvalid and indisready:

SELECT c.relname, i.indisvalid, i.indisready
  FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
 WHERE c.relname = 'draft_pick_tournament_player_uniq';

A cleaner fix would have the runner wait for the job itself. DSQL lists build jobs in sys.jobs, and sys.wait_for_job() blocks until one finishes. I wrote the verification script before I knew that. I’d keep it anyway, because checking that the database is in the state this code needs pays off even on the days every step reported success.

The window you can’t close

The worst gap in MCA Benches’ history came from a unique index that had to gain a column. Achievements get awarded with ON CONFLICT DO NOTHING against a unique index on (player, key, subject), and going multi-tenant meant adding tenant to it. Postgres can’t add a column to an existing index. Two migrations, then. Drop the old index, create the new one.

Between them the table has no unique constraint at all. On DSQL the new index builds ASYNC, so the gap stays open until the build finishes, well after the statement returns. A repeated award landing in that window writes a duplicate row.

The migration system has no way to close this, because there’s no atomic index swap. I closed it with process. The migration, the tenant provisioning and the backfill ran back to back as one documented sequence, and the backfill counted duplicate groups and reported them, so they couldn’t surface later as a doubled badge.

Two precautions made me comfortable running that against production. The first was a row-level export before anything that migrates or backfills. AWS Backup is the only supported backup for a DSQL cluster, and a restore always creates a new cluster, so recovering means pointing the app at a new endpoint. That’s fine if the cluster is gone. If a backfill wrote the wrong tenant across three hundred rows, which is the failure you’re far more likely to hit, restoring a whole cluster is a sledgehammer, and what you want is a JSON dump of every table you can diff and repair from.

The second was a rehearsal on real data. I parked a local Postgres at the production migration number, loaded the export into it, and ran the whole sequence. Migrations that throw are easy to catch anywhere. A backfill that writes a plausible wrong answer only shows up against real rows, because they’re the only ones with the shape to expose it.

Now the CDK part

All of the above holds however the migration gets run. What follows is how I’d move it into the deploy, as a design I haven’t shipped yet.

The neat answer is triggers.TriggerFunction, a Lambda that CDK invokes during the deploy, ordered against other resources.

import { TriggerFunction } from 'aws-cdk-lib/triggers'

const migrate = new TriggerFunction(this, 'Migrate', {
  runtime: lambda.Runtime.NODEJS_22_X,
  handler: 'migrate.handler',
  code: lambda.Code.fromAsset('dist/migrate'), // runner + every .sql file
  timeout: Duration.minutes(10),
})

migrate.executeAfter(cluster)          // the cluster has to exist
migrate.executeBefore(serverFunction)  // expand before the new code goes live

The interesting decisions are in how you use it.

Bundle the SQL files into the handler’s asset. By default a trigger re-runs whenever its handler code changes, so with the migrations in the bundle, a new migration file changes the asset, the trigger runs again, and the ledger turns everything already applied into a no-op. Put them in an S3 prefix instead and the trigger never learns there’s anything new.

A failed trigger fails the deploy, and the rollback leaves the database where it was. CloudFormation reverts the Lambda code, but it can’t un-run SQL. DSQL makes that worse, since the failed file may be half-applied. You’re left with the previous code, a partly migrated schema, and CloudFormation reporting a successful rollback. You survive that only if the expand steps really are invisible to the old code, which brings us back to the start of this post.

Keep the contract step out of the same deploy. “Before the code” is where an expand step belongs and the worst possible place for a drop. The simplest rule I’d trust puts expand migrations in one folder, run by the trigger, and contract migrations in another folder that only runs in a later deploy, after the previous code has been fully replaced.

Don’t migrate on cold start. That’s the other common answer, running migrations when the server Lambda boots. A traffic spike is fifty cold starts, which means fifty concurrent migration runs. Postgres would let you serialise them with pg_advisory_lock. DSQL has no advisory locks, and no LOCK TABLE either. You could build a lock row on top of DSQL’s optimistic concurrency, where the second writer’s insert just aborts, but then stale locks from runners that died are your problem. A trigger sidesteps all of it by only ever having one runner.

Make delete a no-op. When the stack is destroyed, the migration resource should do nothing. Reversing the migrations is the tempting option, and it’s the one that deletes your data.

The checklist

Expand before the code and contract after it, in separate deploys.
Write every statement so it can run twice. On DSQL a failed file is a half-applied file.
New columns are nullable. Decide what null means and write it in the migration.
An ASYNC index enforces nothing until its job finishes, so wait for the job or check it.
Swapping a unique index leaves a window with no constraint. Find it and close it with process.
Export the rows before any backfill, and rehearse big changes on a copy of real data.
If migrations run in the deploy: one runner, idempotent statements, nothing on delete.