Entity Queries

Entity queries centralize database access, declare least-privilege permissions per query, and instrument every query with metrics and tracing automatically via the defineQuery factory.

Setup

Each project creates a local query factory by calling createQueryFactory once:

// src/db/defineQuery.ts
import {createQueryFactory} from '@mantleframework/database'
import {getDb} from './client.js'

export const {defineQuery, definePreparedQuery} = createQueryFactory(getDb)

Pattern

// src/entities/queries/fileQueries.ts
import {defineQuery} from '../../db/defineQuery.js'
import {DatabaseOperation} from '@mantleframework/database'
import {eq} from '@mantleframework/database/orm'
import type {InferSelectModel, InferInsertModel} from '@mantleframework/database/orm'
import {files} from '../../db/schema.js'

export type FileRow = InferSelectModel<typeof files>

export const getFile = defineQuery({
  tables: [{table: files, operations: [DatabaseOperation.Select]}],
}, async function getFile(db, fileId: string): Promise<FileRow | null> {
  const result = await db.select().from(files).where(eq(files.fileId, fileId)).limit(1)
  return result[0] ?? null
})

export const createFile = defineQuery({
  tables: [{table: files, operations: [DatabaseOperation.Select, DatabaseOperation.Insert]}],
}, async function createFile(db, input: InferInsertModel<typeof files>): Promise<FileRow> {
  const [file] = await db.insert(files).values(input).returning()
  return file!
})

export const updateFile = defineQuery({
  tables: [{table: files, operations: [DatabaseOperation.Select, DatabaseOperation.Update]}],
}, async function updateFile(db, fileId: string, data: Partial<InferInsertModel<typeof files>>): Promise<FileRow> {
  const [updated] = await db.update(files).set(data).where(eq(files.fileId, fileId)).returning()
  return updated!
})

export const deleteFile = defineQuery({
  tables: [{table: files, operations: [DatabaseOperation.Delete]}],
}, async function deleteFile(db, fileId: string): Promise<void> {
  await db.delete(files).where(eq(files.fileId, fileId))
})

Each defineQuery call:

• Wraps the function with withQueryMetrics (X-Ray span, CloudWatch metric, error handling)
• Attaches table permission metadata for mantle generate permissions to extract via AST
• Provides automatic OCC retry for Aurora DSQL conflict errors
• Optionally wraps in a transaction with { transaction: true }
• Uses the function's .name property as the metric name (e.g., getFile)

Options

defineQuery({
  tables: [{table: files, operations: [DatabaseOperation.Select]}],
  logInput: (fileId) => ({fileId}),     // DEBUG-level input logging
  retry: {maxAttempts: 5},              // custom OCC retry (or false to disable)
  transaction: true,                     // wrap in database transaction
}, async function getFile(db, fileId: string) { ... })

Using in handlers

// src/lambdas/api/files/[fileId]/index.get.ts
import {defineApiHandler, z} from '@mantleframework/validation'
import {buildValidatedResponse} from '@mantleframework/core'
import {getFile} from '../../../../entities/queries/fileQueries.js'

const ResponseSchema = z.object({fileId: z.string(), title: z.string(), status: z.string()})

const api = defineApiHandler({auth: 'bearer'})
export const handler = api(async ({event}) => {
  const fileId = event.pathParameters?.fileId!
  const file = await getFile(fileId)
  if (!file) return {statusCode: 404, body: JSON.stringify({error: 'Not found'})}
  return buildValidatedResponse(event, 200, file, ResponseSchema)
})

Handlers never call Drizzle directly — they import the query functions exported from query files.

Multi-table queries

Queries that access multiple tables declare all permissions in the tables array:

export const getFileWithDownloads = defineQuery({
  tables: [
    {table: files, operations: [DatabaseOperation.Select]},
    {table: fileDownloads, operations: [DatabaseOperation.Select, DatabaseOperation.Insert]},
  ],
}, async function getFileWithDownloads(db, fileId: string) { ... })

Available operations: DatabaseOperation.Select, Insert, Update, Delete, All.

Permission extraction

At build time, mantle generate permissions uses ts-morph to:

1. Parse all defineQuery tables arrays (and legacy @RequiresTable decorators) across entity query files
2. Trace which Lambda handlers import which query functions
3. Aggregate permissions per Lambda
4. Generate Terraform and SQL for per-Lambda PostgreSQL roles

Automatic instrumentation

defineQuery wraps every query with withQueryMetrics automatically:

• Opens an X-Ray span named db:<functionName>
• Emits QueryDuration CloudWatch metric with QueryName and Success dimensions
• For array results, also emits a RowCount metric
• Wraps database errors in DatabaseError with PG error details in structured logs
• Retries on Aurora DSQL OCC conflict errors (configurable via retry option)

Generating permissions

cd your-instance/backend
mantle generate permissions

This produces three artifacts:

infra/generated_dsql_permissions.tf — Terraform locals mapping each Lambda to a PostgreSQL role:

locals {
  lambda_dsql_roles = {
    "GetFile" = { role_name = "lambda_get_file", requires_admin = false }
  }
}

migrations/XXXX_lambda_roles.sql — PostgreSQL role creation and grants:

CREATE ROLE lambda_get_file WITH LOGIN;
GRANT SELECT ON files TO lambda_get_file;
AWS IAM GRANT lambda_get_file TO 'arn:aws:iam::${AWS_ACCOUNT_ID}:role/${RESOURCE_PREFIX}-GetFile';

build/dsql-permissions-report.md — human-readable permissions summary.

Import conventions

Package	What to import
@mantleframework/database	createQueryFactory, DatabaseOperation, withQueryMetrics (direct use rare — defineQuery wraps it)
@mantleframework/database/orm	eq, and, inArray, sql, InferSelectModel, InferInsertModel
drizzle-orm/pg-core	Schema definitions only (pgTable, text, uuid, etc.)

Never import from drizzle-orm directly in domain code (entity queries or handlers). Schema definition files (src/db/entities/*.ts) may import from drizzle-orm/pg-core.

Next steps

• Database — schema definition and getDrizzleClient setup
• Observability — how withQueryMetrics surfaces in CloudWatch and X-Ray