NodeJS database (mysql) connection vs. transaction management

Question

In NodeJS applications, what is the best strategy for managing number of connections when transactions are involved (mysql community version)?

So, this is my line of thinking -

1) Do not hand over control to an ORM 2) Treat connection as an expensive commodity 3) mysql module for nodejs appears to be creating more connections

Given that, if n promises are executing at the same time (single thread part is understood), each representing a transaction, I do not want each of them create n connections - that much is obvious - but what happens when a transaction needs to be rolled back on a connection, if a connection is shared between promises? ..I do not want one transaction to fail if a another one fails as well..I would expect the 'connection' object to be little more intelligent..such that when beginTransaction is called, it provides a localized context...but just want to confirm if this is the case - I am looking for some documentations. If not, some sort of FIFO queue to facilitate sequalizing these transactions appear to be the way forward - but don't we already have one? What are other strategies you folks deployed?

FYI - I am looking at this already - docs.sequelizejs.com/manual/tutorial/transactions.html — Senthu Sivasambu
– Senthu Sivasambu, Commented Dec 1, 2017 at 15:00

clay · Accepted Answer · 2017-12-01 15:07:12Z

1

I would suggest to you KnexJS, especially because of its transaction supprt and built-in connection pooling. I'm sure Sequelize handles this in some way as well.

The nice thing about knex is that you can pass this transaction object through your promise chain, and if any promise fails (even from a non-DB error, like a business rule!) the transaction promise will be rejected and everything is automatically rolled back.

I hope this helps you out and has "just worked" for us so far - we have not needed to delve into the management of a connection pool at this time.

answered Dec 1, 2017 at 15:07

clay

6,0172 gold badges26 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Senthu Sivasambu Over a year ago

Thanks Clay - will take a look

Collectives™ on Stack Overflow

NodeJS database (mysql) connection vs. transaction management

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related