Prisma•4d ago

CTE query not working properly

Look at attached photo for error code and query. This query works when running against local DB, but doesn't work when running against deployed DB. I'm able to run this query against both DBs using raw sql outside of Prisma. I'd love some help here! On v5.7.0

6 Replies

Yetzederixx•4d ago

Are you using typedsql? https://www.prisma.io/docs/orm/prisma-client/using-raw-sql/typedsql

Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Docu...

Learn how to use TypedSQL to write fully type-safe SQL queries that are compatible with any SQL console and Prisma Client.

Yetzederixx•4d ago

I haven't tried a CTE with it, but definitely like how this works much better than just having sql inline with your code

amanuel•4d ago

Does that really change anything though? It's still the same query being executed

Yetzederixx•4d ago

I had plenty of issues doing it the other way, using the typed sql I was actually able to get it to work

amanuel•4d ago

Interesting, I'll give it a go I tried and it didn't work 😦

Yetzederixx•3d ago

hrm, lemme play with my setup a sec and see if I can replicate it Oh, check if you can issue a query without the CTE's Ok, did a very basic CTE and it worked. I had issues, like omfg issues, using IN before. Also your ? has to be $1, $2... etc Here's my initial test query

with something as (
    select * from "User"
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

with something as (
    select * from "User"
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

and code I used to call it

const { cteTest } = require('@prisma/client/sql');
const { db } = require('../utils/db');

router.all('/', async (req, res, next) => {
  const response = await db.$queryRawTyped(cteTest());
  res.status(200).send(response);
});

const { cteTest } = require('@prisma/client/sql');
const { db } = require('../utils/db');

router.all('/', async (req, res, next) => {
  const response = await db.$queryRawTyped(cteTest());
  res.status(200).send(response);
});

Also, make sure you are using npx prisma generate --sql or it won't find your query Ok, this query worked

with something as (
    select * from "User"
    where email in ($1, $2)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

with something as (
    select * from "User"
    where email in ($1, $2)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

This did not work however

with something as (
    select * from "User"
    where email ANY($1)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

with something as (
    select * from "User"
    where email ANY($1)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

nor did this

with something as (
    select * from "User"
    where email in ($1)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

with something as (
    select * from "User"
    where email in ($1)
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

And this won't even run

with something as (
    select * from "User"
    where email in $1
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

with something as (
    select * from "User"
    where email in $1
)
select * from "InspectionLog" as il 
inner join something as s on s.id=il."userId"

@Tyler Benfield @Jon Harrell I know the docs say to use ANY for these things, but I've yet to get it to work. All I see is this kind of output

    at async /home/jared/Work/mvp-rest-api/src/api/index.js:19:20 {
  code: 'P2010',
  clientVersion: '5.20.0',
  meta: {
    code: '42883',
    message: 'ERROR: operator does not exist: text = text[]\n' +
      'HINT: No operator matches the given name and argument types. You might need to add explicit type casts.'
  }
}

    at async /home/jared/Work/mvp-rest-api/src/api/index.js:19:20 {
  code: 'P2010',
  clientVersion: '5.20.0',
  meta: {
    code: '42883',
    message: 'ERROR: operator does not exist: text = text[]\n' +
      'HINT: No operator matches the given name and argument types. You might need to add explicit type casts.'
  }
}

local:

PostgreSQL 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit

remote: PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.3.1 20240522 (Red Hat 13.3.1-1), 64-bit (Digital Ocean) For completeness here's my db.js file

const { PrismaClient } = require('@prisma/client');

const { config } = require('../../config');

const db = new PrismaClient({ log: config.prisma.log });

// This will only fire when log has 'query' sent to it
// so no need to disable in dev/prod
db.$on('query', (e) => {
  /* eslint-disable no-console */
  console.log(`Params: ${e.params}`);
  /* eslint-enable no-console */
});

module.exports = {
  db,
};

const { PrismaClient } = require('@prisma/client');

const { config } = require('../../config');

const db = new PrismaClient({ log: config.prisma.log });

// This will only fire when log has 'query' sent to it
// so no need to disable in dev/prod
db.$on('query', (e) => {
  /* eslint-disable no-console */
  console.log(`Params: ${e.params}`);
  /* eslint-enable no-console */
});

module.exports = {
  db,
};

and initialization stuff in schema.prisma

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins", "typedSql"] // , "tracing"]
  binaryTargets   = ["native", "debian-openssl-1.1.x", "debian-openssl-3.0.x", "linux-musl", "linux-musl-openssl-3.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins", "typedSql"] // , "tracing"]
  binaryTargets   = ["native", "debian-openssl-1.1.x", "debian-openssl-3.0.x", "linux-musl", "linux-musl-openssl-3.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Gaming

Programming

CTE query not working properly