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

Question

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.

Does this answer your question? [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — zero298, Jul 28 '20 at 17:02
2 things: 1. `getIdbyName` doesn't `return` anything in its body 2. See the linked duplicate to understand how you use the value within a promise further. — zero298, Jul 28 '20 at 17:04
@zero298 tried a lot of things with return fo. But with and without it was the same. Also console.log(test) shows me a "Promise" (I think because of db.all (?)). But its empty. — Amaroks, Jul 28 '20 at 17:52

technogeek1995 · Accepted Answer · 2020-08-01T23:16:18.440

0

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);
})();

edited Aug 01 '20 at 23:16

answered Jul 29 '20 at 18:10

technogeek1995

3,185
2
31
52

Hey thanks for that response. This make sense to me and I tried it. A few things: VSCode mention that await has no effect. With ```.then``` I get a DB but I cant work with it. ```.print``` gave me 'undefined'. Im not sure if asyn works because the console prints ```console.log("Success!", value);``` before ```console.log(row.print);``` [img of console](https://abload.de/image.php?img=imgx3k14.jpg) – Amaroks Jul 30 '20 at 10:15
Sorry that was an oversight on my part. If you're using async/await in the function, you wouldn't use a callback - just remove the callback. I edited the answer. – technogeek1995 Jul 30 '20 at 13:25
Ok Im still lost. I used what you told me and still get the message: await has no effect on the type of this expression. ```console.log(getIdbyName("...", "...", "..."));``` shows me a promise. But I cant access the value I want. It seems to be undefined. – Amaroks Jul 30 '20 at 16:58
Can you update the question with your full source code? – technogeek1995 Jul 30 '20 at 17:27
Well there is not much. Just some "sourroundings" (electron, some html pages etc.). The db.js in question is complete. – Amaroks Jul 30 '20 at 18:28
Based on [this blog post](https://blog.pagesd.info/2019/10/29/use-sqlite-node-async-await/), it seems like async/await is not natively possible with the NodeJS SQLlite module. You have to write a wrapper function that exposes a promise so you can use async/await. I will update the answer. – technogeek1995 Aug 01 '20 at 22:45

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

1 Answers1