How to add columns to database results in nodejs

Question

I'm using nodejs with mysql and fs to query a database and modify the results by adding a text file to each row. I realize I need to use a promise here but I can't quite get it. I can't figure out what I'm doing wrong. Here is my code so far:

db.query(sql, params, function(err, rows) {
    if(err) {
        res.status(400).json({"error": err.message});
        return;
    }

    var data = {};

    var p = new Promise(function(resolve, reject) {
        rows.forEach(function(row) {
            var filePath = `${contentdir}/${row.strTicketNumber}-${row.strTicketRevision}.txt`;
            row.fileTxt = fs.readFile(filePath, 'utf8', function(error, content) { if(error) return ""; return content; });
            data[row.intSerial] = row;
        });
        resolve(data);
    });

    p.then(function() {
        res.json( data );
    })
});

why not add it directly in the sql string? it will save you a lot of trouble — nbk, Jan 15 '22 at 16:15
The text doesn't exist in the database but in a file in the filesystem. I am expecting multiple rows to be returned, each with it's own fileTXT value. — Todd Hammer, Jan 15 '22 at 16:19
I considered doing that as the files are only 1220 bytes. Easier to back up that way too. — Todd Hammer, Jan 15 '22 at 16:37
You get undefined in fileTxt because you are using fs.readFile, you need to use fs.readFileSync(); fs.readFileSync(filePath, 'utf8'); — Eladiorocha, Jan 15 '22 at 16:38

score 0 · Accepted Answer · answered Jan 17 '22 at 23:46

You can save yourself using a Promise here if you struggle with them by using synchronous Node APIs:

db.query(sql, params, function(err, rows) {
    if(err) {
        res.status(400).json({"error": err.message});
        return;
    }

    var data = {};

    rows.forEach(function(row) {
        var filePath = `${contentdir}/${row.strTicketNumber}-${row.strTicketRevision}.txt`;
        row.fileTxt = fs.readFileSync(filePath, 'utf8');
        data[row.intSerial] = row;
    });

    res.json( data );
});

Note readFileSync instead of readFile

Or, if you really want to use Promises, you can do it like so:

db.query(sql, params, function(err, rows) {
    if(err) {
        res.status(400).json({"error": err.message});
        return;
    }

    var data = {};

    var promises = []

    rows.forEach(function(row) {
        var filePath = `${contentdir}/${row.strTicketNumber}-${row.strTicketRevision}.txt`;

        promises.push(new Promise((resolve, reject) => {
            fs.readFile(filePath, 'utf8', (err, content) => {
                if (err) reject(err)
                row.fileTxt = content
                data[row.intSerial] = row
                resolve()
            });
        }))
    });

    Promise.all(promises).then(() => {
        res.json( data );
    })
});

The main difference between these snippets is that no. 1 reads each text file one-by-one synchronously, while no. 2 reads them "at the same time" (with a few asterisks :p)

Whether this matters to you will depend on how many files you'll be reading, as well as whether you find it significantly easier to read & maintain the first example until you are more comfortable with Promises.

Thanks for the great suggestions. I would think your second method would produce results faster than the first since all the promises resolve at the same time. Please elaborate on "with a few asterisks". — Todd Hammer, Jan 19 '22 at 12:30
Sure. And you're right, the second one is faster. One of the asterisks I was thinking of is that JS is not multithreaded - it won't actually execute, say, two lines of your code at once - but it *can* hand off to node to read all the text files at once and then call callbacks in your code one after the other. [Read more](https://stackoverflow.com/questions/65166535/multithreading-with-javascript-promises) — deeBo, Jan 19 '22 at 12:41
One other thing is that if, say, you're trying to read 1,000 files but you've only got 4 CPU cores, node may only be able to do 4 things at once tops before it has to wait for some of the files to finish so it can move on to reading some more. But in general it would still be correct to say that multiple files are being read at once :) — deeBo, Jan 19 '22 at 12:43

How to add columns to database results in nodejs

1 Answers1

Linked