PostgreSQL Query MCP Server

Implementation Reference

• src/index.ts:79-134 (handler)

  The inline handler function for the "query-postgres" tool. Validates the query is a read-only SELECT, executes it using the Postgres pool with a 10-second timeout, formats results as JSON (rows, rowCount, fields), or returns an error message.
  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:79-79 (schema)

  Input schema for the tool using Zod, requiring a single 'query' parameter of type string.
  server.tool("query-postgres", { query: z.string() }, async ({ query }) => {
• src/index.ts:79-79 (registration)

  Registration of the "query-postgres" tool on the MCP server instance.
  server.tool("query-postgres", { query: z.string() }, async ({ query }) => {
• src/index.ts:41-76 (helper)

  Helper function to validate that the SQL query is read-only (starts with SELECT, excludes common write operations like INSERT, UPDATE, etc.). Called within the tool handler.
  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:17-22 (helper)

  Postgres connection pool initialized from DATABASE_URL or command-line argument, used by the query-postgres tool handler.
  const pool = new Pool({ connectionString, ssl: { rejectUnauthorized: false, // Use this if you're getting SSL certificate errors }, });