Raw SQL comparisons - Prisma

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>.fields property to compare the columns.

:::

Comparing numeric values

Example: retrieving posts that have more comments than likes.

prisma

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

const response =
  await prisma.$queryRaw`SELECT * FROM "public"."Post" WHERE "likesCount" < "commentsCount";`;

MySQL

const response =
  await prisma.$queryRaw`SELECT * FROM \`public\`.\`Post\` WHERE \`likesCount\` < \`commentsCount\`;`;

SQLite

const response =
  await prisma.$queryRaw`SELECT * FROM "Post" WHERE "likesCount" < "commentsCount";`;

Comparing date values

Example: get all projects completed after the due date.

prisma

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

const response =
  await prisma.$queryRaw`SELECT * FROM "public"."Project" WHERE "completedDate" > "dueDate";`;

MySQL

const response =
  await prisma.$queryRaw`SELECT * FROM \`public\`.\`Project\` WHERE \`completedDate\` > \`dueDate\`;`;

SQLite

const response =
  await prisma.$queryRaw`SELECT * FROM "Project" WHERE "completedDate" > "dueDate";`;