36

I need some clarification about what the pool is and what it does. The docs say Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database.

var sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'|'mariadb'|'sqlite'|'postgres'|'mssql',

  pool: {
    max: 5,
    min: 0,
    idle: 10000
  },

// SQLite only
   storage: 'path/to/database.sqlite'
});
shershen
  • 9,875
  • 11
  • 39
  • 60
Mark A
  • 1,995
  • 4
  • 18
  • 26
  • 2
    On why connection pools are useful: https://stackoverflow.com/questions/44081488/why-a-connection-pool-of-many-opened-connections-is-less-costly-for-a-system-tha – Maria Ines Parnisari May 26 '17 at 03:27

3 Answers3

61

When your application needs to retrieve data from the database, it creates a database connection. Creating this connection involves some overhead of time and machine resources for both your application and the database. Many database libraries and ORM's will try to reuse connections when possible, so that they do not incur the overhead of establishing that DB connection over and over again. The pool is the collection of these saved, reusable connections that, in your case, Sequelize pulls from. Your configuration of 

pool: {
    max: 5,
    min: 0,
    idle: 10000
  }

reflects that your pool should:

  1. Never have more than five open connections (max: 5)
  2. At a minimum, have zero open connections/maintain no minimum number of connections (min: 0)
  3. Remove a connection from the pool after the connection has been idle (not been used) for 10 seconds (idle: 10000)

tl;dr: Pools are a good thing that help with database and overall application performance, but if you are too aggressive with your pool configuration you may impact that overall performance negatively.

P Ackerman
  • 2,266
  • 20
  • 24
  • 4
    Nice answer! With this `idle` configuration, if my query does more than 10 seconds to execute, is the connection closed without me getting a response? – hytromo Jan 17 '18 at 09:29
  • 3
    no, idle is literally how long the connection will sit there doing nothing. what you're talking about is the `pool.timeout` setting. which i think defaults to 30s. after that the query times out and you'll get an error. – steev Jul 10 '19 at 17:11
  • Nice answer sir – Khalid Skiod Jan 18 '21 at 12:06
  • @steev the current docs as well as those of v4 don't talk about pool.timeout. I also can't find pool.timeout in the source code. See https://stackoverflow.com/a/62697070/4417769 for something like that on postgres – sezanzeb Mar 17 '21 at 10:17
  • 2
    @sezanzeb If I recall correctly I think that option is a default of "generic-pool", the package that sequelize4 uses to do its pooling. if you can't find mention of it in sequelize code then it might never actually reset it? latest sequelize, btw, uses sequelize-pool instead, it seems, and i have no idea about that. these are guesses, tho, it's been a long time since i looked at this stuff... – steev Mar 23 '21 at 19:43
  • Is the pool and limits set per user or global ? In another words, max : 5 will allow only 5 user to connect ( mysql max_connections ) or will restrict 5 connections per user ? – Olavo Mello May 04 '22 at 02:43
7

pool is draining error

I found this thread in my search for a Sequalize error was giving my node.js app: pool is draining. I could not for the life of me figure it out. So for those who follow in my footsteps:

The issue was that I was closing the database earlier than I thought I was, with the command sequelize.closeConnections(). For some reason, instead of an error like 'the database has been closed`, it was instead giving the obscure error 'pool is draining'.

Chris Troutner
  • 477
  • 6
  • 7
  • 1
    Thank you for taking the time to post this. In the end, this was my problem, too. Coupled with [tricky forEach](https://codeburst.io/javascript-async-await-with-foreach-b6ba62bbf404) – drstupid Feb 11 '19 at 12:23
0

Seems that you can try to put pool to false to avoid having the pool bing created. Here is the API details table :http://sequelize.readthedocs.org/en/latest/api/sequelize/

[options.pool={}] Object Should sequelize use a connection pool. Default is true

shershen
  • 9,875
  • 11
  • 39
  • 60