As a lead, how would you pick a house query-authoring style for a large, long-lived codebase?
answer
- default plus escape hatch
- score the codebase, not the style
- codegen needs the schema at build
- readability at three joins
- mixed styles collapse to the weakest
basics
~20 sScore the codebase on rename safety, control of emitted SQL, portability, readability at three joins, runtime assembly, discoverability and build cost; then set one default, one named escape hatch, and an enforcement point. Mixed styles collapse to the weakest.
solid answer
~50 sI would not look for the best style; I would set a default and a stated exception. The axes that decide it are refactoring safety, how much of the emitted SQL you control, portability across engines, readability once a query has three joins, whether the shape must vary at runtime, how you inventory every query touching a table, and what the build must carry. A generated typed DSL buys compile-time safety and charges a generation step that needs the schema at build time and an owner when it breaks - worth it for a long-lived, churning schema, not for a small stable one. Before paying that, I would take the cheap rung: every query declared and validated at boot, with that boot run in the test suite. Then enforce it in the build rather than in review: three styles with no rule leave the codebase at the weakest one.
go deeper
Know that teams pick a standard way to write queries and that each way trades safety, control and readability differently.
Be able to compare the styles on concrete axes and say which one you would reach for in a given situation, and why.
Argue the trade honestly, including the build and ownership cost of code generation, and describe how the rule is enforced rather than merely written down.
Own the policy: a default, a budgeted exception, an enforcement point, and a clear statement of what the standard costs and when you would revisit it.
## Framing the decision There is no authoring style that wins on every axis, so the lead's job is not to find the best one - it is to pick a **default**, name the **escape hatch**, and make both enforceable. A codebase with one dominant style and a stated exception is far healthier than one where every module chose well in isolation. ## The axes worth scoring - **Refactoring safety.** Where do the field names live: symbols the compiler holds, or text nothing checks? This is the axis that compounds over a long-lived codebase. - **Control over the emitted statement.** How precisely can you say what SQL comes out? Generated styles trade control for brevity; the further from the SQL you author, the more you accept the layer's decisions on hot paths. - **Portability across engines.** Generated SQL adapts to the dialect; hand-written SQL does not. Note that portable *text* is not portable *performance* - plans and index behaviour do not travel. - **Readability at three joins.** Styles diverge sharply here. Some read beautifully at one predicate and become unreadable when the query grows; a written query language usually holds up best. - **Assembly at runtime.** Some styles compose from parts naturally; others fix the shape at declaration time. If a surface genuinely needs varying shape, a style that cannot express it will be worked around badly. - **Discoverability.** How do you find every query touching a table? Compilation, a registry, a search, or nothing. - **Build and tooling cost.** Code generation needs the schema at build time, a step that cannot be skipped, generated sources in the build output, and slower builds. It also couples compilation to schema state. - **Team cost.** Onboarding, review fluency, and whether an unfamiliar reader can tell what a query does. | Axis | Written query text | Builder in code | Schema-generated typed DSL | Derived from a name | |---|---|---|---|---| | Rename safety | Runtime or boot | Depends on symbols vs strings | Build | Boot | | Control of SQL | High | Medium | High | Low | | Readability at 3 joins | High | Medium | Medium | Poor | | Runtime assembly | Awkward | Natural | Natural | Not possible | | Build cost | None | None | Generation step | None | ## How I would actually decide 1. **Score the codebase, not the style.** Long-lived and schema-churning favours compile-time safety; short-lived or schema-stable does not justify the build coupling. 2. **Check the build's tolerance.** A generation step needs a schema available where the build runs, an ordering guarantee before compilation, and someone who owns it when it breaks. If that ownership does not exist, choosing it is choosing a future outage in the build. 3. **Buy the cheap rung first.** Before any generation step, make every query declared and validated at boot, and run that boot in the test suite. Most of the value, none of the infrastructure. 4. **Name one default and one escape hatch.** For example: derived finders for trivial lookups, written queries as the default, and an explicit path for hand-written statements where the plan matters - each exception carrying a stated reason. 5. **Enforce it where it cannot be forgotten.** A review rule alone decays; a build check, a boundary that keeps query text out of service code, and a validation step do not. ## The failure modes of getting this wrong - **Three styles, no rule.** Every inventory question then resolves to the weakest style present, and the compile-time safety the team paid for protects only part of the code. - **A generation step nobody owns.** It breaks on a machine that lacks a schema, and the fix becomes "regenerate and commit the output", which quietly turns build output into hand-edited code. - **Standardising on the style that reads best at one predicate.** The codebase's real queries have four, and the style everyone praised in the proposal is unreadable in the pull request. - **Treating compile safety as correctness.** Names resolving is not the same as the predicate being right, the join not multiplying rows, or the plan being acceptable. ## What a strong answer sounds like Name the axes, admit that the ranking flips depending on codebase lifetime and schema churn, and land on a concrete policy - default, exception, enforcement point - rather than a favourite. Say plainly what the chosen default costs, because a lead who cannot name the downside of their own standard has not made a decision, only expressed a preference.
- What does a code-generation step really cost a team?A schema, or a definition of it, available wherever the build runs; an ordering guarantee that generation precedes compilation; generated sources treated as build output rather than editable code; slower builds; and an owner for the day it breaks. Without that owner the fix degenerates into committing generated files by hand, which loses the guarantee entirely.
- When is compile-time query safety not worth buying?When the schema is stable, the codebase is short-lived, or the build cannot host a generation step reliably. The compounding value of compile safety comes from repeated schema change over years; without that churn, boot-time validation catches the same class of error at a fraction of the cost and with no coupling between compilation and schema state.
- How do you keep a mixed-style codebase from degrading?Name the default, name the exception and the reason it is allowed, and put the rule somewhere mechanical - a boundary keeping query text out of service code, a build check that validates declarations, a review rule as backup rather than as the mechanism. Also state that inventory questions are answered at the weakest style present, so exceptions are budgeted, not free.
saying these in an interview costs you the question
- Picks a favourite style without naming what it costs
- Treats compile-checked queries as automatically correct queries
- Ignores that a generation step needs the schema at build time
- Assumes one style can serve trivial finders and hot read paths equally
- Lets each module choose its own style with no stated rule
- Judges readability on one-predicate examples rather than real queries