How do I return a value from a row by querying a sqlite3 database?

Question

I am writing some database middleware for some expressjs app that is based on sqlite3. This is my directory structure:

     | app
       | database
          index.js
       | routes
          index.js

database/index.js contains:

module.exports.getLinkById = function(id) {
    Database.get(`SELECT redir FROM root WHERE id = "${id}"`, [], function(err, rows) {
        try {
            var row = rows;
            console.log(row.redir); // successfully logs the redir attr with correct value
            return row;
        } catch (e) {
            throw e;
        }
    });
};

and routes/index.js contains:

var database = require('../database');
console.log(database.getLinkById("afb8")); // undefined

In the database/index.js file, the row based on the id is selected and returned. For debugging, I console.log()ged it and that logs the correct value. However, even thought I returned the row value, routes/index.js treats it as undefined. How do I correctly return the value so I can access it in routes/index.js?

Answer 1

It's an async function that does not return anything.

Simply return a response for express:

app.get("/api/user/:id", (req, res, next) => {
    var sql = "select * from user where id = ?"
    var params = [req.params.id]
    db.get(sql, params, (err, row) => {
        if (err) {
          res.status(400).json({"error":err.message});
          return;
        }
        res.json({
            "message":"success",
            "data":row
        })
      });
});

If you need to run more code alongside it, see: sqlite3 nodejs get value from table

Middleware required function, added to database.js:

// Hack to look like node-postgres
// (and handle async / await operation)
db.query = function (sql, params) {
  var that = this;
  return new Promise(function (resolve, reject) {
    that.all(sql, params, function (error, rows) {
      if (error)
        reject(error);
      else
        resolve({ rows: rows });
    });
  });
};