Use CensusChat from Claude Desktop (MCP stdio)

Use CensusChat from Claude Desktop (MCP stdio)

Query US Census demographics from Claude Desktop. No web app, no Docker, no browser.

CensusChat ships an MCP server. It has two transports:

Transport	Runs inside	Use it for
HTTP (Streamable)	the Express backend	the web app, Postman, remote clients
stdio	its own process	Claude Desktop and other desktop MCP clients

This guide covers the stdio transport. Both transports expose the same tools and enforce the same SQL security controls.

What you get

Nine tools, all read-only against a local DuckDB file:

Tool	Does
get_information_schema	lists tables, columns, and the active security policy
validate_sql_query	checks SQL against the security policy without running it
execute_query	validates, then runs, a SELECT
execute_drill_down_query	pages block groups inside one county
execute_comparison_query	same as execute_query, rendered as a bar chart
execute_trend_query	same as execute_query, rendered as a line chart
generate_excel_report	writes an Excel file from a result set
generate_csv_report	writes a CSV file from a result set
generate_pdf_report	writes a PDF from a result set

Two tables, depending on what your database file contains: county_data (3,144 US counties) and block_group_data_expanded (239,741 block groups, 84 variables). The examples in this guide use county_data.

Prerequisites

1. Node.js 20 or newer. node --version.
2. This repository, cloned locally. The server is not on the npm registry. It is useless without a census database you build yourself, so a registry install would not save you a step.
3. Backend dependencies installed:

   cd CensusChat/backend
   npm ci
   

4. A census DuckDB file at a path you can point DUCKDB_PATH at. See below — this is the step that takes real work.

Getting the census database

Read this before running any loader. The loader scripts in backend/scripts/ (load-acs-data.ts, load-acs-blockgroup-expanded.ts, and the rest) all import * as duckdb from 'duckdb' — the legacy DuckDB npm package. This repository installs @duckdb/node-api instead and does not install duckdb. On a clean checkout every loader therefore fails immediately:

Error: Cannot find module 'duckdb'

This is a known gap in the loaders, not something this MCP transport introduced, and it affects the web app equally.

Your options today:

• Point DUCKDB_PATH at a census.duckdb you already have. This is the reliable path. The MCP server only reads the file; it does not care how it was produced.
• Install the legacy package yourself, then load. npm install duckdb in backend/, then run the loaders. This is not covered by CI and the native build may or may not succeed on your platform, so treat it as unverified.

If you do run the loaders, note that npm run load-blockgroups-expanded creates only block_group_data_expanded. county_data comes from npm run load-acs-data. Run both if you want the examples in this guide to work:

cd CensusChat/backend
npm run load-acs-data              # county_data (3,144 rows)
npm run load-blockgroups-expanded  # block_group_data_expanded (239,741 rows)

Both call the Census API and take a while. They need CENSUS_API_KEY in backend/.env — free from https://api.census.gov/data/key_signup.html. The result is backend/data/census.duckdb, roughly 170 MB.

You do not need an ANTHROPIC_API_KEY for this path. Claude Desktop is the model. ANTHROPIC_API_KEY is only for the web app, which does its own natural language to SQL translation. CENSUS_API_KEY is only for loading data, not for querying it.

There is no account, login, or token. The server reads one local file.

Build it

cd CensusChat/backend
npm run build

This produces backend/dist/mcp/stdioServer.js, which the config below points at.

Claude Desktop configuration

Edit your Claude Desktop config file:

• macOS — ~/Library/Application Support/Claude/claude_desktop_config.json
• Windows — %APPDATA%\Claude\claude_desktop_config.json
• Linux — ~/.config/Claude/claude_desktop_config.json

Add the censuschat entry. Replace both absolute paths with your own.

{
  "mcpServers": {
    "censuschat": {
      "command": "node",
      "args": ["/absolute/path/to/CensusChat/backend/dist/mcp/stdioServer.js"],
      "env": {
        "DUCKDB_PATH": "/absolute/path/to/CensusChat/backend/data/census.duckdb"
      }
    }
  }
}

Both paths must be absolute. Claude Desktop does not run the server from your shell, so ~ and relative paths do not resolve.

Restart Claude Desktop. The tools appear under the connectors icon.

Running from source instead

To skip the build step during development:

{
  "mcpServers": {
    "censuschat": {
      "command": "/absolute/path/to/CensusChat/backend/node_modules/.bin/ts-node",
      "args": ["/absolute/path/to/CensusChat/backend/src/mcp/stdioServer.ts"],
      "env": {
        "DUCKDB_PATH": "/absolute/path/to/CensusChat/backend/data/census.duckdb"
      }
    }
  }
}

Startup is a few seconds slower because ts-node compiles first.

Try it

Ask Claude Desktop:

Which 10 US counties have the highest median household income? Use CensusChat.

Claude writes the SQL, calls execute_query, and shows the rows. A direct query looks like this:

SELECT county_name, state_name, population, median_income
FROM county_data
WHERE population > 1000000
ORDER BY median_income DESC

Filter in the WHERE clause rather than with LIMIT — see Security below for why.

Security

The stdio transport uses the same validator as the HTTP transport (backend/src/validation/). It is not a shortcut around it.

• SELECT only. INSERT, UPDATE, DELETE, DROP, and ATTACH are rejected before execution.
• Table and column allowlists. A query naming an unlisted column fails validation.
• A 1,000 row cap on every result. Note that the validator replaces your LIMIT, it does not cap it: LIMIT 10 returns 1,000 rows. Filter in the query, or in Claude, if you want fewer.
• Blocked patterns: SQL comments, stacked statements, and known injection shapes.

A rejected query returns a validation error naming the rule it broke. Nothing is executed.

• Every execute_query call is written to the audit log: the validated SQL, the row count, the execution time, and any validation failure. The stdio entry point pins the log to backend/logs/sql-audit.log regardless of the working directory Claude Desktop starts it from. Override with AUDIT_LOG_DIR.

Troubleshooting

“Census database not found” and the server exits. DUCKDB_PATH points at a file that does not exist. Check the path in your config. DuckDB would otherwise create an empty database and every query would fail with “table not found”, so the server refuses to start instead. Build the database with npm run load-blockgroups-expanded.

Claude Desktop shows “server disconnected”. Read Claude Desktop’s MCP log (~/Library/Application Support/Claude/logs/mcp-server-censuschat.log on macOS). The usual causes are a wrong absolute path in command or args, or npm run build never having been run.

Cannot find module './stdioLogRedirect' from the built server. tsc is incremental. If you deleted dist/ without also deleting backend/.tsbuildinfo, it skips re-emitting files it believes are current. Delete both and rebuild: rm -rf dist .tsbuildinfo && npm run build.

The tools do not appear. Restart Claude Desktop fully. It reads the config only at startup.

node: command not found in the logs. Claude Desktop does not inherit your shell’s PATH. Use the absolute path to your node binary in command — which node gives it.

Verifying without Claude Desktop

cd CensusChat/backend
DUCKDB_PATH=$(pwd)/data/census.duckdb npx ts-node scripts/verify-mcp-stdio.ts

This spawns the server exactly as a client would, lists the tools, runs a real SELECT, and confirms a DELETE is rejected.

The automated tests in backend/src/__tests__/mcp/stdioServer.test.ts cover the same path against a small generated fixture, so they need no census data:

cd CensusChat/backend
npm test -- stdioServer