Node sqlite3: Trying to write a function to return an array of rows

Question

The context of my problem is that I'm trying to do a website signup page, and within that, I'm trying to check whether an email is already included in the database. To do this I'm trying to write a function which will return all the emails in the database so I can then do a

if (emails.includes(new_user_email) {
    return error;
 }

kind of thing.

Essentially I'd like something that can return a boolean telling me if the email already exists. So returns an array I can check, or even just searches for the email in the database, but that waits before returning true or false.

I've tried the function below to return an array of emails, although I understand this doesn't work due to the asynchronous nature of sqlite3. My issue is that I can't get a function that waits for the sql code to run before returning a result.

function get_all_emails() {
    let emails = []
    db.all("SELECT email FROM users", [], (err, rows) => {
        if (err) {
            throw err;
        }
        rows.forEach((row) => {
            emails.push(row.email);
        });
        return emails;
    });
}

Samuel Bolduc · Accepted Answer · 2019-05-23 15:18:27Z

1

If you're using a recent version of Node.js (7.6+), I would advise you switch to a library that supports promises instead of only callbacks. It will make it easy to handle asynchronous operations using async / await. A good candidate would be the sqlite package.

Once you have switched to it (very easy to do, they are similar), you simply have to make your get_all_emails function async. Here's how it would look:

async function get_all_emails() {
    const emails = (await db.all("SELECT email FROM users", [])).map((row) => row.email);

    return emails;
}

The code above retrieves the rows using await db.all and maps them to emails to create the array. You can call that function from somewhere else by either using await get_all_emails() (if where you call it from is an async function) or by using get_all_emails().then((emails) => {...}).catch((err) => {...}).

That said, the best thing to do to accomplish what you want would be to only retrieve the rows matching the email and checking whether the retrieved rows length equals 0.

async function is_email_available(email) {
  const rows = await db.get('SELECT id FROM users WHERE email = ?', email);
  return rows.length === 0;
}

This would return true if the email is available, and false if not.

Hope this helps!

answered May 23, 2019 at 15:18

Samuel Bolduc

19.3k8 gold badges37 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2411182 Over a year ago

Thanks for your reply! My lecture suggested using sqlite over sqlite3 for this exact reason - maybe time to back down from my stubbornness and make the change.

Collectives™ on Stack Overflow

Node sqlite3: Trying to write a function to return an array of rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related