Sqlite .all() function returns an undefined promise. How can I use the result further?

I am just starting to work with JS and SQLite. And could not find any help for my specific question.

I want to keep a return to use it as a foreign key in a second table. This is my function:

async function getIdbyName(table, row, name) {
  let nameNeu = '"' + name + '"';
  let sql =
    "SELECT id as print FROM    " +
    table +
    " WHERE " +
    row +
    " = " +
    nameNeu +
    " LIMIT 1;";
  // await db.get(sql, (err, row) => {
  //   console.log(row.print);
  //   return row;
  // });

  return await db.get(sql);
}

getIdbyName("...", "...", "...")
  .then(function (value) {
    console.log("Success!", value);  // <-- prints: Success! undefined
  })
  .catch(function (err) {
    console.log("Caught an error!", err);
  });

console.log(getIdbyName("r_Tag", "r_Tag", "test"));   //<-- shows me a Promise

What do I have to do so that promise does not stay undefined outside of the function?

Rest of the code:

var sqlite3 = require("sqlite3").verbose();

let db = new sqlite3.Database("./assets/db/test.db", (err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log("Connected to the SQlite database.");
});

My other function just creat some strings and I run a few times db.run(…) to add some tables.

Answer

To put it more plainly, your getIdByName function never returns anything. You need to return the value you get back from await db.get(...). Once you do that, when you call getIdByName, you should get your response from the database.

You should also know that your code is susceptible to SQL injection, which is a major security vulnerability. Instead of concatenating a string, you should use a prepared statement.

async function getIdbyName(table, row, name) {
  return await db.get(sql);
}

---

Update: Promise Wrapper for SQLlite – Aug 1, 2020

Based on this blog post, it seems it’s not possible to do native async/await using sqlite3. However, you can write a wrapper function around db.all to return a promise, which will allow you to use async/await. Note the use of ? in the SQL statement, which will be replaced by the values of the array in the second argument following the same order. For more help with parameterized queries, read the params bullet point in the documentation here.

const sqlite3 = require("sqlite3").verbose();

const db = new sqlite3.Database("./assets/db/test.db", (err) => {
  if (err) {
    return console.error(err.message);
  }
  console.log("Connected to the SQlite database.");
});

db.query = function (sql, params = []) {
  const that = this;
  return new Promise(function (resolve, reject) {
    that.all(sql, params, function (error, result) {
      if (error) {
        reject(error);
      } else {
        resolve(result);
      }
    });
  });
};

async function getIdByName(table, name) {
  // assemble sql statement
  const sql = `
      SELECT id
      FROM ?
      WHERE name = ?;
    `;
  return await db.query(sql, [table, name]);
}

// need async to call
(async () => {
  const result = await getIdByName('books', 'my_name');
  console.log(result);
})();