Javascript/Sequelize/MySql Help with Select and Update in same query

Good Day Experts!

I have quite the dilemma.   I am working on a Javascript project using express, sequelize and mysql.  Generating an order is part of the functionality.  The database has a header and detail table.  I have a separate table that holds the order number.  When a new order is placed, I would like to grab the current order number in the table then increment it.  Then use that current order number in my posts for the header and detail inserts.

Is this possible to achieve using the technologies I am using?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Ideally you should be using the DB to generate the order numbers with an auto increment. If this is not suitable you will need to implement some synchronisation code to ensure you don't have a collision on order number when more than one request is made to the server at the same time.

Having said that - are you asking if it is possible to query a MySQL database from Express - if so the answer is yes.
You should let Sequelize managing the increment and the fact order number must be unique.
below we have, an orderId for "internal" use, you don't need to show it to client side and orderStrPart and orderNumber to represent your order number.
more information on this page :

const Project = sequelize.define('Orders', {
 orderId: { type: Sequelize.INTEGER, unique: true, autoIncrement: true },
 orderStrPart: { type: Sequelize.STRING(3),  unique: 'orderNumber' },
 orderNumPart: { type: Sequelize.INTEGER, unique: 'orderNumber', autoIncrement: true },

Open in new window

you should create order after the user validate a quote or its order, not before, else you are going to increment the order number too quickly each time a user start an order but don't necessarily end it.
Jimbo99999Author Commented:
Hello. I understand I could use the auto-increment in the db.  But the problem is I need to insert into the OrderHeader table and then use the same orderid when I insert into the OrderDetail table.  Is there a better way than generating the orderid first so I could use it on both inserts?

I understand and have been querying mysql from express.  But I was wanting to select the value in the table then update(increment) it all in one statement.  

You can do One-To-One associations  :
OrderDetail.belongsTo(OrderHeader); / Will add a orderHeaderId attribute to OrderDetail to hold the primary key value for OrderHeader

Open in new window

Julian HansenCommented:
The usual practice for this is to create the header record and then ask the database for the last insert id. You then use this as your order reference for all other orders.

This thread has a discussion on how to do this in Sequelize

The gist of the discussion is that you can pass the following as an option
{ type: sequelize.QueryTypes.INSERT }

Open in new window

And the query returns the last insert id
For example
      'INSERT INTO OrderHeader(orderdata) VALUES(\'Data\')', {
        type: Sequelize.QueryTypes.INSERT
    ).then(function (data) {
      console.log(data); // Outputs last insert id - use this value for your inserts into your order data

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.