Multi Tennant setup with Pooled Connections not working

[darrynhoskingluna]

Is there an existing issue for this?

• I have searched the existing issues

Current behavior

When configuring Sequelize with use factory and a single database Sequelize will resuse the connection as expected

SequelizeModule.forRootAsync({
            useFactory: () => ({
                dialect: 'postgres',
                dialectModule: pg,
                host: DB_HOST,
                port: Number(DB_PORT),
                database: 'postgres',
                username: DB_USERNAME,
                password: DB_PASSWORD,
                autoLoadModels: true,
                synchronize: true,
            }),
        }),

Image showing that only a single connection to the DB is made as the api is used

However when using a SequelizeOptionsFactory that is request scoped (intention to enable multi tennant db on a request header stratergy) a new connection is made per api request. This means that eventually the API could use up all the connections on the database and result in an error by overwhelming the connections on the database.

@Injectable({scope:Scope.REQUEST})
export class SequelizeConfigService implements SequelizeOptionsFactory{
    // The intention is you can resolve the DB from the request header
    // constructor(@Inject(REQUEST) private readonly request:RequestContext){}

  createSequelizeOptions(): SequelizeModuleOptions {
    //const firmUuid = this.request.firmGuid;
    let database = 'postgres';
    // if (firmUuid) {
    //   database = `sqmfirm_${firmUuid}`;
    // }
    return {
      dialect: 'postgres',
      host: DB_HOST,
      port: Number(DB_PORT),
      database: database,
      username: DB_USERNAME,
      password: DB_PASSWORD,
      autoLoadModels: true,
      synchronize: true,
    };
  }
}

        SequelizeModule.forRootAsync({
            imports: [DatabaseConfigModule],
            useExisting: SequelizeConfigService,
        }),

Image showing the connections climbing as the api is used

Minimum reproduction code

https://github.com/dchosking/multitennant-connection-pool

Steps to reproduce

npm install
npm start
Open browser to http://localhost:3000/ then contiunuisly Refresh.
Use what ever DB management tool you perfer and observe the connections to the DB climb
(Optional step If you set max connections to an arbitarly low number you can see the database begin to decline connections )

Docker compose for a DB with low number of max connections

version: '3.4'

services:
  db:
    image: postgres:13.5
    command: postgres -c 'max_connections=20'
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: password
    ports:
      - 5432:5432

Expected behavior

Expected behavior is Sequelize caching each connection to the database and then reusing that connection for subsequent requests

Package version

^9.0.0

NestJS version

^9.0.5

Node.js version

v14.19.0

In which operating systems have you tested?

• macOS
• Windows
• Linux

Other

No response

[kamilmysliwiec]

Please, use our Discord channel (support) for such questions. We are using GitHub to track bugs, feature requests, and potential improvements.