Builder (assemble something complex step by step)

When to use

• You need to build complex objects/strings with many optional parts (e.g., SQL with filters, sort, limit).
• You want a fluent API so call sites are readable and order-independent.
• You must keep params separate from SQL (safe binding, no string concat injection).

Avoid when a single function with a few arguments is enough.

Diagram (text)

Client ──> QueryBuilder
            ├─ select(...)
            ├─ from_(...)
            ├─ where(cond, params)
            ├─ order_by(...)
            ├─ limit(n)
            └─ compile() → (sql, params)

Python example (≤40 lines, type-hinted)

Concrete case: build parameterized SQL for an analytics job.
(Uses ? placeholders for demo—adapt to your DB-API if it uses %s.)

from __future__ import annotations
from dataclasses import dataclass, field
from typing import List, Tuple

@dataclass
class QueryBuilder:
    _cols: List[str] = field(default_factory=list)
    _table: str = ""
    _wheres: List[str] = field(default_factory=list)
    _params: List[object] = field(default_factory=list)
    _order: str = ""
    _limit: int | None = None

    def select(self, *cols: str) -> "QueryBuilder":
        self._cols.extend(cols or ["*"]); return self
    def from_(self, table: str) -> "QueryBuilder":
        self._table = table; return self
    def where(self, cond: str, *params: object) -> "QueryBuilder":
        self._wheres.append(cond); self._params.extend(params); return self
    def order_by(self, expr: str) -> "QueryBuilder":
        self._order = expr; return self
    def limit(self, n: int) -> "QueryBuilder":
        self._limit = n; return self
    def compile(self) -> Tuple[str, Tuple[object, ...]]:
        assert self._table, "FROM is required"
        cols = ", ".join(self._cols or ["*"])
        sql = [f"SELECT {cols} FROM {self._table}"]
        if self._wheres: sql.append("WHERE " + " AND ".join(self._wheres))
        if self._order:  sql.append(f"ORDER BY {self._order}")
        if self._limit is not None: sql.append("LIMIT ?"); self._params.append(self._limit)
        return " ".join(sql), tuple(self._params)

Usage

qb = (QueryBuilder()
      .select("id", "event_time", "user_id")
      .from_("events")
      .where("event_time >= ?", "2025-11-01")
      .where("tenant_id = ?", 42)
      .order_by("event_time DESC")
      .limit(100))
sql, params = qb.compile()
# cursor.execute(sql, params)

Tiny pytest (cements it)

def test_builder_outputs_param_sql():
    qb = (QueryBuilder().select("id").from_("t")
          .where("a = ?", 1).where("b IN (?,?)", 2, 3).limit(10))
    sql, p = qb.compile()
    assert sql == "SELECT id FROM t WHERE a = ? AND b IN (?,?) LIMIT ?"
    assert p == (1, 2, 3, 10)

Trade-offs & pitfalls

• Pros: Readable call sites; safe parameter binding; easy to add options; testable output.
• Cons: More code than handcrafted SQL; can grow heavy if you reimplement a full SQL DSL.
• Pitfalls:
  • Mixing raw string interpolation with params → injection risk.
  • Letting the builder “execute” queries—keep it focused on building, not running.
  • Giant “god” builder—favor a small surface; compose builders for joins, filters, etc.

Pythonic alternatives

• SQL libraries: SQLAlchemy Core, pypika—battle-tested builders; prefer them if allowed.
• Dataclass config + renderer: collect options in a dataclass, then one pure render(cfg) function.
• Generators: stream clauses (yield "WHERE ...") and join at the end—nice for very dynamic queries.

Mini exercise

Add .group_by(expr: str) and make sure compile() renders it before ORDER BY. Write a quick test to confirm order: WHERE → GROUP BY → ORDER BY → LIMIT.

Checks (quick checklist)

• Builder keeps data and params separate.
• Fluent methods return self; order of calls doesn’t break output.
• compile() is pure: returns (sql, params) and doesn’t execute.
• Assertions/validation for required parts (e.g., FROM).
• Tests cover typical clause combinations and parameter positions.