Why does a data-access layer translate database errors into its own exception types instead of exposing vendor codes?

## What the engine actually reports When a database rejects a statement, it answers in its own vocabulary: a **numeric code**, a **state string** (the SQL standard defines a five-character one), and a **message** written for a human reader. Only the state string is standardised at all, and it is coarse - a whole family of integrity problems can share one value. The numeric code is the engine's own invention; the same number means different things on different engines. The message is the worst thing to depend on: it is written for people, it is reworded between versions, it may be emitted in another language, and it often embeds row values. Application code needs none of that detail to make its decision. It needs to know **what kind of failure this is**. ## The portable kinds a layer exposes A data-access layer catches the raw failure and rethrows one of a small set of kinds, keeping the original as the cause: | Portable kind | What happened | What calling code usually does | |---|---|---| | Constraint violation | a unique, foreign-key, check or not-null rule rejected the row | map to a business outcome, or treat as a bug | | Deadlock victim | the engine aborted this unit to break a cycle of waits | re-run the whole unit | | Serialization failure | concurrent writes could not be given a consistent order | re-run the whole unit | | Lock or statement timeout | a wait or a statement passed a configured limit | retry sparingly, often surface it | | Connection lost | the link dropped, possibly around commit | outcome unknown: retry only if idempotent | | Data or conversion error | a value did not fit the target column | a bug in the write; re-running changes nothing | Two properties matter more than the exact membership of that list: the kinds are **few enough to branch on exhaustively**, and each carries a default answer to *can this work be repeated?* ## Why matching a code or a message is a defect - **Codes are not portable.** A branch written against one engine's number silently stops matching on another, and the same number may mean something unrelated there. - **Messages are not an interface.** No engine promises to keep its wording stable, and a localised deployment can return the text in another language. - **The failure mode is silence.** A code branch that stops matching does not raise an alarm; the duplicate simply falls through to the generic handler and the user is shown an internal error instead of *that address is already taken*. - **Tests lie.** It is common for tests to run against one setup and production against another. A code-matching branch is green in the suite and broken where it matters. - **It spreads.** Once one use case branches on a raw code, the number is copied into every other catch block that needs the same decision. ## What translation costs Translation **coarsens**. Several distinct engine conditions collapse into one kind, and the specific reason - which constraint, which column - is no longer expressed by the type. That is why layers keep the original failure as the cause rather than discarding it. The rule is not *never look at engine detail*; it is **look at it in exactly one place**. A single mapping component may read the constraint name or the state string and turn it into something the application defines; use cases above it branch only on the portable kind. When that component is the only file that knows an engine's vocabulary, moving to another engine is a change to one file rather than an archaeology exercise. ## The kind is an input, not the decision Knowing the kind does not finish the job. Sort the kinds into three buckets and the decision follows: 1. **Transient** - a deadlock victim or a serialization failure. Nothing was applied; re-running the whole unit is legitimate. 2. **Permanent** - a constraint violation, a data conversion error. Re-running the identical work fails identically; either report the rule to the caller or fix the write. 3. **Ambiguous** - a connection lost around commit. The work may or may not have landed, so a retry is only safe if the operation is idempotent. One more thing holds regardless of kind: if the failure reached the database, the unit of work is normally finished. The exception is not something the calling code can absorb and then continue writing through. ## Where layers differ Data-access layers differ in how much detail survives translation: some publish a rich hierarchy with a distinct type per integrity rule, others publish a handful of kinds and leave the rest to the cause chain. Both are workable, and the choice mostly affects how much of the mapping the application writes itself. What is not workable in either style is application code reading the engine's message text to decide what happened.