# 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` 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`, or `fake-indexeddb`.
- Open a database with `new MinnowDatabase(await IndexedDbBlockStore.open({ name: "shop" }))` —
  `MinnowDatabase` from `@minnowdb/core`, `IndexedDbBlockStore` from `@minnowdb/core/storage`.
  `OpfsBlockStore.open({ name })` is the same call for OPFS, inside a worker only; OPFS does not
  exist in Safari private browsing, so fall back to IndexedDB when
  `navigator.storage?.getDirectory` is missing or rejects.
- `@minnowdb/core`, `@minnowdb/client`, and `@minnowdb/devtools` share a major version and move
  independently inside it. Install them on the same major; npm refuses a mixed-major set.
- `db.query(sql, { params })` runs `SELECT` and returns `{ rows, columns }`. It throws on a
  statement that writes, rather than writing.
- `db.execute(sql, params)` runs any statement. Check `result.kind` to see what happened
  (`rows`, `insert`, `update`, `delete`, `merge`, `transaction`, `create-table`, `add-column`,
  `drop-column`, `drop-table`, `create-index`, `drop-index`, `create-view`, `drop-view`,
  `create-trigger`, or `drop-trigger`) before reading `rowCount`, `version`, or `returnedRows`.
- Bind parameters with `?` in order or `$1` by position. 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.
- `UPDATE` and `DELETE` require a table with a `PRIMARY KEY`. A table without one can only be
  appended to. Give a table a key if its rows will ever be edited.
- `INTEGER`, `BIGINT`, and `SMALLINT` accept only JavaScript safe integers. `NUMERIC` and
  `DECIMAL` are rejected because exact fixed-point storage is not implemented; use integer minor
  units or canonical decimal text, and use `DOUBLE PRECISION` only when approximation is intended.
- Load many rows with `db.insertBatch(table, rows)`, not one `INSERT` per row.
- 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.
- For anything interactive, run the engine in a worker:
  `new MinnowDatabaseClient(new Worker(new URL("@minnowdb/core/worker", import.meta.url), { type: "module" }), { store: { kind: "indexeddb", name: "app-db" } })`,
  or `{ kind: "opfs", name: "app-db" }` for the OPFS store,
  importing `MinnowDatabaseClient` from `@minnowdb/core/client`. Queries, writes, migrations,
  live queries, snapshots, and maintenance use the same calls as `MinnowDatabase`.
- 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.
- `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. `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.
- `db.explain(sql)` returns the optimized plan as text.
- Check https://minnowdb.com/sql-feature-matrix.json before using an unfamiliar SQL form. It
  lists every supported and rejected form with an example.
- Not supported, and what to write instead: `LATERAL` (rewrite as a join), `LISTAGG`
  (aggregate strings in JavaScript — no string aggregate exists), `JSON_TABLE`
  (`JSON_VALUE` and `JSON_QUERY`), `SIMILAR TO` (`LIKE`, or `MATCH` for text search), `COLLATE`,
  `ARRAY[...]`, `TIME` literals (use `TIMESTAMP`), `CREATE SEQUENCE`, `GRANT`,
  `SET TRANSACTION`, table declarations with multiple or composite unique keys, composite primary
  or foreign keys, savepoints, correlated `NOT IN` (use `NOT EXISTS`), non-equality
  correlation in scalar subqueries or `IN`, and correlated `EXISTS` nested below `OR`.