how can i get Json output from Sqlite3

Question

Hello i wrote a SQL for getting information from 2 tables but when i want to return the data i don't know the index to do it at the right moment. what should i do and is there anyway to get Json response from the first instead of each method?

my function:

const searchAntibodies = (
  index: number,
  amount: number,
  information: string,
  startDate: string,
  endDate: string,
) => {
  return new Promise<Antibodies[]>((resolve, reject) => {
    let antibodies: Antibodies[] = [];
    db.serialize(() => {
      db.each(`SELECT id, name as antibodyName FROM Antibodies WHERE
              id IN 
              (SELECT id FROM Antibodies WHERE name LIKE ?
              UNION all
              SELECT antiId FROM AssignedColors WHERE name LIKE ?
              UNION all
              SELECT antiId FROM AssignedReactivities WHERE name LIKE ?)
              AND dateOfCreation >= ? AND dateOfCreation <= ?
              ORDER BY dateOfCreation DESC LIMIT ? OFFSET ?;`
        , [`%${information}%`, `%${information}%`, `%${information}%`, startDate, endDate, amount, index]
        , (err, antibody: Antibodies) => {
          if (err) {
            reject(err.message);
          } else {
            db.all('SELECT name, locations, colorId FROM AssignedColors WHERE antiId = ?', [antibody.id], (err, colors) => {
              if (err) {
                reject(err.message);
              } else {
                antibody.colors = colors;
                antibodies.push(antibody);
                if (antibodies.length === 10) {
                  resolve(antibodies)
                }
              }
            });
          }
        });
    });
  });
}

my expected result:

[   {
    id: 1999,
    antibodyName: 'Antibody 1999',
    colors: [ [Object], [Object], [Object], [Object], [Object] ]   },   {
    id: 1995,
    antibodyName: 'Antibody 1995',
    colors: [ [Object], [Object], [Object], [Object], [Object] ]   },   {
    id: 1994,
    antibodyName: 'Antibody 1994',
    colors: [ [Object], [Object], [Object], [Object], [Object] ]   },   {
    id: 1993,
    antibodyName: 'Antibody 1998',
    colors: [ [Object], [Object], [Object], [Object], [Object] ]   },   {
    id: 1997,
    antibodyName: 'Antibody 1997',   } ]

Answer 1

Ok after searching a lot found the answer. Actually if you don't set types for the first callback function, you can get a second callback function for when it is completed. Here is my result:

const getAntibodies = (
  index: number,
  amount: number,
  startDate: number,
  endDate: number,
  orderBy: string,
  orderType: string,
) => {
  return new Promise<Antibodies[]>((resolve, reject) => {
    let antibodies: Antibodies[] = [];
    let totalCount: number;
    let sql = 'SELECT id, name as antibodyName, dateOfCreation FROM Antibodies ';
    let params = [amount, index];
    if (startDate !== 0 || endDate !== 0) {
      sql += `WHERE dateOfCreation >= ? AND dateOfCreation <= ? 
                ORDER BY ${orderBy} ${orderType} LIMIT ? OFFSET ?;`;
      params.unshift(startDate, endDate);
    } else {
      sql += `ORDER BY ${orderBy} ${orderType} LIMIT ? OFFSET ?;`;
    }
    db.serialize(() => {
      db.each(sql,
        params
        , async (err, antibody) => {
          if (err) {
            reject(err.message);
          } else {
            await getColors(antibody.id).then((colors) => {
              antibody.colors = colors;
              antibodies.push(antibody);
              if (antibodies.length === totalCount) {
                resolve(antibodies);
              }
            }).catch((err) => {
              reject(err);
            });
          }
        }, (err, count) => {
          if (err) {
            reject(err.message)
          } else {
            if (count === 0) {
              resolve(antibodies);
            } else {
              totalCount = count;
            }
          }
        });
    });
  });
}

const getColors = (id: number) => {
  return new Promise<Color[]>((resolve, reject) => {
    db.serialize(() => {
      db.all('SELECT name, locations, colorId FROM AssignedColors WHERE antiId = ?', [id], (err, colors: Color[]) => {
        if (err) {
          reject(err.message)
        } else {
          resolve(colors);
        }
      });
    });
  });
}