Migration

Mantle uses Drizzle Kit to generate migration SQL files and a custom runner in @j0nathan-ll0yd/database to apply them — with full Aurora DSQL constraint handling built in.

Final result

# Edit schema, generate, review, apply
mantle db generate
mantle db migrate

Step 1: Edit your schema

Define tables in src/entities/schema.ts using Drizzle's schema DSL:

import { pgTable, text, timestamp } from 'drizzle-orm/pg-core'

export const items = pgTable('items', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
})

Use text primary keys with gen_random_uuid() defaults on Aurora DSQL — no serial or sequences.

Step 2: Generate a migration

mantle db generate

Wraps npx drizzle-kit generate. Compares the current schema against migration history and writes a new .sql file to migrations/. Requires an interactive terminal — run in a real terminal session, not CI.

Step 3: Check DSQL compatibility

mantle db check-dsql

Analyzes migration files before applying. Output:

0001_initial.sql
  [1] OK     CREATE TABLE "items" (...)
  [2] INDEX  CREATE INDEX "items_name_idx" ON ...  (will use CREATE INDEX ASYNC)
  [3] STRIP  ALTER TABLE ... ENABLE ROW LEVEL SECURITY  (not supported by DSQL)

Summary:
  Total: 3  |  Compatible: 1  |  Index (ASYNC): 1  |  Stripped: 1

Classification	Meaning
OK	Fully compatible
INDEX	Rewritten to CREATE INDEX ASYNC at runtime
STRIP	Unsupported — silently skipped (RLS, DO blocks, CREATE SEQUENCE)
RECREATION	ALTER TABLE requiring column-type change — runner recreates the table

Step 4: Apply migrations

mantle db migrate

Tracks applied migrations by SHA-256 hash (not filename). Acquires an advisory lock in __mantle_migration_lock to prevent concurrent runs. The lock expires after 5 minutes to handle crashed processes.

Options

Flag	Default	Description
--folder <path>	./migrations	Migrations directory
--provider <type>	auto-detect	aurora-dsql, aurora-serverless-v2, or neon
--endpoint <url>	DSQL_ENDPOINT env	Aurora DSQL cluster endpoint
--connection-string <url>	DATABASE_URL env	Connection string (Neon / Aurora v2)
--region <region>	AWS_REGION env	AWS region
--no-lock	—	Skip migration lock (not recommended in production)

Provider is inferred automatically: DSQL_ENDPOINT set → aurora-dsql, DATABASE_URL set → neon.

Aurora DSQL constraints

Constraint	How the runner handles it
No SERIAL / sequences	Use gen_random_uuid() for PKs
No synchronous index creation	CREATE INDEX → CREATE INDEX ASYNC
No Row Level Security	ENABLE ROW LEVEL SECURITY stripped
No PL/pgSQL DO blocks	Stripped silently
One DDL per transaction	Each statement runs in its own transaction

Idempotent retry

The runner catches PostgreSQL errors that indicate a statement already applied:

Code	Meaning
42P07	Table already exists
42P01	Table does not exist (DROP on missing)
42701	Column already exists
42710	Index or constraint already exists

Table recreation

ALTER TABLE operations that change column types are not supported by DSQL. The runner detects these and executes a four-step recreation: create new table → copy rows → drop old table → rename. Table recreation is not wrapped in an idempotent catch — if it fails partway, fix the underlying issue and re-run mantle db migrate.

MigrateDSQL Lambda

Projects using Aurora DSQL include a standalone Lambda at src/lambdas/standalone/MigrateDSQL/index.ts. Invoke it after deploying to apply migrations against the live database:

aws lambda invoke \
  --function-name staging-my-api-MigrateDSQL \
  --payload '{}' \
  response.json

Apply permissions

After running mantle generate permissions, apply the generated SQL to Aurora DSQL:

mantle db apply-permissions

Reads SQL from permissions/ and executes against the admin connection. Idempotent — already-applied permissions are skipped.

Other commands

# Push schema directly to the database (development only)
mantle db push

# Open Drizzle Studio web UI
mantle db studio

Next steps

• Database — Drizzle schema patterns and getDrizzleClient()
• Entity Queries — @RequiresTable and permission generation
• Infrastructure — mantle generate infra for the MigrateDSQL Lambda