javascript - heap out of memory

i have api in nodejs for read data from mysql database and write it into csv file and download file after complete write in csv file. when i have 1.7 million records in database it is working but now i have 5 million record it is give me error

javascript - heap out of memory

my api code:

exports.exportdatatocsv = async (req, res) => {
    con.query(
        "SELECT sender_name, table_name FROM sender_tbl where sender_name ",
        function (error, data) {
            if (error) {
                console.error(error);
                res.status(500).send("Internal Server Error");
                return;
            }

            var mysqlData = JSON.parse(JSON.stringify(data));
            var workbook = new ExcelJS.stream.xlsx.WorkbookWriter({ stream: res });

            mysqlData.forEach((sender, index) => {
                var worksheet = workbook.addWorksheet(
                    sender.sender_name.substring(0, 31)
                );

                con.query(
                    `SELECT * FROM ${sender.table_name} ORDER BY id DESC`,
                    function (error, tableData) {
                        if (error) {
                            console.error(error);
                            res.status(500).send("Internal Server Error");
                            return;
                        }

                        var fileHeader = [
                            "message",
                            "info",
                            "credit/debit",
                            "amount",
                            "netbal",
                        ];
                        worksheet.addRow(fileHeader);

                        tableData.forEach((row) => {
                            worksheet.addRow([
                                row.message,
                                row.info,
                                row.isdebit ? "debit" : "credit",
                                row.amount,
                                row.netbal,
                            ]);
                        });

                        if (index === mysqlData.length - 1) {
                            workbook
                                .commit()
                                .then(function () {
                                    res.status(200).end();
                                })
                                .catch(function (err) {
                                    console.error(err);
                                    res
                                        .status(500)
                                        .send("Error occurred while generating Excel file");
                                });
                        }
                    }
                );
            });
        }
    );
};

i have implement stream but still not working and i do not have to much idea regarding stream

Answers

When dealing with a large amount of data, especially when reading from a database and writing to a file, it's essential to use stream-based processing to avoid memory issues. Stream-based processing allows you to process data in smaller, manageable chunks rather than loading everything into memory at once.

Here's how you can modify your code to use streams for reading data from the database and writing it to the Excel file:

const ExcelJS = require("exceljs");
const fs = require("fs");

exports.exportdatatocsv = async (req, res) => {
    try {
        const senderData = await new Promise((resolve, reject) => {
            con.query(
                "SELECT sender_name, table_name FROM sender_tbl",
                (error, data) => {
                    if (error) {
                        console.error(error);
                        reject(error);
                    } else {
                        resolve(data);
                    }
                }
            );
        });

        const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
            stream: res,
        });

        for (const sender of senderData) {
            const worksheet = workbook.addWorksheet(
                sender.sender_name.substring(0, 31)
            );

            const tableData = await new Promise((resolve, reject) => {
                con.query(
                    `SELECT * FROM ${sender.table_name} ORDER BY id DESC`,
                    (error, data) => {
                        if (error) {
                            console.error(error);
                            reject(error);
                        } else {
                            resolve(data);
                        }
                    }
                );
            });

            worksheet.addRow(["message", "info", "credit/debit", "amount", "netbal"]);

            for (const row of tableData) {
                worksheet.addRow([
                    row.message,
                    row.info,
                    row.isdebit ? "debit" : "credit",
                    row.amount,
                    row.netbal,
                ]);
            }
        }

        workbook.commit()
            .then(() => {
                res.status(200).end();
            })
            .catch((err) => {
                console.error(err);
                res.status(500).send("Error occurred while generating Excel file");
            });
    } catch (error) {
        console.error(error);
        res.status(500).send("Internal Server Error");
    }
};

In this modified code:

1. We use await with Promise to ensure asynchronous operations complete before proceeding.
2. We loop through the sender data and table data using for...of loops, which allows us to process one chunk of data at a time without loading everything into memory simultaneously.
3. We use Promise to execute the database queries asynchronously, ensuring that we don't block the event loop.
4. We handle errors using try...catch blocks to gracefully handle any exceptions that occur during the process.
5. We use stream-based processing provided by ExcelJS.stream.xlsx.WorkbookWriter to write data to the Excel file incrementally, preventing memory issues.

This approach should help mitigate memory-related errors when dealing with large datasets. However, keep in mind that processing large amounts of data can still take time, so be patient and monitor the performance of your application.