RTFM: https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#tagged-template-helpers
import { Prisma } from "@prisma/client";
const ids = [1, 3, 5, 10, 20];
const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${Prisma.join(
ids
)})`;
Top answer 1 of 2
52
RTFM: https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access#tagged-template-helpers
import { Prisma } from "@prisma/client";
const ids = [1, 3, 5, 10, 20];
const result = await prisma.$queryRaw`SELECT * FROM User WHERE id IN (${Prisma.join(
ids
)})`;
2 of 2
7
As @MichaelDausmann has mentioned above, there is a Prisma function that joins array items and formats the SQL by the types of parameters. But when you use uuid type, Prisma's join function formats the parameters adding double apostrophe. So you can take error like 'type mismatch' in PostreSQL. Alternatively, you can use native Array.join function to build a sql string and send it to Prisma raw query function using Prisma.raw function.
const sql = `select * from table where id in (${idArray.map(v => `'${v}'::uuid`).join(",")})`
result = await prisma.$queryRaw(Prisma.raw(sql))
Prisma
prisma.io › home › raw queries › raw queries › raw queries › raw queries
Raw queries | Prisma Documentation
Learn how you can send raw SQL and MongoDB queries to your database using the raw() methods from the Prisma Client API.
dynamic WHERE clause field not working in raw queries
You could use the Prisma.raw method. This method prevents the provided value from being escaped. This could lead to SQL injection if you use untrusted values as the field, so be sure to validate it. `SELECT id from next_auth."User" WHERE ${Prisma.raw(field)} = ${val};` More on reddit.com
13
4
November 25, 2023Type inference in raw queries is broken for arrays containing single null element
The expected behavior is that both queries should work (return an array of a single column with the NULL element). ... PrismaClientKnownRequestError: Invalid `prisma.$queryRaw()` invocation: Raw query failed. More on github.com
1
March 7, 2025queryRaw returns empty array
The problem I am facing is, that ... my raw Query through prisma.$queyRaw, I get an empty array back.. The exact same query run on DBeaver returns a result though. I am really confused here. What is going on?? My code: PrismaJoinThe official Discord server of Prisma! Find us online at prisma.io ... You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into... More on answeroverflow.com
How to get the size of an array type column in Prisma postgresql?
If it's not in the Prisma docs it isn't supported. Google is your friend. More on reddit.com
2
3
January 15, 2022GitHub
github.com › prisma › prisma › issues › 13663
Array as value to Prisma.sql template string · Issue #13663 · prisma/prisma
June 7, 2022 - For example, passing an array of strings in order to perform an IN query. ... const foo = ["buy", "sell"]; prisma.$queryRaw(Prisma.sql`SELECT * FROM "Transaction" WHERE "txType" IN ${foo} LIMIT 1`);
Author prisma
Reddit
reddit.com › r/node › dynamic where clause field not working in raw queries
r/node on Reddit: dynamic WHERE clause field not working in raw queries
November 25, 2023 -
export async function userExists({ field, val} : {field: string, val: string | null | undefined}) {
if(!field || !val) return;
console.log(field, val);
const user: any[] = await prisma.$queryRaw`SELECT id from next_auth."User" WHERE ${field} = ${val};`
console.log(user);
if(user.length == 0) return false;
return true;
}I have this simple function and it was working as expected, but when I made the filed in the where clause dynamic it started to always return an empty array. I've tried to change the field name, but didn't work. And also when I try to give it a field name that doesn't exist, it also returns an empty array.
Top answer 1 of 5
1
You could use the Prisma.raw method. This method prevents the provided value from being escaped. This could lead to SQL injection if you use untrusted values as the field, so be sure to validate it. `SELECT id from next_auth."User" WHERE ${Prisma.raw(field)} = ${val};`
2 of 5
1
Value should be surrounded by single quotes in the query btw.
DEV Community
dev.to › this-is-learning › its-prisma-time-execute-your-own-queries-4olp
It's Prisma Time - Execute your own queries - DEV Community
January 28, 2022 - Sometimes, we need more performance and the query created by Prisma must be rewritten, in this case the $queryRaw method is our lifeline. This method uses the Tagged Template and it prevents attack by SQL-injection too.
Prisma
prisma.io › home › typedsql › typedsql › typedsql › typedsql
Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Documentation
This feature was heavily inspired by PgTyped and SQLx. Additionally, SQLite parsing is handled by SQLx. ... Learn how you can send raw SQL and MongoDB queries to your database using the raw() methods from the Prisma Client API ... Learn how to use SafeQL and Prisma Client extensions to work around features not natively supported by Prisma, such as PostGIS · Getting started with TypedSQLPassing Arguments to TypedSQL QueriesPassing array ...
DEV Community
dev.to › jquagliatini › improving-prisma-raw-query-typing-nhd
Improving Prisma raw queries typing. - DEV Community
March 1, 2023 - That being said, they exposed some intricate data in the dmmf. I don’t really know what dmmf stands for (data model mapping I suppose), but it’s everything I was looking for! I played a bit with it, and thought that I could use it to provide a helper constant, that I named P. More essentially, I needed to come up with a dynamic type to ease the maintenance. Oh boy was I pleased to have template litteral types! import { Prisma } from '@prisma/client'; export type PType = { [K in keyof typeof Prisma.ModelName]: { tableName: Prisma.Sql; fields: { [KK in keyof typeof Prisma[`${K}ScalarFieldEnum`]]: Prisma.Sql; }; }; };
Wanago
wanago.io › home › api with nestjs #109. arrays with postgresql and prisma
API with NestJS #109. Arrays with PostgreSQL and Prisma
May 22, 2023 - We learn how to manage arrays through raw SQL and Prisma in a NestJS project.
MCP Servers
mcpservers.org › home › agent skills library › prisma-client-api-raw-queries
prisma-client-api-raw-queries | Agent Skills Library
// ❌ SQL injection vulnerability! const email = userInput const users = await prisma.$queryRawUnsafe( `SELECT * FROM "User" WHERE email = '${email}'` ) // Array operations const users = await prisma.$queryRaw` SELECT * FROM "User" WHERE 'admin' = ANY(roles) ` // JSON operations const users = await prisma.$queryRaw` SELECT * FROM "User" WHERE metadata->>'theme' = 'dark' `
Author prisma
Wanago
wanago.io › home › api with nestjs #136. raw sql queries with prisma and postgresql range types
API with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range types
December 4, 2023 - Since the tstzrange data is not supported by Prisma, we need to use the $queryRaw tagged template that allows us to make a raw SQL query. It returns an array of results, but in our case, this array should have only one element.
Prisma
prisma.io › home › working with scalar lists › working with scalar lists › working with scalar lists › working with scalar lists
Working with scalar lists/arrays (Concepts) | Prisma Documentation
How to read, write, and filter by scalar lists / arrays.
GitHub
github.com › prisma › prisma › issues › 26545
Type inference in raw queries is broken for arrays containing single null element · Issue #26545 · prisma/prisma
March 7, 2025 - The expected behavior is that both queries should work (return an array of a single column with the NULL element). ... PrismaClientKnownRequestError: Invalid `prisma.$queryRaw()` invocation: Raw query failed.
Author prisma
Nico's Blog
nico.fyi › blog › prisma-typed-sql
The new TypedSQL in Prisma: Raw query with type safety | Nico's Blog
August 28, 2024 - import { randomQuestions } from ... which we specified in the randomQuestions.sql file. The questions variable is automatically typed as an array of Question objects....
Answer Overflow
answeroverflow.com › m › 1428774915787587695
queryRaw returns empty array - Prisma
October 17, 2025 - I am on the latest version of prisma client and prisma mariadb-adapter ... const queryEmbeddingString = vectorToString(embeddedMeal.embedding); // 3. Use a raw SQL query with MariaDB's vector functions console.log('Querying with embedding string:', queryEmbeddingString.substring(0, 100) + '...'); console.log('Thresholds:', { NOT_SIMILAR_THRESHOLD, RELATED_THRESHOLD }); const result = await prisma.$queryRaw<{ mealId: number; imageHash: string; distance: number }[]>` SELECT * FROM ( SELECT m.id as mealId, m.title as title, m.hash as mealHash, mi.imageHash as imageHash, VEC_DISTANCE_COSINE (m.emb
Prisma
prisma.io › home › create custom prisma client queries › create custom prisma client queries › create custom prisma client queries › create custom prisma client queries
Prisma Client extensions: query component | Prisma Documentation
const transactionExtension = Prisma.defineExtension((prisma) => prisma.$extends({ query: { user: { // Get the input `args` and a callback to `query` async findFirst({ args, query, operation }) { const [result] = await prisma.$transaction([query(args)]); // wrap the query in a batch transaction, and destructure the result to return an array return result; // return the first result found in the array }, }, }, }), ); const prisma = new PrismaClient().$extends(transactionExtension); ... Extend Prisma Client query operationsModify a specific operation in a specific modelModify a specific operation in all models of your schemaModify all operations in a specific modelModify all Prisma Client operationsModify all operations in all models of your schemaModify a top-level raw query operationMutate the result of a queryWrap a query into a batch transaction
Reddit
reddit.com › r/node › how to get the size of an array type column in prisma postgresql?
r/node on Reddit: How to get the size of an array type column in Prisma postgresql?
January 15, 2022 -
Is there any feature like cardinality function of postgresql in prisma? Or I would have to use raw query or get the the whole array from table and then get the no. of elements in it?
RedwoodJS Community
community.redwoodjs.com › get help and help others
Prisma queryRaw throws error when using template string variable in table name - Get Help and Help Others - RedwoodJS Community
April 14, 2022 - Below are what I have tried, let result = await db.$queryRaw`SELECT * from "Person"` // This works fine console.log(result) const model = 'Person' result = await db.$queryRaw`SELECT * from "${model}"` // This t…
Atomic Spin
spin.atomicobject.com › prisma-queryraw
Building an SQL Query from Variables with prisma.$queryRaw
July 19, 2024 - Using prisma.$queryRaw, I was able to write a basic query that returned the data I needed. However, I also needed the query to filter and order the data.
GitHub
github.com › prisma › prisma › issues › 14978
Type error in `IN` clause in postgreSQL using `$queryRaw` when upgrading to v4 · Issue #14978 · prisma/prisma
August 24, 2022 - const rows = await this.prismaClient.$queryRaw< { id: string }[] >(Prisma.sql` SELECT id FROM repro WHERE id = ANY (ARRAY[${Prisma.join(ids)}]::uuid[]); `)
Author prisma