ORM Best Practices

Your Prisma queries pass every test locally, then production falls over: the connection pool exhausts under load, an N+1 in the orders list times out, and a “safe” migration locks a hot table for 40 seconds. The ORM was supposed to abstract the database away — instead it hid the footguns. AI assistants are excellent at writing the schema and the happy-path query, and equally good at confidently handing you a deprecated API. This recipe shows how to drive Cursor, Claude Code, and Codex toward ORM code that survives production, and how to catch the patterns they get wrong.

What You’ll Walk Away With

• A workflow for generating a type-safe, multi-tenant Prisma schema with soft deletes and the right indexes
• A repeatable prompt for finding and fixing N+1 queries before they hit production
• A SERIALIZABLE order-placement transaction with row locking and payment compensation that actually compiles on Prisma 7 / TypeORM 1.0
• Testcontainers integration tests that run against a real Postgres, not a mock
• The deprecated APIs (@EntityRepository, prisma.$use, $metrics) AI still suggests — and the current replacements
• Where a Postgres MCP server beats pasting \d+ output into chat

Version targets

Examples target Prisma 7.8 and TypeORM 1.0 — the current majors as of June 2026. Both removed APIs that AI models were trained on heavily (the Prisma $use middleware and $metrics preview, the TypeORM @EntityRepository custom-repository pattern). If a generated snippet uses those, it will not run. The replacements are below.

Designing the schema

Schema design is where AI shines: it reasons about relationships, normalization, and indexes far faster than you can sketch them. Your job is to constrain it — specify the tenancy model, the soft-delete strategy, and the query shapes you actually run, so the indexes match reality instead of guesswork.

Copy-paste prompt: multi-tenant Prisma schema

Design a Prisma 7 schema for a multi-tenant e-commerce app. Requirements:

• Every tenant-scoped table carries organizationId and a deletedAt DateTime? soft-delete column.
• Models: Organization, User (roles via enum), Product, ProductVariant (SKU + stock), Cart, CartItem, Order, OrderItem.
• Store all money as Decimal @db.Decimal(12, 2) — never @db.Money — and add an explicit currency String @db.Char(3) where money lives.
• Add composite indexes that match these queries: list active products by org + category; find a user by email; load an org’s orders by status.
• Use @@map to snake_case table names and @@unique on natural keys (org+slug, product+sku).

Output only the schema. Call out any index you added that I did not ask for and why.

A trimmed version of what a good response looks like — note the @db.Decimal, the soft-delete index, and the composite index ordered to match the query:

model Product {
  id             String   @id @default(cuid())
  name           String
  slug           String
  category       String
  basePrice      Decimal  @db.Decimal(12, 2)
  currency       String   @default("USD") @db.Char(3)
  isActive       Boolean  @default(true)
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  deletedAt      DateTime?

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

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

Money should not be @db.Money

Prisma officially discourages @db.Money: it is integer-backed, formats according to the server’s lc_monetary locale, carries no currency, and rounds in surprising ways. AI models suggest it constantly because older tutorials did. Always use Decimal @db.Decimal(12, 2) (or @db.Numeric) plus an explicit currency column, or store integer cents. See the Prisma recommendation.

The same task differs per tool — Cursor edits the schema file in place, Claude Code drives the migration loop headlessly, Codex runs it in an isolated worktree:

Cursor

Open schema.prisma, select the model you’re evolving, and use agent mode with the prompt above. Cursor edits in place and shows a diff per model — review the indexes before accepting. Add the Prisma VS Code extension so Cursor sees validation errors inline; if it emits a @db.Money column or an invalid datasource field, the squiggle appears immediately and you can prompt “fix the schema-validation errors shown in the Problems panel.”

Claude Code

Let Claude Code own the full edit-validate loop from the terminal:

claude -p "Add a soft-deletable AuditLog model to schema.prisma, then run \
'npx prisma validate' and 'npx prisma migrate dev --name add_audit_log --create-only'. \
Show me the generated SQL before applying it." \
  --allowedTools "Read,Edit,Bash"

Because the migration is --create-only, Claude Code writes the SQL but does not apply it — you review the lock implications first. This is the headless pattern that catches a DROP COLUMN masquerading as additive.

Codex

Schema changes touch generated client types across the repo, so run Codex in a worktree to keep main clean:

git worktree add ../app-schema -b schema/audit-log
codex --ask-for-approval on-request \
  "In this worktree, add an AuditLog model with org scoping and soft deletes, \
   regenerate the Prisma client, and update any call sites that now fail type-check."

Review the worktree diff, then merge. Codex’s IDE and Cloud surfaces drive the same change; the worktree just isolates the regenerated node_modules/.prisma types.

TypeORM: skip the removed custom-repository pattern

If your stack is TypeORM, the single most common AI mistake is the @EntityRepository(User) class UserRepository extends Repository<User> pattern. It was removed in TypeORM 1.0 (along with AbstractRepository and getCustomRepository). Code using it will not compile. The current idiom is Repository.extend():

import { dataSource } from './data-source';
import { User } from './entities/User';

// TypeORM 1.0: extend a repository instead of subclassing Repository<User>
export const UserRepository = dataSource.getRepository(User).extend({
  async findActiveByEmail(email: string) {
    return this.createQueryBuilder('user')
      .leftJoinAndSelect('user.organization', 'org')
      .where('user.email = :email', { email })
      .andWhere('user.deletedAt IS NULL')
      .andWhere('org.deletedAt IS NULL')
      .getOne();
  },
});

AI still writes TypeORM 0.2-era code

Watch for @EntityRepository, getCustomRepository, and getManager() — all removed. Also new Connection(...); the current entry point is new DataSource(...). If a generated entity imports Connection from typeorm, regenerate it and tell the assistant your target is TypeORM 1.0.

Killing N+1 queries

The N+1 is the ORM’s signature failure: a loop that looks innocent issues one query per row. AI is good at spotting it when you show it the loop and the generated SQL together — and good at rewriting it to a single eager-loaded query.

Copy-paste prompt: find and fix N+1

This endpoint is slow under load. Find the N+1 query and rewrite it as a single query with eager loading. Then add a dataloader only if a single join is not possible.

const users = await prisma.user.findMany({ where: { organizationId } });
for (const user of users) {
  user.orders = await prisma.order.findMany({ where: { userId: user.id } });
}

Show the before/after query count and explain why the rewrite is one round-trip. Keep the soft-delete filter (deletedAt: null) on both models.

The fix is almost always a nested include (Prisma) or leftJoinAndSelect (TypeORM):

// Prisma: one query, orders eager-loaded, soft deletes respected
const users = await prisma.user.findMany({
  where: { organizationId, deletedAt: null },
  include: {
    orders: { where: { status: 'COMPLETED', deletedAt: null } },
  },
});

Copy-paste prompt: prove it from the logs

Enable Prisma query logging and run my integration test, then tell me the exact number of SQL statements this endpoint issues and whether any are duplicated:

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

If the count scales with row count, identify the loop responsible and propose the single-query rewrite. Do not change behavior — only the query strategy.

A transaction that survives failure

Order placement is the canonical “everything must succeed or nothing does” flow: lock inventory, create the order, decrement stock, charge the card, and if the charge succeeds but a later step throws, refund it. AI usually writes the happy path and a catch that swallows the compensation. The trap below is real and was in the original version of this recipe: it read error.paymentId, but a thrown Error has no such property, so the refund never fired.

The fix is to track the charged payment id in a local variable inside the try, so the catch can compensate:

// TypeORM 1.0 — SERIALIZABLE transaction with pessimistic locking + payment compensation
async placeOrder(userId: string, items: OrderItemInput[]): Promise<Order> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction('SERIALIZABLE');

  let chargedPaymentId: string | undefined;

  try {
    const variants = await queryRunner.manager
      .createQueryBuilder(ProductVariant, 'v')
      .setLock('pessimistic_write')
      .whereInIds(items.map((i) => i.variantId))
      .getMany();

    for (const item of items) {
      const variant = variants.find((v) => v.id === item.variantId);
      if (!variant || variant.stockQuantity < item.quantity) {
        throw new InsufficientInventoryError(`Insufficient stock for ${variant?.name}`);
      }
    }

    const order = await queryRunner.manager.save(Order, {
      userId,
      status: OrderStatus.PENDING,
      items: items.map((i) => ({
        variantId: i.variantId,
        quantity: i.quantity,
        price: variants.find((v) => v.id === i.variantId)!.price,
      })),
    });

    for (const item of items) {
      await queryRunner.manager.decrement(
        ProductVariant,
        { id: item.variantId },
        'stockQuantity',
        item.quantity,
      );
    }

    const payment = await this.paymentService.charge({ orderId: order.id, amount: order.total, userId });
    chargedPaymentId = payment.id; // track for compensation

    order.status = OrderStatus.PAID;
    order.paymentId = payment.id; // requires `paymentId String?` on the Order model
    await queryRunner.manager.save(order);

    await queryRunner.commitTransaction();
    await this.emailService.sendOrderConfirmation(order); // side effects after commit
    return order;
  } catch (e) {
    await queryRunner.rollbackTransaction();
    if (chargedPaymentId) await this.paymentService.refund(chargedPaymentId);
    throw e;
  } finally {
    await queryRunner.release();
  }
}

Copy-paste prompt: audit a transaction for compensation gaps

Review this order-placement transaction for correctness under failure. Specifically check:

1. Is inventory locked (FOR UPDATE / pessimistic_write) before the stock check, so two concurrent orders can’t both pass?
2. If the external payment succeeds but a later DB write throws, is the payment refunded? Trace the variable that holds the payment id — is it actually in scope in the catch?
3. Are non-transactional side effects (emails, webhooks) fired only after commit?
4. On a serialization failure, does the caller retry with backoff, or surface a 500?

List each gap and the minimal fix. Add paymentId String? to the Order model if the code assigns it.

SERIALIZABLE means retries are not optional

Under SERIALIZABLE, Postgres aborts conflicting transactions with a serialization error (40001). If you don’t catch that and retry with exponential backoff, a burst of concurrent orders turns into a wall of 500s. Wrap the transaction in a retry loop (3 attempts, exponential backoff) and only retry on the serialization error code — never blind-retry, or you’ll double-charge. Ask your assistant to “add a retry loop that only retries Postgres error 40001, with jittered backoff.”

Connection pooling: don’t trust the generated config

A frequent AI hallucination is a Prisma datasource block with connectionLimit and a pool = { ... } object. Those fields do not exist in the Prisma schema language — the schema fails validation. Pool size is set via the connection string (?connection_limit=25&pool_timeout=30) on Prisma ≤6; on Prisma 7 those URL params are gone — pool size comes from the driver adapter’s own config (e.g. the pg adapter’s max). Keep the datasource block to just provider and url:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
// DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=25&pool_timeout=30"

TypeORM exposes pool settings under extra on the DataSource, which is genuinely valid:

const dataSource = new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  extra: { max: 25, min: 5, idleTimeoutMillis: 30000, statement_timeout: 60000 },
});

Integration tests against real Postgres

Mocking the query builder tests your mock, not your SQL. Testcontainers spins up a throwaway Postgres so your transaction, locking, and migration logic run for real. The current @testcontainers/postgresql (12.x) requires an image argument and .start() returns a StartedPostgreSqlContainer — AI often omits the image and mistypes the variable.

Copy-paste prompt: Testcontainers integration test

Write a Vitest integration test for OrderService.placeOrder using @testcontainers/postgresql v12. Requirements:

• Start new PostgreSqlContainer('postgres:16') in beforeAll; type the started container as StartedPostgreSqlContainer.
• Run my Prisma migrations against the container URL, not synchronize: true.
• Test the concurrency case: fire 10 concurrent placeOrder calls for 1 unit of stock and assert that final stockQuantity equals initial minus the number of fulfilled orders.
• Tear down the container in afterAll.

import { PostgreSqlContainer, StartedPostgreSqlContainer } from '@testcontainers/postgresql';

let container: StartedPostgreSqlContainer;

beforeAll(async () => {
  container = await new PostgreSqlContainer('postgres:16')
    .withDatabase('testdb')
    .withUsername('test')
    .withPassword('test')
    .start();
  process.env.DATABASE_URL = container.getConnectionUri();
  await runMigrations(process.env.DATABASE_URL); // run real migrations, not synchronize
});

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

Each tool drives this loop differently:

Cursor

Generate the test with agent mode, then run it from the integrated terminal. When a concurrency assertion fails, paste the failing output back and ask Cursor to add row-level logging — it can iterate on the locking strategy with the diff visible.

Claude Code

Let Claude Code run the suite and fix failures in a loop:

claude -p "Run 'npm test -- order-service.integration', and if the concurrency \
assertion fails, find the missing lock in placeOrder and fix it. Re-run until green." \
  --allowedTools "Read,Edit,Bash"

The headless loop is ideal here: Testcontainers startup is slow, and you don’t want to babysit each re-run.

Codex

Run the suite in a worktree so a flaky container teardown never corrupts your main checkout. codex --ask-for-approval on-request "run the integration suite and fix the deadlock the logs show" — Codex’s Cloud surface can run the same suite on a PR via its GitHub integration.

Performance monitoring with current APIs

Two more removed APIs that AI loves to suggest:

• prisma.$use(...) middleware — deprecated in Prisma 4.16, removed in 6.14. Replacement: a client extension via $extends.
• prisma.$metrics.json() — the metrics preview feature was removed in Prisma 7. For production observability, use OpenTelemetry tracing instead.

The current way to time queries and flag slow ones is a query-level client extension:

// Prisma 7: client extension replaces the removed $use middleware
const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        const start = Date.now();
        const result = await query(args);
        const duration = Date.now() - start;
        if (duration > 1000) {
          logger.warn('Slow query', { model, operation, duration });
        }
        return result;
      },
    },
  },
});

If a snippet uses $use or $metrics, it targets a dead version

prisma.$use, prisma.$metrics, @EntityRepository, and new Connection() are the four tells that an AI answer was trained on an old major. Paste the deprecation back (“this is Prisma 7 / TypeORM 1.0, $use was removed — use $extends”) and it will correct itself. For pool and DB-level metrics, prefer OpenTelemetry instrumentation over any ORM-specific metrics API.

Let the database introspect itself: Postgres MCP

For schema design and N+1 hunting, the assistant works far better when it can see the live schema — actual indexes, row counts, and EXPLAIN ANALYZE output — instead of a stale paste. A Postgres MCP server gives it read access to do exactly that. Reach for a maintained server such as crystaldba/postgres-mcp (Postgres MCP Pro — index tuning, EXPLAIN plans, health checks, and a configurable access mode) pointed at your dev database:

# Claude Code — Postgres MCP Pro against a dev DB (restricted access mode)
# Flags (--transport, --env) must come before the server name; -- separates the command.
claude mcp add --transport stdio --env DATABASE_URI="postgresql://localhost:5432/dev" \
  postgres -- uvx postgres-mcp --access-mode=restricted

The same server is configured in Cursor and Codex via their MCP settings — the command and args are identical across all three tools. With it connected, “look at the actual indexes on orders and tell me which of my hot queries do a sequential scan” becomes a real answer backed by EXPLAIN, not a guess.

Don't use the archived reference Postgres server

The old reference server @modelcontextprotocol/server-postgres is deprecated (npm marks it “no longer supported”, pinned at v0.6.2) and its source was archived in 2025. Worse, its “read-only” framing is unsafe: Datadog Security Labs documented a SQL-injection that escapes the read-only transaction by stacking a COMMIT; and then running arbitrary DDL (e.g. DROP SCHEMA). AI still suggests it because it was trained on it. Use Postgres MCP Pro above, or the Zed-patched fork @zeddotdev/postgres-context-server, and scope the database role to read-only at the DB level regardless of which server you pick.

Skill vs MCP for this task

An MCP server is the right tool here because schema work is a persistent, stateful connection — the assistant queries the catalog repeatedly across a session. A lighter-weight Agent Skill (installed via npx skills add <owner/repo>) is the better fit for stateless, single-purpose augmentation like enforcing a SQL style guide. For live introspection and EXPLAIN, reach for the Postgres MCP. See Database access over MCP for the read-only-credentials setup.

When This Breaks

• Migration drift. prisma migrate dev works locally but migrate deploy fails in CI because someone edited the DB by hand. Run prisma migrate status in CI and fail the build on drift; never prisma db push to a shared environment.
• Pool exhaustion under load. Symptoms: Timed out fetching a new connection from the connection pool. On serverless, every instance opens its own pool — put PgBouncer (or Prisma Accelerate) in front and set connection_limit low per instance. AI’s default of 25 connections × 50 lambdas will melt Postgres.
• Serialization-failure storms. A SERIALIZABLE transaction without a retry loop turns concurrency into 500s (error 40001). Add bounded, jittered retries that only fire on the serialization code.
• The refund that never fires. If your compensation reads a property off the thrown error instead of a tracked local variable, a charged-but-failed order never refunds. Test the partial-failure path explicitly — make the payment succeed and the next DB write throw.
• N+1 regressions slip back in. A new include gets dropped in a refactor and the loop returns. Assert the query count in an integration test so the regression fails CI, not production.
• Generated code targets a dead major. $use, $metrics, @EntityRepository, new Connection() — if any appear, the assistant used an old training cut. Tell it the exact versions (Prisma 7.8 / TypeORM 1.0) and the snippet self-corrects.

What’s Next

• Database Migration Patterns — zero-downtime schema evolution in depth
• SQL Optimization Patterns — EXPLAIN ANALYZE and index strategy beyond the ORM
• Database access over MCP — wiring a read-only Postgres MCP into all three tools
• Integration Testing with AI — Testcontainers patterns across the stack

Review every generated query for injection

Always validate AI-generated queries for SQL injection. Use parameterized queries and the query builder’s bound parameters — never string-concatenate user input. The bulk-update “CASE id WHEN …” pattern AI sometimes emits is a classic injection vector; prefer UNNEST-based bulk updates or chunked parameterized statements instead.