Streaming a CSV file from the database in Laravel
Exporting data to CSV is a requirement in almost any business software project. We needed it to get a convenient sales data export in our card shop sideproject recently, and it turned out to be straightforward but slightly weird to do in Laravel.
Sales data export query
The sales data export query is about 30 lines of SQL. This was easier to write directly in SQL than wrapping it up in Laravel’s ORM, and it’s also handy to be able to run this query separately anyway.
Sales data export controller
The next step was to create a controller to serve this data to admin users. It needs a database connection to run the query, which Laravel can inject automatically:
<?php
namespace PopRobinCards\Modules\Sales\Http\Controllers\Staff;
use Illuminate\Database\Connection;
use PopRobinCards\Http\Controllers\Controller;
class SalesExportController extends Controller
{
/** @var Connection */
private $db;
/**
* @param Connection $db
*/
public function __construct(Connection $db)
{
$this->db = $db;
}
}
It would be possible to save the data to an actual CSV file on the server’s disk and use Laravel’s file download helper to serve it to the client. However, that seems a bit clunky with the unnecessary I/O and dependence on the disk. It could also cause problems if there are concurrent requests for this file to be written and streamed.
Instead, it’s safer and faster to stream the data from the database, through Laravel and on to the client. A database cursor seems like a good way to do that as we don’t need to load the entire result into memory at once:
<?php
/**
* @return \Generator
*/
private function cursor()
{
$this->db->getDoctrineConnection()->setFetchMode(\PDO::FETCH_ASSOC);
return $this->db->cursor(
$this->db->raw(
\file_get_contents(
\base_path('resources/queries/stock-sales-report.sql')
)
)
);
}
We’re setting the fetch mode on the underlying connect to FETCH_ASSOC, as it
will be easier to write arrays to the CSV output stream later. The direct
retrieval of the SQL file with a hard-coded path may be a bit clunky, but it
does the job for now and is quite manageable in this minimal controller class.
If this feature got larger we might want to define that in config and have it
injected into the controller instead.
Now we need a public controller action to respond to client requests:
<?php
use Symfony\Component\HttpFoundation\StreamedResponse;
# ...
/**
* @return StreamedResponse
*/
public function downloadSalesExport(): StreamedResponse
{
return response()->stream(
function (): void {
$this->stream();
}
);
}
We’re using Symfony’s StreamedResponse class, which takes a callback to do the
actual streaming. This is just a wrapper to allow this behaviour to exist in the
context of a web framework like Laravel or Symfony.
The streaming function takes us back to old-school PHP:
<?php
/**
* Stream the query as CSV data to the client.
*/
private function stream(): void
{
\header('Content-Description: File Transfer');
\header('Content-type: text/csv');
\header(
"Content-Disposition: attachment; filename={$this->fileName()}"
);
$fh = \fopen('php://output', 'wb');
foreach ($this->cursor() as $i => $row) {
$row = (array) $row;
if ($i === 0) {
\fputcsv($fh, array_keys($row));
}
\fputcsv($fh, $row);
}
\flush();
\fclose($fh);
}
This is what a lot of PHP applications looked like before OOP and frameworks became the norm! It’s not very modern, and uses global PHP functionality. If this style was widespread in the application it could be a problem, but as we can keep it quite contained in this controller class it doesn’t cause me too much concern.
This function sends some headers to the client to tell it to expect a CSV file download, and then streams each row from the cursor as a CSV row. We’re using PHP’s file-handling to treat the output as a CSV file that we can write to using the native CSV functions. This should be quite fast and light on resources.
The whole controller class looks like this:
<?php
namespace PopRobinCards\Modules\Sales\Http\Controllers\Staff;
use Carbon\Carbon;
use Illuminate\Database\Connection;
use PopRobinCards\Http\Controllers\Controller;
use Symfony\Component\HttpFoundation\StreamedResponse;
class SalesExportController extends Controller
{
/** @var Connection */
private $db;
/**
* @param Connection $db
*/
public function __construct(Connection $db)
{
$this->db = $db;
}
/**
* @return StreamedResponse
*/
public function downloadSalesExport(): StreamedResponse
{
return response()->stream(
function (): void {
$this->stream();
}
);
}
/**
* Stream the query as CSV data to the client.
*/
private function stream(): void
{
\header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
\header('Content-Description: File Transfer');
\header('Content-type: text/csv');
\header(
"Content-Disposition: attachment; filename={$this->fileName()}"
);
$fh = \fopen('php://output', 'wb');
foreach ($this->cursor() as $i => $row) {
$row = (array) $row;
if ($i === 0) {
\fputcsv($fh, array_keys($row));
}
\fputcsv($fh, $row);
}
\flush();
\fclose($fh);
}
/**
* @return string
*/
private function fileName(): string
{
return Carbon::now()->format(DATE_ATOM).'-sales-export.csv';
}
/**
* @return \Generator|array
*/
private function cursor()
{
$this->db->getDoctrineConnection()->setFetchMode(\PDO::FETCH_ASSOC);
return $this->db->cursor(
$this->db->raw(
\file_get_contents(
\base_path('resources/queries/stock-sales-report.sql')
)
)
);
}
}