MCP Tools for DuckDB — in-process analytics, file locking, latency-based health probes, MotherDuck cloud

Setup — in-process, in-memory, and persistent modes

DuckDB needs no server, no credentials, and no network configuration for local use. The Node.js package opens a database directly in the process. Choose the mode based on whether the MCP tool needs data to survive server restarts.

import Database from 'duckdb';
import { promisify } from 'util';

// In-memory — data lost on process exit, fastest for transient analysis
const memDb = new Database(':memory:');

// Persistent file — data survives restarts, exclusive write lock
const fileDb = new Database('/data/analytics.db');

// Read-only — allows multiple processes to share the same file simultaneously
const readOnlyDb = new Database('/data/analytics.db', { readonly: true });

// Promisify-based wrapper for async/await usage
function createConnection(db) {
  const conn = db.connect();
  return {
    all: promisify(conn.all.bind(conn)),
    run: promisify(conn.run.bind(conn)),
    exec: promisify(conn.exec.bind(conn)),
    close: () => conn.close(),
  };
}

// Startup configuration — set before any queries
async function configureSession(conn) {
  await conn.run("SET memory_limit='2GB'");
  await conn.run("SET threads=4");
  // Temp directory for spilling large intermediate results to disk
  await conn.run("SET temp_directory='/tmp/duckdb-spill'");
}

// Full initialization for an MCP server
async function initDuckDB(dbPath = ':memory:') {
  const db = new Database(dbPath);
  const conn = createConnection(db);
  await configureSession(conn);
  return { db, conn };
}

The exclusive write lock on persistent files is held at the OS level, not within DuckDB. If an MCP server crashes without closing the database, the lock file ({dbname}.db.wal) may remain, blocking the next startup. DuckDB handles stale locks on startup — if the owning process no longer exists, it reclaims the lock automatically.

Executing queries and schema introspection

DuckDB supports the full SQL standard with analytical extensions: window functions, GROUPING SETS, PIVOT, UNPIVOT, LIST_AGG, STRUCT and MAP types, and lateral joins. For MCP tools exposing DuckDB to LLM agents, surface the schema via information_schema queries before accepting user SQL.

// List tables in the current database
async function listTables(conn, schema = 'main') {
  return conn.all(`
    SELECT table_name, table_type
    FROM information_schema.tables
    WHERE table_schema = ?
    ORDER BY table_name
  `, schema);
}

// Get column schema for a table
async function describeTable(conn, tableName, schema = 'main') {
  return conn.all(`
    SELECT column_name, data_type, is_nullable, column_default
    FROM information_schema.columns
    WHERE table_schema = ?
      AND table_name = ?
    ORDER BY ordinal_position
  `, schema, tableName);
}

// Safe query execution with timeout
async function safeQuery(conn, sql, params = [], timeoutMs = 30_000) {
  const timeoutPromise = new Promise((_, reject) =>
    setTimeout(() => reject(new Error(`Query timed out after ${timeoutMs}ms`)), timeoutMs)
  );

  const queryPromise = params.length ? conn.all(sql, ...params) : conn.all(sql);
  return Promise.race([queryPromise, timeoutPromise]);
}

// MCP tool: query_duckdb with row limit guard
server.tool('query_duckdb', {
  sql: z.string().describe('DuckDB SQL query to execute'),
  max_rows: z.number().int().max(10_000).default(1_000)
    .describe('Maximum rows to return (capped at 10,000)'),
}, async ({ sql, max_rows }) => {
  // Wrap user SQL in a LIMIT to prevent returning millions of rows
  const limitedSql = `SELECT * FROM (${sql}) _q LIMIT ${max_rows}`;
  const rows = await safeQuery(conn, limitedSql);
  return {
    content: [{ type: 'text', text: JSON.stringify(rows, null, 2) }],
  };
});

Reading Parquet, CSV, and S3 files

DuckDB's most powerful feature for MCP tools is direct file querying — it reads Parquet, CSV, JSON, and Avro files without loading them into tables first. Combined with the httpfs extension, it can query files directly from S3, GCS, R2, or any HTTPS endpoint. This makes it ideal for MCP tools that give LLMs access to data lake files without a pipeline to load them first.

// Query Parquet files — no loading needed, DuckDB reads them directly
async function queryParquetFiles(conn, s3Path, sql, s3Config = {}) {
  // Install extensions once at startup, not per-query
  await conn.run('INSTALL httpfs');
  await conn.run('LOAD httpfs');

  // Configure S3 credentials
  if (s3Config.region) await conn.run(`SET s3_region='${s3Config.region}'`);
  if (s3Config.accessKeyId) await conn.run(`SET s3_access_key_id='${s3Config.accessKeyId}'`);
  if (s3Config.secretAccessKey) await conn.run(`SET s3_secret_access_key='${s3Config.secretAccessKey}'`);
  // For IAM role-based auth (e.g., EC2 instance profile): SET s3_endpoint='' and leave keys empty

  // Query with glob patterns — reads all matching files
  const result = await conn.all(`
    SELECT * FROM read_parquet('${s3Path}') WHERE ${sql} LIMIT 1000
  `);
  return result;
}

// Query a CSV file — auto-detect delimiter, types, and header
async function queryCSV(conn, path, options = {}) {
  const { delim, header = true, columns } = options;
  const opts = [
    header ? 'header=true' : 'header=false',
    delim ? `delim='${delim}'` : '',
    columns ? `columns=${JSON.stringify(columns)}` : '',
  ].filter(Boolean).join(', ');

  return conn.all(`SELECT * FROM read_csv('${path}'${opts ? ', ' + opts : ''}) LIMIT 1000`);
}

// Create a persistent table from Parquet — faster for repeated queries
async function materializeFromParquet(conn, tableName, s3Path) {
  await conn.run(`
    CREATE OR REPLACE TABLE ${tableName} AS
    SELECT * FROM read_parquet('${s3Path}')
  `);
  const [{ count }] = await conn.all(`SELECT count(*) AS count FROM ${tableName}`);
  return { table: tableName, rows: count };
}

Health monitoring — latency-based probes for an in-process database

DuckDB has no HTTP endpoint, no TCP port, and no external process to probe. AliveMCP cannot directly monitor a DuckDB instance — instead, configure AliveMCP to probe the MCP server's own /health endpoint, which performs a DuckDB latency check internally and returns the result as a structured health response.

// Health probe: measure query latency
// DuckDB is healthy when it responds quickly — degraded when slow
async function probeDuckDB(conn, thresholdMs = 500) {
  const start = performance.now();
  let error = null;
  let rowCount = 0;

  try {
    const rows = await conn.all('SELECT count(*) AS n FROM (SELECT 1) t');
    rowCount = rows[0].n;
  } catch (err) {
    error = err.message;
  }

  const latencyMs = performance.now() - start;

  return {
    ok: !error && latencyMs < thresholdMs,
    latencyMs: Math.round(latencyMs),
    error,
    degraded: !error && latencyMs >= thresholdMs,
  };
}

// For persistent file databases: also check file size and WAL state
async function probePersistentDuckDB(conn, dbPath) {
  const [probeResult, dbStats] = await Promise.all([
    probeDuckDB(conn),
    conn.all(`
      SELECT
        database_name, path,
        SUM(estimated_size) AS total_size
      FROM duckdb_databases()
      GROUP BY database_name, path
    `).catch(() => []),
  ]);

  return {
    ...probeResult,
    dbPath,
    dbSizeBytes: dbStats[0]?.total_size || null,
  };
}

// Express health endpoint — configure AliveMCP to probe this URL
app.get('/health/duckdb', async (req, res) => {
  const probe = await probeDuckDB(conn);
  const status = probe.ok ? 200 : (probe.degraded ? 200 : 503);
  res.status(status).json({
    status: probe.ok ? 'healthy' : (probe.degraded ? 'degraded' : 'unhealthy'),
    latencyMs: probe.latencyMs,
    ...(probe.error ? { error: probe.error } : {}),
  });
});

Register the MCP server's /health/duckdb endpoint with AliveMCP. Set the alert threshold at two consecutive non-200 responses (one-off slowdowns are expected during large analytical queries). A persistent latency above 500ms on simple queries (SELECT 1) indicates memory pressure, disk I/O contention, or a large pending WAL checkpoint.

MotherDuck — cloud-hosted DuckDB

MotherDuck provides a cloud-hosted DuckDB service that extends the local DuckDB engine with shared cloud storage. The connection string replaces the local file path with a md: prefix and a token. MotherDuck introduces the network and authentication concerns absent from local DuckDB — token-based auth, network latency, and a cloud endpoint that can be independently monitored.

// MotherDuck connection — has network latency unlike local DuckDB
const mdDb = new Database(`md:my_database?motherduck_token=${process.env.MOTHERDUCK_TOKEN}`);
const mdConn = createConnection(mdDb);

// MotherDuck supports hybrid local + cloud queries
// 'md:' prefix accesses cloud tables; 'my_local_db' accesses a local file
// SELECT * FROM my_cloud_db.main.events UNION ALL SELECT * FROM local_table

// MotherDuck health probe — network latency adds to response time
// Token auth — probe by executing a lightweight query
async function probeMotherDuck(conn) {
  const start = performance.now();
  try {
    await conn.all('SELECT current_database()');
    const latencyMs = Math.round(performance.now() - start);
    return { ok: true, latencyMs, backend: 'motherduck' };
  } catch (err) {
    return { ok: false, error: err.message, backend: 'motherduck' };
  }
}

Common integration pitfalls

File lock on second process open

Only one process can open a DuckDB file in read-write mode at a time. Opening the same .db file from two instances of an MCP server (e.g., during a rolling restart, or when running multiple workers) causes the second process to fail with IO Error: Could not set lock on file. Use readonly: true if the MCP tool only reads data, which allows concurrent opens.

OOM from unbounded analytical queries

DuckDB loads column data into memory in vectors. A query scanning a 50 GB Parquet file in an environment with 2 GB of RAM will not gracefully degrade — it will OOM-kill the Node.js process unless SET memory_limit is configured. Set memory_limit to 70% of available RAM and SET temp_directory to a disk path with sufficient space for spill files.

Extension installation requiring network access

INSTALL httpfs downloads the extension binary from DuckDB's CDN. This fails in air-gapped environments or containers without outbound internet access. Pre-install extensions at image build time, or use DuckDB's --extension-dir flag to point to a local extension cache.

Type coercion from Parquet vs DuckDB SQL types

Parquet's INT96 timestamp (used by older Spark/Hive writers) maps to DuckDB's TIMESTAMP with nanosecond precision. Comparing these values to SQL DATE literals requires explicit casting: WHERE event_time::DATE = '2026-01-01'. Without the cast, comparisons silently return no rows due to type mismatch.

WAL accumulation on write-heavy workloads

DuckDB writes uncommitted data to a WAL (Write-Ahead Log) file. On crash, the WAL is replayed on next open. With frequent small writes, the WAL can grow large before a checkpoint compacts it into the main file. Configure SET wal_autocheckpoint='16MB' to trigger compaction more frequently, or call PRAGMA force_checkpoint periodically from a background task.

Related guides

• MCP tools for BigQuery — service account auth, async Jobs API, dry-run cost estimation
• MCP tools for Snowflake — SQL API v2, JWT auth, warehouse auto-suspend
• MCP tools for Redshift Data API — SigV4 auth, async statement execution, cluster health
• MCP tools for Databricks — SQL Warehouse REST API, PAT auth, warehouse state lifecycle
• MCP server health check patterns — composite endpoints and failure classification
• MCP server uptime monitoring — probes, intervals, and alert routing