.raw doesn't return data if query doesn't start with SELECT statement

[ryanking1809]

I'm using the Prisma Beta2 with Postgres and prisma.raw and am getting a panic error when trying to do a recursive query.

I have the following schema

model Task {
  id String @default(cuid()) @ id
  parent Task? @relation("TaskTree", fields: [parentId], references: [id])
  parentId String?
  children Task[]  @relation("TaskTree")
}

I need to get all descendants of a given node referenced via it's id. The following sql gets me the results I need. It works perfectly fine in postgres.

          WITH RECURSIVE descendants AS (
              SELECT *
              FROM "Task"
              WHERE id = '${id}'
          UNION ALL
              SELECT t.*
              FROM "Task" t
              JOIN descendants ON t."parentId" = descendants.id
          )
          SELECT * FROM descendants;

However, when I plug it into prisma.raw I get the Error: PANIC: expected 0 parameters but got 1

Here's my error logs:

  plusX Execution permissions of /Users/ryanking/Documents/Projects/cc_gantt_server_prod/node_modules/@prisma/client/runtime/query-engine-darwin are fine +0ms
  engine { flags: [ '--enable-raw-queries' ] } +1ms
  engine stderr Printing to stderr for debugging +146ms
  engine stderr Listening on 127.0.0.1:64177 +1ms
  engine stdout { timestamp: 'Apr 16 11:41:01.870',
  level: 'INFO',
  target: 'quaint::pooled',
  fields:
   { message: 'Starting a postgresql pool with 5 connections.' } } +36ms
  engine stdout { timestamp: 'Apr 16 11:41:01.910',
  level: 'INFO',
  target: 'query_engine::server',
  fields:
   { message: 'Started http server on 127.0.0.1:64177',
     'log.target': 'query_engine::server',
     'log.module_path': 'query_engine::server',
     'log.file': 'query-engine/query-engine/src/server.rs',
     'log.line': 109 } } +34ms
  engine stdout { timestamp: 'Apr 16 11:41:02.094',
  level: 'ERROR',
  target: 'query_engine',
  fields:
   { message: 'PANIC',
     reason: 'expected 0 parameters but got 1',
     file: '<::std::macros::panic macros>',
     line: 5,
     column: 6 } } +183ms
  engine { error:
  engine    { Error: socket hang up
  engine        at createHangUpError (_http_client.js:323:15)
  engine        at Socket.socketOnEnd (_http_client.js:426:23)
  engine        at Socket.emit (events.js:194:15)
  engine        at endReadableNT (_stream_readable.js:1125:12)
  engine        at process._tickCallback (internal/process/next_tick.js:63:19) code: 'ECONNRESET' } } +6ms
Error: PANIC: expected 0 parameters but got 1
    at PrismaClientFetcher.request (/Users/ryanking/Documents/Projects/cc_gantt_server_prod/node_modules/@prisma/client/runtime/index.js:1:53048)
    at process._tickCallback (internal/process/next_tick.js:68:7)
Error in Prisma Client:
PANIC: expected 0 parameters but got 1 in
<::std::macros::panic macros>:5:6

Am I also correct in assuming this query can't be done with graphql?

---

Edit: The same query is working fine with https://node-postgres.com/

[pantharshit00]

You don't need to add single quotes to the parameter. Raw uses prepared statements under the hood so every variable you are using in the query is replaced by a prepared statement variable. If you add quotes, postgres will treat them as a string and when we try to pass the variables to the prepared statement, the query will be rejected by the PostgreSQL instance.

The following query should work for your use case:

  const data = await prisma.raw`
          WITH RECURSIVE descendants AS (
              SELECT *
              FROM "Task"
              WHERE id = ${id}
          UNION ALL
              SELECT t.*
              FROM "Task" t
              JOIN descendants ON t."parentId" = descendants.id
          )
          SELECT * FROM descendants;
  `;

[ryanking1809]

Oh in that case prisma.raw is returning an integer of the number of rows, rather than the rows themselves. ie. Using the query above, if I get 2 results, raw will return the number 2, not the rows themselves.

[pantharshit00]

Looks like a bug to me now, we need to investigate this further. Malformed data might be coming from the driver that we are using.

I can confirm this behaviour though.

[pimeys]

Yep. I knew this will happen at some point. So currently we decide is the query a SELECT by looking into the first word. This will definitely not fly as in here, where the statement starts with a WITH.

Solution, implement two different calls:

• execute_raw will execute a statement and return the number of affected rows
• query_raw will execute a statement and return rows, if any

This would follow how every other SQL client does the differentiation between SELECT and other queries.

[TLadd]

Ran into this as well expecting to get data back when doing something like this:

let result = await prisma.raw(`INSERT INTO bookmark (reader_id, page_id) VALUES ('d211756b-70d0-4521-9afd-cd9e5bb0e7bb', '48466bd2-54fb-42cc-ab7b-1d56e62eedcd') returning *;`);

The returning * doesn't take effect and I just get back 1 as the result.

[beeplin]

@TLadd I just got exactly the same problem as yours. Want to get the id of the newly inserted record, but had to write another SELECT ORDER BY id LIMIT 1 to get it done. ;(

[nicosampler]

+1

[richardwardza]

I'm having the same issue of the number of rows being returning instead of the actual row data.
My query starts with:

with all_records as (
  select .......

The query works correctly when run directly on the db.

[albertoperdomo]

We've decided to go ahead and deprecate raw in favor of these two separate methods (one for query, another for execute) as suggested by @pimeys.

Naming of the two methods is still TBD.

[albertoperdomo]

Update:

• naming: we've decided to go with queryRaw and executeRaw.
• existing raw method: will be removed in favor of these two new dedicated methods.