# Advanced queries (/docs/orm/fundamentals/advanced-queries) > For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/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](https://www.prisma.io/docs/orm/coming-from-prisma-orm-7#schema)). 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: ```prisma model User { id String @id @default(cuid(2)) email String @unique name String? posts Post[] } 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[] } model Tag { id String @id @default(cuid(2)) name String @unique posts Post[] } 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](https://www.prisma.io/docs/orm/fundamentals/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`. ## PostgreSQL: SQL query builder [#postgresql-sql-query-builder] 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](https://www.prisma.io/docs/orm/fundamentals/reading-data), [writing](https://www.prisma.io/docs/orm/fundamentals/writing-data), and [relations](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins) pages cover those methods, with less code and the same type safety. ### Build a query and run it [#build-a-query-and-run-it] Start from a table with `db.sql.public.`. `db.sql` is the SQL query builder, keyed by table name. A table's name is the model name with a lowercase first letter unless the model sets `@@map`, and column names are the field names unless a field sets `@map`. `public` is the PostgreSQL schema your tables are in, unless the model sits in a `namespace billing { ... }` block in your contract, in which case the path is `db.sql.billing.
`. `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(...)`: ```typescript 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](https://www.prisma.io/docs/orm/reference/sql-query-builder#built-in-functions) 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: ```typescript 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: ```typescript import type { ResultType } from "@prisma/orm-postgres/components/runtime"; type PublishedPost = ResultType; 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`](https://www.prisma.io/docs/orm/reference/sql-query-builder#resulttype) in the reference. ### Join tables with precise control [#join-tables-with-precise-control] 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: ```typescript 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](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#many-to-many), one row per pair: ```typescript 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); ``` ```js no-copy [ { 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")`. ### Group and rank results [#group-and-rank-results] Answer "top N groups" questions, such as the authors with the most posts, by ordering and limiting on an aggregate directly in the database: ```typescript 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); ``` ```js no-copy [ { 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. ### Write with RETURNING [#write-with-returning] SQL builder writes take an array of rows. Use `.returning(...)` to choose which columns come back from the same statement: ```typescript 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: ```typescript 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 [#raw-sql] 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: ```typescript 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](https://www.prisma.io/docs/orm/reference/raw-queries) 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()`: ```typescript 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 }`: ```typescript 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: ```typescript 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](https://www.prisma.io/docs/orm/reference/raw-queries#the-client-level-dbrawsql-tag) documents every raw query method. ## MongoDB: Pipeline builder [#mongodb-pipeline-builder] 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. ### Build a pipeline and run it [#build-a-pipeline-and-run-it] 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 with a lowercase first letter when the model has no `@@map`. The models above set no `@@map` except `PostTag`, so the collections are `user`, `post`, `tag`, and `post_tag`. That is the same name the ORM API uses, 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: ```typescript 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); ``` ```js no-copy [ { _id: "clx3n9x4v0000abc123def456", postCount: 3 }, { _id: "clx3n9x4v0001abc123def457", postCount: 2 } ] ``` The rows are typed by the stages you built. ### Filter and group in stages [#filter-and-group-in-stages] Chain `.match(...)` before `.group(...)` to aggregate over a subset, the pipeline equivalent of `WHERE` before `GROUP BY`: ```typescript 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](https://www.prisma.io/docs/orm/reference/pipeline-builder#match) lists the operators you can use here. ### Join collections with $lookup [#join-collections-with-lookup] Use `.lookup(...)` for a type-checked join against another collection. The joined documents arrive under the name you give `.as(...)`: ```typescript 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](https://www.prisma.io/docs/orm/reference/pipeline-builder#lookup) has the rest of the stages. ## Choose the right query API [#choose-the-right-query-api] | You need | Use | | -------------------------------------------------------------- | ------------------------------------------- | | CRUD, filters, relations, simple aggregates | ORM API (`db.orm`) | | Explicit join, computed projection, grouped top-N, `RETURNING` | SQL query builder (`db.sql.public.
`) | | A whole SQL statement of your own, such as a CTE | Raw SQL (`db.raw.sql`) | | `$group`, `$lookup` with reshaping, any MongoDB aggregation | Pipeline builder (`db.query.from(...)`) | `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](https://www.prisma.io/docs/orm/fundamentals/transactions). ## Prompt your coding agent [#prompt-your-coding-agent] Projects created with `npm create prisma@latest` include the [Prisma ORM skills](https://www.prisma.io/docs/ai/tools/skills#available-skills-for-prisma-8) 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." ## Next [#next] * [Read data](https://www.prisma.io/docs/orm/fundamentals/reading-data): the ORM API these builders extend. * [Relations and joins](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins): understand relationships before reaching for explicit joins. * [Transactions](https://www.prisma.io/docs/orm/fundamentals/transactions): run built queries inside one. ## Related pages - [`Reading data`](https://www.prisma.io/docs/orm/fundamentals/reading-data): Fetch one record or many with Prisma ORM, then filter, select, sort, paginate, and iterate the results. - [`Relations and joins`](https://www.prisma.io/docs/orm/fundamentals/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`](https://www.prisma.io/docs/orm/fundamentals/transactions): Run several writes so they all succeed or all fail together with db.transaction(). - [`Writing data`](https://www.prisma.io/docs/orm/fundamentals/writing-data): Create, update, delete, and upsert records with Prisma ORM, one at a time or in bulk.