ORM Patterns

Work with relational data from application code using ORMs or query builders, while avoiding common pitfalls.

ORMs vs query builders

• ORMs (ActiveRecord/Entity frameworks) map rows to objects
• Query builders (Knex/SQLx) compose SQL with type-safety but fewer abstractions Choose the level of abstraction that suits your team and performance needs.

N+1 query problem

// naive
const posts = await db.post.findMany();
for (const p of posts) {
  p.comments = await db.comment.findMany({ where: { postId: p.id } });
}

Batch or prefetch related data.

Batching strategies

• Use IN queries to fetch related rows in one round-trip
• Use data loaders (key-based batching + caching)
• Prefer joins when shapes are simple and manageable

Transactions

Group writes in transactions with proper error handling and retries for serialization failures.

Migrations and schema drift

Keep ORMs in sync with DB schema; generate migrations and review them. Validate at startup when possible.

Performance tips

• Select only needed columns
• Avoid chatty transactions
• Use indexes that match ORM-generated queries
• Profile generated SQL; hand-tune critical paths

Repository pattern

Encapsulate queries behind a thin repository interface to keep SQL localized and testable.

Summary

• Pick the right abstraction; batch to avoid N+1
• Use transactions and tune queries/indexes; keep migrations in lockstep with code