Simple PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/query.ts:36-69 (handler)

  The core handler function that executes the SQL query against the PostgreSQL database using DatabaseConnection, enforces read-only restrictions, measures execution time, and returns structured results.

  export async function executeQuery(
    connectionString: string,
    query: string,
    readOnly: boolean = false
  ): Promise<QueryResult> {
    const db = DatabaseConnection.getInstance();
    const startTime = Date.now();
    
    try {
      // Check if query is allowed in readOnly mode
      if (readOnly && !isReadOnlyQuery(query)) {
        throw new Error('Only read-only queries are allowed when readOnly is set to true');
      }
      
      await db.connect(connectionString);
      
      // Execute the query
      const result = await db.query(query);
      const executionTime = Date.now() - startTime;
      
      return {
        success: true,
        rowCount: result.rowCount || 0,
        rows: result.rows || [],
        command: result.command || 'UNKNOWN',
        executionTime
      };
    } catch (error) {
      const executionTime = Date.now() - startTime;
      throw new Error(`Query execution failed: ${error instanceof Error ? error.message : String(error)} (execution time: ${executionTime}ms)`);
    } finally {
      await db.disconnect();
    }
  }

• src/index.ts:15-32 (schema)

  The tool definition including name, description, and input schema for validating arguments to execute_query.

    name: 'execute_query',
    description: 'Execute SQL queries on PostgreSQL database',
    inputSchema: {
      type: 'object',
      properties: {
        query: {
          type: 'string',
          description: 'SQL query to execute'
        },
        readOnly: {
          type: 'boolean',
          description: 'If true, only allows read-only queries (SELECT, EXPLAIN, etc.)',
          default: false
        }
      },
      required: ['query']
    }
  }

• src/index.ts:50-53 (registration)

  Registration of the execute_query tool in the MCP server capabilities.

  capabilities: {
    tools: {
      execute_query: TOOL_DEFINITIONS[0]
    },

• src/index.ts:78-96 (registration)

  Request handler dispatch for execute_query tool, which extracts parameters, enforces server mode, calls the executeQuery handler, and formats the response.

  case 'execute_query': {
    const { query, readOnly = false } = request.params.arguments as {
      query: string;
      readOnly?: boolean;
    };
    
    // Enforce readonly mode if server is in readonly mode
    const enforceReadOnly = this.serverMode === 'readonly' || readOnly;
    
    const result = await executeQuery(this.connectionString, query, enforceReadOnly);
    return {
      content: [
        {
          type: 'text',
          text: JSON.stringify(result, null, 2)
        }
      ]
    };
  }

• src/tools/query.ts:16-34 (helper)

  Helper function to determine if a SQL query is read-only by checking starting command and excluding certain modifiers.

  function isReadOnlyQuery(query: string): boolean {
    // Normalize query - trim whitespace and convert to uppercase for comparison
    const normalizedQuery = query.trim().toUpperCase();
    
    // Check if query starts with SELECT, EXPLAIN, SHOW, etc.
    const readOnlyCommands = [
      'SELECT', 
      'EXPLAIN', 
      'SHOW',
      'WITH', // CTE that ends with SELECT
      'ANALYZE',
      'DESCRIBE'
    ];
    
    return readOnlyCommands.some(cmd => normalizedQuery.startsWith(cmd)) &&
      !normalizedQuery.includes('INTO') && // Exclude SELECT INTO
      !normalizedQuery.includes('FOR UPDATE') && // Exclude locking queries
      !normalizedQuery.includes('FOR SHARE');
  }