nodejs npm mysql return single row handle

Question

Im using node and npm mysql to do some database work.

Is there any way to avoid using the result[0] , if i know Im only going to receive a single row?

connection.query({
    sql: "SELECT spend FROM `account` WHERE `name` = ? order by date desc limit 1",
    values: [market.name]
    }, function (error, results, fields) {
        market.fee = results[0].age;
        resolve(market);
    });

Answer 1

The callback function of connection.query returns three values out of which the second one, is the resultset of query and hence an array of values.

Therefore, you must use result[0] even if you are sure that the resultset would contain just a single record.

Moreover, its somewhat query's own specification that decides what kind of data is supposed to be returned. mysql's SELECT is made to work this way(though you can limit the records) unlike mongodb's db.collection.findOne() where the query itself know it'll always return a single record

Answer 2

You can also use array destructuring to unpack the first row from the results parameter:

const sql = "SELECT age FROM `account` WHERE `name` = ? order by date desc limit 1";

connection.query({ sql, values: [market.name] }, function (error, [account], fields) {
    market.fee = account.age;
    resolve(market);
});

Or go wild and throw some object destructuring into the mix:

const sql = "SELECT age FROM `account` WHERE `name` = ? order by date desc limit 1";

connection.query({ sql, values: [market.name] }, function (error, [{ age }], fields) {
    market.fee = age;
    resolve(market);
});

Another refactor, does the same thing but uses a concise body arrow function and spreads market into a new object which has fee, mapped in the input parameters [{ age: fee }].

connection.query(
  { 
    sql: "SELECT age FROM `account` where `name` = ? order by date desc limit 1", 
    values: [market.name] 
  }, 
  (error, [{ age: fee }], fields) => resolve({ ...market, fee })
);