view release on metacpan or  search on metacpan

docs/adr/0036-atomic-upsert.md  view on Meta::CPAN

# ADR 0036 — Atomic upsert API (ON CONFLICT / ON DUPLICATE KEY / UPSERT)

- Status: accepted
- Date: 2026-07-20 (proposed) · 2026-07-25 (accepted)
- Tags: feature, api, sql, capability, cross-repo, drivers

> **Accepted in maintainer review on 2026-07-25.** All nine open questions are
> settled — the numbered entries under [Open questions](#open-questions) record
> each verdict and point at the section holding it. Nothing here is implemented
> yet; no `.pm` was touched to produce this document. Implementation follows the
> cross-repo rollout in §7, core first.
>
> Two verdicts went *against* the draft's own recommendation and are worth knowing
> before reading further:
>
> - **Question 6 — nested relationships:** the flat-only restriction was rejected.
>   Nested data **cascades** and is in scope for v1 (§1).
> - **Question 1 — return shape:** neither the engine-dependent default nor a
>   uniform `'row'`. The values are `row_or_undef` (default), `row_or_die`,
>   `count`, `void`; there is no plain `'row'` (§4).
>
> Deferred by decision, not by omission: batch `upsert_all` (shape reserved in §1,
> implementation after v1) and insert-vs-update reporting (question 8).
>
> **Implementation split:** "v1" was delivered as two tickets, not one — k100
> is the **flat** upsert (landed 2026-07-25) and k101 the **cascade** on top
> (landed 2026-07-26). Two corrections that came *out* of implementing k100 are
> folded into §2 (split criterion sharpened) and §4 (driver obligation to name the
> written row); the corrections that came out of k101 are in §1, at the end of
> the "Nested relationships" subsection — all marked in place.
>
> **k105 (2026-07-26) added the conditional conflict branch**
> (`DO UPDATE ... WHERE`), which this document described in two places while the
> API could not express it. New subsection in §1 ("Conditional conflict branch");
> it also corrects §3 (a second capability, `supports_upsert_where`, with the
> criterion that keeps it from becoming a flag family) and §4 (`row_or_die` plus
> a condition is refused up front, not thrown at runtime).
>
> **k172 (2026-09-22) settled the MERGE question this ADR left open.** The
> maintainer's verdict is option (c): MERGE-based upsert (Oracle, DB2, SQL
> Server) is a deliberate, permanent non-goal, not a driver waiting to be
> written. `supports_upsert` stays INSERT-borne; a MERGE upsert would be its own
> capability with its own guarantees. New subsection in §3 ("MERGE-based upsert
> stays out of scope"); the §1 k205 boundary note and the §7 rollout rows for
> the MERGE engines now point at it.

## Context

### The over-promise this ticket closes

The driver READMEs (Pg, MySQL, SQLite) advertised "upsert via
`insert_or_update`". That was never true. `DBIO::Row::insert_or_update`
(`lib/DBIO/Row.pm`) is a bare alias:

```perl
sub insert_or_update { shift->update_or_insert(@_) }
sub update_or_insert { my $self = shift; return ($self->in_storage ? $self->update : $self->insert); }
```

It is a *client-side, two-step* helper: it looks at the in-memory
`in_storage` flag and issues either an `UPDATE` or an `INSERT`. It never
consults the server about whether a conflicting row exists, so it is subject
to the classic time-of-check/time-of-use race. The same is true of the
ResultSet-level composite writes that read-then-write:
`find_or_create`, `update_or_create` (`lib/DBIO/ResultSet.pm`), each of which
already carries a `B<Note>` in its POD warning that it "is subject to a race
condition ... use ... inside a transaction". These are honest, useful methods
— but none of them is an **atomic, server-side upsert**.

The README cleanup (audit 2026-07-20) stops the over-promise. This ADR is the
real feature: a cross-driver API for a single-statement upsert that pushes the
conflict resolution down to the engine — PostgreSQL / SQLite
`INSERT ... ON CONFLICT ... DO UPDATE|NOTHING`, MySQL
`INSERT ... ON DUPLICATE KEY UPDATE`.

### The seams (verified, so reviewers know the design is real)

**Insert SQL is generated through one chain.** `DBIO::Storage::DBI::insert`
(`lib/DBIO/Storage/DBI.pm`) assembles an options hash `$sqla_opts` — today its
only interesting key is `returning` — and calls:

```
Storage::DBI::insert
  -> _execute('insert', $source, $to_insert, $sqla_opts)
    -> _prep_for_execute -> _gen_sql_bind
      -> $self->sql_maker->insert( $from, $to_insert, $sqla_opts )   # <-- the seam
```

`$self->sql_maker->insert(...)` resolves to
`DBIO::SQLMaker::ClassicExtensions::insert($table, $data, \%options)`. That
method already reads `$options->{returning}` and, when set, appends the
RETURNING tail via `_insert_returning` before/around `next::method` (the
`SQL::Abstract` core). **This is the exact seam a driver extends for a conflict
clause** — it is the same shape as the RETURNING tail that is already bolted on
there. A driver's `DBIO::<Driver>::SQLMaker` subclass (e.g.
`DBIO::PostgreSQL::SQLMaker`, `DBIO::MySQL::SQLMaker`, `DBIO::SQLite::SQLMaker`
— all already exist) overrides `insert` to read a new `$options->{on_conflict}`
structure and append the engine-specific clause. No new core plumbing method is
strictly required; the options hash is already the transport.

**Capabilities are declared, never string-matched.**
`DBIO::Storage::DBI::Capabilities` holds a flat `@capabilities` list exposed
through `mk_group_accessors( dbms_capability => "_supports_$_" )` and
`mk_group_accessors( use_dbms_capability => "_use_$_" )`. A driver opts in with
`__PACKAGE__->_use_X(1)` or by defining `_determine_supports_X`; the default
`_determine_supports_*` returns `0` (conservative). This is the mechanism
ADR 0026 used for `transactional_ddl` / `supports_if_exists`, and the mechanism
ADR 0021 established the *anti*-pattern for (never branch on `$driver_name`).
The class also carries `$CONTRACT_VERSION` (currently `1.1`), guarded by the
`t/test/12_contract_version.t` tripwire per ADR 0024.

**RETURNING is already a per-engine capability.** `Storage::DBI::insert` only
sets `$sqla_opts->{returning}` when `$self->_use_insert_returning` is true
(`insert_returning` / `insert_returning_bound` capabilities). PostgreSQL
declares it version-gated (`_determine_supports_insert_returning` → `>= 8.002`);
MySQL does not declare it at all; the DBIO-SQLite driver does **not** currently
declare it either (even though SQLite has had RETURNING since 3.35). When