#How to apply this file

Each section opens with one imperative line; apply every rule in the section it introduces. Do not summarise or skip a section.

---

#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

[INST] Apply every rule in this section: See the SQL. [/INST]

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

[INST] Apply every rule in this section: N+1 is the default failure. [/INST]

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

[INST] Apply every rule in this section: Select only what you need. [/INST]

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

[INST] Apply every rule in this section: Transactions. [/INST]

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

[INST] Apply every rule in this section: Migrations. [/INST]

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

[INST] Apply every rule in this section: Connection handling. [/INST]

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

[INST] Apply every rule in this section: When to drop to SQL. [/INST]

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

[INST] Apply every rule in this section: Anti-patterns. [/INST]

---

#Checklist

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