How do I get an array of objects from Deno's sqlite library?

Question

I'm trying to get user.foo and user.bar instead of an array of values from sqlite:

    let user: any = await db.query('SELECT id, email, hashed_password FROM users WHERE email = ?', [body.email]);

    if (!user || !user.length) {
      context.response.status = 400;
      context.response.body = { message: "User not found" };
      return;
    }   
    user = user[0];
    console.log(user);
    console.log(body.password, user[2]);
    const comparison = await bcrypt.compare(body.password, user[2]);
    console.log('comparison: ', comparison);

Is this possible?

edit: I am still getting an error here that query didn't return any rows even though its in a try catch (and the rows DO exist)

    try {
      const query = db.prepareQuery<[number, string]>("SELECT id, email, hashed_password FROM users WHERE email = ?",
      [body.email],
     );
      user = query.oneEntry();
    } catch(err) {
      console.error(err);
      context.response.status = 400;
      context.response.body = { message: "User not found" };
      return;
    }

    console.log('user: ', user);

What is "Deno's sqlite library"? What is the URL of the module that you're importing to create `db`? — jsejcksn, Oct 31 '21 at 15:22
You didn't include the error you said you're seeing. It'd be helpful if you provide a [Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) in your question. (e.g. You can copy, paste, and save the TypeScript file contents I've provided in my answer as a file: then run it, and you'll see the same output.) This will help anyone who wants to help you troubleshoot your issue. — jsejcksn, Nov 01 '21 at 04:59
added error. i think its how i bind ? to prepareQuery. I think i'm doing that wrong. — chovy, Nov 01 '21 at 05:03
That most recent version of your question that I see is [revision 3](https://stackoverflow.com/revisions/69784761/3), which doesn't include any information about the error. — jsejcksn, Nov 01 '21 at 05:19

score -1 · Answer 1 · answered Oct 31 '21 at 17:56

Assuming you mean the module at https://deno.land/x/sqlite, you can use the following methods to receive object rows from your queries:

db.queryEntries
one of the *Entries methods on a PreparedQuery (such as pq.allEntries, pq.iterEntries, pq.oneEntry)

You can use either positional or named parameters in your query statements and arguments. Read more at https://www.sqlite.org/lang_expr.html#parameters.

Here is a complete, working example, adapted from your question's code:

so-69784761.ts

import {DB, PreparedQuery, Row, RowObject} from 'https://deno.land/x/sqlite@v3.1.1/mod.ts';

const db = new DB();
const preparedQueries: PreparedQuery[] = [];

function initialize (): void {
  db.query(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    hashed_password TEXT NOT NULL
  );
  `);
}

function cleanup (): void {
  // All prepared queries need to be finalized before closing the db
  for (const pq of preparedQueries) pq.finalize();
  db.close();
}

type DBUser = {
  id: number;
  email: string;
  hashed_password: string;
};

function main () {
  initialize();

  // The first two generics don't matter: we don't receive or use the return value
  const insertUserQuery = db.prepareQuery<Row, RowObject, Omit<DBUser, 'id'>>(`
  INSERT INTO users (email, hashed_password)
  VALUES (:email, :hashed_password);
  `);
  preparedQueries.push(insertUserQuery);

  for (const user of [
    {email: 'a@example.com', hashed_password: 'a123'},
    {email: 'b@example.com', hashed_password: 'b123'},
  ]) insertUserQuery.execute(user);

  const findUserByEmailQuery = db.prepareQuery<
    [DBUser['id'], DBUser['email'], DBUser['hashed_password']],
    DBUser,
    [DBUser['email']]
  >(`
  SELECT id, email, hashed_password
  FROM users
  WHERE email = ?;
  `);
  preparedQueries.push(findUserByEmailQuery);

  function findUserByEmail (email: DBUser['email']): DBUser | undefined {
    try {
      return findUserByEmailQuery.oneEntry([email]);
    }
    catch {
      return undefined;
    }
  }

  let user = findUserByEmail('a@example.com');
  console.log(user?.email, user?.id, user?.hashed_password);

  user = findUserByEmail('b@example.com');
  console.log(user?.email, user?.id, user?.hashed_password);

  cleanup();
}

main();

deno run so-69784761.ts
a@example.com 1 a123
b@example.com 2 b123

This seems overly complicated. Does the `.one()` and `columns as keys` feature only work with prepared statements? Also what does this `preparedStatements` array do? I don't see it being executed anywhere. — chovy, Nov 01 '21 at 03:52
Regarding methods: the [types documentation](https://doc.deno.land/https/deno.land/x/sqlite@v3.1.1/mod.ts) contains all those references. I don't know what you mean by "`columns as keys` feature". Regarding `preparedStatements`: if you mean the `preparedQueries` array: it's used in the `cleanup` function to finalize the prepared queries, which must be finalized before closing the db cleanly. — jsejcksn, Nov 01 '21 at 03:59
seems to always throw an error when I try it: see updated question — chovy, Nov 01 '21 at 04:39

score -1 · Answer 2 · answered Nov 01 '21 at 05:10

    let user: any;

    try {
      const query = db.prepareQuery<string>("SELECT id, email, hashed_password FROM users WHERE email = :email",
     );
      user = query.oneEntry({ email: body.email });
      query.finalize();
    } catch(err) {
      console.error(err);
      context.response.status = 400;
      context.response.body = { message: "User not found" };
      return;
    }

    console.log('user: ', user);

How do I get an array of objects from Deno's sqlite library?

2 Answers2