SQL queries

SQL queries use ShrikeDB’s shared query planner and execution operators. The interface supports queries and graph-node mutations through the ExecuteQuery gRPC method. Supply the query in its sql field and select the database using the same authentication and database metadata as other gRPC queries.

{"sql":"SELECT UPPER('hello') AS greeting FROM dual"}

DUAL supplies one row. TRIPLES exposes the selected database’s graph as SUBJECT, PREDICATE, and OBJECT. Subjects and entity objects retain their ShrikeDB identities, allowing joins with vector IDs and geospatial entities.

Write graph data

GRAPH.<label> maps a graph label to SQL rows. ID is the generated node identity; other named columns are node properties. Double-quote labels and properties to preserve their case when sharing data with Cypher. Use explicit columns rather than SELECT * for these schemaless graph tables.

INSERT INTO GRAPH."Person" ("name", "age")
VALUES ('Ada', 36), ('Grace', 40);

SELECT ID, "name", "age" FROM GRAPH."Person" ORDER BY "name";

UPDATE GRAPH."Person" SET "age" = "age" + 1 WHERE "name" = 'Ada';

DELETE FROM GRAPH."Person" WHERE "name" = 'Grace';

Submit each statement as a separate request. Each mutation returns ROWS_AFFECTED. INSERT …​ SELECT can copy query results into node properties. Updates evaluate assignment expressions against the original row, and setting a property to null removes it. Deleting a node with relationships fails; remove its relationships through the graph API first. ID cannot be assigned.

The shared graph-write admission setting, SHRIKEDB_CYPHER_WRITE_MODE=canary|full, must be enabled. SQL mutations require write permission and use the selected database’s graph-write service and schema constraints. All row expressions and constraint checks run before batch submission. SQL does not yet provide multi-statement transactions or a separate crash-atomic commit protocol. Snapshot, limit, and offset overrides are rejected for mutations.

Run SQL mutations on a standalone deployment. Cluster mode rejects SQL language writes with UNIMPLEMENTED; enabling the write setting does not change this.

SQL mutations currently target graph nodes. Vector, geospatial, and KV writes continue to use their existing APIs. MERGE, relational DDL, RETURNING, and joined mutation targets remain outside the SQL write subset.

Compose searches

Use TABLE(VECTOR_SEARCH(index, vector_json, k)) to retrieve ID and SCORE. The named index determines the metric and vector search implementation. The JSON vector must contain finite numbers and match the index dimension.

SELECT v.id, v.score
FROM TABLE(VECTOR_SEARCH('embeddings', '[0.1,0.2,0.3]', 10)) v
ORDER BY score

Use TABLE(GEOSPATIAL_SEARCH(index, options_json)) for indexed spatial and time filters. Its options match the geospatial query interface. Times use epoch milliseconds; the time interval includes start_ms and excludes end_ms. Results include EVENT, ENTITY, TIME_MS, LONGITUDE, LATITUDE and query statistics. count_only: true returns a summary row, including zero matches, instead of observation rows. Search limits and cancellation bounds still apply to these table functions.

The following query finds nearby-in-vector-space entities with observations inside a geographic area and time interval, then counts their outgoing graph relationships for each observation. Each relationship/observation pairing contributes one to the count. The vector index and position index must already exist and use the same entity IDs as the graph.

WITH near AS (
  SELECT id
  FROM TABLE(VECTOR_SEARCH('embeddings', '[0.1,0.2,0.3]', 20))
), visits AS (
  SELECT entity
  FROM TABLE(GEOSPATIAL_SEARCH('positions',
    '{"start_ms":1700000000000,"end_ms":1700086400000,
      "shape":{"type":"box","west":-75,"east":-70,"south":35,"north":40}}'))
)
SELECT near.id AS entity, COUNT(*) AS observations_and_links
FROM near
JOIN visits ON near.id = visits.entity
JOIN triples t ON near.id = t.subject
GROUP BY near.id
ORDER BY observations_and_links DESC
FETCH FIRST 10 ROWS ONLY

TABLE(KV_LOOKUP(resource_id, keys_json)) returns KEY and VALUE for each requested unsigned 64-bit key. Missing keys produce null values; repeated keys produce repeated rows. UTF-8 values return strings and other bytes return integer lists. Query access follows the selected database’s read permissions. The resource ID identifies a KV namespace; it is not a schema ID. Obtain the resource ID from your application’s namespace configuration.

SELECT k.key, k.value
FROM TABLE(KV_LOOKUP(42, '[10,20,30]')) k
ORDER BY key

Supported query forms

The current read-query subset includes projections, aliases, WHERE, inner and left joins with ON, cross joins, derived tables, nonrecursive CTEs, UNION and UNION ALL over compatible known column types, DISTINCT, column-based GROUP BY, HAVING, ORDER BY, OFFSET, and FETCH FIRST …​ ROWS ONLY. Order by output column names, expressions over those columns, or column positions. Use explicit output aliases when names would otherwise repeat.

Expressions include arithmetic, comparisons, three-valued Boolean logic, IS NULL, IS NOT NULL, IN, NOT IN, and CASE. Aggregates include COUNT, SUM, AVG, MIN, and MAX, with DISTINCT arguments where applicable. Scalar functions include ABS, CEIL, FLOOR, one-argument ROUND, LOWER, UPPER, LENGTH, and COALESCE.

Unquoted identifiers fold to uppercase; double-quoted identifiers preserve case. Empty SQL string literals become null. Numeric execution uses ShrikeDB’s signed 64-bit integers and binary floating-point values, not Oracle’s complete NUMBER and implicit-conversion rules. Explicit arbitrary-precision decimals, NLS-sensitive conversions and Oracle package functions remain outside this subset.

Unknown relations, unbound parameters, unsupported functions, window functions, recursive CTEs and unsupported clauses return admission errors. SQL does not yet expose GRAPH_TABLE, user-defined relational catalog mappings, or distributed HA routing. Distributed Cypher/SPARQL reads support the shapes listed in HA deployment status and configuration. SQL, Cypher and SPARQL language mutations all require a standalone deployment. SQL is available in builds containing this increment; a previously deployed server must be upgraded before using sql.