Postgres / Typeorm / Express: how to keep database consistent for simultaneous requests from multiple clients?

Question

I'm using Express / Typescript / Typeorm / Postgres to build the backend for my app.

Say I have a table called Restaurant with following columns:

restaurant_id

order (Integer)

quota (Integer)

The idea is that each restaurant has an upper limit for the number of orders it can receive. Multiple clients can send an order to a restaurant, thus incrementing the order by one.

If a restaurant is currently:

id: 1
order : 9
quota : 10

and two clients send an order to it at the same time, there is a problem.

I want it to behave such that whichever request arrives first, successfully increment the order value by one, resulting:

id: 1
order : 10
quota : 10

And the client whose request arrives late will fail to increment the value, and will get an error back in the response, noting that the quota is all used up.

A few thoughts:

1. In Typeorm / Postgres, is there a way to set an upper limit to a column of integer value? Such that if the value is set to some value exceeding the ceiling, it will throw an error?

2. I'm thinking in the direction of limiting the endpoint which performs the increment to be called only once at a time. Given that point 1 above would work, I still want to disable parallel execution of the endpoint for some other situations.

(This is not running code, just some reference):

app.put('/restaurant_order/:restaurantId', async (req, res) => {
  const instance = await Restaurant.findOne(restaurantId);
  if (instance.order < instance.quota){
    await getConnection()
      .createQueryBuilder()
      .update(Restaurant)
      .set({ order: () => `order + 1` })
      .where("id = :id", { id: restaurantId })
      .execute();
  }
  
  res.respond({
      ... 
  })
  
});

The question then becomes:

How can I have this limit in Express? Can I configure Express such that app.put('/restaurant_order/:restaurantId', ...) forbids parallel calls, and only allow one call at a time for each restaurantId?

Answer 1

First, read the relevant part of the docs: https://www.postgresql.org/docs/8.3/ddl-constraints.html

Example:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

In you case, this would be

order integer CHECK (order < quota)