Skip to Content
We are live but in Staging 🎉
RecipesConverging Engines

Converging Engines

The flagship K3 shape: one table is simultaneously a SQL table, a vector search target, and a graph node set — no copies, no sync jobs. This recipe builds a small citation network and queries it through every door:

  1. SQL — pgvector KNN operators (<-> <#> <=>) with ordinary predicates,
  2. Graph — CREATE GRAPH over the same tables, then k-hop / shortest path / cypher(),
  3. KNN → graph convergence — similar-first, then walk who-cites-whom,
  4. GraphQL — all three pillars in one query.

Every statement here mirrors a shape from the engine’s own test suites.

Setup — one dataset, three roles

Any SQL door works. Pick a client and open a session — everything below is plain SQL from there:

# sslmode=require — the pg wire terminates TLS. Never `prefer`: it falls back
# to plaintext silently, and your password here is your service-account secret.
psql "postgresql://$DODIL_USER:$DODIL_TOKEN@pg.uk-lon-1.dodil.io:5432/kb-platform?sslmode=require"

dodil data connect prints a ready postgresql://…?sslmode=require URL — keep require as the floor (prefer would fall back to plaintext without telling you, and your password on this wire is your service-account secret), or move up to verify-full for production. One caveat: CLI releases before the bolt+s:// change still print the Bolt door as neo4j+s://, and that scheme does not work — routing is refused. Use bolt+s://bolt.uk-lon-1.dodil.io:7687 (see §2). Full endpoint and credential detail is in Connect.

-- articles: relational columns + a pgvector column, and the graph's node table
CREATE TABLE articles (id INT PRIMARY KEY, title VARCHAR, emb vector(4));
 
-- cites: an ordinary edge table (synthetic PK; the graph reads only src/dst)
CREATE TABLE cites (edge_id INT PRIMARY KEY, src INT, dst INT);
 
INSERT INTO articles VALUES
  (1, 'vector databases', '[0.9,0.1,0.0,0.0]'),
  (2, 'graph algorithms', '[0.1,0.9,0.0,0.0]'),
  (3, 'delta lake',       '[0.0,0.1,0.9,0.0]'),
  (4, 'sql planners',     '[0.0,0.0,0.1,0.9]'),
  (5, 'htap systems',     '[0.5,0.5,0.0,0.0]');
 
INSERT INTO cites VALUES (0,1,2), (1,1,5), (2,2,3), (3,5,4);
 
-- declare the citation graph over the SAME rows
CREATE GRAPH cite_graph NODES (articles KEY id) EDGES (cites SRC src DST dst);

1 — KNN in plain SQL

The Postgres wire accepts the three pgvector operators anywhere an expression goes (they lower to exact distance functions in the engine):

opdistancelowers to
<->Euclidean (L2)list_distance
<#>negative inner productlist_negative_inner_product
<=>cosinelist_cosine_distance
-- nearest neighbours…
SELECT id, title FROM articles
ORDER BY emb <-> '[0.9,0.1,0.0,0.0]' LIMIT 3;
 
-- …and KNN + relational predicate in ONE statement
SELECT id, title FROM articles
WHERE title LIKE '%database%'
ORDER BY emb <=> '[0.9,0.1,0.0,0.0]' LIMIT 3;

Works from any Postgres client — including ORMs: SQLAlchemy’s order_by(Article.emb.cosine_distance(query_vec)) produces exactly this shape. A '[…]'::vector cast literal is accepted too.

2 — Graph traversal over the same rows

The traversal family is a set of SQL table functions. Each takes the graph name first, then an optional '<direction>' (out / in / both) and an optional '<freshness>' (eventual / strong):

-- who does article 1 cite, out to 2 hops?  → columns (node, hop_distance)
SELECT * FROM graph_khop('cite_graph', 1, 2);
--  node | hop_distance     → 2@1, 5@1, 3@2, 4@2
 
-- the same, in Cypher — cypher('<graph>', '<body>' [, '<freshness>'])
SELECT * FROM cypher('cite_graph',
  'MATCH (a)-[:cites*1..2]->(b) WHERE id(a) = 1 RETURN b');
 
-- one hop only                      → column (neighbor)
SELECT * FROM graph_neighbors('cite_graph', 1);
 
-- citation chain between two articles → columns (step, node)
SELECT * FROM graph_shortest_path('cite_graph', 1, 4);

The same Cypher runs over the Bolt wire with a stock Neo4j driver. The scheme is bolt+s:// — TLS on connect, verified against public roots with nothing to configure. Both neo4j+s:// and neo4j:// fail, for the same non-TLS reason: they ask for the routing protocol, and the adapter refuses the driver’s ROUTE message outright. Plain bolt:// connects unencrypted and should stay in local development, since the password on this wire is your service-account secret:

cypher-shell -a bolt+s://bolt.uk-lon-1.dodil.io:7687 \
  -u "$DODIL_USER" -p "$DODIL_TOKEN" -d kb-platform \
  "MATCH (a)-[:cites*1..2]->(b) WHERE id(a) = 1 RETURN b"

Cypher here is a whitelisted subset, not a full engine. Three shapes map to the native traversals — variable-length expand (→ graph_khop), single hop (→ graph_neighbors), and MATCH p = shortestPath((a)-[*]-(b)) (→ graph_shortest_path). The anchor must be WHERE id(a) = K on the left node. Everything else — writes, WITH/UNWIND/CALL, property filters, aggregations, ORDER/LIMIT, parameters — is a typed invalid_argument prefixed cypher subset:, never a silently wrong answer. Node and edge labels parse but are a no-op filter: a v1 graph has exactly one node table and one edge table.

3 — Convergence: similar first, then walk the graph

“Find articles like this one, then see what the best match cites.” Traversal functions are whole-statement calls (they can’t be JOINed inside one SELECT yet — see Composing traversals with SQL), so the SQL-only pattern is a short pipeline — KNN → traverse → rehydrate:

-- step 1: KNN candidates
SELECT id, title FROM articles
ORDER BY emb <=> '[0.9,0.1,0.0,0.0]' LIMIT 1;
-- → id 1 ('vector databases')
 
-- step 2: walk who-cites-whom from the top hit
SELECT * FROM graph_khop('cite_graph', 1, 2);
-- → nodes 2, 5 (hop 1), 3, 4 (hop 2)
 
-- step 3: rehydrate properties for the reached nodes
SELECT id, title FROM articles WHERE id IN (2, 5, 3, 4);

The inverse composition — graph-filtered KNN (“recommend things similar to X, but only within 2 citation hops of what I already read”) — inverts steps: traverse first, then feed the reached keys into the KNN statement’s predicate:

SELECT id, title FROM articles
WHERE id IN (2, 5, 3, 4)          -- the k-hop frontier from step 2
ORDER BY emb <=> '[0.1,0.9,0.0,0.0]' LIMIT 2;

KNN + WHERE predicate in one statement is a verified engine shape — the predicate and the KNN ordering apply together.

4 — One GraphQL query, three pillars

The GraphQL adapter (POST https://gql.uk-lon-1.dodil.io/graphql, bucket via the X-DB-Id header) generates a per-bucket schema from the live catalog:

GeneratedShape
every table{table}(where limit orderBy desc): [{table}!]! — where only when the table has at least one scalar column
every tableinsert_{table}(objects: [{table}Insert!]!): Int! (returns the affected row count)
every vector column{table}_similar(vector: [Float!]!, topK: Int = 10, metric: Metric = cosine): [{table}WithScore!]!, where {table}WithScore adds _score: Float!
every graph{graph}_khop(startId: Int!, depth: Int = 1): [KhopRow!]! · {graph}_neighbors(startId: Int!): [NeighborRow!]! · {graph}_shortestPath(fromId: Int!, toId: Int!): [PathStep!]!

And node-table types carry a nested field named after the edge table — cites(depth: Int = 1): [articles!]! on both articles and articlesWithScore. That is the hook that converges all three pillars in one query:

{
  articles_similar(vector: [0.9, 0.1, 0.0, 0.0], topK: 3) {
    id            # relational
    title
    _score        # vector: similarity of this row
    cites {       # graph: who this article cites, properties rehydrated
      id
      title
    }
  }
}
curl -s https://gql.uk-lon-1.dodil.io/graphql \
  -H "X-DB-Id: kb-platform" -H "Content-Type: application/json" \
  -d '{"query":"{ articles_similar(vector: [0.9,0.1,0.0,0.0], topK: 3) { id title _score cites { id title } } }"}'

Under the hood this is the same three-step pipeline from section 3 — one vector search, one k-hop traversal per parent (batched through a dataloader), one SELECT … WHERE key IN (…) rehydrate for the whole batch — executed server-side. Graph root fields work standalone too:

{ cite_graph_khop(startId: 1, depth: 2) { node hopDistance } }

The four honest limits on this surface:

  • KhopRow, NeighborRow and PathStep are bare integer ids, not rows — { node hopDistance }, { neighbor } and { step node } respectively. Only the nested edge field rehydrates full node rows.
  • _score exists only at the top level. The nested cites { … } returns the plain articles type, so you cannot ask for a score inside it. Nesting is otherwise recursive — cites { cites { … } } type-checks.
  • _similar takes no filter or where argument, and it targets the table’s first vector column implicitly. It also needs a primary key to rehydrate rows.
  • The nested fan-out is capped at 50 distinct start ids per traversal batch, and going over is a loud error in the errors array: nested traversal over 51 parent rows exceeds the cap of 50 — narrow the parent list (limit/where).

Analytics (graph_pagerank, graph_components, graph_bfs) are SQL-only — they have no GraphQL root field.

Consistency across the doors

All four surfaces read the same bytes: the vector the ORM wrote over pg-wire is the one GraphQL _similar scores; the edge you INSERT INTO cites is the one Bolt traverses. Two freshness notes:

  • Graph traversals default to the pinned artifact — pass 'strong' for read-your-writes over just-inserted edges (details).
  • The GraphQL schema itself refreshes from the catalog within ~10 s of DDL (new table / graph → new fields). It is a lazy per-bucket TTL, not an invalidation — worst case is a full 10 s, and the rebuild cost lands on the first request after expiry.

See also

OverviewRAG Knowledge Base