Database-MCP-Server

# FireDAC MCP Server Turn **any database FireDAC can reach** into an MCP server that an AI assistant can query and (optionally) modify — driven by a single **connection string** and a chosen **access mode**. You give the server one FireDAC connection string plus a mode (`readonly` / `readwrite` / `full`). It opens the connection, owns it for its whole lifetime, and mediates every call from the AI client — enforcing the access mode and a SQL-safety classifier on the way in, and marshalling result sets to JSON on the way out. Because it is driven entirely by the connection string and by FireDAC's *engine-agnostic* metadata API, the **same binary** works with: | Engine | Example `DriverID` | |---|---| | SQLite | `SQLite` | | PostgreSQL | `PG` | | MySQL / MariaDB | `MySQL` | | SQL Server | `MSSQL` | | Oracle | `Ora` | | Firebird / InterBase | `FB` / `IB` | | Anything with an ODBC driver | `ODBC` | The physical driver-link units are linked in by `FireDACMCPServer.dpr`; add or remove them there to control which engines a build supports. --- ## Files | File | Purpose | |---|---| | `FireDACMCPServer.dpr` | Entry point: command-line parsing, driver links, server wiring. | | `DBConnectionManager.pas` | The engine-agnostic core: connection handling, SQL classifier, access-mode enforcement, parameter binding, result marshalling, tool registration. | | `FireDACMCPServer.dproj` | Delphi project (Win32 console). | ## Building Open `FireDACMCPServer.dproj` in the IDE and build (Shift+F9), **or** from a command prompt: ``` "C:\Program Files (x86)\Embarcadero\Studio\37.0\bin\rsvars.bat" msbuild FireDACMCPServer.dproj /t:Build /p:Config=Debug /p:Platform=Win32 ``` The executable lands in `Win32\Debug\FireDACMCPServer.exe`. --- ## Usage ``` FireDACMCPServer [options] ``` | Option | Description | |---|---| | `-conn="<connection string>"` | The FireDAC connection string. Required unless `-conn-env` is used. | | `-conn-env=<ENV_VAR>` | Read the connection string from an environment variable instead (keeps passwords off the command line and out of client config files). | | `-mode=<readonly\|readwrite\|full>` | Access level. Default: **readonly**. | | `-maxrows=<n>` | Hard cap on rows returned by `run_query` (default 1000). | | `-timeout=<ms>` | Per-command execution timeout in milliseconds. | | `-allow-multi` | Permit several `;`-separated statements in a single call (off by default). | | `-help` | Show help and exit. | ### Access modes | Mode | What the AI can do | Tools exposed | |---|---|---| | `readonly` *(default)* | `SELECT` and metadata only. **No mutation tool is registered at all** — the AI has no way to change anything. | `db_info`, `list_tables`, `describe_table`, `run_query` | | `readwrite` | The above **+** `INSERT` / `UPDATE` / `DELETE` / `MERGE` (DML). | above **+** `run_statement` | | `full` | The above **+** DDL (`CREATE` / `ALTER` / `DROP` / `TRUNCATE`) and otherwise-blocked statements (`PRAGMA`, `ATTACH`, …). | above (`run_statement` also accepts DDL) | --- ## Examples **SQLite, read-only (safe default):** ``` FireDACMCPServer -conn="DriverID=SQLite;Database=C:\data\app.db" ``` **PostgreSQL, read/write, password from an environment variable:** ``` set PG_CONN=DriverID=PG;Server=localhost;Database=sales;User_Name=app;Password=secret FireDACMCPServer -conn-env=PG_CONN -mode=readwrite ``` **SQL Server, read-only, Windows auth:** ``` FireDACMCPServer -conn="DriverID=MSSQL;Server=localhost;Database=Northwind;OSAuthent=Yes;ODBCAdvanced=TrustServerCertificate=yes" ``` > **SQL Server + ODBC Driver 18 note:** Driver 18 encrypts by default and > validates the server certificate, so against a local instance with a > self-signed cert you will see *"SSL Provider: The certificate chain was > issued by an authority that is not trusted."* Pass the certificate through > with `ODBCAdvanced=TrustServerCertificate=yes` (keeps encryption on) or > disable encryption with `Encrypt=No`. FireDAC's own `TrustServerCertificate` > parameter is **not** forwarded to the ODBC driver — use `ODBCAdvanced`. --- ## Registering with an MCP client (stdio) Add an entry to your client config (`claude_desktop_config.json`, `.mcp.json`, …): ```json { "mcpServers": { "database": { "command": "C:\\path\\to\\FireDACMCPServer.exe", "args": [ "-conn-env=APP_DB_CONN", "-mode=readonly" ], "env": { "APP_DB_CONN": "DriverID=SQLite;Database=C:\\data\\app.db" } } } } ``` You can also point [MCP Inspector](https://github.com/modelcontextprotocol/inspector) at the executable: ``` npx @modelcontextprotocol/inspector FireDACMCPServer.exe -conn="DriverID=SQLite;Database=demo.db" -mode=full ``` --- ## Tools ### `db_info` *(read-only, idempotent)* Returns the driver id, access mode, row limits and the **password-redacted** connection string. ### `list_tables` *(read-only)* Lists tables and views on any engine (via FireDAC metadata, not engine-specific SQL). Optional `schema` argument filters by schema. ### `describe_table` *(read-only)* Returns each column's `name`, `type`, `size`, `nullable` and `readOnly` flags. - `table` *(required)* — table name, optionally schema-qualified. ### `run_query` *(read-only)* Executes a read-only statement (`SELECT` / `WITH` / `EXPLAIN` / `SHOW`). - `sql` *(required)* — use `:name` placeholders for values. - `params` *(optional)* — JSON object binding those placeholders, e.g. `{"id": 42}`. - `max_rows` *(optional)* — capped by the server's `-maxrows`. Returns: ```json { "columns": [ { "name": "id", "type": "ftInteger" }, ... ], "rows": [ { "id": 1, "name": "Alice", "age": 30 }, ... ], "rowCount": 1, "truncated": false } ``` ### `run_statement` *(registered only in read-write / full modes; destructive)* Executes a data- or schema-changing statement. - `sql` *(required)* — `INSERT` / `UPDATE` / `DELETE` / `MERGE` (read-write) or also DDL (full). Use `:name` placeholders. - `params` *(optional)* — JSON object binding those placeholders. Returns `{ "success": true, "category": "dml" | "ddl", "rowsAffected": N }`. ### `connect` *(only in deferred mode — see below)* Opens the database connection when none was configured at startup. - `connection_string` *(optional)* — a full FireDAC connection string. If given, the discrete parameters below are ignored. - `driver_id`, `server`, `database`, `user`, `password` *(optional)* — used to assemble a connection string when `connection_string` is omitted. An empty `user` means OS/Windows authentication. For `MSSQL`, `ODBCAdvanced=TrustServerCertificate=yes` is added automatically. - `options` *(optional)* — extra `Key=Value;...` pairs to append. - `mode` *(optional)* — `readonly` | `readwrite` | `full` (default `readonly`). Returns `{ "connected": true, "driverId": "...", "accessMode": "..." }`. --- ## Deferred connection (requesting the connection at runtime) If you start the server **without** `-conn`/`-conn-env`, it does not fail — it comes up in *deferred mode*: - It exposes the `connect` tool plus the normal database tools. - Until `connect` succeeds, every database tool returns a clear *"No database connection yet. Call the connect tool first."* message. - The AI (prompted by the user) calls `connect` with a connection string — or the individual driver/server/database/user/password parameters and a mode — and from then on the database tools operate against that connection. This lets a single registered server ask for its target database at runtime instead of baking it into the client config. Example client entry: ```json { "mcpServers": { "database-on-demand": { "command": "C:\\path\\to\\FireDACMCPServer.exe", "args": ["-maxrows=500"] } } } ``` Then, in the client: *"Connect to the MCPSandbox database on localhost in read-write mode"* → the AI calls `connect` and starts working. > **Why a `connect` tool and not server-initiated elicitation?** The MCP spec > has an `elicitation/create` request a server can use to pop a form on the > client. That requires a **bidirectional** transport; the stdio transport used > by Claude Desktop is strictly request→response and cannot deliver a > server-initiated request (or a `tools/list_changed` notification). A `connect` > tool is therefore the robust, universally-supported way to request the > connection at runtime. On a bidirectional transport you could drive the same > flow through `TTMSMCPServer.RequestElicitation` instead. --- ## Robustness & safety This demo is built to be a *safe* bridge, not just a working one: - **Capability gating by construction.** In read-only mode the mutation tool is never registered, so the model cannot even attempt a write. - **SQL classifier.** Every statement is classified *after* comments and string literals are stripped, so keyword/semicolon scanning cannot be fooled by text inside quotes or comments. `run_query` accepts only reads; `run_statement` enforces exactly what the current mode allows. - **No statement stacking.** `SELECT 1; DROP TABLE users` is rejected unless `-allow-multi` is set — this blocks a classic piggy-backed injection. - **Escape statements blocked.** `ATTACH` / `DETACH` / `PRAGMA`-writes / `VACUUM` / `GRANT` / `EXEC` … are refused outside `full` mode. - **Parameterised values.** Callers bind literals through `params`; values are never string-concatenated into SQL. - **Bounded output.** Result sets are capped (`max_rows`) and report `truncated`; large BLOBs are base64-encoded up to a cap, then summarised. - **Secret hygiene.** The connection string is only ever surfaced with its password redacted, and `-conn-env` keeps it out of process arguments entirely. - **Resilient connection.** `AutoReconnect` recovers from dropped connections; in read-only mode datasets are also opened read-only as defence in depth. ### Recommendations for production use - Prefer a **dedicated database user** whose grants match the chosen mode (a read-only DB login behind `readonly` mode is belt-and-braces). - Pass the connection string via `-conn-env`, never on the command line. - Keep `readonly` as the default and only raise the mode deliberately. --- ## Extending - **More engines:** add the matching `FireDAC.Phys.*` unit pair to the `uses` clause in `FireDACMCPServer.dpr`. - **More tools:** add a method to `TDBConnectionManager` and register it in `RegisterTools`. Follow the existing pattern — classify any SQL through `GuardStatement`, and marshal results with `DatasetToJSON`. - **Transactions / stored procedures / schema resources** are natural next steps on top of this foundation. This demo is part of the TMS MCP SDK and is subject to the TMS Software license agreement.