How To Deal With The ConnectionAcquireTimeoutError in Sequelize

One of the most annoying things in web development is to see your application crash for no apparent reason. If you have a Node.js backend that uses Express as ORM, you may have come across what seem to be random crashes.

If you take a look at the logs, you are likely to see a ConnectionAcquireTimeoutError. In particular, the error produced by the backend will involve one of the following messages, depending on your version of Sequelize:

• “ConnectionAcquireTimeoutError [SequelizeConnectionAcquireTimeoutError]: Operation timeout”
• “TimeoutError: ResourceRequest timed out”
• “SequelizeConnectionAcquireTimeoutError: Operation timeout”

These errors all boil down to the same problem.

In this article, you are going to understand what a ConnectionAcquireTimeoutError is, why it happens, and how to fix it!

ConnectionAcquireTimeoutError: What It Is and When It Occurs

ConnectionAcquireTimeoutError is a Sequelize exception that is thrown when the ORM fails to acquire a connection before the timeout.

Behind the scenes, Sequelize manages a pool of database connections. If you are not familiar with this concept, a connection pool is a cache of database connections. It prevents the backend from creating a new connection for every request, which is time-consuming and resource-intensive. The connection pool is pre-initialized with a certain number of connections, known as the pool size.

When Sequelize needs to perform a database operation, such as executing a query or a transaction, it tries to acquire a connection from the pool. If all the connections in the pool are currently in use, Sequelize will wait for a connection to become available. If none become free before 60 seconds — the default timeout — Sequelize will raise a ConnectionAcquireTimeoutError exception.

A connection cannot be available for a number of reasons:

1. The server is doing too many concurrent requests and the pool is unable to keep up. In this case, you should consider incrementing the full size.
2. Some of your queries or transactions are taking too long. In this scenario, Express will start piling new requests up, leading to a bottleneck. You should monitor your database server to see if there are any slow queries and get ready to optimize them.
3. You forget to commit or rollback unmanaged transactions, resulting in idle transactions occupying the pool for no reason. Prefer managed transactions instead.
4. You perform slow operations or there are some deadlocks that prevent your transactions from being committed in time. For example, if you are making network requests inside a transaction and there is a network slowdown, your transaction is going to stay open for longer than expected. To address that, make sure to set a timeout on relevant async operations.

Here you saw the causes of the problem and some high-level solutions, but let’s dive into some code.

Stop SequelizeConnectionAcquireTimeoutError Exceptions

You can address the reasons 3. and 4. by improving your code, but 1. and 2. require proper Sequelize configuration. All you have to do to avoid the Sequelize connection timeout issue is to scale and configure your pool correctly.

When calling the Sequelize() constructor in your Node.js server, make sure to add the following options:

const sequelize = new Sequelize({
  // ...
  pool: {
    max: 10, // default: 5
    acquire: 60000, // default: 30000
    // ...
  }
});

The pool option defines the behavior of the connection pool. By default, the pool has a maximum size of 5 active connections. Depending on your scale, you may need to adjust this value to avoid running out of connections through the max parameter.

Keep in mind that increasing the connection pool size has some serious effects on resource usage. Maintaining a connection open takes resources, and your database server has a maximum number of allowed active connections. Thus, you must set the max option to less than that limit.

Also, do not forget that the connection pool is not shared between Sequelize instances. If your database server supports 100 connections, and your backend runs on two different processes, you should set the max option to 45. 45 and not 50 because you should always reverse some connections for other uses and users.

Sequelize waits up to 30 seconds to get a connection from the pool. The acquire parameter controls that behavior by accepting a value in milliseconds. If you perform very time-intensive transitions or queries, you may need to increase the acquire value too.

Et voilà! The “ConnectionAcquireTimeoutError [SequelizeConnectionAcquireTimeoutError]: Operation timeout” is gone!

Conclusion

In this article, you learned how to fix one of the most frustrating errors with Node.js and Sequelize. As seen here, the Sequelize connection timeout error is pretty common and leads to a fatal crash Node.js cannot recover from. Luckily, there are some easy solutions, and here you explored them all.

Thanks for reading! I hope you found this article helpful.