Preventing over-purchasing products by managing Knex.js inventory levels

Question

Preventing over-purchasing products by managing Knex.js inventory levels

Currently, I am in the process of developing an online store for my school's guild organization.

I must admit that I lack experience working with databases and Knex.js is still a bit challenging for me.

An issue arises when multiple users simultaneously add items to their carts - sometimes resulting in more items being added than what is available in the database. It has led me to believe that I might not have fully grasped the concept of transactions and their functionality. Selling excess tickets or products due to this error could potentially put us in a difficult situation. Any guidance on how to handle this scenario better would be greatly appreciated.

The following code snippet exemplifies my attempt at incorporating the transaction:

 private async inventoryToCartTransaction(
    cart: sql.Cart,
    inventoryId: UUID,
    quantity: number = 1,
  ) {
    return this.knex.transaction(async (trx) => {
      const inventory = await trx<sql.ProductInventory>(TABLE.PRODUCT_INVENTORY)
        .where({ id: inventoryId }).first();
      if (!inventory) throw new Error(`Inventory with id ${inventoryId} not found`);
      if (inventory.quantity < quantity) throw new Error('Not enough inventory');
      // More code follows...
    });
  }

This is how the data is structured within the database:

export interface Product {
  id: UUID,
  name: string,
  description: string,
  SKU: string,
  price: number,
  image_url: string,
  max_per_user: number,
  category_id: UUID,
  created_at: Date,
  updated_at: Date,
  deleted_at?: Date,
}
// Additional data structure interfaces listed below...

typescript postgresql graphql knex.js

Answer 1

Answer №1

It looks like the issue lies in your code: you deduct 1 from your inventory (

quantity: inventory.quantity - 1,

), but then you add quantity to the total cart quantity (

total_quantity: cart.total_quantity + quantity,

)

      await trx<sql.ProductInventory>(TABLE.PRODUCT_INVENTORY).where({ id: inventory.id }).update({
        // this is where it happens
        quantity: inventory.quantity - 1,
      });
      await trx<sql.Cart>(TABLE.CART).where({ id: cart.id }).update({
        total_price: cart.total_price + product.price,
        // and also here
        total_quantity: cart.total_quantity + quantity,
      });

Solution

      await trx<sql.ProductInventory>(TABLE.PRODUCT_INVENTORY).where({ id: inventory.id }).update({
        quantity: inventory.quantity - quantity,
      });
      await trx<sql.Cart>(TABLE.CART).where({ id: cart.id }).update({
        // Consider adding `* quantity` for accuracy
        total_price: cart.total_price + product.price * quantity,
        total_quantity: cart.total_quantity + quantity,
      });

Answer 2

It looks like the issue lies in your code: you deduct 1 from your inventory (

quantity: inventory.quantity - 1,

), but then you add quantity to the total cart quantity (

total_quantity: cart.total_quantity + quantity,

)

      await trx<sql.ProductInventory>(TABLE.PRODUCT_INVENTORY).where({ id: inventory.id }).update({
        // this is where it happens
        quantity: inventory.quantity - 1,
      });
      await trx<sql.Cart>(TABLE.CART).where({ id: cart.id }).update({
        total_price: cart.total_price + product.price,
        // and also here
        total_quantity: cart.total_quantity + quantity,
      });

Solution

      await trx<sql.ProductInventory>(TABLE.PRODUCT_INVENTORY).where({ id: inventory.id }).update({
        quantity: inventory.quantity - quantity,
      });
      await trx<sql.Cart>(TABLE.CART).where({ id: cart.id }).update({
        // Consider adding `* quantity` for accuracy
        total_price: cart.total_price + product.price * quantity,
        total_quantity: cart.total_quantity + quantity,
      });

Preventing over-purchasing products by managing Knex.js inventory levels

Answer №1

Similar questions

The data structure '{ variableName: string; }' cannot be directly assigned to a variable of type 'string'

Is there a way to extract the HTMLElement[] type from the DOM?

Using type as an argument in a hook in a similar fashion to how it is

Every time I clear the information, it seems to be instantly replaced with new data. How can I prevent it from constantly refilling?

Include a string in every tuple

When compiling my TypeScript file, I encountered an error stating that a block-scoped variable cannot be redeclared

Issue encountered when attempting to run "ng test" in Angular (TypeScript) | Karma/Jasmine reports an AssertionError stating that Compilation cannot be undefined

Learn the process of sending a delete request to a REST API with Angular

When using the .concat method on an array, props may display as unidentified

How can I use TypeScript to wrap a component in Vue 3?

Error encountered in Node.js OpenAI wrapper: BadRequestError (400) - The uploaded image must be in PNG format and cannot exceed 4 MB

What is the simplest way to incorporate Vue with Typescript, without the need for a complex build setup?

PostgreSQL is indicating that a relation is not present, even though the table itself does exist

Implementing computed properties: A guide to incorporating type setting

Change the type declaration of a list of elements to a list containing those elements organized within a container - Array<Wrapper<T>>

The variable type 'editor.IStandaloneCodeEditor' does not match the parameter type 'monaco.editor.IStandaloneCodeEditor'

Countdown component in Ant Design failing to display correct date

Is it possible to refresh the webpage in Angular when the tab is clicked?

The file is missing the required fields in the Firestore document

Deciphering key-value pairs that are separated by commas