Question 212289 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Making Sequelize Increment Value Unique

I currently am incrementing a column for order number values with the increment() function in Sequelize. ie: .increment("order_no", { by: 1 });

Occasionally a request hits my api at the exact same second and I end up with a duplicate order number value.

Is there any way that I can guarantee that the increment value is unique for an order number? Do I need to wrap the increment in a transaction? Should I have a random timed check after creating the number value to see if there is a duplicate? Not sure of the right way to solve this.

From Sequelizes documentation (https://sequelize.org/docs/v6/core-concepts/model-instances/#incrementing-and-decrementing-integer-values)

In order to increment/decrement values of an instance without running
into concurrency issues, Sequelize provides the increment and
decrement instance methods.

However, while this appears to be working, I still get duplicate values out when being utilized.

Tags:

2

Answers


  1. 0

    I think you can try to catch the error if duplicate order id occurs and retry the operation and generate a new order id.

    Login or Signup to reply.
  2. 0

    If I am getting this right,
    You have an orders model/table that tracks orders. Each newOrder request that hits your API asks sequelize(Order model) to manually increment the order_no column. If two such requests hit the API at the same time, they insert the same value into your orders table twice.

    So you want to add "unique key" restrictions to this ‘order_num’ column but this is not an efficient way to uniquely identify your orders. Whether you use increment...by or increment/decrement functions, sequelize will ultimately query the database with the same values.

    In my opinion, you shouldn’t be doing this.

    Instead, add a order_no column, auto-increment the ‘order_no’ column. Sequelize supports autoincrement functionality for all dialects.
    Check out this post: Auto increment id with sequelize in MySQL.

    To uniquely identify "orders", generate a random unique ID for each incoming order. It’s better than trying to have unique integral "order_no".
    Check out: https://dev.to/gulshanaggarwal/npm-packages-to-generate-unique-ids-for-your-next-project-1p3b.

    With unique "order_id"s, You don’t even need the order_no autoincrement column.

    To count the number of orders in certain day, combine Sequelize’s model querying methods like findAndCountAll plus where clauses based on a the createdAt timestamps.

    You can do other tasks you need like sorting, counting the number of orders, finding the latest or earliest orders, etc.

    These are well documented in the sequelize docs website.

    Consider, redesigning your schema for better efficiency.

    If this doesn’t help, you can help us help you more by sharing the ‘model instance’ code, and the increment code in your API.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search