SQL dialect

Pipeline: SQL → lexer → parser → binder / catalog → logical plan → rewrite → cost model → vectorized executor.

Rules#

• One statement per request. A trailing ; is optional. Extra tokens after the statement are a syntax error.
• Unquoted identifiers fold to lowercase. Quoted "Ident" is preserved.
• Reserved words include FOREIGN, REFERENCES, CONSTRAINT, CASCADE, RESTRICT, ACTION, MATCH, ALTER, ADD, RENAME, ORDER, ASC, DESC, IF, EXISTS, WITH, OVER, UPSERT, LIKE, RETURNING, VIEW, SAVEPOINT, and CHECK. Quote them ("foreign") to use them as identifiers. PARTITION, ROWS, RANGE, UNBOUNDED, PRECEDING, FOLLOWING, CURRENT, ROW, EXCLUDED, INCLUDE, and CAST are contextual.
• Parameters are $1, $2, … (1-based). The CLI -c flag does not bind parameters; use a driver.
• NULL is typed. Compare with IS NULL / IS NOT NULL.
• Table names that start with nsql_ are reserved. The exception is CREATE TABLE nsql_schema_migrations with the exact history DDL used by migrations.

Types#

A table must declare PRIMARY KEY. Secondary indexes store secondary key + primary key. B-tree indexes may add INCLUDE (cols), WHERE predicate, and expression keys such as LOWER(name). EXPLAIN shows covering when the scan reconstructs the row from the index and skips the heap.

Statements#

CREATE TABLE   [FOREIGN KEY / REFERENCES …] [PARTITION BY RANGE|HASH|LIST]
CREATE TABLE name PRIMARY KEY (col, …) AS <query>
DROP TABLE [IF EXISTS]
ALTER TABLE    ADD/DROP [COLUMN] | ALTER [COLUMN] SET/DROP NOT NULL
               | ALTER [COLUMN] SET/DROP DEFAULT | RENAME
               | ADD/DROP CONSTRAINT (FOREIGN KEY or CHECK)
               | ADD/DROP/ATTACH/DETACH PARTITION
CREATE [OR REPLACE] VIEW name [(col, ...)] AS <query>
DROP VIEW [IF EXISTS]
CREATE INDEX / CREATE UNIQUE INDEX
CREATE SPATIAL INDEX   (POINT, or GEOMETRY / GEOGRAPHY)
CREATE FULLTEXT INDEX [WITH (ANALYZER = 'simple' | 'english' | 'french' | 'german' | 'spanish')]
CREATE VECTOR INDEX … USING HNSW | IVF | IVFPQ | SPARSE
DROP INDEX [IF EXISTS]
REBUILD INDEX [ONLINE]
MAINTAIN INDEX|TABLE|DATABASE
INSERT   VALUES (...) | <query>   [RETURNING]
UPSERT   [ON UNIQUE] [SET] [RETURNING]
SELECT   [WITH] [DISTINCT] [JOIN …] [WHERE] [GROUP BY] [HAVING] [ORDER BY] [SEARCH] [NEAREST] [NEAREST] [LIMIT] [OFFSET]
UPDATE   [WHERE] [LIMIT] [RETURNING]
DELETE   [WHERE] [LIMIT] [RETURNING]
BEGIN    [READ COMMITTED | SNAPSHOT | SERIALIZABLE]
COMMIT
ROLLBACK [TRANSACTION] | ROLLBACK TO [SAVEPOINT] name
SAVEPOINT name
RELEASE [SAVEPOINT] name
CANCEL QUERY '<id>'
ANALYZE  [table]
EXPLAIN  [ANALYZE] <statement>
BACKUP DATABASE
VERIFY BACKUP 'name'
CREATE / ALTER / DROP WORKFLOW    RUN WORKFLOW
CREATE / ALTER / DROP TRIGGER
CREATE / ALTER / DROP SCHEDULE    SHOW TASKS    CANCEL TASK
CREATE / ALTER / DROP RESOURCE GROUP
SET RESOURCE GROUP / RESET RESOURCE GROUP
CREATE USER / DROP USER
CREATE ROLE / DROP ROLE
GRANT / REVOKE
SUBSCRIBE TO table [WHERE operation = 'INSERT|UPDATE|DELETE'] [AFTER commit_lsn]
CLUSTER DRAIN | TRANSFER LEADER | MAINTENANCE ENABLE|DISABLE
SHOW DATABASES | TABLES | INDEXES | CONNECTIONS | QUERIES | TRANSACTIONS | LOCKS | CLUSTER | STORAGE

SELECT 1 and other FROM-less SELECT expressions are accepted (health checks, NOW(), constants).

INSERT takes its rows from a VALUES list or from a query:

INSERT INTO archive (id, total) SELECT id, total FROM orders WHERE closed;

The source is an ordinary SELECT, set operation or WITH, bound and optimized like any other query, and its output columns must match the columns being written. The source is read to completion before the first row is written, so INSERT INTO t SELECT ... FROM t doubles t exactly once instead of feeding itself. Reading it needs SELECT on every relation it touches, on top of INSERT on the target. UPSERT takes VALUES only.

CREATE DATABASE is not supported: a deployment serves exactly one database, and the parser rejects the statement (syntax, IF NOT EXISTS included) with that reason. To run another database, nextsql init a separate deployment. See Limits.

DROP TABLE [IF EXISTS] name removes the catalog row. A table referenced by a foreign key cannot be dropped (foreign_key). After commit and after older snapshots drain, detached heap, vector-store, and index pages return to the durable allocator freelist.

REBUILD INDEX name is a blocking rebuild from the transaction snapshot. REBUILD INDEX name ONLINE is supported for non-partitioned B+Tree, UNIQUE, JSON-path, and spatial indexes; vector, full-text, and partitioned indexes keep the blocking path.

SUBSCRIBE opens a continuous committed-change stream and cannot run inside an explicit transaction. AFTER resumes after an unsigned decimal commit LSN. See Change streams.

ALTER TABLE supports ADD [COLUMN], DROP [COLUMN], ALTER [COLUMN] SET/DROP NOT NULL, ALTER [COLUMN] SET/DROP DEFAULT, RENAME [COLUMN] … TO, RENAME TO, ADD CONSTRAINT / ADD FOREIGN KEY / ADD CHECK, and DROP CONSTRAINT. Adding a NOT NULL column to a non-empty table requires a DEFAULT. A PRIMARY KEY column cannot be dropped. An explicit NULL in INSERT is stored as NULL; a column default applies only when the statement omits the column.

ORDER BY#

ORDER BY expr [ASC|DESC] [, …] sorts the projected result. NULLs sort last in ASC and first in DESC. Keys may be output aliases, 1-based select-list ordinals, or source columns.

SEARCH orders by BM25 then primary key unless ORDER BY is present. SEARCH col [WEIGHT n] [, col [WEIGHT n] …] FOR '…' uses a FULLTEXT index whose column list matches in the same order (1–8 STRING/TEXT columns; phrases do not cross fields; optional WEIGHT scales per-field BM25 tf in (0, 64], default 1). Trailing ASCII * on a token is prefix search (cat* matches catalog; exact cat does not); trailing ASCII ~ is fuzzy matching (cat~ matches cot; optional ~1 / ~2); unadorned tokens apply typo tolerance when the term is absent from the vocabulary (databse matches database); prefix, fuzzy, and typo expansion is fail-closed. HIGHLIGHT(col) / SNIPPET(col) mark original matching tokens in the SELECT list of a SEARCH query. SELECT * … SEARCH … FACET col [, col …] returns independent histograms over the full match set (facet, value, count); LIMIT is per-facet top-N. NEAREST orders by distance then primary key unless ORDER BY is present. Hybrid results are reciprocal-rank fused, then truncated to LIMIT / OFFSET (or re-sorted when ORDER BY is present). A second NEAREST (dense VECTOR + SPARSEVECTOR) is dense+sparse+BM25 fusion. LIMIT n OFFSET m skips m ordered rows then returns up to n. OFFSET may appear before LIMIT. OFFSET without LIMIT skips and returns the rest. UPDATE / DELETE take LIMIT only.

Functions#

The complete signatures, return behavior, NULL rules, and examples are in the Function reference.

UUID(), NOW(), and AI() are evaluated at execution, not folded by the optimizer. AI() is a DECIMAL(p,0) autoincrement starting at 1. Explicit inserts bump the sequence when the value is at least the next number. Allocation is in the statement transaction (ROLLBACK reuses). Concurrent inserts exclusive-lock the sequence key.

EXPLAIN#

EXPLAIN SELECT name FROM products WHERE metadata.category = 'headphones';
EXPLAIN ANALYZE SELECT name FROM products SEARCH description FOR 'wireless' LIMIT 5;

ANALYZE (the statement) writes statistics first. EXPLAIN ANALYZE executes the plan. See hybrid queries for Candidates and Rerank.