Database

Mantle uses Drizzle ORM with Aurora DSQL (IAM-authenticated serverless PostgreSQL) as the default provider.

Quick start

import { getDrizzleClient } from '@mantleframework/database'
import { getRequiredEnv } from '@mantleframework/env'

export async function getDb() {
  return getDrizzleClient({
    provider: 'aurora-dsql',
    endpoint: getRequiredEnv('DSQL_ENDPOINT'),
    region: getRequiredEnv('AWS_REGION'),
    username: 'admin',
    isAdmin: true,
  })
}

Connections are cached at the module level and reused across Lambda warm starts. The default maxConnections: 1 is correct for Lambda.

Defining a schema

Place schema files in src/db/entities/ and re-export from src/db/schema.ts.

// src/db/entities/files.ts
import { pgTable, text, timestamp, uuid, integer } from 'drizzle-orm/pg-core'

export const files = pgTable('files', {
  fileId:    uuid('file_id').primaryKey().defaultRandom(),
  key:       text('key').notNull(),
  title:     text('title').notNull(),
  status:    text('status').notNull().default('pending'),
  size:      integer('size').notNull().default(0),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
})

// src/db/schema.ts
export { files } from './entities/files.js'

Pass the schema object to getDrizzleClient for type inference:

import * as schema from './schema.js'

return getDrizzleClient({ ..., schema })

CRUD operations

import { eq } from '@mantleframework/database/orm'
import { files } from '#db/schema'

// Insert
const [file] = await db.insert(files).values({ key: 'uploads/foo.mp4', title: 'My Video' }).returning()

// Select with filter
const result = await db.select().from(files).where(eq(files.fileId, id)).limit(1)

// Update
const [updated] = await db.update(files).set({ status: 'ready' }).where(eq(files.fileId, id)).returning()

// Delete
await db.delete(files).where(eq(files.fileId, id))

Import Drizzle operators (eq, and, inArray, sql, etc.) from @mantleframework/database/orm, not directly from drizzle-orm.

Query metrics

Wrap every database call with withQueryMetrics to emit CloudWatch metrics and X-Ray traces:

import { withQueryMetrics } from '@mantleframework/database'

const file = await withQueryMetrics('Files.get', async () => {
  const db = await getDb()
  const result = await db.select().from(files).where(eq(files.fileId, id)).limit(1)
  return result[0] ?? null
})

This records QueryDuration and RowCount metrics with QueryName and Success dimensions, and opens an X-Ray span named db:Files.get.

In practice, use entity query classes (see Entity Queries) rather than calling withQueryMetrics directly in handlers.

Transactions

import { withTransaction } from '@mantleframework/database'

const result = await withTransaction(db, async (tx) => {
  const [item] = await tx.insert(files).values({ ... }).returning()
  await tx.update(files).set({ status: 'processing' }).where(eq(files.fileId, item.fileId))
  return item
})

If any operation throws, the entire transaction rolls back.

Provider setup

Aurora DSQL (default)

getDrizzleClient({
  provider: 'aurora-dsql',
  endpoint: getRequiredEnv('DSQL_ENDPOINT'),
  region: getRequiredEnv('AWS_REGION'),
  username: 'admin',   // or per-Lambda role name from DSQL_ROLE_NAME env var
  isAdmin: true,       // false when using per-Lambda least-privilege roles
  schema,
})

IAM authentication — no passwords or connection strings needed. The client generates and refreshes auth tokens automatically.

DSQL constraints:

• A transaction may contain only one DDL statement
• GRANT USAGE ON SCHEMA public is not supported
• REVOKE ALL ON ALL TABLES before GRANTs will fail — use targeted GRANTs only
• GRANT ... ON <nonexistent_table> silently succeeds without error — tables must exist before granting

See Database Changes for the full constraints reference and validation workflow.

Aurora Serverless v2 / Neon

getDrizzleClient({
  provider: 'aurora-serverless-v2',  // or 'neon'
  connectionString: getRequiredEnv('DATABASE_URL'),
  schema,
})

Per-Lambda least-privilege roles

Generate fine-grained database roles from @RequiresTable annotations on entity query classes:

mantle generate permissions

Update getDb() to use the injected role name:

export async function getDb() {
  const roleName = getOptionalEnv('DSQL_ROLE_NAME')
  return getDrizzleClient({
    provider: 'aurora-dsql',
    endpoint: getRequiredEnv('DSQL_ENDPOINT'),
    region: getRequiredEnv('AWS_REGION'),
    username: roleName ?? 'admin',
    isAdmin: !roleName,
    schema,
  })
}

See Entity Queries for how permissions are declared and extracted.

DSQL Translation Layer

Aurora DSQL is PostgreSQL-compatible but has significant differences. All compatibility logic lives in @mantleframework/database/src/dsql/:

Module	Purpose
registry.ts	50+ documented incompatibilities queryable by category/action
classifier.ts	Classifies SQL statements as compatible, needs-recreation, create-index, or unsupported-strip
sanitizer.ts	sanitizeForDsql() (PG→DSQL) and adaptForStandardPg() (DSQL→standard PG)
error-codes.ts	OCC codes, idempotent DDL codes, and predicates (isOccError, isIdempotentDdlError)
introspection.ts	Live table schema introspection via pg_catalog
recreation.ts	Table recreation plans for unsupported ALTER TABLE operations
fk-enforcement.ts	Application-layer foreign key checks (assertRowExists, assertRowsExist)

Query the registry programmatically:

import { getByCategory, DsqlCategory, getByAction, DsqlAction } from '@mantleframework/database'

const ddlIssues = getByCategory(DsqlCategory.DDL)       // all DDL incompatibilities
const mustRecreate = getByAction(DsqlAction.Recreate)    // operations requiring table recreation

See DSQL Incompatibility Reference for the full list.

Database CLI commands

mantle db generate   # generate migration from schema changes (drizzle-kit)
mantle db migrate    # apply pending migrations
mantle db studio     # open Drizzle Studio (requires interactive terminal)
mantle db check-dsql # classify migration statements for DSQL compatibility

mantle db generate requires an interactive terminal (process.stdout.isTTY). Run it locally, not in CI.

Next steps

• Entity Queries — declare per-method permissions and add observability
• Infrastructure — Aurora DSQL Terraform module
• Observability — query metrics in CloudWatch