Running SQL

Minnow accepts PostgreSQL-style SQL for one embedded database. Every statement goes through one of four methods. They differ in what they return, not in what they can run. The executable feature matrix lists the exact compatible surface, differences, and extensions.

Reading

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. Exact decimals, JSON/JSONB, UUIDs, arrays, DATE, TIME, intervals, and enums arrive as strings so no precision or domain value is lost. columns lists the result names, and the positionally aligned columnDomains identifies logical domains such as JSON, UUID, and DATE — enough to render headers or parse JSON without inspecting a single value.

query accepts only statements that produce rows. Hand it an INSERT and it throws rather than silently writing.

Parameters

PostgreSQL-style placeholders are $1, $2 by position. Minnow also accepts ? in order as an adapter-friendly extension. Bind through params; never concatenate values into the text.

await db.query("SELECT * FROM orders WHERE status = $1 AND total >= $2", {
  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.

Fixed compilation and value limits

SQL compilation happens before a query has an execution-memory account, and scalar functions can allocate before a sort or join gets a chance to spill. Minnow therefore refuses these inputs at a fixed boundary:

These are safety limits, not cache tuning. Inputs are checked before tokenization, structural pattern compilation, JSON parsing, padding, or other expanding work. Pattern matching uses a glob walker or Thompson NFA for LIKE/SIMILAR TO. Regex uses a bounded interpreter with leftmost-longest match selection and charges every transition against the same fixed work ceiling. Regex also limits compiled and visited states to 65,536, capture groups to 32, and repetition bounds to 1,000. Exceeding any bound throws instead of hanging or returning an approximate answer. Plain selection and cursor streaming do not apply the scalar-result limit to an existing stored string. All accepted SQL, pattern, JSON, and full-text strings must also be well-formed Unicode; an unpaired UTF-16 surrogate is rejected instead of being silently replaced during UTF-8 persistence.

The corresponding MAX_SQL_* constants are exported from @minnowdb/core so an application can validate editor or API input without copying the numbers.

Compilation cannot create lifetime growth either. The plan and statement caches each retain at most 512 entries per database, and catalog-state, pattern, full-text-term, collation, and compiled-check caches have smaller fixed entry or byte ceilings. Accepted SQL or constraint text longer than 16,384 characters is compiled normally but not cached. Closing the database clears its per-database resident caches; process-global helper caches remain within their fixed bounds.

Writing

execute returns a tagged union, so the result tells you what the statement did:

const result = await db.execute(
  "UPDATE orders SET status = 'refunded' WHERE order_id = $1 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, insert, update, delete, merge, transaction, set, create-table, create-type, create-sequence, add-column, drop-column, drop-table, create-index, drop-index, create-view, drop-view, create-trigger, and drop-trigger. Row-writing kinds carry rowCount and the published version; returnedRows appears only when the statement had a RETURNING clause. TRUNCATE reports as a delete; SET and RESET return { kind: "set", action, name }, and SHOW returns rows.

execute also takes the engine controls a query does, as a third optional argument: execute(sql, params?, { signal?, onStats?, memoize?, executionMemoryBudgetBytes? }). A SELECT honors all of them through the query pipeline; every other statement checks signal once before it starts running, so an already-aborted execute never mutates anything.

Compiling once, running many times

When the same statement runs in a loop, compile it once and skip even the plan-cache lookup:

import { bindStatementParameters, compileStatement } from "@minnowdb/core/query";

const statement = compileStatement("INSERT INTO events (id, kind) VALUES ($1, $2)");
for (const event of batch) {
  await db.runStatement(bindStatementParameters(statement, [event.id, event.kind]));
}

For bulk loading, prefer insertBatch, 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. Statements that are not a function of the data are never memoized: those reading the clock (CURRENT_TIMESTAMP, NOW()), RANDOM() or GEN_RANDOM_UUID(), or a sequence (NEXTVAL, CURRVAL), and any query run with an explicit version, memory budget, or spill option.

That is what you want in an application and exactly what you do not want in a benchmark:

// 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 SQL statements land together — or fail together — open a transaction:

await db.execute("BEGIN");
try {
  await db.execute("UPDATE stock SET on_hand = on_hand - $1 WHERE sku = $2", [1, sku]);
  await db.execute("INSERT INTO shipments (sku, shipped_at) VALUES ($1, $2)", [sku, new Date()]);
  await db.execute("COMMIT");
} catch (error) {
  await db.execute("ROLLBACK");
  throw error;
}

Statements inside see earlier writes from the same transaction. ROLLBACK discards them all. Schema changes are refused inside a transaction, and one left untouched for 30 seconds rolls back automatically. When you are using the batch APIs instead of SQL, a write() scope gives you the same all-or-nothing result and ends with its callback.

What the language covers

Joins, subqueries, recursive CTEs, window functions, set operations, grouping sets, RETURNING, upserts, triggers, and full-text search. The PostgreSQL compatibility page lists every documented supported form, difference, extension, and exclusion. Each example runs in the engine's test suite. SQL compatibility checks explains the comparisons with PGlite, SQLite, and SQLLogicTest.