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. Each column may carry a type name and the constraints PRIMARY KEY and/or NOT NULL. IF NOT EXISTS makes creation idempotent.

CREATE TABLE notes (
  id    INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  body  TEXT,
  score REAL,
  data  BLOB
);

CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT);

• MUST declare at least one column; an empty column list is a parse error.
• MAY omit a column's type name — an untyped column defaults to TEXT affinity.
• MAY write a parenthesised size such as VARCHAR(64) or DECIMAL(10,2) — the size is parsed and ignored; only the storage class matters.
• MAY mark a column UNIQUE or explicitly NULL — these are accepted as constraint markers (uniqueness is enforced for the primary key).

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. OR REPLACE / OR IGNORE are accepted after INSERT and ignored.

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);

-- with parameters
INSERT INTO notes (id, title) VALUES (?, ?);

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().

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

DELETE

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

DELETE FROM notes WHERE score IS NULL;
DELETE FROM notes;

Queries (SELECT)

A query reads from a single table (or no table, for bare expressions) under snapshot isolation. The full shape is:

SELECT items
  FROM table
 WHERE predicate
 ORDER BY expr [ASC | DESC], ...
 LIMIT n

Clauses are optional and parsed in this order. SELECT ALL is accepted (it is the default); SELECT DISTINCT is rejected.

Select items

SELECT * FROM notes;
SELECT id, title AS heading FROM notes WHERE score >= 5 ORDER BY score DESC LIMIT 10;
SELECT COUNT(*), AVG(score), MAX(score) FROM notes;
SELECT title, score * 2 AS doubled FROM notes ORDER BY id;

ORDER BY and LIMIT

• MAY sort by one or more expressions, each independently ASC (default) or DESC.
• MUST give LIMIT an integer literal — a non-integer (including a parameter) is a parse error.

Expressions

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

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'

Parameters

Use a positional ? placeholder anywhere an expression is allowed, then bind by 1-based index. Binding goes through the prepared-statement path of the C ABI.

INSERT INTO notes (id, title) VALUES (?, ?);
SELECT * FROM notes WHERE id = ? OR title LIKE ?;

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.

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 JOIN — queries read from a single table.
• MUST NOT use GROUP BY / HAVING — aggregates are whole-table only.
• MUST NOT use subqueries.
• MUST NOT use SELECT DISTINCT.
• MUST NOT use views or ALTER TABLE. The only CREATE INDEX supported is the HNSW vector index (USING hnsw); general secondary/B-tree indexes are not exposed as SQL.

Related