SQL reference

Statements at a glance

Every supported statement, with its transactional behaviour. Keywords are case-insensitive; a single statement per call, with an optional trailing semicolon.

Data definition (DDL)

CREATE TABLE

Define a table with one or more columns and constraints. IF NOT EXISTS makes creation idempotent.

CREATE TABLE notes (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  title   TEXT NOT NULL,
  email   TEXT UNIQUE,
  status  TEXT DEFAULT 'draft',
  score   REAL CHECK (score >= 0),
  data    BLOB
);

-- table-level constraints (composite PRIMARY KEY / UNIQUE / CHECK)
CREATE TABLE membership (
  org  INTEGER,
  usr  INTEGER,
  role TEXT DEFAULT 'member',
  PRIMARY KEY (org, usr),
  UNIQUE (usr, role)
);

• MAY omit a column's type name — an untyped column defaults to TEXT affinity.
• MAY write a parenthesised size such as VARCHAR(64) — parsed and ignored; only the storage class matters.

ALTER TABLE

Migrate a table's shape (autocommit only). Adding a column backfills existing rows with its DEFAULT (or NULL).

ALTER TABLE notes ADD COLUMN pinned INTEGER DEFAULT 0;
ALTER TABLE notes DROP COLUMN data;
ALTER TABLE notes RENAME COLUMN title TO heading;
ALTER TABLE notes RENAME TO memos;

DROP TABLE

Remove a table. IF EXISTS suppresses the error when the table is absent.

DROP TABLE notes;
DROP TABLE IF EXISTS notes;

Data manipulation (DML)

INSERT

Insert one or more rows. The column list is optional; when given, values bind to those columns by position. Multiple parenthesised tuples insert several rows in one statement, and the row source may be a query (INSERT … SELECT). A clashing primary key is resolved by an explicit ON CONFLICT action or by SQLite's OR IGNORE / OR REPLACE. RETURNING projects the inserted rows back as a result set.

INSERT INTO notes (id, title, body) VALUES (1, 'first', 'hello');

INSERT INTO notes (id, title) VALUES (2, 'two'), (3, 'three');

INSERT INTO notes VALUES (4, 'four', NULL, 9.5, NULL);

-- copy rows from a query
INSERT INTO archive (id, title) SELECT id, title FROM notes WHERE score IS NULL;

-- upsert: do nothing, or update using the proposed row via `excluded`
INSERT INTO notes (id, title) VALUES (1, 'dup') ON CONFLICT (id) DO NOTHING;
INSERT INTO notes (id, title) VALUES (1, 'new')
  ON CONFLICT (id) DO UPDATE SET title = excluded.title;

-- SQLite resolutions
INSERT OR IGNORE  INTO notes (id, title) VALUES (1, 'skip');
INSERT OR REPLACE INTO notes (id, title) VALUES (1, 'replace whole row');

-- return the inserted rows
INSERT INTO notes (id, title) VALUES (?, ?) RETURNING id, title;

UPDATE

Assign one or more columns, optionally filtered by a WHERE predicate. Without WHERE, every row is updated. The number of rows changed is reported by engine_changes(); RETURNING projects the new row versions.

UPDATE notes SET title = 'renamed', score = score + 1 WHERE id = 1;
UPDATE notes SET body = NULL;
UPDATE notes SET score = score + 1 WHERE id = 1 RETURNING id, score;

DELETE

Remove rows matching an optional WHERE predicate. Without WHERE, every row is deleted. RETURNING projects the deleted rows.

DELETE FROM notes WHERE score IS NULL;
DELETE FROM notes;
DELETE FROM notes WHERE id = 9 RETURNING id, title;

Queries (SELECT)

A query reads under snapshot isolation. The full shape is:

[WITH cte AS (query), ...]
SELECT [DISTINCT] items
  FROM source [JOIN source ON ... | USING (...)] ...
 WHERE predicate
 GROUP BY expr, ...
HAVING predicate
 [UNION | INTERSECT | EXCEPT [ALL] SELECT ...]
 ORDER BY expr [ASC | DESC] [NULLS FIRST | LAST], ...
 LIMIT n OFFSET m

Clauses are optional. SELECT ALL is the default; SELECT DISTINCT de-duplicates output rows.

Sources and joins

A FROM source is a table (optionally aliased t a / t AS a), a derived table (SELECT …) AS a, or a join. Qualified references alias.col resolve against the active sources; an ambiguous or unknown name is a parse-time ENGINE_ERR_SQL. Joins are nested-loop and evaluated over the MVCC snapshot.

SELECT a.name, b.title
  FROM authors a JOIN books b ON a.id = b.author_id
 ORDER BY a.name;

SELECT a.name, count(*) AS n
  FROM authors a LEFT JOIN books b ON a.id = b.author_id
 GROUP BY a.name;

Select items

Aggregates and GROUP BY

Aggregates compute over the filtered rows. Without GROUP BY they fold the whole (filtered) result into one row; with GROUP BY they fold each group, and you may project the grouped expressions alongside the aggregates. HAVING filters whole groups. COUNT(DISTINCT x) counts distinct non-NULL values; group_concat(x[, sep]) / string_agg(x, sep) join a group's values. Aggregates nest inside scalar expressions, so shapes like coalesce(json_agg(title), '[]') work.

CTEs, subqueries and set operations

A leading WITH name AS (query), … defines non-recursive common table expressions usable as FROM sources. A parenthesized subquery may appear as a derived table in FROM, as a scalar value (SELECT …), or in x IN (SELECT …) / EXISTS (SELECT …) — these expression subqueries must be non-correlated (they may not reference the outer query's columns). UNION/INTERSECT/EXCEPT (with optional ALL) combine two result sets of matching arity; a trailing ORDER BY/LIMIT applies to the combined result and references output columns.

WITH counts AS (SELECT author_id, count(*) AS n FROM books GROUP BY author_id)
SELECT a.name, c.n FROM authors a JOIN counts c ON a.id = c.author_id;

SELECT name FROM authors WHERE id IN (SELECT author_id FROM books);

SELECT n FROM a UNION SELECT n FROM b ORDER BY n;

ORDER BY, LIMIT and OFFSET

• MAY sort by one or more expressions, each independently ASC (default) or DESC, with optional NULLS FIRST / NULLS LAST; an ORDER BY key may name a select-list alias.
• MUST give LIMIT / OFFSET an integer literal or a parameter (LIMIT ALL means no limit) — a non-integer is a parse error.

Expressions

Expressions appear in WHERE, ORDER BY, select items, UPDATE assignments, and INSERT values. The operand forms are:

Functions

A focused set of standard scalar functions, plus the JSON builders client tooling (PostgREST, Bun.sql) leans on. An unknown function is a parse-time ENGINE_ERR_SQL, never a silent NULL.

Operators

Listed from lowest to highest binding precedence.

SELECT * FROM notes
 WHERE (score > 5 OR title LIKE 'a%')
   AND body IS NOT NULL
 ORDER BY score DESC;

Types and storage classes

Every value belongs to one of six storage classes. A declared column type maps to a storage class by affinity (the same keyword rules SQLite uses), and values are lightly coerced toward the column's class where lossless.

• MAY rely on light affinity: an INTEGER inserted into a REAL column becomes a real; a whole-number REAL in an INTEGER column becomes an integer.
• MUST read all values back as text across the C ABI — a BLOB renders as standard padded base64, and a SQL NULL comes back as a null pointer.

Vector search

Vector search is built into the engine. A VECTOR(N) column stores a fixed-length array of f32, an HNSW index answers top-k nearest-neighbour queries, and three distance operators are available in projections, WHERE, and ORDER BY. Because the index rides the same WAL/replay path as the rows, it branches and scales-to-zero with the database.

The vector type and literals

Declare the dimension at column time; it is validated on insert. Vector literals are written [1, 2, 3] (or the pgvector-style text form '[1,2,3]').

CREATE TABLE memories (
  id        INTEGER PRIMARY KEY,
  note      TEXT,
  embedding VECTOR(3)
);

INSERT INTO memories VALUES
  (1, 'apples',  [1, 0, 0]),
  (2, 'oranges', [0, 1, 0]);

HNSW index

Create an HNSW access method over a vector column. Like CREATE TABLE, index DDL runs in autocommit only. WITH options tune the graph and the search.

CREATE INDEX mem_e ON memories USING hnsw (embedding)
  WITH (metric = 'cosine');           -- 'cosine' | 'l2' | 'inner_product'
  -- also: m, ef_construction, ef_search

DROP INDEX IF EXISTS mem_e;

Distance operators & nearest-neighbour queries

The three operators compute a distance between two vectors: <-> (L2), <=> (cosine), and <#> (inner product). Pass the query vector as a parameter and order by distance:

SELECT note
  FROM memories
 ORDER BY embedding <=> ?      -- bind the query vector, e.g. [0.9, 0.1, 0]
 LIMIT 1;                       -- → 'apples'

Tuning recall per session

ef_search (the HNSW search width) is fixed when the index is created but can be overridden per connection at runtime. Raise it for more recall, lower it for less latency:

SET twill.vector_ef_search = 128;   -- widen ANN search for this connection
SHOW twill.vector_ef_search;        -- inspect the current value
RESET twill.vector_ef_search;       -- restore each index's configured default

The override is per-session state only — it changes recall/latency for your queries, never the stored index — so it is safe to set differently on different connections. See the measured recall/latency curve in the capabilities spec. Heavily-churned indexes (many deletes) are compacted automatically; queries stay correct throughout with no VACUUM step.

Parameters

Bind placeholders by 1-based index through the prepared-statement path. Three placeholder spellings are accepted: positional ? (SQLite), numbered $1 (PostgreSQL), and named :name (SQLite). A repeated $1 reuses one slot; distinct :names are assigned sequential slots in first-seen order.

INSERT INTO notes (id, title) VALUES (?, ?);
SELECT * FROM notes WHERE id = $1 OR title ILIKE $2;
SELECT * FROM notes WHERE author = :who;

Each bound value is a NUL-terminated typed literal: a one-character tag followed by the encoded value. This is how the string-only ABI carries a typed argument.

Transactions

Row DML is fully transactional under snapshot isolation. A single writer serializes through a write lane; readers capture a snapshot LSN and never block.

BEGIN;
INSERT INTO notes (id, title) VALUES (10, 'draft');
UPDATE notes SET score = 1 WHERE id = 10;
COMMIT;   -- returns only once the records are durable on the backend

• MUST keep schema changes out of an explicit transaction — DDL inside BEGIN returns ENGINE_ERR_TXN.
• SHOULD retry a transaction that fails with ENGINE_ERR_CONFLICT — a first-committer-wins check rejects conflicting concurrent writers.

Dialect & session compatibility

To let real PostgreSQL/SQLite clients and ORMs connect without surprises, the engine accepts a few dialect spellings and the session statements clients emit on connect.

• MAY quote identifiers with double quotes "x" or backticks `x`. (Square brackets denote vector literals here, so [x] is not an identifier quote.)
• MAY issue SET, SET TRANSACTION ISOLATION LEVEL …, PRAGMA, VACUUM, ANALYZE, RESET, DISCARD — accepted as no-ops (the engine runs one snapshot-isolation mode).
• MAY issue SHOW name (returns a one-row result) and EXPLAIN [ANALYZE] … (returns a one-line plan; the engine has one strategy per shape).

Out of scope

These are deliberate boundaries, not bugs. Each parses to a clear ENGINE_ERR_SQL rather than a wrong result.

• MUST NOT use correlated subqueries (an expression subquery referencing the outer query's columns); only non-correlated scalar/IN/EXISTS subqueries are supported.
• MUST NOT use OLAP-shaped features — window functions (OVER), GROUPING SETS/ROLLUP/CUBE, LATERAL, or recursive CTEs (compose DuckDB).
• MUST NOT use views or stored secondary/B-tree indexes. The only CREATE INDEX supported is the HNSW vector index (USING hnsw); a scalar UNIQUE is enforced by full scan, not a stored index.
• MUST NOT rely on procedural/extensibility surface — CREATE FUNCTION/TRIGGER/EXTENSION, roles/GRANT, LISTEN/NOTIFY.

Related