# DuckDB MCP Native [![npm version](https://badge.fury.io/js/@seed-ship%2Fduckdb-mcp-native.svg)](https://www.npmjs.com/package/@seed-ship/duckdb-mcp-native) [![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT) Native TypeScript implementation of DuckDB MCP (Model Context Protocol) server with federation, graph algorithms, and human-in-the-loop security. **v1.6.0** — DuckDB 1.5.4 + DuckPGQ `f386a6cf` + optional Onager graph analytics — 514 tests, 0 failures ## Features - **32+ MCP Tools**: SQL queries, schema inspection, CSV/Parquet loading, federation, graph algorithms, process mining, data helpers - **8 Graph Algorithm Tools**: PageRank, eigenvector, community detection, modularity, weighted paths, temporal analysis, period comparison, multi-format export - **HITL Security**: Production mode asks user confirmation before destructive SQL via MCP elicitation API - **Federation**: Distributed queries across multiple MCP servers with `mcp://` URIs - **Virtual Filesystem**: Direct SQL access via `mcp://` URIs with auto-format detection - **Transports**: stdio, WebSocket, TCP (HTTP client-side) - **Process Mining**: 3 tools for workflow analysis from Parquet files - **DuckLake-style Snapshots**: built-in lightweight snapshots + time travel on Parquet files (self-contained emulation; the official `ducklake` extension is separately installable on DuckDB ≥ 1.5.2) - **DuckPGQ**: SQL:2023 property graph queries + native CSR algorithms (PageRank, WCC, clustering) - **Onager (opt-in)**: ~65 native graph analytics table functions (betweenness, louvain, dijkstra, link prediction, …) via `ENABLE_ONAGER=true` — alpha extension - **Geospatial Graphs**: GEOMETRY + CRS vertex tables work with DuckPGQ graph algorithms - **MCP SDK 1.26.0**: Pinned, with elicitation API and connect() guard ## Installation ### As MCP Server (Claude Desktop / Claude Code) ```json { "mcpServers": { "duckdb": { "command": "npx", "args": ["@seed-ship/duckdb-mcp-native"], "env": { "DUCKDB_MEMORY": "4GB", "DUCKDB_THREADS": "4", "MCP_SECURITY_MODE": "development" } } } } ``` With S3/MinIO: ```json { "mcpServers": { "duckdb": { "command": "npx", "args": ["@seed-ship/duckdb-mcp-native"], "env": { "DUCKDB_MEMORY": "4GB", "DUCKDB_THREADS": "4", "MCP_SECURITY_MODE": "development", "MINIO_PUBLIC_ENDPOINT": "https://s3.example.com", "MINIO_ACCESS_KEY": "your-access-key", "MINIO_SECRET_KEY": "your-secret-key", "MINIO_REGION": "us-east-1", "MINIO_USE_SSL": "true" } } } } ``` Config file locations: - **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json` - **Windows**: `%APPDATA%\Claude\claude_desktop_config.json` - **Linux**: `~/.config/Claude/claude_desktop_config.json` ### As NPM Package ```bash npm install @seed-ship/duckdb-mcp-native ``` ### For Development ```bash git clone https://github.com/theseedship/duckdb_mcp_node cd duckdb_mcp_node npm install npm run dev:server # Start MCP server npm run inspector # Test with Inspector UI npm test # Run tests ``` ## MCP Tools ### Database Operations | Tool | Description | | ---------------- | --------------------------------------- | | `query_duckdb` | Execute SQL queries with optional LIMIT | | `list_tables` | List tables in a schema | | `describe_table` | Get table structure (columns, types) | | `load_csv` | Load CSV files into DuckDB | | `load_parquet` | Load Parquet files into DuckDB | ### Federation | Tool | Description | | ----------------------- | ------------------------------ | | `attach_mcp` | Connect to external MCP server | | `detach_mcp` | Disconnect server | | `list_attached_servers` | Show connections | | `list_mcp_resources` | List remote resources | | `create_virtual_table` | Create table from MCP resource | | `drop_virtual_table` | Remove virtual table | | `list_virtual_tables` | Show virtual tables | | `refresh_virtual_table` | Update table data | | `query_hybrid` | Query across local/remote data | ### DuckLake-style Snapshots (ACID + Time Travel) | Tool | Description | | ---------------------- | ------------------------------------------ | | `ducklake.attach` | Attach or create snapshot catalog | | `ducklake.snapshots` | List, view, clone, or rollback snapshots | | `ducklake.time_travel` | Query historical data at any point in time | > These tools are a **self-contained emulation** (catalog tables + Parquet on S3/MinIO) — they do not require, or use, the official `ducklake` extension. If you need true multi-reader/multi-writer lakehouse semantics, install the official [`ducklake` extension](https://ducklake.select) (DuckDB ≥ 1.5.2, PostgreSQL catalog recommended for concurrency) alongside this package. ### MotherDuck Cloud > MotherDuck supports DuckDB clients 1.4.x through **1.5.4** (as of July 2026) — this package runs DuckDB 1.5.4, which is inside the supported window. Check [MotherDuck's version lifecycle](https://motherduck.com/docs/troubleshooting/version-lifecycle-schedules/) for current ranges per region. | Tool | Description | | ---------------------------- | --------------------------- | | `motherduck.attach` | Connect to MotherDuck cloud | | `motherduck.detach` | Disconnect | | `motherduck.status` | Check connection and usage | | `motherduck.list_databases` | List cloud databases | | `motherduck.create_database` | Create cloud database | | `motherduck.query` | Execute cloud queries | | `motherduck.share_table` | Share local table to cloud | | `motherduck.import_table` | Import cloud table locally | --- ## Graph Algorithm Tools 8 MCP tools for graph analysis using iterative SQL with temp tables (no recursive CTEs — DuckPGQ workaround). ### Tools | Tool | Description | | ------------------------ | -------------------------------------------------------------- | | `graph.pagerank` | PageRank centrality with configurable damping/iterations | | `graph.eigenvector` | Eigenvector centrality via power iteration | | `graph.community_detect` | Label propagation community detection | | `graph.modularity` | Modularity score Q for community quality | | `graph.weighted_path` | Weighted paths: strongest, cheapest, or combined | | `graph.temporal_filter` | Filter graph by time period, return stats | | `graph.compare_periods` | Compare two periods (NEW/REMOVED/STRENGTHENED/WEAKENED/STABLE) | | `graph.export` | Export as JSON, CSV (Gephi), D3, GraphML, or Parquet | ### Usage All tools share a common base schema: ```typescript import { graphToolHandlers } from '@seed-ship/duckdb-mcp-native/graph' const result = await graphToolHandlers['graph.pagerank']( { node_table: 'vars', edge_table: 'drives', node_id_column: 'var_id', source_column: 'from_var', target_column: 'to_var', weight_column: 'confidence', iterations: 20, damping: 0.85, top_n: 10, }, duckdb ) // result.nodes = [{ node_id: 4, rank: 0.21 }, ...] ``` ### Temporal Analysis ```typescript // Compare graph evolution across time periods const changes = await graphToolHandlers['graph.compare_periods']( { node_table: 'vars', edge_table: 'drives', source_column: 'from_var', target_column: 'to_var', weight_column: 'confidence', period_column: 'period', period_a: '1993-2023', period_b: '2020-2023', }, duckdb ) // changes.summary = { new_edges: 3, removed_edges: 5, strengthened: 2, ... } ``` --- ## DuckPGQ Property Graph Support **SQL:2023 Property Graph queries — with automatic DuckPGQ installation from community repository.** ### Configuration ```bash # Required for DuckPGQ ENABLE_DUCKPGQ=true ALLOW_UNSIGNED_EXTENSIONS=true DUCKPGQ_SOURCE=community # Default, no custom URL needed ``` ### Compatibility Matrix | DuckDB Version | DuckPGQ Version | Fixed Paths | Bounded {n,m} | ANY SHORTEST | Kleene (alone) | Status | | ------------------- | --------------- | ----------- | ------------- | ------------ | -------------- | ----------------------------------------------------------------------------------------------------- | | 1.0.0 - 1.2.2 | Stable | ✅ | ✅ | ✅ | ✅ | **Production Ready** | | 1.4.1 | 7705c5c | ✅ | ✅ | ✅ | ❌ | Functional | | 1.5.0 | aec2e25 | ✅ | ✅ | ✅ | ❌ | Functional | | 1.5.1 – 1.5.3 | — | — | — | — | — | **No working binary** (1.5.1 SIGSEGVs on LOAD; 1.5.2/1.5.3 never built — cwida/duckpgq-extension#305) | | **1.5.4** (current) | **f386a6cf** | **✅** | **✅** | **✅** | **❌** | **Functional** | > **1.5.1–1.5.3 gap**: DuckPGQ had no working community binary for these (LOAD segfault). Fixed at 1.5.4 — that's why this package jumps DuckDB 1.5.0 → 1.5.4 directly. See `docs/duckpgq/CAPABILITY_REPORT_1.5.md`. ### What Works (Validated 2026-07-03 — f386a6cf on DuckDB 1.5.4; same result as aec2e25 on 1.5.0) ✅ **Native CSR Algorithms:** - `pagerank(graph, vertices, edges)` — native PageRank table function - `weakly_connected_component(graph, vertices, edges)` — WCC table function - `local_clustering_coefficient(graph, vertices, edges)` — clustering table function - `summarize_property_graph('graph_name')` — graph statistics - `vertices(p)` / `edges(p)` / `path_length(p)` — path extraction functions ✅ **GEOMETRY + CRS Integration (NEW):** - Property graphs with `GEOMETRY` vertex columns - Spatial functions (`ST_Distance`, `ST_AsText`) in `GRAPH_TABLE COLUMNS` - `GEOMETRY('OGC:CRS84')` CRS-typed vertex columns - PageRank/WCC/clustering on GEOMETRY vertex tables ✅ **Graph Query Features:** - Property graph creation (VERTEX/EDGE TABLES) - Pattern matching with `GRAPH_TABLE` - Fixed-length paths (1-hop, 2-hop, N-hop) - **ANY SHORTEST** path queries with `->*` syntax - **Bounded quantifiers** `{n,m}` with `->{n,m}` syntax - WHERE on edges in 1-hop patterns - CTE wrapping GRAPH_TABLE (no segfault, #276/#294 fixed) - Edge variable required: `[e:Label]` ❌ **Not Yet Available:** - `ALL SHORTEST` — "Not implemented yet" - `CHEAPEST` path matching — not in parser - Standalone Kleene `->*`/`->+` — blocked (safety: infinite results on cycles) - Anonymous edges `[:Label]` — requires variable binding `[e:Label]` - Edge properties in bounded quantifiers `{n,m}` — edge variable not accessible **Full capability report**: [`docs/duckpgq/CAPABILITY_REPORT_1.5.md`](docs/duckpgq/CAPABILITY_REPORT_1.5.md) ### Onager Graph Analytics (companion community extension) [Onager](https://github.com/CogitatorTech/onager) is a separate community extension (CogitatorTech, Rust + C++, `0.1.0-alpha.6` — **alpha**) that loads alongside DuckPGQ on DuckDB ≥ 1.5.4 and ships **65 native graph table functions** — a NetworkX-class toolkit in SQL: | Family (prefix) | Functions | | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Centrality (`ctr_`) | pagerank, personalized_pagerank, betweenness, closeness, eigenvector, katz, harmonic, degree, voterank, laplacian, local_reaching | | Community (`cmm_`) | louvain, label_prop, infomap, girvan_newman, spectral, components | | Paths (`pth_`) | dijkstra, bellman_ford, floyd_warshall | | Link prediction (`lnk_`) | adamic_adar, jaccard, common_neighbors, pref_attach, resource_alloc | | Metrics (`mtr_`) | density, diameter, radius, transitivity, triangles, avg_clustering, avg_path_length, assortativity | | Subgraphs (`sub_`) | ego_graph, induced, k_hop | | Other | MST (kruskal/prim), BFS/DFS traversal, parallel variants (`par_`), approximation (`apx_` max*clique/tsp/vertex_cover), generators (`gen*` erdos_renyi/barabasi_albert/watts_strogatz) | **Auto-loading (v1.6.0)**: set `ENABLE_ONAGER=true` (plus `ALLOW_UNSIGNED_EXTENSIONS=true`) and `DuckDBService` installs + loads Onager at startup — check `service.onagerLoaded` at runtime. Loading is graceful: if the binary is unavailable (offline, unsupported platform), the service continues without it. Or load manually: ```sql INSTALL onager FROM community; LOAD onager; -- Native PageRank straight from an edge table — no property graph needed SELECT * FROM onager_ctr_pagerank((SELECT src, dst FROM edges)) ORDER BY rank DESC LIMIT 10; -- Louvain communities SELECT * FROM onager_cmm_louvain((SELECT src, dst FROM edges)); -- Single-source shortest paths (named parameter required) SELECT * FROM onager_pth_dijkstra((SELECT src, dst FROM edges), source = 1); ``` Validated on DuckDB 1.5.4 (2026-07-03): pagerank, louvain, betweenness, dijkstra, adamic_adar, density all work. **Caveats** (alpha): - Node ids **must be BIGINT** (`column::BIGINT` if needed) - Some functions bind only via **named parameters** (`source = 1`, not positional) - Not built for `osx_amd64`, `windows_amd64_mingw`, or WASM - API may change between alpha releases — evaluate before depending on it Our 8 `graph.*` MCP tools remain iterative-SQL (portable, no extension dependency); Onager is a candidate native backend for them and adds net-new capabilities (betweenness, louvain, link prediction, ego/k-hop subgraphs). ### Example Queries ```sql -- Create property graph from existing tables CREATE PROPERTY GRAPH social_network VERTEX TABLES (Person) EDGE TABLES ( Knows SOURCE KEY (from_id) REFERENCES Person (id) DESTINATION KEY (to_id) REFERENCES Person (id) ); -- Direct connections (1-hop) - edge variable required FROM GRAPH_TABLE (social_network MATCH (p1:Person)-[k:Knows]->(p2:Person) COLUMNS (p1.name AS person, p2.name AS friend) ); -- Friends of friends (fixed 2-hop) FROM GRAPH_TABLE (social_network MATCH (p1:Person)-[k1:Knows]->(p2:Person)-[k2:Knows]->(p3:Person) WHERE p1.id != p3.id COLUMNS (p1.name AS person, p3.name AS friend_of_friend) ); -- ANY SHORTEST path query FROM GRAPH_TABLE (social_network MATCH p = ANY SHORTEST (start:Person WHERE start.id = 1)-[k:Knows]->*(end:Person WHERE end.id = 10) COLUMNS (start.name AS from_person, end.name AS to_person, path_length(p) AS hops) ); -- Bounded quantifiers (1 to 3 hops) FROM GRAPH_TABLE (social_network MATCH (p1:Person)-[k:Knows]->{1,3}(p2:Person) COLUMNS (p1.name AS person, p2.name AS connection) ); ``` **1.5.0 capabilities**: [`docs/duckpgq/CAPABILITY_REPORT_1.5.md`](docs/duckpgq/CAPABILITY_REPORT_1.5.md) | **Migration**: [`docs/duckpgq/MIGRATE_DUCKDB_1.5.md`](docs/duckpgq/MIGRATE_DUCKDB_1.5.md) --- ## Process Mining Tools Three specialized tools for analyzing workflow processes stored in Parquet files. | Tool | Description | | ------------------ | ------------------------------------------------------------- | | `process.describe` | List and filter process summaries with confidence scores | | `process.similar` | Find similar processes using vector embeddings (FLOAT[384]) | | `process.compose` | Merge multiple processes into unified workflow with QA checks | ### Embeddings & Similarity Search - Validates embedding dimensions (configurable via `PROCESS_EMBEDDING_DIM`, defaults to 384) - Automatic fallback to TypeScript L2 distance when DuckDB VSS is unavailable - Results include `distance_source` field (`duckdb_vss` or `typescript_l2`) for observability ```typescript const results = await handlers['process.similar']({ signature_emb: [0.1, 0.2, ...], // 384-dimensional embedding k: 5, parquet_url: 's3://bucket/signatures.parquet', }) // { matches: [{ doc_id: 'doc1', distance: 0.45, distance_source: 'duckdb_vss' }] } ``` ### Process Composition - Step normalization (lowercase + trim, handles "Login" vs "login") - Conflict resolution via median order when multiple processes share a step - Automatic edge remapping after step deduplication - QA checks: detects orphan steps, cycles, and duplicate edges ```typescript const composed = await handlers['process.compose']({ doc_ids: ['doc1', 'doc2', 'doc3'], steps_url: 's3://bucket/steps.parquet', edges_url: 's3://bucket/edges.parquet', }) // { // success: true, // steps: [...], // Deduplicated and normalized // edges: [...], // Remapped edges // merged_count: 5, // qa: { orphan_steps: [], cycles: [], duplicate_edges: [], warnings: [] } // } ``` ### Configuration ```bash PROCESS_SUMMARY_URL=s3://bucket/process_summary.parquet PROCESS_STEPS_URL=s3://bucket/process_steps.parquet PROCESS_EDGES_URL=s3://bucket/process_edges.parquet PROCESS_SIGNATURE_URL=s3://bucket/process_signatures.parquet PROCESS_EMBEDDING_DIM=384 # Match your embedding model (e.g., 1024 for text-embedding-3-large) ``` --- ## Three Usage Modes ### Mode 1: Standalone Server ```bash DUCKDB_MEMORY=4GB DUCKDB_THREADS=4 npm run dev:server # Or: npx @seed-ship/duckdb-mcp-native ``` ### Mode 2: Library Mode Import tool handlers directly into your existing MCP server: ```typescript import { nativeToolHandlers, nativeToolDefinitions } from '@seed-ship/duckdb-mcp-native/lib' server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: [...yourTools, ...nativeToolDefinitions], })) server.setRequestHandler(CallToolRequestSchema, async (request) => { const { name, arguments: args } = request.params if (name in nativeToolHandlers) { return await nativeToolHandlers[name](args) } }) ``` ### Mode 3: Embedded Server ```typescript import { DuckDBMCPServer } from '@seed-ship/duckdb-mcp-native/server' const server = new DuckDBMCPServer({ embeddedMode: true, duckdbService: yourDuckDBInstance, // Optional }) await server.start() const handlers = server.getNativeHandlers() ``` --- ## Virtual Filesystem Query MCP resources directly in SQL with zero configuration: ```sql -- Direct access with mcp:// URIs SELECT * FROM 'mcp://weather-server/forecast.csv'; -- Join across servers SELECT g.title, j.priority FROM 'mcp://github/issues.json' g JOIN 'mcp://jira/tickets.json' j ON g.id = j.github_id; -- Glob patterns SELECT COUNT(*) FROM 'mcp://*/logs/2024-*.csv' WHERE level = 'ERROR'; ``` Features: auto-format detection (CSV, JSON, Parquet, Arrow, Excel), intelligent caching, glob patterns. --- ## Federation ```typescript // Attach servers await handlers['attach_mcp']({ connectionString: 'stdio://github-mcp-server', alias: 'github', }) // Federated query const result = await handlers['federate_query']({ sql: ` SELECT g.title, s.message FROM 'mcp://github/issues.json' g JOIN 'mcp://slack/messages.json' s ON g.id = s.issue_id `, }) ``` --- ## DuckLake (ACID + Time Travel) ```typescript // Attach catalog await handlers['ducklake.attach']({ catalogName: 'analytics', catalogLocation: 's3://data-lake/analytics', format: 'DELTA', enableTimeTravel: true, }) // Time travel const historical = await handlers['ducklake.time_travel']({ catalogName: 'analytics', tableName: 'sales', query: 'SELECT SUM(revenue) FROM sales', timestamp: '2025-01-20T00:00:00Z', }) // Snapshot management await handlers['ducklake.snapshots']({ catalogName: 'analytics', tableName: 'sales', action: 'rollback', version: 41, }) ``` --- ## Security ### Modes - **development** (default): All queries allowed - **production**: Destructive SQL triggers HITL elicitation Set via `MCP_SECURITY_MODE=production` ### HITL Elicitation In production mode, destructive operations (DROP, DELETE, ALTER, TRUNCATE, INSERT, UPDATE, GRANT, REVOKE) trigger a confirmation prompt via the MCP SDK's elicitation API: | Scenario | Behavior | | ------------------------------------------ | ---------------------------- | | Client supports elicitation, user confirms | Query executes | | Client supports elicitation, user declines | Query blocked | | Client does not support elicitation | Query blocked (safe default) | | Elicitation times out or errors | Query blocked | Configure timeout: `MCP_ELICIT_TIMEOUT=30000` (ms, default 30s) --- ## Architecture ``` src/ duckdb/ # DuckDB service with pooling server/ # MCP server (32+ tools, HITL security) client/ # MCP client for federation federation/ # ResourceRegistry, ConnectionPool, QueryRouter filesystem/ # Virtual Filesystem (mcp:// URIs) protocol/ # Transport implementations (stdio, WS, TCP, HTTP) tools/ # Graph, process, data helper, DuckLake, MotherDuck tools context/ # Multi-tenant space isolation monitoring/ # Performance metrics ``` ## Configuration | Variable | Default | Description | | --------------------------- | ---------------- | -------------------------------------------------------------------------------- | | `DUCKDB_MEMORY` | `4GB` | DuckDB memory limit | | `DUCKDB_THREADS` | `4` | DuckDB thread count | | `MCP_SECURITY_MODE` | `development` | `development` / `production` | | `MCP_ELICIT_TIMEOUT` | `30000` | HITL elicitation timeout (ms) | | `MCP_MAX_QUERY_SIZE` | `1000000` | Max SQL query size (chars) | | `MCP_CACHE_DIR` | `/tmp/mcp-cache` | VFS cache directory | | `MCP_CACHE_TTL` | `300000` | VFS cache TTL (ms) | | `ENABLE_DUCKPGQ` | `false` | Enable DuckPGQ extension | | `ENABLE_ONAGER` | `false` | Enable Onager graph analytics (opt-in, alpha; needs `ALLOW_UNSIGNED_EXTENSIONS`) | | `ONAGER_STRICT_MODE` | `false` | Throw instead of warn if Onager fails to load | | `ALLOW_UNSIGNED_EXTENSIONS` | `false` | Required for DuckPGQ / Onager | | `PROCESS_EMBEDDING_DIM` | `384` | Embedding dimension for process mining | ## Scripts ```bash npm run dev:server # Start MCP server npm run inspector # MCP Inspector UI npm test # Run tests npm run test:watch # TDD mode npm run check:all # Typecheck + lint + format + tests npm run lint:fix # Auto-fix lint issues npm run format # Format code npm run port:clean # Fix port issues npm run inspector:reset # Reset stuck Inspector ``` ## Documentation | Document | Description | | ------------------------------------------------------ | ------------------------------------- | | [`docs/CHANGELOG.md`](docs/CHANGELOG.md) | Detailed changelog | | [`docs/DEVELOPMENT.md`](docs/DEVELOPMENT.md) | Development setup guide | | [`docs/ARCHITECTURE.md`](docs/ARCHITECTURE.md) | System architecture | | [`docs/TRANSPORTS.md`](docs/TRANSPORTS.md) | Transport protocols + HITL flow | | [`docs/FEDERATION_GUIDE.md`](docs/FEDERATION_GUIDE.md) | Federation documentation | | [`docs/duckpgq/`](docs/duckpgq/) | DuckPGQ findings and failure analysis | | [`docs/roadmap/`](docs/roadmap/) | Roadmap and planning | ## Requirements - Node.js 20+ - TypeScript 5+ ## License MIT