> ## Documentation Index > Fetch the complete documentation index at: https://docs.mka1.com/llms.txt > Use this file to discover all available pages before exploring further. # Connect a PostgreSQL database with MCP > Put an MCP server in front of your PostgreSQL database and let a response or an agent answer from live tables. Use MCP when you want the MKA1 API to answer from a PostgreSQL database you already run. The model queries the database when a question needs it, so answers use current data and PostgreSQL computes the filters, joins and totals. You do three things: 1. Create a read-only database role for the model. 2. Run an MCP server in front of the database. 3. Register the server on the MKA1 API and reference it from a response or an agent. API Reference: * [MCP Vault server endpoints](https://docs.mka1.com/api-reference/mcp-vault/create-mcp-server.md) * [MCP credential endpoints](https://docs.mka1.com/api-reference/mcp-vault/create-mcp-credential.md) ## 1. Create a read-only database role The database role decides what the model can read. Grant it `SELECT` on the schemas the model may query, and nothing else. ```sql SQL theme={null} CREATE ROLE mcp_reader LOGIN PASSWORD ''; GRANT USAGE ON SCHEMA TO mcp_reader; GRANT SELECT ON ALL TABLES IN SCHEMA TO mcp_reader; ALTER DEFAULT PRIVILEGES IN SCHEMA GRANT SELECT ON TABLES TO mcp_reader; ``` Use views to expose some columns of a table and not others. ## 2. Run the MCP server The MCP server sits between the MKA1 API and your database. It connects with the read-only role and offers the model three tools: | Tool | What it does | | - | - | | `list_tables` | Lists the tables and views the role can read. | | `describe_table` | Returns a table's columns and their types. | | `query` | Runs one `SELECT` statement and returns its rows with typed columns. | You can run any MCP server that speaks Streamable HTTP. This one is a complete example in a single file. It needs Node.js 20 or later. > [accordion] package.json > > ```json package.json theme={null} > { > "name": "postgres-mcp-server-example", > "private": true, > "type": "module", > "description": "A read-only PostgreSQL MCP server for the MKA1 API docs recipe.", > "scripts": { > "start": "node server.mjs", > "test": "node --test" > }, > "engines": { > "node": ">=20" > }, > "dependencies": { > "@modelcontextprotocol/sdk": "1.29.0", > "pg": "8.16.3", > "zod": "4.4.3" > } > } > ``` > [accordion] server.mjs > > ```js server.mjs theme={null} > // A read-only PostgreSQL MCP server. > // > // Exposes three tools over Streamable HTTP at /mcp: > // list_tables the tables the database role can read > // describe_table a table's columns and types > // query one read-only SQL statement, with typed columns > // > // Configuration (environment): > // DATABASE_URL connection string of a read-only database role (required) > // MCP_AUTH_TOKEN bearer token callers must present (required) > // PORT port to listen on (default 3000) > // MAX_ROWS most rows a query returns (default 200) > // QUERY_TIMEOUT_MS statement timeout per query (default 10000) > > import { createServer } from "node:http"; > import { timingSafeEqual } from "node:crypto"; > import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js"; > import { StreamableHTTPServerTransport } from "@modelcontextprotocol/sdk/server/streamableHttp.js"; > import pg from "pg"; > import { z } from "zod"; > > const DATABASE_URL = requiredEnv("DATABASE_URL"); > const MCP_AUTH_TOKEN = requiredEnv("MCP_AUTH_TOKEN"); > const PORT = Number(process.env.PORT ?? 3000); > const MAX_ROWS = Number(process.env.MAX_ROWS ?? 200); > const QUERY_TIMEOUT_MS = Number(process.env.QUERY_TIMEOUT_MS ?? 10_000); > const MAX_BODY_BYTES = 1_000_000; > > function requiredEnv(name) { > const value = process.env[name]; > if (!value) { > console.error(`${name} is required`); > process.exit(1); > } > return value; > } > > const pool = new pg.Pool({ connectionString: DATABASE_URL, max: 5 }); > > // Type names for the columns of a result (PostgreSQL reports types as OIDs). > const typeNames = new Map(); > async function typeName(client, oid) { > if (!typeNames.has(oid)) { > const { rows } = await client.query("SELECT format_type($1::oid, NULL) AS name", [oid]); > typeNames.set(oid, rows[0].name); > } > return typeNames.get(oid); > } > > // Every statement runs in a READ ONLY transaction with a time limit, and is > // rolled back. The database role is the real boundary: grant it SELECT on > // what the assistant may read and nothing else. > async function readOnly(run) { > const client = await pool.connect(); > try { > await client.query("BEGIN TRANSACTION READ ONLY"); > await client.query(`SET LOCAL statement_timeout = ${QUERY_TIMEOUT_MS}`); > return await run(client); > } finally { > await client.query("ROLLBACK").catch(() => {}); > client.release(); > } > } > > const json = (value) => ({ content: [{ type: "text", text: JSON.stringify(value) }] }); > const failure = (message) => ({ isError: true, content: [{ type: "text", text: message }] }); > > function buildServer() { > const server = new McpServer({ name: "postgres-read-only", version: "1.0.0" }); > > server.registerTool( > "list_tables", > { > description: "List the tables and views this connection can read, with their schema.", > inputSchema: {}, > }, > async () => { > const { rows } = await readOnly((client) => > client.query( > `SELECT table_schema AS schema, table_name AS name, table_type AS type > FROM information_schema.tables > WHERE table_schema NOT IN ('pg_catalog', 'information_schema') > ORDER BY table_schema, table_name` > ) > ); > return json({ tables: rows }); > } > ); > > server.registerTool( > "describe_table", > { > description: "Describe one table: its columns, their types and whether they can be null.", > inputSchema: { > schema: z.string().describe("Schema name, for example public"), > table: z.string().describe("Table name"), > }, > }, > async ({ schema, table }) => { > const { rows } = await readOnly((client) => > client.query( > `SELECT column_name AS name, data_type AS type, is_nullable = 'YES' AS nullable > FROM information_schema.columns > WHERE table_schema = $1 AND table_name = $2 > ORDER BY ordinal_position`, > [schema, table] > ) > ); > if (rows.length === 0) return failure(`No readable table ${schema}.${table}`); > return json({ schema, table, columns: rows }); > } > ); > > server.registerTool( > "query", > { > description: > "Run one read-only SQL statement (SELECT) and return its rows with typed columns. " + > `At most ${MAX_ROWS} rows are returned; "truncated" says whether more matched. ` + > "Call list_tables and describe_table first to learn the schema.", > inputSchema: { > sql: z.string().describe("A single SELECT statement"), > }, > }, > async ({ sql }) => { > try { > return await readOnly(async (client) => { > // A cursor fetches only what is returned, however many rows match. > // DECLARE accepts a SELECT, so a write fails here. The extended > // protocol accepts one statement, so "SELECT 1; COMMIT; ..." cannot > // end the read-only transaction and run something after it. > await client.query({ text: `DECLARE mcp_result NO SCROLL CURSOR FOR ${sql}`, queryMode: "extended" }); > const result = await client.query(`FETCH ${MAX_ROWS + 1} FROM mcp_result`); > const columns = []; > for (const field of result.fields) { > columns.push({ name: field.name, type: await typeName(client, field.dataTypeID) }); > } > const truncated = result.rows.length > MAX_ROWS; > const rows = truncated ? result.rows.slice(0, MAX_ROWS) : result.rows; > return json({ columns, rows, row_count: rows.length, truncated }); > }); > } catch (error) { > // The model reads this and can correct its SQL. > return failure(`Query failed: ${error.message}`); > } > } > ); > > return server; > } > > function authorized(request) { > const expected = Buffer.from(`Bearer ${MCP_AUTH_TOKEN}`); > const given = Buffer.from(request.headers.authorization ?? ""); > return given.length === expected.length && timingSafeEqual(given, expected); > } > > async function readJson(request) { > const chunks = []; > let size = 0; > for await (const chunk of request) { > size += chunk.length; > if (size > MAX_BODY_BYTES) throw new Error("Request body too large"); > chunks.push(chunk); > } > return JSON.parse(Buffer.concat(chunks).toString("utf8")); > } > > function respond(response, status, body) { > response.writeHead(status, { "Content-Type": "application/json" }); > response.end(JSON.stringify(body)); > } > > const httpServer = createServer(async (request, response) => { > const { pathname } = new URL(request.url, "http://localhost"); > > if (pathname === "/healthz") return respond(response, 200, { status: "ok" }); > if (pathname !== "/mcp") return respond(response, 404, { error: "Not found" }); > if (!authorized(request)) return respond(response, 401, { error: "Unauthorized" }); > // Stateless: each POST is a complete exchange, so there is no session to > // resume with GET or end with DELETE. > if (request.method !== "POST") return respond(response, 405, { error: "Method not allowed" }); > > let body; > try { > body = await readJson(request); > } catch { > return respond(response, 400, { error: "Invalid JSON body" }); > } > > const server = buildServer(); > const transport = new StreamableHTTPServerTransport({ sessionIdGenerator: undefined }); > response.on("close", () => { > void transport.close(); > void server.close(); > }); > try { > await server.connect(transport); > await transport.handleRequest(request, response, body); > } catch (error) { > console.error("MCP request failed:", error); > if (!response.headersSent) respond(response, 500, { error: "Internal error" }); > } > }); > > httpServer.listen(PORT, () => { > console.log(`PostgreSQL MCP server listening on :${PORT}/mcp`); > }); > > for (const signal of ["SIGINT", "SIGTERM"]) { > process.on(signal, () => { > httpServer.close(() => void pool.end().then(() => process.exit(0))); > }); > } > ``` Save both files on a host that can reach the database, then start the server: ```bash Bash theme={null} npm install DATABASE_URL="postgres://mcp_reader:@:5432/" \ MCP_AUTH_TOKEN="" \ node server.mjs ``` `MCP_AUTH_TOKEN` is a secret you generate. The MKA1 API presents it on every call. The server also accepts `PORT` (default `3000`), `MAX_ROWS` (default `200`) and `QUERY_TIMEOUT_MS` (default `10000`). The server only reads: every statement runs in a read-only transaction, `query` accepts a single `SELECT`, and results are capped and timed. The MKA1 API calls your server over the internet, so publish it this way: * Serve it over HTTPS, behind your load balancer or reverse proxy. * Give it a hostname that resolves to a public address. The MKA1 API does not connect to private addresses. * Keep the path `/mcp`. ## 3. Register the server Register the server once in the MCP vault. ```ts TypeScript SDK theme={null} import { SDK } from '@meetkai/mka1'; const sdk = new SDK({ bearerAuth: 'Bearer ' }); const server = await sdk.llm.mcpVault.createServer({ createMcpServerRequest: { name: "Shop database", serverLabel: "shop_db", serverUrl: "https:///mcp", serverDescription: "Read-only access to the shop PostgreSQL database: customers and orders.", allowedTools: ["list_tables", "describe_table", "query"], requireApproval: "never", }, }); console.log(server.id); ``` ```python Python SDK theme={null} from meetkai_mka1 import SDK sdk = SDK(bearer_auth="Bearer ") server = sdk.llm.mcp_vault.create_server( name="Shop database", server_label="shop_db", server_url_="https:///mcp", server_description="Read-only access to the shop PostgreSQL database: customers and orders.", allowed_tools=["list_tables", "describe_table", "query"], require_approval="never", ) print(server.id) ``` ```csharp C# SDK theme={null} using MeetKai.MKA1; using MeetKai.MKA1.Types.Components; var sdk = new SDK(bearerAuth: "Bearer "); var server = await sdk.Llm.McpVault.CreateServerAsync(new CreateMcpServerRequest() { Name = "Shop database", ServerLabel = "shop_db", ServerUrl = "https:///mcp", ServerDescription = "Read-only access to the shop PostgreSQL database: customers and orders.", AllowedTools = MCPAllowedToolsUnion.CreateArrayOfStr( new List { "list_tables", "describe_table", "query" }), RequireApproval = MCPRequireApproval.Never, }); Console.WriteLine(server.McpServer?.Id); ``` ```bash CLI theme={null} mka1 llm mcp-vault create-server --body '{ "name": "Shop database", "server_label": "shop_db", "server_url": "https:///mcp", "server_description": "Read-only access to the shop PostgreSQL database: customers and orders.", "allowed_tools": ["list_tables", "describe_table", "query"], "require_approval": "never" }' ``` ```bash Bash theme={null} curl https://apigw.mka1.com/api/v1/llm/mcp/servers \ --request POST \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer ' \ --data '{ "name": "Shop database", "server_label": "shop_db", "server_url": "https:///mcp", "server_description": "Read-only access to the shop PostgreSQL database: customers and orders.", "allowed_tools": ["list_tables", "describe_table", "query"], "require_approval": "never" }' ``` The response includes the server ID, such as `mcp_srv_...`. Store the server's token as a credential. The vault encrypts it and never returns it. ```ts TypeScript SDK theme={null} const credential = await sdk.llm.mcpVault.createCredential({ serverId: "mcp_srv_123", createMcpCredentialRequest: { name: "Shop database token", authType: "bearer", bearerToken: "", }, }); console.log(credential.id); ``` ```python Python SDK theme={null} credential = sdk.llm.mcp_vault.create_credential( server_id="mcp_srv_123", name="Shop database token", auth_type="bearer", bearer_token="", ) print(credential.id) ``` ```csharp C# SDK theme={null} var credential = await sdk.Llm.McpVault.CreateCredentialAsync( serverId: "mcp_srv_123", body: new CreateMcpCredentialRequest() { Name = "Shop database token", AuthType = MCPAuthType.Bearer, BearerToken = "", }); Console.WriteLine(credential.McpCredential?.Id); ``` ```bash CLI theme={null} mka1 llm mcp-vault create-credential \ --server-id mcp_srv_123 \ --body '{ "name": "Shop database token", "auth_type": "bearer", "bearer_token": "" }' ``` ```bash Bash theme={null} curl https://apigw.mka1.com/api/v1/llm/mcp/servers/mcp_srv_123/credentials \ --request POST \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer ' \ --data '{ "name": "Shop database token", "auth_type": "bearer", "bearer_token": "" }' ``` The response includes the credential ID, such as `mcp_cred_...`. ## 4. Test the connection Ask the MKA1 API to connect to your server and discover its tools. ```ts TypeScript SDK theme={null} const test = await sdk.llm.mcpVault.testServer({ serverId: "mcp_srv_123", }); console.log(test.toolNames); ``` ```python Python SDK theme={null} test = sdk.llm.mcp_vault.test_server(server_id="mcp_srv_123") print(test.tool_names) ``` ```csharp C# SDK theme={null} var test = await sdk.Llm.McpVault.TestServerAsync(serverId: "mcp_srv_123"); Console.WriteLine(string.Join(", ", test.McpServerTest?.ToolNames ?? new List())); ``` ```bash CLI theme={null} mka1 llm mcp-vault test-server \ --server-id mcp_srv_123 ``` ```bash Bash theme={null} curl https://apigw.mka1.com/api/v1/llm/mcp/servers/mcp_srv_123/test \ --request POST \ --header 'Authorization: Bearer ' ``` A successful test lists the three tools: ```json Response theme={null} { "server_id": "mcp_srv_123", "object": "mcp.server.test", "connected": true, "tool_count": 3, "tool_names": ["list_tables", "describe_table", "query"] } ``` A failed connection returns HTTP 502. Check the URL, the public address and the token. ## 5. Ask a question Reference the registered server in the `tools` of a response. Tell the model what the database holds in `instructions`. ```ts TypeScript SDK theme={null} const response = await sdk.llm.responses.create({ responsesCreateRequest: { model: 'auto', instructions: 'Answer questions about customers and orders from the shop database. Look up the tables and their columns before you write SQL. Keep the final answer short.', input: 'Which customers have spent the most on delivered orders?', tools: [ { type: 'mcp', mcpServerId: 'mcp_srv_123', mcpCredentialId: 'mcp_cred_123', allowedTools: ['list_tables', 'describe_table', 'query'], requireApproval: 'never', }, ], }, }); console.log(response); ``` ```ts OpenAI SDK theme={null} import OpenAI from 'openai'; const openai = new OpenAI({ apiKey: '', baseURL: 'https://apigw.mka1.com/api/v1/llm/', }); // The OpenAI client has no field for a registered server, // so pass the server URL and its token in the tool definition. const response = await openai.responses.create({ model: 'auto', instructions: 'Answer questions about customers and orders from the shop database. Look up the tables and their columns before you write SQL. Keep the final answer short.', input: 'Which customers have spent the most on delivered orders?', tools: [ { type: 'mcp', server_label: 'shop_db', server_url: 'https:///mcp', headers: { Authorization: 'Bearer ', }, allowed_tools: ['list_tables', 'describe_table', 'query'], require_approval: 'never', }, ], }); console.log(response.output_text); ``` ```python Python SDK theme={null} response = sdk.llm.responses.create( model="auto", instructions="Answer questions about customers and orders from the shop database. Look up the tables and their columns before you write SQL. Keep the final answer short.", input="Which customers have spent the most on delivered orders?", tools=[ { "type": "mcp", "mcp_server_id": "mcp_srv_123", "mcp_credential_id": "mcp_cred_123", "allowed_tools": ["list_tables", "describe_table", "query"], "require_approval": "never", }, ], ) print(response.output_text) ``` ```csharp C# SDK theme={null} var response = await sdk.Llm.Responses.CreateAsync(new ResponsesCreateRequest() { Model = "auto", Instructions = "Answer questions about customers and orders from the shop database. Look up the tables and their columns before you write SQL. Keep the final answer short.", Input = ResponsesCreateRequestInput.CreateStr( "Which customers have spent the most on delivered orders?"), Tools = new List { ResponsesCreateRequestTool.CreateMCPToolDefinition(new MCPToolDefinition() { McpServerId = "mcp_srv_123", McpCredentialId = "mcp_cred_123", AllowedTools = MCPToolDefinitionAllowedTools.CreateArrayOfStr( new List { "list_tables", "describe_table", "query" }), RequireApproval = "never", }), }, }); Console.WriteLine(response.ResponseObject?.OutputText); ``` ```bash CLI theme={null} mka1 llm responses create --body '{ "model": "auto", "instructions": "Answer questions about customers and orders from the shop database. Look up the tables and their columns before you write SQL. Keep the final answer short.", "input": "Which customers have spent the most on delivered orders?", "tools": [ { "type": "mcp", "mcp_server_id": "mcp_srv_123", "mcp_credential_id": "mcp_cred_123", "allowed_tools": ["list_tables", "describe_table", "query"], "require_approval": "never" } ] }' ``` ```bash Bash theme={null} curl https://apigw.mka1.com/api/v1/llm/responses \ --request POST \ --header 'Content-Type: application/json' \ --header 'Authorization: Bearer ' \ --data '{ "model": "auto", "instructions": "Answer questions about customers and orders from the shop database. Look up the tables and their columns before you write SQL. Keep the final answer short.", "input": "Which customers have spent the most on delivered orders?", "tools": [ { "type": "mcp", "mcp_server_id": "mcp_srv_123", "mcp_credential_id": "mcp_cred_123", "allowed_tools": ["list_tables", "describe_table", "query"], "require_approval": "never" } ] }' ``` The model calls `list_tables` and `describe_table` to learn the schema, then calls `query` with the SQL it wrote. Each call and its result appear in the response `output`, followed by the model's answer. The `query` tool returns the rows with the name and PostgreSQL type of every column: ```json Query result theme={null} { "columns": [ { "name": "name", "type": "text" }, { "name": "orders", "type": "bigint" }, { "name": "spent", "type": "numeric" } ], "rows": [ { "name": "Olena Kovalenko", "orders": "2", "spent": "1730.50" }, { "name": "Taras Shevchuk", "orders": "1", "spent": "999.90" } ], "row_count": 2, "truncated": false } ``` `bigint` and `numeric` values arrive as strings, so large and decimal numbers keep their exact value. `truncated` is `true` when more rows matched than `MAX_ROWS`. > [!NOTE] > > You can also pass `server_url` and `headers` in the tool definition without registering the server. See [Use MCP tools](https://docs.mka1.com/docs/mcp-tools.md#call-an-mcp-tool-directly). ## Next steps * Use the same tool definition in a saved agent. See [Build an agent with MCP tools](https://docs.mka1.com/docs/build-an-agent-with-mcp-tools.md#4-create-the-agent). * Set `require_approval` to `"always"` when a person should confirm each query before it runs. See [Require end-user approval](https://docs.mka1.com/docs/mcp-tools.md#require-end-user-approval). * Give each end user their own access by storing one credential per end user with `X-On-Behalf-Of`. See [Store the MCP credential](https://docs.mka1.com/docs/build-an-agent-with-mcp-tools.md#2-store-the-mcp-credential). * Rotate the token by starting the server with a new `MCP_AUTH_TOKEN`, creating a new credential, and deleting the old one. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.