ORM en Python: trade-offs, N+1, migraciones

Cuándo confiar en el ORM y cuándo bajar a Core/SQL, cómo mapea filas a objetos, cómo evolucionan los schemas con migraciones, y por qué la corrección bajo concurrencia la da el motor, no el ORM.

Teoría

Un ORM (Object-Relational Mapper) traduce entre dos mundos con formas incompatibles: filas y columnas en una base relacional, y objetos con referencias en tu programa. Ese desajuste tiene nombre —object-relational impedance mismatch— y el ORM es la capa que lo resuelve por vos: hidrata filas en instancias, sigue relaciones como atributos, y traduce mutaciones de objetos en INSERT/UPDATE/DELETE. La conveniencia tiene un costo: hidden queries, el clásico N+1, materialización cara de objetos, y una capa que a veces te impide expresar el SQL que necesitás. El nivel senior no es “sé usar el ORM” sino saber en qué capa estás pagando y cuándo cambiar de capa a conciencia.

Active Record vs Data Mapper

Son los dos patrones arquitectónicos que definen dónde vive la lógica de persistencia respecto de tu objeto de dominio.

Active Record (Martin Fowler, PoEAA): el objeto de dominio es la fila y sabe persistirse a sí mismo. El registro y su comportamiento de base de datos viven en la misma clase: user.save(), user.delete(), User.objects.filter(...). Django ORM es el ejemplo canónico en Python.

# Active Record (Django ORM): el modelo sabe hablar con la DB
class User(models.Model):
    name = models.CharField(max_length=255)
    email = models.EmailField(unique=True)

user = User.objects.get(pk=1)   # query colgada del modelo
user.name = "Ada"
user.save()                      # el objeto se persiste a sí mismo

• Ventaja: curva plana, poco boilerplate, productividad inmediata para CRUD. El modelo es un solo lugar.
• Costo: acopla el dominio a la infraestructura de persistencia. La lógica de negocio y la de DB conviven en la misma clase, lo que estorba para testear en aislamiento y viola separación de responsabilidades cuando el dominio se complica.

Data Mapper (también PoEAA): una capa separada —el mapper/Session— traduce entre objetos de dominio ignorantes de la persistencia y la base. El objeto no sabe que existe una DB; el mapper decide qué y cuándo escribir. SQLAlchemy ORM implementa este patrón vía su Session (que además es una Unit of Work: acumula cambios y los sincroniza en el flush/commit).

# Data Mapper (SQLAlchemy 2.x): el objeto no sabe de la DB; la Session media
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    email: Mapped[str] = mapped_column(unique=True)

# La persistencia vive en la Session, no en User
user = session.get(User, 1)
user.name = "Ada"
session.commit()          # la Session (Unit of Work) decide el UPDATE y lo emite

• Ventaja: el dominio queda limpio y testeable sin DB; separación clara; la Unit of Work agrupa escrituras y las emite de forma coherente (identity map: una fila = un objeto por sesión).
• Costo: más ceremonia (definís mapeos, manejás el ciclo de vida de la Session), más conceptos que entender (flush vs commit, expire, detached objects).

El trade-off en una frase: Active Record optimiza time-to-first-CRUD; Data Mapper optimiza separación y testabilidad del dominio a cambio de más aparato. En entrevista, la señal senior es no decir “cuál es mejor” sino ubicar cada uno: Django/Active Record encaja en apps CRUD-céntricas donde el modelo ≈ la tabla; SQLAlchemy/Data Mapper encaja cuando el dominio tiene reglas ricas, querés testear sin DB, o el mapeo objeto↔tabla no es 1:1 (un agregado sobre varias tablas, o una vista).

Para qué sirve un ORM y por qué no siempre raw SQL

Raw SQL es correcto y a veces superior. El ORM no existe para “evitar SQL” sino para resolver problemas concretos de un codebase de aplicación:

• Identity map: dentro de una Session, la misma fila se hidrata en el mismo objeto. Evita copias divergentes del mismo registro en memoria.
• Unit of Work: acumulás mutaciones sobre objetos y el ORM calcula el INSERT/UPDATE/DELETE mínimo y lo emite en orden correcto (respetando FKs) en el flush. Vos no orquestás el orden de escrituras.
• Modelos tipados y relaciones como atributos: order.customer en vez de un JOIN manual repetido en 20 lugares. Con typing (Mapped[...]) el IDE y mypy te cubren.
• Portabilidad de dialecto: el mismo código genera SQL válido para Postgres, MySQL, SQLite (útil en tests con SQLite in-memory).
• Seguridad por defecto: binding de parámetros → menos superficie de SQL injection que concatenar strings.

Cuándo NO conviene el ORM (bajar a Core o raw SQL):

• Reporting / analytics / agregaciones grandes: el object-mode estorba; querés tuplas/columnas, no objetos hidratados.
• Bulk operations: insertar/actualizar miles de filas con executemany, no session.add() en loop.
• SQL que la abstracción no expresa bien: window functions, CTEs recursivas, INSERT ... ON CONFLICT, RETURNING, hints específicos del motor.
• Queries hiper-calientes donde cada milisegundo de hidratación cuenta y querés control total del plan.

La regla senior: usá el ORM para lógica de dominio transaccional (CRUD, escrituras con reglas de negocio) y bajá a Core/SQL cuando la forma del query importa más que el objeto. No es “ORM vs SQL”, es saber en qué capa pagás.

Connecting & querying (SQLAlchemy 2.x)

El estilo 2.0 unificó la API alrededor de select() y Session.execute()/scalars(). El viejo session.query(...) es legacy: evitalo en código nuevo.

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session, sessionmaker

# Engine: pool de conexiones + dialecto. Se crea UNA vez por proceso.
engine = create_engine(
    "postgresql+psycopg://user:pass@localhost:5432/app",
    pool_size=5,            # conexiones persistentes en el pool
    max_overflow=10,        # extra temporales bajo carga
    pool_pre_ping=True,     # valida la conexión antes de usarla (evita "stale connection")
    echo=False,             # True => loguea el SQL emitido (útil para cazar N+1)
)

SessionLocal = sessionmaker(engine)

# Patrón recomendado: la Session vive lo que dura una unidad de trabajo (un request)
with SessionLocal() as session:
    # scalars() => devuelve entidades (objetos User), no filas Row
    stmt = select(User).where(User.email == "ada@example.com")
    user = session.scalars(stmt).one()          # .one() / .one_or_none() / .first() / .all()

    # execute() => devuelve Rows (tuplas) cuando pedís columnas o varias entidades
    rows = session.execute(
        select(User.id, User.name).where(User.name.like("A%"))
    ).all()                                       # [(1, "Ada"), ...]

    session.add(User(name="Grace", email="grace@example.com"))
    session.commit()                              # flush + COMMIT

Puntos que un senior no confunde:

• engine vs connection vs session: el engine es la fábrica/pool (global, thread-safe). Una Connection es una conexión concreta del pool. La Session es una unidad de trabajo del ORM que toma conexiones del pool según necesite. No compartas una Session entre threads.
• scalars() vs execute(): scalars() desenvuelve la primera columna de cada Row (típicamente la entidad); execute() te da Rows crudos. Si pedís select(User) y querés objetos User, usá scalars().
• Session como contexto: el with cierra la sesión (devuelve la conexión al pool). Sin commit() explícito, el with hace rollback: nada se persiste por accidente.

Relaciones: Foreign Key, one-to-many, many-to-many

from __future__ import annotations
from typing import List
from sqlalchemy import ForeignKey, Table, Column
from sqlalchemy.orm import Mapped, mapped_column, relationship

class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    # one-to-many: un customer tiene muchos orders
    orders: Mapped[List["Order"]] = relationship(back_populates="customer")

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    # many-to-one: el lado "dueño" de la FK
    customer: Mapped["Customer"] = relationship(back_populates="orders")

# many-to-many: tabla de asociación (Core Table, no un modelo)
post_tags = Table(
    "post_tags", Base.metadata,
    Column("post_id", ForeignKey("posts.id"), primary_key=True),
    Column("tag_id", ForeignKey("tags.id"), primary_key=True),
)

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    tags: Mapped[List["Tag"]] = relationship(secondary=post_tags, back_populates="posts")

class Tag(Base):
    __tablename__ = "tags"
    id: Mapped[int] = mapped_column(primary_key=True)
    posts: Mapped[List["Post"]] = relationship(secondary=post_tags, back_populates="tags")

• back_populates sincroniza ambos lados en memoria: al hacer order.customer = c, c.orders refleja el cambio sin ir a la DB. (backref es la forma vieja y más “mágica”; back_populates es explícito y preferido.)
• La FK vive en el lado “many” (Order.customer_id). El relationship es la vista de alto nivel; la ForeignKey es la restricción real en la DB. Son cosas distintas: podés tener la constraint sin el relationship y viceversa.
• secondary= apunta a la tabla de asociación para many-to-many. Si esa tabla necesita columnas propias (ej. added_at), no uses secondary: modelala como una entidad intermedia (association object) con dos one-to-many.

Lazy vs eager loading — el eje del N+1

Cuando accedés a una relación (order.customer, user.orders), el ORM tiene que decidir cuándo emitir el SQL para traerla. Esa estrategia de carga es la raíz del N+1.

• Lazy (por defecto, lazy="select"): la relación se carga cuando la tocás, con un SELECT extra en ese momento. Cómodo, pero dentro de un loop dispara N queries.
• Eager: la relación se carga junto con (o inmediatamente después de) la query principal. Se configura por-query con .options(...) (preferido) o por-relación con lazy= en el relationship.

Estrategias eager de SQLAlchemy 2.x:

from sqlalchemy.orm import selectinload, joinedload, contains_eager, raiseload

# selectinload => colecciones. 2 queries: users, luego orders WHERE user_id IN (...)
users = session.scalars(
    select(User).options(selectinload(User.orders))
).all()

# joinedload => escalar to-one. 1 query con JOIN
orders = session.scalars(
    select(Order).options(joinedload(Order.customer))
).all()

# joinedload sobre colección => filas duplicadas: OBLIGА .unique() en Python
orders = session.scalars(
    select(Order).options(joinedload(Order.items))
).unique().all()

# Anidado: carga en cadena por nivel (customer de cada order, y orders de cada user)
stmt = select(User).options(
    selectinload(User.orders).joinedload(Order.customer)
)

# contains_eager: yo escribo el JOIN (para filtrar) y le digo que hidrate con él
stmt = (
    select(Order)
    .join(Order.customer)
    .where(Customer.tier == "premium")
    .options(contains_eager(Order.customer))
)

# raiseload: defensa. Cualquier lazy load no previsto explota en vez de N+1 silencioso
stmt = select(User).options(raiseload("*"))

Regla de pulgar: selectinload para to-many, joinedload para to-one. La razón profunda es cardinalidad: joinedload sobre una colección produce un producto cartesiano de filas (padre duplicado por cada hijo), infla el payload y rompe la semántica de LIMIT/paginación (el LIMIT corta filas del JOIN, no entidades). selectinload deja intacta la query principal, así que compone con LIMIT/OFFSET y window functions. Para árboles anidados profundos, encadená por nivel y medí: a veces dos selectinload baten a un JOIN gigante, a veces un joinedload sobre una cadena to-one gana. Decisión perfilada, no dogmática.

N+1: qué es, cómo se detecta, cómo se arregla

El N+1 es el anti-patrón más común del ORM: 1 query para traer N padres + N queries (una por padre) para traer sus hijos vía lazy loading. 200 usuarios con sus órdenes → 201 round-trips a la DB. El cuello de botella no es el costo de escaneo por query sino el número de round-trips y la latencia de red acumulada — por eso “agregar un índice” no lo arregla.

# --- N+1: el ORM te traiciona (lazy loading dentro de un loop) ---
users = session.scalars(select(User)).all()   # 1 query
for u in users:
    print(u.orders)                            # +1 query POR usuario => 1 + N

# --- Fix: eager loading, 1+N pasa a 2 queries ---
users = session.scalars(
    select(User).options(selectinload(User.orders))
).all()
for u in users:
    print(u.orders)                            # 0 queries extra: ya está cargado

Detección (en orden de fiabilidad):

• Ver el SQL real: create_engine(url, echo=True), o configurar logging del logger sqlalchemy.engine a INFO. Si ves el mismo SELECT repetido con distinto parámetro, es N+1.
• Contar queries en un test (regresión): un event listener sobre before_cursor_execute que incrementa un contador; el test asegura “este endpoint hace ≤ 2 queries”. En Django, assertNumQueries. Es la defensa que impide que el N+1 vuelva en silencio tras un refactor.
• APM en prod: Datadog / New Relic / OpenTelemetry muestran span-per-query; un span con 200 hijos idénticos grita N+1.

from sqlalchemy import event

def install_query_counter(engine):
    stats = {"count": 0}
    @event.listens_for(engine, "before_cursor_execute")
    def _count(conn, cursor, statement, params, context, executemany):
        stats["count"] += 1
    return stats

# en un test:
stats = install_query_counter(engine)
get_users_with_orders(session)
assert stats["count"] <= 2, f"posible N+1: {stats['count']} queries"

ORM vs Core — no es todo-o-nada

SQLAlchemy tiene dos capas y podés mezclarlas en el mismo proyecto (y hasta en la misma Session):

• ORM: mapea objetos ↔ tablas, identity map, Unit of Work, relaciones. Para lógica de dominio transaccional.
• Core: el SQL Expression Language. Construís select()/insert()/update() sobre Tables o entidades, pero recibís Rows (tuplas), sin hidratar objetos ni tocar el identity map. Para reporting, bulk, y SQL que “tiene forma”.

from sqlalchemy import func, insert, update

# Core-style: agregación de reporting, tuplas en vez de objetos ORM
stmt = (
    select(User.country, func.count(Order.id).label("n"))
    .join(Order, Order.customer_id == User.id)
    .group_by(User.country)
    .order_by(func.count(Order.id).desc())
)
rows = session.execute(stmt).all()   # [("CO", 1240), ("MX", 980), ...]

# Bulk insert con executemany (NO session.add en loop)
session.execute(
    insert(Order),
    [{"customer_id": 1, "total": 10}, {"customer_id": 2, "total": 20}],  # miles de dicts
)

# Set-based update: una sentencia, sin traer objetos ni aritmética en Python
session.execute(
    update(Order).where(Order.status == "pending").values(status="expired")
)
session.commit()

Core sigue dándote binding de parámetros y portabilidad de dialecto, así que rara vez necesitás string SQL crudo; cuando sí, va parametrizado (text("... WHERE id = :id"), nunca f-strings) y fijado a un dialecto a propósito. El tell de que estás en la capa equivocada: si estás peleando contra el ORM para expresar el query, bajá a Core.

Migraciones — evolucionar el schema con Alembic

El schema de una DB en producción cambia con el tiempo (nuevas columnas, tablas, índices, backfills). Una migración es un cambio de schema versionado, ordenado y reversible, checkeado en el repo junto al código, de modo que cualquier entorno (dev, CI, prod) converja al mismo estado aplicando la misma secuencia. Sin migraciones tenés drift: la DB de prod no coincide con la de nadie.

Alembic es la herramienta estándar para SQLAlchemy (Django trae su propio sistema de migraciones equivalente). Cada migración es un archivo con revision, down_revision (forman una cadena/DAG) y dos funciones: upgrade() y downgrade().

alembic init migrations                              # scaffold inicial
alembic revision --autogenerate -m "add orders table"   # genera diff schema->modelos
alembic upgrade head                                 # aplica hasta la última revisión
alembic downgrade -1                                 # revierte la última
alembic current                                      # en qué revisión está la DB
alembic history                                      # cadena de revisiones

--autogenerate compara el target_metadata (tus modelos, Base.metadata) contra el estado real de la DB y escribe el diff. No es mágico: detecta bien tablas/columnas/índices, pero no cambios de tipo sutiles, renombres (los ve como drop+add), ni cambios de datos. Un senior revisa siempre el archivo generado antes de commitear.

# Un archivo de migración típico (migrations/versions/ab12_add_orders.py)
from alembic import op
import sqlalchemy as sa

revision = "ab12"
down_revision = "9f01"

def upgrade() -> None:
    op.create_table(
        "orders",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("customer_id", sa.Integer, sa.ForeignKey("customers.id"), nullable=False),
        sa.Column("total", sa.Numeric(12, 2), nullable=False),
    )
    op.create_index("ix_orders_customer_id", "orders", ["customer_id"])

def downgrade() -> None:
    op.drop_index("ix_orders_customer_id", table_name="orders")
    op.drop_table("orders")

Práctica senior sobre migraciones:

• Reversibilidad real: downgrade() tiene que devolver el schema al estado previo. Si una migración destruye datos (drop column), documentá que el downgrade no los recupera.
• Migraciones y datos van separados si es grande: un backfill de millones de filas dentro de una migración de DDL bloquea deploys. Separá la migración de schema (rápida) del backfill (batched, idempotente, corrible aparte).
• Zero-downtime = expand/contract: para renombrar/eliminar columnas sin romper la app corriendo, hacés varias migraciones en el tiempo: (1) expand agrega la nueva columna y el código escribe en ambas; (2) backfill; (3) contract elimina la vieja cuando ninguna versión desplegada la usa. Nunca un rename destructivo de un golpe.
• Índices en tablas grandes en Postgres: CREATE INDEX CONCURRENTLY (no bloquea escrituras) — pero requiere estar fuera de una transacción, así que en Alembic va con op.execute y ajustando el modo transaccional de esa migración.

Isolation levels — la corrección la da el motor, no el ORM

Ninguna estrategia de loading ni patrón de repositorio te salva de una anomalía de concurrencia: eso lo definen las transacciones ACID y el isolation level. El nivel de aislamiento decide qué anomalías son posibles cuando transacciones concurrentes tocan los mismos datos.

Anomalías, de menos a más grave: dirty read (leés algo no commiteado que luego hace rollback), non-repeatable read (releés una fila y cambió), phantom read (repetís un WHERE y aparecen/desaparecen filas), lost update (dos read-modify-write se pisan). El orden READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE va quitando anomalías a cambio de throughput.

El bug clásico es el lost update sobre READ COMMITTED: dos requests leen balance = 100, cada uno computa 100 - 10 en código de app, ambos escriben 90; un débito desaparece. Subir el isolation level no lo arregla por sí solo si la lectura-modificación-escritura no es atómica — y en motores MVCC (Postgres) REPEATABLE READ/SERIALIZABLE no bloquean, sino que fallan la transacción con un error de serialización que vos tenés que reintentar (código que probablemente no escribiste). Fixes reales, en orden de preferencia:

1. Update atómico set-based (sin aritmética en Python): la DB hace el cálculo bajo su propio lock de fila.
2. Lock pesimista con SELECT ... FOR UPDATE cuando necesitás leer, decidir y escribir con la fila bloqueada.
3. Concurrencia optimista con columna version y retry.

from sqlalchemy import update

# 1) Atómico set-based: no hay ventana entre leer y escribir. Preferido para dinero/stock.
session.execute(
    update(Account).where(Account.id == acc_id).values(balance=Account.balance - 10)
)
session.commit()

# 2) Lock pesimista: leo la fila BLOQUEADA, decido en app, escribo. Serializa a los concurrentes.
acc = session.scalars(
    select(Account).where(Account.id == acc_id).with_for_update()
).one()
if acc.balance >= 10:
    acc.balance -= 10
session.commit()          # el lock se libera al COMMIT

# Setear isolation level por-transacción (execution option sobre la conexión/engine)
engine_serializable = engine.execution_options(isolation_level="SERIALIZABLE")

Nota de portabilidad: el mismo nombre de nivel se comporta distinto entre motores. El REPEATABLE READ de Postgres es snapshot isolation y bloquea phantoms que el de MySQL trata diferente; el SERIALIZABLE de Postgres es SSI (Serializable Snapshot Isolation) y aborta con serialization failures en vez de bloquear. Nunca asumas semántica cross-engine sin verificar.

Cierre de mentalidad: el ORM es la capa de productividad y dominio; Core/SQL es la capa de forma del query; el motor y sus transacciones son la capa de corrección bajo concurrencia. Seniority es moverse entre las tres a conciencia — no clavar una etiqueta global (“usamos el ORM para todo” / “SERIALIZABLE por las dudas”) sino decidir por operación.

Ejercicios

1. Diagnosticar y arreglar un N+1. Tenés un endpoint que devuelve 500 posts, cada uno con su autor y sus tags, y tarda ~3s. Escribí el select() que lo baje a un número acotado de queries y justificá la elección de estrategia por relación.

stmt = (
    select(Post)
    .options(
        joinedload(Post.author),      # to-one: 1 JOIN, sin duplicar
        selectinload(Post.tags),      # to-many: 2ª query IN (...), sin inflar filas
    )
    .limit(500)
)
posts = session.scalars(stmt).unique().all()   # .unique() por el joinedload

2. Active Record vs Data Mapper en decisión real. Un equipo arranca un servicio con reglas de dominio ricas (cálculo de pricing, workflows de estado) y quiere testear la lógica sin levantar una DB. ¿Qué patrón/ORM recomendás y por qué? ¿Cuándo habrías elegido el otro?

3. Elegir la capa (ORM vs Core). Tenés que (a) confirmar una orden aplicando reglas de negocio, y (b) generar un reporte mensual de ventas por país con ranking. ¿Qué capa usás en cada una?

4. Lost update bajo concurrencia. Dos requests debitan $10 de la misma cuenta con balance = 100 casi simultáneamente en READ COMMITTED, leyendo el balance y escribiendo balance - 10 desde código Python. ¿Qué pasa y cómo lo arreglás sin subir a SERIALIZABLE ciegamente?

5. Migración zero-downtime. Necesitás renombrar orders.notes a orders.internal_notes sin downtime, con la app corriendo en varias instancias. Describí la secuencia de migraciones.

Preguntas tipo entrevista (EN)

Q1 — “You have an endpoint returning 200 users, each with their orders, and it’s slow. What’s happening and how do you fix it?”

• ❌ Wrong / trap: “Add an index on orders.user_id.” Why it fails: an index doesn’t remove the 201 round-trips; it’s the N+1 pattern (1 query for users + N lazy loads for orders). The bottleneck is query count and network latency, not per-query scan cost.
• ✅ Correct: Eager-load the relationship: select(User).options(selectinload(User.orders)), turning 1+N into 2 queries.
• ⭐ Optimal (senior): Confirm the diagnosis first via echo/query-count in a test or APM span count — don’t guess. Pick selectinload because orders is a collection (joinedload would row-multiply 200×orders and needs .unique()). If I only need a couple of fields, drop the ORM objects and go Core with an explicit column select() + JOIN, avoiding object hydration entirely. Also add a query-count regression test so N+1 can’t silently return.

Q2 — “When would you choose joinedload over selectinload, and vice versa?”

• ❌ Wrong / trap: “joinedload is always faster, it’s a single query.” Why it fails: for one-to-many collections joinedload produces a cartesian-style row explosion (parent duplicated per child), inflates payload by orders of magnitude, breaks LIMIT/pagination semantics, and forces .unique() in Python.
• ✅ Correct: joinedload for many-to-one / one-to-one (single JOIN, no duplication); selectinload for one-to-many / many-to-many (a second IN (...) query, no row multiplication).
• ⭐ Optimal (senior): The deciding axis is cardinality and whether the query is paginated. selectinload keeps the primary statement untouched, so it composes with LIMIT/OFFSET and window functions; joinedload is ideal when I need the related row in the same result set and the relation is to-one. For deep nested trees I chain per level and measure — sometimes two selectinloads beat one giant join, sometimes a single joinedload on a to-one chain wins. Decision is profiled, not dogmatic.

Q3 — “When do you drop the ORM and write raw SQL or use SQLAlchemy Core?”

• ❌ Wrong / trap: “Never — the ORM can do everything, raw SQL is a code smell.” Why it fails: forcing reporting/bulk/window logic through object-mode ORM yields unreadable query-building and needless object hydration; it’s slower and harder to reason about than the SQL it hides.
• ✅ Correct: Drop to Core/raw for bulk inserts/updates, complex reporting/aggregations, window functions, CTEs, and DB-specific features (e.g. Postgres INSERT ... ON CONFLICT).
• ⭐ Optimal (senior): I stay in the ORM for transactional domain logic where the unit-of-work and identity map earn their keep, and switch to Core select()/insert() when the shape of the SQL matters more than the object — bulk executemany, RETURNING, set-based updates instead of row-by-row. Core still gives me parameter binding and dialect portability, so I rarely need string SQL; when I do, it’s parameterized and pinned to one dialect on purpose. The tell is: am I fighting the ORM to express the query? Then I’m in the wrong layer.

Q4 — “Walk me through isolation levels and a real bug caused by picking the wrong one.”

• ❌ Wrong / trap: “Just use SERIALIZABLE everywhere to be safe.” Why it fails: serializable maximizes correctness but adds locking/serialization-failure retries and kills throughput; blanket use is a performance and availability foot-gun, and under MVCC engines you must handle serialization-failure retries you probably didn’t code.
• ✅ Correct: Order is READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE, each removing anomalies: dirty read, non-repeatable read, phantom read. Choose per transaction based on the invariant at stake.
• ⭐ Optimal (senior): Concrete bug — a lost update on READ COMMITTED: two requests read balance = 100, each computes 100 - 10 in app code, both write 90; one debit vanishes. The trap is thinking a higher isolation “reads fresher data” fixes it — it doesn’t, because the read-modify-write is non-atomic. Fixes: SELECT ... FOR UPDATE (pessimistic row lock), an atomic UPDATE accounts SET balance = balance - 10 WHERE id = ? (set-based, no app-side arithmetic), or optimistic concurrency with a version column and retry. I match isolation to the invariant and prefer atomic SQL over app-side compute for money/stock/counters. Note the same level name differs across engines (Postgres REPEATABLE READ is snapshot and blocks some phantoms MySQL’s does differently).

Q5 — “Repository pattern with business logic in the app, or push logic into the DB with triggers/stored procedures?”

• ❌ Wrong / trap: “Triggers are always bad / procs are always faster.” Why it fails: absolute rules ignore the trade. Triggers hide side effects and complicate testing/debugging; procs can be right for data-local, high-integrity operations. Neither is universally correct.
• ✅ Correct: Repository/app-centric keeps logic testable, versioned in the codebase, and DB-portable. DB-centric (triggers/procs/constraints) enforces invariants regardless of which client writes, and runs logic next to the data.
• ⭐ Optimal (senior): I keep business logic in the app behind repositories for testability and single-source-of-truth, but I don’t push correctness-critical invariants into app code alone — a unique constraint, FK, or CHECK in the DB is the real guarantee, because multiple services/scripts hit the same tables. Triggers I reserve for cross-cutting data concerns (audit trails) where hiding the side effect is acceptable; heavy set-based data mutations that would be N round-trips from the app can justify a proc. The line: invariants and data-local set operations near the data, orchestration and business rules in the app.

Q6 — “How does CAP theorem influence how you design a service’s data layer?”

• ❌ Wrong / trap: “We’ll build a CA system — consistent and available.” Why it fails: CAP says under a network partition you can only keep two of C/A/P, and partitions are not optional in a distributed system, so “CA” isn’t a real operating choice for a distributed store — you’re really choosing CP or AP.
• ✅ Correct: During a partition you trade consistency vs availability. A payments ledger leans CP (reject writes rather than diverge); a feed/cache/analytics store leans AP (serve possibly-stale data, stay up).
• ⭐ Optimal (senior): CAP is a partition-time statement, so the real question is per-operation, not per-system: within one service, the balance-transfer path wants strong consistency (CP, single Postgres primary), while a “recently viewed” list is fine eventually-consistent (AP). I also weigh PACELC — even without partitions there’s a latency-vs-consistency trade (sync vs async replicas). It’s a design constraint I make explicit per data flow, not a global label I stamp on the architecture.

Q7 — “Active Record vs Data Mapper — what’s the difference and when does it matter?”

• ❌ Wrong / trap: “Data Mapper is the modern/better one, Active Record is legacy.” Why it fails: both are current, first-class patterns (Django ships Active Record, SQLAlchemy ships Data Mapper); calling one obsolete ignores the trade and the ecosystems built on each.
• ✅ Correct: Active Record: the domain object is the row and persists itself (user.save()) — low ceremony, model ≈ table. Data Mapper: a separate Session/mapper mediates between persistence-ignorant domain objects and the DB — cleaner separation, testable domain, Unit of Work + identity map.
• ⭐ Optimal (senior): I pick by domain complexity and testability needs. Active Record (Django) wins for CRUD-centric apps where model≈table and batteries-included (admin, auth) outweigh purity. Data Mapper (SQLAlchemy) wins when the domain has rich rules I want to unit-test without a DB, or the object↔table mapping isn’t 1:1 (an aggregate across tables, a view). The cost of Data Mapper is more ceremony — Session lifecycle, flush vs commit, detached objects — so I don’t reach for it on a simple CRUD service just for ideology.

Referencias

• ORM Querying Guide — SQLAlchemy 2.0
• Relationship Loading Techniques — SQLAlchemy 2.0
• Working with ORM Related Objects — SQLAlchemy 2.0
• Declarative Mapping (Mapped / mapped_column) — SQLAlchemy 2.0
• Transaction Isolation & FOR UPDATE — SQLAlchemy 2.0
• Setting Transaction Isolation Levels — SQLAlchemy 2.0
• Alembic Tutorial & Auto Generating Migrations
• Transaction Isolation Levels — PostgreSQL docs
• Patterns of Enterprise Application Architecture (Active Record, Data Mapper, Unit of Work) — Martin Fowler