IF Commons GitHub

sql-typed

0053Active

0053-sql-typed · owner if · domain data

Requirements

FR-001checkpassing

A value of SQL MUST be an Expr<T> that gives the TypeScript type of what it computes, nullable where it may be null; comparison, arithmetic, logic, text, in, between, like, case and the aggregates MUST take expressions of one type, so that a number compared with a text, a name summed, a text added to or null compared with = does not compile.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: expressions give the type of what they compute and only compile for the types they fit: a number is not compared with a text, a name is not summed, a null is asked with isNull, and a column that may be null is a nullable expression

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

Written in: data/sql/typed/expr.ts, data/sql/typed/mod.ts

Tests: passed: suite/typed.test.ts expressions give the type of what they compute and only compile for the types they fit: a

FR-002checkpassing

An expression MUST be data (a tree) that a dialect writes, and an operand MUST be an expression or a plain value of the type.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: expressions give the type of what they compute and only compile for the types they fit: a number is not compared with a text, a name is not summed, a null is asked with isNull, and a column that may be null is a nullable expression

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

Written in: data/sql/typed/expr.ts

Tests: passed: suite/typed.test.ts expressions give the type of what they compute and only compile for the types they fit: a

FR-003checkpassing

A table MUST be a value with the type of its rows, the type of what an insert takes and the name by which it is known in a query, with a typed column for each key of the row; the keys are the names a program writes and the names in SQL are those the table says; a table MUST be able to be known by another name (a self-join) and be put in another schema; a key that the row does not have MUST NOT compile.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: a table has a typed column for each key of its row, the keys are the names a program writes and the names in SQL are those the table says, a table can be known by another name and put in another schema

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

Written in: data/sql/typed/mod.ts, data/sql/typed/table.ts

Tests: passed: suite/typed.test.ts a table has a typed column for each key of its row, the keys are the names a program write

FR-004checkpassing

A query MUST be built from a table and joins (inner, left, right, full, cross) into a scope of typed columns by the names of the tables; a left, right or full join MUST make the columns of the other side nullable; a name used twice in a query MUST be refused; select MUST give the type of the row from the expressions it names.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: a select with joins (inner, left, right, full, cross), conditions, groups, having, order, limit, offset and distinct gives rows of the type it names, on SQLite, DuckDB and PostgreSQL; an outer join makes the columns of the other side nullable

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

Written in: data/sql/typed/mod.ts, data/sql/typed/select.ts

Tests: passed: suite/typed.test.ts a select with joins (inner, left, right, full, cross), conditions, groups, having, order,

FR-005checkpassing

A query MUST have where, group by, having, order by (by a column or by a key of the row), limit, offset and distinct, union, union all, intersect and except of selects of one row type, in, not in, exists and value subqueries, derived tables and common table expressions, and MUST run on SQLite, DuckDB and PostgreSQL through a driver, giving rows of the type named, with the counts and sums that a driver gives as text read as numbers.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: a select with joins (inner, left, right, full, cross), conditions, groups, having, order, limit, offset and distinct gives rows of the type it names, on SQLite, DuckDB and PostgreSQL; an outer join makes the columns of the other side nullable; suite/typed.test.ts: set operations, subqueries (in, not in, exists, a value), common table expressions, derived tables and a table joined to itself under another name run on each engine, and a name used twice in one query is refused

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

Written in: data/sql/typed/select.ts

Tests: passed: suite/typed.test.ts a select with joins (inner, left, right, full, cross), conditions, groups, having, order, ; passed: suite/typed.test.ts set operations, subqueries (in, not in, exists, a value), common table expressions, derive

FR-006checkpassing

Every value MUST be written by the dialect as a literal, so that a quote in a text cannot become SQL; a date MUST be written as text; a number that is not finite and bytes MUST be an error value that says so; an empty in MUST be false and an empty and true.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: every value is written by the dialect as a literal, so a quote in a text is doubled and cannot become SQL; a date is written as text; a number that is not finite, and bytes, are an error value; an empty list is false and an empty and is true

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

Written in: data/sql/typed/compile.ts

Tests: passed: suite/typed.test.ts every value is written by the dialect as a literal, so a quote in a text is doubled and ca

FR-007checkpassing

insertInto MUST take the rows of the table's insert type (what the database fills may be left out; an unknown key or a value of another type MUST NOT compile) and refuse rows of other columns than the first; it MUST have onConflictDoNothing and onConflictDoUpdate (with the proposed value excluded); update MUST take values or expressions of the table's columns; an update and a delete MUST NOT be runnable until they say where or all; returning MUST give the rows changed, typed as a select's.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: insert (one row and many, with what the database fills left out), upsert (do nothing, do update with the proposed value), update with values and expressions, delete and returning run on each engine; an update or a delete cannot be run until it says where or all, and a row of other columns than the first is refused

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

Written in: data/sql/typed/mod.ts, data/sql/typed/mutate.ts

Tests: passed: suite/typed.test.ts insert (one row and many, with what the database fills left out), upsert (do nothing, do u

FR-008checkpassing

Each model (module of governance) that owns a table MUST get its own file <model>.auto.ts, made from the logical schema and governance by tools/typed.ts and never by hand: for each table a row type, an insert type, a key type and a typed table, with a closed list of codes as a union of its names and what the database fills (a default, a null) optional in the insert; mod.ts MUST name the files; and a file that is missing, stale or stray MUST fail bun run verify.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: the typed tables of each model are what the models say (none stale, missing or stray), every table of every model is owned by one, and the row types name the columns that a database made from the models has

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

Written in: data/sql/typed/generate.ts, data/sql/typed/models/mod.ts, data/tools/typed.ts

Tests: passed: suite/typed.test.ts the typed tables of each model are what the models say (none stale, missing or stray), eve

FR-009checkpassing

Every table of every model MUST be owned by exactly one model, and the typed columns of a table MUST be the columns that a database made from the models has: each table is read through its typed columns on each engine.

Enforced (check): .github: cd commons/data && bun run verify — suite/typed.test.ts: the typed tables of each model are what the models say (none stale, missing or stray), every table of every model is owned by one, and the row types name the columns that a database made from the models has

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

Written in: data/sql/typed/generate.ts

Tests: passed: suite/typed.test.ts the typed tables of each model are what the models say (none stale, missing or stray), eve

Out of scope

  • Typed access to views, routines and the tables of a project's own models; a project makes them with typedFiles from its own modules.
  • Bytes as literals, and prepared statements with parameters: the driver of this library takes text, and every value is a literal.
  • Decoding of every driver's types (decimals, dates, wide integers) into one: values are as a driver gives them, except the counts, sums and averages (FR-005).
  • A DEFAULT VALUES insert, which dialects do not share.

Edge cases

  • Rows of one insert with other columns than the first are refused, per FR-007: insert them apart.
  • A column that a model gives a default is optional in the insert and still present in the row, per FR-008.
  • A table that is in no model's concept or code list is an error of the generation, per FR-009.
  • A key of a table that a language reserves (default, class) is written with an underscore after it, per FR-008.

Assumptions

  • The typed algebra is built on the logical schema and governance, with the engines of the tests SQLite, DuckDB and PGlite (PostgreSQL).
  • The generated files are committed and are in the package; a project that composes other models generates its own.

Open questions

OQ-1open question

Whether arcactl generate should also write a project's typed tables; now tools/typed.ts does it for this library's models.

Key entities

  • Expr - a typed value of SQL, as a tree.
  • Table - a typed table: the row, the insert and the name in a query, with a typed column for each key.
  • Select - a query that says what it wants, with the type of its row.

Success criteria

SC-001success criterion

A program that reads and writes the tables of a model does so with the types the model gives; a change of the model that the program does not follow fails to compile.

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/lifecycle/adopt, sql/typed/compile, sql/typed/expr, sql/typed/generate, sql/typed/mod, sql/typed/mutate, sql/typed/select, sql/typed/table, sql-loads

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