Backend5 min read

ORM Patterns & Best Practices

Use ORMs effectively - Prisma, SQLAlchemy, TypeORM patterns, N+1 prevention, transaction handling, and when raw SQL beats the ORM.

ORMPrismaSQLAlchemyTypeORMDatabase

skill: ORM Patterns & Best Practices category: Backend tags: [ORM, Prisma, SQLAlchemy, TypeORM, Database] readingTime: 5

ORMs reduce boilerplate and provide type safety - but generate poor SQL when misused. Know when to use ORM query builders versus raw SQL. The N+1 problem is the most common ORM mistake: 100 users × 1 query for orders = 101 queries. Always eager-load relationships when you'll access them.

Workflow

Define Prisma schema and queryPrisma provides type-safe database access with auto-generated client from schema. Schema-first approach keeps database schema and TypeScript types in sync.
Prevent N+1 queries with eager loadingN+1 happens when you load a list then loop to load relations - 1 query for the list + N queries for each item. Eager load relations you know you'll access.
Handle transactions with Prisma and SQLAlchemyTransactions ensure atomicity - multiple DB operations either all succeed or all fail together.
Use raw SQL for complex queriesORMs generate suboptimal SQL for complex analytics, hierarchical queries, or window functions. Raw SQL (with parameterization) is the right tool for these.
Optimize with select and paginationSelecting all columns (SELECT *) and fetching unbounded rows are common ORM performance mistakes. Always select only needed columns and paginate.

Output Format

ORM: Prisma schema with indexes, eager loading with include/joinedload, $transaction for atomicity, $queryRaw for complex SQL, and cursor pagination.

Prisma: include: { relation: true } for eager load
Prisma: $transaction(async (tx) => { ... })
SQLAlchemy: selectinload() for to-many
Raw: $queryRaw`SELECT ... ${param}` with type annotation
Paginate: cursor-based for large datasets

Examples

Input

Output

Edge Cases & Gotchas

Prisma connection pool exhaustion - Prisma opens a connection pool per process - serverless environments create a new pool per invocation, exhausting database connections. Use PgBouncer or Prisma Data Proxy in serverless.
SQLAlchemy session scope - SQLAlchemy sessions should be scoped to a web request, not application lifetime. A long-lived session accumulates identity map entries and misses external changes.
ORM optimistic locking - For concurrent updates, use optimistic locking (version column + WHERE version = current). Prisma doesn't have built-in optimistic locking - implement with raw SQL or version column in WHERE clause.
Migration drift - ORM auto-generated migrations may not match hand-crafted migrations. Run npx prisma validate and npx prisma db pull regularly to detect schema drift.

When to Break These Rules

Use raw SQL or query builders (Kysely, Knex) instead of an ORM for high-performance microservices where every millisecond matters - ORM abstraction layers add overhead.
Use an ORM for CRUD endpoints but switch to raw SQL for reporting, ETL, and analytics queries - the ORM query builder doesn't express complex SQL well.

Reference Files

https://www.prisma.io/docs/
https://docs.sqlalchemy.org/en/20/