# DuckDB

The DuckDB connector (`CONNECTOR_TYPE=duckdb`) translates GQL queries into SQL
and executes them against an embedded DuckDB instance. It requires a
[mapping file](https://memgraph.com/docs/memgraph-zero/memgql/quick-start#mapping-file) that maps graph patterns to
relational tables.

DuckDB runs in-process (no separate server needed), making it ideal for local
development and testing.

## 1. Create and seed a DuckDB database

```bash
docker network create memgql-net

# Create a DuckDB file with seed data using the DuckDB CLI
docker run --rm -v ./data:/data \
    datacatering/duckdb:v1.1.1 \
    -c "
CREATE TABLE persons (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
CREATE TABLE companies (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE knows (from_id INTEGER, to_id INTEGER);
CREATE TABLE works_at (person_id INTEGER, company_id INTEGER);

INSERT INTO persons VALUES (1, 'Alice', 30), (2, 'Bob', 25);
INSERT INTO companies VALUES (1, 'Acme Corp');
INSERT INTO knows VALUES (1, 2);
INSERT INTO works_at VALUES (1, 1);
" /data/graph.duckdb
```

## 2. Start MemGQL

```bash
docker run --rm \
    --name memgql \
    --network memgql-net \
    --stop-timeout 2 \
    -p 7688:7688 \
    --env CONNECTOR_TYPE=duckdb \
    --env DUCKDB_PATH=/data/graph.duckdb \
    --env MAPPING_FILE=/data/mapping.json \
    --env BOLT_LISTEN_ADDR=0.0.0.0:7688 \
    -v ./data:/data \
    -v ./mapping.json:/data/mapping.json \
    memgraph/memgql:latest
```

For an in-memory database (no persistence), omit `DUCKDB_PATH`.

## 3. Connect

```bash
mgconsole --port 7688
```

## 4. Query

```gql
MATCH (p:Person) RETURN p.name, p.age;
```

```gql
MATCH (p:Person)-[:WORKS_AT]->(c:Company) RETURN p.name, c.name;
```

```gql
MATCH (a:Person)-[:KNOWS]->(b:Person) RETURN a.name, b.name;
```

For environment variables, see [Reference](https://memgraph.com/docs/memgraph-zero/memgql/reference#duckdb-duckdb).

## Supported GQL features

| Feature                                       | DuckDB |
|-----------------------------------------------|--------|
| `MATCH` / `WHERE` / `RETURN`                  | ✓      |
| Pattern-level `WHERE` (`MATCH (n WHERE …)`)   | ✓      |
| Multiple `MATCH` clauses in one query         | ✓      |
| `OPTIONAL MATCH`                              | ✓      |
| `WITH` clause (chain query steps)             | ✓      |
| `WITH DISTINCT` / `WITH … ORDER BY … LIMIT N` | ✓      |
| Multiple chained `WITH` steps                 | ✓      |
| Pass a whole node through `WITH n`            | ✓      |
| Typed edge `(a)-[r:R]->(b)`                   | ✓      |
| Untyped edge `()-[]->(b)`                     | ✗      |
| `UNION` / `UNION ALL` / `UNION DISTINCT`      | ✓      |
| Quantified path `(){m,n}` (bounded)           | ✓      |
| Whole-node `RETURN n` / whole-rel `RETURN r`  | ✓      |
| Map projections `RETURN n {.a, .b}`           | ✓      |
| `IN` list membership `WHERE x IN [...]`       | ✓      |
| `STARTS WITH` / `ENDS WITH` / `CONTAINS`      | ✓      |
| `collect()` aggregate                         | ✓      |
| `count`, `sum`, `avg`, `min`, `max`           | ✓      |
| `COUNT(DISTINCT …)`                           | ✓      |
| Arithmetic `+ - * / %`                        | ✓      |
| `CASE WHEN … THEN … ELSE … END`               | ✓      |
| `COALESCE`, `NULLIF`                          | ✓      |
| `INSERT (a {…}) RETURN a.x`                   | ✓      |
| `DELETE`                                      | ✓      |
| `SET` / `REMOVE` (property update / delete)   | ✗      |
| `DETACH DELETE`                               | ✗      |
| `INTERSECT` / `EXCEPT`                        | ✗      |
| Quantified path `(){m,}` (unbounded)          | ✗      |
| Shortest-path (`ALL SHORTEST` / `SHORTEST k`) | ✗      |

## Known limitations

- **Unbounded variable-length paths** (`()-[*]->()`) are rejected. Bound the depth (`{1,5}`) or run the query against a Cypher backend.
- **`FOR x IN [...]` (UNWIND-style)** is rejected with an actionable error. Cypher backends (Memgraph, Neo4j) handle this syntax natively.
- **Path variables on variable-length patterns**: `MATCH p = (a){1,3}(b) RETURN p` is not supported. Drop the `p =` binding (or query a Cypher backend) and `RETURN` the individual nodes / edges instead.