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
      },
  });