Duplicate PRIMARY KEY entry despite checking for existence

Question

So I have a pretty simple requirement and am new to Node/Knex and I'm struggling. I have a web server running that accepts order data (e-commerce) for a single order and then writes it to the database. I'm currently testing it locally by sending requests from another local script, which means multiple order requests are arriving per second (I don't think this matters really, but still providing detail).

Here's what the order structure is like:

{
    "id": 123,
    "created_at": "date here",
    "product" : {
      "sku": "sku1",
      "name": "Product 1",
      "description" : "description here",
      // and so on
    },
    "contact": {
      "email" : "me@me.com"
      "first_name" : "First",
      // and so on
    }
}

and here's how I'm processing order data:

// express app
app.post('/write-db-order', (req, res) => {
    const order = req.body.order;

    // check if order already exists
    knex('orders')
    .where('id', order.id)
    .select('id')
    .then((temp) => {
        if (temp.length == 0) {
            saveOrder(order);
        }
    });   

    res.sendStatus(200);
});

the saveOrder() function looks like this:

function saveOrder(order) {
    knex('orders').insert({
        id: order.id,
        title: order.title,
        // other fields
    }).then(() => {
        saveOrderItems(order);
        saveOrderShippingInfo(order);
        saveOrderContact(order);
    }).catch(error => console.log(error));
}

So, the base order details first get inserted into the DB, and then I'm saving the rest of the details. Here's what the saveOrderContact() function is like:

function saveOrderContact(order) {
    let contact = order.contact;

    if(!contact) {
        return;
    }

    knex('contacts')
    .where('email', contact.email)
    .select('email')
    .then((result) => {
        if (result.length == 0) {
            knex('contacts').insert({
                email: contact.email,
                first_name: contact.first_name,
                last_name: contact.last_name,
                company_name: contact.company_name,
                job_title: contact.job_title
            }).then(() => {}).catch(error => { 
                console.log(error);
            });;
        }
    });
}

On a typical trial run, about 1000-1200 orders get sent to this server, of which some of the contacts and products are expected to contain duplicates. That's why in saveOrderContact() I have a check on the primary key (email), testing whether the contact already exists or not.

Anyway, the point is, I'm running into a number of SQL errors that say 'Duplicate entry for PRIMARY KEY email'. It also happens when I try to save products, where the sku is the primary key (I haven't included the code here, though, as it's similar to the function above).

To me, it seems like Knex has weird caching behavior in which it's sending all inserts to the DB in one go and it causing these duplicates. But even then, I'm surprised that having the select check before the insert doesn't work. I'm aware of the async nature of Node, and that's why I added the insert inside the then() but it looks like I'm missing something.

Is this a common problem in Node/Knex? If yes, how do you avoid it? If not, well, please help me out! :-)

Answer 1

If you're hitting the server with a lot of requests in very quick succession, chances are you're encountering a race condition. It's likely that the contacts row is inserted in between checking for it and attempting to insert it.

It's worth noting that these requests:

    }).then(() => {
        saveOrderItems(order);
        saveOrderShippingInfo(order);
        saveOrderContact(order);
    }).catch(error => console.log(error));

will all be fired within a very short time of each other (execution will not wait until the promises are resolved. Another way of expressing the same thing but in strict sequence would be this:

  .then(() => saveOrderItems(order))
  .then(() => saveOrderShippingInfo(order))
  .then(() => saveOrderContact(order))

It is possible to just let the insert fail if the contact already exits. If you define the column with a UNIQUE constraint (in fact, you effectively already have by making it a primary key) it won't allow any duplicate entries. Then you need to deal with the resulting error thrown if a conflict does arise. This leaves it up to the database to be the single 'source of truth'. You do have to do a little introspection to distinguish between errors:

knex('contacts')
  .insert({
    email: 'foo@example.com'
    // ...
  })
  .catch(e => {
    // Example for Postgres, where code 23505 is a unique_violation
    if (e.code && e.code === '23505') {
      return
    }
    console.error('Something bad happened while inserting a contact.')
  })

This silently swallows unique constraint violations. allowing program execution to continue. Unfortunately, it's rather database-specific. It could be cheaper than a query to check for a contact followed by an insert, and reduces the risk of a race condition.

Alternatively, you could use raw SQL (assuming Postgres) which is arguably more elegant:

  const insert = knex('contacts').insert({ email: foo@example.com })
  knex.raw('? ON CONFLICT DO NOTHING', [insert]).then(console.log)