# snowflake-mcp-starter

A small, readable MCP server that gives Claude read-only access to Snowflake — and,
more usefully, a worked example of **two things that are easy to get wrong when
writing any stdio MCP server.**

No framework, no SDK. ~230 lines of standard library plus the Snowflake connector,
speaking JSON-RPC 2.0 over stdio directly, so you can read the whole protocol
interaction in one sitting.

---

## The two patterns

### 1. Answer `initialize` instantly, connect lazily

MCP clients put a short timeout on the `initialize` handshake. If your server opens a
database connection during startup, you spend that budget on the network — and with
browser SSO you're waiting on a *human*, which you will never win. The server gets
killed and the client reports "failed to connect", which sends you debugging the
database when the database was fine.

```python
_conn = None

def get_connection():
    global _conn
    if _conn is not None:
        return _conn, None
    import snowflake.connector          # deferred: the import alone is slow
    _conn = snowflake.connector.connect(**snowflake_params())
    return _conn, None
```

`initialize` returns immediately and touches nothing. The connection is built on the
first `tools/call` and cached. Measured on a laptop: `initialize` in **0.03s**,
first query 2–14s depending on whether SSO is cached, subsequent queries ~2s.

### 2. stdout belongs to the protocol

stdout *is* the transport. Anything else written there corrupts the stream, and the
client fails with an opaque parse error far from the cause.

This bites in practice. The Snowflake connector prints

```
Initiating login request with your identity provider. A browser window should have opened...
```

to **stdout** on first SSO login — exactly when a new user first runs your server.
A JSON-RPC client reading line-by-line hits that and dies.

```python
_PROTOCOL = os.fdopen(os.dup(1), "w", buffering=1)  # private handle to the real stdout
os.dup2(2, 1)                                       # fd 1 now points at stderr
sys.stdout = sys.stderr                             # and so does Python's stdout
```

Protocol writes go to `_PROTOCOL`. Every stray `print()` in your dependency tree
becomes harmless noise on stderr. Do this before importing anything that might print.

---

## Install

```bash
git clone https://github.com/<you>/snowflake-mcp-starter
cd snowflake-mcp-starter
./install.sh
```

`install.sh` creates a venv at `~/.local/share/snowflake-mcp-venv` rather than
installing into your system Python — uv- and Homebrew-managed interpreters are
[PEP 668](https://peps.python.org/pep-0668/) "externally managed" and will refuse a
direct `pip install`.

**The `[secure-local-storage]` extra is required, not optional.** It pulls in
`keyring`, which is what lets the connector cache the SSO token in your OS keychain.
Without it `client_store_temporary_credential` is silently a no-op and you get a
browser popup on *every* server start rather than roughly once a day.

## Configure

Everything is environment variables. `SNOWFLAKE_ACCOUNT` is the only required one.

| Variable | Required | Default | Notes |
|---|---|---|---|
| `SNOWFLAKE_ACCOUNT` | yes | — | Account locator, e.g. `abc12345.us-east-1` |
| `SNOWFLAKE_USER` | no | connector default | Usually your SSO email |
| `SNOWFLAKE_AUTHENTICATOR` | no | `externalbrowser` | `snowflake` for password auth |
| `SNOWFLAKE_DATABASE` | no | — | |
| `SNOWFLAKE_SCHEMA` | no | — | |
| `SNOWFLAKE_WAREHOUSE` | no | — | |
| `SNOWFLAKE_ROLE` | no | — | Point this at a read-only role |
| `SNOWFLAKE_PASSWORD` | no | — | Only read when authenticator is `snowflake` |

Register with Claude Code:

```bash
claude mcp add snowflake --scope user \
  --env SNOWFLAKE_ACCOUNT=abc12345.us-east-1 \
  --env SNOWFLAKE_USER=you@example.com \
  --env SNOWFLAKE_DATABASE=ANALYTICS \
  -- ~/.local/share/snowflake-mcp-venv/bin/python ~/.local/bin/snowflake_mcp_server.py

claude mcp list     # should report: snowflake ... Connected
```

`configs/mcp-config-locations.md` covers the equivalent for Claude Desktop and the
claude.ai connector surface, which are configured in different places.

## Safety

The server refuses anything that isn't `SELECT` / `SHOW` / `DESCRIBE` / `WITH` /
`EXPLAIN`. Treat that as a guard rail against accident, **not** as a SQL firewall —
it is a first-keyword check and does not attempt to parse the statement. The real
control is the Snowflake role: point `SNOWFLAKE_ROLE` at something read-only. An MCP
server grants no access its credentials don't already have.

Default to `externalbrowser`. It stores no secret on disk at all, which is a stronger
position than any amount of care with a password in a config file.

## Testing it without a client

The server is just line-delimited JSON on stdin/stdout, so you can drive it by hand:

```bash
printf '%s\n' \
  '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{}}' \
  '{"jsonrpc":"2.0","id":2,"method":"tools/list","params":{}}' \
| ~/.local/share/snowflake-mcp-venv/bin/python snowflake_mcp_server.py
```

Both should return instantly. If `initialize` is slow, you have reintroduced pattern 1.
If the output isn't parseable JSON, something is printing to stdout — pattern 2.

## Writing your own skill

`skills/query-warehouse/SKILL.md` is a template for a Claude Code
[skill](https://docs.claude.com/en/docs/claude-code/skills) that pairs with this
server: it encodes *which tables to use and which mistakes to avoid* so you stop
re-explaining your schema every session. Copy it to
`~/.claude/skills/<name>/SKILL.md` and fill in your own tables.

The `description:` line is the important part — Claude reads it to decide whether the
skill applies, so describe *when to use it*, not just what it does.

## License

MIT — see `LICENSE`.