# 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 IndexedDB and `CompressionStream`, 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`.
- `@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 and returns a tagged result. Check `result.kind`
  (`rows`, `insert`, `update`, `delete`, `create-table`, `create-trigger`, `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.
- Load many rows with `db.insertBatch(table, rows)`, not one `INSERT` per row.
- 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" } })`,
  importing `MinnowDatabaseClient` from `@minnowdb/core/client`. Its API matches `MinnowDatabase`
  call for call.
- Full-text search is `MATCH(column) AGAINST 'terms'`, ranked with `BM25(column) AGAINST 'terms'`.
  There is no index DDL to write; the index builds itself.
- `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` and
  `JSON_ARRAYAGG` (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`, correlated `NOT IN` (use `NOT EXISTS`), and correlated subqueries whose
  condition is not an equality.