How to use TypeORM raw SQL query parameter within an anonymous code block

user_8252769 0
Viewed 1026

My code below, keeps getting the error -

bind message supplies 1 parameters, but prepared statement "" requires 0.

Similar parametrized raw SQL query work fine when it is not wrapped in an anonymous code block (https://stackoverflow.com/a/40853818/8252769, the "DO $$...END $$"). But I need it so I can execute the INSERT conditionally in one SQL statement.

import { Injectable } from "@nestjs/common";
import { InjectRepository } from "@nestjs/typeorm";
import { Connection, getRepository, Repository } from "typeorm";

.........

const queryRunner = this.connection.createQueryRunner();

    await queryRunner.connect();

    try {
        await queryRunner.query(
          `DO $$
          BEGIN
            IF NOT(SELECT EXISTS(SELECT id FROM "document" WHERE id = $1))
            THEN
              INSERT INTO .............
            ELSE
              RAISE EXCEPTION 'Operation is only allowed when the document no longer exist.';
            END IF;
          END $$;`,
          [
            documentId,
          ],
        ),
      );
    } catch (ex) {
      throw ex;
    } finally {
      await queryRunner.release();
    }
typeorm nestjs node.js postgresql
1 Answers

I just tested passing the value to the string parameter rather than as a parameters array to the query function and it seems to work:

try {
    await queryRunner.query(
        `DO $$
        BEGIN
        IF NOT(SELECT EXISTS(SELECT id FROM "document" WHERE id = '${documentId}'))
        THEN
            INSERT INTO .............
        ELSE
            RAISE EXCEPTION 'Operation is only allowed when the document no longer exist.';
        END IF;
        END $$;`,
    ),
    );
} catch (ex) {}

Don't forget the '' around the value when you pass it as it has to be an actual string for the database.

user_57030870
Related