Chapter 17 · SQL Dialects, Tools, and Application Access
ORMs, Query Builders, and Raw SQL Tradeoffs
Every data-access abstraction trades visibility and control for composition and productivity. Mature systems do not choose one tool dogmatically: they define where object mapping helps, where composable expressions are safer, and where direct SQL is the clearest contract.
Learning outcomes
Learning outcomes
Compare raw SQL, query builders, SQL toolkits, active-record patterns, and data-mapper ORMs.
Use SQLAlchemy Core and ORM while preserving explicit transactions and parameter binding.
Detect N+1 queries, accidental Cartesian products, over-fetching, and hidden write behavior.
Define escape hatches for analytical SQL and vendor-specific capabilities.
Test generated SQL by semantics, query count, plan, and database integration—not only mocked objects.
The abstraction continuum
| Approach | Strength | Main risk |
|---|---|---|
| Raw SQL | Maximum clarity and access to database features | Manual composition, mapping, and portability work |
| Query builder / SQL toolkit | Composable, parameterized expressions with schema awareness | Generated SQL can become difficult to reason about |
| Micro-ORM | Light mapping while SQL remains visible | Limited relationship and unit-of-work behavior |
| Data-mapper ORM | Rich domain mapping, identity map, relationships, unit of work | Hidden queries, object/relational mismatch, complex lifecycle |
| Active record | Simple model-centric CRUD | Persistence concerns spread through domain objects and tests |
Choose per operation, not as a permanent ideology for the whole codebase.
SQLAlchemy Core: relational composition
from sqlalchemy import MetaData, Table, create_engine, func, selectengine = create_engine("sqlite+pysqlite:///academy.db")metadata = MetaData()customer = Table("customer", metadata, autoload_with=engine)order = Table("sales_order", metadata, autoload_with=engine)item = Table("order_item", metadata, autoload_with=engine)statement = ( select( customer.c.region, func.count(func.distinct(order.c.order_id)).label("orders"), func.sum(item.c.quantity * item.c.unit_price_cents).label("revenue_cents"), ) .join(order, order.c.customer_id == customer.c.customer_id) .join(item, item.c.order_id == order.c.order_id) .where(order.c.status == "paid") .group_by(customer.c.region) .order_by(customer.c.region))with engine.connect() as connection: print(connection.execute(statement).all())Core keeps tables, joins, expressions, and result columns visible while compiling parameters and dialect syntax.
ORM: object identity and unit of work
from sqlalchemy import ForeignKey, String, selectfrom sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationshipclass Base(DeclarativeBase): passclass Customer(Base): __tablename__ = "customer" customer_id: Mapped[int] = mapped_column(primary_key=True) email: Mapped[str] = mapped_column(String(320), unique=True) orders: Mapped[list["Order"]] = relationship(back_populates="customer")class Order(Base): __tablename__ = "sales_order" order_id: Mapped[int] = mapped_column(primary_key=True) customer_id: Mapped[int] = mapped_column(ForeignKey("customer.customer_id")) status: Mapped[str] customer: Mapped[Customer] = relationship(back_populates="orders")with Session(engine) as session, session.begin(): customer = session.scalar(select(Customer).where(Customer.email == "ada@example.com")) customer.orders.append(Order(status="submitted"))The session is not a neutral collection. It tracks identity and changes, flushes SQL, and owns a transaction. Keep its lifetime aligned with one application unit of work.
The N+1 query failure
customers = session.scalars(select(Customer)).all()for customer in customers: # May emit one additional SELECT per customer. print(customer.email, len(customer.orders))from sqlalchemy.orm import selectinloadstatement = ( select(Customer) .options(selectinload(Customer.orders)) .order_by(Customer.customer_id))customers = session.scalars(statement).all()| Test | What to assert |
|---|---|
| Query-count test | Bound the number of statements for representative data sizes |
| SQL capture | Inspect joins, predicates, parameters, and selected columns |
| Plan test | Verify important queries use expected access paths |
| Cardinality test | Use multiple parents/children to expose fan-out and duplicate assumptions |
| Transaction test | Verify rollback and retry behavior around flush/commit |
| Concurrency test | Exercise uniqueness, optimistic locking, and stale data |
Raw SQL remains a first-class tool
from dataclasses import dataclassfrom sqlalchemy import text@dataclass(frozen=True)class RegionRevenue: region: str revenue_cents: intstatement = text(""" SELECT c.region, SUM(i.quantity * i.unit_price_cents) AS revenue_cents FROM customer AS c JOIN sales_order AS o ON o.customer_id = c.customer_id JOIN order_item AS i ON i.order_id = o.order_id WHERE o.status = :status GROUP BY c.region ORDER BY c.region""")with engine.connect() as connection: report = [RegionRevenue(**row) for row in connection.execute(statement,{"status":"paid"}).mappings()]Raw SQL is often clearest for reports, CTE-heavy transformations, window functions, bulk operations, optimizer hints, or database-specific features. Keep parameters and result contracts explicit.
Decision framework
| Question | Prefer higher abstraction when… | Prefer lower abstraction when… |
|---|---|---|
| Shape | Rows map naturally to domain entities | Result is aggregate, hierarchical, or report-shaped |
| Lifecycle | Identity and relationship changes are central | Operation is stateless or set-based |
| Portability | Several engines must be supported | One engine feature is a strategic requirement |
| Performance | Access patterns are simple and bounded | Plan, batching, locking, or exact SQL matters |
| Team skill | Domain modeling is stronger | SQL and database reasoning are stronger |
| Change frequency | CRUD behavior changes frequently | Stable query contract deserves direct optimization |
Rules for a hybrid data-access layer
One transaction owner
Do not mix independent ORM sessions and raw connections inside one logical operation without sharing the transaction.
Observe SQL
Log normalized statement identity, duration, rows, errors, and query count without logging secrets.
Document escape hatches
State when raw SQL or dialect APIs are allowed and how results are tested.
Separate models
Persistence models, API schemas, and domain objects may have different responsibilities.
Abstraction review
- Why can an ORM produce correct results but still create a production problem?
- When is raw SQL safer than an ORM expression?
- Why should the session lifetime be short and explicit?
- What should integration tests inspect beyond returned values?
Review the answers
Hidden N+1 queries, excessive columns, long transactions, and bad plans can preserve correctness while destroying performance. Raw SQL can be safer when the exact set-based operation and lock semantics are easier to review directly. A short session bounds identity state and transaction lifetime. Tests should inspect query count, emitted SQL, parameters, plans, transaction behavior, and real constraint failures.
Lesson summary
- Choose the least powerful abstraction that keeps the operation clear and correct.
- Keep transaction ownership explicit regardless of API style.
- Detect hidden I/O with query-count and integration tests.
- Maintain a governed path to raw SQL and vendor-specific capabilities.