sqlnow-mcp 0.3.0

MCP server for DuckDB with interactive table viewer

sqlnow-mcp

An MCP server for DuckDB with an interactive table viewer. Point it at a data directory, attach files or external databases, and explore data through an LLM host such as Cursor or Claude Desktop. Query results from run_query, sample_table, and value_counts render as a scrollable grid inside the host via MCP Apps. Use run_query_json for small SELECT results the model needs as JSON (aggregations, counts, spot checks) — not for large exports.

Requirements

• Python 3.11+
• uv (recommended) or pip
• Node.js 18+ (only if you need to rebuild the table UI)

Install

From a clone of this repo:

uv sync

Or run without installing globally:

uvx sqlnow-mcp --mode local --data-dir ~/mydata

MCP server

Two modes are available:

• local — read/write; point at a data directory, switch databases, attach files and external connections.
• publish — read-only; single database with curated metadata.yaml (and optional stats.json) for sharing a branded dataset.

Transport (stdio, HTTP) works the same in both modes.

Local mode

sqlnow-mcp --mode local --data-dir ~/mydata

--data-dir is the working directory for .db files and attachable data. Session state (active database, attached sources) persists for the lifetime of the server process.

Publish mode

sqlnow-mcp --mode publish \
  --db ~/datasets/gemlive.db \
  --metadata ~/datasets/metadata.yaml \
  --stats ~/datasets/stats.json

--stats is optional. The server name and instructions come from metadata (short_name, welcome, example_questions, etc.). Queries are SELECT-only with a default 10s timeout, 50% memory limit, and 1 DuckDB thread (all overridable).

stdio (Claude Desktop):

{
  "mcpServers": {
    "gem-data": {
      "command": "uvx",
      "args": [
        "sqlnow-mcp", "--mode", "publish",
        "--db", "/path/to/gemlive.db",
        "--metadata", "/path/to/metadata.yaml"
      ]
    }
  }
}

HTTP:

sqlnow-mcp --mode publish \
  --db ~/datasets/gemlive.db \
  --metadata ~/datasets/metadata.yaml \
  --transport streamable-http \
  --host 127.0.0.1 \
  --port 8000

{
  "mcpServers": {
    "gem-data": {
      "url": "http://127.0.0.1:8000/mcp"
    }
  }
}

Publish workflow: call database_info() first, then list_tables / describe_table (with metadata descriptions), then run_query_json or run_query. Publish mode exposes only native tables in the database file — attached databases (sidecar) are not supported.

Generate a starter metadata file from a local database:

sqlnow-mcp --data-dir ./data -d demo generate-metadata -o metadata.yaml
sqlnow-mcp --data-dir ./data -d demo generate-stats -o stats.json

By default both commands include only native tables, not tables from attached databases.

Options

Shared (local and publish server startup):

Option	Default	Description
--transport	stdio	MCP transport: stdio, http, streamable-http, or sse
--host	127.0.0.1	Bind address when using an HTTP transport
--port	8000	Port when using an HTTP transport

Local mode only:

Option	Default	Description
--data-dir	.	Directory containing DuckDB .db files
--allow-path	$HOME	Extra directories allowed for attach_file (repeatable)
--allow-external / --no-allow-external	on	Allow attaching Postgres, SQLite, and MySQL databases

Publish mode only:

Option	Default	Description
--db	—	DuckDB file (required)
--metadata	—	metadata.yaml (required)
--stats	—	Precomputed stats.json (optional)
--strict-metadata	off	Fail startup on metadata/DB mismatches
--query-timeout	10	Per-query timeout in seconds
--memory-limit	50%	DuckDB memory_limit (percentage or absolute, e.g. 2GiB)
--threads	1	DuckDB worker threads
--max-temp-directory-size	—	Cap disk spill for large queries (e.g. 10GiB)

For security, --allow-path / is rejected (it would allow reading any file on disk).

stdio (default)

Best for editor integration. The host spawns the process and talks over stdin/stdout. Works for both --mode local and --mode publish.

Cursor — add to .cursor/mcp.json (project) or ~/.cursor/mcp.json (global):

{
  "mcpServers": {
    "sqlnow": {
      "command": "uv",
      "args": [
        "run",
        "--directory",
        "/absolute/path/to/sqlnow-mcp",
        "sqlnow-mcp",
        "--mode",
        "local",
        "--data-dir",
        "/home/you/mydata"
      ]
    }
  }
}

If the package is installed or you use uvx, you can omit --directory:

{
  "mcpServers": {
    "sqlnow": {
      "command": "uvx",
      "args": [
        "sqlnow-mcp",
        "--mode",
        "local",
        "--data-dir",
        "/home/you/mydata"
      ]
    }
  }
}

Claude Desktop — add to claude_desktop_config.json:

{
  "mcpServers": {
    "sqlnow": {
      "command": "uvx",
      "args": ["sqlnow-mcp", "--mode", "local", "--data-dir", "/home/you/mydata"]
    }
  }
}

Restart the host after changing MCP config.

HTTP

Run the server as a local HTTP service, then point the host at a URL instead of spawning a command. Works for both --mode local and --mode publish.

sqlnow-mcp --mode local \
  --data-dir ~/mydata \
  --transport streamable-http \
  --host 127.0.0.1 \
  --port 8000

Cursor / Claude Desktop:

{
  "mcpServers": {
    "sqlnow": {
      "url": "http://127.0.0.1:8000/mcp"
    }
  }
}

Use http or sse instead of streamable-http if your MCP client expects a different transport.

Typical workflow

The server exposes tools for database discovery, schema inspection, profiling, and SQL. A sensible order for the LLM (or you, when calling tools manually) is:

1. list_databases — find .db files in the data directory
2. create_database — create a new empty database (optional)
3. use_database or use_memory — open a session (required before most other tools)
4. attach_file / attach_database — load CSV, Parquet, JSON, XLSX, or external DBs
5. list_tables — every table includes column names and types; call describe_table for one table
6. sample_table, profile_table, value_counts — build context before writing SQL (no ad-hoc SELECT ... LIMIT 2 for schema)
7. run_query_json — small SELECT for model-side analysis (aggregations, counts, low limits); full JSON in context — not for large result sets
8. run_query — larger or user-visible SELECT results in the table viewer

Schema vs. results: After list_tables, column names and types are already available — do not use run_query with a small LIMIT just to inspect structure. Use describe_table, profile_table, or run_query_json with a low limit instead.

Tools that open the interactive table viewer (run_query, sample_table, value_counts) are wired to the MCP Apps UI resource ui://sqlnow/table.html. Large run_query results are paginated internally; the viewer calls fetch_table_page to load additional rows. run_query_json inlines all returned rows as JSON in the tool response — keep result sets small and aggregate in SQL when possible.

Dev CLI

The same entry point also provides subcommands for testing without an MCP host:

# Create and use a database
sqlnow-mcp --data-dir ./data create-database demo
sqlnow-mcp --data-dir ./data -d demo attach-file ./samples/events.csv
sqlnow-mcp --data-dir ./data -d demo list-tables
sqlnow-mcp --data-dir ./data -d demo generate-metadata -o metadata.yaml
sqlnow-mcp --data-dir ./data -d demo generate-stats -o stats.json
sqlnow-mcp --data-dir ./data -d demo query "SELECT * FROM events LIMIT 10"

Run sqlnow-mcp --help for the full command list.

Building the table UI

The shipped artifact is a single self-contained HTML file at sqlnow_mcp/ui/table.html. The MCP server serves it as the ui://sqlnow/table.html resource (see sqlnow_mcp/ui.py). You normally use the committed copy as-is; rebuild only after changing the React source under ui/.

Source layout

ui/
├── index.html          # Vite entry HTML
├── vite.config.ts      # React + single-file bundle
├── package.json
└── src/
    ├── main.tsx
    ├── TableApp.tsx    # glide-data-grid + @modelcontextprotocol/ext-apps
    └── types.ts

Vite bundles React, glide-data-grid, and the MCP Apps client into one inline HTML file via vite-plugin-singlefile.

Build steps

cd ui
npm install
npm run build

The build script runs vite build and copies the output to the Python package:

ui/dist/index.html  →  sqlnow_mcp/ui/table.html

Commit sqlnow_mcp/ui/table.html if you changed the UI and want others to get the update without running Node.

Local UI preview

There is no dev script in package.json, but you can run Vite's dev server from ui/:

cd ui
npm install
npx vite

This serves ui/index.html with hot reload. MCP Apps integration (host postMessage, fetch_table_page) only works inside a real MCP host; use the built table.html with the running MCP server for end-to-end testing.

Development

uv sync --group dev
uv run pytest

License

See repository metadata and author information in pyproject.toml.