Streaming CSV Using PHP

In one of our application, there’s a need to get list of data from a service. This data is used to generate report by background process, resulting a XLSX file that can be downloaded or attached to email.

This service (an API) is written in Laravel. The background process is also written in Laravel. Both service are hosted in different server.

We pull data from MySQL and simply send the response in JSON to be easily parsed.

The problem we encountered was the amount of data. It could be thousands and PHP would suffered memory allocation error processing this amount of data. Increasing memory_limit in php.ini was not a good solution.

After doing some searching, we found this great article written by Barry vd. Heuvel and following his method.

We decided to switch from JSON to CSV. Streaming CSV was much more simple rather than steraming JSON. It’s just simply write the data line by line to the response.

Both API service and the background should be agreed with the structure and field used. The background process should change the method of consuming data, from simple HTTP request to HTTP streaming request.

Because we’re using Laravel, so we use Laravel manner to stream the CSV. We pulled data from database using model, and then crafted the response using controller.

Here the example of our API server controller.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\StreamedResponse;
use App\Models\User;

class StreamController extends Controller
{
    public function get(Request $request)
    {
        $response = new StreamedResponse();
        
        $fields = ['id', 'name', 'email'];

        $response->setCallback(function () use ($fields, $request) {
            $handle = fopen('php://output', 'w');
            
            // set CSV header
            fputcsv($handle, $fields);
            
            // fill the data, using chunk by 1000
            User::select($fields)
                ->where($request->all())
                ->chunk(1000, function($users) use (&$handle) {
                    $users->each(function($user) use (&$handle) {
                        fputcsv($handle, $user->toArray());
                    });
                });
                
            fclose($handle);
        });

        $response->headers->set('Content-Type', 'text/csv');
        $response->setStatusCode(Response::HTTP_OK);

        return $response;
    }
}

For background process, we use Guzzle to fetch the API response.

<?php
$token = 'jwt.token';
// get the stream using Guzzle
$guzzle = new GuzzleHttp\Client([
    'headers' => [
        'Accept' => 'text/csv',
        'Authorization' => 'Bearer '.$token,
    ],
    'stream' => true,
    'timeout' => 0,
    'base_uri' => 'http://api.server',
]);
$response = $guzzle->request('get', '/stream');
$data = $response->getBody()->getContents();
// process the $data
// exportToXlsx($data);

We’re happy with the results. This method worked as long as there’s no interruption in the connection between background process and the API service.