Fail to connect TypeORM to Heroku Postgres database in Nest.js framework

Question

i'm facing a issue when trying to connect my Nest.js API to my database. Works fine when i'm using a local database.

My database is deployed on Heroku server and i'm able to access it with pgadmin.

I'm using TypeORM with a .env file for configuration.

.env example:

NODE_TLS_REJECT_UNAUTHORIZED=0

TYPEORM_CONNECTION=postgres
TYPEORM_HOST=***
TYPEORM_USERNAME=***
TYPEORM_PASSWORD=***
TYPEORM_DATABASE=***
TYPEORM_PORT=5432
TYPEORM_DRIVER_EXTRA='{"ssl":true}'
TYPEORM_SYNCHRONIZE=false
TYPEORM_ENTITIES=dist/**/*.entity.js
TYPEORM_MIGRATIONS=dist/v1/migration/*.js
TYPEORM_MIGRATIONS_DIR=src/v1/migration
TYPEORM_MIGRATIONS_RUN=true

Running the npm run start:dev cmd return this error

[Nest] 14998   - 2020-04-01 17:02:42   [TypeOrmModule] Unable to connect to the database. Retrying (1)... +712ms
QueryFailedError: syntax error at or near "`"
    at new QueryFailedError (/home/primeradiant/Documents/hitema/agorise/projet/easymove-api/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at Query.callback (/home/primeradiant/Documents/hitema/agorise/projet/easymove-api/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:176:38)
    at Query.handleError (/home/primeradiant/Documents/hitema/agorise/projet/easymove-api/node_modules/pg/lib/query.js:138:19)
    at Connection.connectedErrorMessageHandler (/home/primeradiant/Documents/hitema/agorise/projet/easymove-api/node_modules/pg/lib/client.js:223:17)
    at Connection.emit (events.js:210:5)
    at TLSSocket.<anonymous> (/home/primeradiant/Documents/hitema/agorise/projet/easymove-api/node_modules/pg/lib/connection.js:120:12)
    at TLSSocket.emit (events.js:210:5)
    at addChunk (_stream_readable.js:308:12)
    at readableAddChunk (_stream_readable.js:289:11)
    at TLSSocket.Readable.push (_stream_readable.js:223:10)

I don't know if the error is due to wrong credentials or not. It's possible that I didn't understand a point. Thank's for the help

Edit: The issue is comming from the TYPEORM_MIGRATIONS_RUN set to true, if I set it to false the api build successfully

Or is comming from the query file that auto migration generate

import {MigrationInterface, QueryRunner} from "typeorm";

export class BaseHistory1584471283256 implements MigrationInterface {

    public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("DROP INDEX `IDX_97672ac88f789774dd47f7c8be` ON `users`");
        await queryRunner.query("CREATE TABLE `history` (`id` int NOT NULL AUTO_INCREMENT, `price` varchar(255) NOT NULL, `departure_station` varchar(255) NOT NULL, `created_at` datetime NOT NULL DEFAULT NOW(), `userId` varchar(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB");
        await queryRunner.query("ALTER TABLE `users` DROP COLUMN `createdAt`");
        await queryRunner.query("ALTER TABLE `users` DROP COLUMN `updatedAt`");
        await queryRunner.query("ALTER TABLE `users` ADD `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)");
        await queryRunner.query("ALTER TABLE `users` ADD `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)");
        await queryRunner.query("ALTER TABLE `users` ADD UNIQUE INDEX `IDX_97672ac88f789774dd47f7c8be` (`email`)");
        await queryRunner.query("ALTER TABLE `history` ADD CONSTRAINT `FK_7d339708f0fa8446e3c4128dea9` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE");
    }

    public async down(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("ALTER TABLE `history` DROP FOREIGN KEY `FK_7d339708f0fa8446e3c4128dea9`");
        await queryRunner.query("ALTER TABLE `users` DROP INDEX `IDX_97672ac88f789774dd47f7c8be`");
        await queryRunner.query("ALTER TABLE `users` DROP COLUMN `updated_at`");
        await queryRunner.query("ALTER TABLE `users` DROP COLUMN `created_at`");
        await queryRunner.query("ALTER TABLE `users` ADD `updatedAt` datetime(6) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(6)'");
        await queryRunner.query("ALTER TABLE `users` ADD `createdAt` datetime(6) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(6)'");
        await queryRunner.query("DROP TABLE `history`");
        await queryRunner.query("CREATE INDEX `IDX_97672ac88f789774dd47f7c8be` ON `users` (`email`)");
    }

}

Are you deploying an env file to Heroku or using the Heroku interface for setting environment variables? It looks like there is a template literal somewhere that's giving typeorm trouble — Jackie McDoniel
– Jackie McDoniel, Commented Apr 1, 2020 at 16:27
this example is from a local launch but I also have the same issue in production and I'm setting the environment variables through the heroku interface — Stephane
– Stephane, Commented Apr 1, 2020 at 18:29
Without seeing how you have your typeorm connection setup in your code, I'm not sure there's more enough here to help. If you can show your ormconfig.json or your TypeormModule.forRoot/Async() (whichever you use) then I think it would be better — Jackie McDoniel
– Jackie McDoniel, Commented Apr 1, 2020 at 19:38
The issue comes from the TYPEORM_MIGRATIONS_RUN seted to true, when I set it to false the api build successfully , but know i'm asking myself if I have to build my database by hand — Stephane
– Stephane, Commented Apr 1, 2020 at 19:48
Your error states you have a ` somewhere which is causing the query to fail. Can't help more without seeing more code — Jackie McDoniel
– Jackie McDoniel, Commented Apr 1, 2020 at 19:52

Jackie McDoniel · Accepted Answer · 2020-04-01 20:23:05Z

0

Postgres does not recognize ` as a valid string character. Instead, ' should be used. You can read more on postgres strings here

answered Apr 1, 2020 at 20:23

Jackie McDoniel

71.7k9 gold badges191 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephane Over a year ago

Thank's with this info i've decided to delete my old migrations generated with mysql config to generate them with postgres config, it work with now :)

Collectives™ on Stack Overflow

Fail to connect TypeORM to Heroku Postgres database in Nest.js framework

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related