query-postgres
Execute read-only SQL queries against a PostgreSQL database using natural language. Enables direct interaction with database data for efficient querying and analysis.
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes |
Implementation Reference
- src/index.ts:79-134 (handler)The handler for the 'query-postgres' tool. Registers the tool, defines input schema as a string query, validates it is read-only (SELECT), executes via pg Pool with 10-second timeout, returns JSON-formatted results (rows, rowCount, fields) or error messages.server.tool("query-postgres", { query: z.string() }, async ({ query }) => { try { // Validate that the query is read-only if (!isReadOnlyQuery(query)) { return { content: [ { type: "text", text: "Error: Only SELECT queries are allowed. INSERT, UPDATE, DELETE, and other write operations are not permitted.", }, ], isError: true, }; } // Create a promise that will be rejected after 10 seconds const timeoutPromise = new Promise((_, reject) => { setTimeout(() => { reject(new Error("Query execution timed out after 10 seconds. Pls modify the query and try again.")); }, 10000); // 10 seconds in milliseconds }); // Execute the query with a timeout const result = (await Promise.race([pool.query(query), timeoutPromise])) as any; // Using 'any' here to handle the type after the race return { content: [ { type: "text", text: JSON.stringify( { rows: result.rows, rowCount: result.rowCount, fields: result.fields.map((f: any) => ({ name: f.name, dataType: f.dataTypeID, })), }, null, 2 ), }, ], }; } catch (error) { return { content: [ { type: "text", text: `Error executing query: ${(error as Error).message}`, }, ], isError: true, }; } });
- src/index.ts:41-76 (helper)Helper function used by the handler to validate that the SQL query is read-only (starts with SELECT and does not contain write operations). Normalizes query by removing comments and whitespace.function isReadOnlyQuery(query: string): boolean { // Normalize the query by removing comments, extra whitespace, and converting to lowercase const normalizedQuery = query .replace(/--.*$/gm, "") // Remove single-line comments .replace(/\/\*[\s\S]*?\*\//g, "") // Remove multi-line comments .replace(/\s+/g, " ") .trim() .toLowerCase(); // Check if the query starts with SELECT if (normalizedQuery.startsWith("select")) { return true; } // Check for other write operations const writeOperations = [ "insert", "update", "delete", "drop", "create", "alter", "truncate", "grant", "revoke", "copy", // COPY can write data ]; for (const op of writeOperations) { if (normalizedQuery.startsWith(op) || normalizedQuery.includes(" " + op + " ")) { return false; } } return true; }
- src/index.ts:79-79 (schema)Input schema for the tool: requires a 'query' parameter of type string (validated by Zod).server.tool("query-postgres", { query: z.string() }, async ({ query }) => {
- src/index.ts:79-79 (registration)Registers the 'query-postgres' tool on the MCP server with name, schema, and handler function.server.tool("query-postgres", { query: z.string() }, async ({ query }) => {