Docs / SQL Reference

SQL Reference

Maree-DB implements SQL:2023 with extensions for multi-model queries, AI, compliance, and self-verification.

Standard Statements

Statement	Notes
SELECT	Full SQL:2023. JOINs, CTEs (WITH/WITH RECURSIVE), window functions, DISTINCT, ORDER BY, LIMIT/OFFSET, FETCH FIRST.
INSERT	Single and multi-row. INSERT ... RETURNING. INSERT ... ON CONFLICT (upsert).
UPDATE	Standard UPDATE ... SET ... WHERE. UPDATE ... RETURNING.
DELETE	Standard DELETE ... WHERE. DELETE ... RETURNING.
CREATE TABLE	Standard types + JSONB, VECTOR(n), GEOMETRY. GENERATED ALWAYS AS IDENTITY.
DROP TABLE	DROP TABLE [IF EXISTS] name [CASCADE \| RESTRICT].
ALTER TABLE	ADD COLUMN, DROP COLUMN, RENAME COLUMN, ADD CONSTRAINT, SET DEFAULT.
CREATE INDEX	B-tree, hash, GIN (JSONB), vector (ANN), R-tree (spatial). CONCURRENTLY supported.
BEGIN / COMMIT / ROLLBACK	Full ACID transactions with serializable snapshot isolation. Readers never block writers; writers never block readers.
SAVEPOINT	SAVEPOINT name; ROLLBACK TO SAVEPOINT name; RELEASE SAVEPOINT name.
CREATE VIEW	Standard views and materialised views.
CREATE PROCEDURE	Stored procedures (SQL and procedural).
EXPLAIN / ANALYZE	Query plan analysis with cost estimates and actual row counts.

Maree-DB Extensions

Sharding

-- Distribute a table across cluster nodes
CREATE TABLE orders (...) SHARD BY (customer_id);
CREATE TABLE events (...) SHARD BY HASH(event_id);

AI Agent Functions

-- Run an AI agent (local inference, no cloud)
SELECT AI_AGENT('summarise trends from support_tickets');
SELECT AI_AGENT('classify', document_text) FROM documents;

-- Generate vector embeddings
SELECT AI_EMBED('search query text');

-- Semantic similarity search
SELECT * FROM products
  ORDER BY vector_distance(embedding, AI_EMBED('red running shoes'))
  LIMIT 10;

Verification & Health

-- Run self-verification (available to all users)
SELECT * FROM VERIFY();
SELECT * FROM VERIFY('storage', 'sql');

-- Run health check (ADMIN only)
SELECT * FROM HEALTH_CHECK();

-- View verification history
SELECT * FROM _system.verify_history;
SELECT * FROM _system.health_check_history;

Compliance SQL

-- GDPR right to erasure
SELECT GDPR_ERASE('customer', 'email', 'user@example.com');

-- HIPAA audit trail query
SELECT * FROM HIPAA_AUDIT('patient_records', '2026-01-01', '2026-06-01');

-- PCI tokenisation
SELECT PCI_TOKENIZE('credit_cards', 'card_number');
SELECT PCI_DETOKENIZE('credit_cards', 'card_number', token);

-- SOC2 evidence export
SELECT * FROM SOC2_EVIDENCE('access_controls');

-- Single-run certification-readiness attestation - any user may run it
SELECT control_id, framework, status FROM CERTIFY();

Time-Series Functions

-- Bucket time-series data
SELECT time_bucket('1 hour', recorded_at) AS bucket,
       AVG(temperature) AS avg_temp
FROM sensor_readings
GROUP BY bucket
ORDER BY bucket;

-- First / last aggregates
SELECT first(value, recorded_at), last(value, recorded_at)
FROM metrics WHERE metric_name = 'cpu_usage';

Spatial Functions

-- Distance query
SELECT name, ST_Distance(location, ST_Point(-122.4, 37.7)) AS dist
FROM venues
WHERE ST_DWithin(location, ST_Point(-122.4, 37.7), 5000)
ORDER BY dist LIMIT 10;

-- Containment
SELECT * FROM zones
WHERE ST_Contains(boundary, ST_Point(-122.4, 37.7));