PostgreSQL 12 compatibility (lack of consrc)

[luzat]

Issue type:
[x] bug report
[x] feature request

Database system/driver:
[x] postgres

TypeORM version:
[x] latest
[x] @next

Steps to reproduce or a small repository showing the problem:
PostgreSQL 12 removes the pg_constraint.consrc column. This makes constraintsSql from loadTables in PostgresQueryRunner.ts fail with an exception and aborting the transaction. Documentation:

• PostgreSQL 12
• PostgreSQL 11

The docs for 11 still include consrc, but recommend to use pg_get_constraintdef, which only seems to be used for exclusions constraints (x) here. This has been introduced in #2710 for exclusions constraints. It seems that expression is used in some places, but giving it a very rough look I think whatever pg_get_constraintdef returns might work as well. Always using pg_get_constraintdef at least gets my 1 entity test running, but this obviously does not use many constraints.

[viktor-ku]

@Kononnable I see that it's fixed in the fork of typeorm. Will it be fixed also in the main typeorm (this) repo some day?

[danvy]

Is there any work around until a new release is published?

[stefanpartheym]

I think the only workaround right now is to build https://github.com/eliziario/typeorm yourself and use its command line tool to achieve certain goals.

Make sure you build eliziario-typeorm first like this:

npm install
npm run compile
npm run package

After that you could implement a custom npm-script like this:

...
"scripts": {
  ...
  "typeorm": "node /path/to/eliziario-typeorm/build/package/cli.js",
  ...
}
...

Then you can use this for instance:

npm run typeorm -- migration:run

Hope this helps & works, didn't really test it...

[Domiserver]

I already have a NestJS project built with Angular ready to be deployed.
How should I add this temporary solution to the project?
Thanks

[stefanpartheym]

I already have a NestJS project built with Angular ready to be deployed.
How should I add this temporary solution to the project?
Thanks

I'm not aware of any issues during runtime tbh. Am I missing something?
I only experienced the above issue when using the typeorm command line tool to run migrations.
That's why I currently use the eliziario-typeorm command line tool to achieve this.

[mokanfar]

@stefanpartheym yes I am currently experiencing issues during runtime. Preventing my nestjs app from working in dev environment.

yarn run v1.17.3
$ nest start --watch
12:08:21 AM - Starting compilation in watch mode...


12:08:29 AM - Found 0 errors. Watching for file changes.
[Nest] 15456   - 10/31/2019, 12:08:34 AM   [NestFactory] Starting Nest application...
[Nest] 15456   - 10/31/2019, 12:08:34 AM   [InstanceLoader] AppModule dependencies initialized +241ms
[Nest] 15456   - 10/31/2019, 12:08:34 AM   [InstanceLoader] TypeOrmModule dependencies initialized +1ms
[Nest] 15456   - 10/31/2019, 12:08:35 AM   [TypeOrmModule] Unable to connect to the database. Retrying (1)... +307ms
QueryFailedError: column cnst.consrc does not exist
    at new QueryFailedError (C:\z\nestjs-project\nestjs-task-management\node_modules\typeorm\error\QueryFailedError.js:11:28)
    at Query.callback (C:\z\nestjs-project\nestjs-task-management\node_modules\typeorm\driver\postgres\PostgresQueryRunner.js:176:38) 
    at Query.handleError (C:\z\nestjs-project\nestjs-task-management\node_modules\pg\lib\query.js:142:17)
    at Connection.connectedErrorMessageHandler (C:\z\nestjs-project\nestjs-task-management\node_modules\pg\lib\client.js:211:17)      
    at Connection.emit (events.js:203:13)
    at Socket.<anonymous> (C:\z\nestjs-project\nestjs-task-management\node_modules\pg\lib\connection.js:126:12)
    at Socket.emit (events.js:203:13)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:210:10)

[stefanpartheym]

@mokanfar This seems very odd to me, since I'm not experiencing this issue during runtime.
However, I can imagine you are using ConnectionOptions.synchronize = true in order to automatically update your database after making changes to entity classes.

Since I use ConnectionOptions.synchronize = false in development, staging and production as well. This might be the reason.

[Domiserver]

I downgraded PostgreSQL from version 12 to 11.5 and TyperOrm and the database is running without the "QueryFailedError: column cnst.consrc does not exist".

I will test the project with ConnectionOptions.synchronize = false to check if this is causing the conflict.

Regards.
RonaLT

[mokanfar]

@stefanpartheym Yes, setting ConnectionOptions.synchronize = false removes the error... but I am working now at slight inconvenience in dev mode with it turned off. Thank you for isolating the issue.

[viktor-ku]

@stefanpartheym yeah, thanks for pointing that out.

[ericbiewener]

I'm getting this error on Postgres 12 even with synchronize = false when running the command typeorm migration:generate.

[stefanpartheym]

@ericbiewener I suspect, the error occurs whenever TypORM is trying to use the QueryRunner to either modify table structure or read/get information about table structure.

As I already mentioned earlier, you could use the typeorm cli from https://github.com/eliziario/typeorm, as described here:

I think the only workaround right now is to build https://github.com/eliziario/typeorm yourself and use its command line tool to achieve certain goals.

Make sure you build eliziario-typeorm first like this:

npm install
npm run compile
npm run package

After that you could implement a custom npm-script like this:

...
"scripts": {
  ...
  "typeorm": "node /path/to/eliziario-typeorm/build/package/cli.js",
  ...
}
...

Then you can use this for instance:

npm run typeorm -- migration:run

Hope this helps & works, didn't really test it...

[bartlomiej-korpus]

This is clearly a bug due to breaking change in postgres 12.

[nolazybits]

hit the same issue. subscribing.

[kshitij-srv]

tried running schema:sync with cli and got the same error. I have tried reverting back to postgres 11.5 but it still doesn't work for me. Tried with versions 11.5, 12 and 12.1

[stefanpartheym]

@kshitij-srv

tried running schema:sync with cli and got the same error. I have tried reverting back to postgres 11.5 but it still doesn't work for me. Tried with versions 11.5, 12 and 12.1

Have you tried my proposed solution above?

[kshitij-srv]

@kshitij-srv

tried running schema:sync with cli and got the same error. I have tried reverting back to postgres 11.5 but it still doesn't work for me. Tried with versions 11.5, 12 and 12.1

Have you tried my proposed solution above?

@stefanpartheym my bad, hadn't tried it yet. Using cli after manually building from https://github.com/eliziario/typeorm does solve the issue locally. Thanks 👍

[zishe]

When you can release this change? I have nest app, that method doesn't work for it.

[edge777]

@mokanfar This seems very odd to me, since I'm not experiencing this issue during runtime.
However, I can imagine you are using ConnectionOptions.synchronize = true in order to automatically update your database after making changes to entity classes.

Since I use ConnectionOptions.synchronize = false in development, staging and production as well. This might be the reason.

This does get rid of the bug for time being! thanks!!

[zishe]

It works in 0.2.21! Thanks!