ORM Best Practices

ORM best practices for AI agents come down to three moves: pin the Prisma 7 or TypeORM 1.x version in the agent’s context so it stops writing removed APIs, prompt for query shapes and invariants rather than code, and let integration tests on a real Postgres prove the result. A query-count assertion catches N+1 regressions; a concurrency test catches overselling.

This recipe is for developers who let Claude Code, Codex or Cursor write the ORM layer of a TypeScript service. The agent’s schema looks right and its tests pass against mocks. Then production shows an N+1 on the orders list, two customers buy the last unit of stock, and a snippet that uses prisma.$use fails at startup because Prisma 7 no longer has it.

What these ORM recipes give you

• A version-pin block for AGENTS.md or CLAUDE.md that stops the agent from generating Prisma 6 or TypeORM 0.3 code
• A prompt for a multi-tenant Prisma 7 schema, plus the Prisma 7 config and client setup agents still get wrong
• A query-count test that turns an N+1 regression into a red CI run
• An order-placement flow that cannot oversell and never holds a row lock across a payment call
• A Testcontainers suite the agent can run and fix in a loop, and a table of the gates that sign the change off

Versions checked on 2026-09-26

@prisma/client 7.10.0, @prisma/adapter-pg 7.10.0, typeorm 1.1.1 and @testcontainers/postgresql 12.1.0 (npm registry). One trap: the latest tag of the prisma CLI package points at an 8.0.0 release candidate (8.0.0-rc.17 on 2026-09-26), so an unpinned npm install -D prisma or a fresh npx prisma pulls a release candidate. Pin prisma@7.10.0 next to @prisma/client@7.10.0.

Pin the ORM version in the agent’s context first

Agents were trained on years of Prisma 4–6 and TypeORM 0.2–0.3 code. Without a version pin they reproduce it confidently. Put the versions and the banned APIs in the project context file that your tool reads (AGENTS.md for Codex and Cursor, CLAUDE.md for Claude Code, or both), so every session starts from them:

## Database layer
- Prisma 7.10 (`prisma-client` generator, output in src/generated/prisma) with @prisma/adapter-pg. TypeORM 1.1 in services/billing.
- The datasource URL lives in prisma.config.ts, not schema.prisma. PrismaClient always gets an `adapter`.
- Never use: prisma.$use, prisma.$metrics, `url =` in the datasource block, @EntityRepository,
  getCustomRepository, getManager(), new Connection(). Use $extends, OpenTelemetry, prisma.config.ts,
  Repository.extend(), new DataSource().
- Money: Decimal @db.Decimal(12, 2) plus a currency column. Never @db.Money or Float.
- Every data-access change ships with an integration test on Postgres (Testcontainers), not a mock.

The removed APIs below were checked against the published type definitions of the pinned versions:

Agent writes	Status	Write instead
url = env("DATABASE_URL") in the datasource block	Moved to prisma.config.ts in Prisma 7.0.0	datasource: { url: env('DATABASE_URL') } in prisma.config.ts
new PrismaClient() with no options	Prisma 7 requires a driver adapter or accelerateUrl	new PrismaClient({ adapter })
prisma.$use(...) middleware	Not in the @prisma/client 7.10.0 client	a query extension through $extends
prisma.$metrics	Metrics preview removed in Prisma 7.0.0	OpenTelemetry tracing
@EntityRepository, getCustomRepository, AbstractRepository	Not exported by typeorm 1.1.1	dataSource.getRepository(User).extend({...})
getManager(), new Connection(...)	Not exported by typeorm 1.1.1	dataSource.manager, new DataSource(...)

Design a Prisma 7 schema the agent cannot misread

Agents reason about relations and indexes quickly. Your job is to state the tenancy model, the soft-delete rule and the queries you actually run, so the indexes match real query shapes instead of guesses.

Copy-paste prompt: multi-tenant Prisma 7 schema

Design a Prisma 7.10 schema (generator provider "prisma-client", output "../src/generated/prisma";
datasource block with provider only, the URL lives in prisma.config.ts) for a multi-tenant shop.

- Every tenant-scoped model has organizationId and deletedAt DateTime? (soft delete).
- Models: Organization, User (role enum), Product, ProductVariant (sku, stockQuantity Int),
  Order (status enum, total Decimal @db.Decimal(12, 2), currency, paymentId String?), OrderItem.
- Money is Decimal @db.Decimal(12, 2) with currency String @db.Char(3). Never @db.Money or Float.
- Composite indexes for exactly these queries: active products by org + category;
  user by email; an org's orders by status, newest first.
- @@map every model to a snake_case table.

Output the schema, then list every index you added that I did not ask for and why.
Finish by running `npx prisma validate` and fix anything it reports.

A good response looks like this excerpt. Note the provider-only datasource, the Decimal column and the composite index ordered to match the query:

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

model Product {
  id             String    @id @default(cuid())
  organizationId String
  slug           String
  category       String
  basePrice      Decimal   @db.Decimal(12, 2)
  currency       String    @default("USD") @db.Char(3)
  deletedAt      DateTime?

  organization Organization     @relation(fields: [organizationId], references: [id])
  variants     ProductVariant[]

  @@unique([organizationId, slug])
  @@index([organizationId, category, deletedAt])
  @@map("products")
}

The connection string now lives in prisma.config.ts, and the client gets a driver adapter. Prisma 7 also stopped loading .env automatically, so load it yourself:

prisma.config.ts

import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: { path: 'prisma/migrations' },
  datasource: { url: env('DATABASE_URL') },
});

// src/db.ts
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from './generated/prisma/client';

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL, max: 10 });
export const prisma = new PrismaClient({ adapter });

Two schema traps the agent will not flag

Money. PostgreSQL’s money type formats output according to the server’s lc_monetary locale and stores no currency. Agents still suggest @db.Money because older tutorials did. Use Decimal plus a currency column, or integer minor units.

Soft delete versus unique keys. @@unique([organizationId, slug]) also counts soft-deleted rows, so a deleted product blocks its slug forever. When slugs must be reusable, generate the migration with --create-only and ask the agent to replace that constraint with a partial unique index (WHERE deleted_at IS NULL) in the SQL.

The same schema change runs differently in each tool:

Claude Code

Run the edit-validate loop headlessly, with Bash limited to Prisma commands:

claude -p "Add a soft-deletable AuditLog model to prisma/schema.prisma, run 'npx prisma validate', \
then 'npx prisma migrate dev --name add_audit_log --create-only'. Print the generated SQL and \
flag any DROP, ALTER TYPE or NOT NULL without a default." \
  --allowedTools Read Edit "Bash(npx prisma *)"

--create-only writes the migration SQL without applying it, so you read the lock implications before anything touches the database. It still connects to DATABASE_URL and creates a shadow database, so point it at a local or Testcontainers Postgres, never a shared one.

Codex

A schema change regenerates client types across the repository, so give Codex its own managed worktree:

codex --worktree -c default_permissions=":workspace" \
  "Add an AuditLog model with organizationId and deletedAt, run npx prisma generate, \
   and fix every call site that now fails npx tsc --noEmit."

--worktree (Codex CLI 0.157.1) creates the worktree for you. Permission profiles are beta; the legacy equivalent is --sandbox workspace-write, and the two do not compose, so pick one.

Cursor

Open prisma/schema.prisma and give Cursor’s agent the prompt above. Review the per-file diff, paying most attention to the @@index lines, before you accept it. With the Prisma extension installed, validation errors appear in the Problems panel, and “fix the schema errors in the Problems panel” is a complete follow-up prompt.

Use Repository.extend() on TypeORM 1.x

On TypeORM the most common agent mistake is @EntityRepository(User) class UserRepository extends Repository<User>. TypeORM 1.1.1 does not export that decorator, so the code does not compile. The current idiom is Repository.extend():

export const UserRepository = dataSource.getRepository(User).extend({
  findActiveByEmail(email: string) {
    return this.createQueryBuilder('user')
      .innerJoinAndSelect('user.organization', 'org')
      .where('user.email = :email', { email })
      .andWhere('user.deletedAt IS NULL AND org.deletedAt IS NULL')
      .getOne();
  },
});

Find and kill N+1 queries with a query-count test

The N+1 is the ORM’s signature failure: an innocent loop issues one query per row. Agents fix it well when they see the loop and the SQL together. They also reintroduce it in a later refactor, so the fix is only half the job. The other half is a test that counts queries.

Copy-paste prompt: find and fix the N+1

GET /api/orgs/:orgId/users is slow under load. Find the N+1 in src/routes/users.ts and rewrite it
as one query with a nested include (Prisma) or innerJoinAndSelect (TypeORM). Keep the soft-delete
filter deletedAt: null on both users and orders. Use a DataLoader only if one query is impossible,
and say why.

Then add an integration test that logs queries with
new PrismaClient({ adapter, log: [{ emit: 'event', level: 'query' }] }) and prisma.$on('query'),
calls the handler for an org with 3 users and 20 users, and asserts the query count is the same
for both. Run the test and show me the counts before and after.

The fix is a nested include, which Prisma resolves without a query per user:

const users = await prisma.user.findMany({
  where: { organizationId, deletedAt: null },
  include: { orders: { where: { status: 'COMPLETED', deletedAt: null } } },
});

The test is what keeps it fixed. The count must not grow with the number of rows:

const queries: string[] = [];
const prisma = new PrismaClient({ adapter, log: [{ emit: 'event', level: 'query' }] });
prisma.$on('query', (e) => queries.push(e.query));

it('loads users with orders in a constant number of queries', async () => {
  await seedOrg('small', 3);
  await seedOrg('large', 20);

  queries.length = 0;
  await listUsersWithOrders(prisma, 'small');
  const small = queries.length;

  queries.length = 0;
  await listUsersWithOrders(prisma, 'large');
  expect(queries.length).toBe(small);
});

When the SQL itself is slow rather than repeated, move to SQL optimization patterns, which reads the EXPLAIN plan.

Write an order flow that cannot oversell or double-charge

Order placement is where agent-written ORM code costs money. The agent’s default is one big transaction that locks stock, calls the payment provider and commits. That holds row locks for the length of an HTTP call, and in Prisma the interactive transaction times out after five seconds by default. The safer shape has three phases:

1. Reserve in a short transaction: create a PENDING order and decrement stock only where enough stock remains.
2. Charge outside any transaction, with the order id as the idempotency key.
3. Confirm by marking the order PAID, or release the reservation if the charge failed.

A conditional updateMany makes the stock check and the decrement one atomic statement. Under PostgreSQL’s default READ COMMITTED isolation, a concurrent update re-evaluates the WHERE clause against the committed row, so two buyers cannot both take the last unit:

// Phase 1: reserve (Prisma 7)
const order = await prisma.$transaction(async (tx) => {
  for (const item of items) {
    const { count } = await tx.productVariant.updateMany({
      where: { id: item.variantId, stockQuantity: { gte: item.quantity } },
      data: { stockQuantity: { decrement: item.quantity } },
    });
    if (count === 0) throw new InsufficientStockError(item.variantId); // rolls back all items
  }
  const total = orderTotal(items); // sum of unitPrice * quantity, computed as Decimal
  return tx.order.create({
    data: {
      organizationId,
      userId,
      status: 'PENDING',
      total,
      currency,
      items: { create: items.map(({ variantId, quantity, unitPrice }) => ({ variantId, quantity, unitPrice })) },
    },
  });
});

// Phase 2: charge outside the transaction; the provider deduplicates on the key
let payment: { id: string };
try {
  payment = await payments.charge({ amount: order.total, currency: order.currency, idempotencyKey: order.id });
} catch (err) {
  await releaseReservation(order.id); // restores stock, marks the order CANCELLED
  throw err;
}

// Phase 3: confirm; only a PENDING order can become PAID
await prisma.order.updateMany({
  where: { id: order.id, status: 'PENDING' },
  data: { status: 'PAID', paymentId: payment.id },
});

If phase 3 fails, the customer has paid for a PENDING order. Do not refund blindly from a catch. Let a reconciliation job match PENDING orders older than a few minutes against the payment provider and finish or cancel them. On TypeORM the same reserve step uses dataSource.transaction('READ COMMITTED', ...) with .setLock('pessimistic_write') on the variants before the stock check.

Copy-paste prompt: audit an order flow for money bugs

Review src/services/order-service.ts for correctness under failure and concurrency. Check:
1. Can two concurrent orders both take the last unit? Show the statement that prevents it
   (a conditional UPDATE, or FOR UPDATE / pessimistic_write before the check).
2. Is any external call (payment, email, webhook) made while a transaction is open?
3. Is the payment call idempotent (idempotency key = order id), and can a retry double-charge?
4. If the charge succeeds and the next write fails, what state is the order in, and what
   finds and repairs it?
5. If the code uses SERIALIZABLE, does it retry only on Postgres error 40001, with a bound
   and jitter?
List each gap with the smallest fix, then write a failing integration test for each gap
before you fix it.

SERIALIZABLE needs a retry loop

Agents reach for SERIALIZABLE as a safety blanket. Under it, PostgreSQL aborts conflicting transactions with error 40001, and without a retry a burst of orders becomes a burst of 500s. Either keep READ COMMITTED with a conditional update or an explicit row lock, or wrap the transaction in a bounded, jittered retry that fires only on 40001. Never retry a block that contains the payment call.

Set the connection pool where the ORM actually reads it

Agents often write a datasource block with connectionLimit or a pool = { ... } object. Neither field exists, and prisma validate rejects them. On Prisma 7 the pool belongs to the driver: @prisma/adapter-pg accepts a pg pool config (the max: 10 in src/db.ts above) or an existing pg.Pool. The ?connection_limit= URL parameter from Prisma 6 tutorials is a Prisma 6 idiom; configure the pg pool instead. TypeORM passes pool options through extra:

const dataSource = new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  extra: { max: 10, idleTimeoutMillis: 30_000, statement_timeout: 60_000 },
});

Size the pool per process, then multiply by the number of processes. Ten connections in each of 50 serverless instances is 500 connections, which exceeds the default max_connections of 100 in PostgreSQL. On serverless, put a pooler such as PgBouncer in front and keep max small.

Prove it on real Postgres with Testcontainers

Mocking the query builder tests the mock. @testcontainers/postgresql starts a disposable Postgres in Docker, so locks, constraints and migrations run for real. In version 12 the constructor requires an image, and .start() returns a StartedPostgreSqlContainer. Agents often omit the image.

Copy-paste prompt: Testcontainers suite for the order flow

Write a Vitest integration suite for OrderService using @testcontainers/postgresql 12.1.
- beforeAll: new PostgreSqlContainer('postgres:17').start(); keep it as StartedPostgreSqlContainer.
- Apply the real migrations with `npx prisma migrate deploy` against container.getConnectionUri().
  No db push, no synchronize: true.
- Test 1: 10 concurrent placeOrder calls for a variant with stock 1. Exactly one succeeds,
  nine throw InsufficientStockError, final stock is 0.
- Test 2: the payment mock succeeds and the confirm step throws. The order stays PENDING with
  no refund issued, and the reconciliation job moves it to PAID.
- Test 3: the query-count test for listUsersWithOrders.
- afterAll: stop the container.
Run the suite and iterate until it is green. Do not weaken an assertion to make it pass.

import { execSync } from 'node:child_process';
import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';

let container: StartedPostgreSqlContainer;

beforeAll(async () => {
  container = await new PostgreSqlContainer('postgres:17').start();
  process.env.DATABASE_URL = container.getConnectionUri();
  execSync('npx prisma migrate deploy', { env: process.env, stdio: 'inherit' });
}, 120_000);

afterAll(async () => {
  await container.stop();
});

Each tool drives the red-green loop differently:

Claude Code

claude -p "Run 'npx vitest run tests/integration/order-service.test.ts'. If the concurrency \
test fails, find the missing guard in placeOrder and fix the code, not the test. Re-run until green." \
  --allowedTools Read Edit "Bash(npx vitest *)" "Bash(npx prisma *)"

Headless mode suits this loop: container startup is slow, and you do not need to watch each re-run.

Codex

codex --worktree -c default_permissions=":workspace" \
  "Run npx vitest run tests/integration/order-service.test.ts and fix placeOrder until it passes. \
   Do not edit the assertions."

Testcontainers needs a reachable Docker daemon. If the sandbox blocks the Docker socket, run the suite in CI and paste the failure back instead of widening the sandbox.

Cursor

Give Cursor’s agent the prompt above and let it run the suite in the integrated terminal. When the concurrency test fails, paste the failing output back and ask it to explain which interleaving produced it before it changes code.

Give the agent the live schema through a Postgres MCP server

For schema and N+1 work the agent does better when it reads the real indexes, row estimates and EXPLAIN output instead of your model file. Postgres MCP Pro (PyPI postgres-mcp 0.3.0, from Crystal DBA; last release 2025-05-16, so pin it and watch the repo) provides that; run it against a development or staging database with a read-only role:

# Claude Code: project scope, written to .mcp.json
claude mcp add -s project postgres -e 'DATABASE_URI=${DEV_DATABASE_URL}' \
  -- uvx postgres-mcp --access-mode=restricted

# Codex: user scope, written to ~/.codex/config.toml
codex mcp add postgres --env DATABASE_URI="$DEV_DATABASE_URL" \
  -- uvx postgres-mcp --access-mode=restricted

The single quotes keep ${DEV_DATABASE_URL} literal in .mcp.json. Claude Code expands it from each developer’s environment, so no credential is committed. The Codex line uses double quotes because ~/.codex/config.toml is per-user and stores the value the shell expanded. Cursor takes the same command and arguments in its own MCP config. Database MCP servers has all three configs and the read-only role to create first.

Do not install the archived reference Postgres server

@modelcontextprotocol/server-postgres is marked “Package no longer supported” on npm (last version 0.6.2). Datadog Security Labs showed on 2025-08-21 that stacking COMMIT; before a statement escapes its read-only transaction. Agents still suggest it. Use Postgres MCP Pro in restricted mode, and make the database role read-only whichever server you choose.

How to verify agent-written ORM code without reading every line

Read the migration SQL and the invariants. Let gates check the rest:

Gate	What it proves	Runs in
npx prisma validate and npx tsc --noEmit	The schema is valid Prisma 7 and every call site matches the generated types	Pre-commit hook and CI
A grep for the banned APIs in the pin block	No removed API slipped back in	CI
Query-count tests	N+1 regressions fail the build	CI, against Testcontainers
Concurrency and partial-failure tests	No oversell, no orphaned charge	CI, against Testcontainers
npx prisma migrate status against the target database	Applied migrations match the repository; no hand-edited drift	Deploy job, before migrate deploy
A human read of the migration SQL	Locks, drops, and backfills are intended	PR review

The developer who opens the PR owns the invariants in the tests. A reviewer signs off on the migration SQL and the test names, not on every line of the generated repository code. For the gate order that turns this into a pipeline, see testing in the agent lifecycle.

When agent-written ORM code breaks in production

• The generated code targets a dead major. Symptoms: prisma.$use is not a function, a url validation error in the datasource block, or TypeORM imports that do not resolve. Recovery: add the pin block from the first section to the context file and paste the error back with “this is Prisma 7.10 / TypeORM 1.1”. Then add the banned-API grep to CI.
• A release candidate arrived through npx prisma. Symptoms: the CLI version and @prisma/client differ, and generate output changes. Recovery: pin both packages to 7.10.0 in package.json and run the CLI from node_modules.
• Pool exhaustion under load. Symptom: timeouts while acquiring a connection. Recovery: count max × processes against max_connections, lower max, and add PgBouncer for serverless.
• Serialization retry storms. Symptom: bursts of error 40001 turned into 500s. Recovery: drop to READ COMMITTED with a conditional update or row lock, or add a bounded retry on 40001 only.
• Orphaned charges. Symptom: a paid customer with a PENDING order. Recovery: run the reconciliation job, and add the partial-failure test from the Testcontainers prompt so the path stays covered.
• The N+1 returns after a refactor. Symptom: latency grows with page size. Recovery: the query-count test should already be red; if it is green, the fixture is too small to show growth, so seed more rows.
• Migration drift. Symptom: migrate deploy fails in CI after someone changed the database by hand. Recovery: fail the deploy on migrate status, and never run prisma db push against a shared database. Database migration patterns covers the zero-downtime rewrite.

Bound parameters only

Agents sometimes build bulk updates as a string of CASE id WHEN ... clauses with values concatenated in. That is an injection vector. Use the ORM’s bound parameters, Prisma.sql template tags with $queryRaw, or chunked parameterized statements.

Where to go next with ORM work

• Database recipes: the hub that picks the right recipe for the task in front of you
• Database migration patterns: expand and contract, lint gates, and rollback for the migrations this page generates
• SQL optimization patterns: EXPLAIN plans and index design when the query is slow rather than repeated
• Database development with AI agents: the end-to-end workflow from spec to rollout
• Integration testing with AI: Testcontainers patterns beyond the database layer