How to deserialize SQlite to TS class?

Question

Using Sqlite3 from Electron's main package, I am able to create tables, insert rows, and read those rows. How do I instantiate rows read into defined classes? Example:

export const GetAllObjs = (): Obj[] => {
  const query = db.prepare("SELECT * FROM ObjTable");
  const rows= query.all();
  let objs = [] as Obj[];
  for(const row in rows as Obj[]){
    // This object only contains the index number...
    // const obj= JSON.parse(row) as Obj;

    // This does not seem to work...
    objs.push(
      {
        // How do you access the value of each column, such as row.[columnName]?
        id: row.id, 
        name: row.name 
        amount: row.amount, 
      } as Obj
    );
  }
  return objs;
};

// Example table creation (I have verified this works with DBeaver)
export const CreateObjTable = () => {
  const query = db.prepare(`
    CREATE TABLE IF NOT EXISTS ObjTable
    (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT,
      amount INTEGER
    )
  `);
  return query.run();
};

Your advice would be greatly appreciated. It seems odd to me this isn't more clearly spelled out.

Answer 1

Most database operations are asynchronous, so you have to build either a callback-style function, or make it return a Promise.

Furthermore, sqlite3 all() method provides only a callback-style API, whereas it returns the database or statement for chaining:

executes the statement and calls the callback with all result rows. The function returns the Statement object to allow for function chaining.

const GetAllObjs = (): Promise<Obj[]> => {
    const query = db.prepare("SELECT * FROM ObjTable");

    // Must either use a callback-style signature
    // or return a Promise to handle the asynchronous behaviour
    return new Promise((resolve, reject) => {
        let objs = [] as Obj[];

        query.all((err, rows) => {
            //          ^? any[]
            // Loop over an array with `for...of`
            for (const row of rows as Obj[]) {
                objs.push(
                    {
                        // Okay
                        id: row.id,
                        name: row.name,
                        amount: row.amount,
                    } as Obj
                );
            }

            // Resolve with the result, as a callback return will be lost
            resolve(objs);
        })
    });
};

Playground Link