# jmd-mcp-sql

A JMD demonstration server for relational data analysis through four MCP tools.
QBE supplies compact datasets; the LLM combines results and develops its own
analysis. The interface follows generic SQL concepts, with SQLite as its
execution backend. Exposing every SQLite feature is not the objective.

Start with `read("# Capabilities")` for the supported operations, controls,
QBE syntax, response shapes, examples, and current limitations. Then inspect
`read("#! Database")` and `read("#! <Table>")` for the actual database structure.
Server instructions and each emitted tool description stay within 500
characters; the returned discovery document carries the detailed contract.

Northwind is the default analysis playground. See the
[multi-query walkthrough](NORTHWIND-WALKTHROUGH.md) for exploring employee
performance without requiring joins or aggregation in a single request.

## 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` | Capabilities / exact lookup | QBE; optional joins and aggregation | Describe database objects | — |
| `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 declarations under `## columns`:
JMD types and modifiers (`readonly` = primary key, `optional` = nullable).
Table constraints live under `## constraints`, with `### primary-key[]`,
`### unique[]`, `### check[]` and `### references[]`. Column- and table-level
CHECK constraints are included: simple string enums use `a|b|c`; other
expressions remain SQL CHECK expressions.

For example, a column named `check` coexists with a CHECK constraint:

```jmd
#! Item
## columns
check: string optional
n: integer optional
## constraints
### check[]
- n > 0
```

Schema writes use this same structure. Legacy flat columns and root-level
constraint arrays are refused with placement guidance. Virtual tables also
put columns under `## columns`; `using`, `unindexed[]` and `options[]` stay
at the root. Inline `Index[]` and `Trigger[]` also remain at the root.

Both `open` and `read("#! Database")` list tables and views separately in
`tables[]` and `views[]`. The `open` response's `table-count` counts tables
only. Views remain available to ordinary reads and QBE queries.

Schema metadata is cached. After external schema changes, call `open` with
the same database path to refresh it. Calling `open` without a path only
returns status.

Schema output remains an approximation rather than a migration/backup format.
The column namespace is independent of schema metadata names; generated
columns and some default expressions still have discovery limitations.

## 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 — literal substring (SQLite's default ASCII case folding):**

Percent and underscore are ordinary characters here, not SQL wildcards.
An invalid regex is refused before execution rather than treated as text.

`field: null` matches SQL NULL; `field: !null` matches non-null values.
A quoted `"null"` remains a string, and a valueless query field (`field:`)
is an empty-string condition. Other negated predicates follow SQL
three-valued logic: `!Germany` does not include rows whose field is NULL.
Alternatives such as `null|Germany` explicitly include NULL.

**Filter rows — ranges with SQL semantics:**

```jmd
#? Products
UnitPrice: BETWEEN 10 AND 50
```

`BETWEEN` includes both endpoints. `NOT BETWEEN` (or `!BETWEEN`) negates the
range. For half-open periods, combine ordered comparisons on one field:

```jmd
#? Orders
OrderDate: >=1997-01-01 AND <1997-05-01
ShipCountry: Germany
```

All field predicates combine with AND. Within a field, ordered comparisons
can also combine with AND; either order of bounds works. Keywords are
case-insensitive. Bounds are parameterized JMD scalars; SQLite applies its
normal type affinity, collation and NULL logic. Dates remain stored values,
without timezone conversion or end-of-day assumptions. Reversed bounds need
not match any rows. A NULL endpoint follows SQL three-valued logic.

Quote endpoint strings containing connector words, for example
`name: BETWEEN "A AND B" AND "Z OR Z"`. Outer JMD quoting only encodes the
complete scalar; it does not disable its query operators. Existing equality,
contains and regex expressions retain their meaning. Malformed ranges are
refused before execution; there is no arbitrary SQL, Boolean OR or grouping
grammar inside a predicate.

Field-to-field comparisons and date arithmetic are not offered. Narrow the
dataset with supported filters, retrieve the required fields across all pages,
and finish the comparison or calculation locally. Repeating a field does not
add another condition; it is invalid JMD.

Projection, sorting, grouping, filters and HAVING resolve names against the
applicable source or aggregate-result namespace. Ambiguous joined columns
require explicit unambiguous projection or separate queries. Equivalent join
keys appear once. Malformed controls and duplicate result names are refused.


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

**Filter rows — regex pattern:**

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

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

```text
read("#? Orders\nShipCountry: !Germany")
read("#? Products\nProductName: !~re: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\n## columns\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.

Comma-separated controls take actual names without per-name quotes:
`select: TicketID, opened at`. The quotes in the schema key `"opened at"`
belong to JMD key syntax and are not part of the name. A column name containing
a comma cannot be addressed through these controls.

## 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.
Single-table and joined queries accept the same arithmetic subset:

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

Expressions accept simple column identifiers, numeric literals, parentheses,
unary signs, arithmetic operators (`+`, `-`, `*`, `/`) and single-argument
`ABS`, `ROUND`, `LENGTH`, `UPPER`, `LOWER` calls. A plain column reference
can also be its full name, including spaces. Commas separate aggregate entries;
multi-argument calls, nested aggregates, date arithmetic and arbitrary SQL
expressions are outside this grammar.

**Projection rules for join queries:**

- Unambiguous columns (appear in exactly one table) resolve automatically.
- Shared join keys appear once only when every occurrence is constrained equal.
- Other names present in multiple tables are ambiguous. Select unambiguous
  columns or query related tables separately; table-qualified names are not
  supported.

A one-to-many join repeats the one-side's values for every matching detail row.
Aggregate order-level measures such as freight before joining order details;
summing them after the join changes the measure. Several small queries and
local calculations are a normal analysis workflow.

## 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. Expected failures carry
structured context where available: parser `kind`, `line`, and `field`;
frontmatter `unknown[]` and `accepted[]`; path `path` and `root`; or SQLite
`sqlite-code` and `sqlite-name` (when supplied by the Python runtime).
Exception strings are never copied into responses.

Constraint violations return status 409, busy/locked databases 503, and
database permission failures 403. Unexpected implementation errors return
status 500 with `code: internal_error`; the traceback stays in the server log.
These statuses are fields in the JMD document, not HTTP transport statuses.

A failed database switch preserves the current connection. Bulk inserts
validate all records before writing and roll back the batch on execution
failure.

## Development

This checkout uses the published `jmd-format>=0.10.3` package and the compatible
MCP SDK v1 line (`mcp>=1.26,<2`). It does not depend on an editable sibling
checkout. Install the versions recorded in `uv.lock` and run the quality gates
before the tests:

```bash
uv sync --locked
uv run --locked ruff check .
uv run --locked mypy jmd_mcp_sql tests
uv run --locked pytest -q
```

CI runs these same gates on Python 3.10–3.13. The broader JMD API alignment
and remaining design questions are recorded in [JMD-REVIEW.md](JMD-REVIEW.md).

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