SynapCores SQL Reference
SynapCores is an AI-native SQL database with first-class support for vector embeddings, AutoML, Cypher graph queries, and LLM functions. This page is the public reference for the SQL surface available in the shipped engine (v1.8.0-ce and forward). Use only features documented here — anything labelled "coming in v1.X" is not yet runnable.
If you want the engine's runtime-discoverable manual (the one MCP clients like Claude Code, Cursor, and OpenClaw query at runtime), it's exposed as the sql_manual tool over MCP and lives in AIDB_SQL_MANUAL.md in the engine repo. Anything on this page should match that doc.
Quick reference — SynapCores extensions at a glance
The features below distinguish SynapCores from generic SQL.
| Feature | Form | Min version |
|---|---|---|
| Vector column type | col VECTOR(N) where N is the embedding dimension |
1.0 |
| Text embedding | EMBED(text_expr) → VECTOR(N) |
1.0 |
| Cosine similarity | COSINE_SIMILARITY(vec_a, vec_b) → DOUBLE in [-1, 1] |
1.0 |
| Euclidean distance | EUCLIDEAN_DISTANCE(vec_a, vec_b) → DOUBLE ≥ 0 |
1.0 |
| LLM text generation | GENERATE(prompt [, options_json]) → TEXT |
1.0 (1.8.7 added options) |
| Decision model (typed, 0-token) | DECIDE(state, question_json [, options_json]) → JSON answer |
2.5.1 |
| Decision — choice | DECIDE_CHOICE(state, instructions, criteria_obj [, options_json]) → TEXT |
2.5.1 |
| Decision — yes/no | DECIDE_YESNO(state, instructions [, options_json]) → DOUBLE P(yes) |
2.5.1 |
| Decision — score rubric | DECIDE_SCORE(state, instructions, levels_array [, options_json]) → DOUBLE |
2.5.1 |
| Decision — multi-question | SELECT … FROM DECIDE_ALL(state, questions_obj [, options_json]) |
2.5.1 |
| NLI pair classification | NLI_CLASSIFY(model, premise, hypothesis) → JSON |
2.1.2 |
| NLI statement verification | NLI_VERIFY(model, statement, anchors_json [, options_json]) → JSON |
2.1.2 |
| NLI evidence aggregate | NLI_ANCHORS(text) aggregate → JSON array |
2.1.2 |
| JSON object literal builder | json_object(key, value, ...) → JSON |
1.8.7 |
| In-database agent | AGENT_RUN(persona, task [, options]) → TEXT |
1.6.6.9 (options 1.8.9) |
| Durable agent | CREATE AGENT name PERSONA … TASK … ON INSERT INTO t … |
1.9.0 |
| Learning-loop goal (objective only) | CREATE GOAL name WITH (objective = '…') — plan steps via REST |
2.3.0 |
| Persona as a database object | CREATE PERSONA name WITH (system_prompt = '…') |
1.14.2 |
| Edit / drop a persona | ALTER PERSONA name SET (…), DROP PERSONA [IF EXISTS] name |
1.14.2 |
| Inspect personas | SHOW PERSONAS [LIKE '…'], DESCRIBE PERSONA name |
1.14.2 |
| Fire a durable agent asynchronously | WAKE AGENT name |
1.14.2 |
| Agent-to-agent chaining | … ON UPDATE ON t WHERE … ALLOW AGENT ORIGIN |
1.14.2 |
| Store agent memory | MEMORY_STORE(ns, content [, metadata [, options]]) → TEXT |
1.8.5 |
| Recall agent memory | MEMORY_RECALL(ns, query [, top_k]) → table |
1.8.5 |
| Upsert agent memory | MEMORY_UPSERT(ns, content [, options]) → ADD/UPDATE/DELETE/NOOP |
1.8.9 |
| Forget agent memory | MEMORY_FORGET(ns, id) → BOOLEAN |
1.8.5 |
| Persistent agent memory | CREATE MEMORY name IDENTITY user_id |
1.14.3 |
| Write a memory | REMEMBER mem FOR user_id = 123 '<text>' → envelope |
1.14.3 |
| Read assembled context | RECALL mem FOR user_id = 123 ABOUT '<query>' → envelope |
1.14.3 |
| Authoritative present value | CURRENT mem FOR user_id = 123 ATTRIBUTE attr → envelope |
1.14.3 |
| Explain a remembered value | TRACE mem FOR user_id = 123 ATTRIBUTE attr → envelope |
1.14.3 |
| Authorized removal | FORGET mem FOR user_id = 123 ABOUT '<scope>' → envelope |
1.14.3 |
| Train AutoML model | CREATE EXPERIMENT name AS SELECT ... WITH (task_type=..., ...) |
1.5 |
| Predict with AutoML | SELECT AUTOML.PREDICT('model', col1, col2, ...) FROM t |
1.5 |
| Native model pull | PULL_MODEL('qwen2.5-coder:7b') → TEXT |
1.8.0 |
| Native model list | LIST_MODELS() → table |
1.8.0 |
| Native model drop | DELETE_MODEL('name') → TEXT |
1.8.0 |
| Cypher graph query | MATCH (n:Label) RETURN n |
1.6 |
| Cypher graph write | CREATE, MERGE, DETACH DELETE |
1.6 |
| PL procedure | CREATE PROCEDURE name(args) AS $$ … $$ LANGUAGE plpgsql |
1.6.6 |
| Trigger | CREATE TRIGGER [IF NOT EXISTS] trg BEFORE/AFTER INSERT/UPDATE/DELETE ON t EXECUTE PROCEDURE p() |
1.6.6 |
| Natural language SQL | ASK '…' |
1.0 |
| Append-only audit ledger | CREATE IMMUTABLE TABLE |
1.5 |
| Chain attestation | VERIFY TABLE t, VERIFY RECORD id IN t |
1.12.0 |
| Query Parquet on object storage | CREATE EXTERNAL TABLE t STORED AS PARQUET LOCATION 's3://…' |
1.16.0 |
| Re-read a lake schema | REFRESH EXTERNAL TABLE t |
1.16.0 |
| List lake tables | SHOW EXTERNAL TABLES |
1.16.0 |
Data Types
Scalar
BOOLEAN, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, DECIMAL(p, s), TEXT, VARCHAR(n), CHAR(n), BYTEA, JSON, JSONB, UUID, TIMESTAMP, DATE, TIME.
AI-native
VECTOR(N)— N is the embedding dimension. Must match the configured embedding model (defaultall-minilm:latestis 384).
Multimedia
AUDIO(format)—MP3,WAV,FLAC,AAC,OGGVIDEO(format)—MP4,AVI,MKV,WEBM,MOVIMAGE(format)—JPEG,PNG,WEBP,GIF,BMPPDF
Generic IMAGE / AUDIO / VIDEO without a format are not supported — always specify, e.g. IMAGE(JPEG).
Column constraints
PRIMARY KEY, UNIQUE, NOT NULL, CHECK (expr), DEFAULT expr, REFERENCES other_table(other_col).
Table-level [CONSTRAINT name] UNIQUE (...), FOREIGN KEY (...) REFERENCES ... and CHECK (expr) may also be written after the column list (see Table constraints — out-of-line and ALTER). PRIMARY KEY, UNIQUE and CHECK are enforced on write — see Constraint enforcement is now live.
Data Definition Language (DDL)
Databases
CREATE DATABASE [IF NOT EXISTS] db_name;
DROP DATABASE [IF EXISTS] db_name [CASCADE];
USE db_name;
SHOW DATABASES [LIKE 'pattern'];
Tables
CREATE TABLE [IF NOT EXISTS] table_name (
column_name data_type [column_constraint],
...
[table_constraint]
);
Worked example — products table with a vector column for semantic search:
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price DECIMAL(10, 2),
description TEXT,
description_vec VECTOR(384)
);
Append-only tables
CREATE IMMUTABLE TABLE audit_log (
id BIGINT PRIMARY KEY,
actor TEXT NOT NULL,
action TEXT NOT NULL,
payload JSON,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE IMMUTABLE TABLE produces a hash-chained, append-only ledger. INSERT works as normal; UPDATE and DELETE are refused. Useful for compliance audit trails.
Attest the chain from SQL (v1.12.0+):
VERIFY TABLE audit_log; -- is_valid, message (records/blocks verified, or the first break)
VERIFY RECORD 1 IN audit_log; -- record_id, is_valid, message
VERIFY TABLE walks every block, recomputes each record checksum, each sealed block's checksum and merkle root, and each block-to-block link, then cross-checks the chained record count against the rows actually stored. It errors — rather than passing — when the target is not an immutable table.
Immutable tables must be created empty with an explicit column list — CREATE IMMUTABLE TABLE ... AS SELECT is not supported; populate them with INSERT / INSERT ... SELECT.
Encryption at rest (v1.12.0+):
CREATE IMMUTABLE TABLE ssn_ledger (
id BIGINT PRIMARY KEY,
subject TEXT NOT NULL,
secret TEXT NOT NULL
) WITH (ENCRYPTION='AES256GCM');
WITH (ENCRYPTION='AES256GCM') encrypts both the hash-chain and the row-engine copy on disk.
- Requires the
AIDB_IMMUTABLE_MASTER_KEYenvironment variable (a hex-encoded key). If it is missing or invalid, theCREATEis rejected — never silently downgraded to a plaintext table (fail-closed). AES256GCMis the only supported algorithm; an unsupported or missing value is rejected.ENCRYPTION_KEY='name'(a named-key registry) is not supported in this release — useENCRYPTION='AES256GCM'alone (the table gets an auto-generated per-table key wrapped by the master key).- Encrypted immutable tables are supported only in the default database.
Alter, drop, index
ALTER TABLE t ADD COLUMN c data_type [constraint];
ALTER TABLE t DROP COLUMN c;
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t ALTER COLUMN c TYPE new_type;
ALTER TABLE t ADD CONSTRAINT name UNIQUE (col, ...);
ALTER TABLE t ADD CONSTRAINT name FOREIGN KEY (col, ...) REFERENCES parent (col, ...);
ALTER TABLE t DROP CONSTRAINT [IF EXISTS] name;
DROP TABLE [IF EXISTS] t [CASCADE];
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx_name ON t (col [ASC|DESC], ...);
DROP INDEX [IF EXISTS] idx_name;
Table constraints — out-of-line and ALTER (v2.2.0-ce)
Constraints can be declared out-of-line (table-level, with an optional name) in
CREATE TABLE, and added to or removed from an existing table with ALTER TABLE.
This complements the inline column constraints above.
-- Out-of-line, named constraints in CREATE TABLE (incl. composite keys)
CREATE TABLE pair_parent (
a INT, b INT,
CONSTRAINT pair_key UNIQUE (a, b)
);
CREATE TABLE pair_child (
id INT PRIMARY KEY, a INT, b INT,
CONSTRAINT pair_fk FOREIGN KEY (a, b) REFERENCES pair_parent (a, b)
);
-- Add / drop constraints on an existing table
ALTER TABLE accounts ADD CONSTRAINT uq_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id) REFERENCES customers (id);
ALTER TABLE orders DROP CONSTRAINT fk_cust;
ALTER TABLE orders DROP CONSTRAINT IF EXISTS maybe_missing;
ALTER TABLE ... ADD CONSTRAINT validates the existing rows first: if the current
data already violates the constraint (a duplicate for UNIQUE, an orphan row for a
FOREIGN KEY), the statement fails and nothing is attached — there is no partial
state to clean up. A FOREIGN KEY requires the parent's matching PRIMARY KEY/UNIQUE
key and compatible column types, and uses MATCH SIMPLE (a composite key with any NULL
component is skipped). DROP CONSTRAINT removes the backing enforcement; a key that is
still referenced by a foreign key cannot be dropped.
!!! note "Supported today"
ALTER TABLE ... ADD CONSTRAINT ... UNIQUE (...) and ... FOREIGN KEY (...) REFERENCES ...,
and ALTER TABLE ... DROP CONSTRAINT [IF EXISTS] name, on native row tables.
ALTER ADD CHECK, ALTER ADD PRIMARY KEY, and deferred constraints are not yet
supported and fail explicitly — declare CHECK and PRIMARY KEY inline or table-level
in CREATE TABLE instead. On columnar tables, PRIMARY KEY/UNIQUE are hints unless
the table is declared ENFORCED (see Columnar tables).
Partitioning
-- Range
CREATE TABLE sales (...) PARTITION BY RANGE (date);
CREATE TABLE sales_q1 PARTITION OF sales FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
-- List
CREATE TABLE customers (...) PARTITION BY LIST (country);
CREATE TABLE customers_usa PARTITION OF customers FOR VALUES IN ('USA', 'US');
-- Hash
CREATE TABLE activity (...) PARTITION BY HASH (user_id);
CREATE TABLE activity_0 PARTITION OF activity FOR VALUES WITH (MODULUS 4, REMAINDER 0);
Partition pruning is automatic when WHERE clauses constrain the partition key.
Views
CREATE [OR REPLACE] VIEW v AS SELECT ...;
CREATE MATERIALIZED VIEW mv AS SELECT ...;
REFRESH MATERIALIZED VIEW mv;
DROP VIEW v;
Introspection — INFORMATION_SCHEMA
Standard SQL system tables for catalog inspection:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'products';
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
Columnar tables (ENGINE = SYNAPCORES_COLUMNAR)
Built for analytical scans over large tables: a query reads only the columns it projects. Storage is Parquet parts with RocksDB metadata, and writes are copy-on-write — an affected part is rewritten rather than edited in place.
CREATE TABLE events (
id INT PRIMARY KEY,
ts TIMESTAMP,
region TEXT,
amount DOUBLE
) ENGINE = SYNAPCORES_COLUMNAR;
ENGINE = SYNAPCORES_COLUMNAR is the whole syntax — it takes no further
options. Adding any (, COMPRESSION = …, , BLOCK_SIZE = …) is a parse error,
and a WITH ( … ) clause on CREATE TABLE must not be used at all: it
silently retyped every column to TEXT before v1.15.0-ce.
Columnar tables support the full INSERT / UPDATE / DELETE surface.
!!! warning "PRIMARY KEY and UNIQUE are not enforced on columnar tables" They are accepted as a clustering and metadata hint. Duplicate keys are accepted — the insert succeeds and both rows are stored.
This follows the analytical-store convention: Snowflake, BigQuery, Amazon
Redshift, ClickHouse, Apache Iceberg and Delta Lake all allow the
declaration without enforcing it, because a uniqueness probe on every
insert removes the bulk-ingest advantage that is the reason to use
columnar storage at all. BigQuery makes you write `NOT ENFORCED`
explicitly.
**A row-storage table in this engine DOES enforce it**, so the same
statement behaves differently depending on `ENGINE`. `CREATE TABLE` emits
a warning when a columnar table declares one.
If you need uniqueness: de-duplicate at ingest, or use a row table for the
keyed data and a columnar table for the analytical copy.
COPY query results to lake
COPY (SELECT id, metro, amount FROM permits WHERE amount > 0)
TO LAKE 'matches/'
WITH (CONNECTION = 'werkor_lake', PARTITION_BY = (metro),
MODE = 'overwrite_partitions', COMPRESSION = 'zstd',
ROW_GROUP_SIZE = 122880, FILE_TARGET_MB = 128);
CREATE EXTERNAL TABLE permit_matches STORED AS PARQUET
LOCATION 'matches/' PARTITIONED BY (metro TEXT)
WITH (CONNECTION = 'werkor_lake');
SELECT COUNT(*) FROM permit_matches WHERE metro = 'nyc';
Materializes a one-shot SELECT/WITH read query as plain foreign Parquet.
The source streams directly to the writer, without the REST SELECT response
cap. The result returns job_id, run_id, rows_written, files_written,
bytes_written and partitions_touched.
CONNECTION is required and PARTITION_BY is optional. Path columns become
Hive directories and are omitted from the data files. Output fields are
OPTIONAL. Exact decimals and supported temporal types remain typed Parquet.
Writers emit row-group statistics, page indexes and high-cardinality blooms.
Permissions: configure an explicit destination write_allowlist. Empty,
including on existing upgraded destinations, disables COPY writes. Grants are
relative to the destination root: matches/ permits that child prefix; /
explicitly permits every non-managed child location. A read allowlist is separate
and is required to read back the output. COPY requires a fresh authenticated
principal with effective full data-write/source-database rights. Read-only or
table-restricted credentials and active user transactions are refused. Agent
operating privileges alone do not grant COPY authority. SQL cannot introduce
another bucket, absolute path or managed-lake ancestry.
Modes: overwrite_partitions is the default and replaces only partitions
actually produced; an empty source deletes nothing. append adds files without
deleting old data. Repeating a successful append duplicates the rows; it is not
an exactly-once retry interface. Compression supports zstd, snappy, gzip,
lz4, none. Row groups default to 122880 rows (maximum 1048576). File targets
default to 128 MiB (1..1024); rolling happens at row-group boundaries.
Publication: complete files are staged out of reader visibility, then published with recovery records before old files are removed. Plain object-store listing has no multi-file transaction: readers may temporarily see a mixture of complete old/new files during replacement. Publication across partitions is not a table-wide transaction. Reruns reconcile interrupted publication; unresolved ownership from a different engine is never stolen merely because a timer expired.
COPY shares the lake export concurrency cap and uses bounded batch/partition
writers. The source still obeys normal query memory, spill and timeout limits.
List/poll metadata at /v1/lake/copy/jobs and /v1/lake/copy/jobs/<job_id>, and
inspect/cancel runs through /v1/lake/runs. A disconnected SQL request may still
complete a publication already in progress; inspect the durable run before
retrying. No SQL text or credentials are stored in COPY job records.
The full contract, recovery limits and operating procedure are in
docs/sql/copy_to_lake.md.
External tables remain read-only for row mutations; COPY is a bulk export.
External tables — querying Parquet on object storage (v1.16.0-ce)
An external table is a name in the catalog pointing at a prefix in object storage. Nothing is copied into the database and no storage is allocated: the data stays where it was written, and creating the table costs one Parquet footer read.
CREATE EXTERNAL TABLE [IF NOT EXISTS] [db.]name [(col type [NOT NULL], …)]
STORED AS PARQUET
LOCATION '<uri>'
[PARTITIONED BY (col type, …)]
[WITH (CONNECTION = '<destination name>')];
DROP EXTERNAL TABLE [IF EXISTS] [db.]name;
REFRESH EXTERNAL TABLE [db.]name;
SHOW EXTERNAL TABLES [FROM <database> | IN <database>] [LIKE '<pattern>'];
The reason to care is what the planner does with it: an external table is an ordinary relation, so it joins, filters and aggregates against live tables in a single statement. A year of history sits in cheap object storage and still answers a question alongside today's rows — no ETL step back into the database, and no second query engine whose results you stitch together in application code.
CREATE EXTERNAL TABLE pings
STORED AS PARQUET
LOCATION 's3://acme-lake/raw/pings/'
PARTITIONED BY (dt DATE)
WITH (CONNECTION = 'lake_s3');
-- External table 'pings' registered over PARQUET at s3://acme-lake/raw/pings/
-- (25 column(s), 1 partition column(s))
SELECT c.name, COUNT(*) AS calls
FROM pings p
JOIN customers c ON c.id = p.customer_id
WHERE p.dt BETWEEN '2026-09-01' AND '2026-09-07'
GROUP BY c.name
ORDER BY calls DESC;
customers is an ordinary table. pings is Parquet on S3. One statement, one
result set.
External tables are read-only. INSERT, UPDATE and DELETE are refused
with the route to take instead:
INSERT is not allowed on 'pings': it is an external table and is read-only.
Its data is PARQUET at s3://acme-lake/raw/pings/. Write to the lake, then run
REFRESH EXTERNAL TABLE pings.
Partition layout. PARTITIONED BY declares the columns carried by the
directory name rather than by the files — the Hive-style layout the export job
writes:
s3://acme-lake/raw/pings/dt=2026-09-18/part-0000.parquet
s3://acme-lake/raw/_manifests/pings/dt=2026-09-18.json
Only dt=YYYY-MM-DD and snapshot_dt=YYYY-MM-DD count as partition
directories; anything else is skipped rather than half-read. A partition column
is never repeated in the column list — declaring it twice is a parse error,
since the relation would carry two columns of the same name. Pruning whole
partitions from their directory names, before an object is opened, is what makes
WHERE dt BETWEEN … cheap on a table holding a year.
!!! warning "Only partitions with a verified manifest are visible" The export writes a partition's manifest last, after every data file is durable. Object stores have no rename, so that write order is the atomicity: a partition with no manifest, or an unverified one, does not exist as far as a reader is concerned.
A half-written export is therefore invisible rather than partially
readable. That is the behaviour to want — a partial partition read as if it
were complete is a wrong answer that looks exactly like a right one. If a
query returns fewer rows than expected, check the export job's run history
before suspecting the query.
Schema: inferred, or declared and checked. Omit the column list and the
schema comes from the Parquet footer. Declare one and it is checked against the
data, and a mismatch fails at CREATE:
CREATE EXTERNAL TABLE orders: the declared schema does not match the data:
column 'amount' is declared DOUBLE PRECISION but the data holds BIGINT;
column 'region' is present in the data but not declared.
Omit the column list to take the schema from the data.
Failing there rather than at query time is the point: a table that registers cleanly and breaks on first use usually breaks in front of a BI tool that cannot show the error.
WITH (CONNECTION = '…') names the destination supplying credentials and
path confinement. The grammar marks it optional; in practice it is required, so
always write it. Without it the read path has no destination and therefore no
credentials, and CREATE fails:
external table 'pings' has no CONNECTION; the lake cannot know which
destination — and therefore which credentials — to read it with
It is the only supported option, and an unknown one is rejected rather than
ignored — quietly dropping CONNECTION would build a table reading through
credentials nobody chose.
LOCATION is resolved relative to the destination's prefix and checked against
that destination's read allowlist; a location outside it is refused, naming what
was compared. Anyone who can write SQL could otherwise read every object those
credentials reach, so the allowlist on the destination is the security boundary
— not the SQL statement.
REFRESH EXTERNAL TABLE re-reads the column list. The schema is stored so
DESCRIBE and BI introspection answer without a network round trip, and that is
what goes stale when an export adds or retypes a column:
REFRESH EXTERNAL TABLE pings;
-- External table 'pings' refreshed: 26 column(s) (was 25)
New partitions do not need it. Partitions are discovered per scan and cached for
partition_cache_ttl_secs (default 300 seconds), so yesterday's export becomes
visible on its own inside that window. If a day is missing, wait out the TTL or
check that the export finished — refreshing re-resolves the schema, not the
partition cache.
!!! warning "DROP EXTERNAL TABLE never deletes your data"
sql DROP EXTERNAL TABLE pings; -- External table 'pings' unregistered. No objects were deleted from storage.
The registration goes; not one object under `LOCATION` is touched, and
re-running `CREATE EXTERNAL TABLE` over the same prefix brings the table
back. This is the opposite of `DROP TABLE` on an ordinary table, which does
destroy the rows.
Deleting lake objects is a lifecycle-policy job for the object store, and
deliberately not something SQL can do here: a `DROP` that silently emptied
an S3 prefix would be an unrecoverable mistake one autocomplete away.
SHOW EXTERNAL TABLES returns one row per registered table — table_name,
format, location, connection, partition_columns, columns (the count,
not the names) and created_at. It answers "what is this database reading out
of object storage, and with whose credentials", which is worth reading before
granting access: an external table's reach is the reach of its connection.
SHOW EXTERNAL TABLES;
SHOW EXTERNAL TABLES FROM lake LIKE 'ping%';
Two further refusals worth knowing. If the build has no lake provider installed,
a query against an external table is an error ("external tables are not
available in this build"), never an empty result — an empty answer that should
have been an error is the worst thing a database can return. And a name already
taken by a regular table is refused even under IF NOT EXISTS, which means
"leave the external table I already registered alone", not "accept that this
name belongs to something else".
Indexes
-- Both clause orders are accepted (v1.15.0-ce): PostgreSQL leading, MySQL trailing.
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx ON t (col [ASC|DESC], ...) [USING BTREE|HASH];
CREATE [UNIQUE] INDEX [IF NOT EXISTS] idx ON t USING BTREE|HASH (col [ASC|DESC], ...);
CREATE INDEX [IF NOT EXISTS] idx ON t USING HNSW (vector_col)
[WITH (m = 16, ef_construction = 200, ef_search = 100, metric = 'cosine'|'euclidean'|'dot')];
DROP INDEX [IF EXISTS] idx;
BTREE (the default) and HASH are the scalar index types; HNSW indexes a
VECTOR(N) column (see below). The trailing form was a parse error before
v1.15.0-ce.
CREATE INDEX builds the index from the rows already in the table and reports
what it did:
Index idx_zip created on table zip_crosswalk (B-tree); 189361 rows indexed
CREATE UNIQUE INDEX over data that already contains a duplicate fails and the
index is not created — a half-populated unique index would be worse than
none.
Vector indexes — USING HNSW. An HNSW index on a VECTOR(N) column is
real SQL DDL. Checked on 2.4.3-ce, using the table from the
semantic search recipe:
CREATE INDEX recipe_sem_articles_hnsw ON recipe_sem_articles
USING HNSW (embedding)
WITH (m = 16, ef_construction = 200, ef_search = 100, metric = 'cosine');
-- Index recipe_sem_articles_hnsw created on table recipe_sem_articles (HNSW); 4 rows indexed, 0 NULL skipped
- The column goes in bare. The metric is an option,
metric = 'cosine'(the default),'euclidean'or'dot';USING HNSW (embedding COSINE)and pgvector'svector_cosine_opsare parse errors. ef_searchis set on the index; there is no per-queryefclause.- An unknown option fails at create time:
Unknown index option 'bogus_param'. SHOW INDEXES FROM tlists it withIndex_type = HNSW (COSINE).- No special query syntax:
ORDER BY COSINE_SIMILARITY(embedding, EMBED('…')) DESC LIMIT k, or a bare alias of it (… AS s … ORDER BY s DESC LIMIT k), makesEXPLAINshowHNSW IndexScanin place of the full scan.
USING IVFFLAT is rejected ("IVFFLAT SQL indexes are not supported; use USING
HNSW"). An index on a VECTOR(N) column with no USING clause is rejected
too, because a B-tree over an embedding accelerates nothing while leaving a
table that looks indexed.
!!! warning "Before v1.15.0-ce"
CREATE INDEX returned success without building anything, so no secondary
index ever accelerated a query, and index data was not rebuilt at startup —
which meant a restart silently stopped PRIMARY KEY enforcement. Indexes
created on an older build are registered but empty; CREATE INDEX ... IF NOT EXISTS on this version registers and backfills the missing index.
Data Manipulation Language (DML)
INSERT INTO t [(c1, c2, ...)] VALUES (v1, v2, ...), ...;
INSERT INTO t [(c1, c2, ...)] SELECT ...;
INSERT OR REPLACE INTO t (...) VALUES (...);
INSERT INTO t (...) VALUES (...) ON CONFLICT IGNORE;
UPDATE t SET c1 = v1, c2 = v2 [WHERE condition];
DELETE FROM t [WHERE condition];
rows_affected is the number of rows actually written. An INSERT ... SELECT whose SELECT matches nothing reports rows_affected = 0, so a client can detect a no-op — before v1.14.3 one entry point reported 1 for that case because it counted the result envelope rather than the write. The same rule holds for UPDATE and DELETE: a statement whose WHERE matches no row reports 0, which is what makes a conditional UPDATE usable as an atomic claim.
Worked example — populate a vector column from text using EMBED:
UPDATE products
SET description_vec = EMBED(description)
WHERE description_vec IS NULL;
Query Language
SELECT
SELECT [ALL | DISTINCT] expr [AS alias], ...
FROM table_name
[WHERE condition]
[GROUP BY expr, ...]
[HAVING condition]
[ORDER BY expr [ASC | DESC], ...]
[LIMIT n] [OFFSET k];
Since v2.1.0, comparisons between a numeric value and TEXT compare
numerically when the text parses as a number: int_col = '06' matches 6,
'0123' joins 123, and int_col < '30' orders numerically. Text that does
not parse equals no number (= is false, <> is true). Index lookups and
join keys follow the same domain, so results do not depend on the chosen plan.
Text-to-text comparison remains exact: '06' <> '6'.
ORDER BY can reference projection aliases directly:
SELECT id,
COSINE_SIMILARITY(description_vec, EMBED('wireless headphones')) AS similarity
FROM products
ORDER BY similarity DESC
LIMIT 10;
Joins
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= NOW() - INTERVAL '30 days';
INNER (default), LEFT, RIGHT, and FULL joins are supported. Use explicit JOIN…ON, not comma-separated FROM clauses.
CTEs and recursive queries
WITH recent_orders AS (
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) AS n_orders
FROM recent_orders
GROUP BY customer_id;
-- Recursive CTE — walk an org hierarchy
WITH RECURSIVE managers AS (
SELECT id, manager_id, name, 0 AS level FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name, m.level + 1
FROM employees e
JOIN managers m ON e.manager_id = m.id
)
SELECT * FROM managers ORDER BY level, name;
Window functions
SELECT id, category, price,
SUM(price) OVER (PARTITION BY category ORDER BY id) AS running_total,
AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;
Transactions
As of v2.0-ce transactions are atomic, isolated and crash-durable. Writes are held
until COMMIT, other connections cannot see them, and a COMMIT that returns success has
already reached the write-ahead log — so an acknowledged commit survives a hard kill, and
an unfinished transaction leaves nothing behind.
BEGIN [TRANSACTION];
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK TO SAVEPOINT after_debit; -- keeps the debit, drops the credit
RELEASE SAVEPOINT after_debit;
COMMIT; -- or ROLLBACK
!!! warning "Where you can run this"
A bare BEGIN sent to POST /v1/query/execute is refused with HTTP 400
transaction_session_required — that endpoint has no session to attach a transaction
to, and silently autocommitting is the pre-v2.0 bug. The two supported paths are:
- **A MySQL wire connection**, where `BEGIN` / `COMMIT` / `ROLLBACK` / `SAVEPOINT` work
as your client expects. Disconnecting rolls back an open transaction, and
`SET autocommit = 0` starts one on the next statement.
- **REST**: `POST /v1/transactions` returns an id; pass it as `transaction_id` on each
statement, then `POST /v1/transactions/{id}/commit`. Savepoints are
`POST /v1/transactions/{id}/savepoint` with `{"name": "..."}`, and rolling back to one
is `POST /v1/transactions/{id}/rollback` with `{"savepoint": "..."}`.
Isolation levels. READ COMMITTED (the SQL and REST default), REPEATABLE READ
(the MySQL default) and SERIALIZABLE are supported; REPEATABLE READ is write-skew-safe
(SQLite / InnoDB-RR parity). READ UNCOMMITTED is rejected, not quietly downgraded.
All see your own pending writes, including through scans.
Conflicts are retryable, and conservative. Concurrent writers can receive MySQL error
1213 / SQLSTATE 40001, or HTTP 409 with retryable: true — on real contention:
the same row, or a REPEATABLE READ / SERIALIZABLE transaction whose read set a
concurrent commit invalidated. Retry the whole transaction from a fresh BEGIN —
retrying only the COMMIT will not work. Two writes to different rows of the same table
no longer falsely conflict, including concurrent inserts into a table that carries a
FOREIGN KEY or a secondary UNIQUE constraint.
Ordinary DML also runs in an implicit transaction, so a statement that fails partway — including its cascades — rolls itself back.
!!! note "What does not participate" Columnar tables, immutable tables, external/lake Parquet reads, AI and memory side effects, enabled triggers and agent event bindings. Inside an explicit transaction a write to a table carrying a trigger or a durable-agent event binding is refused with HTTP 501; outside one it works normally, with the hook firing after commit. So a trigger's side effect is not part of the same commit — full ACID covers your rows and constraints, not trigger side effects. DDL inside an open transaction, cross-database foreign keys, and adding a foreign key to an existing table are all rejected.
Limits: 4,096 savepoint frames per transaction, and timeouts from 1 to 300 seconds.
AIDB_SQL_TRANSACTION_BACKEND=legacy returns to the pre-v2.0 non-durable path; any other
value fails startup rather than falling back silently.
Referential integrity — FOREIGN KEY (enforced as of v2.0-ce)
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT REFERENCES customers(id),
total DECIMAL(10,2)
);
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);
An INSERT or UPDATE naming a parent row that does not exist is rejected with
Constraint violation: Foreign key violation, and the whole statement rolls back.
Deleting a parent that still has children is rejected unless you declared an action.
ON DELETE / ON UPDATE accept CASCADE, SET NULL, SET DEFAULT, RESTRICT and
NO ACTION, applied within the same statement. The referenced column must be a declared
PRIMARY KEY or UNIQUE key of matching type. Composite keys use MATCH SIMPLE: a
NULL in any part exempts the whole reference.
Declare foreign keys in CREATE TABLE — adding one to an existing table is rejected, as
are foreign keys across databases.
!!! warning "Upgrading" Before v2.0 the syntax was accepted and nothing checked it, so existing data may violate constraints you declared. v2.0 rejects new violations but does not clean up old ones:
```sql
SELECT COUNT(*) FROM orders o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);
```
Qualified wildcards
SELECT t.* is supported as of v1.15.0-ce, including the fully qualified form
(analytics.orders.*). Where a relation has an alias, either the alias or the
table name resolves it — neither silently guesses. An unknown qualifier is an
error naming what is in scope.
SELECT o.*, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id;
Earlier versions rejected t.* with Complex projections not supported.
COUNT(t.*) is rejected: it is not the same aggregate as COUNT(*) once an
outer join is involved, so it is refused rather than guessed at.
Built-in Functions
Aggregate
COUNT, SUM, AVG, MIN, MAX, STDDEV, VARIANCE,
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col).
Math
ABS, CEIL/CEILING, FLOOR, ROUND, MOD, POWER/POW, SQRT, EXP, LOG/LN, LOG10, SIGN, TRUNCATE/TRUNC, PI, RAND/RANDOM, SIN, COS, TAN, ASIN, ACOS, ATAN, DEGREES, RADIANS.
String
UPPER, LOWER, LENGTH, SUBSTRING, CONCAT, TRIM, LTRIM, RTRIM, REPLACE, LEFT, RIGHT, LPAD, RPAD, REPEAT, REVERSE, INSTR/POSITION, ASCII, CHAR/CHR, INITCAP, MD5, SHA1, SHA256.
Date / time
NOW/CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, UNIX_TIMESTAMP, DATE_FORMAT(date, fmt), STR_TO_DATE(s, fmt), DATE_ADD(date, n, unit), DATE_SUB(date, n, unit), DATEDIFF(d1, d2), LAST_DAY, DAYNAME, MONTHNAME, QUARTER, WEEK/WEEKOFYEAR, DAYOFWEEK, DAYOFYEAR.
Format examples:
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') -- '2026-06-09 14:30:45'
DATE_ADD('2026-01-15', 30, 'DAY') -- 2026-02-14
DATEDIFF('2026-12-31', NOW()) -- days until year end
Conditional / null
GREATEST(a, b, ...), LEAST(a, b, ...), IF(cond, then, else)/IIF, IFNULL(expr, alt)/ISNULL, COALESCE(...), NULLIF(a, b), CASE WHEN ... THEN ... ELSE ... END.
Vector Operations
Distance operators
embedding <=> query_vector -- Cosine distance (1 - cosine similarity)
embedding <-> query_vector -- Euclidean distance
embedding <#> query_vector -- Inner product
Vector helper functions
VECTOR_ADD(vec1, vec2) -- elementwise add
VECTOR_SUBTRACT(vec1, vec2)
VECTOR_MULTIPLY(vec, scalar) -- scalar multiply
VECTOR_NORMALIZE(vec) -- unit-length normalize
VECTOR_MAGNITUDE(vec) -- L2 norm
VECTOR_DOT(vec1, vec2)
Top-K semantic search
SELECT id, name,
COSINE_SIMILARITY(description_vec, EMBED('running shoes')) AS similarity
FROM products
ORDER BY similarity DESC
LIMIT 10;
AI Functions
EMBED(text)
Computes an embedding for the given text using the configured embedding model.
- Argument: any
TEXTexpression. - Returns:
VECTOR(N)matching the configured model dimension. - The column you store the result in must use the matching dimension.
- Input longer than the model's input window (512 tokens for the default all-MiniLM) is truncated to the window and embedded by default (v2.4.8-ce+); the server logs each truncation, so it is never silent. Set
[query.ai_service] embed_truncate = false(orAIDB_EMBED_OVERSIZE=reject) to reject over-length input withprompt exceeds model input budgetinstead. For best retrieval quality, chunk long text to roughly 500 tokens rather than relying on truncation.
SELECT EMBED('wireless noise cancelling headphones');
UPDATE products SET description_vec = EMBED(description);
ARRAY_LENGTH(array_or_vector [, dimension]) · CARDINALITY(array_or_vector)
Returns the number of elements in an array or vector — most often the
dimensionality of an EMBED() result. CARDINALITY is an alias.
- Accepts a one-dimensional array or vector. A JSON-array string works too.
- The optional second argument is the dimension and must be
1; any other dimension returnsNULL, matching PostgreSQL.
-- How wide is the configured embedding model?
SELECT ARRAY_LENGTH(EMBED('hello')) AS dims;
SELECT CARDINALITY(tags) FROM articles;
Renaming a table
Both spellings rename one ordinary row table within its database (v1.17.0-ce). Rows, constraints, defaults and index identities are preserved across the rename and across a restart:
CREATE TABLE orders (id INT PRIMARY KEY, total DECIMAL(10,2));
INSERT INTO orders VALUES (1, 19.99), (2, 5.00);
ALTER TABLE orders RENAME TO archived_orders;
SELECT COUNT(*) FROM archived_orders; -- 2
RENAME TABLE archived_orders TO orders2;
SELECT SUM(total) FROM orders2; -- 24.99
The destination must not already exist, and the source must:
RENAME TABLE orders2 TO occupied; -- error: Table 'occupied' already exists
RENAME TABLE nosuch TO whatever; -- error: Table 'nosuch' does not exist
Row keys are rewritten, so the cost and the temporary write space grow with the size of the table. SQL against that store is briefly gated while the catalogs change, and a durable journal finishes any interrupted rename before later statements run. Re-prepare any prepared statements that referenced the old name.
Not supported, and refused before anything is written: cross-database moves,
columnar, external, partitioned and immutable tables, tables inside an active
transaction, auto-increment columns, names beginning with _, foreign keys and
dependent views. A tenant holding triggers, stored procedures or durable agents
must remove those first — their stored SQL cannot yet be rewritten safely.
Numeric casts
NUMERIC, DECIMAL, BIGNUMERIC and BIGDECIMAL are accepted as cast
targets, so ::NUMERIC both plans and executes rather than erroring.
SELECT (revenue_text)::NUMERIC AS revenue FROM imports;
SIGNED, UNSIGNED, SIGNED INTEGER and UNSIGNED INTEGER are accepted for
MySQL compatibility (v1.16.0-ce) and map to BIGINT, so a query copied from
MySQL — or emitted by a BI tool against the MySQL wire port — parses
unchanged. UNSIGNED is a spelling only; the result is a signed 64-bit
integer.
SELECT CAST('42' AS SIGNED) AS v; -- 42
SELECT CAST(total AS UNSIGNED INTEGER) FROM orders;
Casting to a scaled DECIMAL(p, s) is accepted from any numeric or text
source, but the precision and scale you write are not applied:
SELECT CAST(12.5 AS DECIMAL(10,2)); -- 12.5, exact, still scale 1
SELECT CAST('12.5' AS DECIMAL(10,2)); -- 12.5, but APPROXIMATE (a DOUBLE)
SELECT CAST(12 AS DECIMAL(10,2)); -- 12.0, approximate
A DECIMAL source passes through unchanged at its own scale; a TEXT or
integer source becomes an approximate DOUBLE. A cast is therefore not the
way to rescale money. Declare the column at the scale you want and let
INSERT or UPDATE coerce to it — as of v1.16.0-ce both do, and the declared
scale is honoured:
CREATE TABLE invoices (id INT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO invoices VALUES (1, 29.99);
SELECT amount FROM invoices; -- 29.99
UPDATE invoices SET amount = 7.7 WHERE id = 1;
SELECT amount FROM invoices; -- 7.70
COSINE_SIMILARITY(vec_a, vec_b) / EUCLIDEAN_DISTANCE(vec_a, vec_b)
SELECT id, name,
COSINE_SIMILARITY(description_vec, EMBED('running shoes')) AS similarity,
EUCLIDEAN_DISTANCE(description_vec, EMBED('running shoes')) AS dist
FROM products
ORDER BY similarity DESC
LIMIT 10;
GENERATE(prompt [, options])
Calls the configured completion model and returns the generated text.
prompt(TEXT, required).options(JSON, optional, v1.8.7+) — sampling + output-shape knobs. Build withjson_object(). Recognized keys:max_tokens(default 4096 in v1.8.7+, was 200 prior),temperature,top_p,top_k,repeat_penalty,seed(reproducible sampling),system(system prompt override),grammar(GBNF),grammar_triggers(lazy-activation array),response_format: "json"(engine applies a built-in JSON grammar).- Returns:
TEXT. Cached on identical (prompt, options) tuple within a session. - Local LLMs are slow per-row — use
GENERATEfor small result sets, not full-table scans.
-- Pre-v1.8.7 form — still works.
SELECT id,
GENERATE('Summarize this review in one sentence: ' || review_text) AS summary
FROM reviews
WHERE rating <= 2
LIMIT 50;
-- v1.8.7+ — deterministic JSON output with full sampling control.
SELECT GENERATE(
'Extract product, sentiment, reason as JSON: ' || review_text,
json_object(
'max_tokens', 1024,
'temperature', 0.2,
'seed', 42,
'response_format', 'json'
)
) AS analysis
FROM reviews LIMIT 10;
json_object(key1, value1, key2, value2, ...) (v1.8.7+)
Builds a JSON object literal from an even-length alternating list of (key, value) arguments. Designed for the options bag passed to GENERATE and other AI functions, but usable anywhere a JSON value is accepted. Keys must be TEXT; values can be any scalar SQL type. Returns JSON. Errors on odd-length argument lists or non-text keys.
SELECT GENERATE('Answer in one word: capital of France?',
json_object('max_tokens', 8, 'temperature', 0.0)) AS answer;
NLP helpers
SUMMARIZE(text, max_length)— abstractive summarySENTIMENT_ANALYSIS(text)— sentiment label/scoreEXTRACT_ENTITIES(text)— named entitiesEXTRACT_KEYWORDS(text, count)— keyword extractionCLASSIFY(text, categories)— zero-shot classificationTRANSLATE(text, target_lang)— translation
SEMANTIC_MATCH, MULTI_MODAL_SIMILARITY, CROSS_MODAL_SEARCH
Higher-level helpers used inside SEMANTIC JOIN and multi-modal queries — see the multi-modal section below.
Decision model — the DECIDE family (v2.5.1-ce)
Runs the native decision model (d1 / System One) over a state and one or more typed questions, returning a calibrated answer in a single forward pass with zero output tokens — this is the SQL form of POST /v1/systemone. It never calls the text-generation model: model resolution is options.model → [query.ai_service].decision_model → 'd1:latest'. Requires an engine with native inference (provider = "local").
Each question is one of three types:
choice— pick one option.criteriais an object ofoption → description(capped at 26 options).noul— yes/no; the answer isP(yes).criteriais optional and ignored (supply it or omit it).score— an ordered rubric.criteriais an array of 2–10 level labels, lowest first; the score is the probability-weighted (fractional) level index.
DECIDE(state, question_json [, options_json]) → JSON
The full form: returns the complete answer object — {type, choice|noul|score, probabilities, confidence} — as JSON.
SELECT DECIDE(
body,
json_object('type','choice','instructions','Which team should handle this?',
'criteria', json_object('billing','payments/refunds','shipping','delivery','technical','bugs'))
) AS routing
FROM tickets WHERE status = 'open' LIMIT 20;
Typed convenience scalars
Thin shells over the same decoder that return a single scalar — ideal in WHERE, ORDER BY, or a CREATE AGENT condition.
DECIDE_CHOICE(state, instructions, criteria_obj [, options_json])→TEXT— the chosen option.DECIDE_YESNO(state, instructions [, options_json])→DOUBLE—P(yes)in[0,1].DECIDE_SCORE(state, instructions, levels_array [, options_json])→DOUBLE— the expected (0-based, fractional) level index.
-- Route, flag, and score — all without generating a token.
SELECT id,
DECIDE_CHOICE(body, 'Which team?',
json_object('billing','payments/refunds','shipping','delivery','technical','bugs')) AS route,
DECIDE_YESNO(body, 'Is the customer angry?') AS angry_p,
DECIDE_SCORE(body, 'How urgent?', '["can wait","this week","today","right now"]') AS urgency
FROM tickets
WHERE status = 'open'
ORDER BY angry_p DESC;
DECIDE_ALL(state, questions_obj [, options_json]) — table-valued
Answers several questions at once (goes in FROM, like MEMORY_RECALL). questions_obj maps name → {type, instructions, criteria?}, mixing types; all are decided in one batched set of forward passes over the resident model (the state prefix is rendered once). Returns one row per question: question TEXT, answer_type TEXT, answer JSON, confidence DOUBLE.
SELECT question, answer_type, confidence, answer
FROM DECIDE_ALL(
'Customer charged twice, no shipment, wants it TODAY',
json_object(
'route', json_object('type','choice','instructions','Which team?',
'criteria', json_object('billing','payments','shipping','delivery')),
'angry', json_object('type','noul','instructions','Is the customer angry?'),
'urgency', json_object('type','score','instructions','How urgent?',
'criteria', '["can wait","this week","today","right now"]')
)
);
Agentic SQL — AGENT_RUN (v1.6.6.9+)
AGENT_RUN(persona, task) runs a complete in-database AI agent loop (ReAct: reason → call tool → observe → repeat) and returns the agent's final answer as TEXT. The agent has access to the same per-tenant database as the calling session — it can run SQL (execute_query), list/describe tables (list_tables, describe_table), and do semantic search (rag_search) inside one transaction.
-- Aggregation + reasoning over real rows
SELECT AGENT_RUN(
'aidb-assistant',
'Execute SELECT category, SUM(price*stock) AS v FROM products GROUP BY category.
Tell me which category has the highest v based on the actual returned data.'
) AS reply;
-- Document Q&A via semantic search
SELECT AGENT_RUN(
'aidb-assistant',
'Use rag_search on knowledge_docs to answer: what is the return policy?
Quote the most relevant passage verbatim.'
) AS reply;
-- Composed in a CTE — one agent call per row.
-- 'returns-triage' is not a built-in: create it first (see Personas below) or
-- this errors with "AGENT_RUN: unknown persona 'returns-triage'" (v1.14.3+).
WITH triaged AS (
SELECT order_id,
AGENT_RUN('returns-triage',
'Process return for order_id ' || CAST(order_id AS TEXT)) AS recommendation
FROM pending_returns
)
SELECT * FROM triaged;
Configuration. AGENT_RUN requires a tool-capable LLM in [query.ai_service]. Recommended in v1.8+: qwen2.5-coder:7b via the embedded local provider (provider = "local" — no external daemon; the gateway pulls the model from registry.ollama.ai on first run). The same model also works via provider = "ollama", or you can point at any OpenAI / Anthropic / Gemini endpoint. The agent's tool surface is limited to the AIDB tools above — generic file/shell/http tools are not exposed for security reasons (v1.6.6.9 hardening).
Returns NULL only when no AI service is wired. Otherwise returns the agent's final TEXT response. See the recipes under Agentic AI Recipes for full working examples.
Bounding a run — AGENT_RUN(persona, task, options) (v1.8.9+)
An optional third argument, a JSON object built with json_object(...), bounds a single call.
| Option | Type | Default | Meaning |
|---|---|---|---|
model |
TEXT | persona / config | Override the completion model for this call (v1.8.10+). Outranks the persona and the [query.ai_service] default; a model that is not installed errors. |
max_iterations |
INT | 5 |
ReAct tool-call cap. Clamped to 1..10. |
timeout_ms |
INT | 600000 |
Wall-clock budget. Clamped to 1000..600000. |
allow_unknown_persona |
BOOL | FALSE |
v1.14.3+. Run on the built-in default persona when persona does not exist, instead of erroring. |
SELECT AGENT_RUN('aidb-assistant', 'Investigate the slowest query',
json_object('max_iterations', 3, 'timeout_ms', 60000)) AS reply;
Out-of-range numbers are clamped, not rejected; unknown option keys are an error. One key is deliberately rejected: allow_writes — the agent's tools are read-only from SQL, since a per-query flag would let any caller escalate a read-only agent; write capability is declared by an operator on a durable agent in v1.9.
The persona must exist — breaking change in v1.14.3.
AGENT_RUN('does-not-exist', …)now fails withAGENT_RUN: unknown persona '<name>'. Available: …, the same ruleCREATE AGENThas enforced since v1.14.2. Earlier builds logged a warning and silently ran the task on the built-indefaultpersona — which is conversational, so an unattended agent answered in prose instead of calling its tools and returned plausible-looking wrong output. Create the persona withCREATE PERSONA, name a built-in, or passjson_object('allow_unknown_persona', true)to opt back into the old fallback deliberately.
Agent memory (v1.8.5+)
Four SQL primitives give any agent durable, semantically-searchable memory without a second service. The first call to a namespace auto-creates its backing table _memory_<namespace> — (id, content, embedding VECTOR(384), metadata, created_at, accessed_at). Namespaces must match ^[A-Za-z_][A-Za-z0-9_]*$; everything is per-tenant scoped.
MEMORY_STORE(namespace, content [, metadata [, options]]) — v1.8.5+
Embeds content and inserts it; returns a sortable memory id (TEXT).
SELECT MEMORY_STORE('default', 'I prefer Python over Java') AS memory_id;
This is an unconditional insert — storing the same content twice makes two rows. Since v1.8.9, pass json_object('dedup', true) as a fourth argument to route through MEMORY_UPSERT's noop_if_equal policy, returning the existing row's id instead of duplicating.
MEMORY_RECALL(namespace, query [, top_k]) — v1.8.5+, table-valued
Semantic retrieval in a FROM clause. Returns (id, content, similarity, metadata, created_at), top top_k (default 10, cap 100) ordered by similarity DESC.
SELECT id, content, similarity FROM MEMORY_RECALL('default', 'what languages do I like', 5);
MEMORY_UPSERT(namespace, content [, options | natural_key]) — v1.8.9+
Idempotent, policy-driven write. Finds the row this content should supersede, applies a conflict-resolution policy, and returns the action taken — 'ADD' | 'UPDATE' | 'DELETE' | 'NOOP'. With no key, matching is by embedding similarity (semantic keying, default threshold 0.95).
| Policy | Behavior |
|---|---|
replace |
Overwrite. ADD when absent, UPDATE when present. |
replace_higher_confidence |
Write only if the new confidence ≥ the stored one; else NOOP. A guess never clobbers a fact. |
merge_max_confidence |
Always write, keeping MAX(new, stored) confidence. |
append_history |
Never overwrite — insert a superseding row with _version = prev + 1. |
noop_if_equal |
NOOP when the content is byte-identical; otherwise replace. |
-- Revise a belief, keeping the higher-confidence one
SELECT MEMORY_UPSERT('default', 'User is pescatarian',
json_object('key', 'dietary',
'policy', 'replace_higher_confidence',
'confidence', 0.95)) AS action; -- 'UPDATE'
-- Retract a fact the agent no longer holds
SELECT MEMORY_UPSERT('default', 'User is vegetarian',
json_object('key', 'dietary', 'deleted', true)) AS action; -- 'DELETE'
Retraction via deleted: true or confidence: 0; retracting a belief never held is a NOOP, not an error. Confidence/key/version live under reserved metadata keys, so v1.8.5 namespaces keep working with no migration. Every non-NOOP change is recorded in _system_agent_memory_audit.
MEMORY_FORGET(namespace, id) — v1.8.5+
Hard-deletes one memory by id; returns BOOLEAN.
SELECT MEMORY_FORGET('default', 'mem_abc123') AS deleted;