# spanner-mcp-server

A [Model Context Protocol](https://modelcontextprotocol.io) (MCP) server that
exposes **schema introspection** and **read-only queries** for a single Google
Cloud Spanner instance and database. Authentication uses a Google **Service
Account JSON** key file.

Built with **Node.js 24+**, **TypeScript**, and well-established packages:

- [`@modelcontextprotocol/sdk`](https://www.npmjs.com/package/@modelcontextprotocol/sdk) — MCP server (stdio transport)
- [`@google-cloud/spanner`](https://www.npmjs.com/package/@google-cloud/spanner) — official Cloud Spanner client
- [`zod`](https://www.npmjs.com/package/zod) — configuration & input validation

## Safety model

Every query runs inside a Spanner **read-only snapshot transaction**, so the API
cannot mutate data regardless of the SQL submitted. As defence-in-depth, a
SQL guard rejects anything that is not a single `SELECT`/`WITH` statement
(comments and string literals are stripped before keyword analysis, and multiple
statements are refused). Grant the service account only the
**Cloud Spanner Database Reader** (`roles/spanner.databaseReader`) role.

## Tools

| Tool | Arguments | Description |
| --- | --- | --- |
| `spanner_get_database_info` | — | Target project/instance/database and SQL dialect. |
| `spanner_get_ddl` | — | Full DDL (`CREATE TABLE`, `CREATE INDEX`, …). |
| `spanner_list_tables` | — | User tables and views, with interleave parents. |
| `spanner_describe_table` | `table: string` | Columns: name, type, nullability, generated. |
| `spanner_list_indexes` | `table: string` | Indexes and their key columns. |
| `spanner_execute_query` | `sql: string`, `params?: object` | Runs a read-only query; rows returned as JSON. |

Results from `spanner_execute_query` are capped at `SPANNER_MAX_ROWS`; the
response includes a `truncated` flag when the cap is reached.

## Configuration

Set via environment variables (see [`.env.example`](./.env.example)):

| Variable | Required | Default | Description |
| --- | --- | --- | --- |
| `GOOGLE_APPLICATION_CREDENTIALS` | ✅ | — | Path to the service account JSON key file. |
| `SPANNER_INSTANCE_ID` | ✅ | — | Target Spanner instance id. |
| `SPANNER_DATABASE_ID` | ✅ | — | Target Spanner database id. |
| `SPANNER_PROJECT_ID` | — | project from key file | GCP project id. |
| `SPANNER_MAX_ROWS` | — | `1000` | Max rows returned per query. |
| `SPANNER_QUERY_TIMEOUT_MS` | — | `30000` | Per-query timeout (ms). |

Both **GoogleSQL** and **PostgreSQL** dialect databases are supported; the
dialect is detected at startup and query parameters are adapted automatically
(`@name` for GoogleSQL, `$1` for PostgreSQL).

## Install & build

```bash
npm install
npm run build
```

## Run

```bash
# For local development (loads .env, runs TypeScript directly):
npm run dev

# Production (after `npm run build`):
node dist/index.js
```

The server speaks MCP over **stdio**; protocol traffic uses stdout and all logs
go to stderr.

## Use with an MCP client

Example client configuration (e.g. Claude Desktop / any MCP-compatible host):

```json
{
  "mcpServers": {
    "spanner": {
      "command": "node",
      "args": ["/absolute/path/to/spanner-mcp-server/dist/index.js"],
      "env": {
        "GOOGLE_APPLICATION_CREDENTIALS": "/absolute/path/to/service-account.json",
        "SPANNER_PROJECT_ID": "my-gcp-project",
        "SPANNER_INSTANCE_ID": "my-instance",
        "SPANNER_DATABASE_ID": "my-database"
      }
    }
  }
}
```

### Example query call

```jsonc
// tool: spanner_execute_query
{
  "sql": "SELECT SingerId, FirstName FROM Singers WHERE SingerId = @id",
  "params": { "id": 1 }
}
```

## Project layout

```
src/
  index.ts       # stdio entrypoint & lifecycle
  server.ts      # MCP server + tool registration
  spanner.ts     # Spanner client wrapper (schema + read-only queries)
  config.ts      # environment validation (zod)
  sql-guard.ts   # read-only SQL statement guard
```

## License

MIT