Raw SQL comparisons (Prisma ORM v6) (/docs/orm/v6/more/troubleshooting/raw-sql-comparisons)
For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending
.mdto its URL.
Compare columns of the same table with raw queries in Prisma ORM
Location: ORM > v6 > More > Troubleshooting > Raw SQL comparisons
Comparing different columns from the same table is a common scenario. This page shows how to achieve this using raw queries for Prisma ORM versions prior to 4.3.0.
[!WARNING] From version 4.3.0, you do not need to use raw queries to compare columns in the same table. You can use the
<model>.fieldsproperty to compare the columns.
Comparing numeric values
Section titled “Comparing numeric values”Example: retrieving posts that have more comments than likes.
model Post {
id Int @id @default(autoincrement())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
title String
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId Int
likesCount Int
commentsCount Int
}PostgreSQL / CockroachDB
Section titled “PostgreSQL / CockroachDB”const response =
await prisma.$queryRaw`SELECT * FROM "public"."Post" WHERE "likesCount" < "commentsCount";`;const response =
await prisma.$queryRaw`SELECT * FROM \`public\`.\`Post\` WHERE \`likesCount\` < \`commentsCount\`;`;SQLite
Section titled “SQLite”const response =
await prisma.$queryRaw`SELECT * FROM "Post" WHERE "likesCount" < "commentsCount";`;Comparing date values
Section titled “Comparing date values”Example: get all projects completed after the due date.
model Project {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id])
authorId Int
dueDate DateTime
completedDate DateTime
createdAt DateTime @default(now())
}PostgreSQL / CockroachDB
Section titled “PostgreSQL / CockroachDB”const response =
await prisma.$queryRaw`SELECT * FROM "public"."Project" WHERE "completedDate" > "dueDate";`;const response =
await prisma.$queryRaw`SELECT * FROM \`public\`.\`Project\` WHERE \`completedDate\` > \`dueDate\`;`;SQLite
Section titled “SQLite”const response =
await prisma.$queryRaw`SELECT * FROM "Project" WHERE "completedDate" > "dueDate";`;Related pages
Section titled “Related pages”Bundler issues: Solve ENOENT package error with vercel/pkg and other bundlersCheck constraints: Learn how to configure CHECK constraints for data validation with Prisma ORM and PostgreSQL.GraphQL autocompletion: Get autocompletion for Prisma Client queries in GraphQL resolvers with plain JavaScriptMany-to-many relations: Learn how to model, query, and convert many-to-many relations with Prisma ORMNext.js: Best practices and troubleshooting for using Prisma ORM with Next.js applications.