Metadata-Version: 2.4
Name: sqlnow-mcp
Version: 0.1.0
Summary: MCP server for DuckDB with interactive table viewer
Keywords: mcp,duckdb,sql,llm,claude,cursor
Author: David Raznick
Author-email: David Raznick <david.raznick@globalenergymonitor.org>
License-Expression: MIT
License-File: LICENSE
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Scientific/Engineering
Requires-Dist: click
Requires-Dist: duckdb
Requires-Dist: fastmcp
Requires-Dist: pyarrow
Requires-Dist: pytz
Requires-Dist: pyyaml
Requires-Dist: sqlglot
Requires-Python: >=3.11
Project-URL: Homepage, https://github.com/kindly/sqlnow-mcp
Project-URL: Repository, https://github.com/kindly/sqlnow-mcp
Project-URL: Issues, https://github.com/kindly/sqlnow-mcp/issues
Description-Content-Type: text/markdown

# 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](https://modelcontextprotocol.io/). 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](https://docs.astral.sh/uv/) (recommended) or pip
- Node.js 18+ (only if you need to rebuild the table UI)

## Install

From a clone of this repo:

```bash
uv sync
```

Or run without installing globally:

```bash
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

```bash
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

```bash
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):**

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

**HTTP:**

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

```json
{
  "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:

```bash
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):

```json
{
  "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`:

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

**Claude Desktop** — add to `claude_desktop_config.json`:

```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`.

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

**Cursor / Claude Desktop:**

```json
{
  "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:

```bash
# 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

```bash
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/`:

```bash
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

```bash
uv sync --group dev
uv run pytest
```

## License

See repository metadata and author information in `pyproject.toml`.
