how to avoid insert duplicate record in node, sqlite [knex, objectionJS]

Question

I'm pulling data from an api and storing into the database, here is the values I fetched from api:

const optAdThisMonthsResult =  [
            [ 'google.com', 'Display', '2021-02-01', 3, 48, 76 ],
            [ 'google.com', 'a1.php', '2021-02-01', 94, 31, 42 ],
        ]

After fetching I'm storing it in my database:

for (const data of optAdThisMonthsResult) {

        //1. Assign values to store
        let rowData = {
            url: data[0],
            tags: data[1],
            date: data[2],
            value1: data[3],
            value2:data[4],
            value3: data[5],
        };

        //2. Store to database
        let ret = await OptAd.query().insert(rowData);
        console.log(
            `Inserted OptAd data with return code: ${require("util").inspect(ret)}`
        );
    }

Every time I run this script it also stores the duplicate values which are already present in the database, how can I build a test for this if the data is already present don't insert and only update this value1 value2 value3

Answer 1

first try to update using Knex and if it returns zero rows updated try insert

for (const data of optAdThisMonthsResult) {
  //1. Assign values to store
  let rowData = {
    url: data[0],
    tags: data[1],
    date: data[2],
    value1: data[3],
    value2: data[4],
    value3: data[5],
  };

  // get count first
  const result = await OptAd.query()
    .count("tags as count")
    .where({ url: data[0], taga: data[1], date: data[2] })
    .first();
  try {
    if (result.count === 0) {
      // row not present insert data
      let ret = await OptAd.query().insert(rowData);
      console.log(
        `Inserted OptAd data with return code: ${require("util").inspect(ret)}`
      );
    } else {
      // row already presnt do update
      // checking if its not updated
      const { count } = await OptAd.query()
        .count("tags as count")
        .where(rowData)
        .first();
      if (count === 0) {
        const updated = await OptAd.query()
          .update(rowData)
          .where({ url: data[0], taga: data[1], date: data[2] });
        console.log("updated");
      } else {
        console.log("Not Changed");
      }
    }
  } catch (error) {
    console.log(error.message);
  }
}