Advanced queries (/docs/orm/fundamentals/advanced-queries)

For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending .md to its URL.

Use the SQL query builder on PostgreSQL and the pipeline builder on MongoDB for queries the ORM API can't express.

Location: ORM > Fundamentals > Advanced queries

Most queries fit the ORM API, and when one doesn't, there is a query builder. Prisma ORM 8 supports PostgreSQL, SQLite, and MongoDB; the SQL query builder is for PostgreSQL and SQLite, the pipeline builder for MongoDB. Neither builder asks you to write SQL as a string. When the builder cannot express a query, db.raw.sql lets you write one, with values still sent as parameters. Both builders come with @prisma/orm-postgres and @prisma/orm-mongo, so there is nothing extra to install.

The schema file is now contract.prisma; it is the same schema language as Prisma ORM 7 (with the type changes on Coming from Prisma ORM 7). The docs call it your contract, and TypeScript checks both builders against it.

You choose per query, not per app, so using the ORM API everywhere and a builder in the three places that need it is normal.

Every example here imports db, the client you create once in src/prisma/db.ts, and prisma orm init writes that file for you. The examples run from a file directly inside src/.

Every example on this page queries these four models:

model User {
  id    String  @id @default(cuid(2))
  email String  @unique
  name  String?
  posts Post[]

  @@map("user")
}

model Post {
  id        String   @id @default(cuid(2))
  title     String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])
  tags      Tag[]

  @@map("post")
}

model Tag {
  id    String @id @default(cuid(2))
  name  String @unique
  posts Post[]

  @@map("tag")
}

model PostTag {
  postId String
  tagId  String
  post   Post   @relation(fields: [postId], references: [id])
  tag    Tag    @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])
  @@map("post_tag")
}

In Prisma ORM 8 a many-to-many relation is the two list fields plus a model for the join table, as PostTag here; see Relations and joins. The MongoDB examples use the same models, except that each id is declared as id ObjectId @id @map("_id"), so the property is _id.

A SQL builder query takes two steps: first you build it, then you run it. You decide the exact SQL: the joins, the grouping, and the columns that come back.

Use it when:

• The query is easier to say in SQL: joins with conditions, computed columns, one row per group.
• You need PostgreSQL behavior the ORM API doesn't offer, such as RETURNING on a bulk insert.
• An aggregation needs precise control, such as ordering and limiting by a count in the database.
• A query is performance-sensitive and you want to decide its exact SQL.

Prefer the ORM API when the query is CRUD, filtered reads, or relation traversal. The reading, writing, and relations pages cover those methods, with less code and the same type safety.

Start from a table with db.sql.public.<table>. db.sql is the SQL query builder, keyed by table name. A table's name is the model's @@map value, or the model name exactly as written when the model has no @@map, and column names are the field names unless a field sets @map. public is the PostgreSQL schema your tables are in. If you put a model inside a namespace block in your contract, the block's name is the PostgreSQL schema instead, so for a model inside namespace billing { ... } you write billing in place of public: db.sql.billing.<table>.

db.runtime() is the database connection; call it whenever you run a query. query() always resolves to an array of rows, even when the query matches one row.

Chain the clauses you want, call .build(), and pass the result to db.runtime().query(...):

import { db } from "./prisma/db";

const publishedPosts = db.sql.public.post
  .select("id", "title", "authorId")
  .where((f, fns) => fns.eq(f.published, true))
  .limit(10).offset(20)
  .build();

const rows = await db.runtime().query(publishedPosts);

The .where(...) callback receives two arguments. The first, f, holds the column references, and the second, fns, holds the operators: eq, ne, gt, gte, lt, lte, in, notIn, and, or, and ilike for text columns on PostgreSQL. There are more; the SQL query builder reference has the full list.

Pass a value from your own code straight into an operator. Here authorId is a variable, such as one you read from a request:

const postsByAuthor = db.sql.public.post
  .select("id", "title")
  .where((f, fns) => fns.eq(f.authorId, authorId)).build();

The builder sends every value you pass this way to the database as a query parameter, never as text pasted into the SQL, so it is safe against SQL injection.

ResultType names the type of one row of a built query, so you can use that type in your own function signatures:

import type { ResultType } from "@prisma/orm-postgres/components/runtime";

type PublishedPost = ResultType<typeof publishedPosts>;

function titles(posts: PublishedPost[]) {
  return posts.map((post) => post.title);
}

ResultType<...> is one row, not the array, so await db.runtime().query(publishedPosts) resolves to PublishedPost[]. See ResultType in the reference.

Alias each side with .as(...), join on any condition, and project columns from both sides into a flat result. .select(...) takes column names, or a callback that builds the row from the aliases, or a name plus an expression for a computed column:

const postsWithAuthors = db.sql.public.post
  .as("p")
  .innerJoin(db.sql.public.user.as("u"), (f, fns) => fns.eq(f.p.authorId, f.u.id))
  .select((f) => ({ postId: f.p.id, title: f.p.title, authorEmail: f.u.email }))
  .where((f, fns) => fns.eq(f.p.published, true))
  .limit(10)
  .build();

const rows = await db.runtime().query(postsWithAuthors);
// Array<{ postId, title, authorEmail }>

After a join, f is keyed by the alias you gave each side, so a column is f.p.authorId rather than f.authorId.

Chain more joins for multi-hop traversals. This is how you get a flat post-tag list through a many-to-many join table, one row per pair:

const postTagPairs = db.sql.public.post_tag
  .as("pt")
  .innerJoin(db.sql.public.tag.as("t"), (f, fns) => fns.eq(f.pt.tagId, f.t.id))
  .innerJoin(db.sql.public.post.as("p"), (f, fns) => fns.eq(f.pt.postId, f.p.id))
  .select((f) => ({ postTitle: f.p.title, tagName: f.t.name }))
  .build();

const rows = await db.runtime().query(postTagPairs);

[
  { postTitle: 'Hello Prisma 8', tagName: 'databases' },
  { postTitle: 'Typed queries', tagName: 'typescript' }
]

The table here is post_tag because the model for the join table sets @@map("post_tag").

Answer "top N groups" questions, such as the authors with the most posts, by ordering and limiting on an aggregate directly in the database:

const topAuthors = db.sql.public.post
  .select((f, fns) => ({ authorId: f.authorId, posts: fns.count() }))
  .groupBy((f) => f.authorId)
  .orderBy((f, fns) => fns.count(), { direction: "desc" })
  .limit(5)
  .build();

const rows = await db.runtime().query(topAuthors);

[
  { authorId: 'cuid20000000000000000001', posts: 2 },
  { authorId: 'cuid20000000000000000002', posts: 1 }
]

Pass the same aggregate to .orderBy(...) that you passed to .select(...), and the database does the sorting and the limiting.

Use fns.countBigInt() when a count can grow past Number.MAX_SAFE_INTEGER, because it returns a bigint while fns.count() returns a JavaScript number. A count larger than Number.MAX_SAFE_INTEGER throws an error with code RUNTIME.DECODE_FAILED rather than rounding.

SQL builder writes take an array of rows. Use .returning(...) to choose which columns come back from the same statement:

const insertUser = db.sql.public.user
  .insert([{ email: "sql@prisma.io" }])
  .returning("id", "email").build();

const [insertedUser] = await db.runtime().query(insertUser);
// insertedUser is { id, email }. The id comes from the @default(cuid(2)) in the contract.

Drop the .returning(...) call and the insert returns no rows, so run it with db.runtime().execute(...), which resolves to { affectedRows }.

The builder writes updates and deletes too. Choose the rows with .where(...), and add .returning(...) when you want columns back. postId and tagId here are values from your own code:

const publishPost = db.sql.public.post
  .update({ published: true })
  .where((f, fns) => fns.eq(f.id, postId)).build();
const deleteTag = db.sql.public.tag.delete().where((f, fns) => fns.eq(f.id, tagId)).build();

const { affectedRows } = await db.runtime().execute(publishPost);
await db.runtime().execute(deleteTag);

Neither of those two returns rows, so both go to execute(). Add .returning(...) to either one and you run it with query() instead.

Raw SQL comes in two sizes. When the operators don't cover a single expression you need, write that expression with fns.raw and declare what type it returns with .returns(...), and the rest of the query stays typed.

Calling .select a second time adds columns rather than replacing the ones already chosen. The second call takes a name for the new column, then the expression:

const users = db.sql.public.user
  .select("id", "email")
  .select("upperEmail", (f, fns) => fns.raw`UPPER(${f.email})`.returns("pg/text@1"))
  .limit(10).build();

const rows = await db.runtime().query(users);
// [{ id: 'cuid20000000000000000001', email: 'alice@prisma.io', upperEmail: 'ALICE@PRISMA.IO' }, ...]

.returns(...) takes a type name as a string: "pg/text@1" is the PostgreSQL type text, and type names always end in @1. The raw queries reference covers how a bare value picks its type name.

A raw fragment is as safe as an operator, and you can interpolate columns and other typed expressions the same way.

When the whole statement has to be raw, write it with db.raw.sql and end it with one of two calls. Use .returnsRow(...) when the statement returns rows, then run the built query with query():

const limit = 10;
const user = db.sql.public.user; // alias, to shorten user.columns.id

const authorPostCounts = db.raw.sql`
  SELECT u.id, u.email, count(p.id) AS "postCount"
  FROM "user" u
  LEFT JOIN "post" p ON p."authorId" = u.id
  GROUP BY u.id, u.email
  LIMIT ${limit}
`
  .returnsRow({ id: user.columns.id, email: user.columns.email, postCount: "pg/int8@1" })
  .build();

const rows = await db.runtime().query(authorPostCounts);

Quote identifiers as PostgreSQL requires; "authorId" keeps the camel case. Each entry in the .returnsRow(...) object says how to read one result column. An entry is either a column from the contract, as in user.columns.id, or a type name as a string for a result column the contract has no counterpart for. "pg/int8@1" is a 64-bit integer, so postCount comes back as a bigint.

Use .affectedCount() when the statement returns no rows, then run the built query with execute(), which resolves to { affectedRows }:

const publishDrafts = db.raw.sql`
  UPDATE "post" SET "published" = true WHERE "published" = false
`.affectedCount().build();

const { affectedRows } = await db.runtime().execute(publishDrafts);

To use one raw statement inside another, interpolate it with ${...}. End the inner statement at .returnsRow(...) without .build(); call .build() only on the outer one. Every un-built raw statement exposes its declared columns as .returns, so the outer statement can reuse them:

const minPosts = 5;

const authorsWithPosts = db.raw.sql`
  SELECT p."authorId" AS "authorId", count(*) AS "postCount"
  FROM "post" p
  GROUP BY p."authorId"
  HAVING count(*) >= ${minPosts}
`.returnsRow({
  authorId: db.sql.public.post.columns.authorId,
  postCount: "pg/int8@1",
});

const activeAuthors = db.raw.sql`
  WITH active AS (${authorsWithPosts})
  SELECT u.email, active."postCount"
  FROM active
  JOIN "user" u ON u.id = active."authorId"
`.returnsRow({
  email: db.sql.public.user.columns.email,
  postCount: authorsWithPosts.returns.postCount,
}).build();

const rows = await db.runtime().query(activeAuthors);

A column the inner statement already declared can be reused in the outer row object, as authorsWithPosts.returns.postCount is here. The raw queries reference documents every raw query method.

On MongoDB the builder is at db.query, and it builds a typed MongoDB aggregation pipeline: a sequence of stages such as $match, $group, $sort, and $lookup, checked against your contract. There is no db.sql on MongoDB.

All MongoDB aggregation happens here: the ORM API has no .aggregate(...) on MongoDB, so counts, sums, and grouping are pipeline builder work.

Use it when:

• You need an aggregation: counts, grouping, or summaries per key.
• You want to join and reshape documents across collections with $lookup.
• A query needs MongoDB pipeline stages that don't map to the ORM API, such as multi-stage filtering and projection.
• You want to filter with an operator such as gt or ne. On MongoDB the ORM API's .where(...) matches exact values only, and .match(...) here takes the operators.

Prefer the ORM API when the query is document create, read, update, or delete, and for reading a reference relation. A reference relation is a relation stored as an id in the document rather than as an embedded document, and .include(...) already covers the common $lookup case.

Start from a collection with db.query.from(...), which takes the collection name as a string: the model's @@map value, or the model name exactly as written when the model has no @@map. Every model above sets @@map, so the collections are user, post, tag, and post_tag. On MongoDB the ORM API uses the same name, as in db.orm.post.

acc holds the aggregate functions you can use inside .group(...), such as acc.count() and acc.max(f.createdAt). In the pipeline builder, fields use their stored names, so the id is _id (see the note under the contract above), and _id is also the grouping key in .group(...). On MongoDB db.runtime() returns a promise, so await it before you run anything. Chain stages, call .build(), and run the result:

import { acc } from "@prisma/orm-mongo/query-builder";
import { db } from "./prisma/db";

const runtime = await db.runtime();

// Post count per author, most prolific first
const postsByAuthor = db.query
  .from("post")
  .group((f) => ({ _id: f.authorId, postCount: acc.count() }))
  .sort({ postCount: -1 }) // -1 is descending
  .build();

const rows = await runtime.query(postsByAuthor);

[
  { _id: "clx3n9x4v0000abc123def456", postCount: 3 },
  { _id: "clx3n9x4v0001abc123def457", postCount: 2 }
]

The rows are typed by the stages you built.

Chain .match(...) before .group(...) to aggregate over a subset, the pipeline equivalent of WHERE before GROUP BY:

const draftsByAuthor = db.query
  .from("post")
  .match((f) => f.published.eq(false))
  .group((f) => ({ _id: f.authorId, draftCount: acc.count() })).build();

const rows = await runtime.query(draftsByAuthor);

In a .match(...) callback the operators are on the field itself, as in f.published.eq(false), while the SQL query builder puts them on fns instead. The pipeline builder reference lists the operators you can use here.

Use .lookup(...) for a type-checked join against another collection. The joined documents arrive under the name you give .as(...):

const postsWithAuthors = db.query
  .from("post")
  .match((f) => f.published.eq(true))
  .lookup((from) =>
    from("user").on((local, foreign) => ({ local: local.authorId, foreign: foreign._id })).as("author"),
  )
  .build();

const rows = await runtime.query(postsWithAuthors);
const authorId = "68e1f0c2a4b9d3e5f7a1b2c3";
const mine = rows.filter((row) => String(row.author[0]?._id) === authorId);

Each row is a post document with an extra author field, and that field is an array of the matching user documents. row.author is empty when the author is missing. A document pulled in by .lookup(...) keeps MongoDB's own ObjectId for _id, so wrap it in String(...) before you compare it with an id from your own code. The pipeline builder reference has the rest of the stages.

db.orm holds your models by model name, as in db.orm.public.User on PostgreSQL. On MongoDB there is no schema segment, and the name is the collection: db.orm.post.

A built query that returns rows runs through db.runtime().query(builtQuery) on PostgreSQL. On MongoDB, await the connection first: (await db.runtime()).query(builtQuery). A write that returns no rows goes to execute(builtQuery) instead, which resolves to { affectedRows }.

Inside a transaction, tx is the argument your callback receives, and you run the built query on it: await db.transaction(async (tx) => tx.query(builtQuery)). tx.query(...) and tx.execute(...) run built queries inside a transaction; see Transactions.

Projects created with npm create prisma@latest include the Prisma ORM skills for your coding agent. The prisma-8 skill covers both builders and the choice between them and the ORM API, so try prompts that map to each section:

• "Using the prisma-8 skill, write a SQL builder query for the top 10 authors by post count."
• "This report needs post and author columns in one flat result. Build the join with the SQL query builder."
• "On MongoDB, group posts per author with the pipeline builder and sort by the count."
• "Review this file and tell me which queries should stay on the ORM API and which need a builder."

• Read data: the ORM API these builders extend.
• Relations and joins: understand relationships before reaching for explicit joins.
• Transactions: run built queries inside one.

• Reading data: Fetch one record or many with Prisma ORM, then filter, select, sort, paginate, and iterate the results.
• Relations and joins: Read related records in one query with .include(), and understand how one-to-one, one-to-many, and many-to-many relationships work.
• Transactions: Run several writes so they all succeed or all fail together with db.transaction().
• Writing data: Create, update, delete, and upsert records with Prisma ORM, one at a time or in bulk.