ensureDatabase fails on certain conditions #3713

ml1nk · 2022-11-08T16:42:53Z

Describe the bug

ensureDatabase fails if

user with the name :myuser is used
there is no database with the name :myuser
dbName is :mydatabase and is not existing
postgresql is used

Bug was introduced by b1a867d

DriverException: database ":myuser" does not exist

Versions

Dependency	Version
mikro-orm	5.5.2
pg	8.8.0

The text was updated successfully, but these errors were encountered:

B4nan · 2022-11-08T17:01:41Z

Please share more details, at least your ORM config and full stack trace instead of just an error message.

ml1nk · 2022-11-08T17:03:33Z

Sorry, i thought it was clear after linking your commit. I try to create a testcase for you tomorrow.

B4nan · 2022-11-08T17:05:32Z

Well, what should be clear? That commit fixed another issue, its not something we should just revert.

B4nan · 2022-11-08T17:58:18Z

Tried to do exactly what you described but it works just fine for me.

const orm = await MikroORM.init({
  entities: [...],
  dbName: ':mydatabase',
  user: ':myuser',
  type: 'postgresql',
});
await orm.schema.ensureDatabase();

While adding POSTGRES_USER: :myuser to the postgres docker compose file.

ml1nk · 2022-11-08T18:03:38Z

If you do it like that, it should create a db with the name :myuser in the background. Please check and delete the database before trying again.

B4nan · 2022-11-08T18:05:24Z

No, not really, it works fine on my end

b-bellovic · 2022-11-09T09:02:07Z

Just would like to add that I am experiencing same problem since patch 5.5.1 so I needed to rollback back to 5.5.0.
Here ist config.

{
    entities: ['dist/**/*.entity.js'],
    entitiesTs: ['src/**/*.entity.ts'],
    clientUrl: process.env.DATABASE_URL,
    type: 'postgresql',
    highlighter: new SqlHighlighter(),
    debug,
    logger: logger.log.bind(logger),
    metadataProvider: TsMorphMetadataProvider,
    autoLoadEntities: true,
    migrations: {
      snapshot: false,
      tableName: 'migrations',
      path: './migrations',
      pattern: /^[\w-]+\d+\.ts$/,
      transactional: true, 
      disableForeignKeys: false, 
      allOrNothing: true, 
      emit: 'ts'
    }
 }

and here is connection string

postgresql://postgres:*******@localhost:5432/mod?schema=public

When I delete database and run npx mikro-orm migration:fresh --seed, it fails with DriverException: database "mod" does not exist

B4nan · 2022-11-09T09:24:18Z

Well, if you use a connection url, you need to escape the special characters (like the colon in username, as in the OP. The one you provided almost cetainly dont fail - thats pretty much the default that is tested. I need to see a failing configuration.

b-bellovic · 2022-11-09T09:48:14Z

I tried to run npx mikro-orm migration:up with the latest version and then downgraded to 5.5.0 and run again.

here is my console output and config file I used.

% npx mikro-orm -v          
5.5.2
backend % npx mikro-orm migration:up
mikro-orm migration:up

Migrate up to the latest version

Options:
  -t, --to       Migrate up to specific version                         [string]
  -f, --from     Start migration from specific version                  [string]
  -o, --only     Migrate only specified versions                        [string]
  -v, --version  Show version number                                   [boolean]
  -h, --help     Show help                                             [boolean]

DriverException: database "mod" does not exist
    at PostgreSqlExceptionConverter.convertException (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8:16)
    at PostgreSqlExceptionConverter.convertException (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:42:22)
    at PostgreSqlDriver.convertException (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:192:54)
    at /Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:196:24
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SchemaGenerator.createDatabase (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:348:9)
    at async SchemaGenerator.ensureDatabase (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:39:13)
    at async Migrator.ensureDatabase (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/migrations/Migrator.js:147:25)
    at async Migrator.runMigrations (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/migrations/Migrator.js:279:9)
    at async Function.handleUpDownCommand (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/@mikro-orm/cli/commands/MigrationCommandFactory.js:97:9)

previous error: database "mod" does not exist
    at Parser.parseErrorMessage (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/bbellovic/WebstormProjects/modulaire/backend/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at Socket.Readable.push (node:internal/streams/readable:234:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 88,
  severity: 'FATAL',
  code: '3D000',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postinit.c',
  line: '885',
  routine: 'InitPostgres'
}
backend % npx mikro-orm -v          
5.5.0
backend % npx mikro-orm migration:up
Processing 'Migration20221026153928'
Applied 'Migration20221026153928'
Processing 'Migration20221026183921'
Applied 'Migration20221026183921'
Processing 'Migration20221028063004'
Applied 'Migration20221028063004'
Processing 'Migration20221031092906'
Applied 'Migration20221031092906'
Processing 'Migration20221031180428'
Applied 'Migration20221031180428'
Processing 'Migration20221031210421'
Applied 'Migration20221031210421'
Processing 'Migration20221102081936'
Applied 'Migration20221102081936'
Processing 'Migration20221107200040'
Applied 'Migration20221107200040'
Successfully migrated up to the latest version

config.ts

import { TsMorphMetadataProvider } from '@mikro-orm/reflection';
import { SqlHighlighter } from '@mikro-orm/sql-highlighter';

export default {
  entities: ['dist/**/*.entity.js'],
  entitiesTs: ['src/**/*.entity.ts'],
  clientUrl: process.env.DATABASE_URL,
  type: 'postgresql',
  highlighter: new SqlHighlighter(),
  metadataProvider: TsMorphMetadataProvider,
  autoLoadEntities: true,
  migrations: {
    snapshot: false,
    tableName: 'migrations',
    path: './migrations',
    pattern: /^[\w-]+\d+\.ts$/,
    transactional: true,
    disableForeignKeys: false,
    allOrNothing: true,
    emit: 'ts'
  }
};

snapshot of pacakge.json

    "@mikro-orm/cli": "5.5.0",
    "@mikro-orm/core": "5.5.0",
    "@mikro-orm/postgresql": "5.5.0",
    "@mikro-orm/reflection": "5.5.0",
    "@mikro-orm/sql-highlighter": "^1.0.1",
    
    //devDependencies
     "@mikro-orm/migrations": "5.5.0",
    "@mikro-orm/seeder": "5.5.0",

B4nan · 2022-11-09T12:20:58Z

Thanks, I got it reproduced, apparently it fails even if the : is correctly escaped, it is indeed a problem with the clientUrl.

b-bellovic · 2022-11-09T14:02:03Z

Thanks for your investigation. I noticed that this happens also on 5.5.0 if I use npx mikro-orm migration:fresh instead of npx mikro-orm migration:up

B4nan · 2022-11-09T22:16:14Z

let me know if latest dev version works

ml1nk · 2022-11-10T09:22:03Z

There is no change on my end, it still throws that a database with the same name as my postgres-user does not exists, which is true but shouldn't be necessary. As a workaround i'm overwriting the prototype of PostgreSqlSchemaHelper:

    PostgreSqlSchemaHelper.prototype.getManagementDbName = () => 'postgres'

Just to resolve a little bit of confusion:

:myuser and :mydatabase was meant as placeholder for any valid db / user name.

const orm = await MikroORM.init({
    type: 'postgresql',
    dbName: dbC.database + '-' + Date.now().toString(),
    user: dbC.username, // pitapi_test
    password: dbC.password,
    host: dbC.host,
    port: dbC.port,
    entities,
    pool: {
      min: 0,
      max: 20
    }
  })
  await orm.getSchemaGenerator().ensureDatabase()

DriverException: database "pitapi_test" does not exist
      at PostgreSqlExceptionConverter.convertException (/home/mlink/portal/modules/core/node_modules/.pnpm/@mikro-orm+core@5.5.3_73c42wkoiqn7u4lk4fcjp5lq4q/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8:16)
      at PostgreSqlExceptionConverter.convertException (/home/mlink/portal/modules/core/node_modules/.pnpm/@mikro-orm+postgresql@5.5.3_2raacla5frr7z24irl7bw5xc44/node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:42:22)
      at PostgreSqlDriver.convertException (/home/mlink/portal/modules/core/node_modules/.pnpm/@mikro-orm+core@5.5.3_73c42wkoiqn7u4lk4fcjp5lq4q/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:192:54)
      at /home/mlink/portal/modules/core/node_modules/.pnpm/@mikro-orm+core@5.5.3_73c42wkoiqn7u4lk4fcjp5lq4q/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:196:24
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async SchemaGenerator.createDatabase (/home/mlink/portal/modules/core/node_modules/.pnpm/@mikro-orm+knex@5.5.3_hzyp3vcqguzueqgz7trx4ruqe4/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:348:9)
      at async SchemaGenerator.ensureDatabase (/home/mlink/portal/modules/core/node_modules/.pnpm/@mikro-orm+knex@5.5.3_hzyp3vcqguzueqgz7trx4ruqe4/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:39:13)
      at Object.start (/home/mlink/portal/modules/core/node_modules/.pnpm/@pitman+db@2.0.26/node_modules/@pitman/db/src/bootstrap/orm.ts:66:5)
      at measure (/home/mlink/portal/modules/core/node_modules/.pnpm/@pitman+bootstrap@4.0.1/node_modules/@pitman/bootstrap/src/classes/Bootstrap.ts:180:15)
      at Bootstrap._start (/home/mlink/portal/modules/core/node_modules/.pnpm/@pitman+bootstrap@4.0.1/node_modules/@pitman/bootstrap/src/classes/Bootstrap.ts:135:17)
  
  previous error: database "pitapi_test" does not exist
      at Parser.parseErrorMessage (/home/mlink/portal/modules/core/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/src/parser.ts:369:69)
      at Parser.handlePacket (/home/mlink/portal/modules/core/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/src/parser.ts:188:21)
      at Parser.parse (/home/mlink/portal/modules/core/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/src/parser.ts:103:30)
      at Socket.<anonymous> (/home/mlink/portal/modules/core/node_modules/.pnpm/pg-protocol@1.5.0/node_modules/pg-protocol/src/index.ts:7:48)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

B4nan · 2022-11-10T09:35:05Z

Well, then I really need to see a failing repro, a complete one, not a snippet that uses some undefined variables (what's dbC?). I was never able to verify anything else than the clientUrl issue from @b-bellovic. I tried to do step by step what you said, it works just fine.

:myuser and :mydatabase was meant as placeholder for any valid db / user name.

It works fine even with such names... But next time please don't use placeholders, and provide the exact failing values. You think something is not relevant and generalize, but what you said is simply not enough to reproduce anything - the issue is not that simple. I obviously don't wanna see your exact user/pass/host, feel free to mangle the values, as long as it fails the same - the point is that you should come up with a code I can run myself that will reproduce.

b-bellovic · 2022-11-10T10:17:23Z

let me know if latest dev version works

@B4nan I can confirm that mikro-orm migration:up works again (if the database doesn't exist) at version 5.5.3.

However, mikro-orm migration:fresh is still failing with database database "mod" does not exist. Since I am not using this command in the pipeline, it is not an issue for me but I am writing it here for reference.

Thanks a lot for the quick fix.

B4nan · 2022-11-10T10:21:51Z

Hmm I don't see how it could behave differently, those commands are using the exact same way to init the ORM. Will try one more time while using the CLI, as till now I was reproducing directly with programmatic API instead of CLI.

B4nan · 2022-11-10T10:43:34Z

Ok, I can reproduce the migration:fresh, reopening.

B4nan · 2022-11-10T10:45:20Z

@ml1nk maybe it will be a similar story with your issue - are you using the CLI too? What command? Also enable debug mode and provide the logs, so we see what query caused it. To enable debug mode for CLI you can use MIKRO_ORM_VERBOSE env var.

B4nan · 2022-11-10T11:11:45Z

The issue with migration:fresh is that it tries to drop the schema first, which does not ensure the database exists.

B4nan · 2022-11-10T11:47:07Z

I was able to trace down a subsequent issue with that - inside the ensureDatabase call we reconnect, and the reconnection is not propagated to the migration storage class - it holds outdated knex instance and hence fails to work properly. That could potentially be the same as @ml1nk - at least if you are using migrations when getting the error.

edit: looks like the schema generator class also holds the knex instance, so it could have some side effects there too, not just with migrations

When a database is missing, the ensuring mechanism is reconnecting, hence the cached knex instance becomes outdated. Instead of replacing it, it's better to use a getter and always have up to date value. Closes #3713

B4nan · 2022-11-10T12:19:02Z

Please try 5.5.4-dev.0

ml1nk · 2022-11-10T16:32:13Z

It's fixed. Thank you.

It works fine even with such names... But next time please don't use placeholders, and provide the exact failing values. You think something is not relevant and generalize, but what you said is simply not enough to reproduce anything - the issue is not that simple. I obviously don't wanna see your exact user/pass/host, feel free to mangle the values, as long as it fails the same - the point is that you should come up with a code I can run myself that will reproduce.

Sorry that was my bad, i will try to do better next time.

B4nan · 2022-11-10T16:35:48Z

Amazing, I was running out of ideas :D Found one more issue fixed by 11756ba where the get*SchemaSQL methods failed when database didn't exist.

Related: #3713

B4nan · 2022-11-17T14:47:16Z

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.

B4nan added the bug Something isn't working label Nov 9, 2022

B4nan closed this as completed in d23dde0 Nov 9, 2022

B4nan reopened this Nov 10, 2022

B4nan closed this as completed in fd4c416 Nov 10, 2022

B4nan added a commit that referenced this issue Nov 10, 2022

refactor: ensure db exists before running the schema dump commands

11756ba

Related: #3713

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

ensureDatabase fails on certain conditions #3713

ensureDatabase fails on certain conditions #3713

ml1nk commented Nov 8, 2022 •

edited

B4nan commented Nov 8, 2022

ml1nk commented Nov 8, 2022 •

edited

B4nan commented Nov 8, 2022

B4nan commented Nov 8, 2022

ml1nk commented Nov 8, 2022

B4nan commented Nov 8, 2022

b-bellovic commented Nov 9, 2022 •

edited

B4nan commented Nov 9, 2022

b-bellovic commented Nov 9, 2022 •

edited

B4nan commented Nov 9, 2022

b-bellovic commented Nov 9, 2022 •

edited

B4nan commented Nov 9, 2022

ml1nk commented Nov 10, 2022

B4nan commented Nov 10, 2022 •

edited

b-bellovic commented Nov 10, 2022

B4nan commented Nov 10, 2022

B4nan commented Nov 10, 2022

B4nan commented Nov 10, 2022 •

edited

B4nan commented Nov 10, 2022

B4nan commented Nov 10, 2022 •

edited

B4nan commented Nov 10, 2022

ml1nk commented Nov 10, 2022

B4nan commented Nov 10, 2022 •

edited

B4nan commented Nov 17, 2022

ensureDatabase fails on certain conditions #3713

ensureDatabase fails on certain conditions #3713

Comments

ml1nk commented Nov 8, 2022 • edited

B4nan commented Nov 8, 2022

ml1nk commented Nov 8, 2022 • edited

B4nan commented Nov 8, 2022

B4nan commented Nov 8, 2022

ml1nk commented Nov 8, 2022

B4nan commented Nov 8, 2022

b-bellovic commented Nov 9, 2022 • edited

B4nan commented Nov 9, 2022

b-bellovic commented Nov 9, 2022 • edited

B4nan commented Nov 9, 2022

b-bellovic commented Nov 9, 2022 • edited

B4nan commented Nov 9, 2022

ml1nk commented Nov 10, 2022

B4nan commented Nov 10, 2022 • edited

b-bellovic commented Nov 10, 2022

B4nan commented Nov 10, 2022

B4nan commented Nov 10, 2022

B4nan commented Nov 10, 2022 • edited

B4nan commented Nov 10, 2022

B4nan commented Nov 10, 2022 • edited

B4nan commented Nov 10, 2022

ml1nk commented Nov 10, 2022

B4nan commented Nov 10, 2022 • edited

B4nan commented Nov 17, 2022

ml1nk commented Nov 8, 2022 •

edited

ml1nk commented Nov 8, 2022 •

edited

b-bellovic commented Nov 9, 2022 •

edited

b-bellovic commented Nov 9, 2022 •

edited

b-bellovic commented Nov 9, 2022 •

edited

B4nan commented Nov 10, 2022 •

edited

B4nan commented Nov 10, 2022 •

edited

B4nan commented Nov 10, 2022 •

edited

B4nan commented Nov 10, 2022 •

edited