SQL Reference

Zatabase supports a practical SQL subset routed through the ZQL engine. SQL statements are auto-detected and executed, giving you familiar syntax with the performance of Zatabase’s patent-pending query engine.

Endpoint

All SQL statements are executed via:

POST /v1/sql
Authorization: Bearer <token>
Content-Type: application/json

{"query": "YOUR SQL HERE"}

Data Types

Type	Description	Example
TEXT	UTF-8 string	'hello world'
INTEGER	64-bit signed integer	42
FLOAT	64-bit floating point	3.14
BOOL	Boolean value	true, false
VECTOR(N)	Dense f32 vector of dimension N	ARRAY[1.0, 2.0, 3.0]

CREATE TABLE

Define a new table with typed columns:

CREATE TABLE products (
  name TEXT,
  price FLOAT,
  quantity INTEGER,
  active BOOL
);

With a vector column for embeddings:

CREATE TABLE documents (
  title TEXT,
  content TEXT,
  embedding VECTOR(384)
);

Under the hood, CREATE TABLE is handled by the ZQL engine, which defines the schema and provisions the storage.

INSERT

Insert a single row:

INSERT INTO products (name, price, quantity, active)
VALUES ('Widget', 9.99, 100, true);

Insert with a vector column:

INSERT INTO documents (title, content, embedding)
VALUES (
  'Introduction to Zatabase',
  'Zatabase is a hybrid data platform...',
  ARRAY[0.1, 0.2, 0.3, ..., 0.384]
);

The ARRAY[...] literal syntax is used for vector values. The number of elements must match the declared vector dimension.

SELECT

Basic select with all columns:

SELECT * FROM products;

Select specific columns:

SELECT name, price FROM products;

WHERE Clauses

Filter with equality:

SELECT * FROM products WHERE name = 'Widget';

Comparison operators:

SELECT * FROM products WHERE price > 5.0;
SELECT * FROM products WHERE quantity <= 50;
SELECT * FROM products WHERE price >= 5.0 AND quantity < 200;

Logical operators:

SELECT * FROM products WHERE active = true AND price < 20.0;
SELECT * FROM products WHERE name = 'Widget' OR name = 'Gadget';

Pattern matching:

SELECT * FROM products WHERE name LIKE 'Wid%';

NULL checks:

SELECT * FROM products WHERE quantity IS NOT NULL;

Range queries:

SELECT * FROM products WHERE price BETWEEN 5.0 AND 15.0;

Set membership:

SELECT * FROM products WHERE name IN ('Widget', 'Gadget', 'Doohickey');

ORDER BY

SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY quantity DESC;

LIMIT

SELECT * FROM products LIMIT 10;
SELECT * FROM products ORDER BY price DESC LIMIT 5;

Vector Similarity Search (KNN)

Use the <-> operator with ORDER BY and LIMIT for K-nearest neighbor search:

SELECT * FROM documents
ORDER BY embedding <-> ARRAY[0.1, 0.2, 0.3, ..., 0.384]
LIMIT 10;

This performs a distance-based search using the configured metric (L2 by default). See the Vector Search page for details on distance metrics and HNSW indexing.

UPDATE

Update columns for matching rows:

UPDATE products SET price = 12.99, active = true WHERE name = 'Widget';

Multiple columns can be set in a single statement:

UPDATE products SET quantity = quantity + 50, price = 8.99
WHERE name = 'Gadget' AND active = true;

A WHERE clause is required. Zatabase does not support unconstrained updates to prevent accidental bulk modifications. To update all rows, use an always-true condition:

UPDATE products SET active = false WHERE 1 = 1;

The response returns affected_rows with the number of rows modified:

{
  "rows": [],
  "affected_rows": 3
}

DELETE

Delete matching rows:

DELETE FROM products WHERE name = 'Widget';

Delete all rows from a table:

DELETE FROM products;

DROP TABLE

Remove a table and all its data:

DROP TABLE products;

ZQL Native Queries

In addition to SQL, Zatabase supports its native ZQL query language for advanced operations. You can send ZQL directly via the /api/zql/query endpoint:

# Insert a record using ZQL
curl -s -X POST https://your-project.zatabase.io/api/zql/query \
  -H "Authorization: Bearer $ZATABASE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query": "COMPOSE INTO products (name: '\''Widget'\'', price: '\''9.99'\'')"}'

# Search by similarity
curl -s -X POST https://your-project.zatabase.io/api/zql/query \
  -H "Authorization: Bearer $ZATABASE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query": "RETRIEVE * FROM products WHERE name ~ '\''Widget'\'' THRESHOLD 0.6 LIMIT 10"}'

The ZQL API also exposes dedicated endpoints for common operations:

Endpoint	Method	Description
/api/zql/query	POST	Execute arbitrary ZQL
/api/zql/insert	POST	Insert a record
/api/zql/search	POST	Similarity search
/api/zql/record/:id	GET	Get record by ID
/api/zql/record/:id	DELETE	Delete record by ID
/api/zql/flush	POST	Flush writes to disk
/api/zql/info	GET	Engine statistics

Transactions

Zatabase supports ACID transactions with configurable isolation levels:

# Begin a transaction
curl -s -X POST https://your-project.zatabase.io/v1/transactions/begin \
  -H "Authorization: Bearer $ZATABASE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"isolation_level": "read_committed", "timeout_seconds": 30}'

# Execute within the transaction
curl -s -X POST https://your-project.zatabase.io/v1/transactions/{tx_id}/execute \
  -H "Authorization: Bearer $ZATABASE_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"query": "INSERT INTO products (name, price) VALUES ('\''Gizmo'\'', 14.99)"}'

# Commit
curl -s -X POST https://your-project.zatabase.io/v1/transactions/{tx_id}/commit \
  -H "Authorization: Bearer $ZATABASE_TOKEN"

Supported isolation levels: read_uncommitted, read_committed, repeatable_read, serializable.

Notes

• Column names are case-sensitive
• String values use single quotes ('value')
• SQL vs. ZQL syntax is auto-detected; no configuration needed
• SQL = comparisons provide fast O(1) exact matching
• Vector operations via SQL are convenience wrappers over the native ZQL engine