IF Commons GitHub

Idempotent migrations

0012Active

0012-sql-idempotency · owner if · domain data

Making a step safe to repeat

FR-001checkpassing

A dialect MUST be able to say which of its DROP and ADD COLUMN forms have an IF [NOT] EXISTS (ifExists) and to give, for a table, a column, a constraint and an index, a query that returns a row if and only if it exists (probes), or no probe where the engine cannot say it.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a dialect says which forms have IF EXISTS and gives a probe for a table, a column, a constraint and an index that finds it on the engine

Proved by: data/suite/idempotency.test.ts

Tests: passed: suite/idempotency.test.ts a dialect says which forms have IF EXISTS and gives a probe for a table, a column, a const

Cited by: sql/physical/dialect

FR-002checkpassing

A statement of a step MAY carry a guard, a query and the answer that means the statement is to be skipped; a run MUST honour it, so that a guarded statement is not run when its work is done; and the guard MUST be part of the step's identifier, so that two steps that differ only in their guards are two steps.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a run skips a guarded statement whose work is done, and the guard is part of the identity of the step

Proved by: data/suite/idempotency.test.ts

Tests: passed: suite/idempotency.test.ts a run skips a guarded statement whose work is done, and the guard is part of the identity

Cited by: sql/lifecycle/plan

FR-003checkpassing

The idempotency transform MUST write a statement in its natural form where the dialect has one (ADD COLUMN IF NOT EXISTS, DROP COLUMN IF EXISTS, DROP TABLE IF EXISTS, DROP INDEX IF EXISTS, DROP CONSTRAINT IF EXISTS); MUST guard it by a probe where it has none (adding a constraint, renaming a table, a column, a constraint or an index, dropping a constraint or a column the dialect cannot drop in the natural form); and MUST leave a statement that is idempotent already (CREATE TABLE IF NOT EXISTS, an upsert, a delete by key, SET NOT NULL, SET DEFAULT) as it is.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a statement is natural where the dialect has a form, guarded where it has none, and the step says which

Proved by: data/suite/idempotency.test.ts

Written in: data/sql/extensions/idempotency/transform.ts

Tests: passed: suite/idempotency.test.ts a statement is natural where the dialect has a form, guarded where it has none, and the st

Cited by: Data, sql/extensions/idempotency/transform

FR-004checkpassing

Each step MUST say how it is idempotent: natural where every statement is, guarded where some are guarded and none is left, and ledger-only where some statement can be made neither, so that it is safe only because the ledger runs it at most once; the same MUST hold for the statements that undo it.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a statement is natural where the dialect has a form, guarded where it has none, and the step says which

Proved by: data/suite/idempotency.test.ts

Written in: data/sql/extensions/idempotency/transform.ts

Tests: passed: suite/idempotency.test.ts a statement is natural where the dialect has a form, guarded where it has none, and the st

FR-005checkpassing

A step written by a person MUST be ledger-only unless the person gives its statements guards.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a step a person wrote relies on the ledger unless it is guarded, and a project can refuse such a step

Proved by: data/suite/idempotency.test.ts

Written in: data/sql/extensions/idempotency/transform.ts

Tests: passed: suite/idempotency.test.ts a step a person wrote relies on the ledger unless it is guarded, and a project can refuse

Cited by: Data, sql/extensions/idempotency/transform

On by default

FR-006checkpassing

The idempotency transform MUST be an extension of the lifecycle that contributes through the engine's point lifecycle.transform, and its default composition (defaultIdempotency), which the strategies include, MUST have it on; a project MUST be able to leave it out, or to set it to off, for plain, unguarded statements.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: the transform is an extension that contributes through lifecycle.transform, is in the default composition, and can be left out or switched off

Proved by: data/suite/idempotency.test.ts

Written in: data/sql/extensions/idempotency/mod.ts

Tests: passed: suite/idempotency.test.ts the transform is an extension that contributes through lifecycle.transform, is in the defa

FR-007checkpassing

A project MUST be able to set that a step that is ledger-only is refused, as an error value naming the step, instead of allowed.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a step a person wrote relies on the ledger unless it is guarded, and a project can refuse such a step

Proved by: data/suite/idempotency.test.ts

Tests: passed: suite/idempotency.test.ts a step a person wrote relies on the ledger unless it is guarded, and a project can refuse

Cited by: sql/extensions/convergence/connect, sql/extensions/convergence/propose, sql/lifecycle/plan

Carrying a guard in plain SQL

FR-008checkpassing

Where the dialect has a procedural block (PostgreSQL's DO), a guarded statement MUST be able to be written as one block that runs the statement only if the guard says so, so that a script that no runner reads still does no harm run twice; where it has none, the guarded statement MUST NOT be written as plain SQL, and the answer MUST say so.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: a guarded statement is one block of plain SQL on PostgreSQL that does no harm run twice, and is refused where the dialect has no block

Proved by: data/suite/idempotency.test.ts

Written in: data/sql/extensions/idempotency/inline.ts

Tests: passed: suite/idempotency.test.ts a guarded statement is one block of plain SQL on PostgreSQL that does no harm run twice, a

Cited by: Data, sql/extensions/idempotency/inline

What the database holds

FR-009checkpassing

On SQLite, DuckDB and PostgreSQL, running each step of a plan twice in a row, without the ledger, MUST leave the database as running it once did and MUST raise no error, for the steps that are natural or guarded; and running a whole plan again from its start, without the ledger, MUST do the same.

Enforced (check): .github: cd commons/data && bun run verify — suite/idempotency.test.ts: on three real engines every step can be run twice without the ledger with the result of running it once, and so can a whole plan

Proved by: data/suite/idempotency.test.ts

Tests: passed: suite/idempotency.test.ts on three real engines every step can be run twice without the ledger with the result of ru

Out of scope

  • A step the engine cannot repeat and that has no probe (a constraint of SQLite): it is ledger-only, and FR-007 can refuse it.
  • A data migration written by a person: FR-005.
  • Making a long step resumable half-way: the safety features (0015).

Edge cases

  • A column added on PostgreSQL: ADD COLUMN IF NOT EXISTS, natural, per FR-003.
  • A column added on SQLite: guarded by pragma_table_info, per FR-003.
  • A column renamed on any engine: guarded by the old column's probe, per FR-003.
  • A foreign key added on PostgreSQL: guarded by pg_constraint, per FR-003; on SQLite it cannot be added at all (0011-sql-lifecycle FR-008).
  • A step a person wrote: ledger-only, per FR-005; with refuse, an error value, per FR-007.
  • The feature left out: the statements are the core's and no step says how it is idempotent, per FR-006.

Assumptions

  • A probe reads the catalog of the connection's current schema.
  • A guard is a read; it does not change the database.

Open questions

OQ-1open question

Whether a guard may be a list of conditions (a column exists and has this type), for the steps of the safety features that check a column's shape before a swap.

Key entities

  • Natural form - a statement written with IF [NOT] EXISTS or as an upsert.
  • Guard - a catalog query and the answer that means a statement's work is done.
  • Idempotency level - natural, guarded or ledger-only.

Success criteria

SC-001success criterion

Any step the lifecycle makes can be run twice, by a runner or by hand, with the same result as once, or says plainly that it cannot.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/extensions/convergence/connect, sql/extensions/convergence/propose, sql/extensions/idempotency/inline, sql/extensions/idempotency/mod, sql/extensions/idempotency/transform, sql/lifecycle/plan, sql/physical/dialect

Titles, headings, exports, tables, columns and requirements. Esc closes.