PostgreSQL MCP Server

Instructions

Implementation Reference

• src/server.ts:174-203 (handler)

  The core handler function for the 'execute-sql-query' tool. It validates the SQL query using sqlValidation, executes it safely with initDb().unsafe(sql), and returns the JSON-formatted results or an error message.

  async ({ sql }) => {
    const validation = sqlValidation(sql);
    if (!validation.success) {
      return {
        content: [{
          type: "text",
          text: `❌ Query validation failed: ${validation.message}`
        }],
        isError: true
      };
    }
    try {
      const results = await initDb().unsafe(sql);
      return {
        content: [{
          type: "text",
          text: JSON.stringify(results, null, 2)
        }]
      };
    } catch (err: unknown) {
      const error = err as Error;
      return {
        content: [{
          type: "text",
          text: `❌ Query execution failed: ${error.message}`
        }],
        isError: true
      };
    }
  }

• src/server.ts:165-204 (registration)

  The registration call for the 'execute-sql-query' tool, specifying the name, metadata (title, description), input schema using Zod, and the handler function.

  server.registerTool(
    "execute-sql-query",
    {
      title: "Execute SQL Query",
      description: "Execute a SQL query on the database. Only SELECT statements are allowed for security.",
      inputSchema: {
        sql: z.string().describe("The SQL query to execute")
      },
    },
    async ({ sql }) => {
      const validation = sqlValidation(sql);
      if (!validation.success) {
        return {
          content: [{
            type: "text",
            text: `❌ Query validation failed: ${validation.message}`
          }],
          isError: true
        };
      }
      try {
        const results = await initDb().unsafe(sql);
        return {
          content: [{
            type: "text",
            text: JSON.stringify(results, null, 2)
          }]
        };
      } catch (err: unknown) {
        const error = err as Error;
        return {
          content: [{
            type: "text",
            text: `❌ Query execution failed: ${error.message}`
          }],
          isError: true
        };
      }
    }
  );

• src/server.ts:170-172 (schema)

  The input schema definition for the tool, using Zod to validate a single 'sql' string parameter.

  inputSchema: {
    sql: z.string().describe("The SQL query to execute")
  },

• src/server.ts:56-82 (helper)

  SQL validation helper function used by the handler to ensure only safe (SELECT) queries are executed by blocking dangerous SQL keywords.

  function sqlValidation(sql: string) {
    const dangerousKeywords = ["DROP", "DELETE", "TRUNCATE", "ALTER", "CREATE", "INSERT", "UPDATE"];
    
    // check if these keywords appear at the start of statements allows them in column names, comments, etc.
    const statements = sql.split(';').map(s => s.trim()).filter(s => s.length > 0);
    
    for (const statement of statements) {
      // Skip empty statements and comments
      if (!statement || statement.startsWith('--') || statement.startsWith('/*')) {
        continue;
      }
      
      // Check if statement starts with a dangerous keyword
      const firstWord = statement.split(/\s+/)[0].toUpperCase();
      if (dangerousKeywords.includes(firstWord)) {
        return {
          success: false,
          message: `Dangerous operation detected: ${firstWord}. Only SELECT statements are allowed.`
        };
      }
    }
    
    return {
      success: true,
      message: "SQL is safe"
    };
  }

• src/server.ts:49-54 (helper)

  Database initialization helper that lazily creates and caches the database connection using getDb() for reuse across tool calls.

  const initDb = () => {
    if(!dbConnection) {
      dbConnection = getDb();
    }
    return dbConnection;
  };