TapirusDB

Standalone or Auto Multi-Model · SQL · Document · Vector · Graph

Query Syntax Cheat Sheet · Release v1.2.1 · Source & WASM v1.2.1 (Phases 1–9)

Every query syntax in one place.

Plain-English examples verified directly against the TapirusDB SQL parser (src/sql/parser.rs) — covering SQL, Joins, Recursive CTEs, Partial & Expression Indexes, Computed Columns, Scalar Subqueries, Date/Time built-ins, FTS5, Vectors, GraphRAG, Parquet/Excel Lakehouse, and TAP functions. Also available as Markdown in docs/QUERY_SYNTAX.md.

1. Quick Cheat Sheet (1-Minute Overview)

Pick a pattern below to get started immediately, or test it live inside Browser Studio Lite.

Create table + vector columnCREATE TABLE docs (id INTEGER PRIMARY KEY, title TEXT, emb VECTOR(384));
Upsert without errorsINSERT INTO docs (id, title) VALUES (1, 'A') ON CONFLICT DO UPDATE SET title = EXCLUDED.title;
Recursive tree queryWITH RECURSIVE t AS (SELECT ... UNION ALL SELECT ...) SELECT * FROM t;
Partial index (filtered)CREATE INDEX idx_err ON logs (endpoint) WHERE status >= 500;
Vector nearest neighborsSELECT id, title FROM docs VECTOR NEAR emb = [0.1, 0.2, 0.3] TOP 5;
Graph pattern queryGRAPH MATCH (a:Person)-[r:KNOWS]->(b:Person) WHERE a.id = 1 RETURN a, r, b;
Query Parquet in placeSELECT region, COUNT(*), MEDIAN(ms) FROM read_parquet('lake/**/*.parquet') GROUP BY region;
Inspect table columnsPRAGMA table_info('docs');

2. Tables, Data Types, Writes & Transactions

Supported column types: INTEGER (64-bit), REAL (float64), TEXT (UTF-8 / JSON), BLOB (bytes), and VECTOR(D) (dense f32 embeddings).

SQL · DDL, Batch Insert, Upsert & Savepoints
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    dept_id INTEGER NOT NULL REFERENCES departments(id) ON DELETE CASCADE,
    email TEXT UNIQUE,
    age INTEGER NOT NULL CHECK (age >= 18),
    role TEXT DEFAULT 'engineer',
    profile TEXT,
    embedding VECTOR(4)
);

-- Multi-row batch insert + upsert + RETURNING
INSERT INTO employees (id, dept_id, age, role)
VALUES (1, 10, 29, 'backend'),
       (2, 10, 34, 'ml')
ON CONFLICT DO UPDATE SET age = EXCLUDED.age, role = EXCLUDED.role
RETURNING id, role;

-- Update, delete, and transactions with SAVEPOINT
BEGIN;
  UPDATE employees SET role = 'principal' WHERE id = 1;
  SAVEPOINT sp1;
  DELETE FROM employees WHERE age < 18;
  ROLLBACK TO SAVEPOINT sp1;
  RELEASE SAVEPOINT sp1;
COMMIT;

-- Schema evolution & views
ALTER TABLE employees ADD COLUMN phone TEXT;
CREATE VIEW IF NOT EXISTS senior_staff AS SELECT id, email, role FROM employees WHERE age >= 30;

3. SELECT, Filters, Joins, CTEs & Window Functions

Supported WHERE operators: =, !=, <, <=, >, >=, column-to-column comparisons, scalar subqueries, AND, OR, [NOT] IN (...), [NOT] IN (SELECT ...), [NOT] EXISTS (...), LIKE, IS [NOT] NULL, BETWEEN ... AND .... Joins support INNER, LEFT, RIGHT, FULL, and CROSS JOIN; set ops support UNION [ALL], INTERSECT, and EXCEPT with trailing ORDER BY / LIMIT / OFFSET.

SQL · Expressions, Joins, WITH RECURSIVE & Window Functions
-- Arithmetic (+, -, *, /, %), string concat (||), CASE, IIF, CAST, multi-column ORDER BY
SELECT DISTINCT dept_id,
       role,
       age + 1 AS next_age,
       IIF(age >= 30, 'senior', 'mid') AS band,
       CAST(age AS REAL) AS age_f
FROM employees
WHERE age BETWEEN 25 AND 55 AND role IN ('backend', 'principal')
ORDER BY dept_id ASC
LIMIT 20 OFFSET 0;

-- Join, correlated scalar subquery & CROSS JOIN
SELECT d.id, d.name, (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.id) AS staff_cnt
FROM departments d
ORDER BY staff_cnt DESC, d.id ASC;

-- Recursive hierarchy + UNION / INTERSECT / EXCEPT with trailing ORDER BY
WITH RECURSIVE org_tree AS (
    SELECT id, manager_id, name, 0 AS depth FROM org WHERE manager_id IS NULL
    UNION ALL
    SELECT o.id, o.manager_id, o.name, t.depth + 1 FROM org o JOIN org_tree t ON o.manager_id = t.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth ASC;

-- Window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, SUM, AVG, COUNT, MIN, MAX
SELECT id, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drank,
       LAG(salary, 1) OVER (PARTITION BY dept_id ORDER BY id ASC) AS prev_salary
FROM payroll;

4. Indexes, FTS5 Full-Text Search, Triggers & Time Travel

Create single, composite, unique, partial (WHERE filter), or expression indexes (LOWER(email)), use GENERATED ALWAYS AS (...) computed columns, run FTS5 MATCH, enforce triggers, or query historical timestamps.

SQL · Partial/Expression Index, FTS5, Trigger & AS OF TIMESTAMP
-- Composite, partial & expression indexes
CREATE INDEX IF NOT EXISTS idx_emp_dept_role ON employees (dept_id, role);
CREATE UNIQUE INDEX idx_lower_email ON employees (LOWER(email)) WHERE active = 1;
CREATE INDEX idx_high_latency ON telemetry (endpoint, latency_ms) WHERE status_code >= 500;

-- FTS5 full-text search
CREATE VIRTUAL TABLE docs_fts USING fts5(title, body);
INSERT INTO docs_fts (title, body) VALUES ('WAL Guide', 'Atomic crash recovery and snapshots');
SELECT title, body FROM docs_fts WHERE docs_fts MATCH 'recovery';

-- Trigger with RAISE(ABORT|FAIL|ROLLBACK|IGNORE, 'msg')
CREATE TRIGGER IF NOT EXISTS guard_balance
BEFORE UPDATE ON accounts FOR EACH ROW
WHEN (NEW.balance < 0)
BEGIN
    SELECT RAISE(ABORT, 'balance cannot be negative');
END;

-- Time-travel query
CREATE TABLE audit_log (id INTEGER PRIMARY KEY, msg TEXT) WITH SYSTEM VERSIONING;
SELECT * FROM audit_log AS OF TIMESTAMP 1731600000;

5. JSON Extraction & Vector AI Search

Extract fields from JSON TEXT columns with ->> and run HNSW nearest-neighbor queries (VECTOR NEAR ... TOP k) or scalar similarity helpers directly inside SQL.

SQL · JSON Path & Vector Similarity
-- JSON path extraction
SELECT id, profile->>'city' AS city
FROM employees
WHERE profile->>'tier' = 'enterprise';

-- Top-K HNSW Vector Search with SQL pre-filter WHERE
SELECT id, title
FROM docs
VECTOR NEAR embedding = [0.12, -0.44, 0.89, 0.03] TOP 5
WHERE category = 'engineering';

-- Scalar distance metrics: VECTOR_COSINE, VECTOR_L2, VECTOR_DISTANCE
SELECT id,
       VECTOR_COSINE(embedding, [0.12, -0.44, 0.89, 0.03]) AS sim,
       VECTOR_L2(embedding, [0.12, -0.44, 0.89, 0.03]) AS l2
FROM docs
ORDER BY sim DESC
LIMIT 5;

6. Graph & GraphRAG Queries

Insert labeled nodes and weighted edges, match patterns with openCypher-style GRAPH MATCH, traverse hops, compute shortest paths, or invoke built-in CSR graph algorithms.

SQL · Property Graph, Pattern Match & Algorithms
GRAPH INSERT NODE 1 LABEL 'Service' PROPERTIES '{"name":"auth-api","tier":1}';
GRAPH INSERT NODE 2 LABEL 'Database' PROPERTIES '{"name":"users-db"}';
GRAPH INSERT EDGE 1 -> 2 LABEL 'DEPENDS_ON' WEIGHT 0.95 PROPERTIES '{"critical":true}';

GRAPH MATCH (s:Service)-[r:DEPENDS_ON]->(d:Database)
WHERE s.tier = 1
RETURN s, r, d;

GRAPH TRAVERSE FROM 1 OUTGOING LABEL 'DEPENDS_ON' MAX_DEPTH 3;
GRAPH SHORTEST_PATH FROM 1 TO 42;

-- Algorithms: PAGERANK, CONNECTED_COMPONENTS, BETWEENNESS, LOUVAIN
GRAPH ALGORITHM PAGERANK damping=0.85 iterations=20 tolerance=0.0001;
GRAPH ALGORITHM LOUVAIN;

7. Big Data, Lakehouse & Vectorized Analytics

Query external files directly without creating tables first: read_parquet(), read_excel() / read_xlsx(), read_csv(), read_tsv(), read_jsonl(), read_sqlite(), read_pdf(), and read_text() / read_md(). With olap + bigdata features (on main), Parquet scans automatically perform Hive pruning, row-group zone-map pruning, column projection, metadata COUNT(*), and multi-core execution.

SQL · Parquet Hive Globs, Stats & Bi-Directional COPY
-- Statistical aggregates: COUNT(DISTINCT), STDDEV, STDDEV_POP, VARIANCE, MEDIAN, PERCENTILE
SELECT region,
       COUNT(*),
       COUNT(DISTINCT user_id) AS unique_users,
       STDDEV(latency_ms) AS stddev_ms,
       MEDIAN(latency_ms) AS p50_ms,
       PERCENTILE(latency_ms, 0.95) AS p95_ms
FROM read_parquet('lake/**/*.parquet')
WHERE region = 'apac' AND latency_ms > 100
GROUP BY region
HAVING COUNT(*) > 10
ORDER BY p95_ms DESC
LIMIT 20;

-- Read Excel sheet or legacy SQLite table directly
SELECT * FROM read_excel('sales.xlsx', 'Q4') LIMIT 10;
SELECT * FROM read_sqlite('legacy.db', 'orders') LIMIT 10;

-- Bulk import & partitioned Parquet export
COPY events FROM 'incoming/events.parquet' (FORMAT PARQUET);
COPY (SELECT * FROM events WHERE year = 2026)
  TO 'exports/events_lake' (FORMAT PARQUET, PARTITION_BY region);

8. TAP Functions, Scalar Built-ins & Utility Pragmas

Built-in scalar functions: IIF, IFNULL, NULLIF, COALESCE, CONCAT, REPLACE, INSTR, SUBSTR, TRIM/LTRIM/RTRIM, UPPER, LOWER, LENGTH/LEN, REVERSE, TYPEOF, ABS, ROUND, FLOOR, CEIL, SQRT, POWER, MOD, HEX, and UTC date/time functions (DATE, TIME, DATETIME, STRFTIME, UNIXEPOCH, JULIANDAY, CURRENT_DATE, CURRENT_TIMESTAMP). Note: default TAP weights are deterministic heuristic baselines.

SQL · TAP Decision Functions, Date/Time, PRAGMA & Maintenance
-- TAP heuristic classification, verification, scoring & routing
SELECT id,
       TAP_CLASSIFY(body, 'billing, technical, sales') AS label,
       TAP_VERIFY(body, 'payment failed') AS is_payment_error,
       TAP_SCORE(body, 'urgent outage') AS urgency,
       TAP_ROUTE(body, 'escalate_l2, normal_queue') AS route
FROM tickets;

-- Date/time & SQLite built-ins
SELECT DATE('2026-01-31', '+1 month'), STRFTIME('%Y-%m-%d', 'now'), IIF(urgency >= 0.8, 'high', 'normal');

-- Grounded variants with an explicit context column
SELECT id, TAP_CLASSIFY_GROUNDED(body, context_col, 'refund, bug') FROM tickets;
SELECT id, TAP_VERIFY_GROUNDED(body, context_col, 'approved') FROM tickets;

-- Pragmas, Query Plans & Maintenance
PRAGMA table_info('employees');
PRAGMA foreign_keys = on;
PRAGMA time_travel = on;

EXPLAIN QUERY PLAN SELECT * FROM employees WHERE dept_id = 10;
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE dept_id = 10 OR role = 'backend'; -- USING INDEX UNION when indexed
ANALYZE employees;
VACUUM INTO 'clean_backup.tapir';