Prisma-client-js: Using raw queries with tagged template literals doesn't include parameters

[HendrikJanssen]

Bug description

It seems to me that the raw queries feature does not really work with tagged template literals using parameters.
I have tried multiple variations of various queries but parameters never get included.

How to reproduce

Using postgres and a simple table

model Job {
    id          Int          @id @default(autoincrement())
    title       String
    slug        String       @unique
}

this code does not work

import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient();
const id = 4;
const rawResult = await prisma.raw`SELECT title, id, slug FROM "public"."Job" WHERE id = ${id};`;

and results in the following error

Error: Raw query failed. Code: `42601`. Message: `db error: ERROR: syntax error at or near ";"`

Enabling query logging in postgres and peeking into the log file i see the following log entrys which accurately reflect the error

2020-03-20 00:08:29.560 CET [12294] postgres@[DB NAME REDACTED] LOG:  statement: SELECT 1
2020-03-20 00:08:29.560 CET [12294] postgres@[DB NAME REDACTED] ERROR:  syntax error at or near ";" at character 56
2020-03-20 00:08:29.560 CET [12294] postgres@[DB NAME REDACTED] STATEMENT:  SELECT title, id, slug FROM "public"."Job" WHERE id = ?;

It seems that the parameter simply doesn't get included, looking at other queries generated by the prisma client they all include an extra line in the log, like

2020-03-20 00:08:29.563 CET [12293] postgres@[DB NAME REDACTED] DETAIL:  parameters: $1 = '2020-03-19 23:08:29.561', $2 = '%test%', $3 = '16', $4 = '0'

whereas using the raw query this line is missing.

Am i missing something? That should just work, shouldn`t it?

Expected behavior

Parameters in raw queries using tagged template literals should get included.

Environment & setup

Using Windows 10 with WSL Ubuntu 18.04.4 LTS

$ uname -a
Linux [...] 4.4.0-18362-Microsoft prisma/prisma2#476-Microsoft Fri Nov 01 16:53:00 PST 2019 x86_64 x86_64 x86_64 GNU/Linux

Postgres 10.12

$ postgres -V
postgres (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)

NodeJs 12

$ node -v
v12.16.1

And the respective latest versions of prisma2, typescript and ts-node

"dependencies": {
    "@prisma/client": "^2.0.0-preview024",
    "ts-node": "^8.7.0",
    "typescript": "^3.8.3",
    [...]
  },
  "devDependencies": {
    "prisma2": "^2.0.0-preview024",
    [...]
  }

$ npx prisma2 --version
[email protected], binary version: 377df4fe30aa992f13f1ba152cf83d5770bdbc85

[pantharshit00]

I can confirm this as a bug. We should fix this.

[Jolg42]

I can reproduce on PostgreSQL (not on SQLite)
Will investigate for a fix!

[Jolg42]

Looks like I didn't read the documentation properly when I implemented it 😓
https://github.com/blakeembrey/sql-template-tag

pg.query(query); // Uses `text` and `values`.
mysql.query(query); // Uses `sql` and `values`.

Currently the implementation is using queryInstance.sql for all db 🔥

[Jolg42]

@HendrikJanssen As a workaround you can use

const rawResult = await prisma.raw(`SELECT title, id, slug FROM "public"."Job" WHERE id = ${id};`);

Warning though, as this pure text and doesn't escape the variable like in the other method (that is currently not working with parameters on PostgrSQL)

[Sytten]

I came here to report the same problem, thanks for fixing it.