# ask-oracle-mcp

MCP server for Oracle DB: schema introspection and SQL execution over stdio.

[![npm version](https://img.shields.io/npm/v/ask-oracle-mcp.svg)](https://www.npmjs.com/package/ask-oracle-mcp)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![CI](https://github.com/doberkofler/ask-oracle-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/doberkofler/ask-oracle-mcp/actions/workflows/ci.yml)
[![Coverage Status](https://coveralls.io/repos/github/doberkofler/ask-oracle-mcp/badge.svg?branch=main)](https://coveralls.io/github/doberkofler/ask-oracle-mcp?branch=main)

The server has no LLM dependency. Tool selection, natural-language-to-SQL translation, and answer composition belong to the MCP host. The bundled demo uses pi-ai to run the same bounded tool-calling loop with Ollama or a configured hosted provider.

## Architecture

```text
client-example.ts (transport and terminal I/O)
        |
        v
agent.ts -- bounded LLM/MCP tool-calling loop
        |
        v
llm.ts (pi-ai providers, models, and authentication)
        | MCP over stdio
        v
server.ts -- list_tables (compact discovery)
          |- describe_table (one table's columns)
          `- run_sql (execution, read-only by default)
        |
        v
db.ts (oracledb thin-mode pool)
```

The model receives tool definitions directly from MCP `listTools()`. It decides when to call `list_tables`, `describe_table`, and `run_sql`, then uses the tool results to produce a plain-language answer. It never receives an automatic whole-schema dump.

## Tools

| Tool | Input | Behavior |
| --- | --- | --- |
| `list_tables` | `{owner?: string, like?: string}` | Lists table names only, optionally filtered with a bound SQL LIKE pattern. |
| `describe_table` | `{tableName: string, owner?: string}` | Returns columns for exactly one table. |
| `run_sql` | `{sql: string, binds?: object, allowWrite?: boolean}` | Executes SQL. Writes require both the server `--allow-write` flag and `allowWrite: true`. |

## Requirements

- Node.js 24 or newer
- pnpm 11
- Access to an Oracle database
- Ollama or credentials for a pi-ai provider for the optional demo CLI

Oracle Database access uses node-oracledb thin mode, so Oracle Instant Client is not required.

## Setup

```bash
pnpm install
pnpm run build
```

Configure Oracle with environment variables:

```bash
export ORACLE_USER=myuser
export ORACLE_PASSWORD=mypassword
export ORACLE_SERVER="host:1521/service_name"
```

The demo and standalone server also accept persistent CLI configuration:

```bash
ask-oracle \
  --oracle-user myuser \
  --oracle-password mypassword \
  --oracle-server "host:1521/service_name"
```

The same `--oracle-user`, `--oracle-password`, and `--oracle-server` options are accepted by `ask-oracle-mcp`. Every supplied Oracle CLI value is saved for later runs. A password supplied on the command line can be visible in shell history and process listings; prefer `ORACLE_PASSWORD` where that exposure is unacceptable.

## Run the Server

Run the published package directly from an MCP host or terminal:

```bash
npx -y ask-oracle-mcp@latest
```

Build and start the standalone stdio server:

```bash
pnpm run build
pnpm start
```

An MCP host can equivalently launch `node /absolute/path/to/ask-oracle-mcp/dist/server.js` and provide the three Oracle environment variables or CLI options to that process.

The server writes one readiness banner and errors to stderr while reserving stdout for the MCP protocol. Detailed lifecycle tracing is opt-in through `DEBUG`.

The server is strictly read-only by default. To make writes available to trusted MCP callers, opt in explicitly:

```bash
pnpm start -- --allow-write
```

## Run the Demo

The bundled demo lets the model call MCP tools until it has a final answer. The loop is capped at 8 model turns and 16 total tool calls. Every tool response is limited to 20,000 characters before it is added to model context, preventing schema or query results from exhausting the context window. Generated SQL is stripped of markdown fences and trailing semicolons before being sent to node-oracledb.

When installed from npm, run the interactive client with:

```bash
npx -y --package ask-oracle-mcp@latest ask-oracle
```

On the first interactive run, the client opens a guided setup that:

1. Lists pi-ai providers and their authentication status.
2. Runs the selected provider's API-key or OAuth login flow when needed.
3. Searches the provider's model catalog and saves the selection.

Run setup again at any time with:

```bash
# npm package
npx -y --package ask-oracle-mcp@latest ask-oracle --configure

# development checkout
pnpm run demo -- --configure
```

Persistent data defaults to `${XDG_CONFIG_HOME:-~/.config}/ask-oracle-mcp`:

- `auth.json` stores pi-ai-compatible LLM credentials.
- `llm-config.json` stores the selected provider and model.
- `oracle-config.json` stores the Oracle user and connect string.
- `oracle-auth.json` stores the Oracle password.

Credential and configuration files are written with mode `0600`. Set `ASK_ORACLE_CONFIG_DIR` to override the application directory. `LLM_AUTH_FILE` and `LLM_CONFIG_FILE` continue to override the individual LLM paths. The client never writes credentials to `llm-config.json`.

For local inference, install and start Ollama with a suitable tool-calling model:

```bash
ollama pull llama3.1
ollama serve
```

Then run the built demo and select Ollama. Setup lists models installed by the configured Ollama server:

```bash
# Optional; defaults to http://localhost:11434
export OLLAMA_HOST=http://localhost:11434

pnpm run build
pnpm run demo
```

No API key is required. Data stays between the local Ollama process and Oracle.

For automation and non-interactive execution, configuration can still be supplied explicitly. CLI values override environment variables, which override saved configuration:

1. Provider: `--llm`, `LLM_PROVIDER`, saved provider, interactive selection, then the non-interactive `ollama` fallback.
2. Model: `--model`, `LLM_MODEL`, `OLLAMA_MODEL`/`OPENAI_MODEL`, matching saved model, then interactive selection.
3. Oracle: `--oracle-user`/`--oracle-password`/`--oracle-server`, `ORACLE_USER`/`ORACLE_PASSWORD`/`ORACLE_SERVER`, then saved Oracle configuration.

`--configure` starts a fresh interactive selection and ignores environment and saved provider/model defaults. Combine it with `--llm` or `--model` to preselect either value explicitly.

For example, OpenAI with an environment API key:

```bash
export LLM_PROVIDER=openai
export LLM_MODEL=gpt-4o-mini
export OPENAI_API_KEY=your-api-key

pnpm run demo
```

Or select both values through CLI options:

```bash
pnpm run demo -- --llm openai --model gpt-4o-mini
```

OAuth login and token refresh are handled directly by the guided setup through pi-ai. API-key environment variables, saved credentials, ambient cloud credentials, and provider-specific endpoints continue to be resolved by the selected provider.

The demo uses an Inquirer prompt and accepts multiple questions in one session. Enter `/exit` or `/quit`, press Ctrl-C, or close input to disconnect cleanly. A failed question is reported without ending the session. Expected configuration and connection failures are shown without stack traces and explain which environment variable, login, local service, or model needs attention.

The demo is read-only unless write capability is explicitly enabled for both the agent and its spawned server:

```bash
pnpm run demo -- --allow-write
```

Only use this flag with a trusted model and trusted prompts. The model must still request `allowWrite: true` for a non-read-only statement.

## Debug Tracing

Normal operation shows only the server readiness banner, the client mode/exit banner, prompts, answers, and errors. Enable detailed traces with the `debug` namespaces:

```bash
# Server lifecycle only
DEBUG=ask-oracle-mcp:server pnpm start

# Client, agent turns/tool calls, LLM requests, and spawned server
DEBUG=ask-oracle-mcp:* pnpm run demo
```

Available namespaces are `ask-oracle-mcp:server`, `ask-oracle-mcp:client`, `ask-oracle-mcp:client:agent`, and `ask-oracle-mcp:client:llm`. Traces omit credentials, connection details, questions, SQL text, and result content.

## Development

| Command | Purpose |
| --- | --- |
| `pnpm run dev` | Rebuild with Vite in watch mode. |
| `pnpm run build` | Build `dist/server.js` and `dist/client-example.js`. |
| `pnpm run start` | Run the built MCP server. |
| `pnpm run demo` | Run the built Ollama demo host. |
| `pnpm run typecheck` | Run strict TypeScript checks without emitting files. |
| `pnpm run lint` | Run oxlint. |
| `pnpm run format` | Format files with oxfmt. |
| `pnpm run format:check` | Check formatting without changing files. |
| `pnpm run test` | Run unit tests with Vitest and coverage. |
| `pnpm run integration-test` | Run the integration-test command. |
| `pnpm run ci` | Run typecheck, lint, formatting, build, unit tests, and integration tests. |

## Safety Notes

- `run_sql` rejects non-`SELECT` and non-`WITH` statements unless the server was launched with `--allow-write` and the caller explicitly supplies `allowWrite: true`. This guard is not a complete SQL-injection defense.
- Use bind parameters for user-supplied literal values.
- Schema discovery scopes introspection to one owner and one detailed table response at a time. Do not reintroduce a whole-schema metadata tool.
- Write statements commit automatically when they affect rows. Only trusted hosts should enable writes.