PostgreSQL MCP Server

Instructions

Implementation Reference

• src/tools/migration.ts:30-85 (handler)

  Core handler function that executes the logic for exporting PostgreSQL table data to JSON or CSV format, including database query, data formatting, and file writing.

  async function executeExportTableData(
    input: ExportTableDataInput,
    getConnectionString: GetConnectionStringFn
  ): Promise<{ tableName: string; rowCount: number; outputPath: string }> {
    const resolvedConnectionString = getConnectionString(input.connectionString);
    const db = DatabaseConnection.getInstance();
    const { tableName, outputPath, where, limit, format } = input;
    
    try {
      await db.connect(resolvedConnectionString);
      
      let query = `SELECT * FROM "${tableName}"`; // Consider quoting table name properly
      const params: unknown[] = [];
      
      if (where) {
        query += ` WHERE ${where}`; // SECURITY: Ensure 'where' is safe or validated if user-supplied
      }
      
      if (limit) {
        query += ` LIMIT ${limit}`;
      }
      
      const data = await db.query<Record<string, unknown>[]>(query, params);
      
      const dir = path.dirname(outputPath);
      // Use fs.promises.mkdir for cleaner async/await
      await fs.promises.mkdir(dir, { recursive: true });
      
      if (format === 'csv') {
        if (data.length === 0) {
          await fs.promises.writeFile(outputPath, '');
        } else {
          const headers = Object.keys(data[0]).join(',');
          const rows = data.map(row => 
            Object.values(row).map(value => {
              const stringValue = String(value); // Ensure value is a string
              return typeof value === 'string' ? `"${stringValue.replace(/"/g, '""')}"` : stringValue;
            }).join(',')
          );
          await fs.promises.writeFile(outputPath, [headers, ...rows].join('\n'));
        }
      } else {
        await fs.promises.writeFile(outputPath, JSON.stringify(data, null, 2));
      }
      
      return {
          tableName,
          rowCount: data.length,
          outputPath
      };
    } catch (error) {
      throw new McpError(ErrorCode.InternalError, `Failed to export data: ${error instanceof Error ? error.message : String(error)}`);
    } finally {
      await db.disconnect();
    }
  }

• src/tools/migration.ts:20-28 (schema)

  Zod input schema defining parameters for the pg_export_table_data tool, including table name, output path, optional filters, and format.

  const ExportTableDataInputSchema = z.object({
    connectionString: z.string().optional(),
    tableName: z.string(),
    outputPath: z.string().describe("absolute path to save the exported data"),
    where: z.string().optional(),
    limit: z.number().int().positive().optional(),
    format: z.enum(['json', 'csv']).optional().default('json'),
  });
  type ExportTableDataInput = z.infer<typeof ExportTableDataInputSchema>;

• src/tools/migration.ts:87-104 (registration)

  Tool object definition and export, including name, description, input schema reference, and wrapper execute function that handles validation and delegates to core handler.

  export const exportTableDataTool: PostgresTool = {
    name: 'pg_export_table_data',
    description: 'Export table data to JSON or CSV format',
    inputSchema: ExportTableDataInputSchema,
    async execute(params: unknown, getConnectionString: GetConnectionStringFn): Promise<ToolOutput> {
      const validationResult = ExportTableDataInputSchema.safeParse(params);
      if (!validationResult.success) {
        return { content: [{ type: 'text', text: `Invalid input: ${validationResult.error.format()}` }], isError: true };
      }
      try {
        const result = await executeExportTableData(validationResult.data, getConnectionString);
        return { content: [{ type: 'text', text: `Successfully exported ${result.rowCount} rows from ${result.tableName} to ${result.outputPath}` }] };
      } catch (error) {
        const errorMessage = error instanceof McpError ? error.message : (error instanceof Error ? error.message : String(error));
        return { content: [{ type: 'text', text: `Error exporting data: ${errorMessage}` }], isError: true };
      }
    }
  };

• src/index.ts:225-257 (registration)

  Inclusion of the pg_export_table_data tool (via exportTableDataTool) in the central allTools array, which is passed to the MCP server constructor for registration and capability advertisement.

  const allTools: PostgresTool[] = [
    // Core Analysis & Debugging
    analyzeDatabaseTool,
    debugDatabaseTool,
    
    // Schema & Structure Management (Meta-Tools)
    manageSchemaTools,
    manageFunctionsTool,
    manageTriggersTools,
    manageIndexesTool,
    manageConstraintsTool,
    manageRLSTool,
    
    // User & Security Management
    manageUsersTool,
    
    // Query & Performance Management
    manageQueryTool,
    
    // Data Operations (Enhancement Tools)
    executeQueryTool,
    executeMutationTool,
    executeSqlTool,
    
    // Documentation & Metadata
    manageCommentsTool,
    
    // Data Migration & Monitoring
    exportTableDataTool,
    importTableDataTool,
    copyBetweenDatabasesTool,
    monitorDatabaseTool
  ];