{
  "markdown": "# @shahid.la/cds-db-nlquery-mcp\n\nMCP server for natural language queries against **CDS db-layer entities** (`db/schema.cds`).\n\nAsk questions about your database in plain English. The server discovers your schema automatically, generates real SQL JOINs using CDS, and returns rows. No hardcoded queries, no SQL, no schema configuration. Bring your own LLM: Anthropic or OpenAI, or any OpenAI-compatible endpoint (Azure OpenAI, Groq, Ollama, local models, etc.).\n\n> **Targets the `db/` layer, not OData services.** If your entities are exposed as OData services, use an MCP package that targets the service layer instead.\n\n**Read-only.** This package only executes SELECT queries. No INSERT, UPDATE, or DELETE.\n\n---\n\n## Prerequisites\n\n- A CAP project with `@sap/cds >= 7`\n- A configured CDS database (HANA Cloud, SQLite, PostgreSQL)\n- An MCP client (Claude Code, Claude Desktop, or any MCP-compatible host)\n- An API key for an LLM provider (see [LLM provider](#llm-provider) below), used to translate your question into a query, not to answer it\n\n---\n\n## Quick start\n\n**1. Install in your CAP project**\n\n```bash\nnpm install @shahid.la/cds-db-nlquery-mcp\n```\n\n**2. Create `.mcp.json` in your CAP project root** (same folder as `package.json`)\n\n```json\n{\n  \"mcpServers\": {\n    \"cds-db-nlquery\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@shahid.la/cds-db-nlquery-mcp\"],\n      \"cwd\": \"/absolute/path/to/your/cap/project\",\n      \"env\": {\n        \"ANTHROPIC_API_KEY\": \"sk-ant-...\"\n      }\n    }\n  }\n}\n```\n\nSet `cwd` to the absolute path of your CAP project root, where `db/schema.cds` lives. See [LLM provider](#llm-provider) for using OpenAI, Gemini, or another provider instead.\n\n`npx -y` re-resolves the package on every server start (from npm's cache once\ndownloaded, not a fresh network fetch each time, but still an extra resolution\nstep). If you've run `npm install @shahid.la/cds-db-nlquery-mcp` already, you\ncan point `\"command\"` at the installed binary directly instead,\n`node_modules/.bin/cds-db-nlquery-mcp`, for a slightly more predictable startup,\nespecially in production.\n\n**3. Open your project in Claude Code and ask a question**\n\n```\nWhich customers have a DTI ratio above 5?\nShow me all open payments overdue by more than 30 days, include the borrower name.\nList all loans in the MINING sector with the customer's current DTI ratio.\n```\n\n**Example response:** the server tells the client to render rows as a vertical\nfield/value list, not a markdown table (real output, verified against a live\ndeployment):\n\n```\nFound 3 customers with DTI above 5:\n\nPARTNER    : 30100003\nDTI_RATIO  : 7.20\nBU_SORT1   : Domestic Customer AU 3\n\nPARTNER    : 30100001\nDTI_RATIO  : 5.80\nBU_SORT1   : Domestic Customer AU 1\n\nPARTNER    : 30100002\nDTI_RATIO  : 5.40\nBU_SORT1   : Domestic Customer AU 2\n```\n\n---\n\n## How it works\n\nWhen you ask a question:\n\n1. Your MCP client (e.g. Claude Code) calls the `natural_language_query` tool with your question.\n2. The MCP server has already loaded your CDS schema at startup: entity names, columns, and associations. It sends this schema plus your question to the LLM provider you configured (Anthropic, OpenAI, or any OpenAI-compatible endpoint), which translates it into a structured query descriptor.\n3. The server executes a single CDS query. CDS association paths (`customer.BU_SORT1`) generate real SQL JOINs, executed by your database, not by JavaScript. Scales to production data volumes.\n4. Results come back to your MCP client, which formats and presents the answer.\n\nThe LLM call in step 2 is a small, cheap planning step (translating your question into JSON). It does not need a large or expensive model. A fast/cheap tier model is recommended.\n\n---\n\n## Configuration\n\nAll configuration is via environment variables in the `.mcp.json` `env` block.\n\n```json\n{\n  \"mcpServers\": {\n    \"cds-db-nlquery\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@shahid.la/cds-db-nlquery-mcp\"],\n      \"cwd\": \"/path/to/your/cap/project\",\n      \"env\": {\n        \"ANTHROPIC_API_KEY\": \"sk-ant-...\",\n        \"MCP_ALLOWED_ENTITIES\": \"Customers,Orders,Products\",\n        \"MCP_BLOCKED_COLUMNS\": \"PASSWORD,EMBEDDING,SSN\",\n        \"MCP_MAX_ROWS\": \"100\",\n        \"MCP_MODEL_PATH\": \"db\"\n      }\n    }\n  }\n}\n```\n\n| Variable | Default | Description |\n|---|---|---|\n| `MCP_ALLOWED_ENTITIES` | *(all entities)* | Comma-separated list of entity **short names**, the name after the last dot in the FQN (e.g. `Customers`, not `my.app.Customers`). Leave unset during development; always set for production. |\n| `MCP_BLOCKED_COLUMNS` | *(none)* | Comma-separated column names to exclude from all results. Stripped before the query runs. Useful for columns like `EMBEDDING`, `PASSWORD`, `SSN`. |\n| `MCP_MAX_ROWS` | `500` | Maximum rows per query. Enforced as a SQL `LIMIT`, not a post-fetch filter. |\n| `MCP_MODEL_PATH` | `db` | Path to your CDS model folder or file, relative to `cwd`. Change if your schema is at `model/`, `srv/`, etc. |\n| `MCP_DB_USER` / `MCP_DB_PASSWORD` | *(consumer app's own DB user)* | Connect with a different HANA user than your app's runtime user. Host/port/schema are reused, only credentials are overridden. See [Security → Production](#production) for why this matters. |\n\n### LLM provider\n\nA small LLM call translates your question into a query descriptor. **Bring your own provider**, set ONE of the following in the `env` block:\n\n**Anthropic (Claude):**\n```json\n\"env\": {\n  \"ANTHROPIC_API_KEY\": \"sk-ant-...\",\n  \"ANTHROPIC_MODEL\": \"claude-haiku-4-5-20251001\"\n}\n```\n\n**OpenAI, or any OpenAI-compatible provider:**\n```json\n\"env\": {\n  \"OPENAI_API_KEY\": \"sk-...\",\n  \"OPENAI_MODEL\": \"gpt-4o-mini\"\n}\n```\n\n`OPENAI_MODEL` accepts any model name. Use whatever your provider expects. Set `OPENAI_BASE_URL` to point at a different OpenAI-compatible endpoint:\n\n| Provider | `OPENAI_BASE_URL` | Example `OPENAI_MODEL` |\n|---|---|---|\n| OpenAI | *(omit, uses default)* | `gpt-4o-mini` |\n| Google Gemini | `https://generativelanguage.googleapis.com/v1beta/openai/` | `gemini-2.0-flash` |\n| Groq | `https://api.groq.com/openai/v1` | `llama-3.1-8b-instant` |\n| Mistral | `https://api.mistral.ai/v1` | `mistral-small-latest` |\n| DeepSeek | `https://api.deepseek.com` | `deepseek-chat` |\n| xAI (Grok) | `https://api.x.ai/v1` | `grok-2-latest` |\n| Azure OpenAI | your Azure endpoint | your deployment name |\n| Ollama (local) | `http://localhost:11434/v1` | `llama3.1` (API key can be any non-empty string) |\n\n| Variable | Description |\n|---|---|\n| `LLM_PROVIDER` | `\"anthropic\"` or `\"openai\"`. Auto-detected from whichever API key is set, only needed if both are set and you want to force one. |\n| `ANTHROPIC_API_KEY` / `ANTHROPIC_MODEL` | Anthropic native API |\n| `OPENAI_API_KEY` / `OPENAI_MODEL` / `OPENAI_BASE_URL` | OpenAI or any OpenAI-compatible endpoint |\n\nThis is a planning-only call (question to JSON descriptor). A fast/cheap tier model is sufficient and recommended.\n\n---\n\n## Security\n\n### Development\n\nThe server uses your project's existing database connection, whatever CDS has configured in `default-env.json` or your service binding. No extra setup needed.\n\n### Production\n\n**Important:** this package queries the database directly via `cds.run()`. It does **not** go through the CAP service layer, so CAP `@requires` and `@restrict` annotations are **not enforced**. Access control is entirely your responsibility at the database and configuration level.\n\n**Step 1: Create a dedicated read-only database user**\n\n```sql\n-- Run as DBADMIN in SAP HANA Cloud Central\nCREATE USER MCP_READER PASSWORD 'your-password';\nGRANT SELECT ON SCHEMA YOUR_HDI_SCHEMA TO MCP_READER;\n\n-- Revoke tables you do not want queryable\nREVOKE SELECT ON \"YOUR_HDI_SCHEMA\".\"your.AuditLog\" FROM MCP_READER;\nREVOKE SELECT ON \"YOUR_HDI_SCHEMA\".\"your.RegulatoryDocuments\" FROM MCP_READER;\n```\n\nThis user can only read. No write access. HANA itself enforces it, independently of this package.\n\n**Step 2: Point the server at it with `MCP_DB_USER`/`MCP_DB_PASSWORD`**\n\nThe server connects with these credentials instead of inheriting your app's own\ndatabase connection. Host, port, and schema are reused automatically, only the\nuser/password are overridden:\n\n```json\n\"env\": {\n  \"MCP_DB_USER\": \"MCP_READER\",\n  \"MCP_DB_PASSWORD\": \"your-password\"\n}\n```\n\nIf you keep these in a separate gitignored file instead of directly in `.mcp.json`,\nthat's fine too. The server just reads `process.env`, same as any other variable here.\n\n**Step 3: Set `MCP_ALLOWED_ENTITIES`**\n\n```json\n\"env\": {\n  \"MCP_ALLOWED_ENTITIES\": \"Customers,Orders,Products\"\n}\n```\n\nThis is enforced on the entity you query directly **and** on any entity reached via\nan association join in `select`/`where`. For example, if `Customers` isn't in the allowlist,\na query against `Orders` can't read `Customers` data through a `customer.NAME` join\npath either. The database user restricts access at the HANA level;\n`MCP_ALLOWED_ENTITIES` adds a second layer at the application level. Use both.\n\n---\n\n## Joins\n\nThe server reads CDS associations from your schema. When the LLM references `customer.BU_SORT1` in a query, CDS generates a real SQL JOIN, executed by the database.\n\n**Multiple associations in one query work:**\n\n> *\"Show loans in the MINING sector with borrower name and current DTI\"*\n\nGenerates a single SQL statement joining `Loans → BusinessPartners → BCA_DTI` in one database round-trip.\n\n**One constraint:** do not select the same column name from two different entities in the same query (e.g. `LOAN_ID` from both the main entity and a joined entity). The database rejects duplicate column names. Claude is instructed to avoid this, but worth being aware of.\n\n### Comparing two columns to each other\n\nMost filters compare a column to a fixed value (`DTI_RATIO > 5`). For questions like *\"which loans have collateral worth less than the loan amount\"*, the comparison is between two columns instead. Use `valCol` in place of `val`:\n\n```json\n{ \"col\": \"collateral.VALUE\", \"op\": \"<\", \"valCol\": \"AMOUNT\" }\n```\n\nEither side can be an association path. This is handled by the same join mechanism as everything else. No separate query, no JavaScript-side comparison.\n\n### Filtering by a coded value's human meaning\n\nIf a column has a `@Common.Text` value-help association (see [Coded values](#coded-values--what-does-status--c-mean)), filter via the text field directly rather than guessing the underlying code:\n\n```json\n{ \"col\": \"status.TEXT\", \"op\": \"like\", \"val\": \"Active\" }\n```\n\n`like` is case-insensitive (enforced via `UPPER()` on both sides, not relying on database collation), so the exact case of the value doesn't need to match.\n\n---\n\n## Column and entity labels\n\nThe LLM only sees what's in your CDS model: column names, types, and any labels you've annotated. Plain code comments (`// customer name`) are invisible to it. Without a label, an ambiguous column name can cause the LLM to guess wrong, regardless of how capable the model is.\n\n### `@title`: reused automatically, zero extra work\n\nIf your schema already has SAP's standard `@title` annotation (common in projects with a Fiori UI or OData service), the server picks it up automatically. You don't need to do anything:\n\n```cds\nentity Customers {\n  key PARTNER  : String(10);\n  @title: 'Customer Name'\n  BU_SORT1     : String(40);\n}\n```\n\n### `@NLP.label`: for disambiguation `@title` isn't meant for\n\nUse this when you need to tell the LLM something a UI-facing label shouldn't say, e.g. \"don't use this column for X.\" This was a real bug we hit: a `BU_TYPE` code column (`1`=person, `2`=organisation) was being picked by the LLM whenever a question asked for a customer's \"name,\" because nothing told it otherwise. The fix:\n\n```cds\nentity Customers {\n  key PARTNER  : String(10);\n  @NLP.label: 'Partner type code: 1=person, 2=organisation. NOT a name, never use for name lookups'\n  BU_TYPE      : String(2);\n  @NLP.label: 'Customer / business partner full name, use this whenever a question asks for a name'\n  BU_SORT1     : String(40);\n}\n```\n\nAfter adding these two labels, even the cheapest tier model (`claude-haiku-4-5`) picked the right column every time. **Fix ambiguity at the schema level, not by tweaking prompts per-bug.** It's permanent and works regardless of which LLM provider or model you use.\n\n**Precedence:** `@NLP.label` is checked first, falls back to `@title`, falls back to the raw column/entity name if neither is set.\n\n### Other `@NLP` annotations\n\n```cds\nentity Customers @(NLP.label: 'Active borrowers, loan customers with income and sector data') {\n  dti : Association to BCA_DTI on dti.PARTNER = PARTNER\n        @NLP.joinType: 'LEFT';\n}\n```\n\n| Annotation | Where | Effect |\n|---|---|---|\n| `@NLP.label` | Entity or column | Description shown to the LLM. Falls back to `@title`, then the name. |\n| `@NLP.joinType` | Association | Override join type: `'LEFT'` or `'INNER'`. Auto-detected from cardinality if not set. |\n| `@NLP.alias` | Association | Override the association name used in queries |\n\nAll optional. The package works without any of them. But for any column whose name alone could be misread (codes, abbreviations, anything that looks like one thing but means another), a label is the difference between the LLM guessing and the LLM knowing.\n\n---\n\n## Coded values: what does `STATUS = 'C'` mean?\n\nLabels solve \"what does this column mean.\" A separate problem: what does a *coded\nvalue* in that column mean? `STATUS = 'C'`. Closed? Cancelled? Confirmed?\n\nThere are two ways to tell the LLM, and **which one to use depends on whether the\nvalue list can change without a code deploy.**\n\n### `@Common.Text`: for business-configurable codes (the common case)\n\nMost status/type codes are business data. Someone in operations might introduce a\nnew value next quarter, and that should never require touching the schema file. The\nSAP-standard mechanism for this is `@Common.Text`, pointing through an association to\na small lookup/check table, the same pattern Fiori elements uses for value-help\ndropdowns:\n\n```cds\nentity LoanStatusCodes {\n  key CODE : String(1);\n  TEXT     : String(20);\n}\n\nentity Loans {\n  @Common.Text: status.TEXT\n  STATUS : String(1);\n  status : Association to LoanStatusCodes on status.CODE = STATUS;\n}\n```\n\nSeed it with `{CODE: 'A', TEXT: 'Active'}`, `{CODE: 'C', TEXT: 'Closed'}`. Adding a\nnew status later is an `INSERT` into `LoanStatusCodes`, no schema change, no\nredeploy. The server detects `@Common.Text`, tells the LLM a readable value is\navailable via the association path, and the LLM includes it in `select` using the\nnormal join mechanism. No extra code on our side, it's the same association-path\nJOIN used everywhere else in this package.\n\n### CDS `enum`: only for sets that are genuinely fixed forever\n\n```cds\nSTATUS : String(1) enum { active = 'A'; closed = 'C'; };\n```\n\nThis is compile-time: adding a value means editing `schema.cds` and redeploying.\nAppropriate only for values that are tied to actual program logic anyway (so a code\nchange would be required regardless), not for business classifications. We initially\nused `enum` for our own demo's status fields and walked it back to `@Common.Text` for\nexactly this reason. It's documented here as a contrast, not a recommendation.\n\n**Rule of thumb: reach for `@Common.Text` by default. Use `enum` only when you're sure\nthe list can never grow without code changing anyway.**\n\nWhen a column has `@Common.Text`, query results get the raw code back as normal.\nPresentation (showing \"Closed\" instead of \"C\") is up to whichever LLM renders the\nfinal answer, using the readable value it fetched via the join.\n\n---\n\n## Startup log\n\nWhen the server starts, check the output panel in Claude Code:\n\n```\n[cds-db-nlquery-mcp] Schema loaded — 12 entities\n[cds-db-nlquery-mcp] WARNING: MCP_ALLOWED_ENTITIES not set — all entities are queryable. Set in .mcp.json for production.\n[cds-db-nlquery-mcp] WARNING: MCP_DB_USER not set — using the project's default DB connection (likely the main app's full-access user). Set MCP_DB_USER/MCP_DB_PASSWORD to a restricted read-only user for production use.\n[cds-db-nlquery-mcp] LLM provider: anthropic\n[cds-db-nlquery-mcp] Ready (stdio)\n```\n\n**`0 entities` at startup?** Check that `cwd` in `.mcp.json` points to your CAP project root and that `MCP_MODEL_PATH` matches your schema folder name.\n\n**`WARNING: No LLM provider configured`?** Set `ANTHROPIC_API_KEY` or `OPENAI_API_KEY` in the `.mcp.json` `env` block, see [LLM provider](#llm-provider).\n\n**`WARNING: MCP_DB_USER not set`?** Expected during development, the server is using the same database connection as your CAP app. Before production use, see [Security → Production](#production) for creating a dedicated read-only user.\n\n---\n\n## Testing this against your own deployment\n\n`npm install` gives you `src/`, the MCP server, and nothing else. That's the\nentire published package. The GitHub repository also has an\n`examples/capability-demo/` folder with a real CDS schema, seed data, ~35\nexample queries with verified expected results, and four scripts to actually\nexercise them against your own database (not just read about it): one runs the\nexample queries directly against the internal functions, one lets you ask your\nown question end-to-end through a real LLM, one runs every example question's\nnatural-language text through a real LLM and checks the result, and one spawns\n`src/mcp-server.js` itself as a real child process and drives it through the\nactual MCP stdio protocol, the same way Claude Code or Claude Desktop would,\nrather than calling internal functions directly. None of this ships with\n`npm install`. Clone the repo if you want it. See that folder's own README for details.\n\n---\n\n## How releases are tested\n\n`npm test` (126 unit tests) runs against a mocked database. Fast, but it can't\ncatch backend-specific behavior (a real HANA deployment has rejected things the\nmocks happily accepted, more than once). Before tagging a release, `npm run\ntest:deployment` is run against a real CAP project connected to live HANA\nCloud. It requires real credentials and a deployed `examples/capability-demo/`\nschema, so it isn't part of `npm test` or CI, but it is a required manual step,\nnot an optional one. `examples/capability-demo/smoke-test-server.js` is also\nrun. It spawns `src/mcp-server.js` itself and drives it over the real MCP\nstdio protocol with a real LLM call, not just the internal functions directly.\n\n[RELEASE_VERIFICATION.md](./RELEASE_VERIFICATION.md) is the actual, append-only\nrecord of this: real output from a real run against live BTP HANA, captured\nand committed before each release, not just claimed.\n\n---\n\n## License\n\nMIT\n",
  "bytes": 18609,
  "sha": "25b34f9206e553543d3ae799bf6806f40468a5fcfa0770df04c72796fa10f11f",
  "repo_slug": "shahidla/cds-db-nlquery-mcp",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_shahidla_cds_db_nlquery_mcp_d79438e8/readme"
}