# jmd-mcp-sql

MCP server that exposes a SQLite database through four JMD tools — a natural language database interface for LLM-driven workflows.

## What is JMD?

[JMD](https://github.com/ostermeyer/jmd-spec) (JSON Markdown) is a lightweight
document format that combines Markdown headings with `key: value` pairs. It is
designed as a structured data format that LLMs can read and write naturally —
without JSON brackets or SQL syntax. A heading line sets the document type and
target table; the body carries the data:

```text
# Order
id: 42
status: shipped
total: 149.99
```

A prefix on the heading selects the operation: `#` for data, `#?` for queries,
`#!` for schema, `#-` for deletes. See the
[JMD specification](https://github.com/ostermeyer/jmd-spec) for the full format
definition.

## Tools

| Tool | `#` Data | `#?` Query | `#!` Schema | `#-` Delete |
| --- | --- | --- | --- | --- |
| `open` | Open database / show status | — | — | — |
| `read` | SELECT by fields | SELECT with filters + aggregation | PRAGMA (describe table) | — |
| `write` | INSERT OR REPLACE | — | CREATE / ALTER TABLE | — |
| `delete` | — | — | DROP TABLE | DELETE WHERE |

All inputs and outputs are JMD documents. The LLM speaks JMD — no SQL required.

## Installation

Install from PyPI:

```bash
pip install jmd-mcp-sql
```

Or with [uv](https://docs.astral.sh/uv/) (no manual install needed — `uvx` fetches it on demand):

```bash
uvx jmd-mcp-sql
```

Alternatively, install directly from GitHub:

```bash
pip install git+https://github.com/ostermeyer/jmd-mcp-sql.git
```

## Configuration

The server runs as a **stdio-based MCP server**. Without arguments it starts with
the bundled Northwind demo database. Pass a path to use your own SQLite file:

```bash
jmd-mcp-sql /path/to/your.db
```

The demo database ships as `northwind.sql` (plain text, version-controlled). On the
first run without an explicit path, the server creates `northwind.db` from that dump
automatically.

### Claude Code

Add the server via CLI:

```bash
claude mcp add --transport stdio sql -- uvx jmd-mcp-sql
```

With a custom database:

```bash
claude mcp add --transport stdio sql -- uvx jmd-mcp-sql /path/to/your.db
```

This writes a `.mcp.json` in the project root (shareable via version control).
You can also create it manually:

```json
{
  "mcpServers": {
    "sql": {
      "command": "uvx",
      "args": ["jmd-mcp-sql"]
    }
  }
}
```

### Claude Desktop / Cowork

Claude Cowork runs inside Claude Desktop. MCP servers configured in the Desktop
config are automatically available in Cowork sessions.

Edit `claude_desktop_config.json`:

- **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`
- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`

```json
{
  "mcpServers": {
    "sql": {
      "command": "uvx",
      "args": ["jmd-mcp-sql"]
    }
  }
}
```

With a custom database:

```json
{
  "mcpServers": {
    "sql": {
      "command": "uvx",
      "args": ["jmd-mcp-sql", "/path/to/your.db"]
    }
  }
}
```

Restart Claude Desktop after saving the file. The server will appear as a tool
in both Chat and Cowork mode.

### VS Code

Create `.vscode/mcp.json` in the project root:

```json
{
  "servers": {
    "sql": {
      "type": "stdio",
      "command": "uvx",
      "args": ["jmd-mcp-sql"]
    }
  }
}
```

Alternatively, add it to your VS Code `settings.json` (user or workspace):

```json
{
  "mcp": {
    "servers": {
      "sql": {
        "type": "stdio",
        "command": "uvx",
        "args": ["jmd-mcp-sql"]
      }
    }
  }
}
```

## JMD Document Syntax

Every document starts with a heading line that sets the document type and table name,
followed by `key: value` pairs (one per line):

```text
# Product          → data document   (exact lookup / insert-or-replace)
#? Product         → query document  (filter / list / aggregate)
#! Product         → schema document (describe / create / drop table)
#- Product         → delete document (delete matching records)

key: value         → string, integer, or float — inferred automatically
key: true/false    → boolean
```

## Opening a Database

Open a different SQLite database at any time:

```text
open("# Database\npath: /path/to/mydb.db")
```

If the file does not exist, a new empty database is created. The previous
database is closed automatically. The response uses frontmatter for metadata
and lists tables in the body:

```text
path: /path/to/mydb.db
table-count: 3

# Database
## tables[]
- Customers
- Orders
- Products
```

Check which database is currently active:

```text
open("# Database")
```

## Path Restriction

The server reads optional settings from `~/.config/jmd/sql.jmd`:

```text
# Config
root: /Users/me/data
```

| Field | Default | Description |
| --- | --- | --- |
| `root` | *(none)* | Restricts `open` to databases under this directory tree |

Without a config file, no restrictions apply and the server accepts any path.

## Discovering the Database

To see which tables exist, read each table's schema:

```text
read("#! Customers")
```

This returns a `#!` document with column names, JMD types, and modifiers
(`readonly` = primary key, `optional` = nullable).

## Typical Workflows

**List all rows** (small tables only):

```text
read("#? Orders")
```

**Filter rows — equality:**

```text
read("#? Orders\nstatus: shipped")
```

**Filter rows — comparison:**

```text
read("#? Orders\nFreight: > 50")
```

**Filter rows — alternation (OR):**

```text
read("#? Orders\nShipCountry: Germany|France|UK")
```

**Filter rows — contains (case-insensitive substring):**

```text
read("#? Customers\nCompanyName: ~Corp")
```

**Filter rows — regex pattern:**

```text
read("#? Products\nProductName: ^Chai.*")
```

**Filter rows — negation** (composes with any operator):

```text
read("#? Orders\nShipCountry: !Germany")
read("#? Products\nProductName: !^LEGACY.*")
```

**Look up one record:**

```text
read("# Customers\nid: 42")
```

**Insert or replace a record:**

```text
write("# Orders\nid: 1\nstatus: pending\ntotal: 99.90")
```

**Create a table:**

```text
write("#! Products\nid: integer readonly\nname: string\nprice: float optional")
```

**Delete a record:**

```text
delete("#- Orders\nid: 1")
```

**Drop a table:**

```text
delete("#! OldTable")
```

## Pagination

Always use pagination when querying tables that may contain many rows.

Use frontmatter fields before the `#?` heading to control pagination:

```text
read("page-size: 50\npage: 1\n\n#? Orders")
```

The response carries pagination metadata as **frontmatter** — before the root heading:

```text
total: 830
page: 1
pages: 17
page-size: 50

# Orders
## data[]
- OrderID: 10248
  ...
```

**Count only** (no rows returned):

```text
read("count: true\n\n#? Orders")
```

Returns:

```text
count: 830

# Orders
```

Use `total` and `pages` to determine whether to fetch more pages.
For tables with fewer than ~20 rows pagination is optional.

## Field Projection

Use `select:` frontmatter to return only specific columns. This keeps
responses small and context windows focused.

```text
read("select: OrderID, EmployeeID\npage-size: 50\n\n#? Orders")
```

Works with both `#` (data) and `#?` (query) documents. When combined with
aggregation, `select:` filters the result columns after the GROUP BY.

## Joins

Use `join:` frontmatter to query across multiple tables in one call.
The value is `<TableName> on <JoinColumn>` (INNER JOIN, equi-join on a
column that exists in both tables).

```text
read("join: Order Details on OrderID\nsum: UnitPrice * Quantity * (1 - Discount) as revenue\ngroup: EmployeeID\nsort: revenue desc\n\n#? Orders")
```

**Multiple joins** — comma-separated in a single `join:` value:

```text
join: Order Details on OrderID, Employees on EmployeeID
```

**Expression syntax** — use `<expression> as <alias>` in aggregate functions
to compute derived values across joined columns:

```text
sum: UnitPrice * Quantity * (1 - Discount) as revenue
```

The alias becomes the result column name. Without `as`, the default alias
`<func>_<field>` applies (e.g. `sum_Freight`).

Allowed in expressions: column names, numeric literals, arithmetic operators
(`+`, `-`, `*`, `/`), and standard SQL functions (`SUM`, `AVG`, `ROUND`, …).
Subqueries and SQL keywords are not permitted.

**Projection rules for join queries:**

- Unambiguous columns (appear in exactly one table) resolve automatically.
- Join key columns always resolve to the main table.
- Columns present in multiple tables (other than join keys) require explicit
  qualification — specify them via `select:` or filter on the unambiguous side.

## Aggregation

Aggregation is expressed as **frontmatter** before the `#?` heading.
QBE filter fields narrow rows *before* aggregation (SQL WHERE).
The `having:` key filters *after* aggregation (SQL HAVING).

| Key | SQL | Result column name |
| --- | --- | --- |
| `group: f1, f2` | GROUP BY | grouping keys pass through unchanged |
| `sum: field` | SUM(field) | `sum_field` |
| `avg: field` | AVG(field) | `avg_field` |
| `min: field` | MIN(field) | `min_field` |
| `max: field` | MAX(field) | `max_field` |
| `count` | COUNT(*) | `count` |

Multiple fields per function: `sum: Freight, Total` → `sum_Freight` and `sum_Total`.

| Frontmatter | Meaning |
| --- | --- |
| `sort: sum_revenue desc, EmployeeID asc` | ORDER BY (multiple columns, mixed) |
| `having: count > 5` | HAVING COUNT(*) > 5 |
| `having: sum_Freight > 1000, count > 2` | HAVING … AND … (comma = AND) |

`having:` supports: `>`, `>=`, `<`, `<=`, `=`.
`sort:` references any result column — grouping keys or aggregate aliases.
`page-size:` and `page:` apply to the aggregated result set.

**Example — top 3 employees by revenue:**

```text
read("group: EmployeeID\nsum: revenue\nsort: sum_revenue desc\npage-size: 3\n\n#? OrderDetails")
```

## Error Handling

All tools return a `# Error` document on failure:

```text
# Error
status: 400
code: not_found
message: No records found in Orders
```

Check the `code` field to decide how to proceed.

## Specification

The JMD format is documented at [jmd-spec](https://github.com/ostermeyer/jmd-spec).

## License

Copyright © 2026 Andreas Ostermeyer.

Licensed under the Apache License, Version 2.0 — see [LICENSE](LICENSE).
The JMD ecosystem is open: the specification is CC BY 4.0, the Python and
JavaScript reference implementations are Apache 2.0, and this server matches.
No copyleft, no dual licensing, no CLA. Use it, fork it, extend it, ship it —
attribution preserved per Apache 2.0 § 4.

### License history

This project has changed license twice during its early development:

- **0.4 – 0.7.1** (MIT, yanked from PyPI as the default install target)
- **0.8.0 – 0.9.x** (AGPL-3.0, reflecting a brief period in which a commercial
  deployment path was under consideration)
- **0.10.0 and later** (Apache 2.0, aligned with the rest of the JMD ecosystem)

Users who installed any of the prior versions retain the rights those licenses
granted for those specific artifacts. License changes are not retroactive.
See [CHANGELOG.md](CHANGELOG.md) for the rationale.