how to create api route that will send a CSV file to the frontend in Next.js

Question

As far as I know (correct me if i'm wrong please) the flow of downloading a file should be that the frontend make a call to an api route and everything else is going on on the server.

My task was to read from firestore and write it to the CSV file, I populated the CSV file with the data and now when I try to send it to the frontend only thing that is in the file after the download it the first line containing headers name and email (the file that was written on my computer is correctly willed with the data). This is my route

import { NextApiHandler } from "next";
import fs from "fs";
import { stringify } from "csv-stringify";
import { firestore } from "../../firestore";
import { unstable_getServerSession } from "next-auth/next";
import { authOptions } from "./auth/[...nextauth]";

const exportFromFirestoreHandler: NextApiHandler = async (req, res) => {
  const session = await unstable_getServerSession(req, res, authOptions);

  if (!session) {
    return res.status(401).json({ message: "You must be authorized here" });
  }

  const filename = "guestlist.csv";
  const writableStream = fs.createWriteStream(filename);
  const columns = ["name", "email"];
  const stringifier = stringify({ header: true, columns });

  const querySnapshot = await firestore.collection("paprockibrzozowski").get();
  await querySnapshot.docs.forEach((entry) => {
    stringifier.write([entry.data().name, entry.data().email], "utf-8");
  });
  stringifier.pipe(writableStream);
  const csvFile = await fs.promises.readFile(
    `${process.cwd()}/${filename}`,
    "utf-8"
  );

  res.status(200).setHeader("Content-Type", "text/csv").send(csvFile);
};

export default exportFromFirestoreHandler;

since I await querySnapshot and await readFile I would expect that the entire content of the file would be sent to the frontend. Can you please tell me what am I doing wrong?

Thanks

Do you actually need the CSV file on disk? If you don't, you could pipe the CSV strinigfying straight to the response. To pipe to the response, see https://stackoverflow.com/a/67579901/4166407 As @Nelloverflow mentioned though, there are a few other things wrong with the current flow — Sophie, Jul 18 '22 at 11:20

score 2 · Answer 1 · answered Jul 18 '22 at 14:04

If anyone will struggle with this same stuff here is the answer base on the @ Nelloverflowc thank you for getting me this far, hoverver files not always were populated with data, at first I tried like so

 stringifier.on("close", async () => {
    const csvFile = fs.readFileSync(`${process.cwd()}/${filename}`, "utf-8");
    res
      .status(200)
      .setHeader("Content-Type", "text/csv")
      .setHeader("Content-Disposition", `attachment; filename=${filename}`)
      .send(csvFile);
  });
  stringifier.end();

the api of https://csv.js.org/ must have changed becuase instead of on.('finish') it is on close now, so reading file sync did the job regarding always getting the file populated with the correct data, but along with it there was an error

API resolved without sending a response for /api/export-from-db, this may result in stalled requests.

the solution to that is to convert file into readable stream like so

  try {
    const csvFile = fs.createReadStream(`${process.cwd()}/${filename}`);
    res
      .status(200)
      .setHeader("Content-Type", "text/csv")
      .setHeader("Content-Disposition", `attachment; filename=${filename}`)
      .send(csvFile);
  } catch (error) {
    res.status(400).json({ error });
  }

Here is the tread and the discussion that helped me

Node.js send file in response

score 1 · Accepted Answer · answered Jul 18 '22 at 11:14

1

The await on that forEach is most definitely not doing what you expect it to do, also you probably shouldn't use await and forEach together

Either switch to using the Sync API for the csv-stringify library or do something along these lines (assuming the first .get() actually contains the actual values from a promise):

[...]
  stringifier.pipe(writableStream);
  stringifier.on('finish', () => {
    const csvFile = await fs.promises.readFile(
       `${process.cwd()}/${filename}`,
        "utf-8"
     );
     res.status(200).setHeader("Content-Type", "text/csv").send(csvFile);
  });
  for (const entry of querySnapshot.docs) {
      stringifier.write([entry.data().name, entry.data().email], "utf-8");
  );
  stringifier.end();

[...]

answered Jul 18 '22 at 11:14

Nelloverflow

1,511
1
11
15

thanks @Nelloverflow that works! Can you please explain that to me because I have experimented a little and ok I understand that on 'finish' we send back the data but why does for of loop work and forEach does not? I have tried to replace it with my forEach and I got a one liner again, also when I moved for loop above .on 'finish' I got a one liner. Could you please shed some light on this flow? :) i'm really curious. Thanks! – seven Jul 18 '22 at 11:44
@Nelloverflowc actually after more experimnts it not always return proper data :) sometimes it does and sometime it return a one liner and a warning in the console saying that API resolved without sending a response for /api/export-from-db, this may result in stalled requests. – seven Jul 18 '22 at 12:19

how to create api route that will send a CSV file to the frontend in Next.js

2 Answers2