ORM & data layer patterns

Active Record trade-offs: one class encapsulates schema, validation, business rules, queries, AND persistence. User.where(age: 30..).joins(:posts) is terse. But at scale, fat models (100+ methods on User) obscure structure. Callbacks (before_save, after_create) are convenient and catastrophic for testability — a seemingly-pure method suddenly hits the DB. Django's ORM is Active Record-ish but less magical than Rails (explicit .save(), no implicit validation callbacks by default).

Data Mapper trade-offs: session.add(user); session.commit() — persistence is an explicit concern, not a model responsibility. Domain objects can be unit-tested without a DB. Fits DDD, hexagonal architecture, ports-and-adapters. Costs: more layers (entity, repository, service, DTO), more code to write, more framework knowledge required. SQLAlchemy 2.0 supports both Declarative (AR-like) and Classical Mapping (pure Data Mapper).

The N+1 query problem: the classic ORM trap. posts = Post.all(); posts.each { |p| p.author.name } → 1 query for posts + N queries (one per post) for authors. Easy to write, costs linearly with data volume. Fix: eager loading — Post.includes(:author) (Rails), .select_related('author') (Django), joinedload(Post.author) (SQLAlchemy). Every non-trivial project hits this in prod at least once.

Migration story: Rails db:migrate, Django migrate, Alembic (SQLAlchemy), Prisma (schema diffing with generated migrations), Flyway/Liquibase (Java). Auto-generated migrations from schema diffs save time but miss: column renames (shown as drop + add — DATA LOSS), data migrations (populate new column from old), complex constraints. Always review generated SQL. Deploy-safety rule: schema changes go BEFORE the code that depends on them; backward compatibility must span the rollout window.

Transactions & unit-of-work: most ORMs offer per-request transaction wrapping — Rails around_filter, Django ATOMIC_REQUESTS, Spring @Transactional. Implicit transactions are convenient but can cause surprises (long-held locks, retry semantics). Distributed transactions across microservices are basically impossible safely — use saga patterns, event sourcing, or eventual consistency.

Type safety evolution: Prisma (TS), Kysely (TS), sqlc (Go), SQLAlchemy 2.0 + MyPy, jOOQ (Java) — all generate compile-time-validated query types from your schema. db.user.findUnique({ where: { id }}) returns a typed User | null. Removes a huge class of runtime bugs (typo in column name → compile error, not runtime crash). Traditional ORMs returning any-typed rows survive only by legacy inertia.

Pragmatic advice: start with whatever your framework ships. Use the ORM's query builder for complex stuff; drop to raw SQL for performance-critical paths (bulk inserts, window functions, CTE chains). Never mix three data-access styles in the same module — be explicit about which layer owns which table. Log every query in dev to catch N+1s early (bullet gem, django-debug-toolbar, SQLAlchemy echo).