Export data using StreamedResponse

[midnight quartz]

I am trying to export a CSV of a cemetery on my website. This is the function right now. However, it is timing out after 10 seconds. Any ideas? To make it more efficient or, simply put, not time out?

public function exportCSV()
    {
        $fileName = 'cemeteries.csv';
        $cemeteries = Cemetery::select(
            'name',
            'location',
            'lot_status',
            'gender',
            'birth_date',
            'death_date',
            'veteran',
            'holocaust_survivor',
            'notes'
        )->get();

        $headers = [
            "Content-type"        => "text/csv",
            "Content-Disposition" => "attachment; filename=$fileName",
            "Pragma"              => "no-cache",
            "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
            "Expires"             => "0"
        ];

        $columns = [
            'Name',
            'Location',
            'Lot Status',
            'Gender',
            'Birth Date',
            'Death Date',
            'Veteran',
            'Holocaust Survivor',
            'Notes'
        ];

        $callback = function() use($cemeteries, $columns) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $columns);

            foreach ($cemeteries as $cemetery) {
                $row = [
                    $cemetery->name,
                    $cemetery->location,
                    $cemetery->lot_status,
                    $cemetery->gender,
                    $cemetery->birth_date,
                    $cemetery->death_date,
                    $cemetery->veteran,
                    $cemetery->holocaust_survivor,
                    $cemetery->notes
                ];

                fputcsv($file, $row);
            }

            fclose($file);
        };

        return new StreamedResponse($callback, 200, $headers);
    }

[zenith totem]

Reading/writing to files are indeed an expensive operation, it is normal to take a little while, especially when dealing with a lot of data. I assume there are a lot of cemeteries? So it is definitely normal to take more than 10 seconds. I have one of my scripts that reads an .csv and just opening the file takes 8 seconds

[midnight quartz]

Yeah, there are around 3000 rows of data right now. Not as much as it may be soon, but certainly no light feat

Do you know the max timeout in vapor.yml? I am trying to figure that out, but I do not remember

And cannot find it

[zenith totem]

Anyway, in order to better optimize this, maybe you could see what it looks like in a debugging tool, like Telescope or Laravel Debugbar? That would tell you if you have any slow queries or something like that

I'm not familiar with Vapor. Are you using a background worker (like queue) to process this?

[midnight quartz]

No. The queues were not working for me. I was talking to the support staff at Vapor, and they could not figure it out either. I am trying to get away from that, and just figure out how to expand the max timeout time

[zenith totem]

That's bad news. I'm afraid you'll need a worker for this going forward, specially if you data grows with time. With a worker running, you can set different timeouts. It should be easy to setup, if you ever want to try setting it up again I can try to help

10 seconds is not that long when it comes to writing/reading files

[midnight quartz]

Sounds good, I was trying to avoid that

Oh well

It is timing out after 10s as that is the default timeout - I was trying to just raise this

I don't know how long it actually is

[zenith totem]

oh Vapor is laravel serverless!

[midnight quartz]

Yeah

[zenith totem]

never worked with it. Yeah, that will be different setting up the workers

Let me see if I can think of something

[midnight quartz]

Sounds good. And just an update: I limited it to only 20 rows, and increased the timeout to 120 seconds, and it is not working.

So there is surely another problem

I will upload everything that is happening real quick:

CemeteryController.php -> exportCSV()

public function exportCSV()
    {
        $fileName = 'cemeteries.csv';
        $cemeteries = Cemetery::select(
            'name',
            'location',
            'lot_status',
            'gender',
            'birth_date',
            'death_date',
            'veteran',
            'holocaust_survivor',
            'notes'
        )->take(20)->get();

        $headers = [
            "Content-type"        => "text/csv",
            "Content-Disposition" => "attachment; filename=$fileName",
            "Pragma"              => "no-cache",
            "Cache-Control"       => "must-revalidate, post-check=0, pre-check=0",
            "Expires"             => "0"
        ];

        $columns = [
            'Name',
            'Location',
            'Lot Status',
            'Gender',
            'Birth Date',
            'Death Date',
            'Veteran',
            'Holocaust Survivor',
            'Notes'
        ];

        $callback = function() use($cemeteries, $columns) {
            $file = fopen('php://output', 'w');
            fputcsv($file, $columns);

            foreach ($cemeteries as $cemetery) {
                $row = [
                    $cemetery->name,
                    $cemetery->location,
                    $cemetery->lot_status,
                    $cemetery->gender,
                    $cemetery->birth_date,
                    $cemetery->death_date,
                    $cemetery->veteran,
                    $cemetery->holocaust_survivor,
                    $cemetery->notes
                ];

                fputcsv($file, $row);
            }

            fclose($file);
        };

        return new StreamedResponse($callback, 200, $headers);
    }

web.php:

Route::get('/cemetery/export', [CemeteryController::class, 'exportCSV'])->name('cemetery.export');

vapor.yml:

id: 61899
name: mysite
environments:
    production:
        timeout: 120
        memory: 1024
        cli-memory: 512
        runtime: 'php-8.3:al2'
        database: 'mydb'
        domain: 'mysite.com'
        storage: 'myimages'
        build:
            - 'COMPOSER_MIRROR_PATH_REPOS=1 composer install --no-dev'
            - 'php artisan event:cache'
            - 'php artisan config:cache'
            - 'php artisan route:cache'
            - 'php artisan view:cache'
            - 'npm ci && npm run build --production && rm -rf node_modules'
        deploy:
            - 'php artisan migrate --force'

navigation.blade.php (export button)

<div title="Export Data as CSV" class="flex items-center">
   <a href="{{ route('cemetery.export') }}" itemprop="url"><i class="text-gray-400 hover:text-gray-500 pl-2 text-[14px] fa-solid fa-file-csv"></i></a>
</div>

[zenith totem]

Can you run it locally with a tool like Telescope? That will help you see any pain points in the code

[midnight quartz]

The one thing that I have been trying to avoid is local development. All of it has been in production. I guess I should do that so that when I have companies using my code (in about a month!) I do not break the code every time...

[zenith totem]

but yeah it shouldn't take 2 minutes with only 20 recors

[midnight quartz]

Yeah lol

[zenith totem]

with serverless I guess Laravel runs it inside a lambda? Maybe there is an issue trying to store a temp file there? I don't really now, I'll need to study it a bit more

hopefully another more experienced dev can weigh in

but I can't see how you can code without a local environment, that's wild.

[midnight quartz]

It does use AWS Lambda

And yes, I am literally using the logs in Vapor to debug on the website. Whoops!

I am going to set up a local env real quick!

[zenith totem]

I'll need to log off for a while but I'll try to help you out later if you're still stuck

anyway, I was looking at https://docs.vapor.build/resources/storage.html and it seems that you can use a temporary storage for this (last paragraph)

so you would write to an csv file in this /tmpdirectory, maybe /tmp/cemeteries.csv

[midnight quartz]

Yeah, I am going to work on setting up local dev, and then look into this.

[midnight quartz]

It turns out that the error is essentially: Call to undefined method "show". I am now not getting timeout errors, but I am getting that error. That being said, it does not make much sense as I am not calling any show method here... Not sure if anyone knows why this would be

[zenith totem]

In your error stack trace you should have more information about what exactly called the "show" method and where it is exactly. I bet your route is not set up correctly and it didn't even get in that exportCSV function. You can add some logs along the way to know exactly where your code stopped working as well

[midnight quartz]

Yeah... It is basically this 20 times

It is just strange because this works in prod no problems, but it does not work here. I have gone back on my word and I am looking into Docker. I hope to get that working tomorrow, but if not it will be in another week (as I am headed out of town)

[zenith totem]

With your error message you can see that your route is wrong. It is trying to call the method @show in the CemeteryController but that doesn't exist. As a matter of fact, it is treating your route as cemetory/:something, basically calling the method show with the parameter export. Something similar to this: https://laravel.com/docs/11.x/controllers#actions-handled-by-resource-controllers

It never got to the export function in the first place