# Minnow > Minnow is a columnar SQL engine that runs entirely in the browser. It parses, plans, and executes SQL itself over immutable columnar blocks stored in IndexedDB, with no server and no WebAssembly to download. Complete documentation for Minnow 0.1.1, one page after another, in the order the site presents them. --- # Overview > A columnar SQL engine that runs entirely in the browser. Minnow is a SQL database that runs in the browser. You give it SQL; it parses, plans, optimizes, and executes that SQL against columnar data stored in IndexedDB. There is no server, no WebAssembly module to download and compile, and no build step. ```ts import { MinnowDatabase } from "@minnowdb/core"; import { IndexedDbBlockStore } from "@minnowdb/core/storage"; const db = new MinnowDatabase(await IndexedDbBlockStore.open({ name: "shop" })); await db.execute(`CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total DOUBLE PRECISION NOT NULL, placed_at TIMESTAMP NOT NULL )`); const { rows } = await db.query(` SELECT DATE_TRUNC('month', placed_at) AS month, ROUND(SUM(total), 2) AS revenue FROM orders GROUP BY DATE_TRUNC('month', placed_at) ORDER BY month DESC `); ``` > **Experimental** > > The version-zero block format carries no compatibility promise, and the SQL surface is a > correctness-first subset of SQL:2023 rather than the whole standard. Everything the engine does > and deliberately refuses to do is listed, keyed to the standard's own feature identifiers, in the > [feature matrix](/docs/sql/feature-matrix.md). ## What is here The engine is the product. Parser, planner, optimizer, and a vectorized executor are implemented in this repository — there is no SQLite or DuckDB underneath, and no WebAssembly anywhere. - **[SQL](/docs/sql.md)** — how to run statements, and what the language surface is: joins, CTEs, window functions, set operations, grouping sets, upserts with `RETURNING`, full-text search. - **[Schema](/docs/schema.md)** — declaring tables, constraints, and views in TypeScript, and what a migration will and will not do to a database that already holds rows. - **[Typed client](/docs/client.md)** — the optional query builder: inferred row types, typed writes, and live queries. - **[Engine](/docs/engine.md)** — the `MinnowDatabase` API, transactions and snapshots, running the engine in a worker, and the memory budget. - **[Storage](/docs/storage.md)** — the block store contract and the two adapters that implement it, plus snapshots and background maintenance. ## What it is for Minnow suits an application that already has data on the device and wants to ask real questions of it: an offline-first tool, a local analytics view, a large table a user needs to slice without a round trip. It is a database, not a cache — writes are durable, commits are atomic across tabs, and a reader never sees half of a write. It is not a replacement for a server database. Everything runs on one machine, against one browser's storage quota, with one writer's worth of throughput. ## Design commitments Four rules are fixed. The reasoning is in [Architecture](/docs/reference/architecture.md). - **IndexedDB is the source of truth.** Correctness never depends on `BroadcastChannel`, Web Locks, or page-close handlers. Durability ends at a committed IndexedDB transaction. - **Published data is immutable.** Writes append and publish atomically; another tab sees the old version or the new one, never a partial write. - **Reads are snapshot reads.** A query executes against one version. Reads never block writes and writes never block reads. - **The engine is our own.** No embedded database underneath, and no Wasm to fetch before the first query answers. --- Minnow 0.1.1 · this page on the site: /docs/ --- # Installation > Install the package and open a database. ```bash npm install @minnowdb/core ``` Plain JavaScript, no post-install step and no binary to fetch. It is around 159 KB gzipped — roughly a third of SQLite's WebAssembly build, which also has to download and compile its module before it can answer anything. The engine speaks SQL and needs nothing else. If you also want a typed query builder, add the optional client: ```bash npm install @minnowdb/client ``` It ships as its own package, built only from the primitives at `@minnowdb/core/plan` — the same ones any other builder would use. Every Minnow package shares a major version and moves independently inside it, so any `0.x` client works with any `0.x` engine and npm refuses a mixed-major pair on its own. See [Versioning](/docs/reference/versioning.md). ## Entry points | Import | What it is | | ----------------------------- | --------------------------------------------------------------------------------------- | | `@minnowdb/core` | The engine: `MinnowDatabase` and everything for running SQL in the current thread. | | `@minnowdb/core/storage` | Block stores: `IndexedDbBlockStore`, `MemoryBlockStore`, and the `BlockStore` contract. | | `@minnowdb/core/worker` | A ready-made worker entry. Point a module worker at it. | | `@minnowdb/core/client` | `MinnowDatabaseClient`, the main-thread half of the worker pair. | | `@minnowdb/core/plan` | Plan-construction primitives, for building a typed layer over the engine. | | `@minnowdb/core/testing` | `FaultInjectingBlockStore`, for testing behaviour under storage failure. | | `@minnowdb/core/block-format` | The on-disk block encoding, for tools that read blocks directly. | | `@minnowdb/client` | Optional typed query builder: `createMinnow`, `InferDatabase`. | ## Opening a database A database is an engine plus a block store. The store decides where blocks live; everything else is identical whichever one you choose. ```ts import { MinnowDatabase } from "@minnowdb/core"; import { IndexedDbBlockStore } from "@minnowdb/core/storage"; const store = await IndexedDbBlockStore.open({ name: "shop" }); const db = new MinnowDatabase(store); ``` For tests and for data that should not outlive the page, swap in the in-memory store — it implements the same contract, so nothing else changes: ```ts import { MemoryBlockStore } from "@minnowdb/core/storage"; const db = new MinnowDatabase(new MemoryBlockStore()); ``` See [Storage](/docs/storage.md) for what each adapter costs and guarantees. ## Where it runs Anything with IndexedDB and `CompressionStream`: current Chrome, Firefox, Safari, and Edge, in a window or a worker. There is no Node build — the engine targets browsers, and the tests run it in real ones. Most applications should [run the engine in a worker](/docs/engine/workers.md). The API is identical on either side of the boundary, and query execution then never competes with rendering. --- Minnow 0.1.1 · this page on the site: /docs/installation/ --- # Your first query > Create a table, write rows, and read them back — all in SQL. Everything below runs in a browser tab. Nothing is sent anywhere. ### Open a database ```ts import { MinnowDatabase } from "@minnowdb/core"; import { IndexedDbBlockStore } from "@minnowdb/core/storage"; const db = new MinnowDatabase(await IndexedDbBlockStore.open({ name: "shop" })); ``` ### Create tables DDL is SQL like everything else. `PRIMARY KEY` declares the table's unique key — the column that `UPDATE`, `DELETE`, and upserts address rows through. ```ts await db.execute(`CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name VARCHAR(80) NOT NULL, city VARCHAR(80), signed_up_on TIMESTAMP NOT NULL )`); await db.execute(`CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status VARCHAR(20) NOT NULL, total DOUBLE PRECISION NOT NULL, placed_at TIMESTAMP NOT NULL )`); ``` ### Write rows `execute` runs any statement. Bind values with `?` rather than building SQL strings — the compiled plan is cached on the statement text and re-bound per execution, so parameters are faster as well as safer. ```ts await db.execute( `INSERT INTO customers (customer_id, name, city, signed_up_on) VALUES (?, ?, ?, ?), (?, ?, ?, ?)`, [1, "Ada Lovelace", "London", new Date("2024-03-02")], ); const inserted = await db.execute( `INSERT INTO orders (order_id, customer_id, status, total, placed_at) VALUES (?, ?, 'completed', ?, ?) RETURNING order_id, total`, [1001, 1, 24.5, new Date("2025-11-14")], ); // inserted.kind === "insert", inserted.returnedRows === [{ order_id: 1001, total: 24.5 }] ``` For loading a lot of rows at once, [`insertBatch`](/docs/engine.md#bulk-writes) takes them columnar and skips the parser entirely. ### Query ```ts const { rows, columns } = await db.query( `SELECT c.name, COUNT(*) AS orders, ROUND(SUM(o.total), 2) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = ? GROUP BY c.name ORDER BY revenue DESC LIMIT 10`, { params: ["completed"] }, ); ``` `rows` are plain objects — numbers are numbers, `TIMESTAMP` columns come back as `Date`. `columns` carries the result's names and types, which is what a grid needs to render without inspecting values. ### See what it decided to do ```ts console.log(await db.explain("SELECT * FROM orders WHERE order_id = 1001")); ``` The plan shows which access path the optimizer chose, where predicates were pushed, and which joins were reordered. [Query plans](/docs/sql/plans.md) reads one line by line. ## Try it without installing anything The [playground](/playground) is this, already set up: a generated retailer's database of around 590,000 rows, built in your browser and kept in IndexedDB. Every query on this site runs against it for real. ## Where to go next - [Running SQL](/docs/sql.md) — the full statement API, parameters, and result shapes. - [Writing data](/docs/sql/dml.md) — inserts, updates, deletes, upserts, `RETURNING`. - [Schema & migrations](/docs/schema.md) — declare those tables in TypeScript instead, and evolve them safely as the application changes. - [The typed client](/docs/client.md) — the optional builder, if you would rather write queries that infer their own row types than write SQL strings. - [Transactions](/docs/engine/transactions.md) — atomic multi-statement writes and stable reads. - [Workers](/docs/engine/workers.md) — moving the engine off the main thread. --- Minnow 0.1.1 · this page on the site: /docs/first-query/ --- # Devtools > An embeddable SQL console for your database — a floating panel in dev, an inline playground in docs. A panel you drop into any app to browse, edit, and query the database it is already using. It floats over your page without blocking it, and every change asks first. ## Try it This is the panel an application mounts: a launcher in the corner, and a window over the page that leaves the page underneath it usable. It builds a small retail database in memory here, so nothing is written to your machine and closing it throws the data away. _A button here on the site mounts the floating panel over the page: /docs/devtools/_ The [playground](/playground) is the same panel embedded inline, in its production shape: a database running in a web worker (the same [worker client](/docs/engine/workers.md) an app ships) over a seven-table retail dataset generated in your browser and kept in IndexedDB. The title-bar badge reads **worker** there because the queries genuinely leave the page. ## Install ```bash npm install @minnowdb/devtools ``` ## Mount it The panel attaches to a `MinnowDatabase`, a `MinnowDatabaseClient`, or the `Minnow` facade over either — it reaches the database behind a facade through [`db.driver`](/docs/reference/api.md). ```ts import { mountMinnowDevtools } from "@minnowdb/devtools"; if (import.meta.env.DEV) { mountMinnowDevtools(db, { corner: "bottom-right" }); } ``` That adds a launcher button in the corner. Click it, or press `Cmd/Ctrl + Shift + D`. Keep the mount behind a development check. The devtools are a separate package precisely so they can be left out of a production bundle. ## Or use the element `` is a custom element, so it works unchanged in React, Vue, Svelte, Solid, Astro, and plain HTML. Its shadow root keeps your styles out and its own styles in. ```ts import { defineMinnowDevtools } from "@minnowdb/devtools"; defineMinnowDevtools(); document.querySelector("minnow-devtools").target = db; ``` ```html ``` The database is a property rather than an attribute, because it is an object. ## Options | Option | Attribute | Default | What it does | | -------------- | --------------- | ------------------------ | ----------------------------------------------------------------------------------------------------------------------------- | | `mode` | `mode` | `"launcher"` | `"launcher"` floats over the page; `"inline"` renders in flow. | | `corner` | `corner` | `"bottom-right"` | Which corner the launcher and the opening panel use. | | `hotkey` | `hotkey` | `"mod+shift+d"` | Toggle shortcut. `mod` is Cmd or Ctrl. Empty turns it off. | | `defaultOpen` | `open` | `false` | Open on mount. Inline panels are always open. | | `zIndex` | `z-index` | `2147483000`, `0` inline | Stacking against your own overlays. A floating panel clears the page; an inline one stays in its flow, under a sticky header. | | `permissions` | `write` | `{ write: true }` | `write: false` refuses every statement that changes data. | | `initialQuery` | `initial-query` | `""` | SQL the console starts with. | | `storageKey` | `storage-key` | `"minnow-devtools"` | Namespace for the remembered panel geometry. | | `theme` | `theme` | `"system"` | `"light"` or `"dark"` pins the palette; `"system"` follows the OS. | | `height` | `height` | container | Height of an inline panel. A number is pixels; a string is any CSS length. | ## The schema rail Your tables sit down the left of both tabs, each expanding to its columns with their types, nullability, and which one is the unique key. Knowing what a column is called is as useful for writing a query as it is for browsing a table, so the rail never goes away. What clicking does follows the tab you are on: - On **Query**, a table or column name is inserted at the caret — `orders`, or `orders.total` — spaced from whatever precedes it. - On **Data**, a table opens in the grid. The chevron expands a table either way, so you can read its columns without loading it. ## Browsing data The **Data** tab browses one table at a time. Pick it from the rail, or from the picker in the toolbar. **Sorting.** Click a column header to sort ascending, again for descending, again to return to the table's own order. The unique key is appended to every sort, so rows with equal values keep a stable order instead of shuffling between pages. **Filtering.** `+ filter` builds a typed comparison: `=`, `≠`, `contains`, `starts with`, `<`, `≤`, `>`, `≥`, `like`, `in`, `between`, `is null`, `is not null`, offered per column type. Values are converted to the column's type before they reach SQL, so `score > 10` compares numbers. Filters combine with AND. For text, **`contains` is the one you usually want** — it adds the wildcards, so `crea` finds `created`. `like` takes a pattern exactly as written, which is standard SQL: `like crea` matches only the string `crea`, and you need `%crea%` to search inside a value. The value box hints which one you are in. Both are case-sensitive. A `contains` or `starts with` value is treated as literal text — `_` and `%` typed into it are escaped, so searching for `100%` finds `100%` and nothing else. In `like` they stay live as wildcards, because there you are writing the pattern yourself (with `ESCAPE '\'` available when you need a literal one). **Paging.** Rows load as you scroll. Where it can, the explorer asks for "the rows after the last one I saw" rather than "skip the first N", so reading deep into a table costs the same as reading the start of it. The status bar always says which it is using. A cursor needs a total order it can address exactly, so the explorer counts from the start instead when the table has no unique key, when the sort column is nullable (no comparison matches NULL), or when it is a datetime (the engine's date literals carry no time of day). Both give the same rows; one just gets slower the further in you go. **Counting.** `COUNT(*)` scans the whole table, so it runs alongside the first page rather than delaying it. Until it lands, the status bar says how many rows are loaded. ## Editing rows Double-click a cell to edit it, then save with the check beside the input or with `Enter`; the × or `Escape` discards it. Clicking elsewhere leaves the editor open rather than throwing the edit away. Click a row to select it, then **Delete row**. **Add row** opens a form with one input per column. Every one of them describes what it is about to do and waits for you to agree — the confirmation names the table, the key, and the before and after values. Values are typed as the column is typed, and checked in the editor rather than after you confirm: `twelve` in a number column is refused on the spot. A blank input means NULL where the column allows it, and `NULL` typed into a text column means the same thing (a literal `'NULL'` string needs the quotes). Writes go through the keyed batch API rather than generated SQL, so a datetime is written to the millisecond — the day-granular limit applies only to filters, which have to compile to SQL. After a write the row is re-read rather than patched in place, so the grid shows what actually landed. **When editing is unavailable**, the panel says so in a banner instead of leaving a dead button: | Situation | What you can still do | | --------------------------- | ------------------------------------------------------------------------------------------------------- | | `permissions.write: false` | Browse only. | | The target has no write API | Browse only. | | The table has no unique key | Browse and insert — the engine keys updates and deletes by the unique key and refuses them without one. | ## The window It is a window, not a modal. There is no backdrop over your page and no focus trap — the app underneath stays fully clickable while the panel is open. Drag it by its title bar and resize it from any edge or corner; dragging a left or top edge holds the opposite one still, the way a window manager does. **Maximize** next to the close button fills the screen, and restores to the size the window was actually left at — double-clicking the title bar does the same. In the console, the divider between the editor and the results is draggable, and the height you give the editor is remembered. Both sidebars collapse to a narrow strip with the chevron in their header, which is how you give the editor or the grid the full width. The panel reopens where you left it, with the same sidebars collapsed. They also step aside on their own as the panel narrows — history first, then the tables — so a small panel spends its width on the thing you are looking at. The Data toolbar carries its own table picker, so choosing a table never depends on the rail being there. Two badges in the title bar say what you are working with: - **worker** or **main thread** — a database built in the page runs queries on the main thread, so a slow one will freeze it. The [worker client](/docs/engine/workers.md) does not. - **write on** or **read-only** — whether `permissions.write` allows changes. ## Downloading the database The ⭳ button beside the badges saves the whole database as a [snapshot](/docs/storage/snapshots.md) file — one committed version, blocks and catalog and counters, in a single `minnow-v42-2026-08-17.minnow`. It is how you keep a copy of what you are looking at, send it to someone, or carry it to another machine. The ⭱ button beside it loads one back. Pick a file and the panel reads its header — which costs nothing, whatever the file's size — and tells you the version, the table count, the date it was taken, and how big it is before anything is loaded. The tables reappear in the rail as soon as the load finishes. **The database has to be empty to restore into.** One that already holds data refuses the load rather than merging two histories, so the usual shape is a fresh page against a fresh store. A progress chip beside the badges reports what is happening throughout — reading, copying, writing — because a real database is not a quick file. The bytes come out of a worker in slices, so the page keeps painting while a large one is copied. Restoring is a write, so the button is absent with `permissions.write: false`. Both buttons are absent when the target cannot do snapshots at all. ## Running statements The **Query** tab is a SQL console over the same database, with syntax highlighting and completion drawn from your own catalog: type a table name and its columns are offered, `events.` narrows to that table's columns. `Cmd/Ctrl + Enter` runs. The editor loads the first time you open the tab, not when the panel mounts — the launcher and the data explorer never pay for it. Until it arrives (and if it fails to arrive at all) the console is a plain text box that runs queries exactly the same way. Queries return rows; anything that changes data is described and confirmed first: - The prompt names the table, the operation, and the statement itself before it runs. - An `UPDATE` or `DELETE` with no `WHERE` clause is called out as hitting every row. - With `permissions.write: false`, the statement is refused outright and never reaches the database. What a statement does is read off the compiled plan, not its text, so a `SELECT` that merely mentions `DELETE` in a string literal is still a query. SQL that fails to compile is reported with the offending token selected in the editor — the position comes from [`SqlCompileError`](/docs/engine/index.md#errors). ## Diagnostics The editor compiles as you type and underlines what it cannot parse, on the token rather than the line. This costs nothing per keystroke: `compileStatement` is part of the library and runs in the page, so nothing is sent to the worker and no query is executed to find out that the SQL is wrong. Where the failure names a capability the engine records as unsupported, the message says which one and what stands in for it — "Expected SELECT, found BEGIN" also explains that transactions are scoped through the API rather than opened in SQL. That comes from the shipped [feature matrix](/docs/sql/feature-matrix.md), so it stays true as the engine changes. A message that several features share explains nothing rather than guessing between them. ## Plan The **Plan** tab beside the results shows what the optimizer made of the statement — the join order, which predicates were pushed down, and whether the scan can stream. It is asked for only when you look at it, and running a query returns you to the rows. ## History The last 50 runs are kept beside the console, newest first, each with its row count, timing, and age — or its error message, in red, if it failed. Click one to put it back in the editor. The query text and those timings persist, so history survives a reload. Result sets do not: fifty of them would exhaust the storage quota on the first wide query, so recent rows are cached in memory only and a recalled entry offers to run again once its rows have aged out. **Clear** forgets everything. History is namespaced by `storageKey`, so two panels on a page keep their own. ## Embedding a playground `mode: "inline"` renders the panel in the document instead of over it, with no launcher — the same panel, in flow. The [playground](/playground) is exactly this, over a worker client with its blocks in IndexedDB: ```ts mountMinnowDevtools(db, { container: document.querySelector("#playground"), mode: "inline", initialQuery: "SELECT * FROM people", }); ``` ### Sizing it An inline panel **fills its container**. Give the container a height and the panel takes all of it; give it none and the panel falls back to 520px, so dropping it into a page needs no CSS at all. ```html
``` Pass `height` instead when the container is not yours to style. It takes a number of pixels or any CSS length: ```ts mountMinnowDevtools(db, { container, mode: "inline", height: "70vh" }); ``` ```html ``` Two custom properties do the same from a stylesheet, which is what a responsive embed wants — they are read off the container, so a media query can change them without JavaScript: ```css #playground { --mdt-height: 60vh; --mdt-min-height: 400px; } ``` ### Matching your page The panel lives in a shadow root, so it follows the reader's OS colour scheme rather than your page's. A page with its own light/dark switch tells the panel which way it went: ```ts const devtools = mountMinnowDevtools(db, { container, mode: "inline", theme: "dark" }); devtools.setTheme("light"); // when your switch is flipped — no remount, the query survives ``` Its colours are custom properties, and properties set on the container reach inside the shadow root, so the whole palette is yours to override: ```css #playground { --mdt-accent: #7c3aed; --mdt-bg: #ffffff; --mdt-bg-secondary: #f7f7f5; --mdt-text: #37352f; --mdt-border: rgba(55, 53, 47, 0.12); --mdt-sans: "Inter", sans-serif; --mdt-mono: "JetBrains Mono", monospace; } ``` The full set is `--mdt-bg`, `--mdt-bg-secondary`, `--mdt-bg-hover`, `--mdt-bg-active`, `--mdt-bg-code`, `--mdt-text`, `--mdt-text-secondary`, `--mdt-text-faint`, `--mdt-border`, `--mdt-border-strong`, `--mdt-accent`, `--mdt-accent-bg`, `--mdt-selection`, `--mdt-danger`, `--mdt-danger-bg`, `--mdt-warn`, `--mdt-warn-bg`, `--mdt-ok`, `--mdt-ok-bg`, `--mdt-shadow`, `--mdt-sans`, and `--mdt-mono`. Set them in a dark-mode block too, or the ones you override will be the only colours that do not turn. Nothing else crosses the boundary in either direction: your stylesheet cannot restyle the panel's internals, and the panel cannot leak into your page. ## Cleaning up `mountMinnowDevtools` returns a handle: ```ts const devtools = mountMinnowDevtools(db); devtools.open(); devtools.close(); devtools.destroy(); // removes every listener and the element it created ``` --- Minnow 0.1.1 · this page on the site: /docs/devtools/ --- # Running SQL > The four calls that execute statements, how parameters bind, and what comes back. Every statement goes through one of four methods. They differ in what they return, not in what they can run. | Call | Use it for | Returns | | ---------------------------------- | --------------------------------------------- | ---------------------------------- | | `query(sql, options?)` | `SELECT` | `{ rows, columns }` | | `execute(sql, params?)` | Any statement, including DDL and writes | A tagged result per statement kind | | `explain(sql)` | Understanding a plan | The optimized plan as text | | `runStatement(compiled, options?)` | Re-running a statement compiled ahead of time | Same as `execute` | ## Reading ```ts const { rows, columns } = await db.query(` SELECT category, ROUND(SUM(line_total), 2) AS revenue FROM order_items i JOIN products p ON p.product_id = i.product_id GROUP BY category ORDER BY revenue DESC `); ``` `rows` is an array of plain objects in result order. Values arrive as their JavaScript equivalents: numbers as `number`, `TIMESTAMP` as `Date`, `BOOLEAN` as `boolean`, and SQL `NULL` as `null`. `columns` describes the shape — name and type per column — so a table component can render headers before it has looked at a single value. `query` accepts only statements that produce rows. Hand it an `INSERT` and it throws rather than silently writing. ## Parameters Placeholders are `?` in order, or `$1`, `$2` by position. Bind through `params`; never concatenate values into the text. ```ts await db.query("SELECT * FROM orders WHERE status = ? AND total >= ?", { params: ["completed", 50], }); await db.execute("UPDATE orders SET status = $2 WHERE order_id = $1", [1001, "refunded"]); ``` This matters for speed as well as safety. Compiled plans are cached on the statement text, so a parameterized statement is parsed, planned, and optimized once and then re-bound per execution. Interpolating values produces a new statement string every time and throws that work away. A statement's placeholders must all be bound: passing too few or too many is an error, not a silently-null column. ## Writing `execute` returns a tagged union, so the result tells you what the statement did: ```ts const result = await db.execute( "UPDATE orders SET status = 'refunded' WHERE order_id = ? RETURNING order_id, total", [1001], ); if (result.kind === "update") { result.rowCount; // rows changed result.version; // the version this commit published result.returnedRows; // present because of RETURNING } ``` The kinds are `rows`, `create-table`, `create-trigger`, `drop-trigger`, `insert`, `update`, and `delete`. Write kinds carry `rowCount` and the published `version`; `returnedRows` appears only when the statement had a `RETURNING` clause. ## Compiling once, running many times When the same statement runs in a loop, compile it once and skip even the plan-cache lookup: ```ts import { compileStatement } from "@minnowdb/core"; const statement = compileStatement("INSERT INTO events (id, kind) VALUES (?, ?)"); for (const event of batch) { await db.runStatement(statement, { params: [event.id, event.kind] }); } ``` For bulk loading, prefer [`insertBatch`](/docs/engine.md#bulk-writes), which takes rows or columns directly and never touches the parser. ## Result caching, and turning it off A statement re-run over data that has not changed is answered from a memo rather than executed again. The memo is validated against the catalog before it is served, so it can never return a stale answer — a commit anywhere in the tables the statement reads invalidates it. That is what you want in an application and exactly what you do not want in a benchmark: ```ts // Measures execution. Without `memoize: false` a timing loop measures the cache. await db.query(sql, { memoize: false }); ``` ## Multiple statements One call runs one statement. To make several writes land together — or fail together — use a [write scope](/docs/engine/transactions.md): ```ts await db.write(async (tx) => { await tx.execute("UPDATE stock SET on_hand = on_hand - ? WHERE sku = ?", [1, sku]); await tx.execute("INSERT INTO shipments (sku, shipped_at) VALUES (?, ?)", [sku, new Date()]); }); ``` `BEGIN` / `COMMIT` as SQL text are deliberately not supported: a transaction that a statement string can open is a transaction a lost reference can leave open forever. Scopes end when their callback does. ## What the language covers Joins, subqueries, CTEs including recursive ones, window functions, set operations, grouping sets, `RETURNING`, upserts, triggers, and full-text search. The exact surface — every supported form and every deliberately rejected one — is the [feature matrix](/docs/sql/feature-matrix.md), which is a test fixture rather than a description: each example is executed on every test run. --- Minnow 0.1.1 · this page on the site: /docs/sql/ --- # Reading data > Joins, subqueries, CTEs, window functions, set operations, and grouping sets. The examples on this page run against the [playground](/playground) schema: `stores`, `employees`, `products`, `customers`, `orders`, `order_items`, and `returns`. Paste any of them into the console there. Prefer a typed builder to SQL strings? [Queries](/docs/client/queries.md) covers the same ground through `@minnowdb/client`, and compiles to the same plans. ## Filtering and projection ```sql SELECT order_id, total, placed_at FROM orders WHERE status = 'completed' AND placed_at >= TIMESTAMP '2025-01-01' AND total BETWEEN 20 AND 500 ORDER BY placed_at DESC LIMIT 50 ``` `WHERE` supports comparison, `BETWEEN`, `IN`, `LIKE`, `IS NULL`, `AND` / `OR` / `NOT`, and `CASE`. Predicates on columns with block-level zone maps skip whole row groups without decoding them — see [Query plans](/docs/sql/plans.md). `ORDER BY` takes expressions, `ASC` / `DESC`, `NULLS FIRST` / `NULLS LAST`, and ordinals. `LIMIT` and `OFFSET` both work, and a `LIMIT` without an `ORDER BY` returns rows in no promised order, as in any SQL engine. ## Joins ```sql SELECT s.name AS store, COUNT(*) AS orders, ROUND(SUM(o.total), 2) AS revenue FROM stores s JOIN orders o ON o.store_id = s.store_id LEFT JOIN employees e ON e.employee_id = o.employee_id WHERE o.status = 'completed' GROUP BY s.store_id, s.name ORDER BY revenue DESC ``` `INNER`, `LEFT`, `RIGHT`, `FULL`, and `CROSS` joins are all supported, with `USING` as well as `ON`. The optimizer reorders joins by estimated cardinality and builds hash tables on the smaller side; equality joins take an index-nested-loop path when one side is a unique key. An `ON` clause that carries more than its equality still hashes on that equality — `ON b.order_id = a.order_id AND b.product_id > a.product_id` builds on the order and applies the rest to the pairs it finds, rather than comparing every row with every row. ## Aggregation ```sql SELECT p.category, COUNT(*) AS lines, COUNT(DISTINCT i.order_id) AS baskets, ROUND(SUM(i.line_total), 2) AS revenue, ROUND(AVG(i.line_total), 2) AS average_line, MIN(i.unit_price) AS cheapest, MAX(i.unit_price) AS dearest FROM order_items i JOIN products p ON p.product_id = i.product_id GROUP BY p.category HAVING SUM(i.line_total) > 10000 ORDER BY revenue DESC ``` `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`, and `COUNT(DISTINCT …)` are available, with `FILTER (WHERE …)` for conditional aggregates. `GROUP BY` also accepts `GROUPING SETS`, `ROLLUP`, and `CUBE`. `DISTINCT` is per aggregate, not per query: each one keeps its own set of values, so a select can carry several of them beside ordinary aggregates, inside expressions, and in `HAVING` — `COUNT(DISTINCT r.return_id) / COUNT(DISTINCT i.order_item_id)` counts two different things. An aggregate over a whole column reads only that column. This is the shape columnar storage is for: summing one column of a fourteen-column table touches a fourteenth of the bytes. ## Subqueries and derived tables ```sql SELECT category, name, revenue FROM ( SELECT p.category, p.name, SUM(i.line_total) AS revenue, ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(i.line_total) DESC) AS rank FROM order_items i JOIN products p ON p.product_id = i.product_id GROUP BY p.category, p.name ) AS ranked WHERE rank <= 3 ``` A derived table needs an alias. Scalar subqueries, `IN (SELECT …)`, and `EXISTS` all work, and correlated `EXISTS` decorrelates into a semi-join rather than executing per row. Two correlated forms are rejected rather than executed slowly: `NOT IN` with a correlated subquery, and correlated subqueries joined on a non-equality predicate. Both would need a per-row nested execution, and the error says so instead of quietly taking minutes. ## Common table expressions ```sql WITH monthly AS ( SELECT DATE_TRUNC('month', placed_at) AS month, SUM(total) AS revenue FROM orders WHERE status = 'completed' GROUP BY DATE_TRUNC('month', placed_at) ) SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change FROM monthly ORDER BY month ``` `WITH RECURSIVE` is supported too, for hierarchies and generated series: ```sql WITH RECURSIVE months(month) AS ( SELECT TIMESTAMP '2025-01-01' UNION ALL SELECT month + INTERVAL '1 month' FROM months WHERE month < TIMESTAMP '2025-12-01' ) SELECT month FROM months ``` A CTE can name its own output columns, as `months(month)` does above; a recursive one takes those names before its step runs, which is how the step reads `month` back. `DATE '2025-01-01'` and `TIMESTAMP '2025-01-01 09:30:00'` are both literals, read as UTC — the way every datetime in a Minnow database is stored — and `INTERVAL '1 month'` added to or subtracted from a datetime does calendar arithmetic, so 31 January plus a month is the end of February. ## Window functions ```sql SELECT customer_id, placed_at, total, SUM(total) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total, RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS biggest_basket, LAG(placed_at) OVER (PARTITION BY customer_id ORDER BY placed_at) AS previous_order FROM orders ``` `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `NTILE`, `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`, and the aggregates as window functions, with `PARTITION BY`, `ORDER BY`, and explicit `ROWS` / `RANGE` frames. A window runs after `GROUP BY` and `HAVING`, as the standard orders them, so it ranks the groups rather than the rows behind them and its `OVER` clause reads the group's own aggregates — that is what makes the `ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(i.line_total) DESC)` above the best sellers per category. `SUM(SUM(total)) OVER (PARTITION BY region)` is the same idea: the inner aggregate makes the group, the outer one totals across groups. A window is an expression, so it composes like one. The arithmetic around it runs afterwards, over the column the window produced: ```sql WITH monthly AS ( SELECT DATE_TRUNC('month', placed_at) AS month, SUM(total) AS revenue FROM orders WHERE status = 'completed' GROUP BY DATE_TRUNC('month', placed_at) ) SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS change, 100.0 * revenue / SUM(revenue) OVER () AS pct_of_total FROM monthly ORDER BY month ``` ## Set operations ```sql SELECT customer_id FROM orders WHERE placed_at >= TIMESTAMP '2025-01-01' EXCEPT SELECT customer_id FROM orders WHERE placed_at >= TIMESTAMP '2025-07-01' ``` `UNION`, `UNION ALL`, `INTERSECT`, and `EXCEPT`, each requiring matching column counts and compatible types. ## Consistency One `query` call executes against one version of the database. A join across seven tables sees all seven as they were at a single point, whatever else commits while it runs. To hold that same version across _several_ calls, open a [snapshot scope](/docs/engine/transactions.md#stable-reads). --- Minnow 0.1.1 · this page on the site: /docs/sql/select/ --- # Writing data > Insert, update, delete, upsert, and RETURNING. Prefer a typed builder to SQL strings? [Mutations](/docs/client/writes.md) covers the same ground through `@minnowdb/client`. ## Insert ```sql INSERT INTO customers (customer_id, name, city, signed_up_on) VALUES (?, ?, ?, ?) ``` Multiple row tuples in one statement are fine, and so is `INSERT … SELECT`, which materializes the query at one version before writing: ```sql INSERT INTO archived_orders (order_id, customer_id, total, placed_at) SELECT order_id, customer_id, total, placed_at FROM orders WHERE placed_at < TIMESTAMP '2024-01-01' ``` Columns you leave out are written as `NULL`, so a nullable column can simply be omitted. A `NOT NULL` column without a value or a default is an error. ## Update and delete ```sql UPDATE orders SET status = 'refunded', total = total - ? WHERE order_id = ? DELETE FROM orders WHERE placed_at < ? ``` Any `WHERE` clause works — the engine resolves it to the affected rows and writes a mutation segment addressing them. `SET` expressions can read the row's current values, as `total - ?` does above. > **The table needs a unique key** > > `UPDATE` and `DELETE` are rejected on a table with no `PRIMARY KEY`: > > ``` > UPDATE requires a table with a unique key: logs > ``` > > Mutation segments identify rows by unique key, so a table without one can only be appended to. > This holds for every write path, not just SQL. Give a table a key if it will ever be edited. When you already hold the keys, the batch APIs skip the parser and the lookup: ```ts await db.deleteBatch("orders", { keys: [1001, 1002, 1003] }); ``` ## Upsert ```sql INSERT INTO customers (customer_id, name, city, signed_up_on) VALUES (?, ?, ?, ?) ON CONFLICT (customer_id) DO UPDATE SET name = EXCLUDED.name, city = EXCLUDED.city ``` `DO NOTHING` is also available. `EXCLUDED` refers to the row that would have been inserted, which is how you write "keep the newer value" without reading first. ## RETURNING Any of the four statements can return the rows it touched — post-update values for `UPDATE`, and the removed rows for `DELETE`: ```sql UPDATE products SET list_price = list_price * 1.05 WHERE product_id = ? RETURNING product_id, name, list_price ``` ```ts const result = await db.execute(sql, [productId]); result.returnedRows; // [{ product_id: 42, name: "…", list_price: 18.85 }] ``` This is one round trip instead of a write followed by a read, and it observes exactly the rows the statement wrote — no window in which something else changes them. ## Unique keys A `PRIMARY KEY` column is enforced: inserting a key that already exists throws `UniqueConstraintError` rather than duplicating the row. Membership is tracked separately from the data blocks, so the check does not scan the table. ```ts import { UniqueConstraintError } from "@minnowdb/core"; try { await db.execute("INSERT INTO customers (customer_id, name) VALUES (?, ?)", [1, "Ada"]); } catch (error) { if (error instanceof UniqueConstraintError) { // error.tableName, error.keys } } ``` ## Triggers `AFTER` and `BEFORE` triggers fire inside the same commit as the write that caused them, so a row and everything derived from it publish together or not at all. ```sql CREATE TRIGGER log_refunds AFTER UPDATE ON orders FOR EACH ROW BEGIN INSERT INTO audit (order_id, old_status, new_status, at) VALUES (OLD.order_id, OLD.status, NEW.status, CURRENT_TIMESTAMP); END ``` `DROP TRIGGER log_refunds` removes it. ## Atomicity One statement is one commit. Several statements that must land together belong in a [write scope](/docs/engine/transactions.md): ```ts const { version } = await db.write(async (tx) => { await tx.execute("UPDATE stock SET on_hand = on_hand - ? WHERE sku = ?", [qty, sku]); await tx.execute("INSERT INTO shipments (sku, qty, at) VALUES (?, ?, ?)", [sku, qty, new Date()]); }); ``` Either both are visible or neither is — including to another tab, which never sees the stock decremented without the shipment. The same scope is reachable from SQL, for a console or a client that only speaks statements: ```sql BEGIN; UPDATE stock SET on_hand = on_hand - 1 WHERE sku = 'A-1'; INSERT INTO shipments (sku, qty, at) VALUES ('A-1', 1, CURRENT_TIMESTAMP); COMMIT; ``` Statements inside see each other — a `SELECT` after the `UPDATE` reads the new value — and `ROLLBACK` discards the lot. Two rules keep an open transaction from becoming a leak: schema changes are refused inside one, because the catalog commits outside the scope and a rollback could not take them back, and a transaction left untouched for 30 seconds rolls itself back. A callback scope has no such bound, which is why it stays the better form when you have one. ## Merging `MERGE` writes one source's rows into a table, deciding per row what to do: ```sql MERGE INTO stock s USING (SELECT sku, qty FROM delivery) d ON s.sku = d.sku WHEN MATCHED AND d.qty = 0 THEN DELETE WHEN MATCHED THEN UPDATE SET on_hand = s.on_hand + d.qty WHEN NOT MATCHED THEN INSERT (sku, on_hand) VALUES (d.sku, d.qty) ``` The branches are tried in order for each source row, the whole statement is one commit, and it fires the same triggers the equivalent `INSERT`, `UPDATE`, and `DELETE` would. The `ON` condition has to equate the target's unique key with a source value: that is how rows are addressed, and it is also why one source row can never match two target rows. ## Bulk loading Parsing a statement per row is the wrong shape for loading a lot of data. The batch APIs take rows or columns directly: ```ts await db.insertBatch("orders", rows); // an array of plain objects ``` See [bulk writes](/docs/engine.md#bulk-writes) for the columnar form and the buffered writer. --- Minnow 0.1.1 · this page on the site: /docs/sql/dml/ --- # Tables and types > CREATE TABLE, the column types, and what a unique key buys you. ## CREATE TABLE ```sql CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, employee_id INTEGER, status VARCHAR(20) NOT NULL, total DOUBLE PRECISION NOT NULL, refunded BOOLEAN NOT NULL, placed_at TIMESTAMP NOT NULL ) ``` Columns are nullable unless marked `NOT NULL`. Exactly one column may be `PRIMARY KEY` — written on the column or as a table-level `PRIMARY KEY (order_id)` — and it is the table's unique key: the column that `UPDATE`, `DELETE`, and `ON CONFLICT` address rows through, and the only uniqueness the engine enforces. `CREATE TABLE IF NOT EXISTS` leaves an existing table of that name alone, and `CREATE TABLE … AS SELECT` takes both its columns and its first rows from a query: ```sql CREATE TABLE completed_orders AS SELECT order_id, customer_id, total FROM orders WHERE status = 'completed' ``` A `CHECK` constraint is a row condition over the table's own columns, and it runs on every path that writes a row — insert, upsert, and update, which is checked against the row as it will be once the update lands: ```sql CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, total DOUBLE PRECISION NOT NULL CHECK (total >= 0), status VARCHAR(20) NOT NULL, CONSTRAINT settled_orders_have_a_total CHECK (status <> 'completed' OR total > 0) ) ``` A constraint fails only when it evaluates to false, so SQL's unknown passes: a NULL column satisfies `CHECK (total >= 0)` unless the column is also `NOT NULL`. A `FOREIGN KEY` references another table's unique key — the column the engine can probe for existence, and the one its keyed writes address rows by: ```sql CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(customer_id), note_id INTEGER REFERENCES notes(note_id) ON DELETE SET NULL ) ``` Every write of a referencing column checks that the parent row exists, reading through the writing transaction, so a child inserted beside its parent in one scope sees it. A NULL reference names no parent and is satisfied. `ON DELETE` takes `RESTRICT` (the default), `CASCADE`, and `SET NULL`, and the action runs inside the deleting transaction — a parent and its dependents publish together or not at all. `ON UPDATE` has nothing to act on, because a unique key cannot change. A table without a `PRIMARY KEY` is append-only. That is a reasonable choice for an event log, and a mistake for anything a user edits, because it cannot be changed later without recreating the table. ## Column types Four logical types, chosen because they are what a browser can store and compare without ambiguity. The usual SQL spellings map onto them: | Type | SQL spellings | JavaScript | | ---------- | --------------------------------------------------------------------------------- | ---------- | | `number` | `INTEGER`, `BIGINT`, `SMALLINT`, `DOUBLE PRECISION`, `REAL`, `NUMERIC`, `DECIMAL` | `number` | | `string` | `VARCHAR(n)`, `TEXT`, `CHAR(n)` | `string` | | `boolean` | `BOOLEAN` | `boolean` | | `datetime` | `TIMESTAMP`, `DATE` | `Date` | Widths in `VARCHAR(80)` are accepted and ignored — they document intent, and nothing truncates. `NUMERIC` is IEEE-754 double precision, not arbitrary precision: money is safe to the cent in the ranges an application deals with, but this is not the engine to settle accounts in. Types the engine deliberately does not have — `JSON`/`JSONB`, arrays, `UUID`, `INTERVAL` as a stored type, enums as a database type — are rejected at `CREATE TABLE` rather than silently stored as text. ## Defaults A column can declare a default in SQL, either a constant or `CURRENT_TIMESTAMP`: ```sql CREATE TABLE events ( event_id INTEGER PRIMARY KEY, kind TEXT NOT NULL, source TEXT DEFAULT 'app', noted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ``` A column with a default is `NOT NULL` unless declared otherwise, because the default is what an absent value means — NULL and the default cannot both claim the slot. The same defaults, and the `autoincrement` kind that SQL has no spelling for, are available through `createTable`, which is also the form the [schema DSL](/docs/schema.md) compiles to: ```ts await db.createTable({ name: "events", uniqueKey: "event_id", columns: [ { name: "event_id", type: "number", defaultValue: { kind: "autoincrement" } }, { name: "kind", type: "string" }, { name: "noted_at", type: "datetime", defaultValue: { kind: "now" } }, { name: "source", type: "string", defaultValue: { kind: "literal", value: "app" } }, ], }); ``` Three kinds: `now`, a `literal`, and `autoincrement` on the key column. They fill null-or-absent slots at insert time and are never applied at read time, so adding a default later does not rewrite the rows already stored. Arbitrary expressions are deliberately not representable — the spec is stored in the catalog and crosses the worker boundary, so it has to be plain data. ## Evolving a table ```sql ALTER TABLE orders ADD COLUMN channel TEXT ``` Tables can gain nullable columns and widen a `NOT NULL` column to nullable. Both are catalog-only changes: no blocks are rewritten, so they are effectively instant however large the table is. A column added to a table with rows in it is always nullable — the rows already stored have no value for it — though a `DEFAULT` fills the rows written afterwards. `DROP TABLE` takes the table's rows, its catalog record, its full-text index, and its triggers. The blocks are retired through the commit rather than deleted, so a reader pinned to an older version keeps resolving the bytes it already holds and the collector reclaims them once nobody can reach them: ```sql DROP TABLE IF EXISTS old_orders ``` What a pinned reader does lose is the table itself — the catalog has one present tense, so a snapshot open across a drop sees the table disappear rather than a frozen copy of it. A table another table's trigger writes to cannot be dropped; the trigger would fail at every firing. Narrowing a column, changing its type, dropping it, or adding a `NOT NULL` column to a table with rows in it are all refused — each would need a rewrite of every block, and doing that silently behind a DDL statement is how a browser tab freezes. The [schema DSL](/docs/schema.md) plans those changes for you from a declared schema, with `migrate()` applying only the steps that are safe. ## Reading the catalog ```ts const tables = await db.listTables(); // [{ name: "orders", columns: [{ name: "order_id", type: "number", nullable: false }, …] }] ``` This is what the devtools schema rail and the SQL editor's autocompletion are built on. --- Minnow 0.1.1 · this page on the site: /docs/sql/ddl/ --- # Full-text search > MATCH and BM25 over any column, with no index DDL. Search is a predicate, not a separate subsystem. There is no `CREATE INDEX` and no shadow table: name the columns to search and the engine handles the rest. ```sql SELECT product_id, name FROM products WHERE MATCH(name) AGAINST 'espresso grinder' ``` Several columns at once, or every column in the row: ```sql SELECT name FROM products WHERE MATCH(name, brand) AGAINST 'copper kettle' SELECT name FROM products WHERE MATCH(*) AGAINST 'yirgacheffe' ``` `MATCH(*)` searches numbers and datetimes through their canonical rendering, so `MATCH(*) AGAINST '2025'` finds rows by a date column as well as by text. ## Ranking `BM25` scores a row against the same query, and is an ordinary expression — select it, order by it, filter on it: ```sql SELECT name, BM25(name, brand) AGAINST 'single origin ethiopia' AS score FROM products WHERE MATCH(name, brand) AGAINST 'single origin ethiopia' ORDER BY score DESC LIMIT 20 ``` Scores use the whole column's term statistics, so they are comparable across rows in a way a naive term count is not. ## Prefix matching A trailing `*` matches by prefix, which is what a search-as-you-type box needs: ```sql SELECT name FROM products WHERE MATCH(name) AGAINST 'grind*' ``` Multiple terms are combined; a row matches when it contains all of them. ## Indexes build themselves A `MATCH` on an unindexed column scans and re-verifies, which is fast enough on small tables and slow on large ones. Above a threshold — 4,096 visible rows by default — the first `MATCH` on an append-only column schedules a background index build and answers from the scan meanwhile. Correctness never waits on it. To build one explicitly, before a user's first search rather than during it: ```ts await db.buildFtsIndex("products", "name"); ``` > **Append-only columns only** > > A full-text index can only cover a table that has not been updated or deleted from: > > ``` > Full-text indexes support append-only tables; orders has keyed mutations > ``` > > `MATCH` still works on such a table — it just scans. The index is a pruning accelerator that the > scan re-verifies, so a missing, stale, or invalidated index costs time, never correctness. ## What it does to a term Terms are lowercased and split on non-alphanumeric boundaries. There is no stemming, no stop-word list, and no language configuration: `running` does not match `run`. That is a deliberate floor — a tokenizer that guesses a language is a tokenizer that is wrong for somebody, and the behaviour here is one a caller can predict and pre-process around. ## Tuning the threshold ```ts const db = new MinnowDatabase(store, { ftsAutoIndexRows: 20_000 }); ``` Raise it when tables are small and searches rare; lower it to zero to disable background building entirely and manage indexes yourself. --- Minnow 0.1.1 · this page on the site: /docs/sql/full-text-search/ --- # Query plans > What EXPLAIN shows, and what the optimizer does before it. ```ts console.log( await db.explain(` SELECT c.city, COUNT(*) AS orders FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.status = 'completed' AND o.total > 100 GROUP BY c.city `), ); ``` The plan is the optimized one — what will actually run, not the shape you wrote. ## What the optimizer does **Predicate pushdown.** Filters move as close to their table scan as they can get, so rows are discarded before a join builds a hash table out of them. **Zone-map pruning.** Every block records the minimum and maximum of the column it holds. A predicate that cannot be satisfied inside a block's range skips the block without decoding or decompressing it. This is why `WHERE placed_at >= '2025-01-01'` on a table written in date order reads a fraction of the bytes. **Column pruning.** Only the columns a query mentions are read. A `SELECT` of two columns from a fourteen-column table reads two columns' worth of blocks. **Join reordering.** Joins are ordered by estimated cardinality, and the smaller side becomes the hash build side. An equality join against a unique key takes an index-nested-loop path instead. **Decorrelation.** A correlated `EXISTS` or `IN` subquery is rewritten into a semi-join rather than executed once per outer row. The two correlated forms that cannot be rewritten this way are rejected rather than run row-by-row — see the [feature matrix](/docs/sql/feature-matrix.md). **Top-N.** `ORDER BY … LIMIT n` keeps a bounded heap instead of sorting the whole input. ## Execution The executor is vectorized: it works on batches of column values rather than a row at a time, and scans stream block by block so a table is never required to be resident. When a hash table, sort, or grouping payload would exceed the [memory budget](/docs/engine/memory.md), the operator spills to storage and continues rather than failing. ## Measuring a query honestly Repeating one statement over unchanging data measures the result memo, not execution. In a timing loop, turn it off: ```ts const start = performance.now(); await db.query(sql, { memoize: false }); const elapsed = performance.now() - start; ``` `onStats` reports what an execution actually cost, including peak modeled memory — something the engine can report because it reserves before it allocates: ```ts await db.query(sql, { memoize: false, onStats: (stats) => { console.log(stats); }, }); ``` The [benchmarks page](/benchmarks) runs the full read and write suites against SQLite WASM and PGlite in your own browser, on a dataset size you pick. --- Minnow 0.1.1 · this page on the site: /docs/sql/plans/ --- # Feature matrix > Every SQL form the engine supports, and every one it deliberately rejects. This page is generated from `sql-feature-matrix.json`, which is a **test fixture** rather than a description. On every test run, each supported example below is executed through both of the engine's executors, and each rejected example is checked to still fail with the recorded error. A page built from it cannot drift from what the engine does. Every entry is keyed to the feature identifier ISO/IEC 9075:2023 (SQL:2023) gives it in Annex F, so what the engine claims can be checked against the standard rather than against its own vocabulary. Forms the standard does not define — `MATCH` and BM25 relevance, `ILIKE`, `DATE_TRUNC` — are marked as extensions instead. Supported forms are also diffed against SQLite and PostgreSQL on every run, and where the three genuinely disagree the difference is recorded with the reason rather than hidden: multi-character `TRIM` removes the whole string here, as the standard says, while PostgreSQL removes any of its characters. ## Deliberate omissions Some of what is missing is missing on purpose. The reasons fall into three groups. ### Because the browser is the runtime `BEGIN` … `COMMIT` works, but with a bound on it. A transaction a statement string can open is one a lost reference, a closed tab, or a thrown error could leave open forever, holding storage a background sweep cannot reclaim — so one left untouched rolls itself back, and schema changes are refused inside it, because the catalog commits outside the scope and a rollback could not take them back. `db.write(async (tx) => { … })` remains the form with no bound to hit: it ends when its callback does, however it ends. There is one isolation level, so `SET TRANSACTION ISOLATION LEVEL` has nothing to set: every read runs against a single version and every write scope commits atomically. There are no array, `UUID`, or `INTERVAL` column types. Each would need an encoding, a comparison order, and a set of operators that the columnar format would carry forever, for a gain an application can get today by storing text and parsing it. There is no `JSON` column type either, for the same reason — but the SQL/JSON functions read documents out of ordinary text columns, the way SQLite does: `JSON_VALUE`, `JSON_QUERY`, `JSON_EXISTS`, `JSON_OBJECT`, `JSON_ARRAY`, and the `IS JSON` predicate. `JSON_TABLE` is missing because it produces rows rather than a value, which the executors have no operator for. `FOREIGN KEY` is rejected at `CREATE TABLE` rather than accepted and ignored: referential actions would have to run on every write path of every table, and the engine has no cross-table write hook to hang them on. A constraint that never runs is worse than one the engine declines to promise, so it fails by name — and a `BEFORE` trigger can raise instead. `CHECK` is enforced, on every path that writes a row. ### Because of how mutations work `UPDATE` and `DELETE` need the table to have a unique key. Mutation segments address rows by that key; a table without one can only be appended to. This is not a SQL-layer restriction — it holds for every write path. ### Not yet, rather than never Correlated `NOT IN` and correlated subqueries joined on a non-equality predicate are rejected because the decorrelation rewrite cannot preserve their semantics, and running them row-by-row would turn a query that looks ordinary into one that takes minutes. `NOT EXISTS` expresses the first correctly today. ## Supported 176 forms, each executed on every test run and diffed against SQLite and PostgreSQL wherever the three agree on what the answer should be. | Feature | SQL:2023 | Example | Notes | | --- | --- | --- | --- | | `select.projection` | E051 | `SELECT region, amount FROM rows` | | | `select.alias` | E051-05 | `SELECT amount AS total FROM rows` | | | `select.wildcard` | E051 | `SELECT * FROM rows` | | | `select.distinct` | E051-01 | `SELECT DISTINCT region FROM rows` | | | `select.scalar-subquery` | F471 | `SELECT (SELECT MAX(amount) FROM rows) AS peak FROM rows LIMIT 1` | | | `expression.arithmetic` | E011-04 | `SELECT amount * 2 + 1 AS scaled FROM rows` | | | `expression.round` | T441 | `SELECT ROUND(amount / 3, 2) AS thirds FROM rows` | Precision truncates to an integer and clamps to 0..30; halfway values round away from zero, matching SQLite. | | `literal.string` | E021-03 | `SELECT region FROM rows WHERE region = 'west'` | | | `literal.number` | E011 | `SELECT region FROM rows WHERE amount >= 10` | | | `literal.boolean` | T031 | `SELECT active, amount FROM rows WHERE active = TRUE` | | | `literal.null-comparison` | E131 | `SELECT region FROM rows WHERE region != NULL` | | | `literal.date` | F051-01 | `SELECT region FROM rows WHERE joined >= DATE '2026-01-01'` | | | `literal.timestamp` | F051-03 | `SELECT region FROM rows WHERE joined >= TIMESTAMP '2026-01-01 00:00:00'` | TIMESTAMP 'y-m-d h:m:s' with the time optional. A literal without a zone is UTC, as every datetime in a Minnow database is. | | `parameter.numbered` | E182 | `SELECT region, amount FROM rows WHERE amount >= $1 ORDER BY amount` | Values bind by 1-based number and may repeat; the compiled plan is cached on the SQL text and re-bound per execution. | | `parameter.positional` | E182 | `SELECT region, amount FROM rows WHERE amount >= ? AND active = ? ORDER BY amount` | Each ? takes the next value in order. A statement uses either ? or $n placeholders, never both; PostgreSQL itself has no ? form. | | `join.inner-equi` | F041-01 | `SELECT r.region, d.label FROM rows r JOIN dims d ON d.region = r.region` | | | `join.left-equi` | F041-03 | `SELECT r.region, d.label FROM rows r LEFT JOIN dims d ON d.region = r.region` | | | `where.and` | E061-14 | `SELECT region FROM rows WHERE amount > 5 AND region = 'west'` | | | `where.in-list` | E061-03 | `SELECT region FROM rows WHERE region IN ('west', 'east')` | | | `where.not-in-list` | E061-03 | `SELECT region FROM rows WHERE region NOT IN ('north')` | | | `where.in-subquery` | E061-11 | `SELECT region FROM rows WHERE region IN (SELECT region FROM dims)` | | | `where.scalar-subquery` | E061-09 | `SELECT region FROM rows WHERE amount > (SELECT AVG(amount) FROM rows)` | | | `group-by` | E051-02 | `SELECT region, COUNT(*) AS count FROM rows GROUP BY region` | | | `group-by.rollup` | T431 | `SELECT region, SUM(amount) AS total FROM rows GROUP BY ROLLUP(region)` | ROLLUP/CUBE/GROUPING SETS desugar into a UNION ALL of grouped blocks. The GROUPING() marker is deliberately unsupported, so rollup NULLs and data NULLs are indistinguishable. SQLite itself has none of these. | | `group-by.grouping-sets` | T431 | `SELECT region, active, COUNT(*) AS c FROM rows GROUP BY GROUPING SETS ((region), (active), ())` | | | `having` | E051-06 | `SELECT region, COUNT(*) AS count FROM rows GROUP BY region HAVING COUNT(*) > 1` | | | `aggregate.count` | E091-02 | `SELECT COUNT(*) AS count FROM rows` | | | `aggregate.sum` | E091-05 | `SELECT SUM(amount) AS total FROM rows` | | | `aggregate.avg` | E091-01 | `SELECT AVG(amount) AS mean FROM rows` | | | `aggregate.min-max` | E091-03 | `SELECT MIN(amount) AS low, MAX(amount) AS high FROM rows` | | | `order-by.multi-column` | E121 | `SELECT region, amount FROM rows ORDER BY region, amount DESC` | | | `order-by.wildcard-reference` | E121 | `SELECT * FROM rows ORDER BY amount` | | | `limit` | F856 | `SELECT amount FROM rows ORDER BY amount LIMIT 2` | | | `cte.non-recursive` | T121 | `WITH west AS (SELECT amount FROM rows WHERE region = 'west') SELECT COUNT(*) AS count FROM west` | | | `cte.chained` | T121 | `WITH a AS (SELECT amount FROM rows), b AS (SELECT amount FROM a WHERE amount > 5) SELECT COUNT(*) AS count FROM b` | | | `cte.column-list` | T121 | `WITH totals(place, total) AS (SELECT region, SUM(amount) FROM rows GROUP BY region) SELECT place, total FROM totals` | A CTE names its own output columns. A recursive CTE takes the names before its step member, which refers to the working set by them. | | `derived-table` | F591 | `SELECT d.total FROM (SELECT region, SUM(amount) AS total FROM rows GROUP BY region) d ORDER BY d.total` | | | `union.distinct` | E071-01 | `SELECT region FROM rows UNION SELECT region FROM dims ORDER BY region` | | | `union.all` | E071-02 | `SELECT region FROM rows UNION ALL SELECT region FROM dims` | | | `window.row-number` | T611 | `SELECT region, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount) AS rn FROM rows` | | | `window.rank` | T611 | `SELECT region, RANK() OVER (ORDER BY amount) AS r FROM rows` | | | `window.dense-rank` | T611 | `SELECT region, DENSE_RANK() OVER (ORDER BY amount) AS dr FROM rows` | | | `mutation.insert-values` | E101-01 | `INSERT INTO keyed (name, score) VALUES ('a', 1), ('b', 2)` | Through execute(); query() stays read-only. | | `mutation.update-keyed` | E101-03 | `UPDATE keyed SET score = score + 1 WHERE score > 0` | Requires a unique-key table; read-then-mutate, not serializable. | | `mutation.delete-keyed` | E101-04 | `DELETE FROM keyed WHERE score < 0` | Requires a unique-key table. | | `mutation.returning` | T495 | `DELETE FROM keyed WHERE name = 'x' RETURNING name, score` | RETURNING works on INSERT, UPDATE, and DELETE; inserts echo written values, updates return post-update values, deletes the rows as read. | | `mutation.upsert` | F312 | `INSERT INTO keyed (name, score) VALUES ('x', 9) ON CONFLICT (name) DO UPDATE SET score = EXCLUDED.score` | Whole-row upsert: DO UPDATE must set every inserted column from EXCLUDED, and the conflict target is the unique key. | | `mutation.insert-do-nothing` | F312 | `INSERT INTO keyed (name, score) VALUES ('x', 9), ('z', 1) ON CONFLICT (name) DO NOTHING` | Rows whose key already exists at the statement's snapshot are skipped. | | `mutation.upsert-partial` | F312 | `INSERT INTO keyed (name, score, bonus) VALUES ('x', 50, 9) ON CONFLICT (name) DO UPDATE SET score = EXCLUDED.score` | Assigning a subset of inserted columns merges only those into existing rows; unassigned columns keep their stored values. Mixed update/insert batches publish atomically or roll back together. | | `where.or` | E061-14 | `SELECT region FROM rows WHERE amount > 5 OR region = 'west'` | | | `where.like` | E061-04 | `SELECT region FROM rows WHERE region LIKE 'w%'` | % matches any run and _ matches one Unicode codepoint. | | `predicate.is-distinct-from` | T151 | `SELECT region FROM rows WHERE region IS DISTINCT FROM 'west'` | Null-safe: NULL is not distinct from NULL. | | `predicate.boolean-test` | T031 | `SELECT region FROM rows WHERE active IS TRUE OR active IS UNKNOWN` | IS [NOT] TRUE/FALSE/UNKNOWN never return UNKNOWN; they desugar to null-safe comparisons. | | `predicate.like-escape` | E061-05 | `SELECT region FROM rows WHERE region LIKE 'we!%st' ESCAPE '!' OR region LIKE 'we%'` | ESCAPE makes the next pattern character literal, wildcards included. | | `predicate.quantified` | E061-07 | `SELECT region FROM rows WHERE amount > ALL (SELECT amount FROM dims)` | ANY/SOME/ALL with full three-valued logic; correlated forms are rejected. SQLite itself has no quantified comparisons. | | `predicate.ilike` | Minnow extension | `SELECT region FROM rows WHERE region ILIKE 'WE%'` | Case-insensitive LIKE, a PostgreSQL extension; SQLite's LIKE is case-insensitive by default instead. | | `predicate.match` | Minnow extension | `SELECT region FROM rows WHERE MATCH(region) AGAINST 'west'` | | | `predicate.match-star` | Minnow extension | `SELECT region FROM rows WHERE MATCH(*) AGAINST 'wes*'` | | | `function.bm25` | Minnow extension | `SELECT region, BM25(region) AGAINST 'west' AS score FROM rows WHERE MATCH(region) AGAINST 'west' ORDER BY score DESC` | | | `order-by.expression` | E121 | `SELECT region FROM rows WHERE amount > 0 ORDER BY amount * 2 DESC, region` | | | `where.between` | E061-02 | `SELECT region FROM rows WHERE amount BETWEEN 1 AND 5` | | | `where.between-symmetric` | T461 | `SELECT region FROM rows WHERE amount BETWEEN SYMMETRIC 5 AND 1` | SYMMETRIC accepts the bounds in either order. | | `where.is-null` | E061-06 | `SELECT region FROM rows WHERE region IS NULL` | | | `where.is-not-null` | E061-06 | `SELECT amount FROM rows WHERE region IS NOT NULL` | | | `where.exists` | E061-08 | `SELECT region FROM rows WHERE EXISTS (SELECT 1 FROM dims)` | Uncorrelated EXISTS only; correlated references still fail as unknown aliases. | | `expression.case` | F261-02 | `SELECT CASE WHEN amount > 5 THEN 'big' ELSE 'small' END AS size FROM rows` | | | `subquery.correlated` | E061-13 | `SELECT region FROM rows r WHERE amount > (SELECT AVG(amount) FROM rows q WHERE q.region = r.region)` | Equality-correlated subqueries decorrelate into derived-table joins at compile time; both executors run plain joins. | | `subquery.correlated-exists` | E061-13 | `SELECT amount FROM rows r WHERE EXISTS (SELECT region FROM dims d WHERE d.region = r.region)` | EXISTS joins the subquery's distinct correlation keys; NOT EXISTS becomes a left join checked with IS NULL. | | `subquery.correlated-select` | E061-13 | `SELECT r.region, (SELECT AVG(q.amount) FROM rows q WHERE q.region = r.region) AS regional FROM rows r` | Correlated scalar aggregates decorrelate in the select list too, outside grouped queries. | | `cte.recursive` | T131 | `WITH RECURSIVE n AS (SELECT MIN(amount) AS v FROM rows UNION ALL SELECT v + 1 FROM n WHERE v < 6) SELECT v FROM n` | Linear delta recursion with UNION or UNION ALL, capped at 10,000 iterations and 1,000,000 rows. Plain WITH still rejects self-references. | | `mutation.with-cte` | T121 | `WITH totals AS (SELECT MAX(score) AS top FROM keyed) DELETE FROM keyed WHERE score >= (SELECT top FROM totals) RETURNING name` | WITH precedes INSERT/UPDATE/DELETE; the CTEs are visible to the statement's queries and subqueries. | | `set.intersect` | F302-01 | `SELECT region FROM rows INTERSECT SELECT region FROM dims` | INTERSECT binds tighter than UNION and EXCEPT, per the SQL standard. | | `set.except` | E071-03 | `SELECT region FROM rows EXCEPT SELECT region FROM dims` | | | `set.intersect-all` | F302-02 | `SELECT region FROM rows INTERSECT ALL SELECT region FROM dims` | Bag semantics; SQLite itself has no INTERSECT ALL. | | `set.except-all` | F304 | `SELECT region FROM rows EXCEPT ALL SELECT region FROM dims` | Bag semantics; SQLite itself has no EXCEPT ALL. | | `aggregate.count-distinct` | E091-07 | `SELECT COUNT(DISTINCT region) AS regions FROM rows` | | | `aggregate.filter` | T612 | `SELECT region, COUNT(*) FILTER (WHERE amount > 5) AS big FROM rows GROUP BY region` | Desugars into a CASE inside the aggregate, so it works with every aggregate and DISTINCT. | | `window.aggregate-over` | T611 | `SELECT SUM(amount) OVER (PARTITION BY region) AS total FROM rows` | Default frame only: whole partition without OVER ordering, running with peers when ordered. | | `window.in-expression` | T611 | `SELECT amount, amount - LAG(amount) OVER (ORDER BY amount, region) AS change, 100.0 * amount / SUM(amount) OVER () AS pct FROM rows` | A window is an expression: the arithmetic around it is evaluated after the window has run, over the column it produced. | | `window.over-grouped` | T611 | `SELECT region, SUM(amount) AS total, ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC, region) AS rank, SUM(SUM(amount)) OVER () AS everything FROM rows GROUP BY region HAVING COUNT(*) > 0` | Windows run after GROUP BY and HAVING, as the standard orders them, so they rank groups and their OVER clause reads the group's aggregates. | | `window.value-functions` | T617 | `SELECT amount, FIRST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount) AS lowest, LAST_VALUE(amount) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest FROM rows` | FIRST_VALUE/LAST_VALUE respect the frame; the default frame ends at the current peer group, as the standard specifies. | | `window.ntile` | T614 | `SELECT amount, NTILE(2) OVER (ORDER BY amount) AS half FROM rows` | | | `window.distribution` | T612 | `SELECT amount, PERCENT_RANK() OVER (ORDER BY amount) AS pr, CUME_DIST() OVER (ORDER BY amount) AS cd FROM rows` | | | `window.frame` | T612 | `SELECT amount, SUM(amount) OVER (ORDER BY amount, joined ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS windowed FROM rows` | ROWS frames take row-distance bounds; RANGE frames take UNBOUNDED and CURRENT ROW bounds, where CURRENT ROW spans the ordering peer group. | | `join.right` | F041-04 | `SELECT r.region FROM rows r RIGHT JOIN dims d ON d.region = r.region` | Desugars to the mirrored LEFT JOIN; supported as the sole join of a block. | | `join.non-equi` | F041-08 | `SELECT r.region FROM rows r JOIN dims d ON d.amount > r.amount` | Executes as a nested-loop join (probe x build); equalities keep the hash path. | | `select.distinct-wildcard` | E051-01 | `SELECT DISTINCT * FROM rows` | Expands to DISTINCT over every wildcard output column once input schemas are known. | | `limit.offset` | F856 | `SELECT amount FROM rows LIMIT 5 OFFSET 2` | OFFSET is accepted directly after LIMIT. | | `select.no-from` | E051 | `SELECT 1 + 1 AS two, UPPER('minnow') AS name` | | | `select.values` | F641 | `SELECT v.column1 AS n, v.column2 AS tag FROM (VALUES (1, 'one'), (2, 'two')) v` | VALUES works standalone, as a set-operation member, and as a derived table with AS alias(col, ...) renaming; columns default to column1..columnN. | | `limit.parameter` | F865 | `SELECT region, amount FROM rows ORDER BY region NULLS LAST, amount LIMIT $1 OFFSET $2` | LIMIT and OFFSET take placeholders; the plan re-binds per execution like any parameter. | | `limit.fetch-first` | F856 | `SELECT amount FROM rows ORDER BY amount OFFSET 1 ROWS FETCH FIRST 2 ROWS ONLY` | The standard fetch clause is a spelling of LIMIT; SQLite itself only speaks LIMIT. | | `offset.standalone` | F856 | `SELECT amount FROM rows ORDER BY amount OFFSET 2` | OFFSET no longer requires LIMIT. SQLite itself needs LIMIT -1 OFFSET n. | | `ddl.create-table` | F031-01 | `CREATE TABLE made (id INTEGER PRIMARY KEY, label TEXT NOT NULL, at TIMESTAMP)` | Standard type names map onto the four logical types (widths parse and are ignored); one PRIMARY KEY or UNIQUE column becomes the unique key. Dropping or altering tables stays a programmatic concern. | | `trigger.create-after` | T211 | `CREATE TRIGGER keyed_audit AFTER INSERT ON keyed BEGIN INSERT INTO rows (region, amount) VALUES (NEW.name, NEW.score); END` | AFTER and BEFORE row triggers on INSERT/UPDATE/DELETE, executed atomically inside the triggering commit with NEW/OLD references. Bodies: INSERT ... VALUES into keyless tables; UPDATE/DELETE against keyed tables. One cascade level is allowed; deeper chains error at write time. | | `trigger.create-before` | T211 | `CREATE TRIGGER keyed_before BEFORE INSERT ON keyed BEGIN INSERT INTO rows (region, amount) VALUES (NEW.name, NEW.score); END` | BEFORE bodies stage ahead of the primary write but publish in the same atomic commit, so timing is a portability feature: atomicity and visibility are identical to AFTER. | | `trigger.body-update-delete` | T211 | `CREATE TRIGGER keyed_counts AFTER INSERT ON keyed BEGIN UPDATE stats SET total = total + NEW.score WHERE region = NEW.name; END` | UPDATE and DELETE trigger bodies run against keyed tables, reading current state each firing. Touching the same target row twice in one firing is rejected. | | `trigger.drop` | T211 | `DROP TRIGGER droppable_audit` | | | `where.parenthesized` | E061-14 | `SELECT region FROM rows WHERE (amount > 5 AND region = 'west')` | | | `where.not` | E061-14 | `SELECT region FROM rows WHERE NOT active = TRUE` | | | `expression.concat` | E021-07 | `SELECT region \|\| '-' \|\| label AS tag FROM dims` | \|\| concatenates strings and propagates NULL; non-string operands are a type error. | | `expression.modulo` | T441 | `SELECT amount % 3 AS remainder FROM rows` | Division and remainder by zero are NULL, matching SQLite. | | `expression.cast` | F201 | `SELECT CAST(amount AS INTEGER) AS whole, CAST(amount AS TEXT) AS label FROM rows` | Standard type names map to the four logical types; integer targets truncate toward zero, and non-numeric strings fail rather than becoming 0. | | `identifier.quoted` | E031-01 | `SELECT "region", "rows"."amount" FROM "rows" WHERE "amount" > 5` | Double-quoted identifiers are never keywords and keep their exact spelling. | | `order-by.nulls` | T611 | `SELECT region, amount FROM rows ORDER BY region NULLS LAST, amount` | Without NULLS FIRST/LAST the default matches SQLite: NULLs first ascending, last descending. | | `expression.coalesce` | F261-04 | `SELECT COALESCE(region, 'unknown') AS region_label FROM rows` | Arguments evaluate left to right; the first non-NULL value wins. All non-NULL arguments must share one type. | | `expression.date-trunc` | Minnow extension | `SELECT DATE_TRUNC('month', joined) AS joined_month FROM rows` | Units: year, quarter, month, week (Monday start), day, hour, minute, second. Truncation is in UTC; the engine has no session time zone. | | `expression.date-add` | F052 | `SELECT joined + INTERVAL '1 month' AS next_month, joined - INTERVAL '2 days 3 hours' AS earlier FROM rows WHERE joined IS NOT NULL` | INTERVAL added to or subtracted from a datetime. Months are calendar arithmetic, so 31 January plus a month clamps to the end of February. | | `function.string-core` | E021-08 | `SELECT UPPER(label) AS u, LOWER(label) AS l, LENGTH(label) AS n, SUBSTR(label, 2, 3) AS mid, TRIM(label) AS t FROM dims` | SUBSTRING is accepted as a spelling of SUBSTR; LENGTH and SUBSTR count characters, not UTF-16 units. | | `function.abs` | T441 | `SELECT ABS(amount - 5) AS distance FROM rows` | | | `function.numeric-core` | T441 | `SELECT NULLIF(amount, 3) AS n, GREATEST(amount, 5) AS g, LEAST(amount, 5) AS l, FLOOR(amount) AS f, CEILING(amount) AS c, MOD(amount, 4) AS m, POWER(2, 3) AS p, SQRT(16) AS s FROM rows` | GREATEST/LEAST ignore NULL arguments, matching PostgreSQL. | | `function.string-extended` | E021-06 | `SELECT REPLACE(region, 'we', 'be') AS r, LTRIM(' x') AS lt, RTRIM('x ') AS rt, INSTR(region, 'st') AS i FROM rows WHERE region IS NOT NULL` | | | `function.extract` | F052 | `SELECT EXTRACT(year FROM joined) AS y, EXTRACT(dow FROM joined) AS d FROM rows WHERE joined IS NOT NULL` | Fields: year, quarter, month, week (ISO), day, hour, minute, second, epoch, dow — all in UTC. SQLite spells this strftime. | | `aggregate.distinct-argument` | E091-07 | `SELECT region, COUNT(DISTINCT amount) AS amounts, COUNT(DISTINCT active) AS states, SUM(amount) AS total FROM rows GROUP BY region` | COUNT/SUM/AVG/MIN/MAX accept DISTINCT. Each one keeps its own set of values, so several can appear in one select, beside ordinary aggregates, inside expressions, and in HAVING. | | `join.multi-key` | F041-01 | `SELECT r.region FROM rows r JOIN dims d ON d.region = r.region AND d.amount = r.amount` | Multi-key conditions execute as a nested-loop join; single equalities keep the hash path. | | `join.cross` | F401-04 | `SELECT r.region AS region, d.label AS label FROM rows r CROSS JOIN dims d` | | | `join.full` | F401-02 | `SELECT r.amount AS amount, d.label AS label FROM rows r FULL JOIN dims d ON d.region = r.region` | Desugars into a union of two left joins, so it must be the sole join, with an equality ON and no grouping or DISTINCT yet. | | `order-by.ordinal` | E121 | `SELECT region, amount FROM rows ORDER BY 2 DESC` | Ordinals resolve to the select list at compile time; out-of-range ordinals are an error. | | `window.lag-lead` | T615 | `SELECT amount, LAG(amount) OVER (ORDER BY amount) AS previous, LEAD(amount, 1, -1) OVER (ORDER BY amount) AS next FROM rows` | LAG/LEAD take a constant offset (default 1) and default value (default NULL), and require ORDER BY inside OVER. | | `mutation.insert-select` | E101-01 | `INSERT INTO keyed (name, score) SELECT name \|\| '2' AS name, score + 1 AS score FROM keyed` | The SELECT runs at one snapshot and materializes before the batch write. | | `mutation.merge` | F312 | `MERGE INTO keyed k USING (SELECT 'z' AS name, 9 AS score) s ON k.name = s.name WHEN MATCHED THEN UPDATE SET score = s.score WHEN NOT MATCHED THEN INSERT (name, score) VALUES (s.name, s.score)` | One pass over the source decides each row's branch, and the branches apply as batched writes inside a single write scope — atomic, and firing the same triggers the equivalent INSERT, UPDATE, and DELETE would. The match must equate the target's unique key with a source value, which is how rows are addressed. Two source rows matching one target row is a cardinality violation, as the standard says, rather than a last-one-wins race. MATCHED BY SOURCE, MATCHED BY TARGET, and RETURNING are not supported. | | `transaction.begin` | E151-01 | `BEGIN` | Holds the same scope `write()` opens between statements instead of around a callback: writes stage into it, reads see what it staged, and COMMIT publishes them together. Schema changes are refused inside one, because the catalog commits outside the scope and a rollback could not take them back. A transaction left untouched past the idle bound rolls itself back, so an abandoned BEGIN cannot hold storage forever. | | `transaction.commit` | E151-01 | `COMMIT` | | | `transaction.rollback` | E151-02 | `ROLLBACK` | | | `function.char-length` | E021-04 | `SELECT CHAR_LENGTH(region) AS n FROM rows WHERE region IS NOT NULL` | | | `function.octet-length` | E021-05 | `SELECT OCTET_LENGTH(region) AS n FROM rows WHERE region IS NOT NULL` | Counts the UTF-8 encoding's bytes. | | `function.substring-from-for` | E021-06 | `SELECT SUBSTRING(region FROM 1 FOR 2) AS part FROM rows WHERE region IS NOT NULL` | The position window is intersected with the string, so a start below 1 shortens the result instead of shifting it. | | `function.trim-specification` | E021-09 | `SELECT TRIM(LEADING 'w' FROM region) AS trimmed FROM rows WHERE region IS NOT NULL` | | | `function.trim-multi-character` | T056 | `SELECT TRIM(BOTH 'we' FROM region) AS trimmed FROM rows WHERE region IS NOT NULL` | The trim string is removed as a whole repeated unit, per the standard; PostgreSQL reads a multi-character argument as a set of characters instead. | | `function.position` | E021-11 | `SELECT POSITION('es' IN region) AS at FROM rows WHERE region IS NOT NULL` | | | `function.pad` | T055 | `SELECT LPAD(region, 6, '-') AS padded FROM rows WHERE region IS NOT NULL` | | | `function.overlay` | T042 | `SELECT OVERLAY(region PLACING 'X' FROM 1 FOR 1) AS masked FROM rows WHERE region IS NOT NULL` | | | `select.qualified-wildcard` | E051-07 | `SELECT rows.* FROM rows` | Output names follow the rule a bare * uses: the column's own name from one source, alias-qualified from several. | | `from.column-alias-list` | E051-09 | `SELECT y.a AS a FROM rows AS y(a, b, c, d)` | | | `aggregate.all-quantifier` | E091-06 | `SELECT SUM(ALL amount) AS total FROM rows` | | | `derived-table.set-operation` | E071-06 | `SELECT s.amount AS amount FROM (SELECT amount FROM rows UNION SELECT amount FROM dims) s` | | | `comment.simple` | E161 | `SELECT amount FROM rows -- a comment` | | | `comment.bracketed` | T351 | `SELECT /* a comment */ amount FROM rows` | | | `join.comma` | F041-07 | `SELECT rows.amount AS amount FROM rows, dims WHERE dims.region = rows.region` | | | `join.using` | F401-04 | `SELECT rows.amount AS amount FROM rows JOIN dims USING (region)` | The joined columns are not merged the way the standard describes: they stay one per side, so `SELECT *` returns both and an unqualified reference to a join column is ambiguous. Qualify it, or name the side you want. | | `join.natural` | F401-01 | `SELECT rows.amount AS amount FROM rows NATURAL JOIN dims` | The shared columns are compared but not merged, so an unqualified reference to one is ambiguous — qualify it. NATURAL RIGHT JOIN is rejected, because the right-join mirror rewrites the sources the shared-column search reads. | | `datetime.current-date` | F051-06 | `SELECT CURRENT_DATE > DATE '2000-01-01' AS elapsed` | Resolved once per execution, so every row of a statement sees one instant; results that read the clock never memoize. | | `datetime.current-timestamp` | F051-08 | `SELECT CURRENT_TIMESTAMP > TIMESTAMP '2000-01-01 00:00:00' AS elapsed` | | | `datetime.localtime` | F051-07 | `SELECT LOCALTIME IS NOT NULL AS ticking` | The engine has no TIME type, so LOCALTIME reads as an 'HH:MM:SS' string, like SQLite's CURRENT_TIME. | | `predicate.row-comparison` | F641 | `SELECT amount FROM rows WHERE (region, amount) = ('west', 10)` | | | `predicate.row-in` | F641 | `SELECT amount FROM rows WHERE (region, amount) IN (('west', 10), ('east', 3))` | | | `predicate.row-null` | F641 | `SELECT amount FROM rows WHERE (region, region) IS NOT NULL` | | | `literal.radix` | T661 | `SELECT 0x0A AS ten` | | | `literal.digit-separator` | T662 | `SELECT 1_000 AS thousand` | | | `limit.with-ties` | F866 | `SELECT region FROM rows WHERE region IS NOT NULL ORDER BY region DESC FETCH FIRST 1 ROWS WITH TIES` | The limit cannot be pushed into a scan, so these plans run unlimited and the ordered result is trimmed. | | `cte.in-subquery` | T122 | `SELECT s.amount AS amount FROM (WITH inner_cte AS (SELECT amount FROM rows) SELECT amount FROM inner_cte) s` | | | `window.nth-value` | T618 | `SELECT NTH_VALUE(amount, 2) OVER (ORDER BY amount) AS second FROM rows` | | | `window.named` | T620 | `SELECT SUM(amount) OVER w AS running FROM rows WINDOW w AS (ORDER BY amount)` | | | `window.frame-groups` | T612 | `SELECT COUNT(*) OVER (ORDER BY amount GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW) AS peers FROM rows` | | | `window.frame-exclude` | T612 | `SELECT COUNT(*) OVER (ORDER BY amount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS others FROM rows` | | | `aggregate.grouping` | T433 | `SELECT GROUPING(region) AS aggregated FROM rows GROUP BY ROLLUP(region)` | A bitmask over the arguments, most significant first. | | `aggregate.any-value` | T626 | `SELECT ANY_VALUE(amount) AS sample FROM rows` | Which row of the group answers is implementation-dependent; this engine returns the minimum. | | `aggregate.variance` | T621 | `SELECT VAR_POP(amount) AS spread FROM rows` | Built from COUNT and SUM rather than a dedicated accumulator: the variance is E(x2) - E(x)2. Bare VARIANCE and STDDEV are the sample forms, as in PostgreSQL. | | `aggregate.stddev` | T621 | `SELECT STDDEV_POP(amount) AS spread FROM rows` | | | `aggregate.boolean` | T631 | `SELECT EVERY(amount > 1) AS all_positive FROM rows` | | | `json.value` | T822 | `SELECT JSON_VALUE('{"a": 1}', '$.a') AS a` | JSON documents are text in ordinary string columns, as in SQLite; the path subset is $, member steps, and array subscripts. | | `json.query` | T823 | `SELECT JSON_QUERY('{"a": [1, 2]}', '$.a') AS a` | | | `json.exists` | T821 | `SELECT JSON_EXISTS('{"a": 1}', '$.a') AS present` | | | `json.is-json` | T825 | `SELECT '{"a": 1}' IS JSON OBJECT AS shaped` | | | `json.object` | T811 | `SELECT JSON_OBJECT('a' VALUE 1) AS document` | | | `json.array` | T812 | `SELECT JSON_ARRAY(1, 2) AS document` | | | `ddl.create-table-if-not-exists` | F031-01 | `CREATE TABLE IF NOT EXISTS made (a INTEGER)` | | | `ddl.create-table-default` | E141-07 | `CREATE TABLE defaulted (id INTEGER PRIMARY KEY, tier TEXT DEFAULT 'basic')` | A column with a DEFAULT is NOT NULL unless declared otherwise: the engine fills absent values from the default, so NULL and the default cannot both claim the slot. | | `ddl.create-table-key-clause` | E141-08 | `CREATE TABLE keyed_clause (a INTEGER, b TEXT, PRIMARY KEY (a))` | | | `ddl.alter-table-add-column` | F031-04 | `ALTER TABLE rows ADD COLUMN note TEXT` | Existing rows have no value for the new column, so it is always nullable. | | `ddl.create-table-as-select` | T172 | `CREATE TABLE copied AS SELECT region FROM rows` | | | `ddl.drop-table` | F031-13 | `DROP TABLE doomed` | Takes the table's rows, catalog record, full-text index, and triggers. The blocks are retired through the commit rather than deleted, so a reader pinned to an older version keeps resolving them and the lease-aware collector reclaims them later. Refused while a view reads the table or another table's trigger writes to it — both would be left pointing at something that is not there. DROP TABLE CASCADE is refused too: nothing cascades, because there are no dependent objects to reach. | | `ddl.create-view` | F031-02 | `CREATE VIEW west AS SELECT region, amount FROM rows WHERE region = 'west'` | The catalog stores the query text and the schema inferred from it, so a view answers the same questions a table does and reads expand it into the query it stands for — anywhere a read runs, including inside a write scope. A view is never a write target. CREATE OR REPLACE redefines one; a view stacked on it follows the new definition, and a cycle two redefinitions close is caught on the next read rather than recursed. | | `ddl.drop-view` | F031-16 | `DROP VIEW doomed_view` | | | `ddl.check-constraint` | E141-06 | `CREATE TABLE checked (a INTEGER NOT NULL CHECK (a > 0), CONSTRAINT small CHECK (a < 100))` | A row condition over the table's own columns, evaluated by the writer on every path that writes a row — insert, upsert, and update, which is checked against its post-image. A constraint fails only when it evaluates to false, so SQL's unknown passes: NULL satisfies CHECK (a > 0) unless the column is also NOT NULL. | | `ddl.foreign-key` | E141-04 | `CREATE TABLE children (id INTEGER PRIMARY KEY, parent INTEGER REFERENCES parents(id) ON DELETE CASCADE)` | Single-column references to the parent's unique key, which is the column the engine can probe for existence and the one its keyed writes address rows by. Every write of a referencing column checks the parent exists, reading through the writing transaction so a child inserted beside its parent in one scope sees it; a NULL reference names no parent and is satisfied. ON DELETE takes RESTRICT (the default), CASCADE, and SET NULL, applied inside the deleting transaction. ON UPDATE has nothing to act on, because a unique key cannot change. | The same data as JSON: /sql-feature-matrix.json ## Rejected 14 forms, each checked on every test run to still fail with the error below. | Feature | SQL:2023 | Example | Rejected with | | --- | --- | --- | --- | | `subquery.correlated-non-equi` | E061-13 | `SELECT region FROM rows r WHERE EXISTS (SELECT region FROM dims d WHERE d.amount > r.amount)` | support only equality conditions | | `subquery.correlated-not-in` | E061-13 | `SELECT region FROM rows r WHERE region NOT IN (SELECT d.region FROM dims d WHERE d.region = r.region)` | use NOT EXISTS | | `mutation.update-keyless` | E101-03 | `UPDATE rows SET amount = 1` | UPDATE requires a table with a unique key | | `transaction.isolation-level` | E152-01 | `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` | Expected SELECT, found SET | | `privileges.grant` | E081 | `GRANT SELECT ON rows TO reader` | Expected SELECT, found GRANT | | `from.lateral` | T491 | `SELECT x.amount FROM rows, LATERAL (SELECT amount FROM dims WHERE dims.region = rows.region) x` | LATERAL sources are not supported | | `aggregate.listagg` | T625 | `SELECT LISTAGG(region, ',') AS regions FROM rows` | Unsupported function: LISTAGG | | `json.table` | T824 | `SELECT j.a FROM rows, JSON_TABLE(rows.region, '$' COLUMNS (a INTEGER PATH '$.a')) AS j` | JSON_TABLE is not supported | | `predicate.similar-to` | T141 | `SELECT amount FROM rows WHERE region SIMILAR TO 'w%'` | Expected eof, found SIMILAR | | `collation.explicit` | F690 | `SELECT region FROM rows ORDER BY region COLLATE "en"` | Expected eof, found COLLATE | | `aggregate.json` | T826 | `SELECT JSON_ARRAYAGG(region) AS regions FROM rows` | Unsupported function: JSON_ARRAYAGG | | `type.array` | S091 | `SELECT ARRAY[1, 2] AS pair` | Unsupported SQL character: [ | | `type.time` | F051-02 | `SELECT TIME '12:00:00' AS at` | Expected eof | | `ddl.sequence` | T176 | `CREATE SEQUENCE order_ids` | Expected TABLE, found SEQUENCE | The same data as JSON: /sql-feature-matrix.json --- Minnow 0.1.1 · this page on the site: /docs/sql/feature-matrix/ --- # Schema & migrations > Define tables once in TypeScript, and evolve them without rewriting stored data. Define tables once in TypeScript. The same declaration drives migrations, row types, and every query builder — and `migrate()` never rewrites stored data. Schema management lives in `@minnowdb/core`, not in the typed client. `migrate()` is an engine capability, and the catalog it produces is what [schema tooling of any kind](/docs/reference/extending.md) builds on — so it works with or without [the query builder](/docs/client.md). The examples below use the builder where it makes the types easier to see. ## Defining a schema ```ts import { column, schema, table, view } from "@minnowdb/core"; import { createMinnow, type InferDatabase } from "@minnowdb/client"; const people = table("people", { name: column.string().unique(), score: column.number(), joined: column.datetime().nullable(), }); const appSchema = schema([people]); interface DB extends InferDatabase {} await database.migrate(appSchema); // The same declaration drives the query builder's types end to end: const db = createMinnow(database, { schema: appSchema }); await db.insertInto("people").values({ name: "Ada", score: 10 }).execute(); // joined pads to null const rows = await db.selectFrom("people").selectAll().execute(); // Array<{ name: string; score: number; joined: Date | null }> ``` There's a builder per column type — `column.boolean()`, `column.number()`, `column.string()`, `column.datetime()`, and `column.enum([...])` — and six modifiers: - **`.unique()`** — marks the table's unique key (one non-null column). - **`.nullable()`** — permits NULL and widens the inferred type. - **`.autoIncrement()`** — generates monotonically increasing integers for omitted values from a persistent per-table counter that stays atomic across tabs. Number unique-key columns only. Explicit values are accepted and bump the counter past their maximum, so imports keep stable ids. - **`.default(value)` / `.default(fn)`** — fills omitted (or null) slots at insert time. A plain value persists in the catalog and fills inside the engine, so every write path gets it — raw batches, SQL statements, other tabs (datetime columns take only `"now"`, which stamps one consistent timestamp per batch). A function is a userland generator — `() => ulid()`, a custom nanoid, anything: the typed facade (`insertInto`, `typedTable`) calls it just before the batch is sent, so it never persists, never crosses the worker boundary, and write paths that skip the facade don't see it. Defaults require non-nullable columns, and `returning` echoes the written values either way. - **`.renamedFrom()`** — renames through the column's stable ID, so a rename is a metadata step rather than a drop-and-add. - **`.backfill(value)`** — what rows written before this column existed read as, instead of NULL. Giving one is what makes adding a **non-nullable** column possible. - **`.references(table, column, { onDelete })`** — declares a FOREIGN KEY onto another table's unique key. `migrate()` creates it as a real constraint, so a write naming a parent row that does not exist is rejected. `onDelete` is `"restrict"` (the default), `"cascade"`, or `"set null"`; the last requires a nullable column. ### Enum columns `column.enum([...])` is a string column restricted to a closed set of values, typed as their literal union: ```ts const tickets = table("tickets", { id: column.number().unique().autoIncrement(), status: column.enum(["open", "closed", "reopened"]).default("open"), }); // Selects return "open" | "closed" | "reopened"; inserts and updates accept nothing else. await db.insertInto("tickets").values({ status: "open" }).execute(); await db.insertInto("tickets").values({ status: "lost" }).execute(); // compile error ``` The set is enforced twice: at compile time through the inferred union, and at runtime on every write path (batch inserts, upserts, keyed updates, and SQL statements), so an untyped caller can't sneak an outside value into storage. Physically the column stays a plain string column — the value set is catalog metadata, which keeps its migrations metadata-only: adding values or relaxing the column to `column.string()` is safe, while removing values or tightening an existing string column into an enum is rejected (existing rows could already violate the set). ```ts const notes = table("notes", { id: column.number().unique().autoIncrement(), slug: column.string().default(() => nanoid()), // any userland generator status: column.string().default("draft"), created: column.datetime().default("now"), body: column.string(), }); // Insert types make generated columns optional: await db.insertInto("notes").values({ body: "hello" }).returningAll().executeTakeFirstOrThrow(); // { id: 1, slug: "V1StGXR8_Z5jdHi6B-myT", status: "draft", created: Date, body: "hello" } ``` `InferDatabase` carries the "engine can fill this" fact into insert types automatically. If you hand-write your `DB` interface instead, mark those columns with `Generated` and wrap the row in `FromRow`, which reads the marker once — at your declaration — and produces the three shapes: ```ts import { type FromRow } from "@minnowdb/client"; interface DB { notes: FromRow<{ id: Generated; slug: Generated; body: string }>; } ``` Declaration order does not matter: `migrate()` creates a table after the tables it references, so a child may be listed before its parent. ### Relations and row conditions A declared relation is enforced, not decorative. The same is true of `checks`, the third argument to `table()`: ```ts const parents = table("parents", { id: column.number().unique(), label: column.string(), }); const children = table( "children", { id: column.number().unique(), parent_id: column.number().references("parents", "id", { onDelete: "cascade" }), qty: column.number(), }, { checks: [{ name: "positive_qty", sql: "qty > 0" }] }, ); await database.insertBatch("children", [{ id: 1, parent_id: 999, qty: 1 }]); // throws: FOREIGN KEY children_parent_id_fkey has no parents row with 999 await database.insertBatch("children", [{ id: 1, parent_id: 1, qty: 0 }]); // throws: CHECK positive_qty failed for row 0 of children ``` This is exactly what the equivalent SQL DDL produces — same catalog, same constraint names, same rejections: ```sql CREATE TABLE children ( id INTEGER PRIMARY KEY, parent_id INTEGER NOT NULL REFERENCES parents(id) ON DELETE CASCADE, qty INTEGER NOT NULL, CONSTRAINT positive_qty CHECK (qty > 0) ); ``` Each check is a boolean SQL expression over the table's own columns, compiled when the table is created — so an expression the engine cannot evaluate fails at migration time rather than on the first write. ### Backfilling an added column A column added by a migration has no data in older segments, so its rows would read NULL forever — which is why an added column had to be nullable. A backfill says what those rows read instead: ```ts const notes = table("notes", { id: column.number().unique(), body: column.string(), status: column.string().backfill("archived"), // added later; old rows read "archived" }); ``` Nothing is rewritten. The stored segments are untouched, and the value is substituted at read time — so adding a backfilled column to a table of ten million rows costs a catalog write, not a scan. Compaction folds the value into the blocks whenever it next rewrites them. The value is real to the engine, not patched onto output rows: you can filter, group, and join on it exactly as if it had always been stored. A function runs **once**, when the migration adds the column, and its result is frozen into the catalog: ```ts column.datetime().backfill(() => new Date()); // one timestamp, shared by every pre-existing row ``` That is what "derived" means here — derived at migration time, not per row. A value that depends on _other columns_ would need one value per row, which is a rewrite rather than a metadata step, and is not supported. Backfills apply to non-nullable columns only. A nullable column already has an answer for rows that never had a value, and the declaration is rejected rather than quietly ignored. ## Views A view is a named query. Declare it with the columns you expect it to produce: ```ts const activeCustomers = view("active_customers", { sql: `SELECT customer_id, name FROM customers WHERE status = 'active'`, columns: { customer_id: column.number(), name: column.string() }, }); const appSchema = schema([customers, orders], { views: [activeCustomers] }); ``` The engine infers the query's real output schema when it creates the view and compares it to what you declared, so a body that drifts from its declaration fails the migration instead of surprising a reader later. **Views are readable, not writable.** They join `DB` like tables, so `selectFrom` works and the row type is what you declared — but they carry no insert shape, which makes a write a compile error: ```ts await db.selectFrom("active_customers").select(["name"]).execute(); // fine await db.insertInto("active_customers"); // compile error: not a writable table ``` Because nothing is stored under a view, replacing one is always safe: change the `sql` and the next `migrate()` redefines it in place. Removing the declaration drops the view — within a schema, the declaration is the source of truth. That authority stops at the views the schema created. A view made with `CREATE VIEW`, or one written before Minnow recorded ownership, belongs to no schema and no migration removes it — "the schema never mentioned it" is not proof that it should go. `introspect()` reports which is which as `managed`, and `database.dropView(name)` removes either. ## What migrate() does `migrate()` compares the live catalog with your declaration and applies the difference as deterministic steps: | Step | What it does | | ---------------------------- | -------------------------------------------------------------------------- | | create table | Including its constraints, and after any table it references. | | add column | Nullable, or non-nullable [with a backfill](#backfilling-an-added-column). | | rename column | Through the column's stable ID, so it is not a drop plus an add. | | widen nullability | NOT NULL to NULL. | | tighten nullability | NULL to NOT NULL, [proven first](#proven-rather-than-assumed). | | widen an enum | Add values, or drop the restriction to a plain string. | | alter a default | Defaults are write-time only, so changing one never touches stored rows. | | adopt or drop auto-increment | [Proven first](#proven-rather-than-assumed) when adopting. | | replace a view | Nothing is stored under a view, so a body change needs no proof. | | drop a column or table | Only [with your say-so](#dropping-things). | - **Each step is atomic**, and the whole run is idempotent — an interrupted migration completes by re-running. - **Concurrent migrators can't interleave** — the loser fails with a typed conflict. - **Nothing is rewritten.** Not one stored byte changes: a column added later is answered at read time, and folding it into the blocks is [compaction](/docs/storage/maintenance.md#compaction)'s job, on its own schedule. ### Proven rather than assumed Two changes are earned rather than declared. Both read block headers only — the same checksum-authenticated statistics that drive data skipping — so they cost one header read per block, with nothing decompressed or decoded: - **Tightening a column to NOT NULL.** Every block records its own null count. If any visible row holds NULL the migration is refused and nothing is applied; otherwise the column tightens with no scan and no rewrite. Rows written before the column existed count as NULL unless it carries a backfill. - **Adopting `.autoIncrement()`.** The counter is seeded past the largest key already stored, taken from each block's numeric zone map, so a generated id can never collide with one already written. Dropping the generator is free — writes simply stop being filled. ## Dropping things Removing a column from a table you declare, or a table from a schema that speaks for the whole database, is a **metadata step** — the column stops being projected, the table record goes, and compaction reclaims the bytes when it next rewrites those segments. Nothing is scanned. It is also the only kind of migration that destroys data, and a migration runs when an application opens, with nobody to review it. A schema file that drifted — a rename typed wrong, a branch checked out — would otherwise delete rows on launch. So destroying anything is a decision you make: ```ts await database.migrate(appSchema); // throws, naming exactly what it would have destroyed await database.migrate(appSchema, { allowDestructive: true }); // applies it ``` Tables need a second word. A schema is not necessarily the whole database — an application may migrate feature by feature, each call declaring only its own tables — so a table you no longer declare is left alone unless you say the schema speaks for everything: ```ts await database.migrate(appSchema, { allowDestructive: true, schemaOwnsDatabase: true }); ``` Even then, only tables a migration created are dropped. One made with `CREATE TABLE` belongs to no schema, exactly as with [views](#views). A drop is refused outright when something in the catalog still points at the column — the unique key, a `FOREIGN KEY`, or a `CHECK` — because dropping it would leave that constraint naming a column that is not there. Rejected outright, rather than attempted: - type changes - unique-key changes - non-nullable column additions **without a backfill** - removing enum values, or tightening a string column into an enum Both need the stored bytes rewritten, which is compaction's job, not a migration's. - adding, changing, or dropping a FOREIGN KEY or CHECK on an existing table That last one is the same rule as the rest: existing rows are not known to satisfy a constraint nobody has verified them against, and there is no validation scan. Declare constraints when you create the table, or create a new table and copy deliberately. Views are the exception — they hold no rows, so replacing one needs no proof. If you need one of those, that's a new table plus a deliberate application-level copy. ## Inferred shapes `InferDatabase` maps each name in your schema to its shapes. A table contributes three; a view contributes one. ```ts type DB = { people: { select: {...}; insert: {...}; update: {...} }; active_people: { select: {...} }; // a view: readable only }; ``` Naming the three explicitly is what makes `DB` readable by code that did not build it — including your own tooling — instead of requiring it to decode a marker. It is also what makes a write to a view a compile error: a view has no `insert`. | Type | Meaning | | -------------------- | --------------------------------------------------- | | `InferRow` | The select shape; nullable columns are `\| null`. | | `InferInsertRow` | The insert shape; nullable columns may be omitted. | | `InferUpdateChanges` | The partial-update shape accepted by keyed updates. | | `SelectRowOf` | Pulls the select row back out of a `DB` entry. | | `InsertRowOf` | Likewise for inserts. | | `UpdateRowOf` | Likewise for updates. | | `WritableTable` | The names that accept writes — views excluded. | Each table definition also carries a Standard Schema-compatible `~standard` validator, so any library that speaks that interface can validate rows at runtime with your definitions. `planMigration(catalog, schema)` is the same diff `migrate()` runs, exposed as a pure function over the [published catalog](/docs/reference/extending.md#introspecting-the-catalog) — useful for previewing what a migration would do, or for building schema tooling of your own. > Raw `createTable` (see [Writes](/docs/sql/dml.md)) remains available when compile-time types aren't > needed. --- Minnow 0.1.1 · this page on the site: /docs/schema/ --- # The typed client > A schema-aware query builder with inferred row types, shipped as its own package. ```bash npm install @minnowdb/client ``` `@minnowdb/client` is optional. SQL is Minnow's contract — the engine runs every statement on its own — and this package is one consumer of the primitives the engine publishes. It ships separately for two reasons. It can move at its own pace without a version of the engine riding along; and building it only from [published primitives](/docs/reference/extending.md) is what proves those primitives are enough for anyone else to build a layer of their own. If the seam were incomplete, this package would be the first thing to break. ## What you get One schema declaration drives migrations, row types, and every query: ```ts import { MinnowDatabase, column, schema, table } from "@minnowdb/core"; import { IndexedDbBlockStore } from "@minnowdb/core/storage"; import { createMinnow, type InferDatabase } from "@minnowdb/client"; const appSchema = schema([ table("customers", { customer_id: column.number().unique(), name: column.string(), city: column.string().nullable(), }), ]); interface DB extends InferDatabase {} const database = new MinnowDatabase(await IndexedDbBlockStore.open({ name: "shop" })); await database.migrate(appSchema); const db = createMinnow(database, { schema: appSchema }); const rows = await db .selectFrom("customers") .select(["name", "city"]) .where("city", "=", "London") .execute(); // Array<{ name: string; city: string | null }> ``` The named `interface DB extends InferDatabase<...> {}` is the standard form: it keeps hovers, errors, and emitted declarations printing `Minnow` instead of the fully expanded schema, which matters as soon as you have more than a table or two. ## Where to go next - **[Reading data](/docs/client/queries.md)** — joins, expressions, aggregates, subqueries, and set operations. - **[Writing data](/docs/client/writes.md)** — typed inserts, updates, deletes, and `returning`. - **[Live queries](/docs/client/live.md)** — subscribe to a query and get a fresh result after every relevant commit. - **[Schema & migrations](/docs/schema.md)** — defining tables, constraints, and views. Part of `@minnowdb/core`, and usable without this package. - **[Extending Minnow](/docs/reference/extending.md)** — the primitives this package is built on, if you'd rather build your own layer. ## It wraps either half of the worker pair `createMinnow` takes a `MinnowDatabase` or a `MinnowDatabaseClient`, so moving the engine into a worker changes where you get the driver from and nothing about the queries you write. See [Workers & multi-tab](/docs/engine/workers.md). --- Minnow 0.1.1 · this page on the site: /docs/client/ --- # Queries > The select builder — joins, expressions, aggregates, subqueries, and set operations. This is the builder. For the SQL the engine accepts — and for reading with `db.query()` instead — see [Reading data](/docs/sql/select.md); the two produce the same plans. Every builder is immutable: each call returns a new builder, so partial queries are safe to share and extend. Nothing runs until you call `execute()`. ```ts const rows = await db .selectFrom("customers") .select(["customer_id", "name"]) .where("city", "=", "London") .orderBy("name") .limit(20) .execute(); // Array<{ customer_id: number; name: string }> ``` The row type follows the select list, not the table. Ask for two columns and you get two. ## Selecting `select()` takes a column, an array of columns, or a callback for expressions. `selectAll()` takes every column in scope. ```ts db.selectFrom("orders").select("total"); db.selectFrom("orders").select(["order_id", "total"]); db.selectFrom("orders").select("total as amount"); // renames in the row type too db.selectFrom("orders").selectAll(); ``` Repeated `select()` calls accumulate, so a query can be built in pieces. Mixing `select()` with `selectAll()` throws — the wildcard cannot carry named additions. Reach for the output type without running anything: ```ts const query = db.selectFrom("orders").select(["order_id", "total"]); type Row = typeof query.$inferRow; // { order_id: number; total: number } ``` ## Aliases and joins Alias a table with `"table as alias"`; every column reference then resolves against the aliases in scope. ```ts const rows = await db .selectFrom("orders as o") .innerJoin("customers as c", "o.customer_id", "c.customer_id") .select(["c.name", "o.total"]) .execute(); ``` `leftJoin` widens the joined table's columns with `null` in the row type, because that is what a left join actually returns. For anything beyond a single equality, pass a callback: ```ts db.selectFrom("orders as o").innerJoin("customers as c", (join) => join.onRef("o.customer_id", "=", "c.customer_id").on("c.city", "=", "London"), ); ``` `onRef` compares two columns; `on` compares a column to a value. A join whose alias is already in scope is a compile error naming the collision rather than a silent overwrite. ## Filtering The three-argument form covers most predicates: ```ts db.selectFrom("orders") .where("status", "=", "shipped") .where("total", ">", 100) // repeated where() is AND .where("note", "is", null) .where("status", "in", ["shipped", "delivered"]); ``` For anything else, pass a callback and use the expression builder: ```ts db.selectFrom("orders as o").where((eb) => eb.or([eb("o.total", ">", 500), eb.and([eb("o.status", "=", "vip"), eb("o.total", ">", 100)])]), ); ``` ### The expression builder `eb` is callable — `eb(column, operator, value)` — and carries these members: | Member | Produces | | ------------------------------------------- | ------------------------------------------------------------- | | `eb.and([...])` / `eb.or([...])` | Conditions folded left to right. | | `eb.not(condition)` | Negation. | | `eb.between(ref, lo, hi)` / `notBetween` | A range, desugared exactly as the parser does. | | `eb.exists(subquery)` | `EXISTS (...)`, capped at one row. | | `eb.ref("c.name")` | A column in value position, for column-to-column comparisons. | | `eb.val(42)` | A literal as an expression. | | `eb.neg(x)` | Arithmetic negation. | | `eb.case()` | `CASE WHEN ... THEN ... ELSE ... END`. | | `eb.match(columns, query)` | Full-text `MATCH ... AGAINST`. | | `eb.rowNumber()` / `rank()` / `denseRank()` | Ranking window functions; call `.over(...)`. | | `eb.selectFrom(table)` | A correlated or uncorrelated subquery. | | `eb.fn` | Aggregates and scalar functions, below. | Strings on the left of an operator are column references; strings on the right are values. To compare two columns in a `where`, wrap one in `eb.ref`. ## Aggregates and grouping `eb.fn` covers `count`, `countAll`, `sum`, `avg`, `min`, `max`, `round`, `coalesce`, `dateTrunc`, and `bm25`. Name each with `.as(alias)` — that alias becomes the row's key. ```ts const revenue = await db .selectFrom("orders as o") .innerJoin("customers as c", "o.customer_id", "c.customer_id") .select((eb) => ["c.city", eb.fn.sum("o.total").as("revenue"), eb.fn.countAll().as("orders")]) .groupBy("c.city") .having((eb) => eb(eb.fn.sum("o.total"), ">", 1000)) .orderBy("revenue", "desc") .execute(); // Array<{ city: string; revenue: number | null; orders: number }> ``` Numeric functions only accept columns whose type fits, so `eb.fn.sum("c.name")` is a compile error rather than a runtime surprise. `eb.fn.count(...).distinct()` gives `COUNT(DISTINCT ...)`. Window functions take `.over()`: ```ts db.selectFrom("orders as o").select((eb) => [ "o.order_id", eb.fn .sum("o.total") .over((over) => over.partitionBy("o.customer_id")) .as("running"), eb .rowNumber() .over((over) => over.orderBy("o.total", "desc")) .as("rank"), ]); ``` ## Subqueries, derived tables, and CTEs A builder used as a value becomes a subquery; `.as(alias)` makes it a derived table. ```ts // Subquery in a predicate: pass the builder itself const bigSpenders = db.selectFrom("orders as o").select("o.customer_id").where("o.total", ">", 500); db.selectFrom("customers as c").where("c.customer_id", "in", bigSpenders); // Correlated, through the expression builder db.selectFrom("customers as c").where((eb) => eb.exists( eb .selectFrom("orders as o") .select("o.order_id") .where("o.customer_id", "=", eb.ref("c.customer_id")), ), ); // Derived table const big = db.selectFrom("orders").select(["customer_id", "total"]).where("total", ">", 500); db.selectFrom(big.as("b")).select(["b.customer_id"]); ``` `db.with(name, factory)` declares a common table expression, returning a facade that knows about it: ```ts const scoped = db.with("recent", (qb) => qb.selectFrom("orders").select(["order_id", "customer_id"]).where("total", ">", 100), ); await scoped.selectFrom("recent").select(["order_id"]).execute(); ``` ## Set operations `union`, `unionAll`, `intersect`, and `except` combine two builders with the same row type: ```ts const a = db.selectFrom("customers").select(["name"]).where("city", "=", "London"); const b = db.selectFrom("customers").select(["name"]).where("city", "=", "Paris"); await a.union(b).orderBy("name").execute(); ``` ## Ordering, limits, and distinct ```ts db.selectFrom("orders") .select(["order_id", "total"]) .distinct() .orderBy("total", "desc") .limit(20) .offset(40); // offset requires limit ``` `orderBy` accepts a column in scope, an output alias from the select list, or an expression callback. ## Full-text search `.search(query)` filters to rows matching every term and orders by BM25 relevance — sugar for a `MATCH` predicate plus a `BM25` ordering, with the score riding as a hidden select item so your row shape is exactly what you asked for: ```ts await db .selectFrom("products") .select(["product_id", "name"]) .search("wireless keyboard") .limit(10) .execute(); ``` Select `eb.fn.bm25("*", query).as("score")` as well if you want the score value. Searching needs no index declaration — see [Full-text search](/docs/sql/full-text-search.md). ## Running a query | Call | Returns | | --------------------------- | ----------------------------------------- | | `execute()` | Every row. | | `executeTakeFirst()` | The first row, or `undefined`. | | `executeTakeFirstOrThrow()` | The first row, or throws `NoResultError`. | | `compile()` | The plan envelope, without running it. | | `live()` | A [live query](/docs/client/live.md). | ## Escaping to SQL The `sql` template tag runs a statement the builder cannot express, with values bound as parameters rather than interpolated: ```ts import { sql } from "@minnowdb/client"; const rows = await sql`SELECT * FROM orders WHERE total > ${threshold}`.execute(db); ``` --- Minnow 0.1.1 · this page on the site: /docs/client/queries/ --- # Mutations > Typed inserts, updates, deletes, and returning — and which engine path each takes. These are the builders. For the equivalent SQL statements — and for the batch APIs underneath — see [Writing data](/docs/sql/dml.md). The mutation builders mirror the select builder: immutable, nothing runs until `execute()`, and the row types come from your schema. ```ts await db.insertInto("customers").values({ customer_id: 1, name: "Ada", city: "London" }).execute(); ``` ## Insert `values()` takes one row or an array, and accepts the table's **insert** shape — nullable columns and columns the engine can fill may be omitted: ```ts await db .insertInto("orders") .values([ { order_id: 1, customer_id: 1, status: "new", total: 24.5 }, { order_id: 2, customer_id: 1, status: "new", total: 88.0 }, // note omitted, pads to null ]) .execute(); ``` Repeated `values()` calls accumulate, so a batch can be assembled in pieces. `orReplace()` routes the same rows through the upsert path, replacing any row with the same unique key instead of throwing: ```ts await db.insertInto("customers").values(row).orReplace().execute(); ``` Inserts go through the engine's batch API rather than the SQL parser — the same path [bulk loading](/docs/sql/dml.md#bulk-loading) uses — so a large `values([...])` is a columnar write, not thousands of parsed statements. ## Update and delete Both address rows through a predicate and compile to the same mutation statements SQL produces: ```ts await db.updateTable("orders").set({ status: "shipped" }).where("order_id", "=", 1).execute(); await db .updateTable("orders") .set((eb) => ({ total: eb("total", "*", 2) })) // expressions, not just literals .where("status", "=", "pending") .execute(); await db.deleteFrom("orders").where("status", "=", "cancelled").execute(); ``` `set()` also takes a single column and value — `set("status", "shipped")` — and accepts the table's **update** shape, which excludes the unique key. An `undefined` entry means "leave this column alone", so a spread-patch built from optional fields is safe: ```ts const patch: { status?: string; note?: string } = { status: "shipped" }; await db.updateTable("orders").set(patch).where("order_id", "=", 1).execute(); ``` Writes are rejected against a [view](/docs/schema.md#views) at compile time — a view has no insert shape, so `db.insertInto("active_customers")` does not typecheck. ## Getting rows back `returning()` projects named columns; `returningAll()` gives the whole row. Both change what `execute()` resolves to: ```ts const created = await db .insertInto("notes") .values({ body: "hello" }) .returningAll() .executeTakeFirstOrThrow(); // { id: 1, slug: "…", status: "draft", created: Date, body: "hello" } const [updated] = await db .updateTable("orders") .set({ status: "shipped" }) .where("order_id", "=", 1) .returning(["order_id", "status"]) .execute(); ``` This is how you read back values the engine generated — auto-increment keys and defaults — without a second query. Inserts echo the written values overlaid with generated columns; updates return post-update values; deletes return the rows as they were read. ## Running a mutation | Call | Returns | | --------------------------- | ------------------------------------------------------------------ | | `execute()` | An array: the `returning` rows, or one result object with a count. | | `executeTakeFirst()` | The first element, or `undefined`. | | `executeTakeFirstOrThrow()` | The first element, or throws `NoResultError`. | | `compile()` | The compiled statement, without running it. | Without `returning`, `execute()` resolves to a single-element array carrying the count — `numInsertedRows`, `numUpdatedRows`, or `numDeletedRows` — so `executeTakeFirstOrThrow()` is the idiomatic call either way. ## Atomicity One builder call is one commit. To make several land together, run them inside a [write scope](/docs/engine/transactions.md#atomic-writes) on the driver — every staged mutation publishes as one atomic commit, and a throw aborts the scope with nothing published. Constraints declared in your schema are enforced on every one of these paths: a [foreign key](/docs/schema.md#relations-and-row-conditions) with no matching parent row, or a `CHECK` a row fails, rejects the write rather than being applied. --- Minnow 0.1.1 · this page on the site: /docs/client/writes/ --- # Live queries > Subscribe to a query and get a fresh result after every commit that could change it. Any select builder becomes a live query with `.live()`. Subscribers get the current result immediately, then a fresh result after any commit that could have changed it. ```ts const live = db .selectFrom("orders") .select(["order_id", "status", "total"]) .where("status", "=", "pending") .live(); const subscription = await live.subscribe({ onChange(rows) { render(rows); // Array<{ order_id: number; status: string; total: number }> }, onError(error) { console.error(error); }, }); // later await subscription.close(); ``` The rows are typed exactly as `execute()` would return them, and `typeof live.$inferRow` gives you that type without running anything. ## As an async iterable `LiveQuery` is also an `AsyncIterable`, which suits a `for await` loop or any consumer that prefers pull to push: ```ts for await (const rows of live) { render(rows); if (done) break; // breaking closes the subscription } ``` Breaking out of the loop closes the subscription immediately rather than waiting for the next change — worth knowing if you are cancelling a view that may sit idle for a long time. ## What triggers a re-run A subscription records the tables its query depends on. A commit touching a dependency re-runs the query; a commit that cannot affect it does not. Identical results are suppressed, so a re-run that produces the same rows does not call `onChange`. Correctness never depends on a notification arriving. Each check reconciles against the current committed version in storage, so a missed or duplicated cross-tab hint cannot produce a stale result — it can only delay a fresh one. ## Across tabs and workers Live queries work the same whether the engine runs in this thread or in a worker. When several tabs share a database, commits in one tab reach subscriptions in another; see [Workers & multi-tab](/docs/engine/workers.md) for the coordination details and options. ## Handlers | Handler | When | | ---------------- | ----------------------------------------------------- | | `onChange(rows)` | The initial result, then every changed result. | | `onError(error)` | A re-execution failed. The subscription stays open. | | `onComplete()` | Once, when the subscription or its owning set closes. | --- Minnow 0.1.1 · this page on the site: /docs/client/live/ --- # The database API > MinnowDatabase, batch writes, and the options that shape a database. `MinnowDatabase` is the engine. It takes a [block store](/docs/storage.md) and exposes everything else — SQL, batch writes, the catalog, and maintenance. ```ts import { MinnowDatabase } from "@minnowdb/core"; import { IndexedDbBlockStore } from "@minnowdb/core/storage"; const db = new MinnowDatabase(await IndexedDbBlockStore.open({ name: "shop" }), { compression: "gzip", bufferPoolBytes: 64 * 1024 * 1024, }); ``` The same surface is available from the main thread when the engine [runs in a worker](/docs/engine/workers.md) — `MinnowDatabaseClient` mirrors it call for call. ## Catalog ```ts await db.createTable({ name: "orders", uniqueKey: "order_id", columns: [ { name: "order_id", type: "number" }, { name: "total", type: "number" }, { name: "note", type: "string", nullable: true }, ], }); await db.listTables(); ``` `createTable` is the programmatic form of [`CREATE TABLE`](/docs/sql/ddl.md) and the only place column defaults can be declared. ## Bulk writes Parsing an `INSERT` per row is the wrong shape for loading data. The batch APIs take rows directly: ```ts await db.insertBatch("orders", [ { order_id: 1, total: 24.5, note: null }, { order_id: 2, total: 88.0, note: "gift wrap" }, ]); ``` Or columns, when you already hold them that way — which skips the pivot the engine would otherwise do: ```ts await db.insertBatch("orders", { columns: { order_id: [1, 2], total: [24.5, 88.0], note: [null, "gift wrap"], }, }); ``` The full set: | Call | Effect | | --------------------------------------- | ---------------------------------------------------- | | `insertBatch(table, input)` | Append rows. A duplicate key throws. | | `upsertBatch(table, input)` | Insert, replacing any row with the same key. | | `updateBatch(table, { keys, changes })` | Change named columns on rows addressed by key. | | `deleteBatch(table, { keys })` | Remove rows by key. | | `insert` / `upsert` / `update` | Single-row convenience wrappers over the same paths. | Each returns what it did — `rowCount`, `blockCount`, `storedBytes`, and the published `version` — which is enough to drive a progress bar over a large load without a second query. One batch is one commit. To make several land together, wrap them in a [write scope](/docs/engine/transactions.md). ### Buffered writing For a stream of small writes — telemetry, edits as a user types — a buffered writer coalesces them into blocks worth committing: ```ts const writer = db.bufferedWriter("events", { maxRows: 5_000, maxDelayMs: 250 }); writer.add({ event_id: id, kind: "click", at: new Date() }); await writer.flush(); ``` `attachLifecycleFlush` wires a writer's flush to page hide and freeze events, so a buffer does not follow the tab into the grave. ## Options | Option | Default | What it does | | ------------------ | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `compression` | `"gzip"` | Block codec. `"raw"` ingests about twice as fast; `gzip` halves stored bytes and reads _faster_ cold, because reading half the bytes out of IndexedDB more than pays for decompressing them. | | `rowsPerBlock` | `65536` | The scan's row group and the buffer pool's residency unit. Measured flat above ~16k; small blocks cost up to 66% on top-N. | | `bufferPoolBytes` | 64 MiB | Retained decoded blocks and their vectorized forms. `0` disables it. Every entry is keyed by an immutable identity, so a cached entry can never be stale. | | `ftsAutoIndexRows` | `4096` | Rows above which a `MATCH` on an unindexed append-only column schedules a background index build. | | `maxCommitRetries` | `8` | How many times a losing writer rebases and retries before giving up. | | `autoCompact` | `true` | Whether small segments are merged in the background. | ## Errors Errors are classes, so they can be caught by kind rather than by matching a message: ```ts import { SqlCompileError, UniqueConstraintError, WriteConflictError } from "@minnowdb/core"; ``` `SqlCompileError` carries the `offset` and `length` of the offending span, which is what the devtools editor underlines. `WriteConflictError` means another writer committed first; the engine retries these itself up to `maxCommitRetries` before surfacing one. ## Closing ```ts db.close(); ``` Closes the underlying store. Any in-flight query rejects rather than hanging. --- Minnow 0.1.1 · this page on the site: /docs/engine/ --- # Transactions and snapshots > Making several writes atomic, holding one version across several reads, and what happens when two writers collide. ## Atomic writes A write scope stages any number of mutations across any number of tables and publishes them as one commit: ```ts const { result, version } = await db.write(async (tx) => { await tx.execute("UPDATE stock SET on_hand = on_hand - ? WHERE sku = ?", [qty, sku]); await tx.insertBatch("shipments", [{ sku, qty, shipped_at: new Date() }]); return sku; }); ``` Everything inside becomes visible at once, including to other tabs: nobody observes the stock decremented without the shipment. If the callback throws, nothing is published. Scopes are a callback rather than `begin()` / `commit()` calls on purpose. A transaction a caller holds is a transaction a caller can drop — through a thrown error, a closed tab, a forgotten `await` — and a dropped transaction pins storage that background collection then cannot reclaim. A scope ends when its callback does, however it ends. This is also why `BEGIN` as SQL text is [rejected](/docs/sql/feature-matrix.md). ### A failed scope stays failed A statement that fails _after_ registering part of its work — key membership, full-text deltas, staged blocks — cannot be undone statement by statement. The scope is poisoned: even if your code catches the error and carries on, the commit refuses rather than publishing a fragment. A statement that fails validation _before_ registering anything — updating a key that does not exist, say — leaves the scope clean and usable. ## Stable reads Every single query already executes against one version. A snapshot scope extends that to several: ```ts const report = await db.snapshot(async (session) => { const orders = await session.query("SELECT COUNT(*) AS n FROM orders"); const items = await session.query("SELECT COUNT(*) AS n FROM order_items"); return { orders: orders.rows[0].n, items: items.rows[0].n }; }); ``` Both queries see the same version, so the counts are consistent with each other however many commits land while the scope is open. The scope holds a lease on that version so background collection cannot reclaim it underneath, and releases it when the callback returns. Hold one for as long as you need consistency and no longer: a scope kept open across a user's whole session pins every version since it started. ## Freshness Outside a snapshot scope, every query observes the latest committed state — including commits from another tab. Stale reads are not something you can accidentally get; they are unrepresentable. That is a stronger guarantee than it sounds, and it is why cross-tab consistency does not depend on `BroadcastChannel` or Web Locks. A reader checks the current version in the same storage transaction it reads through. ## Conflicts Readers never block writers and writers never block readers. Two writers that touch overlapping data do conflict, and the loser rebases onto the winner's version and retries — up to `maxCommitRetries`, eight by default. Past that you get a `WriteConflictError` to handle yourself. ```ts import { WriteConflictError } from "@minnowdb/core"; try { await db.write(async (tx) => { /* … */ }); } catch (error) { if (error instanceof WriteConflictError) { // Another writer won repeatedly. Re-read and decide what the write should now be. } } ``` Retrying is safe because a scope's callback runs again from the top against the new version, so a read-modify-write inside it re-reads. ## Durability Durability ends at a committed IndexedDB transaction. Nothing depends on a page-close handler firing, because they do not reliably fire — a tab that vanishes loses only writes that had not committed. The IndexedDB store defaults to `relaxed` durability, which lets the browser batch flushes to disk. For data that must survive a power loss rather than a tab closing, open the store with `durability: "strict"` and pay the flush per commit. --- Minnow 0.1.1 · this page on the site: /docs/engine/transactions/ --- # Workers & multi-tab > The shipped worker entry, the main-thread client, and bundler setups. Minnow ships both sides of the worker setup: a ready-made worker entry (`@minnowdb/core/worker`) and a main-thread client (`@minnowdb/core/client`). Your app contributes one line — the `new Worker(...)` call — because that's the only line your bundler needs to see. ## Quick start ```ts import { MinnowDatabaseClient } from "@minnowdb/core/client"; import { column, schema, table } from "@minnowdb/core"; import { createMinnow, type InferDatabase } from "@minnowdb/client"; const people = table("people", { name: column.string().unique(), score: column.number(), }); const appSchema = schema([people]); interface DB extends InferDatabase {} const client = new MinnowDatabaseClient( new Worker(new URL("@minnowdb/core/worker", import.meta.url), { type: "module" }), { store: { kind: "indexeddb", name: "app-db" } }, ); await client.migrate(appSchema); // The typed facade wraps the client exactly as it wraps an in-worker database. const db = createMinnow(client, { schema: appSchema }); await db .insertInto("people") .values([ { name: "Ada", score: 10 }, { name: "Grace", score: 20 }, ]) .execute(); const rows = await db .selectFrom("people") .select(["name", "score"]) .orderBy("score", "desc") .execute(); // Array<{ name: string; score: number }> ``` - The client sends its configuration to the worker on startup, so the stock entry needs none of its own. - The channel is ordered, so you can issue calls immediately. `await client.ready()` exists to surface store-open failures eagerly. - Storage access, decoding, planning, and execution all run in the worker; the main thread holds only the proxy. - The raw layer is there too: `client.query(sql)`, `client.insertBatch(...)`, `client.createTable(...)` — the full database API. ## Why you construct the Worker A worker needs a script URL at runtime, and bundlers only rewrite `new Worker(new URL("…", import.meta.url), { type: "module" })` correctly when that exact expression appears in _your_ code — buried inside a library, it breaks differently under every bundler. Keeping it in your code also keeps lifecycle ownership honest (you decide when the worker starts and stops) and satisfies `worker-src` content-security policies from your own origin. ## What changes across the boundary - **Everything is async.** Members that are synchronous in the worker return promises on the client, and getters become methods: `writer.stats()`. - **`snapshot()` pins its version in the worker** for the callback's lifetime — session queries cross the channel pinned to that version, so a scope observes one consistent state even while other tabs commit. - **`migrate()` takes the same schema DSL** — it's serialized over the wire automatically. - **Typed errors survive the trip.** `instanceof UniqueConstraintError` works on the client, with its fields; stack traces point into the worker. - **Functions can't cross.** Construction options like `now`, `createId`, or a custom store need a custom entry (below). The typed facade doesn't care about any of this: compiled plans cross the channel by structured clone, so `selectFrom`, typed [writes](/docs/sql/dml.md), and `.live()` behave identically. ## Buffered writers and live queries Stateful handles proxy transparently — the writer's age timer runs on the worker's clock, and live-query callbacks arrive as events: ```ts const writer = client.bufferedWriter("people", { maxRows: 500, onError: (error) => console.error("background flush failed", error), }); await writer.add({ name: "Edsger", score: 30 }); await writer.close(); const live = client.liveQueries({ channelName: "app-db-commits" }); const subscription = await live.subscribe("SELECT name, score FROM people ORDER BY score DESC", { onChange: (result) => render(result.rows), }); // … later await subscription.close(); await live.close(); await client.close({ terminateWorker: true }); ``` ## Bundler setups **Vite and webpack 5** understand the quick-start pattern as written — they resolve the package subpath and emit a separate worker chunk. Nothing else needed. **esbuild** (and Parcel 2 by default) doesn't rewrite `new URL` worker expressions. Bundle the worker entry separately and point at the output: ```ts // worker.ts — your one-line worker entry, bundled separately: import "@minnowdb/core/worker"; // esbuild app.ts worker.ts --bundle --format=esm --outdir=dist --splitting // app.ts: const client = new MinnowDatabaseClient( new Worker(new URL("./worker.js", import.meta.url), { type: "module" }), ); ``` **No bundler** — module workers can't resolve bare specifiers, so use full URLs from a CDN or vendored files: ```ts // db-worker.js — served from your origin: import "https://cdn.example.com/@minnowdb/core/dist/worker.js"; // main page: import { MinnowDatabaseClient } from "https://cdn.example.com/@minnowdb/core/dist/client.js"; const client = new MinnowDatabaseClient( new Worker(new URL("./db-worker.js", import.meta.url), { type: "module" }), ); ``` ## Custom worker entries The stock entry covers everything a message can carry: the store descriptor (`indexeddb` or `memory`) plus `compression`, `rowsPerBlock`, `maxCommitRetries`, `spillOwnerLeaseMs`, and `bufferPoolBytes`. For anything a message can't carry — a custom `BlockStore`, deterministic `now`/`createId` — write your own entry: ```ts // my-worker.ts import { MinnowDatabase, exposeDatabase } from "@minnowdb/core"; import { IndexedDbBlockStore } from "@minnowdb/core/storage"; const store = await IndexedDbBlockStore.open({ name: "app-db", durability: "strict" }); exposeDatabase(new MinnowDatabase(store, { compression: "gzip" }), self, { onDispose: () => store.close(), }); ``` `exposeDatabase()` speaks the same protocol as the stock entry, so the main-thread code doesn't change. Underneath both sits `@minnowdb/core/worker-protocol`: versioned, structured-clone-safe RPC where each handle answers a fixed list of methods — never arbitrary property access. > The worker changes where work runs, not the rules: IndexedDB stays authoritative, and > durability still ends at a committed transaction. See [Architecture](/docs/reference/architecture.md). --- Minnow 0.1.1 · this page on the site: /docs/engine/workers/ --- # Memory > The execution budget, spilling, and the buffer pool. A browser tab does not get to use all the memory it likes. Two separate mechanisms keep a database inside a bound you choose: a per-execution budget with spilling, and a byte-bounded cache of decoded blocks. ## The execution budget Set a ceiling for what one statement may allocate: ```ts await db.query(sql, { executionMemoryBudgetBytes: 32 * 1024 * 1024 }); ``` The budget covers the modeled vectors, row-index arrays, grouping and result payloads, and ordering buffers — the parts the engine allocates and can therefore account for. It does not cover JavaScript container overhead, the lifetime of the result you are handed, or the browser's own allocator overhead. When an operator would exceed the budget, it **spills** to storage and continues. A sort, a hash join, or a grouping over more data than fits writes runs to temporary pages and merges them. The query gets slower; it does not fail. If a statement cannot proceed even with spilling, it throws `QueryMemoryBudgetError` rather than letting the tab die. ```ts import { QueryMemoryBudgetError } from "@minnowdb/core"; ``` ## Measuring what a query used ```ts await db.query(sql, { memoize: false, onStats: (stats) => { console.log(stats); }, }); ``` The engine can report its own peak because it reserves before it allocates — a measurement neither the storage layer nor a caller could take from outside. ## The buffer pool Separately from execution, decoded blocks are cached so a warm scan does not re-read and re-decompress storage: ```ts const db = new MinnowDatabase(store, { bufferPoolBytes: 64 * 1024 * 1024 }); db.bufferPoolStats(); // what is resident right now ``` It holds decoded physical blocks, their vectorized per-block column forms, zone-map descriptions, and derived-subquery results. Every entry is keyed by an immutable identity — a block id, or an exact visible-segment fingerprint — so a cached entry can never serve stale data. Superseded entries simply stop being referenced and age out of the byte-bounded LRU. `0` disables it, which is the right setting for a one-shot import that will never re-read what it writes. ## Spill cleanup Spilled pages are owned by a lease that is renewed while the query runs, so a tab that disappears mid-query leaves pages that a later session can identify as abandoned and reclaim: ```ts await db.cleanupQuerySpill(); ``` Worth calling at startup in a long-lived application. It is bounded work and safe to run concurrently with queries. ## Choosing numbers The defaults — 64 MiB of buffer pool, no explicit execution budget — suit an application working over tens of megabytes of data. Lower the pool on a memory-constrained device or when many databases are open at once. Set an execution budget when a user can write arbitrary queries, which is exactly the case the [playground](/playground) is in. --- Minnow 0.1.1 · this page on the site: /docs/engine/memory/ --- # Storage adapters > The block store contract, and choosing between IndexedDB and memory. A database is an engine plus a store. The engine decides what to write; the store decides where it goes. Two adapters ship today, both implementing the same `BlockStore` contract, so swapping one for the other changes nothing else about your code. | Adapter | Import | Survives a reload | Use it for | | ------------------------------------------------ | ------------------------ | ----------------- | ---------------------------------------- | | [`IndexedDbBlockStore`](/docs/storage/indexeddb.md) | `@minnowdb/core/storage` | Yes | Applications. | | [`MemoryBlockStore`](/docs/storage/memory.md) | `@minnowdb/core/storage` | No | Tests, scratch work, throwaway analysis. | OPFS is a likely third: it is a better fit for large sequential writes than IndexedDB, at the cost of needing cross-origin isolation to use its fast synchronous handles. ## What a store holds Not rows. The engine hands the store immutable, compressed, self-describing **blocks** — one column's values for one row group — plus the records that say which blocks are live: - **Blocks** — the data, keyed by an immutable id. - **Manifests** — which block ids are live at each version. Publishing a manifest is what makes a commit visible. - **Segments** — which blocks belong to which table, and which row ids they cover. - **Transactions** — the commit each segment belongs to, which is how visibility resolves. - **Catalog** — tables, columns, counters, unique-key membership, full-text index state. - **Leases, temp pages, and job records** — reader pins, query spill, and the cursors that let compaction and collection resume. Because published blocks are immutable and a version is just a set of block ids, a reader can hold a version open while writers keep committing. That is the whole concurrency story, and it is a property of this layout rather than of any locking. ## Writing your own `BlockStore` is a public interface. Implementing it against another substrate — OPFS, a remote object store, an encrypted wrapper — gives you a working database with no engine changes. Several methods are optional (`getCatalogProbe`, `getQueryCatalogState`, `beginTransaction`, `stageTransactionArtifacts`). They exist so an adapter that can do something atomically may say so; callers fall back to the individual calls when they are absent. Be honest about which you implement — the engine trusts a present method to be atomic. `FaultInjectingBlockStore` from `@minnowdb/core/testing` wraps any store and fails at named points, which is how the engine's own crash-recovery behaviour is tested: ```ts import { FaultInjectingBlockStore } from "@minnowdb/core/testing"; const store = new FaultInjectingBlockStore(new MemoryBlockStore(), (point) => point === "beforeManifestCommit" ? new Error("boom") : undefined, ); ``` ## Moving data between stores A [snapshot](/docs/storage/snapshots.md) copies one committed version out as a single portable file and loads it into any store — memory to IndexedDB, one browser to another, or a build script to a published asset. --- Minnow 0.1.1 · this page on the site: /docs/storage/ --- # IndexedDB > The durable adapter — options, durability, quota, and what it stores. ```ts import { IndexedDbBlockStore } from "@minnowdb/core/storage"; const store = await IndexedDbBlockStore.open({ name: "shop", durability: "relaxed", }); ``` | Option | Default | Effect | | ------------ | ----------- | --------------------------------------------------------------------------------- | | `name` | — | The IndexedDB database name. Two stores with the same name are the same database. | | `durability` | `"relaxed"` | `"strict"` flushes to disk per commit. | | `indexedDB` | the global | An `IDBFactory` to use instead, for tests. | ## Durability `relaxed` lets the browser batch flushes to disk. A commit is still atomic and still ordered — a tab that closes, crashes, or is killed loses nothing committed — but a power loss can lose the most recent commits, because the operating system had not written them yet. `strict` pays a real flush per commit. It is measurably slower on write-heavy work, and it is the right choice when data must survive the machine losing power rather than the tab going away. ## Quota Browsers give an origin a share of free disk, not a fixed number, and evict from origins the user has not visited when space runs low. ```ts const { quota, usage } = await navigator.storage.estimate(); await navigator.storage.persist(); // ask to be exempt from eviction ``` `persist()` prompts or silently grants depending on the browser and how engaged the user is with the site. Ask before writing a lot, and handle a refusal by writing less rather than by failing. `getLogicalStorageBytes()` reports what this database occupies, which is the number to show a user and the one to watch before a bulk load: ```ts await store.getLogicalStorageBytes(); ``` ## What it creates One IndexedDB database with nine object stores: `blocks`, `manifests`, `segments`, `transactions`, `catalog`, `leases`, `temp`, `gc`, and `statistics`. Block payloads are stored as `Uint8Array` values keyed by block id; everything else is small structured records. Manifests are stored as a checkpoint every 32 commits with deltas in between, so publishing a commit writes work proportional to the blocks that changed rather than to the database's total size. Reads resolve a version by walking back to the nearest checkpoint. ## Multiple tabs Several tabs may open the same database at once. Readers never block writers; competing writers conflict, rebase, and retry. No coordination channel is involved — correctness comes from the storage transactions themselves, so it holds even when `BroadcastChannel` is unavailable or a message is lost. One caveat worth knowing: a browser may throttle or suspend a background tab's IndexedDB activity. A long compaction in a hidden tab can simply stop making progress until it is foregrounded, which is why [maintenance](/docs/storage/maintenance.md) is stepped and resumable rather than one long operation. --- Minnow 0.1.1 · this page on the site: /docs/storage/indexeddb/ --- # Memory > The in-process adapter, for tests and throwaway work. ```ts import { MemoryBlockStore } from "@minnowdb/core/storage"; import { MinnowDatabase } from "@minnowdb/core"; const db = new MinnowDatabase(new MemoryBlockStore()); ``` No options, nothing to open, nothing to clean up. It implements the same `BlockStore` contract as the IndexedDB adapter, including atomic commits, snapshot reads, leases, and conflict detection — so a test against it exercises the same engine paths an application uses, not a simplified stand-in. ## What it is for **Tests.** A fresh database per test with no cleanup and no shared state between them. ```ts function freshDatabase(): MinnowDatabase { return new MinnowDatabase(new MemoryBlockStore()); } ``` **Ephemeral analysis.** A file a user dropped in, queried, and will never look at again does not need to touch their disk or their quota. **Building data to publish.** A [snapshot](/docs/storage/snapshots.md) exported from a memory store is how a prepared database becomes a file — a build script loads rows in memory and writes one portable artifact. ## What it costs Everything lives in JavaScript memory: block payloads stay compressed, but they stay resident. Budget roughly what the same data would occupy in IndexedDB, plus the catalog. Nothing survives a reload, and nothing is shared between tabs — two tabs each get their own database. Where the IndexedDB adapter's cross-tab behaviour needs testing, that has to be a browser test against the real store. --- Minnow 0.1.1 · this page on the site: /docs/storage/memory/ --- # Snapshots > Copy one committed version out as a portable file, and load it back into any store. A snapshot is one committed version of a database, copied out as a single byte array and loadable into any block store. It is how you back a database up, seed a test fixture from real data, ship a prepared database as a static asset, or hand a colleague exactly what you were looking at. ## From the database The database copies itself out as the finished file, and loads one back: ```ts const bytes = await db.exportSnapshot(); const restored = new MinnowDatabase(new MemoryBlockStore()); await restored.importSnapshot(bytes); ``` Both take an `onProgress` callback, and both work the same way through the [worker client](/docs/engine/workers.md) — there the file crosses the channel in slices, so the main thread copies a few megabytes at a time instead of stalling on one clone of the whole database. That is also what the devtools' **Download database** button does; see [the devtools](/docs/devtools.md#downloading-the-database). Everything below is the layer underneath, for when you want the records rather than the file, or a store that has no database in front of it. ```ts import { encodeSnapshot, decodeSnapshot } from "@minnowdb/core/storage"; const bytes = await encodeSnapshot(await store.exportSnapshot()); ``` ## Loading Into a fresh in-memory store: ```ts import { MemoryBlockStore } from "@minnowdb/core/storage"; const db = new MinnowDatabase(MemoryBlockStore.fromSnapshot(await decodeSnapshot(bytes))); ``` Or into IndexedDB, where it is durable and available on the next visit: ```ts const store = await IndexedDbBlockStore.open({ name: "seeded" }); await store.importSnapshot(await decodeSnapshot(bytes), { onProgress: ({ writtenBytes, totalBytes }) => { setProgress(writtenBytes / totalBytes); }, }); ``` The target store must be empty. Loading into a database that already holds one throws rather than merging two histories. `MemoryBlockStore.fromSnapshot` builds a store around a snapshot instead, which is the same thing for a store that was never used. ## What it carries Everything needed to read the data and to keep writing correctly afterwards: - The block bytes the current manifest points at — verbatim, already compressed. - One checkpoint manifest, the table catalog, the live segments, and the committed transactions that segment visibility resolves through. - The row-id and auto-increment counters, so later writes continue past the high-water mark instead of colliding with rows that are there but hidden. - Unique-key membership, so an insert that duplicates an existing key still conflicts. - Full-text bases, when they already cover the exported version. And what it deliberately drops: leases, query spill pages, garbage-collection and compaction job records, in-flight transactions, and **version history**. A database with ten thousand commits behind it loads as one clean version. Superseded blocks a compaction left behind are not copied, so a snapshot is usually smaller than the database it came from. A full-text index that does not already cover the exported version is marked for rebuild rather than shipped stale. The index is a pruning accelerator that the scan re-verifies, so that costs a rebuild, never a wrong answer. ## The container A magic number, a format version, a gzipped JSON header, and the block payloads laid end to end. Blocks are already self-describing and doubly CRC-checked, so loading authenticates every block header without decompressing any payload — a corrupt file fails at load rather than mid-query. Read the header alone when you only need to know what a file is: ```ts import { readSnapshotSummary } from "@minnowdb/core/storage"; const summary = await readSnapshotSummary(bytes); // { formatVersion, version, createdAt, tableCount, blockCount, payloadBytes, byteLength } ``` Cheap enough to run against a large file before deciding whether to load it. ## Exporting safely `MemoryBlockStore#exportSnapshot` runs on the store's commit queue, so it always sees one consistent version. `IndexedDbBlockStore#exportSnapshot` reads across several transactions, so a concurrent commit could move the version underneath it. Hold a backup lease across the call when another writer is possible; a build script that is the only writer in its process does not need one. `MinnowDatabase#exportSnapshot` delegates to whichever of those the database was built on, so it inherits the same rule. A store that implements neither method says so rather than failing as a missing property. > **Warning** > > The snapshot format is versioned and validated, but like the block format it carries no > compatibility promise while the library is version zero. Treat a snapshot as a copy of a database > you can rebuild, not as an archival format. --- Minnow 0.1.1 · this page on the site: /docs/storage/snapshots/ --- # Compaction and collection > Merging small segments and reclaiming superseded blocks, without blocking anything. Writes append. A table written in many small batches ends up as many small segments, and every update or delete leaves the blocks it superseded on disk until something reclaims them. Two background jobs handle both, and both are stepped and resumable — a browser tab can be backgrounded, throttled, or closed mid-job. ## Compaction Merges small segments into larger ones, which is what keeps a scan from paying per-segment overhead after a long run of small writes. ```ts await db.compactTable("orders"); ``` It runs automatically by default. To drive it yourself — a slice per idle callback, so it never holds the main thread: ```ts const db = new MinnowDatabase(store, { autoCompact: false }); requestIdleCallback(async function step() { const progress = await db.compactTableStep("orders", { maxBlocks: 64 }); if (progress.result === null) requestIdleCallback(step); }); ``` Each step processes at most `maxBlocks` output blocks and checkpoints; `progress.result` stays `null` until the job publishes. ### Deletes and updates before compaction Compaction is not what makes a mutated table readable at speed. A query applies the table's deltas over its appended data directly, so a table that has been deleted from or updated answers from the same scan every other table gets, plus the cost of the deltas themselves. What compaction adds is returning the table to a plain append — the deltas stop being re-read on every query, and the storage they occupy is freed. One limit worth knowing: merging a keyed table plans the merge in memory, and a large table can need more than the default 32 MiB budget. Raise it with `memoryBudgetBytes` when a compaction reports `CompactionMemoryBudgetError`: ```ts await db.compactTable("orders", { memoryBudgetBytes: 256 * 1024 * 1024 }); ``` Automatic compaction backs off a table whose attempt fails, rather than retrying it on every query. Jobs are records in the store, so `listCompactionJobs()` finds one a previous session left behind and `resumeCompactionJob(jobId)` picks it up. `cancelCompactionJob(jobId)` stops one cleanly — compaction is visible-data-neutral by construction, so cancelling it can never lose data. ## Garbage collection Reclaims blocks no live version references any more: ```ts await db.collectGarbage(); ``` A block is only collectable when no manifest, no open reader lease, and no non-terminal job still roots it. That is what makes it safe to run while a report is being read: an open [snapshot scope](/docs/engine/transactions.md#stable-reads) pins its version, and collection skips everything that version needs. The same stepped shape applies: ```ts await db.collectGarbageStep({ jobId, maxItems: 128 }); ``` ## Scheduling Neither job needs to run on a timer. Reasonable triggers: - After a bulk load, compact the tables it touched. - On an idle callback, take one step of whatever is outstanding. - At startup, resume jobs a previous session left and call [`cleanupQuerySpill()`](/docs/engine/memory.md#spill-cleanup). Leaving them undone costs storage and scan speed. It never costs correctness — a database that is never compacted or collected still answers every query correctly, just over more blocks and more bytes than it needs. --- Minnow 0.1.1 · this page on the site: /docs/storage/maintenance/ --- # API reference > Every public export of @minnowdb/core and @minnowdb/client, by entry point. Everything ships in one package, `@minnowdb/core`. The root export is the everyday surface; the subpaths expose the layers it's built from. | Entry point | Contents | | ---------------------------------------- | ----------------------------------------------------------- | | `@minnowdb/core` | Schema DSL, typed facade and builders, engine, live queries | | `@minnowdb/core/client` | Main-thread worker client | | `@minnowdb/core/worker` | Ready-made worker entry (side-effect import) | | `@minnowdb/core/storage` | Block stores: IndexedDB, in-memory, the storage interface | | `@minnowdb/core/transactions` | Snapshots, transactions, recovery (lower level) | | `@minnowdb/core/block-format` | Binary block containers and codecs (lower level) | | `@minnowdb/core/worker-protocol` | Versioned RPC frames (lower level) | | `@minnowdb/core/testing` | Deterministic fault injection | | `@minnowdb/core/sql-feature-matrix.json` | The checked-in SQL conformance matrix | --- ## Schema DSL From `@minnowdb/core`. See [Schema & migrations](/docs/schema.md). | Export | Description | | -------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `table(name, columns, { checks })` | Defines a table from column builders. `checks` declares row conditions (`{ name, sql }`) enforced on every write. The result carries inferred row types and a Standard Schema `~standard` validator. | | `column.boolean() / number() / string() / datetime()` | Column builders for the four logical types. | | `column.enum([...])` | A string column restricted to a closed value set, typed as the literal union and validated on every write. Migrations may add values, never remove. | | `.unique() / .nullable() / .renamedFrom()` | Column modifiers: unique key, NULL widening, stable-ID rename. | | `.references(table, column, { onDelete })` | Declares a FOREIGN KEY onto another table's unique key, created as a real constraint. `onDelete` is `"restrict"` (default), `"cascade"`, or `"set null"`. | | `.autoIncrement() / .default(value \| fn)` | Generated values: a persistent cross-tab counter for number unique keys; literal / `"now"` defaults filled engine-side on every write path; function defaults called by the typed facade just before the batch is sent. | | `schema(tables, { views })` | Bundles tables and views into a `SchemaDefinition` for `migrate()` and the facade. | | `view(name, { sql, columns })` | Declares a read-only view. The engine verifies the declared columns against the query's inferred output at migration time. | | `typedTable(database, tableDef)` | Thin schema-typed handle over the batch APIs. | | `planMigration(catalog, definition)` | Computes the metadata-only `MigrationPlan` that `migrate()` executes. | | `InferRow / InferInsertRow / InferUpdateChanges` | Per-table select / insert / keyed-update shapes. | | `Generated` | Marks engine-filled columns in hand-declared `DB` interfaces so inserts keep the omission; `InferDatabase` applies it automatically. | | `SchemaDefinition, TableSchema, AnyTable, ColumnBuilder, SchemaColumnType, MigrationStep, MigrationPlan` | Supporting types. | ### Catalog introspection From `@minnowdb/core`. See [Extending Minnow](/docs/reference/extending.md). | Export | Description | | --------------------------------------------------- | -------------------------------------------------------------------------------------------------- | | `database.introspect()` | The published catalog: stable column IDs, key identity, foreign keys, checks, triggers, and views. | | `Catalog, CatalogTable, CatalogColumn, CatalogView` | Its types. | | `CatalogForeignKey, CatalogCheck, CatalogTrigger` | Constraint and trigger entries. | | `toCatalog(records)` | Projects storage table records into a `Catalog`; sorted by name so a diff is stable. | ## Typed facade From `@minnowdb/client`, an optional package installed separately. See [Reading data](/docs/client/queries.md) and [Writing data](/docs/client/writes.md). | Export | Description | | ------------------------------------------------------ | --------------------------------------------------------------------------------------------------------- | | `InferDatabase` | Maps a schema to `DB`: `select`/`insert`/`update` per table, `select` only per view. | | `FromRow` | Builds those three shapes from one hand-written row type, reading `Generated`. | | `SelectRowOf` / `InsertRowOf` / `UpdateRowOf` | Pull one shape back out of a `DB` entry. | | `WritableTable` | The `DB` names that accept writes; views are excluded structurally, so writing to one is a compile error. | | `TableShape` / `ViewShape` | The entry types `InferDatabase` produces. | | Export | Description | | ------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `createMinnow(driver, { schema })` | Builds the facade. The standard form passes a named `interface DB extends InferDatabase {}` so tooling prints `Minnow`; omitting the type argument infers `DB` from the schema value instead. | | `class Minnow` | The facade itself; wraps a `MinnowDatabase` or `MinnowDatabaseClient` (any `DslDriver`). | | `.selectFrom(table \| derived)` | Starts a `SelectQueryBuilder`; accepts `"people"`, `"people as p"`, or an aliased subquery. | | `.insertInto / .updateTable / .deleteFrom` | Start the mutation builders. | | `.with(name, () => query)` | Adds a CTE usable as a from/join source in the following query. | | `.search(query, { tables?, limit? })` | Document search across tables (all schema tables by default): per-table MATCH + BM25 scans merged into one relevance-ranked `{ table, row, score }` list. | | `.close()` | Closes the shared live set (and any driver-owned resources the facade created). | | `.driver` | The `MinnowDatabase` or `MinnowDatabaseClient` behind the facade, for tools handed only the facade. Application code should keep its own reference instead. | | `MinnowOptions` | Facade options: the schema, plus `live: { channelName?, pollIntervalMs? }` defaults for `.live()`. | | `DslDriver, DriverLiveSet, DslLiveOptions` | The driver contract, implemented by both the database and the worker client. | ### SelectQueryBuilder `execute()` resolves to typed rows; the row type accretes through the chain. | Method | Description | | --------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `innerJoin / leftJoin(table, lhs, rhs)` | Joins; callback form gets a `JoinBuilder` with `on(...)` / `onRef(...)`. Left-joined columns widen to `\| null`. | | `where(lhs, op, rhs)` / `where((eb) => ...)` | Filters; string LHS is a column reference, RHS is a value. | | `having(...)` | Post-aggregation filter, same forms as `where`. | | `groupBy(cols)` / `orderBy(col \| expr, dir?)` | Grouping and ordering; ORDER BY takes a selected column, an output alias, or any expression (desugared to a hidden select item — wildcard selects order by columns only). | | `limit(n)` / `offset(n)` / `distinct()` | Row-set modifiers. | | `select([...])` / `select((eb) => [...])` / `selectAll()` | Projections; string and expression selections may be mixed across repeated calls. | | `search(query, { columns? })` | Filters by `eb.match` and orders by BM25 relevance descending; the row shape is untouched (select `fn.bm25` yourself to read the score). Columns default to `"*"`. | | `union / unionAll / intersect / except(other)` | Set operations; member row types must match. | | `as(alias)` | Turns the query into a derived table for `selectFrom` / joins. | | `compile()` | The typed plan envelope — the same object `.execute()` runs and `.live()` subscribes. | | `execute / executeTakeFirst / executeTakeFirstOrThrow()` | Run and return `TRow[]`, the first row or `undefined`, or throw `NoResultError`. | | `live()` | A `LiveQuery` over this query. See [Live queries](/docs/sql.md). | ### Mutation builders | Method | Description | | ---------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------- | | `InsertQueryBuilder.values(row \| rows)` | Rows to insert; omitted nullable columns pad with `null`. Literals validate eagerly. | | `.orReplace()` | Upsert by the table's unique key. | | `UpdateQueryBuilder.set(col, value)` / `.set(patch)` / `.set((eb) => patch)` | Changes; `undefined` entries in a patch are skipped, explicit `null` writes NULL. | | `.where(...)` | Same forms as select `where`, on both update and delete builders. | | `.returning([...]) / .returningAll()` | Rewrites the result type to projected rows: written rows for inserts, post-update values for updates, deleted rows for deletes. | | `.compile()` | The `CompiledStatement` the engine executes. | | `.execute / .executeTakeFirst / .executeTakeFirstOrThrow()` | Run; without `returning`, resolves to `InsertResult` / `UpdateResult` / `DeleteResult` with plain-number counts. | ### Expression builder The callback argument of `where` / `having` / `select` / `set`. See the [expression vocabulary](/docs/sql/select.md#filtering-and-projection) for the full table: comparisons (`eb(lhs, op, rhs)`), `eb.and/or/not`, arithmetic, `eb.between/notBetween`, `eb.ref`, `eb.fn` aggregates and scalar functions, window functions with `.over(...)`, `eb.case()...end()`, `eb.selectFrom`, and `eb.exists`. Exported supporting types include `ExpressionBuilder`, `ExpressionWrapper`, `AggregateExpressionWrapper`, `CaseBuilder`, `OverBuilder`, `WindowFunctionBuilder`, and the operator token unions. ### The `sql` tag | Export | Description | | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | ``sql`…` `` | Tagged template producing a `RawSqlFragment`; interpolations become bound `$n` parameters, arrays expand to IN-list placeholders, nested fragments splice with renumbered parameters. `.execute(db)` runs it through the facade; `.sql`/`.params` expose the rendered statement. | | `RawSqlFragment, RawSqlValue, SqlExecutable` | Supporting types. | ## Live queries From `@minnowdb/core`. See [Live queries](/docs/sql.md). | Export | Description | | ----------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `class LiveQuery` | Returned by `.live()`. `subscribe({ onChange, onComplete? })` resolves to a `LiveSubscriptionHandle`; also an async iterable with latest-wins coalescing. | | `class LiveQuerySet` | The SQL-level mechanism behind the typed layer, from `database.liveQueries(options)`. `subscribe(sqlOrPlan, { onChange })`, `refresh()`, `stats`, `close()`. | | `LiveQuerySetOptions` | `channelName?` (BroadcastChannel hints), `pollIntervalMs?` (fallback polling). | | `LiveQueryStats` | Hints, sweeps, reruns executed/avoided, suppressed notifications, sweep latency. | | `LiveQuerySubscribeOptions, LiveQuerySubscription, LiveQueryInput, LiveQueryHintChannel, LiveQueryHandlers, LiveSubscriptionHandle` | Supporting types. | ## The engine — `MinnowDatabase` From `@minnowdb/core`. The low-level asynchronous engine the facade drives. See [Writes & transactions](/docs/sql/dml.md). ```ts new MinnowDatabase(store: BlockStore, options?: MinnowDatabaseOptions) ``` `MinnowDatabaseOptions` covers `compression`, `rowsPerBlock`, `maxCommitRetries`, `spillOwnerLeaseMs`, `bufferPoolBytes`, and deterministic seams (`now`, `createId`). `bufferPoolBytes` (default 64 MiB) bounds one shared LRU holding assembled column vectors, decoded blocks, zone-pruned projections, and derived-block results, and `0` disables it; compiled SQL plans are cached separately by statement text. | Group | Methods | | ---------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Catalog | `createTable`, `listTables`, `introspect()`, `migrate(schema)`, `createView`, `dropView`, `dropTable` | | Writes | `insertBatch`, `insert`, `upsertBatch`, `upsert`, `updateBatch`, `update`, `deleteBatch`, `bufferedWriter(table, options)` | | Reads | `readTable(table, { columns, version? })`, `listVisibleSegments` | | SQL | `query(sql, options?)`, `snapshot(callback)`, `write(callback)`, `explain(sql)`, `execute(sql, params?)`, `runStatement(statement)`. `write()` publishes every staged mutation as one commit and reads its own staged rows — see [write scopes](/docs/engine/transactions.md#atomic-writes). `CREATE TRIGGER` / `DROP TRIGGER` persist row triggers fired inside the triggering commit — see [triggers](/docs/sql/dml.md#triggers). Statements cover `INSERT ... SELECT`, `ON CONFLICT (key) DO NOTHING / DO UPDATE SET col = EXCLUDED.col`, and `RETURNING` on every mutation; placeholders (`?`/`$n`) bind through `options.params` or the `execute` parameter list. | | Snapshots | `exportSnapshot(options?)` returns the encoded file; `importSnapshot(bytes, options?)` loads one into an empty store. Both take `onProgress` — see [snapshots](/docs/storage/snapshots.md). | | Live | `liveQueries(options?)` | | Compaction | `compactTable`, `compactTableStep`, `resumeCompactionJob`, `listCompactionJobs`, `cancelCompactionJob` | | GC | `collectGarbage`, `collectGarbageStep`, `resumeGarbageCollectionJob`, `listGarbageCollectionJobs`, `cleanupQuerySpill` | Notable supporting exports: - `BufferedTableWriter` — `add(row)`, `flush()`, `requestFlush()`, `close()`, `discard()`, stats; configured by `BufferedWriterOptions` (`mode`, `maxRows`, `maxBytes`, `maxAgeMs`, `onError`). - `attachLifecycleFlush(writerProxy, options)` — requests flushes on `visibilitychange` / `pagehide`. - `QueryOptions` — including `executionMemoryBudgetBytes` and spill configuration; `QueryResult` / `QueryRow` / `QueryValue` for results; `WriteMetrics` on every batch result. - Plan tooling — `compileQuery`, `compileStatement`, `executeQuery`, `bindPlanParameters`, `bindStatementParameters`, `optimizePlan`, `renderPlan`, `referencedColumns`, `CompiledQuery`, `CompiledStatement`. - Input/result types — `CreateTableInput`, `InsertBatchInput/Result`, `UpsertBatchResult`, `UpdateBatchInput/Result`, `DeleteBatchInput/Result`, `ReadTableOptions`, `TableDefinition`, `CompactTableOptions/Result`, `CollectGarbageOptions`, `GarbageCollectionResult`, and friends. ### Errors | Error | Thrown when | | --------------------------------------------------------------------------------------------- | ------------------------------------------------------ | | `UniqueConstraintError` | A write violates the table's unique key. | | `MissingKeyError` | A keyed update/delete names a key that does not exist. | | `SqlCompileError` | SQL fails to compile; carries `offset` and `length`. | | `QueryMemoryBudgetError` | A reservation exceeds `executionMemoryBudgetBytes`. | | `NoResultError` | `executeTakeFirstOrThrow()` finds no row. | | `CompactionMemoryBudgetError, CompactionWriteAmplificationError, CompactionJobCancelledError` | Compaction guardrails. | All are rehydrated across the worker channel — `instanceof` works on the client. ## Worker hosting From `@minnowdb/core`. See [Workers & multi-tab](/docs/engine/workers.md). | Export | Description | | --------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------- | | `exposeDatabase(database, scope, options?)` | Serves the full client protocol for a database you constructed — the custom-entry hook. | | `attachDatabaseWorker(scope)` | What the stock `@minnowdb/core/worker` entry calls: builds the database from the client's init frame. | | `StoreDescriptor` | `{ kind: "indexeddb", name, … } \| { kind: "memory" }` — the cloneable store config. | | `WireDatabaseOptions, DatabaseInitPayload` | The cloneable subset of `MinnowDatabaseOptions` and the init frame shape. | | `serializeSchema / deserializeSchema / serializeMigrationSteps` | Schema DSL ⇄ wire form (used automatically by `client.migrate`). | ## `@minnowdb/core/client` | Export | Description | | ------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | `class MinnowDatabaseClient` | Main-thread proxy of the full database API. Construct with a `Worker` (any `ClientTransport`) and `MinnowDatabaseClientOptions` (`store`, wire options). | | `.ready()` | Surfaces store-open failures eagerly; calls may be issued before it resolves. | | Mirrored API | Every `MinnowDatabase` group above, promisified: catalog, writes, reads, SQL, live, maintenance. | | `.close({ terminateWorker? })` | Tears down handles, optionally terminating the worker. | | `ClientBufferedWriter, ClientLiveQuerySet, ClientLiveSubscription, ClientWriteSession, ClientSnapshotSession` | Handle proxies; synchronous getters become methods (`stats()`, `memoryUsage()`). | | `ClientTransport, ClientLiveQueryOptions, CloseClientOptions, ClientMigrationResult` | Supporting types. | ## `@minnowdb/core/worker` A side-effect module: importing it inside a module worker attaches the database host to `self`. Point a `Worker` at it and pass the store descriptor from the client — see [the quick start](/docs/engine/workers.md#quick-start). ## `@minnowdb/core/plan` Plan-construction primitives for building a typed layer over the engine — the block-assembly functions the SQL parser itself ends in, plus the plan types and validators that keep a hand-built plan as strict as a parsed one. See [Extending Minnow](/docs/reference/extending.md#building-plans-directly). | Export | Description | | ----------------------------------------------------------------------------------------- | ------------------------------------------------------- | | `assembleSelectBlock, compoundSelectBlock, derivedTableSource` | Assemble one select block, a set operation, a subquery. | | `splitCondition, validateLimit, validateOffset, hasAggregate` | The validators and helpers the parser applies. | | `optimizePlan, renderPlan` | Optimize a plan; render one for display. | | `CompiledQuery, Expression, JoinPlan, Predicate, SelectItem, SetOperator, TableSource` | Plan types. | | `AggregateName, PredicateOperator, WindowFunctionName, QueryValue, QueryRow, QueryResult` | Supporting types. | ## `@minnowdb/client` The optional typed query builder. Installed separately: `npm install @minnowdb/client`. See [Typed facade](#typed-facade) above and [Schema & migrations](/docs/schema.md). ## `@minnowdb/core/storage` | Export | Description | | ------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------- | | `class IndexedDbBlockStore` | The production store. `IndexedDbBlockStore.open({ name, durability?, … })`; `close()`. | | `class MemoryBlockStore` | Same interface, in memory — the unit-test store. | | `BlockStore` | The storage interface both implement (blocks, manifests, tables, segments, leases, jobs, temp pages). | | `Manifest, TableRecord, TableColumnRecord, SegmentRecord, RowIdSpan, LeaseRecord, …` | The persistent record types. | | `WriteConflictError, TableRecordConflictError` | Storage-level conflicts surfaced through the engine. | | `SimpleDataType, simpleDataTypes` | The four logical types as a value and union. | Record and job types beyond these (compaction plans, GC cursors, temp-run pages) are exported for tooling but are storage internals — the version-zero format carries no compatibility promise. ## `@minnowdb/core/transactions` The commit machinery under the engine — useful for storage-level tooling and tests, not needed for application code. | Export | Description | | --------------------------------------------------------------------------------------- | --------------------------------------------------------------- | | `class TransactionManager` | Opens snapshots and transactions over a `BlockStore`; recovery. | | `class DatabaseTransaction` | Staged blocks + atomic manifest publication. | | `class Snapshot` / `class LeasedSnapshot` | Immutable read views; leased snapshots persist expiry records. | | `TransactionClosedError` | Use after commit/abort. | | `TransactionManagerOptions, RecoveryOptions, RecoveryReport, OpenLeasedSnapshotOptions` | Supporting types. | ## `@minnowdb/core/block-format` The versioned binary containers: block headers, column encodings, codec registry, checksums, zone-map statistics, and physical-type mapping. Everything here is re-exported for tooling and inspection; it is the layer the no-compatibility-promise applies to most directly. ## `@minnowdb/core/worker-protocol` The versioned, structured-clone-safe RPC frames between client and worker: `protocolVersion`, request/response/event frame types, `parseRequest` / `parseRpcRequest` / `parseRpcResponse`, `serializeError`, and the frame constructors. Method dispatch is whitelisted per handle — the worker never dispatches arbitrary property access. ## `@minnowdb/core/testing` | Export | Description | | -------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `class FaultInjectingBlockStore` | Wraps any `BlockStore`; `new FaultInjectingBlockStore(inner, inject)` calls `inject(point)` around storage operations. | | `faultPoints, FaultPoint` | The named points: `beforeBlockWrite`, `afterBlockWrite`, `beforeBlockRead`, `afterBlockRead`, `beforeManifestCommit`, `afterManifestCommit`, `beforeTransactionCommit`, `afterTransactionCommit`. | | `FaultInjector` | `(point: FaultPoint) => void \| Promise` — throw to simulate the crash. | ## `@minnowdb/core/sql-feature-matrix.json` The checked-in conformance matrix rendered at [SQL support](/docs/sql/feature-matrix.md): every SQL feature the engine claims, with per-feature support status. The engine's conformance suite reports drift against this file, so the docs and the engine cannot silently disagree. --- Minnow 0.1.1 · this page on the site: /docs/reference/api/ --- # Extending Minnow > The primitives for building a typed layer, schema tool, or adapter on top of the engine. SQL is Minnow's contract. The engine runs statements on its own, and every typed layer — including the official [`@minnowdb/client`](/docs/engine.md) — is a consumer of the primitives on this page. That is not an accident of packaging. `@minnowdb/client` ships as a separate package built only from what is documented here, so if these primitives were not enough to build a query builder, the first thing to break would be ours. ## The primitives | Primitive | Import | What it gives you | | ------------------------- | --------------------- | -------------------------------------------------------------------------------------- | | `execute(sql, params)` | `@minnowdb/core` | The query channel. One entry point, one discriminated result. | | `introspect()` | `@minnowdb/core` | The catalog: stable IDs, keys, constraints, triggers, views. | | Statement transactions | `@minnowdb/core` | `BEGIN` / `COMMIT` / `ROLLBACK`, drivable from a layer that owns its own control flow. | | Plan construction | `@minnowdb/core/plan` | Build the same logical plan the SQL parser builds, and hand it to the engine. | | `sql-feature-matrix.json` | `@minnowdb/core` | Machine-readable capability discovery. | ## Running statements Everything the engine can do is reachable through one call. The result is a discriminated union, so a caller learns what happened without a second query: ```ts const result = await database.execute( `INSERT INTO orders (order_id, total) VALUES (?, ?)`, [1, 25], ); // { kind: "insert", table: "orders", rowCount: 1, version: 4 } ``` Bind values with `?` rather than building SQL strings. The compiled plan is cached on the statement text and re-bound per execution, so parameters are faster as well as safer — and a layer that inlines literals instead defeats that cache, since every distinct value becomes a distinct key. Identifiers quote with double quotes, doubling an embedded quote: `"order id"`, `"say ""hi"""`. ## Introspecting the catalog `introspect()` returns what a schema tool needs to diff a live database against a desired state. It is deliberately richer than `listTables()`, which answers what a _reader_ needs: ```ts const catalog = await database.introspect(); for (const table of catalog.tables) { table.name; table.uniqueKeyColumnId; // identity, not a name table.columns; // { id, name, type, nullable, defaultValue?, enumValues?, isAutoIncrementing } table.foreignKeys; // { name, column, parentTable, parentColumn, onDelete } table.checks; // { name, sql } table.triggers; // { name, event, timing } } for (const declared of catalog.views) { declared.name; declared.sql; // the query text it stands for declared.columns; // the query's inferred output schema declared.managed; // true when a migration created it, and may therefore drop it } ``` Two things make it plannable rather than merely descriptive: - **Column IDs are stable across renames.** A rename is only expressible as a diff because the column keeps its ID; matching on names alone cannot tell a rename from a drop plus an add. - **Derived facts are resolved for you.** `isAutoIncrementing` is reported directly rather than leaving a planner to decode a default spec. Tables and views are sorted by name, so a diff over two catalogs is stable. ## Planning a migration `planMigration` diffs a `Catalog` against a schema declaration. It takes the published catalog and nothing else — no database, no store, no engine — so a tool can plan against a catalog it fetched, cached, or built by hand: ```ts import { planMigration, schema, table, column } from "@minnowdb/core"; const catalog = await database.introspect(); // or any Catalog value you have const plan = planMigration(catalog, schema([table("notes", {/* ... */})])); for (const step of plan.steps) { step.kind; // "create-table" | "add-column" | "rename-column" | "widen-nullable" | // "widen-enum" | "alter-default" | "replace-view" | "drop-view" } ``` Planning is a pure function, so it is also how you preview: run it, show the steps, and decide whether to apply. Anything it cannot prove safe throws with a message naming the fix rather than appearing as a step — see [the rejected list](/docs/schema.md#what-migrate-does). **Applying still goes through the engine.** `database.migrate(schema)` plans and applies in one call. Some steps have no SQL spelling — a rename happens through the column's stable ID, which `ALTER TABLE` cannot express — so there is no statement list you could run yourself today. If you need the split, plan with `planMigration` to decide and inspect, then hand the same schema to `migrate()` to apply. ## Transactions The primitive is imperative, because a layer that owns its own control flow cannot always express its work as a callback: ```ts await database.execute("BEGIN"); try { await database.execute(`UPDATE accounts SET balance = balance - ? WHERE id = ?`, [10, 1]); await database.execute(`UPDATE accounts SET balance = balance + ? WHERE id = ?`, [10, 2]); await database.execute("COMMIT"); } catch (error) { await database.execute("ROLLBACK"); throw error; } ``` A scoped API can always be built on top of this. The reverse — recovering imperative control from a callback-only API — requires suspending the callback on deferred promises, which is why the imperative form is what gets published. Savepoints are not implemented, so transactions do not nest. ## Building plans directly `@minnowdb/core/plan` exposes the block-assembly functions the SQL parser itself ends in, plus the plan types and the validators that keep a hand-built plan as strict as a parsed one. A builder that uses them produces plans the engine cannot distinguish from parsed SQL — same validation errors, same desugaring, same execution strategy. ```ts import { assembleSelectBlock, optimizePlan, type CompiledQuery } from "@minnowdb/core/plan"; ``` This is the lowest-level primitive here, and the one most likely to change shape as the plan types move into a module of their own. Most layers should emit SQL and let the engine parse it: parsing costs 11–28 µs, which is under 1% of any query that touches real data. ## Discovering what the engine accepts A layer that generates SQL will eventually emit something the engine does not support. The answer is not a handful of capability flags but the feature matrix, shipped as data: ```ts import matrix from "@minnowdb/core/sql-feature-matrix.json" with { type: "json" }; const unsupported = matrix.features.filter((entry) => entry.status === "unsupported"); // each carries: id, the SQL:2023 Annex F feature, an example, and the error it raises ``` 190 entries, 176 supported. It is the input to the conformance suite, so it cannot drift from the engine without failing tests. The gaps most likely to matter to a SQL generator: | Gap | Note | | ----------------------------- | --------------------------------------------------------------------------- | | correlated non-equi `EXISTS` | correlation must be a plain equality between one inner and one outer column | | correlated `NOT IN` | use `NOT EXISTS` | | `LATERAL` sources | no operator re-executes a source per row of its left side | | `ON CONFLICT DO UPDATE SET` | supported only as `column = EXCLUDED.column` | | `COLLATE`, sequences, `ARRAY` | absent; the matrix records each with its error | Every one of these raises an explicit error naming the constraint, so a generator can surface a useful message rather than a failure deep in execution. ## Row types If your layer is typed, [`InferDatabase`](/docs/schema.md#inferred-shapes) gives you a `DB` where each table names its `select`, `insert`, and `update` shapes and each view names only `select`. Reading those three names is all that is required — there is no marker to decode, and the absence of `insert` on a view is what makes writing to one a compile error. --- Minnow 0.1.1 · this page on the site: /docs/reference/extending/ --- # Architecture > How Minnow works and why it's built this way. Minnow is built for how browsers actually behave: tabs close without warning, storage is slow, and the same app may be open in five tabs at once. Everything below follows from that. The short version: data lives in compressed columnar blocks inside IndexedDB, reads see consistent snapshots (MVCC), and queries run through a vectorized executor. The full design document is `ARCHITECTURE.md` in the repository. This page covers the choices and the reasons. ## The ground rules These are fixed. Everything else is built around them. - **IndexedDB is the source of truth.** BroadcastChannel, Web Locks, and page lifecycle events can all fail silently. Minnow uses them to speed things up, never for correctness. Correct behavior always rests on a committed IndexedDB transaction. - **Durability ends at a committed transaction.** A committed write is safe. A write still in flight when the tab closes is not. Minnow flushes early when the page hides, but treats that as a bonus, not a guarantee. - **Where the engine runs is your choice.** Put it in a worker and hold an async proxy in the page, or construct it on the main thread. The API is async everywhere, so your code — and any adapter written against it — looks the same either way. - **Published data is immutable.** Once written, a block never changes. Most of the design falls out of this one rule. - **Nothing special to deploy.** No COOP/COEP headers, no SharedArrayBuffer, no WASM file to host. `npm install` and go. - **The engine is our own.** No SQLite or DuckDB underneath — engines built for POSIX files carry assumptions browsers don't honor. The trade-off: the SQL surface is a careful subset, tracked in the [feature matrix](/docs/sql/feature-matrix.md). ## Why columns, why immutable IndexedDB charges a lot per operation and little per byte. So Minnow stores a few large values instead of many small ones: - Data is packed into **compressed columnar blocks** — one column for a group of rows, roughly a megabyte before compression. - No row is ever its own IndexedDB entry, and no table is one giant entry. - Each block carries **checksums** (one over the payload, one over the header and its statistics) and min/max stats, so queries can skip blocks that can't match a filter without even decompressing them. There are no user-managed indexes. Columns beat rows here because most reads touch a few columns across many rows: filter, aggregate, scan. Similar values sit together, so they compress well, and queries fetch only the columns they use. Immutability is the rule that pays for everything else: - A crash can strand unused data — it can never corrupt visible data. - Retrying a write is always safe. - Another tab can keep reading old data for as long as it needs. - A snapshot is just a list of blocks, so snapshots cost nothing. Writes append small **delta segments**: an update stores just the key and the changed columns, a delete stores a key marker. Background compaction folds deltas into larger read-friendly segments later. Nothing is ever edited in place. A query reads a table with deltas by scanning the appended data and applying the deltas over it: deleted keys mask rows out, updated keys patch the cells they changed, and the row groups a delta cannot reach are skipped from their statistics alone. The cost is the size of the deltas, not the size of the table — deleting one row of a million does not make the next query re-read the million. ## How a write commits Readers see the database through a **manifest** — the list of exactly which blocks make up version N. Physically each commit stores only what changed (a full checkpoint lands every 32 versions), so publishing costs the size of the change, not the size of the database. A commit publishes version N + 1, in strict order: ``` 1. encode and compress the new blocks 2. write the blocks (nothing points to them yet) 3. open a short metadata transaction 4. check the manifest is still at version N 5. publish manifest N + 1 ``` Data first, pointer last, and the pointer flip is atomic. A crash anywhere in between leaves orphaned blocks for the garbage collector — never a manifest pointing at half-written data. Step 4 is the concurrency control. If another tab published first, the commit fails cleanly and retries against the new version. Conflicts surface as typed errors, never as silent interleaving. Encoding is bounded but parallel across independent columns. Blocks within each column keep their original order, and the staged metadata stays in schema order, so native compressors can overlap without making the committed layout depend on completion timing. ## Sharing the database across tabs Minnow assumes several tabs, unaware of each other, some frozen or already gone. - **One writer wins.** IndexedDB serializes the manifest flip. Two tabs can prepare writes at the same time; only one publishes, the other retries. - **Notifications are hints.** BroadcastChannel announces that a new version exists, but every tab reconciles against IndexedDB. A missed message costs a little latency, never a stale result. [Live queries](/docs/sql.md) are built on this. - **Dead tabs are handled by leases.** A long-running read holds a lease — a stored record with an expiry, renewed while the tab is alive. If the tab vanishes, the lease expires and whatever it pinned becomes reclaimable. No heartbeats, no guessing. ## How queries run - Data flows through the executor in typed batches of 2,048 rows: numbers and dates in `Float64Array`s, strings dictionary-coded, nulls in packed bitmaps. Tight loops over typed arrays instead of millions of short-lived objects. - Grouping on dictionary-coded strings reuses their integer codes. Small compound domains use direct-address slots; high-cardinality sparse domains pack the codes into a numeric key. Both avoid repeatedly encoding and hashing the same strings for every input row. - Queries accept a **memory budget** (`executionMemoryBudgetBytes`). Memory is reserved before it is allocated. Under a budget, sorts and grouped aggregations spill to durable temp pages instead of blowing up the tab; past it, you get a typed `QueryMemoryBudgetError`. - Spill pages are lease-protected, so a query abandoned by a dead tab gets cleaned up. - Compiled plans are cached separately, by statement text, so re-issuing a statement doesn't re-parse or re-plan it. - Everything else repeated is cached in one byte-bounded buffer pool (`bufferPoolBytes`, default 64 MiB): decoded blocks by immutable block id, assembled column vectors and zone descriptions by the same, and computed results — whole-block results, the columnar forms of derived and windowed sources, and whole statement results — by exact visible-segment fingerprint. A commit moves the fingerprints, so computed entries stop matching, but unchanged blocks stay decoded: the next statement pays re-assembly, not re-fetch and re-decompression. Passing `memoize: false` to a query bypasses the computed-result entries and measures execution, which is what the benchmarks do. The budget is a model, not a heap meter, and not every operator can spill yet — see [what we don't claim](#what-we-dont-claim). ## Background work Compaction and garbage collection run for a long time inside a tab that can die at any moment. So: - **Every job is a durable record.** Plan first, persist the plan, then advance in small checkpointed steps. A dead tab costs one step, not the whole job. - **Cancellation is a real outcome.** Cancel and publish settle atomically — exactly one wins. - **Garbage collection is reachability.** Whatever the current manifest, unexpired leases, live transactions, and running compactions still reference is kept. Each deletion re-checks liveness atomically, so nothing is revived after it's gone. - **Compaction has a write budget.** A rewrite that would cost more than a set multiple of the data it consolidates is refused up front, not discovered in the quota bill. ## The shape of the system ``` Page (UI thread) async proxy: requests, cancellation, results | Coordinator worker catalog, snapshots, transactions, planning, live queries | Storage IndexedDB, block codecs, manifests, leases, GC ``` - Each tab owns its own coordinator worker. - Buffers cross the boundary as transferable `ArrayBuffer`s — no shared memory, which is why no cross-origin isolation is needed. - The [worker protocol](/docs/engine/workers.md) is versioned RPC; each handle answers a fixed list of methods, nothing else. - Inside `@minnowdb/core`, the layers are separate modules — `block-format`, `storage`, `transactions`, and the engine — each importable on its own. ## Crash testing Fault injection has been required since the first storage code. Tests kill the engine before and after every block write and manifest commit, and these must hold no matter where the crash lands: 1. A visible manifest only references complete, checksum-valid blocks. 2. A stale manifest check cannot publish anything. 3. Retrying a block write cannot change published bytes. 4. Unpublished data never affects a read and is always safe to collect. 5. The UI stays responsive regardless of operation size. The same hook is public: `FaultInjectingBlockStore` in `@minnowdb/core/testing` wraps any store, so your own tests can crash the engine on purpose too. ## What we don't claim - **Bounded memory is a goal, not a guarantee yet.** Projected columns still materialize in full before accounting starts, and some join shapes can't spill. The budget catches whole classes of blowups; it is not a hard cap on the heap. - **No big-dataset performance claims** until real browser measurements back them. The [benchmarks](/benchmarks) publish what has been measured, methodology included. - **The early block format may change.** Formats only ever grow (new codecs get new IDs; old bytes are never reinterpreted), but this early format — currently version 1, whose header and statistics are independently checksummed — carries no compatibility promise yet. - **No index DDL.** Skipping comes from block statistics. That's a feature, not a gap. One working rule sits behind all of this: each layer advances only when measurements support it — storage throughput before query work, atomic commits under injected faults before multi-tab features. Finding out early is cheap. Finding out late is a rewrite. --- Minnow 0.1.1 · this page on the site: /docs/reference/architecture/ --- # Testing & benchmarks > The test runners, the release gate, the performance gate, and the benchmark workloads. One set of focused runners covers fast correctness checks, real-browser behavior, performance regressions, and the benchmark harness behind the live benchmarks page. This page is the source of truth for running and maintaining Minnow's tests and benchmarks. ## Choose the smallest runner that proves the change | Command | What it runs | Use it for | | ------------------------------ | --------------------------------------------------------------------- | ---------------------------------- | | `npm test` | Vitest unit, conformance, differential, and harness tests | Normal implementation work | | `npm run test:coverage` | The same suite, with the coverage floors enforced | Before pushing | | `npm run soak` | The generative suites on fresh random seeds, to find new failures | Hunting bugs rather than pinning | | `npm run fixture:format` | Freezes a database this build wrote, as a compatibility fixture | Before changing a format version | | `npm run test:browser:library` | Core IndexedDB and transaction tests in Chromium, Firefox, and WebKit | Storage or browser-runtime changes | | `npm run test:browser:site` | Public-site examples in Chromium, Firefox, and WebKit | Docs, examples, and site changes | | `npm run test:browser` | Both browser runners, in sequence | Cross-browser regression checks | | `npm run benchmark:gate` | Seeded Node performance ratios against the checked-in baseline | Query-executor performance changes | | `npm run benchmark:sizes` | Download size of every comparison engine, from the installed packages | Dependency or public-entry changes | | `npm run check` | Formatting, types, lint, build, and unit tests with coverage floors | The local merge gate | | `npm run check:release` | The local gate, performance gate, and all browser runners | Release candidates | Install the browser binaries once before using a browser runner: ```bash npx playwright install chromium firefox webkit ``` Everything above also runs in CI: `.github/workflows/ci.yml` on every push and pull request, and `.github/workflows/performance.yml` nightly. The performance gate is deliberately kept off the merge path — it measures a machine as much as it measures the code, and a merge gate that fails on a noisy runner is one people learn to re-run rather than read. Each browser runner owns its test directory, server, port, and build prerequisites. Shared browser defaults live in `playwright.shared.mjs`; runner-specific configuration stays in its named `playwright.*.config.ts` file. This keeps every runner independently callable without hiding which application it starts. ## What each layer proves - **Unit and conformance tests** stay beside the source they exercise. They cover deterministic behavior, SQL and mutation conformance, differential execution, fault injection, and the benchmark generator/oracle contracts. `npm test` picks up `apps/site/bench` alongside `packages/**`, so the dataset generator, the oracles, and the suite definitions are checked on every unit run. - **Library browser tests** exercise IndexedDB and transaction behavior that a Node substitute cannot prove, and drive a database through a real module worker: the published entry booting, transferred buffers arriving intact, and a second worker reopening what the first one wrote. - **Site browser tests** execute the public examples and drive the benchmarks page itself: one case runs a suite end to end in each browser and asserts it verified against the oracles, another asserts the `/benchmarks` route is cross-origin isolated. Documentation cannot drift into non-running sample code, and the harness cannot drift from the page that runs it. - **The performance gate** detects regressions on stable seeded query shapes, reads and writes alike. It is a guardrail, not a published cross-engine benchmark: the comparison engines run without indexes, which is fine for noticing that Minnow got slower and useless as a fair cross-engine claim. When adding a runner, give it one responsibility and make it independently runnable. When adding a suite case, derive smoke-test counts from the suite itself instead of copying totals into tests or HTML. ## Seeds, and how a soak failure becomes a permanent test The generative suites — SQL conformance, DML conformance, and the columnar-versus-row differential — build their corpora from a seeded generator. A committed run is deterministic: it uses the checked-in seeds plus every seed that has ever failed, listed in `packages/core/regression-seeds.json`. That makes the suite a reliable regression net, and on its own it would be nothing else, because the questions it asks never change. `npm run soak` is the other half. It runs the same suites on seeds nobody has tried and stops at the first failure, printing the seed: ```bash npm run soak -- --rounds 200 ``` A failing seed is the whole artifact. Replay it directly: ```bash MINNOW_SEED=1476318588 npx vitest run packages/core/src/engine/sql-conformance.test.ts ``` Then add it to `regression-seeds.json` under the suite that failed, where every future run picks it up. Never remove one: a seed in that file is a bug that used to exist, and the entry is what stops it coming back. The explored space only grows, and it grows by exactly what the soak found. ## Format compatibility A browser database's data lives in the user's browser, so it outlives every deploy. There is no migration window and no way to reach back and rewrite it: if a format change makes yesterday's blocks unreadable, the first anyone hears is a user whose application will not open. `packages/core/format-fixtures/` holds one frozen database per released format version — a snapshot, which carries the raw block bytes verbatim, plus the answers that build gave to a fixed set of queries. `format-compatibility.test.ts` opens all of them on every run, checks the answers still hold, and checks that writes into a restored database still work. It also fails when the current `BLOCK_FORMAT_VERSION` or `SNAPSHOT_FORMAT_VERSION` has no fixture behind it. That failure is the useful one, because it fires _before_ the damage: ```bash npm run fixture:format ``` Run it on the build that still writes the old format, commit the fixture, and only then change the version. A fixture can only be produced by the build that writes it — once that code is gone, the format it wrote cannot be regenerated. For the same reason, never delete one. ## Running out of quota `quota.test.ts` covers what happens when the browser refuses a write because the origin is out of space — the characteristic way a browser database fails, and the one an application most needs to handle deliberately. It pins four things: the write fails rather than half-landing, the error keeps its `QuotaExceededError` identity so an application can branch on it, everything committed earlier stays readable, and the same write succeeds once there is room, with no repair step. ## Property-based tests `block-format/properties.test.ts` states the format's contract as properties and checks them against generated inputs rather than chosen ones: a value written and read back is the same value, both codecs agree, a zone map contains everything it summarizes, and a flipped byte is detected rather than decoded into plausible rows. The generators reach for what breaks encoders — negative zero, the extremes of the double range, subnormals, empty and astral-plane strings, all-null and empty columns. A failure shrinks to a minimal case and prints the seed. ## Soaks Two suites cover accumulation rather than a single operation, which is where a fold that loses a row or a compactor that stops folding actually shows up: - `compaction-soak.test.ts` runs 1,500 interleaved mutations against a reference `Map`, compacting at checkpoints, and compares the whole table to the reference each time. Compaction is bounded and incremental — one call folds a limited number of blocks — so the contract asserted is monotone progress, not a fixed point reached in one call. - `concurrency-simulation.test.ts` runs twelve independent databases over one shared store, the shape of twelve browser tabs on one origin, issuing a seeded random schedule. It checks that the outcome is explicable: every visible row was written by some tab, acknowledged writes are present, no key appears twice, the write the store acknowledged _last_ is the one visible, and every tab sees the same database afterwards. ## Concurrent writes `write-contention.test.ts` documents a sharp edge. Writes commit optimistically and rebase on conflict, up to `maxCommitRetries` attempts. Issued sequentially, they all land. Issued concurrently against IndexedDB, at most `maxCommitRetries + 1` land — and that ceiling does not move with the number of writers, because each commit that wins costs every other in-flight writer one retry. Sixty-four parallel writes leave the same nine winners as sixteen do. The tests guarantee the losses are clean: a rejected write leaves nothing behind, an accepted one is fully present, and which is which is deterministic. An application issuing parallel writes to one table should await them, or retry the rejections. ## The fault sweep `packages/core/src/testing/fault-sweep.test.ts` runs one fixed write workload many times, failing a different storage operation each time — the first block write, then the second, then the first block read, and so on through every operation a clean run performs. After each interruption it reopens the store and asks whether what survived is coherent. The invariant is atomicity, not durability. A write interrupted mid-flight may be present or absent; both are correct. What is never correct is a torn state — a row nobody wrote, a duplicated key, a database that will not reopen. Every assertion is phrased as "the surviving state is one a caller could have observed", never "the write landed". The set of fault points the workload reaches is pinned in the test. If a change starts or stops routing writes through one, the sweep fails and someone decides whether it should follow. ## Benchmark workload model The benchmarks page keeps four workload classes apart. They are never combined into one score: | Workload | Read measurement | Write measurement | | -------- | --------------------------------------------------- | ---------------------------------------------------------------- | | **OLTP** | Selective key, small-set, and bounded-range latency | Point and small-batch inserts, updates, and upserts (1–100 rows) | | **OLAP** | Scan, join, window, and aggregate execution | Bulk ingestion and mutation throughput (10,000–100,000 rows) | A read workload keeps every query visible rather than collapsing to one figure: each cell is the median of the timed windows for that query, after one untimed warm-up. Writes keep every operation and batch size visible the same way. Nothing is reported unless it was verified — every read must match an independent JavaScript oracle and every write is read back and compared row for row, and a query an engine got wrong or could not run prints a dash instead of a timing. There is nothing to publish or regenerate. The [benchmarks page](/benchmarks) has no checked-in numbers: it builds every engine and runs the suites in the visitor's browser, on the engines, suites, and dataset size they pick, and explains the measurement methodology, storage differences, caching, and memory caveats as it reports them. The suites themselves live in `apps/site/bench` and the page that drives them in `apps/site/app/benchmarks`. To iterate on the harness, run the unit tests (`npm test`) for the generator, oracles, and suite contracts, then `npm run test:browser:site` to run a suite in real browsers. To try a change by hand, `npm run site:dev` and open `/benchmarks`. ## Update the performance-gate baseline After an intentional executor change, inspect the performance-gate output before updating its thresholds: ```bash npm run benchmark:gate -- --update ``` Treat that update as a code change: explain it in review and run the gate again without `--update`. ## Refresh the download-size comparison The benchmarks page opens with what a browser downloads to run each engine. It is measured from the installed packages rather than quoted, so refresh it whenever a dependency version or the public entry point changes: ```bash npm run benchmark:sizes ``` Each engine's browser entry is bundled and minified with identical esbuild settings, and the WebAssembly and data files it fetches at run time are added at their shipped size. The result lands in `apps/site/components/bench/library-sizes.json`, which is a scratch output — nothing imports it. The figure each engine shows on the page is the `download` field in `apps/site/components/bench/config.ts`, updated by hand from that run. --- Minnow 0.1.1 · this page on the site: /docs/reference/testing/ --- # Versioning > One major across every package, what a version change means, and how a release is cut. Every Minnow package shares a major version and moves independently inside it. The major is the compatibility line: any `@minnowdb/client@0.x` works with any `@minnowdb/core@0.x`, whichever minors the two happen to be on. ```bash npm install @minnowdb/core @minnowdb/client ``` The client and the devtools console are built on the engine's published primitives, and a change that breaks that contract is a major change by definition. So each package depends on its siblings by a range that spans the major and stops at it: ```json "dependencies": { "@minnowdb/core": ">=0.1.0 <1.0.0" } ``` The ceiling is the compatibility line, and npm refuses a mixed-major install on its own. The floor is the sibling release this package was built against — when the devtools start using something the engine added in 0.4.0, their next release says `>=0.4.0` and npm resolves an engine that has it. Below the major, a fix to the console does not drag the engine's version along with it. ## What 0.x means Minnow is in 0.x, and the usual 0.x rule applies: **breaking changes can land in a minor release.** Pin exact versions in an application, and read the release notes before moving from `0.1` to `0.2`. A change is breaking when it is one of these: - **The API.** An export removed or renamed, a signature narrowed, or a default changed. - **SQL.** A form that used to run and no longer does, or one that runs and now answers differently. Additions to the [feature matrix](/docs/sql/feature-matrix.md) are not breaking. - **Stored data.** A database written by the old version that the new one cannot open. From 1.0 onwards these move the major version — every package's, together — and minor releases stay additive. ## The block format has its own version The version on the package describes the code. The bytes in IndexedDB carry a separate `BLOCK_FORMAT_VERSION`, and a snapshot file carries `SNAPSHOT_FORMAT_VERSION`. They move on their own schedule — most package releases do not touch them. Every released format version keeps a frozen database in `packages/core/format-fixtures/`, opened and queried on every test run, so a change that would make an existing database unreadable fails in CI rather than in an application. See [Testing](/docs/reference/testing.md) for how that suite works. ## How a release is cut One command writes the versions, the dependency ranges between them, and the lockfile: ```bash npm run version:set -- minor @minnowdb/core # one package, inside the shared major npm run version:set -- major # every package to the next major, together ``` Then commit and push. That is the whole release: ```bash npm run check:release # optional locally; CI runs the same gate git commit -am "Release core 0.2.0" && git push ``` Publishing is driven by the versions in the manifests, not by a tag or a command. When that commit's CI run goes green, `.github/workflows/release.yml` publishes every package whose version npm does not already have and tags each one `@minnowdb/core@0.2.0`. A push that changes no version publishes nothing, and a rerun after a failure is safe, because the registry decides what has already shipped. There is no npm token in the repository. npm is configured to trust that workflow in this repository, and hands it a credential that lives for the length of one publish — which is also what signs the provenance attestation you can see on each version's npm page. Nothing expires and there is nothing to rotate. It is configured once per package, in the package's settings on npm, and a package has to exist before it can be configured: the first release of a new package is published from a machine with `npm run release:publish`, and every release after it from CI. Two things are checked before anything leaves the machine. `npm run version:check` proves the workspace agrees with itself — matching majors, ranges that span them, and a docs version that matches the engine — as the first step of both `npm run check` and CI. And the publish refuses a tarball carrying anything that is not part of the package: tests compile into `dist` beside the code, and `files` has to exclude them. ## Where each version's documentation lives The documentation describes the engine, so it is versioned with `@minnowdb/core`: `v0.1` while Minnow is in 0.x, where a minor can break things, and `v1`, `v2` from 1.0 onwards. | URL | What it serves | | -------------------------- | ------------------------------------------------------------- | | `minnowdb.com/docs/…` | The current release. A link written here never goes stale. | | `minnowdb.com/v0.1/docs/…` | 0.1.x, frozen at its tag, for as long as it is worth keeping. | The picker at the top of the docs sidebar moves between them and keeps you on the same page. It reads [`/versions.json`](/versions.json) from the site's root rather than the copy compiled into the build, so an archived version still lists releases that did not exist when it was frozen. Everything under an archived prefix is versioned with it: its search index, its [markdown and llms.txt](/docs/reference/agents.md), and every link between its pages. Archived pages also carry `noindex`, so a search engine offers the current documentation first. The playground and the benchmarks are not archived — they always run the current release. An archive is the same site built from the tag with a base path: ```bash git worktree add ../minnow-v0.1 v0.1.0 cd ../minnow-v0.1 && npm ci SITE_BASE_PATH=/v0.1 npm run site:build ``` `apps/site/out/` is then deployed at that prefix, and the version is added to the `archived` list in `apps/site/public/versions.json` on `main`. On Vercel that means a project of its own, with the main site rewriting `/v0.1/:path*` to it — a static export has no server to route with, so the prefix has to come from the platform. --- Minnow 0.1.1 · this page on the site: /docs/reference/versioning/ --- # AI agents & LLMs > Machine-readable documentation, and the rules to give an agent writing Minnow code. Everything on this site is published twice: once as a page, and once as the markdown it was written from. An agent reading the second one gets the sentences without the navigation tree, the search dialog, or the syntax highlighter's markup around every keyword. ## Machine-readable documentation | URL | What it is | | ------------------------------------------------------ | --------------------------------------------------------------------------------- | | [`/llms.txt`](/llms.txt) | Every page, titled and described, linked to its markdown. Follows llmstxt.org. | | [`/llms-full.txt`](/llms-full.txt) | The whole documentation set in one file, about 240 KB. | | `/docs/.md` | The markdown twin of any page: drop the trailing slash and add `.md`. | | [`/sql-feature-matrix.json`](/sql-feature-matrix.json) | Every SQL form the engine supports and rejects, with a runnable example for each. | | [`/versions.json`](/versions.json) | Which versions are published, and the path each one is served at. | | [`/sitemap.xml`](/sitemap.xml) | Every page on the site. | Each page also declares its markdown twin in its head, as ``, so a crawler finds it without being told the rule. The feature matrix is worth pointing an agent at directly. It is not a description of the engine written by hand — it is the fixture the engine is tested against, so every example in it is executed on every test run and every rejection is checked to still fail with the error recorded there. See [Feature matrix](/docs/sql/feature-matrix.md). Each of these is versioned with the docs it belongs to. `minnowdb.com/llms.txt` describes the current release; an archived release serves its own at `minnowdb.com/v0.1/llms.txt`. Point an agent at the version your project pins, and see [Versioning](/docs/reference/versioning.md). ## Rules for an agent writing Minnow code Paste this into `AGENTS.md`, `CLAUDE.md`, or your editor's rules file. It is also served on its own at [`/agent-rules.md`](/agent-rules.md), so you can fetch it directly: ```bash curl -o AGENTS-minnow.md https://minnowdb.com/agent-rules.md ``` ```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 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. ``` ## Why those rules They are the mistakes a model makes with Minnow specifically — the places where a habit learned from Postgres or SQLite produces code that fails, or code that works but is ten times slower than it needs to be. - **`query` versus `execute`.** Handing a write to `query` throws instead of writing, which is a confusing failure if you assumed one entry point. See [Running SQL](/docs/sql.md). - **Parameters.** Interpolated values produce a new statement string per call, and the plan cache is keyed on that string, so every call re-parses, re-plans, and re-optimizes. - **The unique key.** Mutations address rows by unique key, so `UPDATE` and `DELETE` are rejected outright on a keyless table. This is the rejection agents hit most. See [Writing data](/docs/sql/dml.md). - **Batch writes.** `insertBatch` skips the parser entirely and writes columns as columns. A loop of `INSERT` statements is the single most common performance mistake. See [The database API](/docs/engine.md). - **Workers.** Query execution on the main thread competes with rendering. The API is identical either side of the boundary, so there is no reason to start on the main thread and move later. See [Workers & multi-tab](/docs/engine/workers.md). - **The SQL surface.** It is large but not complete, and the rejections are deliberate and documented. An agent that checks the matrix writes a supported form the first time instead of discovering the gap through an error. ## Verifying what an agent wrote The [playground](/playground) runs a real database of about 590,000 rows in the browser, so a query can be pasted in and run against a schema that already exists. In an application, the [devtools console](/docs/devtools.md) does the same against your own data, and its **Plan** tab shows what the optimizer made of the statement. --- Minnow 0.1.1 · this page on the site: /docs/reference/agents/