Can one ensure one database connection per HTTP request with Express?

[webJose]

I made this question in Stack Overflow: https://stackoverflow.com/questions/77975527/is-sequelize-flawed-how-to-ensure-independent-db-connections-in-an-express-serv

In a Nutshell

It appears to me that sequelize in a NodeJS + Express server cannot ensure one database connection per HTTP request. Am I correct? If not, how does one ensure one database connection per HTTP request?

[ephys]

It depends on what you're trying to do.

If your goal is to have each request use their own connection, but all connections go to the same database, you can use sequelize.withConnection to access a connection object directly and pass it down to all query calls. This API is only available in Sequelize 7.
If you really don't want to share the connection, you can also use the destroyConnection option to kill it after it's been used.

await sequelize.withConnection({ destroyConnection: true }, async connection => {
  // These two queries will run on the same connection
  const entity = await YourModel.findOne({ connection });
  await entity.destroy({ connection });

  // The connection will be closed at the end of this function
})

The problem with this is that it's not a use case Sequelize is designed for because it's a lot less efficient than pooling and sharing connections. For instance, all DB queries made by this connection will be done in series, because a DB connection can only process one query at a time.
As a result of this being a niche request, we don't support starting a transaction on a reserved connection yet, because we never had a need for it.

---

If the issue is that you need multiple Sequelize instances because they connect to different databases, but each database has the same structure, then you need to create a copy of each model per Sequelize instance. Models can only be associated with one Sequelize instance (this is what sequelize.define, Model.init or sequelize.addModels do).

Alternatively, you can wait for support for the Repository mode, but we've barely scratched the surface of the work that needs to be done to get there so it's years away. Repository mode allows your to use the same model class with multiple sequelize instances, as the model class is only a data holder and is not tied to a specific Sequelize instance.

---

Finally, if you can explain why you need independent connections per HTTP request, we can probably help you further

[webJose]

Hello, @ephys and thanks for dropping by. Initially, I thought this: This code looks like 2 HTTP requests could potentially use the same connection. If this is true, a long-running query from one user could affect the response time of the second user. Then I was reminded that sequelize will use a connection pool. So I guess this won't happen because every time I use an operation, a new connection is brought from the pool, as I understand it.

But then there's another issue: What if I needed to transact multiple statements? For example: Saving master-detail scenarios. For example, saving a new Cart object that contain multiple CartItems. I would traditionally need to first save Cart, then save individual CartItem objects after retrieving the new CartId property, and all should be done in a single transaction in case a rollback is required due to errors. This doesn't seem possible right now, is it?

[ephys]

You can use sequelize.transaction to start a transaction. Any query executed in the callback will run on the connection that started the transaction, and the transaction will automatically be committed or rolled back at the end of the block. Any query run outside of the callback will run on its own, separate, connection.

In Sequelize 7, this is done pretty much magically thanks to AsyncLocalStorage: https://sequelize.org/docs/v7/querying/transactions/

In Sequelize 6, you need to pass around a transaction object to let methods know on which connection they need to run: https://sequelize.org/docs/v6/other-topics/transactions/#managed-transactions

[webJose]

Once more, your information seems to be very helpful. I still see a problem here: What if 2 HTTP requests need a transaction? Since both will use the same sequelize instance, would that merge the SQL from the 2 HTTP requests together under the same connection and transaction?

[ephys]

They indeed use the same Sequelize instance but a single sequelize instance can have many connection to the database (by default it's 5 concurrent connections, but can be configured)

So what would happen if two concurrent HTTP requests each started their own transaction would be that both would execute the following step concurrently:

• Take a connection from the connection pool
  • Because the connection is removed from the pool while the transaction is active, no other query can use this connection, except if they're in the transaction callback
• Start its transaction on that connection
• Execute the callback, in which all queries will use that same connection that was taken from the pool
• Once the callback finishes, either commit or rollback the transaction, then return the connection to the pool

All of these steps happen on two entirely separate db connections, even if the two HTTP requests are concurrent

[webJose]

Ok ok. I think I understand. It should work, I suppose.

The next step is serverless Vercel functions. I'll do some testing.

Thank you for the time.