Last Updated: 2024-04-26

I inherited a script to save certain data available on APIs to a database. Initially it just operated on the stripe_deals table

function getDealsOnstripe() {
  var query = "select * from stripe_deals where dealId is not null"

  mysqlConnection.query(query, (error, result, fields) => {
    result.forEach(async row => {
      const dealId = row.dealId
      const productId = await fetchHubspotDetails(dealId)
      updateDb(row.id, productId)
    })
  })
}

function updateDb(id, productId) {
  var query = `UPDATE stripe_deals SET productId = ? WHERE id = ?`

  mysqlConnection.query(query, [productId, id], (error, results, fields) => {
    console.log("Updated row: ", id, " with", productId)
  })
}

That worked fine, so later I expanded it to operate on another table, the bank_deals table. I wrote the following, re-using the updateDb function:

function getBankDeals() {
  var query = "select * from deals where dealId is not null"

  mysqlConnection.query(query, (error, result, fields) => {
    result.forEach(async row => {
        const dealId = row.dealId
        const productId = await fetchHubspotDetails(dealId)
        updateDb(row.id, productId)
    })
  })
}

I ran it late at night and the logs showed a stream of successes:

Updated row:  693  with 15
Updated row:  695  with 14
Updated row:  696  with 14
Updated row:  697  with 16
Updated row:  698  with 15
Updated row:  699  with 14
Updated row:  700  with 16
Updated row:  701  with 38
Updated row:  702  with 38
Updated row:  703  with 38
Updated row:  704  with 38
Updated row:  705  with 19
Updated row:  707  with 58

The next morning, I noticed something strange: the bank deals table had no productIds. What happened?

The issue, which was really dumb, was that my updateDb function wrote to the wrong table, the stripe one, clobbering my old data! I should have given the updateDb function a parameter for the specific DB table to write to.

function updateDb(id, productId, table) {
  var query = `UPDATE ${table} SET productId = ? WHERE id = ?`

  mysqlConnection.query(query, [productId, id], (error, results, fields) => {
    console.log("Updated row: ", id, " with", productId)
  })
}

Lessons

• Do not pay too much attention to log content when at the early stage of development as it can deceive you. They are a bit like comments.
• Instead look end-to-end and check if the output data is correct and full
• Before running code that modifies data, read through from end-to-end and check that everything makes sense - especially table and column names
• Also: whenever re-using code as a basis via copy and paste, mark all the bits that need to vary with comments.