#Scope contract

FILE_ISOLATION: Modify only files inside the scope the task names. Reading elsewhere is allowed; writing outside it is not, and a needed out-of-scope change is reported, not made. SIGNATURE_PINNING: Before implementing, write the exact signatures you will add or change (name, parameters, return type). Implement to those signatures; if one must change, say so before changing it. TYPE_CONTRACTS: Every public function carries explicit parameter and return types. No any, untyped dict, or interface{} at a module boundary.

---

#Purpose

Rules for working with an ORM (Prisma, Drizzle, TypeORM, SQLAlchemy, ActiveRecord, Ent). An ORM removes boilerplate and gives you types. It does not remove the need to understand the SQL — it hides it, which is the problem this package addresses.

Working rule: you must be able to see the SQL for any query you ship. If you cannot, you are not in a position to say whether it is correct or fast.

---

#See the SQL

Turn on query logging in development, permanently:

js
// Prisma
new PrismaClient({ log: [{ emit: "event", level: "query" }] })
  .$on("query", (e) => console.log(e.query, e.params, `${e.duration}ms`));

py
# SQLAlchemy
create_engine(url, echo=True)

rb
# ActiveRecord — already on in development; make it visible
ActiveRecord::Base.logger = Logger.new($stdout)

Two things become obvious immediately: the number of queries per request, and any query the ORM built that you would never have written.

---

#N+1 is the default failure

Lazy loading turns a property access into a query. It is invisible in the code and catastrophic under load.

js
// N+1 — one query, then one per row
const posts = await db.post.findMany();
for (const p of posts) p.author = await db.user.findUnique({ where: { id: p.authorId } });

// Eager — one query
const posts = await db.post.findMany({ include: { author: true } });

Assert query counts in integration tests. Code review does not catch N+1 reliably; a failing test does.

js
// Fails the moment someone adds a lazy relation to this endpoint
expect(queryCount(() => getFeed(userId))).toBeLessThan(5);

Never access a relation inside a loop. → Database/query-optimization

---

#Select only what you need

An ORM's default is to hydrate every column into an object.

js
// Moves password hashes, blobs and audit columns you never read
const users = await db.user.findMany();

// Explicit projection — smaller payload, enables index-only scans
const users = await db.user.findMany({ select: { id: true, email: true } });

Projection is also a security control: a default findMany() that reaches a JSON response is how password hashes and internal flags leak. Serialise from an explicit shape, never from the ORM entity directly.

---

#Transactions

Scope a transaction to one unit of work, and keep everything slow outside it.

js
await db.$transaction(async (tx) => {
  await tx.account.update({ where: { id: from }, data: { balance: { decrement: amt } } });
  await tx.account.update({ where: { id: to   }, data: { balance: { increment: amt } } });
});

• Never make an HTTP call, send an email, or await user input inside a transaction. It holds locks and a connection for the duration of someone else's latency.
• Use { decrement: n }-style atomic operators rather than read-then-write in application code — the read-modify-write loses updates under concurrency.
• Handle serialization failures and deadlocks with a bounded retry. → Database/transactions

---

#Migrations

Use the ORM's migration tool, but read the generated SQL before applying it. Generators routinely produce a table rewrite or a blocking index build where a safe equivalent exists.

• Review every generated migration as code, in the pull request.
• Add CREATE INDEX CONCURRENTLY by hand — most generators do not emit it.
• Never edit an applied migration; write a new one.
• Verify the migration is reversible, or state explicitly that it is not. → Database/migration

---

#Connection handling

The ORM owns the pool. Misconfiguring it is the most common ORM-caused outage, and it looks like a slow query rather than what it is: waiting for a connection.

In serverless, a per-invocation PrismaClient/create_engine opens a new pool on every cold start. Instantiate once at module scope and route through a pooler. → Database/postgres

Instrument pool.wait_time or the equivalent. If p99 request latency is high while the database is idle, the queue is in the pool, not in the engine.

---

#When to drop to SQL

Use raw SQL, parameterised, when the ORM's generated query is wrong or slow:

js
await db.$queryRaw`SELECT tenant_id, count(*) FROM orders
                   WHERE created_at > ${since} GROUP BY tenant_id`;

Legitimate cases: window functions, recursive CTEs, bulk upserts, complex aggregation, and anything where EXPLAIN shows the ORM's plan is unusable.

Never build SQL by string concatenation, even inside an ORM's raw escape hatch. $queryRaw with a tagged template parameterises; $queryRawUnsafe with an interpolated string does not. → Security/sql-injection

---

#Anti-patterns

---

#Checklist

• Query logging with durations is enabled in development
• Relations are eager-loaded; no relation access inside a loop
• Query counts are asserted in integration tests for key endpoints
• Queries project explicit columns rather than whole entities
• API responses are built from explicit shapes, not ORM entities
• Transactions contain no network calls and no user interaction
• Counters use atomic operators, not read-then-write
• Deadlock and serialization retries are implemented
• Generated migrations are reviewed as SQL before merge
• Index creation on large tables is concurrent
• Raw SQL is parameterised; no string concatenation anywhere