When should a team drop to native SQL, and how do you keep dialect differences from spreading through a codebase?

## What you are actually deciding A native statement buys expressive power and plan control with **coupling**: to a schema, to a dialect, and to a class of change no compiler will help with. Both halves are real, so the question is never "is raw SQL allowed" but "under what justification, and held where". ## When it is justified 1. **Expressiveness.** The operation exists in SQL and not in the object-level language — a window function, a recursive query, a set operation, an aggregate shape the mapper cannot form. This is the strongest case, and the least arguable. 2. **A measured plan need.** The generated statement is demonstrably wrong for the workload and no fetch strategy or hint reaches it. "Measured" means an execution plan and a number, before and after. 3. **Shape.** The work is set-based or scalar, and materialising objects to do it would be waste. And the justifications that should not survive review: unfamiliarity with the object-level language; a preference for reading SQL; copying a statement out of a database console because it was already written; "we might need it later". ## Where the statements live | Placement | What it costs you | |---|---| | Inline at the call site | statements scattered, no inventory, renames missed | | One named catalog (files or named definitions) | one place to grep, one place to review, small indirection | | Per-engine implementations behind one interface | more files, but divergence is visible and bounded | The catalog is the load-bearing decision. It converts "how many raw statements do we have and what do they touch" from an archaeology exercise into a listing, and it gives a schema migration a checklist. Give each statement a name, keep the name at the call site, and let the text sit next to the other text. ## Containing dialect divergence If you support more than one engine — and note that supporting a different engine in tests than in production is *already* supporting two — divergence needs a shape: - **One implementation per engine behind one interface**, chosen once at startup. The alternative, an engine check inside each statement builder, spreads the same conditional through the codebase and guarantees one gets missed. - **Select on capability, not on identity.** "This deployment supports the upsert form we need" is a fact you can declare and test; "this is engine X at version Y" is a fact that drifts and invites a growing table of exceptions. - **Keep the divergent surface small and named.** Paging, string and date functions, locking clauses, conflict handling and identifier quoting are where engines actually differ; the rest of a statement is usually standard. - **Test what you claim.** A dialect abstraction with only one implementation exercised is an untested abstraction; it will be wrong the day the second engine is switched on. Either run the suite against every supported engine or stop claiming to support them. ## Making the cost visible - **Count them.** The number of native statements, and its trend, is a better health signal than any argument about whether one of them is justified. - **Review them as schema-coupled code.** A rename, a type change, or a table split has to sweep the catalog. Put that step in the migration checklist rather than in someone's memory. - **Own the mapped shapes.** Result labels and select-list order are a contract with the mapping; name and alias them so the contract is written down. - **Watch for the second query language problem.** Past some threshold, a codebase in which half the reads are object-level and half are raw asks every reader to know both, and the two are refactored at different speeds. That threshold is a judgement, but it exists, and noticing it is the lead's job. ## The honest single-engine case Many systems will only ever run on one engine. Paying for a dialect abstraction there is real waste, and saying so is legitimate — **provided it is a recorded decision** rather than an accident. The recorded version names the engine, states that portability is not a goal, and notes what would have to change if that ever became false. The accidental version looks identical today and costs a quarter of engineering time the day someone acquires a company running something else. ## What a good answer sounds like A justification bar with three items on it, a catalog so the inventory is knowable, one implementation per engine rather than conditionals at call sites, capability-based selection, a migration checklist that includes the catalog, and an explicit position on whether portability is a goal at all. Plus the metric: how many statements, and is that number growing.