Query Optimization & Performance Features

┌──────────────────────────────────────────────────────────────────────────────┐ LibreDB Studio includes enterprise-grade query optimization features to prevent system freezes and provide performance insights for DBAs, data engineers, and developers. ┌──────────────────────────────────────────────────────────────────────────────┐

┌──────────────────────────────────────────────────────────────────────────────┐

Query Pagination System
Silent Auto-Limiting
Where the page size comes from
Destructive-Statement Confirmation
Load More Functionality
Result-Level Signals
Query EXPLAIN Integration
Performance Insights
Architecture ┌──────────────────────────────────────────────────────────────────────────────┐

Query Pagination System

Overview

┌──────────────────────────────────────────────────────────────────────────────┐ All SELECT queries are automatically paginated to prevent browser freezes when dealing with large datasets. This is handled transparently without interrupting the user workflow. ┌──────────────────────────────────────────────────────────────────────────────┐

Key Constants

Constant	Value	Description
`DEFAULT_QUERY_LIMIT`	500	Rows per page when the caller asks for no particular size
`PREVIEW_PAGE_SIZE`	50	Rows a tree click asks for, sent as the `limit` execution option (`src/hooks/use-tab-manager.ts`)
`MAX_UNLIMITED_ROWS`	100,000	Maximum rows for "Load All"
┌──────────────────────────────────────────────────────────────────────────────┐

How It Works

User executes a SELECT query
System automatically adds LIMIT <page size> if no LIMIT exists (an OFFSET clause is only appended when the offset is greater than 0). The clause is inserted at the end of the statement itself, before any trailing comment or ; — see Where the bound is placed
If user already specified a LIMIT, it's preserved (no override) — and the requested offset is discarded with it, which is why no further page is offered for such a statement
Results display with pagination metadata ┌──────────────────────────────────────────────────────────────────────────────┐

Silent Auto-Limiting

Philosophy

┌──────────────────────────────────────────────────────────────────────────────┐ Instead of showing warning popups for large datasets, LibreDB Studio silently limits results to 500 rows. This provides: ┌──────────────────────────────────────────────────────────────────────────────┐

Uninterrupted workflow - No confirmation dialogs
Safe defaults - System never freezes
User control - Load More when needed ┌──────────────────────────────────────────────────────────────────────────────┐

Visual Indicators

┌──────────────────────────────────────────────────────────────────────────────┐ When auto-limiting is applied: ┌──────────────────────────────────────────────────────────────────────────────┐

┌──────────────────────────────────────────────────────────────────────────────┐
│ 50 rows • load 50 more   4 columns   AUTO-LIMITED   ORDER NOT GUARANTEED     │
└──────────────────────────────────────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐ Everything the grid says about the bound is in the one stats strip above the table, and nothing is added below it: the strip is there whether or not a further page exists, so the table does not move when one does. ┌──────────────────────────────────────────────────────────────────────────────┐

AUTO-LIMITED badge - Shows when the system added a LIMIT
Row count - Displays actual returned rows
Ordering notice - ORDER NOT GUARANTEED, beside the badge, only where a Load More is offered and the statement carries no outer ORDER BY; the full sentence is on its title and in an sr-only span — see Row order across pages
Load More control - The load n more button folded into the row count, where the text (more available) used to sit. It appears when more data is available AND the provider can serve it, and its label names the page size it will fetch ┌──────────────────────────────────────────────────────────────────────────────┐

Query Limiter Utility

┌──────────────────────────────────────────────────────────────────────────────┐ Location: src/lib/db/utils/query-limiter.ts ┌──────────────────────────────────────────────────────────────────────────────┐

import { analyzeQuery, applyQueryLimit } from '@/lib/db/utils/query-limiter';
┌──────────────────────────────────────────────────────────────────────────────┐
// Analyze a query
const info = analyzeQuery('SELECT * FROM users WHERE active = true');
// Returns: { type: 'SELECT', hasLimit: false, hasOffset: false, ... }
┌──────────────────────────────────────────────────────────────────────────────┐
// Apply limit to query
const result = applyQueryLimit('SELECT * FROM users', 500, 0);
// Returns: { sql: 'SELECT * FROM users LIMIT 500', wasLimited: true, ... }
// (OFFSET is only appended when offset > 0, e.g. applyQueryLimit(sql, 500, 500) -> '... LIMIT 500 OFFSET 500')

Supported Query Types

Query Type	Auto-Limit Applied
SELECT	Yes
SELECT behind a leading comment	Yes
SELECT ending in a comment	Yes (the bound is placed before the comment), except a `#` comment — see below
SELECT with LIMIT	No (preserved)
SELECT with UNION	Yes (LIMIT appended after the last statement)
Read-only CTE (`WITH … SELECT`)	Yes
Data-modifying CTE (`WITH … INSERT`/`UPDATE`/`DELETE`/`MERGE`)	No — the bound would apply to the rows the statement writes, committing only part of it
INSERT/UPDATE/DELETE	No
DDL (CREATE, ALTER)	No
┌──────────────────────────────────────────────────────────────────────────────┐
The statement's type is read from its first keyword that is neither whitespace nor a comment
(`src/lib/sql/leading-keyword.ts`), so `-- note`, `/* note */` and MySQL's `# note` before a `SELECT`
are skipped and the limit is still applied. Before this, an annotated `SELECT` was classified as an
unknown statement type and returned every row while the badge reported it as not limited.
An already-bounded annotated statement (`LIMIT n`, `FETCH FIRST n ROWS ONLY`, `SELECT TOP n`) is
still recognised as bounded and is not limited twice.
┌──────────────────────────────────────────────────────────────────────────────┐
A statement leading with `WITH` is typed by the keyword its CTE list operates — the first one
after the list, read by walking the list's grammar in `src/lib/sql/operative-keyword.ts`. A list
element is read in either of the two shapes the supported dialects use: the standard
`name [(cols)] AS [[NOT] MATERIALIZED] (body)`, and ClickHouse's `<expr> AS <alias>`
(`WITH now() AS ts SELECT …`, `WITH 1 AS one SELECT …`) — an element the walker cannot cross ends the
reading, and the statement then loses its bound, which is what happened to ClickHouse's own CTE idiom
between the two fixes. The shapes are told apart by whether the element's head reads as a name and by
what follows its `AS`: a body or one of PostgreSQL's inlining hints can only be the standard shape,
anything else is an alias. Asking
instead whether the text contained `SELECT` let the `INSERT INTO … SELECT` idiom answer for its own
statement: a data-modifying CTE was typed `SELECT`, received a `LIMIT`, and in PostgreSQL that bound
applies to the rows the statement writes — it committed at most 500 of them while the badge
reported a truncated result set. Where the reader cannot cross the CTE list, the statement is not
treated as a `SELECT` and is not bounded: an over-large read can be re-run, a partly committed write
cannot be undone.
┌──────────────────────────────────────────────────────────────────────────────┐
That covers most malformed input (an unclosed body, a missing `AS`, an unterminated comment or
literal) and, deliberately, two well-formed forms the reader does not walk — so they lose their
bound and return every row:
┌──────────────────────────────────────────────────────────────────────────────┐
Form	Why it is not read
------	--------------------
PostgreSQL's `SEARCH` / `CYCLE` clause after a recursive CTE list	sits between the list and the operative keyword; the reader stops at the first word after the list
An expression element aliased with an inlining hint (`WITH col AS materialized SELECT …`)	the hint commits the element to the standard shape, which then expects a body
┌──────────────────────────────────────────────────────────────────────────────┐
Both are reads, so the cost is an unbounded result set rather than a partial write, and both are
pinned by tests in `tests/unit/sql/operative-keyword.test.ts` so the gap stays a decision.
┌──────────────────────────────────────────────────────────────────────────────┐
The reverse also has a narrow case, pinned in the same file: reading an expression element means
knowing where it ends only by its `AS`, so any element the standard shape declines — a head that is
not a name, or an `AS` with no body after it — is re-read as an expression that ends at the first
`AS <name>` at depth 0, however far away. `WITH 2 INSERT INTO users AS u SELECT 1` and
`WITH x AS DELETE, foo AS (SELECT 1) SELECT 1` therefore answer `SELECT` and receive a bound. No
supported dialect accepts such text, so what the server receives is a rejected statement either way
rather than a partly limited write.
┌──────────────────────────────────────────────────────────────────────────────┐

Which dialect the readers are reading

┌──────────────────────────────────────────────────────────────────────────────┐ Every reader above answers from characters alone, and a few characters mean different things in different engines. Where they do, a reader with no dialect has to take one engine's side, and the wrong side moves where a construct ends — a ) that closes a CTE body, a comment that hides a bound, the keyword that types the statement. ┌──────────────────────────────────────────────────────────────────────────────┐ The dialect was always available at the callers: every provider knows its own type, the multi-statement route resolves its provider before it asks, and both client-side execution paths hold the active connection. It is now passed down (src/lib/sql/grammar.ts), and exactly one place maps a database type to grammar facts — the readers receive the resolved record and never see a type id, so no reader can grow a dialect test of its own. ┌──────────────────────────────────────────────────────────────────────────────┐

Character	Established reading	Dialects
`#`	opens a line comment	MySQL, MariaDB, ClickHouse (which also has `#!`), OpenSearch
`#`	ordinary code: a jsonb/geometric operator, an identifier character, a temp-table name, a bind-variable prefix, or a character the parser simply refuses	PostgreSQL, Oracle, Db2 LUW, SQL Server, SQLite, libSQL, DuckDB, Elasticsearch, Trino, Databend, Apache Spark
`q'…'`	a string literal (alternate quoting): the delimiter after the tag opens the body and its partner followed by `'` closes it, so the body carries apostrophes unescaped — `[ ] { } ( ) < >` pair up, any other character closes with itself, either letter case of the tag, and `nq'…'` is the same form for the national character set	Oracle only
`q'…'`	not a form at all — a name followed by an ordinary string, which is what those characters are there	everything else, including the default
`[…]`	a quoted name: everything between the brackets is the identifier (`SELECT [a--b] FROM t` selects a column called `a--b`) and the run does not nest. The doubled `]` this reading honours is SQL Server's escape — SQLite stops at the first `]` and has none, so `[a]]b]` reads as one name where SQLite reads `[a]` and then junk, which it rejects either way	SQL Server, SQLite, libSQL, OpenSearch
`[…]`	an array literal or subscript: it nests (`[[1,2],[3,4]]`), nothing inside it is escaped, and a literal inside it is a literal (`m['a]b']`)	ClickHouse, PostgreSQL, DuckDB, Trino, Databend, Apache Spark
`/* … /* … / … /`	one nesting comment: a `/*` inside a comment opens another and the run continues until the depth returns to zero, so a region that already contains comments can be commented out. A run short of a closer is undeterminable rather than closed early	PostgreSQL, Db2 LUW, SQL Server, ClickHouse, DuckDB, Apache Spark
`/* … /* … / … /`	a flat comment: the first `*/` ends it, and everything after that is the statement's own code	MySQL, MariaDB, SQLite, libSQL, Oracle, Databend, and the default
`//`	opens a line comment, ending at the newline like `--` (on Apache Spark, which has no `//` comment, a `--` comment also ends at a lone carriage return and continues past a backslash before a line feed)	Apache Cassandra (and its relatives), ClickHouse
`//`	ordinary code — an operator the parser refuses (`operator does not exist: integer // integer` on PostgreSQL 18), or a character it rejects outright	everything else, including the default
`r'…'`	a raw string: it takes no escapes and ends at the first quote of its kind	Apache Spark
`/+ … /`	an optimizer hint: `/*+` is code, and inside a comment it opens no level	Apache Spark
`SET` / `RESET` of a session key	asks for confirmation when a `SET` assigns `spark.sql.variable.substitute`, `spark.sql.defaultCatalog` or a `spark.sql.catalog.` key (`=` after the key), when a `RESET` names one of them, and when a `RESET` names no key, which restores every key	Apache Spark
`\` before a line feed in a `--` comment	a backslash right before a line feed continues a `--` comment onto the next line, and one before CR LF does not	Apache Spark
a carriage return in a `--` comment	a lone carriage return ends a `--` comment, and a CR LF pair ends it once	Apache Spark
┌──────────────────────────────────────────────────────────────────────────────┐
Each row was established from an authoritative source: the engine's own documentation, or its
driver's own tokenizer under `node_modules` (node-oracledb accepts `#` inside an identifier; the
SQLite amalgamation classifies `#` as a bind-variable prefix and `[` as a "`[...]` style quoted id").
For the engines that ARE an HTTP endpoint — Elasticsearch, OpenSearch and Trino — the grammar was
asked directly instead: each fact is a statement the live server answered, not an inference. Trino's
first four were probed on 2026-08-20 against 476 (its fifth, `//`, on 2026-08-25), and two of them are
the opposite of what a neighbouring
dialect would have suggested: `SELECT 1 AS a # trailing` is `line 1:15: mismatched input '#'`, so `#`
hides nothing and is `code`; and `SELECT [customer] FROM tpch.sf1.nation` fails with "Column
'customer' cannot be resolved" while `SELECT ARRAY[ARRAY[1,2],ARRAY[3,4]][1][2]` answers `2`, so the
brackets are a nesting subscript and never a name quote.
A rule that could not be established is not guessed from a neighbouring dialect — the dialect stays
at the compatibility default below, and it is listed here rather than left implicit. The default is per
fact, not per dialect: a dialect whose `#` rule is known can still be undecided about its brackets.
┌──────────────────────────────────────────────────────────────────────────────┐
MongoDB, Firestore, Redis, Prometheus, InfluxDB (InfluxQL), Kafka, MQTT, RabbitMQ, etcd, Neo4j, Milvus, Qdrant, Oxia, S3-compatible object storage, Kubernetes, OPC UA, MTConnect, Modbus, NETCONF, SNMP and Sparkplug B are the twenty-one types whose query text is not SQL at all: `NON_SQL_DIALECTS` in `src/lib/sql/grammar.ts` holds exactly those twenty-one, which is what `readsSqlText()` reports on.
Their providers never reach these readers on the query path, and the confirmation gate, which reads whatever is in the editor, asks `readsSqlText()` before applying any span-based rule to their text, so a JSON document, a Redis command, a PromQL expression, an InfluxQL statement, a Kafka read request, an etcdctl command, a Cypher statement, a Milvus request, a Qdrant request, an `oxia client` read command, an AWS CLI read command, a kubectl statement, a NETCONF read request, a Sparkplug read request or an SNMP walk is not judged by a SQL reader that cannot parse it.
┌──────────────────────────────────────────────────────────────────────────────┐
The gate's SQL keyword test still reads MongoDB and Redis text first, as a backstop, which on Redis also asks about a read whose arguments include `update` and then `set` (`docs/BACKLOG.md` U43).
Beyond it, each type whose text is not SQL has a row of its own in `NON_SQL_DESTRUCTIVE_VOCABULARY` in `src/lib/db/destructive-commands.ts`, and a test holds that table to the set `readsSqlText()` reports on.
The MongoDB and Redis rows name the destructive operations the provider can actually dispatch (`deleteOne`/`deleteMany`/`updateOne`/`updateMany` and the `$out`/`$merge` pipeline stages for MongoDB; `DEL`, `FLUSHALL`, `SET`, `CONFIG SET` and the rest for Redis), and the gate reduces the buffer the way the provider would, to one JSON document or, for Redis, to the one command the provider's own `readRedisCommandText()` reads (each line is a command, and a second one is refused in the editor before anything is sent), before looking a name up in it.
Text it cannot read as a command at all is not treated as safe: mongosh syntax, a half-typed document or a broken JSON command body asks.
Before that table existed the answer for both types was a bare `false`, so a `FLUSHALL` and a `deleteMany` ran with no confirmation while a `DELETE FROM` on every SQL engine asked.
The Prometheus and Kafka rows name no operation, the etcd row names what `guard.ts` classifies, the Neo4j row is a fourth that names no operation and decides alone, and each of the four is the gate's whole answer, the rows the keyword test does not read in front of: PromQL has no statement that writes, its editor text only ever reaches `POST /api/v1/query`, and a metric may legally be named `update`, `delete` or `drop`, which the keyword test read as a write; a Kafka read request only reads, and a topic may be named any of those too; etcd's text is read by `guard.ts` over `commands.ts`, the provider's own parser, so a key named `update` or `drop` is data and never a SQL keyword; and Neo4j's provider refuses every write before it is sent - its read policy denies every word that writes and its statement gate classifies the statement through `EXPLAIN`, failing closed - so there is no write left that an operation name would have to catch.
The InfluxDB (InfluxQL) row is read by the InfluxQL policy the provider runs (`src/lib/db/providers/timeseries/influxdb/influxql-policy.ts`), so what asks and what runs are one reading: an allowed statement only reads, so nothing asks, and what the policy refuses the editor refuses before anything is sent.
InfluxDB 3 (SQL) is not in that set: its text is SQL, read under the DataFusion row of `SQL_GRAMMARS`.
The Oxia row names no operation and, like the etcd row, is the gate's whole answer: Oxia's text is read by `guard.ts` over `commands.ts`, the provider's own parser, so a key named `update` or `drop` is data and never a SQL keyword.
The S3 row names no operation and, like the Oxia row, is the gate's whole answer: S3's text is read by `console/guard.ts` over `console/commands.ts`, the provider's own parser, so a key named `delete` or `drop` is data and never a SQL keyword, and a command is never posted for an AI safety analysis, because it names buckets and keys.
The Kubernetes row names no operation and, like the S3 row, is the gate's whole answer: kubectl text is read by `console/guard.ts` over `console/commands.ts`, the provider's own parser, so an object named `delete` or `drop` is data and never a SQL keyword, and a statement is never posted for an AI safety analysis, because it names namespaces and objects.
The embedded LibreDB is not in that set: its text is read as SQL, and its undecided grammar facts are rows in the table below.
┌──────────────────────────────────────────────────────────────────────────────┐
Fact	Undecided, so left at the default	Established, and it happens to equal the default
------	-----------------------------------	--------------------------------------------------
`#`	Couchbase, Druid, the embedded LibreDB provider	—
`q'…'`	nobody	everything except Oracle: the form is Oracle's alone, so "not a literal" is the correct reading for the rest
`[…]`	MySQL, Oracle, Db2 LUW, Elasticsearch, Couchbase, Druid, LibreDB	SQL Server, SQLite, libSQL and OpenSearch, whose rule the default already applied
`/* … */` nesting	Couchbase, Druid, LibreDB	MySQL, SQLite, libSQL, Oracle, Elasticsearch, OpenSearch, Trino and Databend, whose flat rule the default already applied — each established from its own source rather than assumed to agree
`//`	Elasticsearch, OpenSearch, Couchbase, Druid, LibreDB	PostgreSQL, MySQL, SQLite, libSQL, DuckDB, Oracle, Db2 LUW, SQL Server and Trino, each refused on a live server: `SELECT 1 // note` is an error there, so "not a comment" is the reading the default already applied; Databend, whose lexer has `//` as an operator token; Apache Spark, whose lexer reads `/` as division
┌──────────────────────────────────────────────────────────────────────────────┐
The distinction is visible in `src/lib/sql/grammar.ts` too: an established fact is written out in that
dialect's row, an undecided one is written `DEFAULT_SQL_GRAMMAR.<fact>`.
┌──────────────────────────────────────────────────────────────────────────────┐
The bracket row is the one where a default costs something, and PostgreSQL is why it is worth
establishing rather than accepting: left at the name reading, a nested array or a subscript key
containing a `]` lost its bound there — and, since #297, also asked for confirmation, because the same
undeterminable run is text the safety gate cannot read. A prompt on everyday syntax teaches operators
to click the gate away, which costs more than the missed bound, so the rule was established from the
manual (4.2.3 Subscripts, 4.2.12 Array Constructors). MySQL, Oracle and Db2 LUW stay undecided: `[` is not a
name quote in any of them (`SELECT 1 AS [a] FROM SYSIBM.SYSDUMMY1` is SQLCODE -104 on Db2 12.1), but none has a subscript rule to read it under instead, and reading one
engine's rule off another's is what this channel exists to stop. The direction there is safe — a run
the name reading cannot close is reported as undeterminable, an undeterminable end is never cut, so
the cost is an unbounded read and never a misplaced clause — and those three dialects do not write
bracket runs in everyday SQL. See
Text the reading cannot resolve asks, and says so.
┌──────────────────────────────────────────────────────────────────────────────┐
A call that names no dialect keeps today's reading, and that is a decision with its own tests, not
an accident: `#` opens a comment unless the next character makes a PostgreSQL operator (`#>`, `#>>`,
`#-`, `##`) — neither of the honest readings, but what this folder did for every engine before the
channel existed, kept so that the fixtures written for #275, #280, #287, #291 and #294 keep asserting
the behaviour they were written for. Block comments do not nest under the default for the same reason.
The default has no alternate-quoting form either, which is the
same reading every non-Oracle dialect gets and the correct one for all of them.
┌──────────────────────────────────────────────────────────────────────────────┐
One reading is deliberately not the dialect's, and it is the only such override in this folder:
`#` in a statement's leading trivia is skipped as a comment on every dialect
(`src/lib/sql/leading-keyword.ts`). No dialect here can open a statement with `#` — `#tmp`, `#>` and
`ID#` are all mid-statement forms — so skipping the run only ever changes which syntax error the
server reports, while reading it as code would take the bound back off a `# note`-led SELECT on
PostgreSQL and SQL Server. Every other fact that reader consults, including where a block comment
ends, is the dialect's own.
┌──────────────────────────────────────────────────────────────────────────────┐
The alternate-quote tag has to start a word: Oracle's lexer reads a name greedily, so `freq'x'` is
a name followed by an ordinary string there, and this reader answers the same. That makes the tag the
one span in `src/lib/sql/spans.ts` whose reading depends on the character before it, so callers pass
a whole statement (or a prefix of one), never a suffix.
┌──────────────────────────────────────────────────────────────────────────────┐
What changes for a caller that does name one:
┌──────────────────────────────────────────────────────────────────────────────┐
Statement	Dialect	Before
-----------	---------	--------
`WITH t AS (` + `#- note )` + `SELECT 1) DELETE FROM users`	MySQL	typed `SELECT`, bound appended to a `DELETE`
`SELECT * FROM t # LIMIT 10`	MySQL	the commented-out bound read as real, statement unbounded
`SELECT * FROM #tmp`	SQL Server	end not cuttable, appending branch declines
`SELECT * FROM EMP WHERE ID# = 1`	Oracle	not bounded
`WITH t AS (SELECT q'{it's}' …) SELECT * FROM t`	Oracle	the apostrophe inside the literal opened a string, the CTE list could not be read, not bounded
`SELECT q'[it's a -- note )]' FROM dual`	Oracle	the `--` inside the literal read as a trailing comment, so the bound was inserted inside the literal and the statement was reported limited
`SELECT * FROM users # daily`	ClickHouse	not bounded
`SELECT meta #> '{a}' FROM docs`	PostgreSQL	bounded (the default already read this correctly)
`WITH m['a]b'] AS v SELECT v`	ClickHouse	the `]` inside the key ended the bracketed run, the CTE element could not be crossed, not bounded
`SELECT [[1,2],[3,4]] AS a FROM t`	ClickHouse	the closing `]]` read as an escape, so the run never closed and the end was not cuttable, not bounded
`SELECT [it's] FROM users`	SQL Server, SQLite	bounded (the name reading is these dialects' own)
`WITH t AS (` + `/* a /* b / ) SELECT 1 /` + `… ) INSERT INTO …`	PostgreSQL, ClickHouse	the comment closed at the inner `*/`, so the `)` after it ended the CTE body and the statement typed `SELECT` — a bound appended to an INSERT
`/* a /* b / x / SELECT id FROM logs`	PostgreSQL, SQL Server, ClickHouse	the word after the inner `*/` typed the statement, so it was not bounded
`SELECT /* a /* b / DISTINCT / name FROM t`	SQL Server	`TOP` spliced after a `DISTINCT` that is inside the comment — i.e. inside the comment, so the statement ran unbounded while reporting a limit
`/* a /* b */ SELECT id FROM logs`	PostgreSQL, SQL Server, ClickHouse	the comment closed at its only `*/` and the statement read as an ordinary SELECT
┌──────────────────────────────────────────────────────────────────────────────┐

Where the bound is placed

┌──────────────────────────────────────────────────────────────────────────────┐ The clause is inserted between the statement and its trailing trivia — whitespace, comments and the terminating ; — and the trivia is re-attached verbatim. A statement carrying no trailing trivia is emitted exactly as it was before this rule existed. ┌──────────────────────────────────────────────────────────────────────────────┐

Statement	Emitted SQL
`SELECT * FROM t`	`SELECT * FROM t LIMIT 500`
`SELECT * FROM t;`	`SELECT * FROM t LIMIT 500;`
`SELECT * FROM t -- note`	`SELECT * FROM t LIMIT 500 -- note`
`SELECT * FROM t; -- note`	`SELECT * FROM t LIMIT 500; -- note`
`SELECT * FROM t /* note */`	`SELECT * FROM t LIMIT 500 /* note */`
┌──────────────────────────────────────────────────────────────────────────────┐
Appending after the trivia instead put the bound inside a trailing line comment: the engine ran
the statement unbounded while the badge reported it as capped, and the re-appended `;` ended up
inside the comment as well.
┌──────────────────────────────────────────────────────────────────────────────┐

An error that names the bound

┌──────────────────────────────────────────────────────────────────────────────┐ The engine parses the statement Studio sends, bound included, so a syntax error in the user's text can be reported at the bound: SELECT * FROM D_PARENT WHERE is sent as SELECT * FROM D_PARENT WHERE LIMIT 501, and Snowflake reads LIMIT as a column and answers syntax error line 1 at position 35 unexpected '501'. When an engine's syntax error points at a word or number of the clause Studio added (LIMIT, TOP, OFFSET or the count, spelt as Studio wrote it), POST /api/db/query, POST /api/db/multi-query (the script's bounded last statement) and POST /api/db/transaction add one sentence after the engine's message, once, keeping its class and status: ┌──────────────────────────────────────────────────────────────────────────────┐ Studio added the clause LIMIT 501 to this statement to bound its rows, so a position or token this error names may be in that clause rather than in your text. ┌──────────────────────────────────────────────────────────────────────────────┐ The sentence is plain text, since the results panel shows an error message as written. Only the shapes in which an engine names the token it stopped at count: at or near "LIMIT" (PostgreSQL), near 'LIMIT 501' (MySQL, the quoted rest of the statement must start with the clause), near "LIMIT" (SQLite), near 'TOP' and near the keyword 'TOP' (SQL Server), unexpected '501' (Snowflake) and failed at position 38 (501) (ClickHouse). An error that merely contains the word or the number gets no sentence: PostgreSQL's column "limit" does not exist or invalid input syntax for type integer: "foo 501", MySQL's Unknown column 'limit', ClickHouse's list of expected tokens or a message quoting the statement sent. An engine that reports the clause in another shape (Oracle's ORA-00933, for one) gets none either; a missing sentence leaves the engine's message as it was, while a wrong one would blame the clause for an error in the user's text. ┌──────────────────────────────────────────────────────────────────────────────┐ The engine's own message is never rewritten, and its positions are not remapped: they count the text that was sent. ┌──────────────────────────────────────────────────────────────────────────────┐

Clauses that must follow the bound

┌──────────────────────────────────────────────────────────────────────────────┐ Some dialects have a clause that is written after the row bound and refused before it (#1398). The dialect grammar declares them: AS OF [AT LEAST] <t> for Materialize's postgres route, ALLOW FILTERING, BYPASS CACHE and USING TIMEOUT <duration> for the cassandra type-id (Apache Cassandra and ScyllaDB), FORMAT <name> for Databend, FOR UPDATE [NOWAIT | WAIT <n>] for Snowflake and OFFSET n for Apache Spark. The bound is placed before the declared run, which is re-attached verbatim: ┌──────────────────────────────────────────────────────────────────────────────┐

Statement	Emitted SQL
`SELECT * FROM ui_mv AS OF AT LEAST 0` (Materialize)	`SELECT * FROM ui_mv LIMIT 500 AS OF AT LEAST 0`
`SELECT * FROM t BYPASS CACHE` (ScyllaDB)	`SELECT * FROM t LIMIT 500 BYPASS CACHE`
`SELECT * FROM t USING TIMEOUT 5s` (ScyllaDB)	`SELECT * FROM t LIMIT 500 USING TIMEOUT 5s`
`SELECT * FROM t ALLOW FILTERING` (CQL)	`SELECT * FROM t LIMIT 500 ALLOW FILTERING`
`SELECT number FROM numbers(10) FORMAT CSV` (Databend)	`SELECT number FROM numbers(10) LIMIT 500 FORMAT CSV`
`SELECT * FROM t ORDER BY a OFFSET 10` (Apache Spark)	`SELECT * FROM t ORDER BY a LIMIT 500 OFFSET 10`
`SELECT * FROM t -- note` ⏎ `ALLOW FILTERING` (CQL)	`SELECT * FROM t LIMIT 500 -- note` ⏎ `ALLOW FILTERING`
┌──────────────────────────────────────────────────────────────────────────────┐
A comment the writer put between the code and the clause stays between the bound and
the clause, its own closing newline kept, so the bound is written in code rather than
inside the comment - and a commented-out bound behind one (`-- LIMIT 5` ⏎
`ALLOW FILTERING`) does not answer for the statement.
┌──────────────────────────────────────────────────────────────────────────────┐
The same reading answers "is this statement already bounded": `LIMIT 10 BYPASS CACHE` is an
existing bound followed by a declared clause, so it is detected, honoured and never doubled. On
the old path it read as unbounded — the bound was not at the end — and a second one was appended,
which the engine refuses.
┌──────────────────────────────────────────────────────────────────────────────┐
A dialect with no declared clause keeps today's answer for its words: the bound is appended after
them and the engine refuses the statement, which it refused before the bound was there too. The
clause patterns accept word-runs, a number, a single-quoted literal or one bare token as the
argument, so a clause-shaped run inside a literal (`… WHERE note = 'BYPASS CACHE'`) and an alias
followed by statement text (`SELECT a AS of FROM t`) do not read as the clause.
┌──────────────────────────────────────────────────────────────────────────────┐
The same reading of the end answers "is this statement already bounded". The `LIMIT n`,
`FETCH FIRST n ROWS ONLY` and `OFFSET n` probes are anchored at the end of the statement
(`src/lib/sql/statement-end.ts`), so:
┌──────────────────────────────────────────────────────────────────────────────┐

a bound written inside a trailing comment (SELECT … -- LIMIT 10) is not mistaken for a real one, and the statement is bounded;
a real bound followed by a comment (SELECT … LIMIT 10 -- deliberate) is still detected, honoured and never doubled — a second bound is a syntax error, not extra rows. ┌──────────────────────────────────────────────────────────────────────────────┐ Reading where a statement ends and cutting it there are separate answers, because they are not equally risky: a probe that stops early merely finds no bound, while a clause inserted early lands in the middle of the statement and the server rejects it outright. Three shapes are therefore read but not cut, and a statement carrying one is returned untouched with wasLimited: false — the second branch of "never true alongside a bound the engine cannot see": ┌──────────────────────────────────────────────────────────────────────────────┐ | Shape | Why the cut is refused | |-------|------------------------| | An unterminated comment or literal, or a quote behind an odd backslash run (… WHERE name = 'O\'Brien') | MySQL escapes with backslashes and PostgreSQL does not, so the two close the literal in different places; inserting on a guess puts the bound after the statement's own ; | | On a dialect that nests block comments, a comment carrying one opener too many (/* a /* b */ SELECT 1) | the run never returns to depth zero, so it is an unterminated comment there — the same refusal as the row above, reached by a rule that is the dialect's own (#300) | | A # run at the end of the statement (SELECT * FROM #tmp, … WHERE ID# = 1, SELECT flags # 5), when no dialect was named | # is a comment marker in MySQL and ClickHouse and ordinary code elsewhere — a T-SQL temp table, an Oracle identifier, a PostgreSQL XOR — and nothing in the text tells them apart (#note and #tmp are the same characters) | | A bracketed run that never closes — an unterminated name (SELECT [abc) or, under the subscript reading, an array short of a bracket (SELECT [[1,2] AS a) or one holding an unresolvable literal | a run cannot be more certain than what it contains; guessing where it ends puts the bound inside a name or a literal, which is the corrupted-statement shape rather than a missed bound | ┌──────────────────────────────────────────────────────────────────────────────┐ Each is a deliberate loss of a bound: appending after everything, as the limiter used to, was valid SQL for the dialect in which those characters are code, and is exactly what put the clause inside a trailing comment for the dialect in which they are not. Not bounding costs an over-large read the user can re-run, which is the trade every reader in src/lib/sql/ makes. ┌──────────────────────────────────────────────────────────────────────────────┐ Rows one and three cost one thing more since #297, because a run that never closes is also text the safety gate cannot read: such a statement asks for confirmation before it runs (see Text the reading cannot resolve asks, and says so). The # row is the exception — a # line comment closed by the end of the input is terminated, so that shape loses its bound silently, as it always did. ┌──────────────────────────────────────────────────────────────────────────────┐ The # row applies to the dialect-less reading only. A caller that names its dialect has told the two apart, so the refusal is lifted in whichever direction that dialect requires: MySQL and ClickHouse cut before the comment, PostgreSQL, Oracle, SQL Server and SQLite read the run as the statement's own text and cut after it. Every caller in this project names one, so the row describes the compatibility default rather than everyday behaviour. ┌──────────────────────────────────────────────────────────────────────────────┐ A # comment with code after it on a later line is unaffected. Where the cut is refused, the statement's text is still read to its very end (trailing whitespace and ; aside), so a bound written after a # is still found and never doubled — which is also why MSSQL's TOP splice, writing into the head, keeps bounding SELECT * FROM #tmp even without a dialect. Under the dialect-less reading that is not airtight — trailing trivia written after an existing bound hides it from the end-anchored probe, so a TOP is spliced alongside an OFFSET … FETCH that SQL Server rejects — and naming the dialect is what closes it for a hash: # is never a comment in T-SQL, so the probe sees the bound that is there. Every other route to a refused cut reached that provider the same way, and what closes those is the rule in the next paragraph. Likewise the one shape that stays wrong without a dialect, a bound commented out with # (… # LIMIT 10), is read correctly under MySQL's grammar: the commented-out clause is not a clause, and a real bound is added before it. ┌──────────────────────────────────────────────────────────────────────────────┐ A refused cut costs more than the bound, and this is the part that is easy to miss: it also makes every already-bounded probe unreliable. Those probes read the statement's own text, and where the cut is refused that text is the terminator strip — trailing whitespace and ; removed and nothing else — so a real bound written before a trailing comment sits away from the end anchor and reads as absent. For a caller that responds to "not bounded" by leaving the statement alone, that is harmless. For a caller that responds by ADDING a clause, it is not: the clause lands beside a bound the probe could not see, and engines reject the pair outright rather than returning too many rows. So a provider that adds its own clause has to treat those probes as non-authoritative wherever the cut was refused. MSSQL is the one that adds a clause the refusal does not otherwise stop — TOP n goes into the head, which no trailing comment can reach — and it now declines whenever such a statement mentions an OFFSET or a FETCH at all, blunt on purpose, since a page cannot be ruled out from text nothing can read (#293). ┌──────────────────────────────────────────────────────────────────────────────┐ Providers that append a clause of their own follow the same rule: Oracle's FETCH FIRST and OFFSET … FETCH NEXT, Db2's FETCH FIRST n ROWS ONLY or OFFSET m ROWS FETCH NEXT n ROWS ONLY past the first page, and MSSQL's OFFSET … FETCH NEXT pagination branch. MSSQL's SELECT TOP n splices into the head, which no trailing comment can reach, so it keeps bounding a statement whose end may not be cut — with the one exception above. MSSQL also recognises a page form of its own that the shared probes above do not, OFFSET n ROWS with no FETCH tail; it is read in that provider, because the form is T-SQL's alone and no other dialect's probes should move for it. ClickHouse's refusal to rewrite a statement ending in FORMAT/SETTINGS, and Druid's refusal to rewrite one ending in an OFFSET, both read the same end — otherwise a trailing comment would hide the clause from them and the bound placed before that comment would turn a working statement into a server-side syntax error. ┌──────────────────────────────────────────────────────────────────────────────┐

Multi-statement runs

┌──────────────────────────────────────────────────────────────────────────────┐ In standalone studio, a script holding several statements is split (src/lib/sql/statement-splitter.ts) and sent to POST /api/db/multi-query, which bounds the last statement only, and only when it is a SELECT. Embedded in a host application the query goes to the host's own executor and none of this route applies. ┌──────────────────────────────────────────────────────────────────────────────┐ Whether that last statement is a SELECT is the shared reading described above (isSelectQuery), not a pattern belonging to the route. It used to be /^\s*SELECT\b/i, which tolerates whitespace but not a comment — and the splitter keeps each statement's leading comments — so a final SELECT behind a -- note was not recognised and reached the engine unbounded, the one place the comment-tolerant classifier had not been adopted (#281). The CTE typing applies here too: a final read-only CTE is bounded, a final data-modifying one is not. The route resolves its connection before it asks, so the reading is done under that connection's dialect and this route and the provider that runs the statement cannot disagree about what the statement is. ┌──────────────────────────────────────────────────────────────────────────────┐ The splitter reads spans through the shared reader (src/lib/sql/spans.ts) under the caller's grammar, so it agrees with every other reader here about which ; is code. It used to inline its own scan and know none of the dialect facts, and unlike the other readers its disagreement was not a missing bound: this route RUNS what it returns. Measured on postgres 18, /* a /* b */ ; DROP TABLE t; -- */ SELECT 1 is one read (block comments nest there), and the flat reading cut it into three whose second was a bare DROP TABLE t — which the route executed. Measured on MySQL, whose comments are flat, that same text really does drop the table, so the fragment is honest there and the confirmation gate is what must see it. Callers pass the connection's dialect (this route, and the editor's multi-statement decision); a call that names none keeps the compatibility grammar, the same stated default the rest of src/lib/sql applies. ┌──────────────────────────────────────────────────────────────────────────────┐

Procedural bodies, separator lines and batches (#1312)

┌──────────────────────────────────────────────────────────────────────────────┐ A ; that is code is not always the end of what the engine receives. The grammar's script facts say where it is not, and the splitter reads them: ┌──────────────────────────────────────────────────────────────────────────────┐

Dialect	Body that holds its `;`	Separator line	One request carries
Oracle	a PL/SQL unit: an anonymous block (`DECLARE` or `BEGIN` first) and `CREATE [OR REPLACE] PROCEDURE`, `FUNCTION`, `PACKAGE [BODY]`, `TRIGGER`, `TYPE BODY`, read to its matching `END`; the `;` after that `END` stays in the text, because Oracle refuses the unit without it	`/` alone on a line (SQL*Plus)	one statement
SQL Server	none needed: the batch holds every body	`GO` alone on a line (sqlcmd, SSMS)	the whole batch between `GO` lines
SQLite, libSQL	`CREATE [TEMP] TRIGGER … BEGIN … END`; a bare `BEGIN` is still a transaction	none	one statement
every other dialect	none: PostgreSQL's routine bodies are `$$` literals the span reader already holds	none	one statement
┌──────────────────────────────────────────────────────────────────────────────┐
Inside a body, `CASE … END` closes a frame and `END IF` / `END LOOP` are skipped, and a declaration
section shares its block's `END`. A compound trigger's section holds its timing points
(`BEFORE STATEMENT IS BEGIN … END BEFORE STATEMENT;`) to its own last `END`, a `<<label>>` before a
block is read past, and a call spec (`AS LANGUAGE …`, `AS EXTERNAL …`, `AS MLE MODULE …`) has no body,
so the `;` after it ends it. A unit whose frames still never close runs to the next `/` line or the end
of the input: one statement too long, never a fragment stored INVALID. `GO 5`, sqlcmd's repeat count, is
not a separator; it reaches the server, which refuses it (measured on SQL Server 2025 RTM-CU9: `Incorrect syntax near 'GO'.`).
┌──────────────────────────────────────────────────────────────────────────────┐
Two shapes the reader does not model, both recorded rather than guessed at: an unquoted, unqualified column named
`end` inside a SQLite trigger body closes the body early (quote it, as `"end"`), and an Oracle
`WITH FUNCTION` read is not a unit and is still cut at its inner `;`.
┌──────────────────────────────────────────────────────────────────────────────┐
Measured in the end-to-end pass of 2026-10-03/04 before these facts existed: Oracle 26ai Free stored a
procedure cut at its inner `;` INVALID (PLS-00103) while this route reported its first fragment
`success`, SQLite and libSQL refused a trigger as incomplete input, and SQL Server 2025 answered
`Must declare the scalar variable "@x"` for `DECLARE @x INT = 5; SELECT @x * 2`, because each fragment
was its own request on a pooled connection.
┌──────────────────────────────────────────────────────────────────────────────┐
When the script's last unit is a T-SQL batch of several statements, its last statement is bounded
when it is a read and spliced back in place, so `SELECT 1; SELECT * FROM big` is bounded as it was when
the two were separate requests. The exception is a batch that is a module definition: its first
statement is `CREATE`, `ALTER` or `CREATE OR ALTER` of a procedure, function, trigger or view, which
T-SQL requires first in its batch, and everything after it is the body the server stores, where a
`TOP` would change the procedure rather than the result; that batch is sent as written. SQL Server's
provider carries every result set of a batch, and the route shows the last one with rows, the same rule
it applies across a script's statements. A batch's earlier result sets are not sent back one by one,
which costs nothing the grid showed before: it shows one result, and `statements[i]` is now the
batch. Across a `GO`, a `#temp` table survives only if the next batch borrows the same pooled
connection, which nothing guarantees (`docs/BACKLOG.md` D92).
┌──────────────────────────────────────────────────────────────────────────────┐
"Run the statement at the cursor" runs a whole body (a PL/SQL unit, a SQLite trigger, a T-SQL module
batch), but in a T-SQL batch that is a run of statements it still runs only the caret's own statement,
so a caret on a `SELECT` never sends the `DELETE` written after it. A script whose statements share a
`DECLARE @x` is run by selecting it, or by running the whole editor.
┌──────────────────────────────────────────────────────────────────────────────┐
The confirmation gate keeps reading the `;`-statements inside a body, and reads INTO a statement led
by a control-flow word (`BEGIN`, `DECLARE`, `IF`, `ELSE`, `ELSIF`, `THEN`, `WHILE`, `LOOP`, `FOR`,
`EXCEPTION`, `WHEN`) or a `<<label>>`: there it looks for every dangerous keyword anywhere in the
statement's code, so `BEGIN DELETE FROM emp; END;`, `BEGIN IF c THEN DELETE FROM emp; END IF; END;` and
T-SQL's `IF @@ROWCOUNT > 0 DELETE FROM t` ask. Dynamic SQL in such a block (`EXECUTE IMMEDIATE`,
`DBMS_SQL`) asks whatever its text says, because the text is a literal the gate cannot read into, so
`BEGIN EXECUTE IMMEDIATE 'DROP TABLE t PURGE'; END;` asks. Two shapes that only look like a write are
skipped: a T-SQL variable (`@alter`, `@delete`) and a cursor's `FOR UPDATE` lock clause. Before
procedural bodies were read, Oracle refused the
fragment `BEGIN DELETE FROM emp` and nothing ran; now the whole block runs, so the gate has to read
into it. What a procedure call does (`EXEC sp_cleanup`, `BEGIN p; END;`) is the server's, and it does
not ask, as before. MySQL, Db2 and Trino compound statements are not read yet (`docs/BACKLOG.md` S7).
┌──────────────────────────────────────────────────────────────────────────────┐
Last-only is that route's own policy, and it leaves a hole this section does not close: a non-final
`SELECT` runs exactly as written, and its entire result set travels back in `statements[i].rows`.
That is also what the grid displays whenever the final statement returns no rows of its own
(`SELECT * FROM huge; INSERT INTO t VALUES (1)`), since the response picks the last result that has
rows. Noted here rather than claimed closed.
┌──────────────────────────────────────────────────────────────────────────────┐
Two indicators do not follow the bound onto this route, because it returns no pagination metadata at
all: the AUTO-LIMITED badge does not appear for a multi-statement run, and **Load More is not
offered** even when rows were capped. The bound itself is real and applied — re-run the final statement
on its own to page through it.
┌──────────────────────────────────────────────────────────────────────────────┐

Where the page size comes from

┌──────────────────────────────────────────────────────────────────────────────┐ The bound a statement runs under is an execution option, never text the product wrote into the statement for you. ┌──────────────────────────────────────────────────────────────────────────────┐ It was not always. generateTableQuery used to emit the tree click's preview cap as SQL — SELECT * FROM t LIMIT 50, FETCH FIRST 50 ROWS ONLY on Oracle, SELECT TOP 50 on SQL Server — and that is what disengaged pagination end to end (#816). A statement carrying its own bound is returned untouched by the limiter, which discards the requested offset with it, while prepareQuery still reports the caller's default as the limit. Measured on PostgreSQL, asking for offset 50: ┌──────────────────────────────────────────────────────────────────────────────┐

in : SELECT * FROM public.orders LIMIT 50;
out: SELECT * FROM public.orders LIMIT 50;              wasLimited=false appliedOffset=0
in : SELECT * FROM public.orders;
out: SELECT * FROM public.orders LIMIT 500 OFFSET 50;   wasLimited=true  appliedOffset=50

┌──────────────────────────────────────────────────────────────────────────────┐ Nothing downstream could tell a cap the product generated from a bound the user typed, because both were text in the same string, and any rule that read intent out of the text would be a guess. So the ambiguity was removed at the source: ┌──────────────────────────────────────────────────────────────────────────────┐

Statement	Where its bound lives	Page two
A tree click's preview	`options.limit = 50`, sent with the request	Offered, if the provider can serve it
A statement the user ran with no bound	`options.limit = 500` (the route's default)	Offered, if the provider can serve it
A statement the user wrote `LIMIT n` into	the text, and it is left alone	Not offered: the bound is the user's, and it is hard
A statement the limiter declined to rewrite	nowhere; it runs unbounded	Not offered: there is no bound to advance
┌──────────────────────────────────────────────────────────────────────────────┐
The generated preview statement is therefore something you can copy out of the editor and run
elsewhere unchanged — which it is worth knowing means every row, with no cap, when you do.
┌──────────────────────────────────────────────────────────────────────────────┐

Destructive-Statement Confirmation

┌──────────────────────────────────────────────────────────────────────────────┐ Before a destructive statement runs, studio opens a confirmation dialog carrying an AI risk analysis (src/components/QuerySafetyDialog.tsx). Which statements reach it is decided by the same reading the auto-limiter uses, so the two cannot disagree about where a statement begins. ┌──────────────────────────────────────────────────────────────────────────────┐

Statement	Confirmation
`DELETE` / `DROP` / `TRUNCATE` / `ALTER` / `GRANT` / `REVOKE` / `UPDATE` / `MERGE`	Yes
A PostgreSQL `DO` block (`DO $$ … $$`, `DO '…'`, `DO LANGUAGE …`), whatever its body does, since the gate does not read the body	Yes
The `DO` of `ON CONFLICT DO NOTHING` or `CREATE RULE … DO INSTEAD`, which a word follows	No
Any of those behind a comment (`-- note`, `/* note */`, MySQL's `# note`, several stacked)	Yes
A `WITH` whose CTE list precedes one (`WITH x AS (…) DELETE FROM …`)	Yes
A `WITH` whose CTE list contains an `UPDATE … SET` (PostgreSQL's data-modifying CTE)	Yes
A statement carrying a run the reader cannot resolve (`SELECT '\';` and anything after it, an unterminated comment, literal, name or array)	Yes, and the dialog says why
Any of those behind a nested comment (`/* a /* b / c / DROP TABLE users`), on a dialect that nests — PostgreSQL, SQL Server, ClickHouse	Yes: the comment is read whole, so the statement's own keyword is the one that answers
The same text on a dialect that does not nest — MySQL, MariaDB, SQLite, Oracle	No, because there the comment closed at the first `*/` and what follows it is text the engine rejects outright
`SELECT`, including one naming a destructive keyword in a string or a comment	No
┌──────────────────────────────────────────────────────────────────────────────┐
The reading is done under the active connection's dialect on both execution paths, because what
counts as a comment decides what the statement says. On MySQL, a `#` comment inside a CTE list used to
hide the `)` that closes it, so the reader answered with the `SELECT` inside the comment's reach and
the `DELETE` after the list ran with no confirmation at all; under MySQL's grammar the comment is a
comment and the `DELETE` is seen. The other direction matters as much: `SELECT 1 # UPDATE t SET x = 1`
is a commented-out note on MySQL — prompting there would be a false alarm — and is the statement's own
code on PostgreSQL, where those characters are an operator.
┌──────────────────────────────────────────────────────────────────────────────┐
The statement's own keyword is read with `src/lib/sql/operative-keyword.ts` and tested against a
keyword set. It used to be re-derived here as six anchored patterns (`/^\s*DROP\b/i`, …), which
tolerate whitespace but not a comment — so `-- cleanup` above a `DROP TABLE` skipped the dialog
and the statement executed unconfirmed, on both the standalone and the embedded execution path.
Writing a note above a destructive statement is ordinary, and it is exactly where a confirmation
matters most.
┌──────────────────────────────────────────────────────────────────────────────┐
One probe stays deliberately unanchored: a statement whose code contains `UPDATE` followed by
`SET` asks for confirmation whatever its own keyword is. PostgreSQL's data-modifying CTE is
operated by its `SELECT` — which is the honest answer, and the one the limiter needs so it does
not bound the rows a write commits — so anchoring this probe too would take the last check away
from a statement that really does write. It reads the statement's code rather than its text
(`src/lib/sql/words.ts`), so unlike the pattern before it, a read that merely quotes
`'UPDATE t SET x = 1'` or mentions it in a comment no longer prompts.
┌──────────────────────────────────────────────────────────────────────────────┐

Text the reading cannot resolve asks, and says so

┌──────────────────────────────────────────────────────────────────────────────┐ Every other reader in src/lib/sql/ errs toward not acting on text it cannot resolve, because its mistake would be a row bound appended to a write — a partial commit (#287). This gate's costs run the other way: a false prompt costs one click, silence costs an unconfirmed destructive statement. So a statement carrying a run that never closes prompts (#297), and the dialog leads with a distinct notice — "Part of this statement could not be read" — explaining that a quoted, commented or bracketed run never closes, that nothing after that point could be checked, and that this is why it is asking. The notice stays visible beside the AI risk verdict, including a verdict of Safe: that analysis was produced from text whose reading stopped early, so it may not describe what the statement does. ┌──────────────────────────────────────────────────────────────────────────────┐ SELECT '\'; followed by a write is the shape the issue was filed about — '\' closes the string under PostgreSQL's standard_conforming_strings and continues it under MySQL's default, so src/lib/sql/spans.ts reports it as undeterminable rather than guessing, and everything after it was invisible to the reading. It now asks instead of executing. ┌──────────────────────────────────────────────────────────────────────────────┐ The accepted cost, pinned by tests rather than left to be discovered, in two classes: ┌──────────────────────────────────────────────────────────────────────────────┐

A closing quote behind an odd backslash run is reported undeterminable on most rows, and this is the most frequent prompt the rule buys: it covers a literal ending in a backslash (a Windows path) and \' as an escaped apostrophe — which is MySQL's own escape, so an everyday MySQL read such as … WHERE name = 'O\'Brien' asks on every execute. Naming a shipped dialect narrows this one only where the dialect has one fixed reading: the grammar record carries the fact as backslashAlwaysEscapes, and most rows declare it false, MySQL included because NO_BACKSLASH_ESCAPES in sql_mode changes its reading per session. Databend's, BigQuery's, Snowflake's and Apache Spark's rows declare it true, because their lexers escape with a backslash and no setting turns that off, so there 'it\'s' is one closed literal and a backslash before a line feed leaves the literal open. Snowflake's row also declares doubleQuoteBackslashEscapes false, because a backslash inside "..." is part of the name there, so "a\" is one closed name. Apache Spark's row leaves doubleQuoteBackslashEscapes absent, because Spark's lexer pairs a backslash with the next character in both '...' and "...". What does not happen is a prompt for a statement that merely contains a backslash — SELECT 'a\nb' FROM t, … LIKE 'a\_b' and 'C:\\Users\\me' all resolve and run without one.
A bracketed run a dialect at the default bracket reading cannot close. […] is read as SQL Server's quoted name for the dialects with no established subscript rule (MySQL, Oracle, Couchbase, Druid, LibreDB), so a nested run or a key carrying a ] written there is unresolvable and asks. ClickHouse and PostgreSQL read those as subscripts and do not — the PostgreSQL row was established precisely because everyday syntax there (ARRAY[[1,2],[3,4]], j['a]b']) was paying this prompt. Ordinary a[1] and ARRAY[1,2] resolve under every reading. ┌──────────────────────────────────────────────────────────────────────────────┐ Not a cost this rule pays: non-SQL query text. Both execution paths ask about whatever is in the editor, so this predicate is handed MongoDB documents and Redis commands as well. A SQL span reader's verdict about text that is not SQL is not evidence of anything — the escaped quote in {"filter":{"msg":"say \"hi\""}} or in GETRANGE k "a\"b" 0 1 closes perfectly in the grammar the text is actually written in — so the unresolvable-run rule is applied only where readsSqlText() says the dialect writes SQL. Saying "could not be read" about text that reads fine is the false alarm this notice exists to avoid, and a gate operators learn to click through protects nothing. Only the span-based rule is narrowed: the non-SQL vocabulary above still answers for that text, which is why the Redis half of this example is a GETRANGE and not the SET k "a\"b" it used to be — SET is in that vocabulary and now prompts on its own merits, whatever its quoting. ┌──────────────────────────────────────────────────────────────────────────────┐ A fourth class arrives with the nesting fact (#300), and it is the reverse of the ones above — a prompt the dialect adds rather than one it narrows: on PostgreSQL, SQL Server and ClickHouse a block comment carrying one opener too many (/* a /* b */ SELECT 1) never closes, so an ordinary read written that way asks before it runs. That is the fail-safe direction on those engines, where the same text is either an unterminated comment (so the server rejects it) or a comment hiding a statement nobody can see; the flat reading of it — comment closed, SELECT 1 executed — is what a dialect-less caller and the flat dialects still get. ┌──────────────────────────────────────────────────────────────────────────────┐ Naming the dialect narrows the second class — ClickHouse's [[1,2],[3,4]] is a closed run under its own grammar (#295) and unresolvable only to a reader without it — and it lifts a form the default cannot read at all: Oracle's q'{it's}' (#292). It does nothing for the first class, per that bullet. ┌──────────────────────────────────────────────────────────────────────────────┐ Two gaps are known and pinned by tests rather than claimed closed: ┌──────────────────────────────────────────────────────────────────────────────┐
Only UPDATE … SET is looked for inside a read. A write hidden in a CTE body under any other keyword (WITH gone AS (DELETE FROM t RETURNING *) SELECT * FROM gone) does not prompt. Widening the probe to DELETE/INSERT/MERGE would also make every read whose code names one of them prompt, so the asymmetry is inherited from the vocabulary above rather than chosen here.
A statement whose shape cannot be TYPED — an unclosed CTE list such as WITH t AS (DELETE FROM x — does not prompt. This is the boundary of the rule above rather than an exception to it: every character was read, and what they spell is an incomplete statement rather than a run hiding what is written inside it. No dialect accepts the text, so the server rejects it; and the keyword inside the unclosed list is a CTE-body write, which the gap above already does not prompt for even when the list closes. Pinned by tests/components/QuerySafetyDialog.test.tsx ("does not prompt when the statement's shape cannot be typed") so the boundary stays a decision — the shipped rule keys on unresolvable runs, and widening it to every statement the reader cannot type would prompt for an empty editor. ┌──────────────────────────────────────────────────────────────────────────────┐ A third gap sat beside them and is closed (S1). A multi-statement script used to be read as one statement, so SELECT 1; DROP TABLE users did not prompt — its first keyword is the SELECT — while SELECT 1; UPDATE t SET x = 1 did, because the unanchored probe happened to find it. The gate now splits the editor text with the same splitter and the same grammar the runner uses, and asks about each fragment the multi-statement route will run. The split is only performed where the dialect's text is SQL — a ; in a Mongo document or a Redis command separates nothing, so a fragment cut there would be invented (#427). Pinned by tests/components/QuerySafetyDialog.test.tsx ("The gate reads what the RUNNER will run"). ┌──────────────────────────────────────────────────────────────────────────────┐ Four runs bypass the gate on purpose, all on the standalone path (src/hooks/use-query-execution.ts): an EXPLAIN run (see below), a Load-More page of a result the user already has, a playground run (rolled back rather than committed), and anything carrying skipSafety — which is the dialog's own "Execute Anyway" button and the inline row editor, whose UPDATE comes from a grid edit the user has already confirmed. The embedded adapter (src/workspace/hooks/use-query-adapter.ts) has only the force-execute bypass, so every other query a host application runs through it reaches the gate. ┌──────────────────────────────────────────────────────────────────────────────┐

Load More Functionality

When the control appears

┌──────────────────────────────────────────────────────────────────────────────┐ Three things must all hold, and ResultsGrid checks them as one predicate so the control and the ordering notice beside it cannot disagree: ┌──────────────────────────────────────────────────────────────────────────────┐

Condition	Where it comes from	Why
`supportsResultPagination === true`	the connection's `ProviderCapabilities`	Sixteen providers cannot serve page two. Cassandra and Elasticsearch throw on a positive offset; MongoDB, Redis, LibreDB, Prometheus, InfluxDB (InfluxQL), Kafka, MQTT, RabbitMQ, etcd, Neo4j, Milvus, Qdrant, Oxia and S3-compatible object storage answer it with page one. An absent flag reads as unsupported
`pagination.hasMore`	`POST /api/db/query`	Requires the limiter's `PreparedQuery.wasLimited` as well as a row past the page — see below
the surface supplies `onLoadMore`	`BottomPanel`	A result hydrated from an agent run has no statement of its own to page
┌──────────────────────────────────────────────────────────────────────────────┐

User Flow

Execute query → one page displayed, 50 rows from a tree click and 500 from a hand-run statement
Click load n more, in the stats strip beside the row count → the next page, of the same size, appended
Repeat until all data loaded or satisfied ┌──────────────────────────────────────────────────────────────────────────────┐ The control's label names the size it will fetch, read from pagination.limit, because the two page sizes are not interchangeable: a 50-row preview offering "Load More (500 rows)" is a promise the click does not keep. While a page is in flight the control is disabled and reads Loading..., so a second click cannot ask for the same offset twice. ┌──────────────────────────────────────────────────────────────────────────────┐ The control sits in the stats strip rather than in a bar of its own below the table. A footer that came and went with hasMore moved the grid by its own height between queries, and it said a second time what the row count beside it already said. ┌──────────────────────────────────────────────────────────────────────────────┐

API Request

// Initial query — a tree click, which asks for the preview page size
POST /api/db/query
{
  "connection": {...},
  "sql": "SELECT * FROM orders",
  "options": { "limit": 50, "offset": 0 }
}
┌──────────────────────────────────────────────────────────────────────────────┐
// Load More — the SAME page size, read back off the first page's pagination
POST /api/db/query
{
  "connection": {...},
  "sql": "SELECT * FROM orders",
  "options": { "limit": 50, "offset": 50 }
}

┌──────────────────────────────────────────────────────────────────────────────┐ The sql of the second request is the statement that produced the rows already on screen - QueryTab.resultQuery - not whatever is in the editor now. The buffer is rewritten on every keystroke, and a run takes the editor's effective query, which may be only a selection of it; paging the buffer appended another table's rows under these columns and left the tab holding rows from two tables while naming one. ┌──────────────────────────────────────────────────────────────────────────────┐

Response Format

{
  "rows": [...],
  "fields": ["id", "name", ...],
  "rowCount": 50,
  "executionTime": 45,
  "pagination": {
    "limit": 50,
    "offset": 0,
    "hasMore": true,        // wasLimited AND a row past the page came back
    "totalReturned": 50,
    "wasLimited": true      // a row past our injected bound came back (#1440), or the provider cut its result
  }
}

┌──────────────────────────────────────────────────────────────────────────────┐ The response's wasLimited is false for a short result under an injected cap. It becomes true when a further page exists: the statement that runs asks for limit + 1 rows, the extra row is never answered, and its arrival is the proof (#1440). A page that ends exactly full is the last page and reports hasMore: false. Query and transaction responses follow the same rule. ┌──────────────────────────────────────────────────────────────────────────────┐ hasMore is wasLimited && rows.length > limit over the probed statement, with wasLimited read from the limiter alone, and the first half is load-bearing. A provider that bounds its own result reports that bound on the response's wasLimited too (#1085, section 5.4), and it never sets hasMore. We can only offer page two of a bound this layer applied, because only then do we know how to advance it. A statement returned untouched — one carrying the user's own LIMIT 50, or a ClickHouse query whose trailing FORMAT/SETTINGS clause the limiter declines to cut into — runs identically at every offset, so a Load More over it would append the rows already on screen. ┌──────────────────────────────────────────────────────────────────────────────┐

Row order across pages

┌──────────────────────────────────────────────────────────────────────────────┐ Studio does not inject, require or suggest an ORDER BY in order to paginate. On a table of millions of rows a sort can be fatal, and paying for it is the user's call. ┌──────────────────────────────────────────────────────────────────────────────┐ With an ORDER BY, pages are stable. Without one, rows may repeat or be skipped between pages. That is accepted rather than prevented, and it is not an error — but it is also not allowed to look like the ordered case: where a Load More is offered and the statement carries no outer ORDER BY, the stats bar says so once, beside the AUTO-LIMITED badge. Whether the statement orders its result is read by src/lib/sql/result-order.ts, under the connection's own grammar, so an ORDER BY inside a literal, a comment, a subquery or a window function's OVER (…) does not count as one. ┌──────────────────────────────────────────────────────────────────────────────┐ SQL Server deserves a note. Page one is SELECT TOP n and page two is OFFSET … FETCH NEXT with an injected ORDER BY (SELECT NULL), which exists only because T-SQL requires an ORDER BY before OFFSET and promises nothing about order. Two structurally different statements, so an unordered SQL Server query overlaps more readily than an unordered PostgreSQL one. Accepted under the same rule. ┌──────────────────────────────────────────────────────────────────────────────┐

Load All Option

┌──────────────────────────────────────────────────────────────────────────────┐ For advanced users, a "Load All" button triggers an unlimited query (max 100K rows) with a confirmation dialog: ┌──────────────────────────────────────────────────────────────────────────────┐

┌──────────────────────────────────────┐
│  Load all results?                   │
│                                      │
│  This may slow down your browser.    │
│  Max 100K rows will be loaded.       │
│                                      │
│  [Cancel]          [Load All]        │
└──────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────────────────┐

Result-Level Signals

┌──────────────────────────────────────────────────────────────────────────────┐ Two things a result can say about itself, both optional and both filled by the provider that ran the statement (issue #273). Neither has anything to do with auto-limiting. ┌──────────────────────────────────────────────────────────────────────────────┐

Engine Warnings

┌──────────────────────────────────────────────────────────────────────────────┐ QueryResult.warnings carries the notices an engine attached to a run it completed — a Couchbase query-service advisory, or a Druid answer admitting that some segments of the queried data were unavailable while still answering 200. The field is absent when the engine reported none, so its presence alone decides whether anything renders. ┌──────────────────────────────────────────────────────────────────────────────┐

With rows: an amber WARNINGS badge sits beside the AUTO-LIMITED badge in the stats bar. The messages are in its tooltip and are also read out by assistive technology.
With no rows: the messages are listed outright under "Query returned no data". A result that is empty because the data was unreachable must not read as a result that is empty because the data is not there. ┌──────────────────────────────────────────────────────────────────────────────┐ This is not the plan analysis in Automatic Warning Detection: those are derived by the client from EXPLAIN output, these are the engine's own words about the run. ┌──────────────────────────────────────────────────────────────────────────────┐

Declared Column Types

┌──────────────────────────────────────────────────────────────────────────────┐ `QueryResult.columnTypes` carries the type the wire format declared for each column of this result, keyed by its name in `fields` and spelled the way the engine spells it (`Nullable(String)`, `BIGINT`). For a computed column or an ad-hoc projection it is the only source of a type at all — the schema tree has no catalog entry to answer with. ┌──────────────────────────────────────────────────────────────────────────────┐ The type is shown beside the column name in the desktop table's header, and is part of that header's accessible name. The compact table shown below the `md` breakpoint carries it as a tooltip only: that table sizes its header and body cells from their own content, so visible text in the header alone would push it out of step with the rows. A column the engine declared no type for renders exactly as it did before. ┌──────────────────────────────────────────────────────────────────────────────┐

Query EXPLAIN Integration

Automatic EXPLAIN

┌──────────────────────────────────────────────────────────────────────────────┐ Every SELECT query automatically runs EXPLAIN in the background (parallel execution). This provides instant performance insights without user action. ┌──────────────────────────────────────────────────────────────────────────────┐ The background plan is always the estimate: it plans the statement and never executes it, because the run beside it already is the execution. On PostgreSQL that is EXPLAIN (FORMAT JSON); the Explain button asks for analyze instead and gets EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), which runs the statement to report actual rows and timings. Until #1311 the background request built the ANALYZE form too, so every SELECT ran twice: measured on PostgreSQL 18.6, one RUN of SELECT nextval('my_seq') advanced the sequence by two, and on Citus and TimescaleDB create_distributed_table / create_hypertable did their work in the hidden request before the user's own call failed as "already distributed". An estimated plan shows the planner's rows (~250.0K rows) and cost on each node and says "not executed" in the header, instead of zero rows in zero time. ┌──────────────────────────────────────────────────────────────────────────────┐ The background plan is asked for one statement only. A run of several statements (for example SELECT 1 AS a; INSERT ...) gets no plan, the Explain button refuses one with "Only a single statement can be explained", and POST /api/db/query refuses an explain request of more than one statement with a 400: an EXPLAIN prefixes one statement, so the rest of the text would run as statements of their own (measured on Materialize, AlloyDB Omni and Cloudberry: the INSERT was applied twice). ┌──────────────────────────────────────────────────────────────────────────────┐ The plan request carries a queryId of its own, and Cancel cancels it on the server together with the run. Closing an editor tab whose statement is still running cancels it the same way, run and plan, since the tab's Cancel button goes with it. That cancel raises no toast of its own, so the close's Undo toast stays the one on top to click; Undo brings back the tab's text, not the run (standalone app; the embedded package's host owns its runs). ┌──────────────────────────────────────────────────────────────────────────────┐

Supported Databases

Database	Background plan (estimate)	Explain button (analyze)
PostgreSQL	`EXPLAIN (FORMAT JSON)`	`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)`
MySQL	`EXPLAIN FORMAT=JSON`	`EXPLAIN FORMAT=JSON`
SQLite	`EXPLAIN QUERY PLAN` (tree, no cost/timing metrics)	`EXPLAIN QUERY PLAN`
┌──────────────────────────────────────────────────────────────────────────────┐

Non-SELECT Statements

┌──────────────────────────────────────────────────────────────────────────────┐ EXPLAIN is built for SELECT statements only. Clicking Explain with anything else (UPDATE, INSERT, DDL, …) executes nothing and reports "Only SELECT statements can be explained" — an explain run never falls back to running the original statement, because it deliberately bypasses the dangerous-query confirmation dialog. ┌──────────────────────────────────────────────────────────────────────────────┐ Because it bypasses that dialog, the classification is the only screen on this path, and on PostgreSQL the Explain button's wrapper is EXPLAIN (ANALYZE, …), which runs what it explains. So the PostgreSQL and ClickHouse strategies read the statement under their own dialect's grammar rather than the shared default (#300): block comments nest in both, and a flat reading of /* a /* b */ SELECT 1 */ DELETE FROM users reports SELECT as the leading keyword while PostgreSQL reads the whole run as one comment and executes the DELETE. Verified on PostgreSQL 18: explaining that statement against a three-row table left zero rows. Under PostgreSQL's grammar the statement leads with DELETE, so nothing is built and the button reports that it cannot be explained. The other four strategies stay dialect-blind: their engines' comment rules are the flat one the default already applies (MySQL, SQLite) or were never established (Druid, Couchbase), and their EXPLAIN describes without running, so the worst a misread comment costs there is a refused button. ┌──────────────────────────────────────────────────────────────────────────────┐

How It Works

User executes: SELECT * FROM orders WHERE status = 'pending'
┌──────────────────────────────────────────────────────────────────────────────┐
┌─────────────────────────────────────────────────────────────┐
│                    Parallel Execution                        │
│                                                              │
│  ┌──────────────────┐      ┌──────────────────────────────┐ │
│  │   Main Query     │      │   Background EXPLAIN          │ │
│  │   (with LIMIT)   │      │   (estimate, never executes)  │ │
│  └────────┬─────────┘      └────────────┬─────────────────┘ │
│           │                              │                   │
│           ▼                              ▼                   │
│     Results Tab                    Explain Tab               │
└─────────────────────────────────────────────────────────────┘

Accessing EXPLAIN Data

┌──────────────────────────────────────────────────────────────────────────────┐ Click the "Explain" tab in the results panel to view:

Performance Insights
Execution Plan Tree
Raw JSON ┌──────────────────────────────────────────────────────────────────────────────┐

Performance Insights

VisualExplain Component

┌──────────────────────────────────────────────────────────────────────────────┐ Location: src/components/VisualExplain.tsx ┌──────────────────────────────────────────────────────────────────────────────┐ The VisualExplain component analyzes execution plans and provides actionable insights. ┌──────────────────────────────────────────────────────────────────────────────┐

Automatic Warning Detection

Warning	Trigger	Severity
Sequential Scan	Seq Scan on >10K rows	Warning
Estimate Mismatch	Actual/Planned ratio >10x or <0.1x	Info
Expensive Sort	Sort operation >100ms	Warning
High Loop Count	Nested Loop >1000 iterations	Critical
┌──────────────────────────────────────────────────────────────────────────────┐

Warning Examples

┌──────────────────────────────────────────────────────────────────────────────┐ Sequential Scan Warning:

⚠️ Sequential Scan
Full table scan on "orders" (15.2K rows). Consider adding an index.

┌──────────────────────────────────────────────────────────────────────────────┐ N+1 Problem Detection:

🔴 High Loop Count
Nested loop executed 5.2K times. This could indicate an N+1 problem.

┌──────────────────────────────────────────────────────────────────────────────┐ Estimate Mismatch:

ℹ️ Estimate Mismatch
Expected 100 rows, got 15.2K. Statistics may be outdated.

Metrics Grid

Metric	Description
Cache Hit Rate	Buffer cache efficiency (>95% is good)
Operations	Number of plan nodes
Execution	Total query time
┌──────────────────────────────────────────────────────────────────────────────┐

Plan Tree View

┌──────────────────────────────────────────────────────────────────────────────┐ Interactive, collapsible execution plan with:

Node type icons (Seq Scan, Index Scan, Join, Sort, etc.)
Time bars showing relative cost
Row counts and costs
Filter conditions
Index usage ┌──────────────────────────────────────────────────────────────────────────────┐

▼ Limit (0.12ms, 500 rows)
  └─▼ Sort (45.2ms, 500 rows)
      └─▼ Seq Scan on orders (120.5ms, 15.2K rows)
          Filter: status = 'pending'

┌──────────────────────────────────────────────────────────────────────────────┐

Architecture

File Structure

src/
├── lib/db/utils/
│   └── query-limiter.ts      # Query parsing and LIMIT injection
├── lib/sql/
│   └── result-order.ts       # Whether a statement orders its OWN result
├── lib/query-generators.ts   # The tree click's statement — no row bound of its own
├── hooks/
│   ├── use-tab-manager.ts    # PREVIEW_PAGE_SIZE, sent as an execution option
│   └── use-query-execution.ts # Append, currentOffset, Load More (standalone)
├── workspace/hooks/
│   └── use-query-adapter.ts  # The same, for the embedded shell
├── app/api/db/
│   └── query/route.ts        # Query API with pagination; hasMore requires wasLimited
├── components/
│   ├── Studio.tsx            # Query execution orchestration
│   ├── ResultsGrid.tsx       # Results display; the one predicate behind Load More
│   ├── results-grid/StatsBar.tsx  # AUTO-LIMITED badge, ordering notice, Load More control
│   └── VisualExplain.tsx     # EXPLAIN visualization
└── lib/types.ts              # QueryPagination interface

Data Flow

┌─────────────┐     ┌──────────────────┐     ┌─────────────────┐
│  Dashboard  │────▶│  /api/db/query   │────▶│  DB Provider    │
│             │     │                  │     │                 │
│ executeQuery│     │ - Parse query    │     │ - Execute SQL   │
│             │     │ - Apply LIMIT    │     │ - Return rows   │
└─────────────┘     │ - Add pagination │     └─────────────────┘
       │            └──────────────────┘
       │
       ▼
┌─────────────┐     ┌──────────────────┐
│ ResultsGrid │     │  VisualExplain   │
│             │     │                  │
│ - Show rows │     │ - Parse plan     │
│ - Load More │     │ - Show warnings  │
│ - Stats bar │     │ - Render tree    │
└─────────────┘     └──────────────────┘

Key Interfaces

// Query pagination metadata
interface QueryPagination {
  limit: number;
  offset: number;
  hasMore: boolean;
  totalReturned: number;
  wasLimited: boolean;
}
┌──────────────────────────────────────────────────────────────────────────────┐
// Query result with pagination
interface QueryResult {
  rows: any[];
  fields: string[];
  rowCount: number;
  executionTime: number;
  explainPlan?: any;
  pagination?: QueryPagination;
  warnings?: QueryWarning[];             // notices the engine attached; absent when it reported none
  columnTypes?: Record<string, string>;  // declared type per column, keyed by its name in `fields`
}
┌──────────────────────────────────────────────────────────────────────────────┐
// Query tab state
interface QueryTab {
  id: string;
  name: string;
  query: string;                         // the editor buffer, rewritten on every keystroke
  result: QueryResult | null;
  resultQuery?: string;                  // the statement that produced `result`, as it was sent
  explainPlan?: any;
  currentOffset?: number;
  isLoadingMore?: boolean;
  allRows?: any[];
}

┌──────────────────────────────────────────────────────────────────────────────┐

Best Practices

For Users

Use WHERE clauses - Filter data at the database level
Add LIMIT when known - If you only need 10 rows, add LIMIT 10
Check Explain tab - Review performance before running in production
Use indexes - Add indexes for frequently filtered columns ┌──────────────────────────────────────────────────────────────────────────────┐

For Developers

Never bypass the limiter - Always use the query API
Handle pagination - Support hasMore in custom implementations
Parse EXPLAIN - Use the analyzePlan function for custom analysis ┌──────────────────────────────────────────────────────────────────────────────┐

Configuration

┌──────────────────────────────────────────────────────────────────────────────┐ Currently, limits are hardcoded. Future versions may support configuration: ┌──────────────────────────────────────────────────────────────────────────────┐

// Future: .env configuration
QUERY_DEFAULT_LIMIT=500
QUERY_MAX_UNLIMITED=100000
EXPLAIN_AUTO_RUN=true

┌──────────────────────────────────────────────────────────────────────────────┐

Changelog

Version	Changes
0.7.0	Initial query optimization system
0.7.1	Removed Large Dataset popup (silent limiting)
0.7.1	Added automatic background EXPLAIN
0.7.1	Added VisualExplain with Performance Insights
┌──────────────────────────────────────────────────────────────────────────────┐

FilesExpand file tree

query-optimization.md

Latest commit

History

query-optimization.md

File metadata and controls

Query Optimization & Performance Features

Table of Contents

Query Pagination System

Overview

Key Constants

How It Works

Silent Auto-Limiting

Philosophy

Visual Indicators

Query Limiter Utility

Supported Query Types

Which dialect the readers are reading

Where the bound is placed

An error that names the bound

Clauses that must follow the bound

Multi-statement runs

Procedural bodies, separator lines and batches (#1312)

Where the page size comes from

Destructive-Statement Confirmation

Text the reading cannot resolve asks, and says so

Load More Functionality

When the control appears

User Flow

API Request

Response Format

Row order across pages

Load All Option

┌──────────────────────────────────────────────────────────────────────────────┐

Result-Level Signals

Engine Warnings

Declared Column Types

Query EXPLAIN Integration

Automatic EXPLAIN

Supported Databases

Non-SELECT Statements

How It Works

Accessing EXPLAIN Data

Performance Insights

VisualExplain Component

Automatic Warning Detection

Warning Examples

Metrics Grid

Plan Tree View

┌──────────────────────────────────────────────────────────────────────────────┐

Architecture

File Structure

Data Flow

Key Interfaces

┌──────────────────────────────────────────────────────────────────────────────┐

Best Practices

For Users

For Developers

Configuration

┌──────────────────────────────────────────────────────────────────────────────┐

Changelog