AI agents & LLMs

curl -o AGENTS-minnow.md https://minnowdb.com/agent-rules.md

# Minnow

Minnow (`@minnowdb/core`) is a columnar SQL database that runs in the browser. Documentation for
machines: https://minnowdb.com/llms.txt

- Browser only. It needs `CompressionStream` and `DecompressionStream` plus one durable store —
  IndexedDB, or OPFS in a worker — and there is no Node build. In Node tests, use `MemoryBlockStore` from
  `@minnowdb/core/storage/memory`, or `fake-indexeddb`.
- Open a database with `new MinnowDatabase(await IndexedDbBlockStore.open({ name: "shop" }))` —
  `MinnowDatabase` from `@minnowdb/core`, `IndexedDbBlockStore` from
  `@minnowdb/core/storage/indexeddb`.
  `OpfsBlockStore.open({ name })` is the same call for OPFS, inside a worker only; OPFS does not
  exist in Safari private browsing. Through the worker client, `{ kind: "auto", name }` picks
  OPFS where the worker can hold it and IndexedDB elsewhere, remembering the choice per name.
  A failed probe for an existing database rejects; it must never select an empty replacement store.
- Durable adapters default to `durability: "strict"`. Use `strict` whenever losing an acknowledged
  write is unacceptable. Use `"relaxed"` only when every acknowledged write can be reconstructed,
  replayed, or recovered from another durable source. For browser-local data that must be preserved,
  call `ensureOriginPersistence("required")` from `@minnowdb/core/storage/persistence` before
  opening the store; this prevents automatic quota eviction when granted, not deliberate site-data
  clearing or device loss, so those risks still need an independent synchronized or exported copy.
- Minnow is 0.x: breaking changes land in minor releases, so pin exact `@minnowdb/*` versions and
  upgrade them together. Engine-dependent adapters declare ranges that make npm reject an incompatible
  core; the structurally typed React hook has no core runtime dependency.
- `db.query(sql, { params })` runs `SELECT` and returns `{ rows, columns, columnDomains }`.
  `columnDomains` is positionally aligned with `columns`; use it to generically parse declared or
  aliased JSON/JSONB text. It throws on a statement that writes, rather than writing.
- `db.queryCursor(sql, { params, batchRows, signal })` pulls bounded result pages. Single-table,
  unordered scans stream directly; blocking sorts, groups, joins, and derived sources page the
  ordinary completed result. The worker transfers one columnar page per pull.
- `db.execute(sql, params, options?)` runs any statement. Check `result.kind` to see what happened
  (`rows`, `insert`, `update`, `delete`, `merge`, `transaction`, `set`, `create-table`, `add-column`,
  `create-type`, `create-sequence`, `drop-column`, `drop-table`, `create-index`, `drop-index`,
  `create-view`, `drop-view`, `create-trigger`, or `drop-trigger`) before reading `rowCount`,
  `version`, or `returnedRows`.
  The optional third argument takes `{ signal?, onStats?, memoize?, executionMemoryBudgetBytes? }`:
  a SELECT honors all of them; other statements check `signal` once before running.
- Write PostgreSQL-style SQL and bind parameters with `$1`, `$2` by position. `?` in order is
  also accepted as a Minnow extension. Never concatenate values into SQL: compiled plans are
  cached on the statement text, so a parameterized statement is planned once and interpolation
  throws that work away on every call.
- Treat Minnow's fixed SQL safety limits as input validation, not tuning: one statement is capped
  at 1,048,576 characters, 16,384 tokens, 4,096 parameter positions, and 128 syntax levels. Pattern,
  JSON/array, numeric, scalar-result, and full-text inputs have their own documented ceilings in
  https://minnowdb.com/docs/sql/. Keep the 64 MiB execution budget unless the application has a
  measured reason to change it; `null` explicitly opts into unbounded execution memory.
- `UPDATE` and `DELETE` require scalar or composite row identity: a `PRIMARY KEY`, or the
  single inline `UNIQUE` form. A table without one can only be appended to. Primary-key components
  cannot be updated.
- `INTEGER`, `BIGINT`, and `SMALLINT` accept only JavaScript safe integers, and `/` between two
  integers (columns, constants, `COUNT`, integer aggregates) truncates toward zero as in
  PostgreSQL: `7 / 2` is `3`. Divide by `2.0` or a `DOUBLE PRECISION`/`NUMERIC` operand for a
  fractional quotient. `NUMERIC` and
  `DECIMAL` are exact and return decimal strings, rendered at the column's declared scale like
  PostgreSQL (`'1.50'` from `NUMERIC(10, 2)`). Exact `+`, `-`, `*`, `%`, `ROUND`, and `TRUNC`
  carry PostgreSQL's display scale (`'8.00'` from `NUMERIC(10, 2) + 1`); a bare `NUMERIC` and a
  division result render canonically, without trailing fractional zeros. Use `DOUBLE PRECISION`
  only when approximation is intended. Numeric constants in SQL are exact, as in PostgreSQL: `0.1 + 0.2` is `0.3`, and a
  constant or constant-arithmetic result that would not read back identically from a JavaScript
  number (24-digit decimals, integers beyond 2^53) comes back as a decimal string rather than a
  rounded number or an error.
  JSON/JSONB, UUID, arrays, `DATE`, `TIME`, intervals, and enum values also return strings at the
  JavaScript boundary. `DATE` is canonical `YYYY-MM-DD` calendar text, not a JavaScript `Date`.
- Use a column builder's `.generatedSql("expression")` or SQL `GENERATED ALWAYS AS (...) STORED` for a
  value derived from sibling columns. Omit it from inserts and updates; Minnow recomputes it.
  Adding a new generated column to an existing table is refused, while adopting generation on an
  existing column scans and verifies its values.
- Load many rows with `db.insertBatch(table, rows)`, not one `INSERT` per row.
- Construct the engine or client with the declaration — `new MinnowDatabase(store, { schema })`,
  `new MinnowDatabaseClient(worker, { store, schema })` — then call `migrate()` with no
  argument. Every batch method is then typed by table name: rows, keys, update changes,
  `conflictWhere`, `readTable` results and `write()` scopes all follow the declared columns, and
  a misspelled table or column fails to compile. Without `schema` those methods take plain strings.
- Use `db.upsertBatch(table, rows, { conflictWhere: { column, operator, value } })` when an
  existing target row must satisfy a guard before replacement. Inserts still land; inspect
  `skippedRowCount` for rejected conflicts. The same `conflictWhere` option works on
  `tx.upsertBatch` inside a `db.write()` scope, so a guarded upsert can compose atomically with
  other staged mutations in one commit.
- A database has one writer at a time, across engines, workers, and tabs: every `write()`,
  batch write, SQL mutation, `BEGIN` transaction, migration, and DDL statement takes its turn
  before reading anything and holds it until its outcome is known. Issue concurrent writes
  freely from any number of tabs; do not add an application queue, a serializer, or a
  contention-retry loop, and do not pass `coordinateWrites` (removed in 0.12.0). Use the
  supplied `tx` for writes inside a callback; another write on the same database awaited from
  inside the callback waits on the callback itself and is reported through `onBackgroundError`
  after ten seconds. Keep network requests outside scopes. Callbacks are never replayed.
- On teardown, `await db.close()` before `store.close()` for a direct engine. For a worker,
  `await client.close({ terminateWorker: true })` when the worker is dedicated to the database.
  This rolls back open SQL transactions, stops live-query timers and maintenance scheduling,
  releases reader leases, and drops resident caches.
  Never chunk a write to suit the engine: hand `insertBatch`/`upsertBatch` the whole batch, and
  inside `write()` single-row inserts, updates, deletes, upserts, and keyed constant `UPDATE`/
  `DELETE` statements coalesce into a few segments per table. Chunking only multiplies commits. Worker calls have a 60-second default `requestTimeoutMs` that counts
  silence, not wall time; close has a five-second default silence `timeoutMs` and the same
  ten-timeout absolute cap. An unknown mutation outcome requires reconciliation by durable
  application ID, never blind replay. Deploy matching client/worker builds (wire protocol 7 in
  core 0.9.0).
- An idle SQL transaction rolls back and rejects subsequent statements with
  `TransactionExpiredError` until explicit `ROLLBACK` or `BEGIN`. The 30-second default counts from
  the connection's last statement, and no other connection's crash, reopen, or maintenance ends
  your transaction. `WriteConflictError` only comes from a writer that did not take a turn (an
  older build in another tab); a `COMMIT` that loses that way publishes nothing and leaves
  nothing to acknowledge: rerun the whole transaction. Keep transaction callbacks
  short; readers use renewable leases, and a scope suspended past an expired lease fails explicitly.
- For anything interactive, run the engine in a worker. Put
  `import "@minnowdb/core/worker"` in a local `db-worker.ts`, then pass
  `new Worker(new URL("./db-worker.ts", import.meta.url), { type: "module" })` to
  `MinnowDatabaseClient` with `{ store: { kind: "auto", name: "app-db" } }` — OPFS where
  available, else IndexedDB, chosen once per name — or name `indexeddb` or `opfs` outright.
  `client.storeKind()` reports what opened. Import the client from
  `@minnowdb/core/client`. Queries, writes, migrations, cursors, live queries, snapshots, and
  maintenance use the same calls as `MinnowDatabase`.
- When the worker always opens one kind of store, import `@minnowdb/core/worker/indexeddb`,
  `@minnowdb/core/worker/opfs`, or `@minnowdb/core/worker/memory` instead of
  `@minnowdb/core/worker`: it bundles that store alone, which matters under Vite's default
  `iife` worker format, where the generic entry carries all three. It refuses any other store
  kind in the client's options.
- Live queries stay fast only when the engine can maintain them incrementally: one base
  table with a unique key, at most one inner or left join whose `ON` is a single equality on
  the joined table's unique key, no subquery, `DISTINCT`, or window function. Anything else re-executes on every relevant
  commit. Add `CREATE INDEX` for every non-key column a live query filters on. Aggregate in
  SQL (`COUNT`, `SUM`, `GROUP BY`) rather than reducing a live result in application code. A
  live list with `ORDER BY` and no `LIMIT` should be `live.window()`. Do not set
  `pollIntervalMs` with a built-in store; commit hints already cross tabs. Never create
  per-run tables inside a sync; DDL bumps the schema epoch and conflicts every open scope.
  `explain(sql)` ends with a `-- live:` line saying whether the statement is maintained and
  why not; `liveSet.stats().groups` reports the same per statement with rerun counts.
- For typed Kysely live queries, create one `createKyselyLiveQueries({ driver, db })` manager
  and wrap a selectable query with `query.$call(live)` or `live(query)`. Prefer `live.window()`
  with a unique result key, `ORDER BY`, and a limit for UI lists: single-table statements with a
  unique key are maintained incrementally from each commit's rows rather than re-executed. Close
  queries and then the manager. One subscription per component is fine: unchanged rows keep
  their object identity across snapshots, so memoize list items on the row and use
  `useLiveSelector` for a derived value.
- A Kysely DB derived from the Minnow schema infers `count()` / `countAll()` as `number`, numeric
  `sum()` / `avg()` from the column domain, and fixed-return Minnow functions through `fn` /
  `fn.agg`; supported `cast()` targets are inferred too. Do not add redundant output generics.
  Preserve `InferKyselyDatabase` when constructing Kysely manually. Only arbitrary function names
  and raw SQL strings need an explicit result type. All aggregates except `count` include `null`
  for empty input; use inferred `fn.coalesce()` when the query supplies a fallback.
- Set Kysely's `resultDecoding: { numeric: "number", json: "parse" }` when native Float64 and
  parsed JSON results are preferred to core's lossless strings. The option covers buffered,
  streamed, and `RETURNING` rows; omit numeric decoding when decimal precision must remain exact.
- Kysely builder forms outside Minnow's profile — MySQL/SQLite/T-SQL spellings such as
  `replaceInto()`, `orIgnore()`, `.top()`, `identity()`, and update/delete `LIMIT`, plus SQL
  `EXPLAIN`, data-modifying CTEs, the JSON path operators `->$`/`->>$`, and `ALTER TABLE` beyond
  add/drop column — are refused when the query compiles, with the alternative named in the
  error. Follow that alternative instead of retrying the same form. `distinctOn()`,
  `updateTable().from()`, `deleteFrom().using()`, the row-locking modifiers (`forUpdate()`,
  `forShare()`, `skipLocked()`; accepted and ignored), and `createTable().temporary()` pass
  through to the engine; `onCommit()` and `dropTable().temporary()` are refused.
- Full-text search is `MATCH(column) AGAINST $1`, ranked with `BM25(column) AGAINST $1`. Pass the
  search text as a parameter. There is no full-text index DDL to write; that index builds itself.
  With Kysely, use `search.match(eb, columns, query)` and `search.rank(eb, columns, query)` so the
  non-empty column list is checked against the query's visible tables and aliases.
- With Kysely, import `jsonBuildObject`, `jsonArrayFrom`, and `jsonObjectFrom` from
  `@minnowdb/kysely/helpers`; the `kysely/helpers/postgres` versions also run, but Minnow's are
  the typed default. Enable
  `resultDecoding: { json: "parse" }` and use explicit named selections in row-to-object
  subqueries.
- Declare a JSON column's document shape in the schema — `column.jsonb<Shape>()` — so the derived
  Kysely DB types `eb.ref(column, "->>")` traversal with `.key()` / `.at()`. The shape is a
  compile-time promise only: reads and writes stay JSON text unless `json: "parse"` decoding is
  set, and `->>` leaves are always text at runtime.
- `CREATE INDEX name ON table(a, b DESC)` creates a durable scalar or composite accelerator.
  Leftmost equality/`IN` prefixes and the next range prune candidates. A nullable trailing column
  still prunes: its NULL rows are indexed under the non-null prefix, and no comparison matches
  them. `CREATE UNIQUE INDEX`
  enforces additional candidate keys atomically; any NULL component does not conflict.
  `DROP INDEX name` removes the catalog, postings, and unique membership. A matching non-null index
  can satisfy `ORDER BY` (and cover the query) on a keyless append-only table; other shapes sort.
  Logical domains such as exact NUMERIC and enum use indexes for equality, but scan/sort for ranges.
- SQL transactions support `SAVEPOINT name`, `ROLLBACK TO SAVEPOINT name`, and
  `RELEASE SAVEPOINT name`. A second `BEGIN` is rejected; use a savepoint for nested work.
- `db.explain(sql)` returns the optimized plan as text.
- Check https://minnowdb.com/sql-feature-matrix.json before using an unfamiliar SQL form, and
  https://minnowdb.com/postgres-feature-profile.json before assuming PostgreSQL behavior. They
  list the tracked supported and rejected forms plus deliberate differences and extensions.
- Supported PostgreSQL forms include exact NUMERIC/DECIMAL, JSON/JSONB, UUID, arrays, DATE, TIME,
  intervals, enums, sequences, multiple/composite keys, savepoints, filtered upserts, correlated
  IN/NOT IN/ANY/ALL and EXISTS/NOT EXISTS at nested expression depth, correlated scalar JSON
  projections with multiple qualified outer-column references and per-probe ordering/limits,
  subquery predicates in UPDATE/DELETE,
  range-correlated LATERAL, ordered STRING_AGG/JSON_ARRAYAGG, SIMILAR TO, COLLATE, constant
  JSON_TABLE, the `->`/`->>` JSON operators (which also accept plain JSON text, and error on
  a non-JSON document), `DISTINCT ON`, `UPDATE … FROM` and `DELETE … USING`, `TRUNCATE`,
  PostgreSQL's `json_agg`/`json_build_object`/`to_json`/`row_to_json` spellings with a table
  alias as a row value (Kysely's `jsonArrayFrom`/`jsonObjectFrom`), integer division that
  truncates, `GROUP BY` a primary key with the row's other columns ungrouped, case-insensitive
  unquoted identifiers, `SET`/`RESET`/`SHOW` session settings (accepted and ignored, except a
  non-UTC time zone), `FOR UPDATE` (ignored), and `E'…'`/`$$…$$` strings.
- Array values use canonical JSON text. Scalar subscripts, ARRAY_AGG, and typed UNNEST over
  ARRAY constructors are supported. Numeric RANGE offsets need one numeric ordering expression.
  FULL JOIN supports grouping, DISTINCT, wildcards, and compound ON in its sole-join form.
- Unsupported: keyless `UPDATE`/`DELETE`, `GRANT`, `SERIALIZABLE` isolation, `LOCK TABLE`, DDL
  inside `BEGIN … COMMIT`, several statements in one call, range-correlated LATERAL
  grouping/order/limit, correlated JSON_TABLE documents, array slices and multidimensional access,
  `ANY`/`ALL` over array values, `ARRAY(SELECT …)`, UNNEST outside ARRAY constructor sources,
  `generate_series`, `||` on array or JSONB operands and the `#>`/`@>`/`?` operators,
  `jsonb_set`/`jsonb_typeof`/`json_object_agg`, `regexp_match` and the other array-returning
  regex functions, `ROW(…)` as a value, bit-string literals, interval-valued arithmetic such as
  interval-plus-interval or timestamp-minus-timestamp, `AT TIME ZONE`, `version()` and other
  server introspection, `TABLESAMPLE`, `SET column = DEFAULT` and `SET (a, b) = (…)` in
  `UPDATE`, data-modifying CTEs, `ALTER TABLE` beyond ADD/DROP COLUMN, `CREATE TABLE … (LIKE …)`,
  `SERIAL` on a non-key column, `ON UPDATE` and `DEFERRABLE` foreign-key options, partial and
  expression indexes, `ON CONFLICT` on a non-key UNIQUE column, `DROP TABLE a, b`, view column
  lists and materialized views, `setval`, domains, functions, `EXCLUDE` constraints, schemas,
  schema-qualified names, and `information_schema`, `EXPLAIN`/`VACUUM`/`ANALYZE`, ordered-set
  aggregates, bitwise operators, `bytea`, enum evolution, sequence options, `RIGHT JOIN` or
  `FULL JOIN` beside another join, `RIGHT JOIN` combined with `SELECT *`, temporal interval
  `RANGE` frame offsets, `DISTINCT` window aggregates, windows in WHERE/GROUP BY/HAVING, and
  `ON DELETE SET DEFAULT`. Every rejected form is an `unsupported` row of the feature matrix
  with its exact error; check each supported entry's note before assuming every PostgreSQL
  variant.
- Supported stored-format upgrades run automatically through ordinary open/load, including
  IndexedDB schemas 1–3 to 4 and OPFS layouts 6–8 to 9. Do not add a required export/import
  step. Every format change needs frozen
  older-writer fixtures and data-preservation, continued-write/reopen, interruption/power-loss,
  corruption-refusal, concurrent-open and older-writer-barrier tests. Close older connections
  if they block an upgrade; quota/I/O failures must report an error and remain retryable.
- OPFS coordination exhaustion and queue overload throw `OpfsCoordinationError` from
  `@minnowdb/core`; `instanceof` survives worker RPC. Retry boot/migrate with bounded backoff;
  never delete a database merely because migration rejected. Established live queries retain
  their last good result and retry coordination errors automatically. An
  `OpfsUncertainOutcomeError` needs reconciliation because its mutation may have committed; it
  is raised only when a leader vanished mid-flight and the recovered log can neither answer the
  re-sent request nor prove it never ran — never during a handover, a slow write, or an ordinary
  failover, which the log resolves.
- Pass `onWorkerError` to `MinnowDatabaseClient` to hear worker failures that belong to no call
  (uncaught errors, unhandled rejections, failed maintenance, coordination errors); without it
  they go to `console.error`. They are not fatal. `DatabaseWorkerFailedError` is: it means the
  Worker itself raised an unhandled error or an unreadable frame, and the client must be
  replaced.
- Never call `worker.terminate()` yourself; `client.close({ terminateWorker: true })` disposes the
  store first on a healthy connection. After initialization or the transport already failed, the
  original `ready()` or operation reports that error; close removes local listeners and requests
  termination because the unusable channel cannot confirm store disposal. Without a terminable
  transport, server-side release cannot be confirmed. A worker killed with a write in flight leaves
  WebKit holding its IndexedDB connection until that document is gone, and every connection to the
  database — new ones in other tabs included — blocks meanwhile. The engine bounds that wait and
  throws `StorageUnresponsiveError` after thirty seconds of total storage silence; the remedy is to
  reload the page, not `reopen()`, which opens the same wedged database.
- OPFS deletion requires Web Locks and all connections closed. `OpfsDatabaseInUseError` refuses
  deletion while a current client is open; a client just closed is waited for, not refused. Close
  pre-0.9.0 clients too: they do not participate in the deletion lock protocol.