# analyticbase

analyticbase lets an agent — or you, at a terminal — look inside a
ClickHouse database and ask it questions, without ever being able to
change it. It lists the databases on a server, lists the tables in
one database along with each table's engine, comment, row count and
column count, and runs single read-only `SELECT` queries. Everything
travels over ClickHouse's plain HTTP interface, so there is no client
library bundled and no native port to tunnel.

When ClickHouse is not there, analyticbase says so. The MCP server
starts and lists its tools with no server behind it at all; only an
actual operation makes a request, and a refused connection or a
server error comes back as an error carrying the server's own text —
never as invented rows.

## Install

```bash
npx @reneturcios/analyticbase list-databases
```

Or run the container image:

```bash
docker run -i ghcr.io/cloudcorprecords/analyticbase
```

The container defaults to the MCP server (see below). Pass CLI
arguments to use it as a CLI instead.

## CLI

```bash
# Which databases does this server hold?
analyticbase list-databases

# What tables live in one database? (name filters are LIKE patterns)
analyticbase list-tables analytics
analyticbase list-tables analytics --like "events_%" --not-like "%_tmp"

# Run one read-only query; rows print as JSON
analyticbase query "SELECT count() AS n FROM analytics.events"

# Start the MCP server
analyticbase mcp

analyticbase --version
```

Exit codes: `0` on success, `1` when the query, the guard, or the
connection fails (the error says which), `2` for usage errors.

The endpoint is configured through the environment:

| Variable | Meaning | Default |
| --- | --- | --- |
| `CLICKHOUSE_URL` | Whole base URL of the HTTP interface; overrides host and port | — |
| `CLICKHOUSE_HOST` | Hostname, or a full `http(s)://` URL | `localhost` |
| `CLICKHOUSE_PORT` | HTTP interface port | `8123` |
| `CLICKHOUSE_USER` | Username | `default` |
| `CLICKHOUSE_PASSWORD` | Password | (empty) |

## How the queries work

Each operation POSTs its SQL to the HTTP interface as the request
body, with the credentials as URL parameters — the interface's own
convention. The listing operations append `FORMAT JSONEachRow` and
parse one JSON object per line; `list_tables` reads `system.tables`
and counts each table's columns with a subquery over
`system.columns`, applying the `like` / `not_like` filters as `LIKE`
/ `NOT LIKE` clauses with safely escaped literals. Select queries
append `FORMAT JSON` and return the response's `data` array.

## The read-only guard

`run_select_query` (and the CLI `query` command) accepts a query only
if all of these hold:

- After leading whitespace and comments are stripped, its first
  keyword is `SELECT` or `WITH`.
- It is a single statement: a semicolon followed by anything other
  than whitespace ends the conversation.
- None of `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `CREATE`,
  `TRUNCATE`, `GRANT`, `REVOKE`, `OPTIMIZE`, `RENAME`, `DETACH`, or
  `ATTACH` appears as a whole word outside a string literal.
  Single-quoted literals are blanked out before scanning, so
  `SELECT 'drop table t'` is a fine query; a column literally named
  `update` is not — the guard would rather refuse an odd name than be
  talked around.

The listing operations build their SQL internally from validated,
escaped pieces, so they need no guard.

## MCP server

One bin serves both faces. The `mcp` subcommand starts a stdio MCP
server:

```bash
npx -y @reneturcios/analyticbase mcp
```

MCP client configuration:

```json
{
  "mcpServers": {
    "analyticbase": {
      "command": "npx",
      "args": ["-y", "@reneturcios/analyticbase", "mcp"],
      "env": { "CLICKHOUSE_HOST": "localhost", "CLICKHOUSE_PORT": "8123" }
    }
  }
}
```

### Tools

| Tool | Input | What it returns |
| --- | --- | --- |
| `list_databases` | (none) | One object per database, from `system.databases`. |
| `list_tables` | `database` (string, required), `like` / `not_like` (strings, optional) | One object per table in that database: its schema (database), name, engine, comment, row count, and column count. |
| `run_select_query` | `query` (string, required) — one read-only SQL statement | The result rows as a JSON array of objects keyed by column name. Guard violations, a missing server, and database errors all come back as tool errors — never as made-up rows. |

## Development

```bash
npm install
npm run build   # tsc -> dist/
npm test        # node test runner over test/*.test.ts, no network, no live ClickHouse
npm run dev     # run the CLI from source via tsx
```

All HTTP goes through an injectable fetch function, which is what the
test suite drives: config resolution, the guard, SQL construction,
both response-format parsers, and all three operations are covered
against a fake fetch.

## License

MIT — see `LICENSE`.