# Array and database statements

> Read when the stack reads or writes rows (`s.db.*`), or transforms an array in place (`s.array.map`, `s.array.union`).

Array blocks (an `if`/`transform` is applied per item):

- `s.array.map({ source, as?, transform? })` — `transform` is either a per-item `Value` expression (each item maps to that value) or a **record of values** (each item maps to an object with those keys), or a list of `{ key, value }` pairs, for a key computed per item or two rows sharing one. Use `ref("$this")` for the item and `ref("$index")` for its position. These are THIS statement's own bindings, in a value expression — not the JavaScript lambda contract (see **Lambda bodies**), which binds a different set per surface and is written with `lam.fn`.
- `s.array.union({ source, with?, as?, transform? })` — set-union two arrays.

DB reads/writes (`table` is a def handle or name; `fieldName` defaults to the
primary key `id`):

- `s.db.get({ table, fieldName?, fieldValue, lock?, output?, as? })` — one row by field match; `output` restricts returned columns (and overrides column visibility — it can pull `internal` columns like a password hash).
- `s.db.get_by_id({ table, id, output?, addon?, tableAlias?, as? })` — get by primary key. Takes `id`, NOT `fieldName`/`fieldValue`; binds the row or `null` like `db.get`. Both spellings are live in pulled workspaces.
- `s.db.has({ table, fieldName?, fieldValue, as? })` — existence test.
- `s.db.del({ table, fieldName?, fieldValue, as? })` — delete by field match.
- `s.db.add({ table, row?, data?, output?, as? })` — insert; `row` is a partial keyed by column.
  - A row CELL takes a tagged `Value`, a nested object of sub-keys, or a bare JS literal typed against that column: `row: { is_hidden: true, notes: "…" }` encodes exactly as `{ is_hidden: c.bool(true), notes: c.text("…") }`. The tag comes from the COLUMN, not the literal — `10` on an `f.decimal()` column is `const:decimal`, not `const:int` — so a literal contradicting its column is a compile error on a `f.*`-schema table (`{ is_hidden: "yes" }` on an `f.bool()` column) and throws at encode on a raw-`ColumnDef[]` one. An `f.enum()` column keeps its member union. A column with no literal form — obj/json/list/geo/vector/file — still needs `c.obj`/`c.array`.
  - `null` is accepted on EVERY column, including ones that refuse every other literal, and encodes `const:null` — a write OF null, not the same as omitting the key (omitted takes the column's type default on `add`: `[]` for a list, `{}` for obj/json, else `null`; on `edit` it is left untouched). A column's `nullable` is not consulted; the engine refuses a null it forbids.
  - An `f.password()` cell takes the PLAINTEXT — the column hashes on write, so a pre-hashed value, or a hashing filter on the cell, stores a hash of a hash that `security.check_password` can never match.
- `s.db.edit({ table, fieldName?, fieldValue, row?, data?, output?, as? })` — update by field match.
- `s.db.patch({ table, fieldName?, fieldValue, data, output?, as? })` — merge a partial (`data` is an object value).
  On these three, `output` restricts the columns of the RETURNED row only — it does not change
  what is written. Not offered on `db.del`/`db.has` (their result is a scalar) or on
  `db.add_or_edit` (no output envelope).
- `s.db.add_or_edit({ table, fieldName?, fieldValue, row?, data?, as? })` — upsert.
- `s.db.query({ table, where?, additionalWhere?, bind?, sort?, paging?, external?, returnType?, distinct?, eval?, output?, lock?, addon?, as? })` — search.
  - `where` / `additionalWhere` — `expr(...)`, an `expr[]` (ANDed), or a raw `Value`. Rides `context.search`.
    - ⚠ `ignoreEmpty` DROPS the predicate when the operand is empty — it does not match zero rows. On an `in` comparison an empty list therefore returns the UNFILTERED set, so never use it to scope rows to a permitted-id list: an empty list of permissions returns everything.
    - For the full operator set use `cmp(left, op, right, { ignoreEmpty? })` — `op`: `in`/`not in`/`like`/`ilike`/`between`/`contains`/`includes`/`overlaps`/`@>`/`~`/`search`/… plus the `expr` comparisons. Database-only — a runtime condition takes the `expr` set only.
    - Compose nested boolean logic with `and(...)` / `or(...)` groups (also available on `addon()` `where`).
    - An operand may be a bare value (`col`/`inp`/`ref`/`auth`/`c.*`) OR a **filtered** value (`withFilters(...)`) inline. Hoisting into a prior `s.set_var` is a readability option, not a requirement.
  - `bind: [{ table, as?, join?, where? }]` — joins (`context.bind[]`). `join` defaults to `"inner"`. `as` defaults to the table name; two joins to the same table need distinct aliases.
    - ⚠ In `where`/`sort`/`eval` a JOINED column takes a dotted path (`col("team_row.id")`); THIS query's own columns stay **bare** (`col("team")`). Qualifying your own by table name needs `tableAlias` (same rule as `aggregate`) — without it the engine reads the operand as text and 400s `ParseError: Invalid value for param` naming the OTHER operand, so it throws at export instead.
    - `bind: [{ table: team, as: "team_row", join: "left", where: expr(col("team"), "=", col("team_row.id")) }]`
  - `returnType` — `"list"` (default) | `"single"` | `"count"` | `"exists"` | `"stream"` | `"aggregate"`. Drives `context.return.type` AND the `InferResponse` shape: `count`→`number`, `exists`→`boolean`, `single`→`Row|null`, `stream`→`Row[]` (pageable, no envelope), `list`→`Row[]`/envelope, `aggregate`→rows keyed by the `aggregate.group`/`eval` aliases. ⚠ A bare `count` of ZERO serializes as an EMPTY body, not `0` — a client parsing JSON gets a parse error on the one result it most needs to handle. Wrap it: `response: { count: ref("n") }`.
  - `eval: [{ name, as, filters? }]` — computed columns (`context.eval[]`). Each `as` grafts onto the row as an `unknown` key in `InferResponse`; shadowing a real column throws. Write `name` **bare** (`"embedding"`) — it is alias-qualified on emit exactly like `aggregate` (a bare eval name is `Unsupported param format` at runtime), and the statement declares the alias it used. An `as` alias is `sort`able in the SAME query.
    - An `eval`/`sort`/`where` filter pipeline compiles to **SQL**, so it resolves a DIFFERENT registry than `fl.*` (which runs in the request): the vector family, geo `distance`/`within`/`covers`, `search_rank`, the aggregators. Exported as `QUERY_EXPRESSION_FILTERS`/`VECTOR_FILTERS`.
    - **Vector similarity search** — the ONLY way to query an `f.vector` column (no `SearchOp` does distance). `eval: [{ name: "embedding", as: "distance", filters: [{ name: "vector_cos_distance", arg: [inp("q")] }] }]` + `sort: [{ sortBy: "distance", dir: "asc" }]` ranks in the DATABASE over the column's index. Match the filter to the index `op` (`vector_cos_distance`↔`vector_cosine_ops`, `vector_l2_distance`↔`vector_l2_ops`, `vector_l1_distance`↔`vector_l1_ops`, `vector_inner_product`↔`vector_ip_ops`); `vector_cos_similarity` is the inverse, so sort it `desc`. The same filter on a `where` operand cuts off BY distance instead of by row count.
  - `aggregate: { group?, eval?, sort?, paging? }` (with `returnType:"aggregate"`) builds `context.return.aggregate`. `group`/`eval` are `{ name, as, filters? }`, an aggregator like `sum`/`count` riding `filters`. Some aggregators resolve ONLY here, not in a runtime value pipeline: `count_distinct`, `median`, `to_list`/`to_distinct_list` (each with `_asc`/`_desc`), and `vector_distance`.
    - ⚠ Write each `name` as a **bare** column (`"status"`). It is alias-qualified to `"<alias>.status"` on emit — the engine rejects an unqualified column in an aggregate with `Unsupported param format`. An already-dotted `name` (a `bind`ed/joined column) passes through.
    - The alias it qualifies WITH is `tableAlias` when you set one, otherwise the table's name — and the statement DECLARES that alias (`dbo.as`) so the qualified name resolves. Nothing to do by hand; a bare `name` is the form to write.
  - `sort: [{ sortBy: <col>, dir?: "asc"|"desc"|"rand" }]` and `paging: { page?, per_page?, offset?, totals?, metadata?, search?, sort? }` ride `context.return.list`.
    - ⚠ `paging` with a page/per_page/offset field and `metadata` on (the DEFAULT) wraps the result in an envelope `{ items: Row[], curPage, nextPage, prevPage, offset, perPage, itemsReceived }` — plus `itemsTotal`/`pageTotal` when `totals: true` — instead of a bare `Row[]`. `InferResponse` reflects it. Pass `metadata: false` to keep the bare array.
    - Read `nextPage` (`number|null`) as the typed has-next signal.
    - **Input-bound paging:** `page`/`per_page`/`offset` also accept a `Value` (`inp("page")`), riding `context.simpleExternal` while the static block stays the engine gate (`enabled:true`). `paging.search`/`sort` are `Value` dynamic overrides.
    - A `search`/`sort`-only `paging` (no numeric field) does NOT paginate.
  - `external: { value, permissions? }` — the classic whole-config blob (forces the gate on). It falls back to input-bound `paging` when it resolves empty, so supplying both is valid.
  - `distinct` — `"auto"` (default) | `"yes"` | `"no"`, riding `context.return.<list|stream>.distinct`.
- `s.db.truncate({ table, reset?, as? })` · `s.db.schema({ table, path, as? })`.
- `s.db.direct_query({ sql, responseType?, args?, parser?, as? })` — `sql` is a **raw string** (not a `Value`); binds go in `args: Value[]`. `parser: "template_engine"` renders the body as a template first — how a query interpolates a column or table name a bound arg cannot carry; omit it for the default.
- `s.db.external.<engine>.direct_query({ sql, connectionString, responseType?, args?, parser?, as? })` — same shape against an EXTERNAL database; `<engine>` is `postgres`/`mysql`/`mssql`/`oracle`/`snowflake`. `connectionString` is a `Value` — reach for `env(...)`, not a literal — stored as `context.connection_string_flex`. A bare string stores the older `context.connection_string` instead (an env-var name unless it looks like a URL); each form round-trips as itself.
- `s.db.transaction({ body, as? })` — run a `Statement[]` atomically. `as` binds whatever the block returned.
- `s.db.bulk.add({ table, items, allowIdField?, as? })` / `s.db.bulk.update` / `s.db.bulk.patch` — `items` is an array `Value`.
  - ⚠ `bulk.add` **drops `id` on every row unless `allowIdField: true`** (silently, next sequence value instead) — the opposite of `seed`, where `id` pins. Rows referenced by a foreign key need `allowIdField: true`; literal `items` carrying `id` without it throw. `bulk.update`/`patch` keep `id` (their match key).
  - ⚠ **`bulk.update` is a whole-row REPLACE: every column an item OMITS is zeroed** (`""`/`0`/`null`), HTTP 200, no error — `{ id: 7, status: "done" }` blanks the rest of row 7. **Use `s.db.bulk.patch`** for the partial write "update these rows" means. `export()` warns on a STATIC `items` missing columns (`--strict` fails); a `ref`/`inp` `items` is uninspectable.
- `s.db.bulk.delete({ table, where?, allRows?, as? })` — deletes rows by a `context.search` filter. `where` is the same surface as `s.db.query` (`expr(...)`/`cmp(...)`, `and(...)`/`or(...)` groups, an array of those ANDed, or a raw `Value`) and encodes through the identical `{expression:[…]}` search shape. ⚠ A filter that constrains nothing deletes **every** row, so a missing or empty `where` **throws**: pass the filter, or `allRows: true` for a deliberate wipe (both together also throw). `allRows` emits the empty search the engine requires and returns the deleted count; reach for `s.db.truncate({ table, reset: true })` when the id sequence should restart too.