Views and SQL sources

Use SQL views and query-backed labels to shape a graph over existing relational data.

Map a physical view

A view can select, rename, or derive the columns that form a graph. Create the view in the same DuckDB session used by your mapped engine:

CREATE VIEW adult_users AS
SELECT id, name, age FROM users WHERE age >= 18;

Register adult_users with its Arrow schema, then map it like a table:

mapping.map_node(
    NodeMapping::table("Adult", "adult_users", "id")
        .property("name", "name")
        .property("age", "age")
);

The SQL generated by OrchidDB references the view. DuckDB evaluates the view definition against its underlying data.

Define a query-backed label

For a label defined directly by SQL, use NodeMapping::query:

mapping.map_node(
    NodeMapping::query(
        "Adult",
        "SELECT id, name, age FROM users WHERE age >= 18",
        "id",
    ).property("name", "name").property("age", "age")
);

Register the schemas of the tables referenced by the SQL. Query-backed definitions are incorporated into the generated relational query.

MATCH (p:Adult)
WHERE p.age > 30
RETURN p.name, p.age
ORDER BY p.age DESC

Define relationships with SQL

EdgeMapping::query takes the same endpoint information as a table mapping. A SQL expression can filter or reshape an existing relation before exposing it to graph queries:

mapping.map_edge(EdgeMapping::query(
    "LARGE_ORDER",
    "SELECT order_id, user_id, total FROM orders WHERE total > 100.0",
    "user_id", "order_id", "Person", "Order",
).with_id("order_id").property("total", "total"));

Work with attached data

Use the DuckDB connection to attach your data sources and create stable local views over them. Register the view schemas with OrchidDB and point mappings at those views. This keeps source connection details in the database setup and graph vocabulary in the mapping.

For example, after your session exposes a source as warehouse.customers, create a local view:

CREATE VIEW graph_customers AS
SELECT customer_id AS id, full_name AS name
FROM warehouse.customers;

Map graph_customers to Person using id and name. The same mapping can then be used by all graph languages.

Inspect the generated query

Use the SQL API to inspect generated SQL and logical plans without execution. Existing callers of the MappedGraphEngine compatibility facade can also call explain_cypher to inspect how graph properties, filters, and relationships refer to the source SQL:

let sql = graph.explain_cypher(
    "MATCH (p:Person) WHERE p.age > 30 RETURN p.name"
).await?;
println!("{sql}");