Unable to connect to postgres pgbouncer pool

[gianlazz]

Describe the bug
I've setup a managed pgbouncer pool via digital ocean. I'm able to connect to it via all other clients including psql in the command line. However, mikroorm seems unable to connect to the pool and always says that there's no such "postgres" database, though the db name provided in the configuration for the pool is "pool".

Stack trace

/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8
        return new exceptions_1.DriverException(exception);
               ^
DriverException: no such database: postgres
    at PostgreSqlExceptionConverter.convertException (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8:16)
    at PostgreSqlExceptionConverter.convertException (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:42:22)
    at PostgreSqlDriver.convertException (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:192:54)
    at /Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:196:24
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at SchemaGenerator.createDatabase (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:348:9)
    at SchemaGenerator.ensureDatabase (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:39:13)
    at Migrator.ensureDatabase (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/migrations/Migrator.js:147:25)
    at Migrator.runMigrations (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@mikro-orm/migrations/Migrator.js:279:9)
    at AppModule.onModuleInit (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/src/app.module.ts:283:5)
    at callModuleInitHook (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9)
    at NestApplication.callInitHook (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@nestjs/core/nest-application-context.js:176:13)
    at NestApplication.init (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@nestjs/core/nest-application.js:96:9)
    at NestApplication.listen (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/@nestjs/core/nest-application.js:154:33)
    at bootstrap (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/src/main.ts:44:3)

previous error: no such database: postgres
    at Parser.parseErrorMessage (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/pg-protocol/src/parser.ts:103:30)
    at TLSSocket.<anonymous> (/Users/gianlazzarini/Documents/Development/Lazztech.Hub-Service/node_modules/pg-protocol/src/index.ts:7:48)
    at TLSSocket.emit (node:events:390:28)
    at TLSSocket.emit (node:domain:475:12)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at TLSSocket.Readable.push (node:internal/streams/readable:228:10)
    at TLSWrap.onStreamRead (node:internal/stream_base_commons:199:23)

To Reproduce
Steps to reproduce the behavior:

1. set connection details to provided values for pgbouncer pool, using the pool as the database name value

Expected behavior
A clear and concise description of what you expected to happen.

It should connect.

Additional context
Add any other context about the problem here.

I have no issues connecting to this managed postgres database without going through the pool.

Versions

Dependency	Version
node	v16.13.0
typescript	v4.8.4
mikro-orm	5.5.0
your-driver	@mikro-orm/postgresql

[gianlazz]

I've done a bit of debugging & it seems that node_modules/@mikro-orm/knex/schema/SchemaGenerator.js on the ensureDatabase function, when the this.helper.databaseExists() function is called it returns false. However if I manually edit this to set exists to true, overriding the helper.databaseExists response then I'm able to successfully connect. There seems to be an issue within the logic for that helper objects databaseExists function.

    /**
     * Returns true if the database was created.
     */
    async ensureDatabase() {
        const dbName = this.config.get('dbName');
        let exists = await this.helper.databaseExists(this.connection, dbName);
        exists = true;
        if (!exists) {
            this.config.set('dbName', this.helper.getManagementDbName());
            await this.driver.reconnect();
            await this.createDatabase(dbName);
            this.config.set('dbName', dbName);
            await this.driver.reconnect();
            return true;
        }
        return false;
    }

[gianlazz]

I've used https://www.npmjs.com/package/patch-package to make a temporary patch for my exact situation as a temporary fix for the moment.

diff --git a/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js b/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js
index e7f9f8f..306c672 100644
--- a/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js
+++ b/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js
@@ -32,7 +32,12 @@ class SchemaGenerator extends core_1.AbstractSchemaGenerator {
      */
     async ensureDatabase() {
         const dbName = this.config.get('dbName');
-        const exists = await this.helper.databaseExists(this.connection, dbName);
+        // this.helper.databaseExists is failing to accuratly return the existence with a pgbouncer db named "pool"
+        // this patch overrides the result if db name is "pool"
+        let exists = await this.helper.databaseExists(this.connection, dbName);
+        if (dbName == 'pool') {
+            exists = true;
+        } 
         if (!exists) {
             this.config.set('dbName', this.helper.getManagementDbName());
             await this.driver.reconnect();

[B4nan]

If the database you set in our ORM config does not exist (which is checked via select 1 from pg_database where datname = '${name}' query), the schema generator will try to use management database to connect to, which should always exist - in case of postgres driver it is called postgres.

IIRC we need to set a database when connecting (that's why the concept of management database was added), but maybe not in all drivers. I can definitely offer making this management database name configurable, maybe we could also just use information_schema as it should always exist - that is what other SQL drivers use, only postgres driver overrides this.

[B4nan]

information_schema won't work, that database might not exist apparently. But it looks like postgres driver does not require the database to be set, all tests seem to be passing if I just omit the database name.

[B4nan]

FYI I will be reverting this change because of #3769, postgres requires the database name to be present, and apparently defaults to the user name if not provided. So while there was a bug in with the undefined value, the solution to use '' was wrong too and works only if you actually have the database called the same as your user (at least I think so). So if you don't have a database called postgres in your system, you will either need to create one, or specify another management database name - will add a config option for that, probably under schemaGenerator section.

I see you said the databaseExists logic was wrong, would appreciate more feedback on that, if it does not work for your use case, please try to come up with a way that works, will be happy to change that.

[darkadept]

I'm on digital ocean and just started having this no such database: postgres error popup.

I found out that my pool name was different than my db name, and Mikro-orm was trying to find the wrong database, the name of the pool. I renamed my pool to match my database name and now it's working.

[B4nan]

FYI you can now override the management database name via schemaGenerator: { managementDbName: '...' }.