PostgreSQL MCP Server

Instructions

Implementation Reference

• src/server.ts:91-131 (handler)

  Inline async handler function that initializes the DB connection, tests it using testDB helper, and returns formatted success or error response.

  async () => {
    try {
      // Show current configuration (without password)
      const config = {
        host: process.env.DB_HOST,
        port: process.env.DB_PORT || '5432',
        database: process.env.DB_NAME,
        username: process.env.DB_USERNAME,
        password: '***hidden***'
      };
  
      // Attempt to connect and test
      const db = initDb();
      const testResult = await testDB(db);
      
      if (testResult.success) {
        return {
          content: [{
            type: "text",
            text: `✅ Database connection successful!\n\nConfiguration:\n${JSON.stringify(config, null, 2)}\n\nConnection test result: ${JSON.stringify(testResult.result, null, 2)}`
          }]
        };
      } else {
        return {
          content: [{
            type: "text",
            text: `❌ Database connection failed!\n\nConfiguration:\n${JSON.stringify(config, null, 2)}\n\nError: ${testResult.error}`
          }],
          isError: true
        };
      }
    } catch (error) {
      return {
        content: [{
          type: "text",
          text: `❌ Unexpected error during connection test: ${error instanceof Error ? error.message : String(error)}`
        }],
        isError: true
      };
    }
  }

• src/server.ts:85-132 (registration)

  Registration of the 'test-postgres-connection' tool with server.registerTool, including name, metadata (title, description), and the handler function.

  server.registerTool(
    "test-postgres-connection",
    {
      title: "Test Postgres Connection",
      description: "Test the Postgres connection and return connection status",
    },
    async () => {
      try {
        // Show current configuration (without password)
        const config = {
          host: process.env.DB_HOST,
          port: process.env.DB_PORT || '5432',
          database: process.env.DB_NAME,
          username: process.env.DB_USERNAME,
          password: '***hidden***'
        };
  
        // Attempt to connect and test
        const db = initDb();
        const testResult = await testDB(db);
        
        if (testResult.success) {
          return {
            content: [{
              type: "text",
              text: `✅ Database connection successful!\n\nConfiguration:\n${JSON.stringify(config, null, 2)}\n\nConnection test result: ${JSON.stringify(testResult.result, null, 2)}`
            }]
          };
        } else {
          return {
            content: [{
              type: "text",
              text: `❌ Database connection failed!\n\nConfiguration:\n${JSON.stringify(config, null, 2)}\n\nError: ${testResult.error}`
            }],
            isError: true
          };
        }
      } catch (error) {
        return {
          content: [{
            type: "text",
            text: `❌ Unexpected error during connection test: ${error instanceof Error ? error.message : String(error)}`
          }],
          isError: true
        };
      }
    }
  );

• src/server.ts:34-46 (helper)

  testDB helper function that performs a simple SELECT query to verify the database connection and returns success/error status.

  const testDB = async (db: postgres.Sql) => {
    try {
      // Simple query to test connection
      const result = await db`SELECT 1 as test`;
      return { success: true, message: "Connection successful", result };
    } catch (error) {
      return { 
        success: false, 
        message: "Connection failed", 
        error: error instanceof Error ? error.message : String(error)
      };
    }
  };

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

  initDb helper that lazily initializes the global dbConnection using getDb.

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

• src/server.ts:20-31 (helper)

  getDb helper that creates a new postgres connection using environment variables.

  const getDb = () => {
    const db = postgres({
          host: process.env.DB_HOST,
          port: parseInt(process.env.DB_PORT || '5432'),
          database: process.env.DB_NAME,
          username: process.env.DB_USERNAME,
          password: process.env.DB_PASSWORD,
          ssl: 'prefer'
  
        });
    return db;
  };