# Supported SQL
The canonical reference for the SQL surface SQLRite implements today. Parsing is delegated to [`sqlparser`](https://crates.io/crates/sqlparser) using the SQLite dialect, so tokens and grammar follow SQLite — execution only implements the subset below, and anything else is rejected with a typed `NotImplemented` error rather than silently partial behavior.
If you're looking for _how_ to use SQLRite (REPL flow, meta-commands, history, embedding), see [Using SQLRite](usage.md). This document is the strict reference for what statements execute and what semantics they carry.
## Statement at a glance
| Statement | Supported today |
|---|---|
| [`CREATE TABLE`](#create-table) | Columns with `PRIMARY KEY` / `UNIQUE` / `NOT NULL` / `DEFAULT <literal>`; typed columns; auto-indexes on constrained columns |
| [`CREATE [UNIQUE] INDEX`](#create-index) | Single-column named indexes, `IF NOT EXISTS`, persisted as cell-based B-Trees |
| [`INSERT INTO`](#insert-into) | Auto-ROWID, UNIQUE/PK enforcement, clean type errors, NULL/DEFAULT padding |
| [`SELECT`](#select) | `*` or column list, `WHERE`, single-column `ORDER BY`, `LIMIT`; index probing on `col = literal` |
| [`UPDATE`](#update) | Multi-column `SET`, `WHERE`, arithmetic RHS, type + UNIQUE enforcement |
| [`DELETE`](#delete) | `WHERE` predicate or whole-table |
| [`ALTER TABLE`](#alter-table) | `RENAME TO`, `RENAME COLUMN`, `ADD COLUMN`, `DROP COLUMN` (one operation per statement) |
| [`DROP TABLE`](#drop-table) / [`DROP INDEX`](#drop-index) | `IF EXISTS`; single target; auto-indexes refused for `DROP INDEX` |
| [`BEGIN`](#transactions) / [`COMMIT`](#transactions) / [`ROLLBACK`](#transactions) | Snapshot-based; single-level; WAL-backed commit; auto-rollback on COMMIT disk failure |
| [`VACUUM`](#vacuum) | Compacts the file: rewrites every live B-Tree contiguously from page 1 and clears the freelist. Bare `VACUUM;` only — no modifiers. |
Statements the parser accepts (because sqlparser understands them in the SQLite dialect) but SQLRite doesn't execute yet return `SQL Statement not supported yet`. The [Not yet supported](#not-yet-supported) section below enumerates the common ones.
### Parameter binding (SQLR-23)
Every statement above accepts `?` placeholders anywhere a value literal is allowed (WHERE, ORDER BY, INSERT VALUES, …). Bind via the public Rust API:
```rust
use sqlrite::{Connection, Value};
let mut conn = Connection::open_in_memory()?;
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")?;
let mut ins = conn.prepare_cached("INSERT INTO users (name, age) VALUES (?, ?)")?;
ins.execute_with_params(&[Value::Text("alice".into()), Value::Integer(30)])?;
ins.execute_with_params(&[Value::Text("bob".into()), Value::Integer(25)])?;
let stmt = conn.prepare_cached("SELECT name FROM users WHERE age > ?")?;
let rows = stmt
.query_with_params(&[Value::Integer(26)])?
.collect_all()?;
# Ok::<(), sqlrite::SQLRiteError>(())
```
- **Positional only.** `?` placeholders are bound by source-order index (`params[0]` = first `?`, etc.). Named placeholders (`:foo`, `$1`) are not yet supported.
- **Strict arity.** The slice length must match the placeholder count or `query_with_params` / `execute_with_params` returns a typed error.
- **Vector binding.** `Value::Vector(Vec<f32>)` binds where a bracket-array literal would normally appear — including the second arg of `vec_distance_*` inside an HNSW-eligible `ORDER BY`. The optimizer recognizes the bound shape, so the graph shortcut still fires.
- **Plan cache.** `Connection::prepare_cached` keeps a per-connection LRU (default cap 16; tune via `set_prepared_cache_capacity`) so a hot SQL string parses exactly once across the connection's lifetime. `Connection::prepare` always re-parses.
---
## `CREATE TABLE`
```sql
CREATE TABLE <name> (<col> <type> [column_constraint]* [, ...]);
```
### Column types
| Keyword(s) | Storage class | Notes |
|---|---|---|
| `INTEGER`, `INT`, `BIGINT`, `SMALLINT` | Integer (i64) | All four alias to the same 64-bit signed storage class |
| `TEXT`, `VARCHAR` | Text (String) | UTF-8; no length limit enforced (VARCHAR's `(n)` is parsed and ignored) |
| `REAL`, `FLOAT`, `DOUBLE`, `DECIMAL` | Real (f64) | Double-precision; `DECIMAL(p,s)` precision/scale parsed and ignored |
| `BOOLEAN` | Boolean | Stored compactly in the null bitmap's sibling bits; accepts `TRUE` / `FALSE` |
| `VECTOR(N)` | Vector (Vec\<f32\>, fixed dim N) | **Phase 7a.** Dense f32 array of fixed dimension. `N` is required and must be ≥ 1. Inserted as bracket-array literals `[0.1, 0.2, ...]`. Dimension is enforced at INSERT/UPDATE; mismatched-length values are rejected. Distance functions and ANN indexing land in 7b–7d. |
| `JSON`, `JSONB` | Text (canonical JSON) | **Phase 7e.** JSON document stored as canonical UTF-8 text — same as SQLite's JSON1 extension (Q3 scope correction since bincode was removed in Phase 3c). INSERT/UPDATE values are validated via `serde_json::from_str`; malformed JSON is rejected with a typed error and no row is written. `JSONB` is accepted as an alias for `JSON` (PostgreSQL convention; both store as text in our case). Path-style read access via the `json_extract` / `json_type` / `json_array_length` / `json_object_keys` functions below. |
### Column constraints
- `PRIMARY KEY` — one column per table; the column **must** be `INTEGER` and gets auto-ROWID behavior (omitted on INSERT → auto-assigned). Auto-creates an index named `sqlrite_autoindex_<table>_<column>`.
- `UNIQUE` — enforced at INSERT/UPDATE time. Auto-creates an index with the same naming scheme.
- `NOT NULL` — rejects NULL at INSERT/UPDATE. Omitted columns on INSERT are NULL by default (or pick up the column's `DEFAULT`, if any), so a `NOT NULL` without an INSERT-time value or DEFAULT is an error.
- `DEFAULT <literal>` — value substituted when the column is omitted from an INSERT. Accepts integer / real / text / boolean / NULL literals (and unary `+` / `-` on numerics). Function-call defaults like `CURRENT_TIMESTAMP` and other non-literal expressions are rejected at CREATE TABLE time. Explicit `INSERT ... VALUES (..., NULL, ...)` is preserved as NULL — the default only fires for omitted columns (matches SQLite).
### What's **not** enforced at CREATE TABLE time
- **Table-level constraints** (`PRIMARY KEY (col1, col2)`, `FOREIGN KEY`, `CHECK`, `UNIQUE (col1, col2)`) are parsed but ignored.
- **Multi-column `PRIMARY KEY`** — only single-column PKs work; a composite PK is accepted by the parser but treated as no PK.
### Errors returned
- `Table 'foo' already exists.` — duplicate `CREATE TABLE`.
- `'sqlrite_master' is a reserved name used by the internal schema catalog` — you tried to shadow the catalog table.
- `Column 'foo' appears more than once in the table definition` — duplicate column names.
- `PRIMARY KEY column must be INTEGER` — PK on a non-integer column.
---
## `CREATE INDEX`
```sql
CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name> ON <table> (<column>);
```
- Single-column only. Composite indexes (`CREATE INDEX ... ON t (a, b)`) are parsed but rejected at execution.
- The index name is **required**. Anonymous (`CREATE INDEX ON t (col)`) is rejected with `anonymous indexes are not supported`.
- Supported column types: `INTEGER`, `TEXT`. `REAL` and `BOOLEAN` columns cannot be indexed yet.
- `CREATE UNIQUE INDEX` on a column whose existing rows already carry duplicate values is rejected before any change is made — the table + other indexes stay untouched.
- `IF NOT EXISTS` — skips the create if an index with that name already exists. No-op return value in that case.
- Indexes persist as their own cell-based B-Trees (see [Storage model](storage-model.md)).
### Auto-indexes
Every `PRIMARY KEY` and every `UNIQUE` column gets an auto-index at `CREATE TABLE` time:
```
sqlrite_autoindex_<table>_<column>
```
These are full-citizen indexes — they're visible via `.tables`-adjacent catalog queries (once those land), persist across saves, and accelerate equality probes. You don't need to `CREATE INDEX` them yourself.
### HNSW indexes (Phase 7d)
```sql
CREATE INDEX <name> ON <table> USING hnsw (<vector_column>)
[WITH (metric = '<l2|cosine|dot>')];
```
Builds an [HNSW](https://arxiv.org/abs/1603.09320) approximate-nearest-neighbor index over a `VECTOR(N)` column. The query optimizer recognizes `ORDER BY vec_distance_l2(col, literal) LIMIT k` (or the cosine / dot variants) on an HNSW-indexed column **whose metric matches the query's distance function**, and probes the graph instead of full-scanning. SQLR-23 — the second arg can be either an inline `[...]` literal *or* a bound `Value::Vector(...)` parameter via `Statement::query_with_params`; the optimizer recognizes both, so prepared-statement KNN queries still take the graph shortcut.
The `WITH (metric = '…')` clause picks the distance the graph is built for. Three values are recognized: `'l2'` (Euclidean — the default, also accepts `'euclidean'`), `'cosine'`, and `'dot'` (negated dot-product — also accepts `'inner_product'` / `'ip'`). Omitting the clause is equivalent to `metric = 'l2'`, so pre-SQLR-28 catalogs round-trip unchanged. **The metric is not a query-time choice** — the graph topology depends on the metric used during INSERT (neighbour pruning is metric-specific), so a query whose `vec_distance_*` function doesn't match the index's metric falls through to brute-force rather than getting a wrong answer back from the graph. If you need both L2 and cosine probes on the same column, create two indexes.
- Recall@10 ≥ 0.95 at default parameters (`M=16`, `ef_construction=200`, `ef_search=50`). The `M` / `ef_*` knobs aren't tunable from SQL yet — see Q2 of [`docs/phase-7-plan.md`](phase-7-plan.md).
- The index is built incrementally on `INSERT`. `DELETE` / `UPDATE` mark the index `needs_rebuild`; the next save rebuilds from current rows under the same metric.
- Persisted as a `KIND_HNSW` cell tree alongside the regular page hierarchy — open path loads the graph bit-for-bit, no algorithm runs. The metric travels through the synthesized CREATE INDEX SQL in `sqlrite_master`; no file-format bump.
- Without an HNSW index — or with a metric mismatch — the same `ORDER BY vec_distance_… LIMIT k` query still works; it just brute-force-scans every row (Phase 7c's bounded-heap top-k optimization keeps the memory footprint to O(k)).
### FTS indexes (Phase 8)
```sql
CREATE INDEX <name> ON <table> USING fts (<text_column>);
```
Builds an FTS5-style inverted index with BM25 ranking over a `TEXT` column. Pairs with the [`fts_match`](#fts_match) and [`bm25_score`](#bm25_score) functions for keyword retrieval, and composes with `vec_distance_*` for hybrid retrieval (see [`docs/fts.md`](fts.md)).
- **TEXT columns only.** `INTEGER`, `REAL`, `BOOLEAN`, `VECTOR`, `JSON` columns are rejected at CREATE-INDEX time.
- **Single-column only.** Multi-column FTS is deferred to Phase 8.1.
- **`UNIQUE` is rejected** — UNIQUE has no meaning for an inverted index.
- The index is built incrementally on `INSERT`. `DELETE` / `UPDATE` mark the index `needs_rebuild`; the next save rebuilds from current rows.
- Persisted as a `KIND_FTS_POSTING` cell tree alongside the regular page hierarchy — open path loads posting lists bit-for-bit, no re-tokenization.
- The first save of a database with at least one FTS index promotes the file format from v4 to v5 (on-demand bump per Phase 8 plan Q10).
- Tokenizer is ASCII MVP per Q3: `[^A-Za-z0-9]+` split, lowercased, no stemming, no stop list.
- BM25 parameters are fixed at SQLite FTS5's defaults (`k1 = 1.5`, `b = 0.75`).
---
## `INSERT INTO`
```sql
INSERT INTO <name> (col1, col2, ...) VALUES (v1, v2, ...)
[, (v1, v2, ...) ...];
```
- **Explicit column list is required.** Value-list-only inserts (`INSERT INTO t VALUES (...)`) are not supported yet.
- **`INTEGER PRIMARY KEY` auto-ROWID** — omit the PK column and a ROWID is auto-assigned (max existing + 1, starting at 1).
- **Multi-row inserts** — the parser accepts `VALUES (...), (...), (...)`, and SQLRite runs each row through the type + UNIQUE checks in order. A failure mid-batch leaves the already-inserted rows in place.
- **NULL padding** — columns not named in the column list default to NULL. `NOT NULL` columns must appear in the list (or be the omitted PK).
- **Type validation** happens at INSERT time. A mismatched literal (`INSERT INTO t (age) VALUES ('not-a-number')` where `age` is `INTEGER`) is rejected with a typed error — no panic, no partial write.
- **UNIQUE enforcement** runs *before* any row insert so a failing batch doesn't leave partial state.
### Value literals accepted
| Literal | Example |
|---|---|
| Integer | `42`, `-5`, `0` |
| Real | `3.14`, `-0.001`, `1e10` |
| Text | `'single-quoted'` — doubled single quotes escape: `'it''s'` |
| Boolean | `TRUE`, `FALSE` (case-insensitive) |
| NULL | `NULL` (case-insensitive) |
| Vector | `[0.1, 0.2, 0.3]` — JSON-style bracket-array; integer elements widen to f32 (`[1, 2, 3]` is valid). For `VECTOR(N)` columns; dimension must match the declared `N`. *(Phase 7a)* |
Hex literals, blob literals, and date/time functions are not supported.
---
## `SELECT`
```sql
SELECT [DISTINCT] {* | <projection_item>[, <projection_item>, ...]}
FROM <table> [AS <alias>]
[{INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN <table> [AS <alias>] ON <expr>]*
[WHERE <expr>]
[GROUP BY <col>[, <col>, ...]]
[ORDER BY <expr> [ASC|DESC]]
[LIMIT <non-negative-integer>];
```
`<projection_item>` is one of:
```
<column> -- bare column reference
COUNT(*) -- counts every row, including all-NULL ones
COUNT([DISTINCT] <column>) -- counts non-NULL values, optionally deduping
{SUM | AVG | MIN | MAX}(<column>) -- aggregate over a single column
<projection_item> AS <alias> -- optional column alias
```
### What works
- **Projection**: `*` (all columns in declaration order), a bare column list, or an explicit list mixing bare columns and aggregate calls. Each item can carry an optional `AS alias` (the alias becomes the output column header and is recognized by `ORDER BY`).
- **`WHERE`**: any [expression](#expressions). Evaluated per row; NULL-as-false in WHERE context (three-valued logic collapsed to two-valued for filtering). Includes **`IS NULL`** / **`IS NOT NULL`** for explicit null tests, **`LIKE` / `NOT LIKE` / `ILIKE`** for pattern matching, and **`IN (list) / NOT IN (list)`** for set-membership against literal lists.
- **`DISTINCT`**: `SELECT DISTINCT` deduplicates result rows after projection (and after aggregation, when both apply). `NULL` values compare equal to other `NULL`s for dedupe, matching SQL's DISTINCT semantic.
- **`GROUP BY`**: one or more bare column names. Every non-aggregate item in the projection must appear in the `GROUP BY` list (the parser rejects the violation with a clear message). `GROUP BY <col>` without any aggregate behaves like an implicit `DISTINCT <col>`.
- **Aggregates** (SQLR-3): `COUNT(*)`, `COUNT(col)`, `COUNT(DISTINCT col)`, `SUM(col)`, `AVG(col)`, `MIN(col)`, `MAX(col)`. `SUM` over an integer column stays `INTEGER` until a `REAL` input arrives or the running sum overflows `i64` (one-time promotion to `REAL`). `AVG` always returns `REAL` (or `NULL` on empty / all-NULL groups). `MIN` / `MAX` skip NULLs and use the same total order as `ORDER BY`. Aggregates over an empty table or empty group return `0` for `COUNT(*)` / `COUNT(col)` and `NULL` for the rest.
- **`ORDER BY`**: single sort key, `ASC` (default) or `DESC`. For non-aggregating queries the key is any expression — including function calls — so KNN queries like `ORDER BY vec_distance_l2(embedding, [...]) LIMIT k` work end-to-end *(Phase 7b)*. For aggregating queries the key resolves against the *output* row by name: a bare identifier matches an alias or a `GROUP BY` column, and a function call like `COUNT(*)` matches an aggregate projection by its canonical display form. Sort key types must match across rows.
- **`LIMIT`**: non-negative integer literal. `LIMIT 0` is valid (returns zero rows). When `DISTINCT` is in play, `LIMIT` is applied after deduplication so it counts unique rows.
### `JOIN` semantics (SQLR-5)
Four flavors are supported, all with explicit `ON` conditions:
| Flavor | Keeps unmatched rows from… |
|---|---|
| `INNER JOIN` | …neither side. Only ON-matched pairs survive. |
| `LEFT [OUTER] JOIN` | …the left side; right-side columns become `NULL` for unmatched left rows. |
| `RIGHT [OUTER] JOIN` | …the right side; left-side columns become `NULL` for unmatched right rows. |
| `FULL [OUTER] JOIN` | …both sides, NULL-padded on the unmatched side. |
- **Engine choice:** SQLite ships only `INNER` and `LEFT OUTER`. SQLRite implements all four because the per-flavor differences boil down to NULL-padding policy on top of one shared nested-loop driver — adding `RIGHT` / `FULL` was effectively free once the executor had a multi-table scope. See [`docs/design-decisions.md`](design-decisions.md) for the rationale.
- **Aliases:** `FROM customers AS c INNER JOIN orders AS o ON c.id = o.customer_id`. When an alias is supplied the original table name leaves scope (SQL standard) — qualifier resolution uses the alias.
- **Qualified column references:** `<table>.<col>` and `<alias>.<col>` resolve to that specific side. Bare `<col>` references must resolve to exactly one in-scope table; ambiguous references error with a "qualify it as `<table>.col`" hint.
- **Output of `SELECT *`** over a join is every column of every in-scope table, in source order. Duplicate header names are permitted (SQLite-style). Disambiguate with explicit `SELECT t.col AS t_col, u.col AS u_col`.
- **Multi-join** chains left-fold: `A JOIN B ON ... JOIN C ON ...` evaluates as `(A ⨝ B) ⨝ C`. Each new clause sees every prior alias / table in its `ON` expression.
- **Self-joins** require an alias on at least one side: `FROM nodes AS p INNER JOIN nodes AS c ON p.id = c.parent_id`. Without one, you get a `duplicate table reference` error so qualifiers stay unambiguous.
- **`WHERE` runs after joins.** A `WHERE right.col IS NULL` filter on a `LEFT JOIN` correctly returns left rows with no match (the standard "anti-join via outer-join" idiom).
- **`ORDER BY` and `LIMIT`** apply to the fully joined row stream.
- **Algorithm:** plain nested-loop join, O(N×M) per join level. Adequate for an embedded learning database; hash / merge joins on equi-join shapes are a future optimization.
#### What's not supported in JOINs
- `JOIN ... USING (col)` and `NATURAL JOIN` — explicit `ON` only. (Both are deferred — `USING` is straightforward but adds a column-resolution rule we haven't needed yet.)
- `CROSS JOIN` (write `INNER JOIN ... ON true` instead) and comma-separated FROM lists.
- Aggregates / `GROUP BY` / `DISTINCT` *over* a join. The single-table aggregator is wired against one rowid stream; rewiring it for joined rows is a separate increment. Surfaces as a clean `NotImplemented` at parse time.
- `fts_match` / `bm25_score` inside a JOIN expression. They need to look up an FTS index by column, which is single-table-bound today. Use them on a single-table SELECT first, or fold the FTS lookup into the FROM side.
### Index probing
The executor includes a tiny optimizer: if the `WHERE` is exactly `<indexed_col> = <literal>` or `<literal> = <indexed_col>`, it probes the index and scans only matching rows. Mixed predicates (`WHERE a = 1 AND b > 2`), range predicates (`WHERE a > 1`), and OR-combined predicates fall back to a full table scan. Aggregating queries (`GROUP BY` / aggregate functions) skip the rowid-shape optimizations (HNSW / FTS / bounded-heap top-k) since every matching row contributes to its group.
### `LIKE` semantics
- `%` matches any (possibly empty) char sequence; `_` matches exactly one char. `\` escapes the next character so `\%` matches a literal percent. Outside `\%` / `\_` / `\\`, a backslash is itself a literal — matching SQLite's loose default.
- Case folding is **ASCII-only and on by default**, mirroring SQLite's default `PRAGMA case_sensitive_like = OFF`. `LIKE 'a%'` matches both `Apple` and `apple`. Non-ASCII characters compare by code point (no Unicode case folding).
- `LIKE … ESCAPE '<char>'` is not supported. `LIKE ANY (...)` is not supported.
- `NULL LIKE 'pattern'` evaluates to `NULL`; in a `WHERE` that excludes the row.
### `IN` semantics
- Only the literal-list form is supported: `WHERE x IN (1, 2, 3)` and `WHERE x NOT IN (...)`.
- Three-valued logic: if the LHS is `NULL`, the result is `NULL`; if the RHS list contains a `NULL` and no other entry matches, the result is `NULL`. In a `WHERE` both cases collapse to "row excluded", matching SQLite.
- `IN (subquery)`, `IN UNNEST(...)`, and `BETWEEN` are not supported yet.
### What doesn't work
- **`CROSS JOIN`**, **comma-separated FROM lists**, **`NATURAL JOIN`**, **`JOIN ... USING (col)`** — explicit `INNER` / `LEFT` / `RIGHT` / `FULL OUTER JOIN ... ON ...` only (see [JOIN semantics](#join-semantics-sqlr-5))
- **Aggregates** / **`GROUP BY`** / **`DISTINCT`** over a JOIN — pipe through a subquery once subqueries land
- **Subqueries**, CTEs (`WITH`), views
- **`HAVING`** — pre-aggregation `WHERE` works; post-aggregation filtering does not yet
- **`DISTINCT`** on `SUM` / `AVG` / `MIN` / `MAX` (only `COUNT(DISTINCT col)` is supported)
- **`GROUP BY` on expressions** — bare column names only in v1
- **`LIKE … ESCAPE '<char>'`**, **`IN (subquery)`**, **`BETWEEN`**, **`GLOB`**, **`REGEXP`**
- **Expressions in the projection list** beyond aggregate calls (`SELECT age + 1 FROM users` is still rejected; aggregates are the one allowed expression form)
- **Multi-column `ORDER BY`**, `NULLS FIRST/LAST` (single sort key only)
- **`OFFSET`**
- **Window functions** (`OVER (...)`, `FILTER (WHERE ...)`, `WITHIN GROUP`)
Any of the above reaches the executor as a parsed AST node that execution doesn't handle, producing either `NotImplemented` or a more specific error (e.g., `joins are not supported`).
---
## `UPDATE`
```sql
UPDATE <table> SET col1 = <expr> [, col2 = <expr>]* [WHERE <expr>];
```
- **Multi-column `SET`** — separate assignments with commas.
- **RHS is a full expression** — can reference other columns of the same row:
```sql
UPDATE users SET age = age + 1, updated_at = 'now' WHERE id = 42;
```
- **Type enforcement** — the declared column type of each target is checked against the assigned expression's result. Mismatch is a clean error; the row (and all other rows that would have been updated by the same statement) stays untouched.
- **UNIQUE enforcement** — if the update would collide with another row's value on a UNIQUE / PRIMARY KEY column, the whole statement is rejected before any write. No partial updates.
- **NULL assignments** respect `NOT NULL` — `SET col = NULL` on a `NOT NULL` column errors.
---
## `DELETE`
```sql
DELETE FROM <table> [WHERE <expr>];
```
- **No `WHERE`** deletes every row (tables and indexes are preserved; only row data is removed).
- **`WHERE`** uses the same [expression](#expressions) evaluator as `SELECT`.
- Secondary indexes are updated alongside the row deletes so a subsequent `WHERE col = ...` doesn't return stale hits.
---
## `ALTER TABLE`
```sql
ALTER TABLE [IF EXISTS] <table> RENAME TO <new_table>;
ALTER TABLE [IF EXISTS] <table> RENAME COLUMN <old_col> TO <new_col>;
ALTER TABLE [IF EXISTS] <table> ADD COLUMN <col_def>;
ALTER TABLE [IF EXISTS] <table> DROP COLUMN <col>;
```
One operation per statement (SQLite-style). `ALTER TABLE foo RENAME TO bar, ADD COLUMN x ...` is rejected — issue separate statements instead.
### `RENAME TO`
- Reserved-name rejection: cannot rename to `sqlrite_master`.
- Errors if the target name is already a table.
- Auto-indexes whose names embed the old table name (`sqlrite_autoindex_<old>_<col>`) are renamed in lockstep so the schema catalog stays consistent. Explicit indexes carry their user-given name unchanged.
### `RENAME COLUMN`
- Errors if the old column doesn't exist or the new name already exists in the table.
- Re-keys the row storage and updates every dependent index (auto + explicit, secondary / HNSW / FTS) — including auto-index name regeneration.
- Renaming the PRIMARY KEY column is allowed; the table's `primary_key` pointer follows the new name.
### `ADD COLUMN`
- The column definition reuses the same parser that handles CREATE TABLE columns: same types, same `NOT NULL` / `DEFAULT` semantics.
- **Rejected:** `PRIMARY KEY` and `UNIQUE` constraints on the added column. Both would require backfilling the column under uniqueness constraints against existing rows; that path will land alongside multi-column UNIQUE.
- **`NOT NULL` on a non-empty table requires `DEFAULT`.** Without one there's no value to backfill existing rowids with. Same rule SQLite applies.
- With a `DEFAULT`, every existing rowid is backfilled with the default value at ADD COLUMN time. Without a `DEFAULT`, existing rowids read as NULL for the new column.
### `DROP COLUMN`
- **Rejected:** dropping the PRIMARY KEY column.
- **Rejected:** dropping the only remaining column (degenerate table).
- Cascades to every dependent index (auto + explicit, secondary / HNSW / FTS) on the dropped column.
- `CASCADE` / `RESTRICT` modifiers are accepted by the parser and ignored — SQLite has no real distinction here either.
---
## `DROP TABLE`
```sql
DROP TABLE [IF EXISTS] <table>;
```
- Single target per statement. `DROP TABLE a, b, c;` is parsed but rejected with a NotImplemented error.
- Reserved-name rejection: `DROP TABLE sqlrite_master` errors with the same message `CREATE TABLE` uses.
- All indexes attached to the table (auto, explicit, HNSW, FTS) disappear with the table — they live inside the `Table` struct and ride along.
- Without `IF EXISTS`, dropping a table that doesn't exist errors. With it, that's a benign 0-tables-dropped no-op.
- **Disk pages move onto the freelist.** Pages the dropped table occupied are pushed onto a persisted free-page list (SQLR-6) so subsequent `CREATE TABLE` or inserts can reuse them. The file shrinks automatically when the freelist crosses 25% of `page_count` (SQLR-10 auto-VACUUM, default-on); embedders that need the prior "manual `VACUUM;` only" behavior can call `Connection::set_auto_vacuum_threshold(None)` at open time.
---
## `DROP INDEX`
```sql
DROP INDEX [IF EXISTS] <index_name>;
```
- Single target per statement.
- Walks every table searching for an index with the given name across the secondary B-Tree, HNSW, and FTS index families.
- **Refuses to drop auto-indexes.** `sqlrite_autoindex_*` names are constraint-bound to the column they index — the only way to remove them is to drop the underlying column or table. Same rule SQLite enforces for its `sqlite_autoindex_*` indexes.
- `IF EXISTS` makes a missing index a benign no-op.
---
## Expressions
Expressions work inside `WHERE` (both in `SELECT`, `UPDATE`, `DELETE`) and on the right-hand side of `UPDATE`'s `SET`.
### Operators
| Category | Operators |
|---|---|
| Comparison | `=`, `<>`, `<`, `<=`, `>`, `>=` |
| Null tests | `IS NULL`, `IS NOT NULL` |
| Pattern | `LIKE`, `NOT LIKE`, `ILIKE` (`%`, `_`, `\`-escape; case-insensitive ASCII) |
| Set | `IN (list)`, `NOT IN (list)` (literal lists only) |
| Logical | `AND`, `OR`, `NOT` |
| Arithmetic | `+`, `-`, `*`, `/`, `%` |
| String | `\|\|` (concatenation) |
| Unary | `+`, `-` |
| Grouping | Parentheses |
### Literals
Same set accepted by `INSERT` (see [Value literals accepted](#value-literals-accepted)).
### Built-in functions
| Function | Returns | Notes |
|---|---|---|
| `vec_distance_l2(a, b)` | Real (f64) | Euclidean distance √Σ(aᵢ−bᵢ)². Smaller is closer. *(Phase 7b)* |
| `vec_distance_cosine(a, b)` | Real (f64) | Cosine distance `1 − (a·b) / (‖a‖·‖b‖)`. Errors on zero-magnitude vectors (cosine is undefined). Smaller is closer; identical vectors return 0.0, orthogonal vectors return 1.0. *(Phase 7b)* |
| `vec_distance_dot(a, b)` | Real (f64) | Negated dot product `−(a·b)`. Negation makes "smaller is closer" consistent with the others. For unit-norm vectors equals `vec_distance_cosine(a, b) - 1`. *(Phase 7b)* |
| `json_extract(json, path)` | Depends on the resolved node | Walks `path` over `json` and returns the resolved value coerced to the closest SQL type — JSON strings → `TEXT`, numbers → `INTEGER` / `REAL`, booleans → `BOOLEAN`, `null` → `NULL`, and composites (`object` / `array`) → their canonical JSON-text serialization. Path defaults to `$` when only one argument is supplied. A path that doesn't resolve returns `NULL`. *(Phase 7e)* |
| `json_type(json[, path])` | Text | One of `'object'`, `'array'`, `'string'`, `'integer'`, `'real'`, `'true'`, `'false'`, `'null'`. Path defaults to `$`. *(Phase 7e)* |
| `json_array_length(json[, path])` | Integer | Number of elements in the JSON array at `path`. Errors if the resolved node is not an array. Path defaults to `$`. *(Phase 7e)* |
| `json_object_keys(json[, path])` | Text (JSON-array string) | Returns the object's keys as a JSON-array text in insertion order — e.g. `'["a","b","c"]'`. Path defaults to `$`. **Diverges from SQLite**, which exposes keys as a *table-valued* function (one row per key). SQLRite has no set-returning functions yet, so we return the keys as a JSON array and let callers parse if needed. *(Phase 7e)* |
| `fts_match(col, 'q')` | Bool | True iff the row contains at least one tokenized query term in `col`. Requires an FTS index on `col`; errors otherwise. Tokenization rules: ASCII split + lowercase, no stemming, no stop-list. Multi-token queries use any-term (OR) semantics. *(Phase 8b)* |
| `bm25_score(col, 'q')` | Real (f64) | Per-row BM25 relevance score for the given query. Higher is more relevant. Requires an FTS index on `col`. Pairs with `fts_match` in the canonical `WHERE … ORDER BY bm25_score(...) DESC LIMIT k` shape, which the optimizer probes via the inverted index instead of scanning rows. *(Phase 8b)* |
All three vector-distance functions take exactly two arguments, both of which must be vectors of the same dimension. Either argument can be a column reference (`embedding`), a bracket-array literal (`[0.1, 0.2, 0.3]`), or any sub-expression that evaluates to a vector. Mismatched dimensions error with `vector dimensions don't match (lhs=N, rhs=M)`.
The KNN ranking pattern that motivates this set:
```sql
SELECT id, title FROM docs
ORDER BY vec_distance_l2(embedding, [0.1, 0.2, ..., 0.0])
LIMIT 10;
```
> **Operator forms (`<->` `<=>` `<#>`) are not supported yet.** They're the de facto pgvector convention but blocked on a sqlparser limitation — will land as a Phase 7b.1 follow-up. Use the function-call form for now.
#### JSON path syntax
The `json_*` functions accept a string path argument with a small subset of JSONPath:
| Token | Meaning |
|---|---|
| `$` | Root of the document (default if path is omitted). |
| `.key` | Object member access. Bare keys only — no quoted-string variant yet. |
| `[N]` | Array index (0-based). Negative indices are not supported. |
Tokens chain naturally: `$.user.tags[0]`, `$[2].name`, `$.matrix[1][0]`. A malformed path (unbalanced brackets, missing `$`) errors at runtime with a typed message; a well-formed path that simply doesn't resolve returns `NULL`.
```sql
CREATE TABLE events (id INTEGER PRIMARY KEY, payload JSON);
INSERT INTO events (payload) VALUES
('{"user": {"name": "alice", "tags": ["admin", "ops"]}, "score": 42}'),
('{"user": {"name": "bob", "tags": []}, "score": 7}');
SELECT id,
json_extract(payload, '$.user.name') AS name,
json_extract(payload, '$.user.tags[0]') AS first_tag,
json_array_length(payload, '$.user.tags') AS tag_count,
json_type(payload, '$.score') AS score_type
FROM events
WHERE json_extract(payload, '$.user.name') = 'alice';
```
#### FTS query patterns
```sql
CREATE TABLE docs (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO docs (body) VALUES ('rust embedded database');
INSERT INTO docs (body) VALUES ('postgres relational database server');
CREATE INDEX docs_fts ON docs USING fts (body);
-- Lexical filter (Boolean predicate).
SELECT id FROM docs WHERE fts_match(body, 'database');
-- Top-k by BM25 relevance — the optimizer probes the inverted index
-- when WHERE / ORDER BY share the same query string and direction.
SELECT id FROM docs
WHERE fts_match(body, 'embedded database')
ORDER BY bm25_score(body, 'embedded database') DESC
LIMIT 5;
```
See [`docs/fts.md`](fts.md) for the canonical FTS reference (tokenizer rules, BM25 parameters, persistence, hybrid retrieval with `vec_distance_*`).
### Type coercion in arithmetic
- **Integer-only ops stay integer.** `1 + 2` → `3` (Integer).
- **Any `REAL` operand promotes to `f64`.** `1 + 2.0` → `3.0` (Real).
- **Divide/modulo by zero** returns a typed runtime error rather than panicking: `division by zero` for `/` and `%`.
- **`TEXT` in arithmetic context** errors — `'hello' + 1` is not silently coerced.
### NULL handling
SQLRite follows standard SQL three-valued logic:
- **Comparisons involving NULL** (`NULL = 1`, `1 < NULL`) evaluate to unknown, which behaves as `false` inside `WHERE`. Neither the NULL = NULL equality nor the NULL <> NULL inequality is true — use **`IS NULL`** / **`IS NOT NULL`** for explicit null tests (`SELECT … WHERE col IS NULL`). NULLs are not stored in secondary, HNSW, or FTS indexes, so `IS NULL` always falls through to a full scan; that's correct, just not as fast as an indexed equality probe.
- **Logical operators with NULL**: `NULL AND false` → `false`, `NULL AND true` → `NULL`, `NULL OR true` → `true`, `NOT NULL` → `NULL`. The short-circuit rules prevent NULL from propagating when one operand already decides the result.
- **Arithmetic with NULL**: any operand NULL → result NULL. `NULL + 1` → `NULL`.
- **String concat with NULL**: `'foo' || NULL` → `NULL` (same propagation as arithmetic).
### Case sensitivity
- **Keywords** (`SELECT`, `FROM`, `AND`, `TRUE`, `NULL`, …) are case-insensitive. `select`, `SELECT`, `SeLeCt` all parse.
- **Identifiers** (table names, column names) are **case-sensitive** — no normalization is applied at definition or lookup time. `CREATE TABLE Users (…)` followed by `SELECT * FROM users` fails with `Table doesn't exist`. (This is the opposite of SQLite's default; we'll revisit once the cursor refactor in Phase 5 lands.)
- **String literals** preserve case: `'Alice'` stays `Alice`.
---
## Transactions
```sql
BEGIN;
INSERT INTO users (name) VALUES ('alice');
UPDATE counters SET n = n + 1 WHERE name = 'signups';
COMMIT;
```
Or:
```sql
BEGIN;
DELETE FROM users WHERE banned = TRUE;
ROLLBACK; -- nothing was actually deleted
```
### Semantics
- **`BEGIN`** deep-clones the in-memory database into a snapshot held on `db.txn`. Auto-save is **suppressed** while the transaction is open — mutations accumulate in memory.
- **`COMMIT`** flushes every accumulated change to the WAL in one atomic commit frame and drops the snapshot. Readers of the file after COMMIT see all of the transaction's changes at once.
- **`ROLLBACK`** replaces the live state with the snapshot and drops the snapshot. Nothing hits disk.
### Details that matter
- **Nested `BEGIN` is rejected** with `a transaction is already open`. No savepoints yet.
- **`BEGIN` on a read-only database** (`sqlrite --readonly foo.sqlrite`) is rejected with `cannot execute: database is opened read-only`.
- **Runtime errors mid-transaction do NOT auto-rollback.** If an `INSERT` fails inside a transaction (UNIQUE violation, type mismatch, bad syntax), the transaction stays open. The caller decides whether to `ROLLBACK` or `COMMIT` whatever succeeded before the failure.
- **`COMMIT`'s disk write failing DOES auto-rollback.** If the save at COMMIT time errors (disk full, permission denied, checksum mismatch), SQLRite restores the pre-BEGIN snapshot and surfaces `COMMIT failed — transaction rolled back: <underlying error>`. Leaving in-flight mutations live after a failed COMMIT would be unsafe — any subsequent non-transactional statement's auto-save would silently publish partial work.
- **Cost**: `BEGIN` is `O(N)` in the total size of the in-memory database because of the snapshot clone. On a huge database, opening a transaction just to run a single read-only query is wasteful — use a plain `SELECT` instead.
- **Visibility to other processes**: with POSIX file locks (Phase 4a–4e), a writer excludes all concurrent readers anyway, so "uncommitted transaction state leaking to a concurrent reader" isn't a concern — no concurrent reader exists during an open transaction.
---
## `VACUUM`
```sql
VACUUM;
```
Compacts the database file: rewrites every live table, index, HNSW graph, FTS posting tree, and `sqlrite_master` itself contiguously from page 1, drops the freelist, and lets the next checkpoint truncate the tail.
- **Bare `VACUUM;` only.** Modifiers — `VACUUM FULL`, `VACUUM REINDEX`, table targets, `TO ... PERCENT`, `BOOST` — are parsed (sqlparser supports them) but rejected at execution with `VACUUM modifiers (FULL, REINDEX, table targets, etc.) are not supported`.
- **Refused inside a transaction.** `BEGIN; VACUUM;` errors with `VACUUM cannot run inside a transaction`. Use `COMMIT;` first, then `VACUUM;`.
- **No-op on in-memory databases.** Returns a `VACUUM is a no-op for in-memory databases` status string and does nothing — there's no file to compact.
- **Status string** carries pages and bytes reclaimed: `VACUUM completed. <N> pages reclaimed (<B> bytes).`
- **Format-version side effect.** A v4/v5 file that has been promoted to v6 by an earlier drop stays at v6 after VACUUM (v6 is a strict superset; we don't downgrade). A file that's already at v4/v5 because no drop ever happened on it doesn't get bumped by VACUUM.
When to run it: any time after a string of `DROP TABLE` / `DROP INDEX` / `ALTER TABLE DROP COLUMN` operations if you care about file size. SQLRite reuses freelist pages on subsequent inserts, so a write-heavy workload may not need VACUUM at all — its main use is reclaiming space when you don't expect to grow back.
### Auto-VACUUM (SQLR-10)
Manual `VACUUM;` is rarely needed in practice: by default, every page-releasing DDL (`DROP TABLE`, `DROP INDEX`, `ALTER TABLE DROP COLUMN`) checks the freelist after committing and runs `vacuum_database` automatically when the freelist exceeds **25%** of `page_count` (SQLite parity). The trigger:
- skips databases under 16 pages (64 KiB) so tiny files don't churn,
- skips inside an explicit transaction (the freelist isn't accurate until `COMMIT`),
- skips on in-memory and read-only databases.
The threshold is tunable per-connection from Rust:
```rust
let mut conn = Connection::open("db.sqlrite")?;
conn.set_auto_vacuum_threshold(Some(0.5))?; // fire only when freelist > 50%
conn.set_auto_vacuum_threshold(None)?; // disable entirely (manual VACUUM only)
```
…or via SQL (SQLR-13), which is the path SDK / FFI / MCP consumers reach for since they can't call the Rust setter directly:
```sql
PRAGMA auto_vacuum; -- read; renders a single-row result set
PRAGMA auto_vacuum = 0.5; -- arm the trigger at 50%
PRAGMA auto_vacuum = 0; -- arm at 0% (compact on any released page)
PRAGMA auto_vacuum = OFF; -- disable; equivalent: NONE, 'OFF', 'NONE'
```
Out-of-range values (anything outside `0.0..=1.0`, `NaN`, `±∞`) and unknown identifiers like `WAL` / `FULL` are rejected with a typed error — the trigger never silently saturates or falls back to a default. The setting is per-`Connection` runtime state — it's not persisted in the file header, so every reopen starts at the default `Some(0.25)`.
### `PRAGMA journal_mode` (Phase 11.3, SQLR-22)
> The full Phase 11 user-facing reference — conceptual model, embedding API, SDK error mapping, REPL meta-commands, durability story, limitations — lives at [`docs/concurrent-writes.md`](concurrent-writes.md). This section is the SQL-syntax reference.
Selects the per-database concurrency model. `wal` (default) is the legacy WAL-backed pager every pre-Phase-11 build used; `mvcc` opts the database into multi-version concurrency control (Phase 11 — concurrent writes via `BEGIN CONCURRENT`).
```sql
PRAGMA journal_mode; -- read; renders a single-row "wal" or "mvcc"
PRAGMA journal_mode = mvcc; -- opt into MVCC for this database
PRAGMA journal_mode = wal; -- switch back (rejected if the MvStore
-- already carries committed versions)
```
Case-insensitive on both the pragma name and the value. Quoted values (`'mvcc'`) work; numeric values are rejected (the field is enum-shaped). Unknown modes return a typed error and don't disturb the existing setting.
The setting is **per-database** — every `Connection::connect` sibling sees the same value. Reachable through the public API as `Connection::journal_mode() -> JournalMode`.
---
## `BEGIN CONCURRENT` (Phase 11.4, SQLR-22)
> For the conceptual walkthrough (version chains, snapshot-isolation visibility, the WAL log-record durability story, REPL `.spawn` demos), see [`docs/concurrent-writes.md`](concurrent-writes.md). This section is the SQL-syntax reference.
Opens a transaction that doesn't acquire the engine's single-writer lock — multiple `BEGIN CONCURRENT` transactions can coexist, on the same `Connection` or across sibling [`Connection::connect`](embedding.md#sharing-one-database-across-threads) handles. Writes accumulate against a per-transaction snapshot; at `COMMIT`, the engine validates the write-set against any versions that committed after the transaction's `begin_ts` and aborts with [`SQLRiteError::Busy`](../src/error.rs) if some other transaction superseded a row.
```sql
PRAGMA journal_mode = mvcc; -- opt the database into MVCC
BEGIN CONCURRENT;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT; -- may return Busy → caller retries
```
**Retry shape (Rust):**
```rust
loop {
conn.execute("BEGIN CONCURRENT")?;
conn.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")?;
conn.execute("UPDATE accounts SET balance = balance + 50 WHERE id = 2")?;
match conn.execute("COMMIT") {
Ok(_) => break,
Err(e) if e.is_retryable() => continue,
Err(e) => return Err(e),
}
}
```
**`SQLRiteError::is_retryable()`** covers both `Busy` and `BusySnapshot`. Use it in retry helpers rather than matching the variants individually so adding a third retryable variant later doesn't break callers.
**Requirements + restrictions (v0):**
- Database must be in `journal_mode = mvcc` first. Plain `BEGIN CONCURRENT` against a `Wal`-mode database returns a typed error.
- DDL (`CREATE TABLE` / `CREATE INDEX` / `DROP TABLE` / `DROP INDEX` / `ALTER TABLE` / `VACUUM`) is rejected inside `BEGIN CONCURRENT` — the typed error keeps the transaction open so the caller can `ROLLBACK`.
- Nested `BEGIN CONCURRENT` (or plain `BEGIN` inside an open `BEGIN CONCURRENT`) is rejected with a typed error.
- Reads inside the transaction see the BEGIN-time snapshot through every public read path: `Connection::execute("SELECT …")` *and* `Statement::query()` / `Statement::query_with_params()` (the prepared-statement path). Phase 11.5 closed the prepare/query gap by routing the read side through a per-connection `Mutex<Option<ConcurrentTx>>` + `with_snapshot_read` helper.
- Tables touched by writes inside `BEGIN CONCURRENT` should not carry FTS / HNSW indexes — the per-row commit-apply path only maintains B-tree secondary indexes today. Plain `WHERE col = literal` index probing still works on the post-commit live database.
- `AUTOINCREMENT`-bearing INSERTs are not specifically guarded; two concurrent INSERTs that each allocate the same rowid surface as a `Busy` at the second commit. The plan's "reject AUTOINCREMENT under MVCC" gate is a clean follow-up.
---
## Read-only databases
A REPL launched with `sqlrite --readonly foo.sqlrite` (or `sqlrite::open_database_read_only(path, name)` programmatically) takes a shared POSIX advisory lock instead of an exclusive one. In that mode:
- `SELECT` works normally.
- Every write statement (`INSERT`, `UPDATE`, `DELETE`, `CREATE TABLE`, `CREATE INDEX`) is rejected **before** touching memory with `cannot execute: database is opened read-only`. The in-memory state never diverges from disk.
- `BEGIN` is rejected.
- Multiple read-only openers of the same file coexist (shared flock). Any read-write opener blocks all read-only openers and vice versa — POSIX's "many readers OR one writer, not both" semantics.
---
## Statement-level rules
- **One statement per call** — `process_command` / `Connection::execute` expects a single statement. Multi-statement strings (`"INSERT …; INSERT …;"`) are rejected with `Expected a single query statement, but there are N`. For multi-statement execution, use the SDK's `executescript` / `execute_batch` helpers (Phases 5c/5d).
- **Trailing semicolons** are optional. Both `SELECT 1` and `SELECT 1;` parse.
- **Empty / comment-only input** is a benign no-op — no error, no auto-save triggered.
- **Multi-line statements** work. The REPL (via rustyline) buffers continuation lines until a terminating semicolon is seen.
---
## Not yet supported
For context when you hit `NotImplemented`. See [Roadmap](roadmap.md) for when these land:
### Joins & composition
- `CROSS JOIN`, comma joins, `NATURAL JOIN`, `JOIN ... USING` — explicit `INNER` / `LEFT` / `RIGHT` / `FULL OUTER JOIN ... ON ...` works (SQLR-5); the others don't
- Aggregates / `GROUP BY` / `DISTINCT` *over* a JOIN — pipe through a subquery once subqueries land
- `fts_match` / `bm25_score` inside a JOIN expression — single-table-bound today
- Subqueries (scalar, `IN (SELECT ...)`, correlated)
- CTEs (`WITH`), recursive CTEs
- Views (`CREATE VIEW`)
### Aggregation & grouping
- `HAVING` — pre-aggregation `WHERE` works; post-aggregation filtering doesn't yet
- `DISTINCT` on `SUM` / `AVG` / `MIN` / `MAX` (only `COUNT(DISTINCT col)` is supported)
- `GROUP BY` on expressions — bare column names only
- Other aggregate functions (`GROUP_CONCAT`, `STRING_AGG`, …) — only `COUNT` / `SUM` / `AVG` / `MIN` / `MAX` are wired
### Predicate & expression
- `GLOB`, `REGEXP`
- `BETWEEN`
- `LIKE … ESCAPE '<char>'` — bare `LIKE` / `NOT LIKE` / `ILIKE` work; the explicit-escape form doesn't
- `IN (subquery)`, `IN UNNEST(...)` — only literal lists are supported
- `CASE WHEN ... THEN ... END`
- Expressions in the `SELECT` projection list — aggregate calls are the one allowed expression form (`SELECT age + 1 FROM users` is rejected)
- Built-in functions (`LENGTH`, `UPPER`, `LOWER`, `COALESCE`, `IFNULL`, date/time, `printf`, …)
### DDL
- `ALTER TABLE` extras: multi-operation (`ALTER TABLE foo RENAME TO bar, ADD COLUMN x ...`), `ALTER COLUMN ... SET / DROP DEFAULT`, `ALTER COLUMN ... TYPE`
- `ADD COLUMN` constraint extras: `PRIMARY KEY` and `UNIQUE` on the added column (would need backfill + uniqueness against existing rows)
- `DROP TABLE` / `DROP INDEX` extras: multi-target (`DROP TABLE a, b, c;`)
- `CREATE VIEW`, `CREATE TRIGGER`
- Table-level constraints (composite PK, composite UNIQUE, `FOREIGN KEY`, `CHECK`)
- Non-literal `DEFAULT` expressions (`CURRENT_TIMESTAMP`, function calls, column references)
- Composite / multi-column indexes
### Transactions
- Savepoints (`SAVEPOINT`, `RELEASE SAVEPOINT`, `ROLLBACK TO SAVEPOINT`)
- Isolation-level control (`BEGIN IMMEDIATE`, `BEGIN EXCLUSIVE`)
### Query shape
- `OFFSET`
- Multi-column `ORDER BY`, `NULLS FIRST/LAST`
- `UNION`, `INTERSECT`, `EXCEPT`
- `INSERT ... SELECT`
- `UPDATE ... FROM`, `DELETE ... USING`
- Window functions (`OVER (...)`, `FILTER (WHERE ...)`, `WITHIN GROUP`)
### Parameter binding
- Named placeholders (`:foo`, `$1`, `@x`) — only positional `?` is supported (SQLR-23)
### Session / schema
- Multiple attached databases (`ATTACH DATABASE`, `DETACH DATABASE`)
- `PRAGMA` statements other than `auto_vacuum` (SQLR-13) and `journal_mode` (SQLR-22 / Phase 11.3). The dispatcher is in place — adding a pragma is a single arm in `execute_pragma`. `synchronous`, `cache_size`, etc. are not yet wired up
- `REPLACE INTO`, `INSERT OR IGNORE`, `INSERT OR REPLACE` (conflict-resolution clauses)
---
## Cross-reference
- [Using SQLRite](usage.md) — REPL flow, meta-commands, history, read-only mode
- [Embedding](embedding.md) — the `Connection` / `Statement` / `Rows` API surfacing the same SQL
- [Storage model](storage-model.md) — how columns, rows, and indexes live in memory and on disk
- [SQL engine](sql-engine.md) — how a query flows from tokens to executor to rows
- [Roadmap](roadmap.md) — when each [Not yet supported](#not-yet-supported) entry lands