Raw queries reference (/docs/orm/reference/raw-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.

Reference for Prisma ORM raw queries: PostgreSQL raw SQL and MongoDB raw commands.

Location: ORM > Reference > Raw queries reference

Raw queries are the way out when the typed APIs can't express the query you need. Reach for a typed API first: the ORM client for everyday reads and writes, the SQL query builder for PostgreSQL joins and aggregates, and the pipeline builder for MongoDB aggregation. When none of them reaches the SQL clause or MongoDB command you need, drop down to raw.

There are two kinds of raw query: PostgreSQL raw SQL (the fns.raw tagged template for fragments and the db.raw.sql tag for whole statements) and MongoDB raw commands (db.raw.collection(...) and db.query.rawCommand(...)).

Raw SQL comes in two forms.

A raw fragment is a piece of SQL you put inside a builder query. Write it as a tagged template with fns.raw and pass it to a select(), where(), orderBy(), or update() call. The rest of the query stays typed.

A whole raw statement is a complete SQL statement written with db.raw.sql. If it returns rows, end it with .returnsRow(spec).build(), where the spec is a column list defined below, and pass the result to runtime.query(...). If it does not, end it with .affectedCount().build() and pass the result to runtime.execute(...).

[!WARNING] Only

.returnsRow()

converts values for you

A type id such as pg/text@1 tells Prisma ORM what JavaScript type a column should become, and the property is named codecId. .returnsRow(spec) names a type id for every column of a whole db.raw.sql statement, so you get real JavaScript values back. A fragment's .returns(...) does not convert anything: it only tells TypeScript what type to expect, so the value is whatever the pg package, the PostgreSQL driver underneath, gives you. For a text or an integer column that is already a JavaScript string or number, while a numeric column arrives as a string. A date column gets no conversion by Prisma ORM, so you get the pg package's own value for that column.

The examples in this section run against the user / post schema from the SQL query builder page. See its example schema for the full model definitions. As on that page, create the client with postgres(...) and reach tables through db.sql.public, which is keyed by table name (the model name exactly as written, unless the model sets the @@map attribute, which renames a table). db.runtime() gives you the connection that runs a built query.

import postgres from '@prisma/orm-postgres/runtime';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };
const db = postgres<Contract>({ contractJson, url: process.env.DATABASE_URL });
const runtime = db.runtime();
const aliceId = '00000000-0000-4000-8000-000000000001';

A projection is the select() list. A select() callback receives two arguments: f, with one property per column, and fns, with the expression helpers. fns.raw is a tagged template. Write a SQL fragment, interpolate columns and values with ${...}, and declare the fragment's result type with .returns(typeId). See select() for every form it accepts.

const plan = db.sql.public.user
  .select('id')
  .select('upperEmail', (f, fns) => fns.raw`UPPER(${f.email})`.returns('pg/text@1'))
  .where((f, fns) => fns.eq(f.id, aliceId))
  .build();
const rows = await runtime.query(plan);
// rows === [{ id: aliceId, upperEmail: 'ALICE@EXAMPLE.COM' }]

.returns(...) only tells TypeScript what to expect: it does not cast the value in SQL and it does not change how the pg package reads it. It takes a type id string, or an object of the form { codecId, nullable }. See Binding a bare value with param() for the type ids you are most likely to write, and the SQL query builder's fns.raw and .returns() for the full treatment.

where()'s callback returns a boolean expression, the same thing the comparison helpers such as fns.eq(...) return. A raw fragment that declares .returns('pg/bool@1') is itself a boolean expression, so you can pass it straight to where() without wrapping it in fns.eq(...).

const plan = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.raw`LENGTH(${f.email}) > 15`.returns('pg/bool@1'))
  .build();
const rows = await runtime.query(plan);
// only users whose email is longer than 15 characters

You can interpolate another expression, such as a comparison, a column reference, or another raw fragment. The expression itself goes into the fragment, not its rendered SQL, so it stays type-checked. An interpolated value is sent as a query parameter, never pasted into the SQL. A column or table name cannot be interpolated as a string, because a string becomes a parameter. When the column is chosen at run time, pick it in your own code from a fixed set of columns you control, and choose the matching fns.raw fragment or select() call for each one.

const plan = db.sql.public.user
  .select('id', 'kind')
  .select('kindLabel', (f, fns) => fns.raw`CASE WHEN ${fns.eq(f.kind, 'admin')} THEN 'admin' ELSE 'regular user' END`.returns('pg/text@1'))
  .build();
const rows = await runtime.query(plan);
// each row's kindLabel is 'admin' or 'regular user'

Interpolate a plain number, string, boolean, bigint, or Uint8Array directly. For anything else, such as a point in time or a decimal you want stored as numeric, wrap the value in param(value, { codecId }) and name the type id yourself. Import param from @prisma/orm-postgres/relational-core/expression.

A bare value that you do interpolate directly gets the type id Prisma ORM picks from its JavaScript type:

A type id is pg/, a name, and @1. The name is usually the PostgreSQL type, and a few carry a suffix that says which JavaScript type you get, such as pg/int8number@1 for a number and pg/timestamptz-temporal@1 for a Temporal.Instant. Between those two, pick pg/int8@1 when the value can exceed 2^53. Three more you are likely to write are pg/uuid@1, pg/numeric@1, and pg/jsonb@1. Those are the common ones, not the whole set. For any column, db.sql.public.<table>.columns.<column>.codecId is its id, and the same ids are in contract.json under storage.namespaces.<schema>.entries.table.<table>.columns.<column>.codecId. A point in time is the case param() exists for, and pg/timestamptz-temporal@1 takes a Temporal.Instant, the standard JavaScript object for a point in time, not a Date. Temporal is a global in Node.js 26.8.2 and later, and on earlier versions you install temporal-polyfill and add import 'temporal-polyfill/full/global'.

import { param } from '@prisma/orm-postgres/relational-core/expression';

const cutoff = param(Temporal.Instant.from('2024-01-01T00:00:00Z'), { codecId: 'pg/timestamptz-temporal@1' });
const plan = db.sql.public.user
  .select('id', 'email')
  .where((f, fns) => fns.raw`${f.createdAt} > ${cutoff}`.returns('pg/bool@1'))
  .build();
const rows = await runtime.query(plan);
// users created after the start of 2024

The object form of .returns() declares a nullable result. Use it when a fragment can evaluate to SQL NULL, so the result type is T | null.

const plan = db.sql.public.user
  .select('id')
  .select('adminName', (f, fns) => fns.raw`CASE WHEN ${fns.eq(f.kind, 'admin')} THEN ${f.displayName} END`.returns({ codecId: 'pg/text@1', nullable: true }))
  .build();
const rows = await runtime.query(plan);
// adminName is the displayName for admins, null for everyone else

db.raw is the client's raw API, beside db.sql and db.orm. On PostgreSQL it holds sql, the same tagged template as fns.raw. On MongoDB it holds collection(...) instead, which the MongoDB raw commands section covers. Unlike fns.raw, db.raw.sql works outside a builder callback, and it has the extra methods that turn a whole statement into a query you can run.

Use .returns(...) to build an expression ahead of time and pass it to the object form of select(), which names each output column:

const serverNow = db.raw.sql`now()`.returns('pg/timestamptz-temporal@1');
const plan = db.sql.public.user
  .select((f) => ({ id: f.id, serverNow }))
  .where((f, fns) => fns.eq(f.id, aliceId))
  .build();
const rows = await runtime.query(plan);

Use .returnsRow(spec) to run a whole statement and get converted rows back. The spec names the columns of every returned row, and each entry is either a column from your contract or a type id. A column such as db.sql.public.user.columns.id brings its own conversion, nullability, and TypeScript type. Write a type id for a column your contract has no match for, and the object form { codecId: 'pg/text@1', nullable: true } when that column can be NULL, as a LEFT JOIN column can. If a returned row has no column your spec names, the query throws an error whose code is RUNTIME.RAW_ROW_COLUMN_MISSING. A failing statement throws a SqlQueryError whose sqlState is the PostgreSQL error code for the failure, such as 23505 for a unique-constraint violation.

const user = db.sql.public.user;
const post = db.sql.public.post;

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

const rows = await runtime.query(plan);
// row.email is a string; row.postCount is a bigint

Use .affectedCount(), which takes no spec, for a statement that returns no rows. Pass the built query to runtime.execute(...), which resolves to { affectedRows }, the number of rows the statement changed. You can also run a built raw query inside a transaction: db.transaction() gives you a tx, and tx.query(...) and tx.execute(...) take the same built queries as runtime.query(...) and runtime.execute(...) do.

const plan = db.raw.sql`
  UPDATE "post"
  SET priority = ${'urgent'}
  WHERE "userId" IN (SELECT id FROM "user" WHERE kind = ${'admin'})
`.affectedCount().build();

const stats = await runtime.execute(plan);
// stats.affectedRows

You can nest a .returnsRow() query inside another: that is how you write a subquery or a CTE, which is a WITH query. Interpolate it before .build(), and call .build() on the outer one only. Its .returns property holds its declared columns, so outer.returnsRow({ postCount: inner.returns.postCount }) reuses a declaration. You cannot nest a statement that ends with .affectedCount(), because it produces no rows. You can nest an UPDATE ... RETURNING, because it does.

[!NOTE] Three names look alike

.returns(typeId) is a method on a fragment, .returnsRow(spec) is a method on a statement, and .returns is a property on the object .returnsRow() gives you.

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

const plan = db.raw.sql`
  WITH active AS (${authorsWithPosts})
  SELECT u.email, active."postCount"
  FROM active
  JOIN "user" u ON u.id = active."userId"
  ORDER BY active."postCount" DESC, u.email ASC
`.returnsRow({ email: user.columns.email, postCount: authorsWithPosts.returns.postCount }).build();

Interpolation accepts columns, expressions, param(...) values, raw queries that return rows, and the bare types number, bigint, string, boolean, and Uint8Array. Interpolating anything else, such as a Date, is rejected by TypeScript when you compile. In plain JavaScript the tagged template throws as soon as you call it, with an error whose code is RUNTIME.RAW_SQL_UNSUPPORTED_INTERPOLATION and this message:

unsupported JS value type for raw-SQL interpolation: wrap this value in `param(...)` with an explicit codec

To bind any value whose type is not in the list, wrap it in param(value, { codecId }) with the right type id. For a Date, convert it to a Temporal.Instant first and bind that.

db.raw.collection(collectionName) returns an object with nine methods for running MongoDB commands directly against a collection. The collection name is the one your contract declares ('users', 'posts'), the same name the ORM client uses (db.orm.users). MongoDB has no schemas, so there is no public segment here and none on db.orm. There is no raw find(), so use aggregate<Row>() with a $match stage. MongoDB has no transactions in Prisma ORM, so a raw command cannot run inside one. See Transactions (MongoDB).

Each of these nine methods builds a command: call .build() on it, then run every one of them with runtime.query(...), including the writes. The raw collection methods all run through query(). A write returns a one-element array, so destructure it with const [result] = ....

[!WARNING] MongoDB raw results are native BSON values

Nothing on the MongoDB side of this page is converted for you. An _id comes back as it is stored: one that MongoDB generated is an ObjectId instance from the mongodb package, the MongoDB driver underneath, not a hex string. Compare one with String(row._id) === aliceId. Raw filters take native BSON values too, so build ids with that package's ObjectId class. The write methods give you the counts and ids under the mongodb package's own key names, { insertedId }, { matchedCount, modifiedCount, upsertedCount, upsertedId }, and { deletedCount }, and you read those keys directly.

The examples in this section run against the users / posts schema from the pipeline builder page. See its example schema for the full model definitions. Create the client with mongo(...), get the connection that runs a built query from await db.runtime(), and declare the two example ids once:

import mongo from '@prisma/orm-mongo/runtime';
import { ObjectId } from 'mongodb';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };
const db = mongo<Contract>({ contractJson, url: process.env.MONGODB_URL, dbName: 'app' });
const runtime = await db.runtime();
const aliceId = '6539a1f2c3d4e5f60718293a';
const bobId = '6539a1f2c3d4e5f60718293b';

Run a raw aggregation pipeline against a collection. The type parameter says what each returned row looks like. The pipeline stages are raw MongoDB documents, sent as you wrote them, so a filter on _id with a real ObjectId matches.

const plan = db.raw.collection('posts').aggregate<{ _id: unknown; title: string }>([{ $match: { title: 'Hello world' } }]).build();
const [row] = await runtime.query(plan);
// row.title === 'Hello world'; row._id is a raw ObjectId

Insert one or many documents. Both return the mongodb package's own insert result, with ObjectId instances for the generated ids. insertMany()'s insertedIds is a plain array of ObjectId, in the order you passed the documents.

const users = db.raw.collection('users');
const onePlan = users.insertOne({ name: 'Dave', email: 'dave@example.com', role: 'author' }).build();
const [oneResult] = await runtime.query(onePlan);
// { insertedId: <ObjectId> }

const manyPlan = users.insertMany([{ name: 'Eve', email: 'eve@example.com', role: 'author' }, { name: 'Frank', email: 'frank@example.com', role: 'author' }]).build();
const [manyResult] = await runtime.query(manyPlan);
// { insertedCount: 2, insertedIds: [<ObjectId>, <ObjectId>] }

Update one or every matching document. The first argument is a raw filter document, and the second is either an update document such as { $set: ... } or an aggregation pipeline, which is an array of stages. Both return { matchedCount, modifiedCount, upsertedCount, upsertedId }.

const plan = db.raw.collection('users').updateOne({ _id: new ObjectId(aliceId) }, { $set: { bio: 'Updated bio' } }).build();
const [result] = await runtime.query(plan);
// { matchedCount: 1, modifiedCount: 1, ... }

An array update is a full aggregation-pipeline update, so a stage can reference the document's other fields.

const plan = db.raw.collection('users').updateMany({ role: 'author' }, [{ $set: { bio: { $concat: ['bio for ', '$name'] } } }]).build();
const [result] = await runtime.query(plan);
// { matchedCount: 2, modifiedCount: 2, ... }

Delete one or every matching document. Both return { deletedCount }.

const onePlan = db.raw.collection('users').deleteOne({ _id: new ObjectId(bobId) }).build();
const [oneResult] = await runtime.query(onePlan);
// { deletedCount: 1 }

const manyPlan = db.raw.collection('users').deleteMany({ role: 'author' }).build();
const [manyResult] = await runtime.query(manyPlan);
// { deletedCount: 2 }

Atomically update a matching document and return the document as it was before the update. When no document matches and upsert is true, the update inserts one.

• findOneAndUpdate() accepts only { upsert } as its third argument. It cannot sort, and it cannot give you the document as it is after the update. TypeScript rejects sort or returnDocument in the options object.

To sort, or to read the document after the update, build the command yourself and run it through db.query.rawCommand(...). new RawFindOneAndUpdateCommand(...) takes six arguments:

• collection: the collection name. Required.
• filter: the filter document. Required.
• update: an update document, or an array of aggregation-pipeline stages. Required.
• upsert: a boolean. Optional, and false when you leave it out.
• sort: a record of field name to 1 or -1. Optional.
• returnDocument: 'before' or 'after'. Optional. Leave it out and you get the document as it was before the update.

import { RawFindOneAndUpdateCommand } from '@prisma/orm-mongo/query-ast/execution';

// collection, filter, update, upsert, sort, returnDocument
const command = new RawFindOneAndUpdateCommand('users', { _id: new ObjectId(aliceId) }, { $inc: { count: 1 } }, true, { count: -1 }, 'after');
const [updated] = await runtime.query(db.query.rawCommand(command));

findOneAndUpdate() returns the document as it was before your update. The first call creates the document, so there is nothing to return and you get []. The second call returns count: 1, which is the value before that call added one. The example schema has no counter collection, so users stands in for one here. MongoDB allows a plain string _id, so 'pageViews' is a valid id. The advice to build ids with ObjectId applies to ids MongoDB generated.

const filter = { _id: 'pageViews' };
const update = { $inc: { count: 1 }, $setOnInsert: { _id: 'pageViews' } };
const counter = db.raw.collection('users');
const bump = () => runtime.query(counter.findOneAndUpdate(filter, update, { upsert: true }).build());
const first = await bump(); // [], because the insert has no earlier version
const second = await bump(); // [{ ..., count: 1 }], the version before the second increment
const third = await bump(); // [{ ..., count: 2 }]

Atomically delete a matching document and return it. The yielded row is the raw matched document itself, not a wrapper object, so its _id is a raw ObjectId.

const plan = db.raw.collection('users').findOneAndDelete({ _id: new ObjectId(aliceId) }).build();
const [deleted] = await runtime.query(plan);
// deleted is the removed document, and its _id is a raw ObjectId
const isAlice = String(deleted._id) === aliceId; // true

db.query.rawCommand(command) runs a raw MongoDB command, sent exactly as you wrote it. See the pipeline builder. It returns a built query already, so do not call .build() on it. Unlike aggregate<Row>() it has no row type parameter, so its rows come back as unknown and you cast them yourself.

import { RawAggregateCommand } from '@prisma/orm-mongo/query-ast/execution';

const plan = db.query.rawCommand(new RawAggregateCommand('posts', [{ $count: 'total' }]));
const rows = (await runtime.query(plan)) as { total: number }[];
// [{ total: 2 }]

rawCommand() is the way out when the typed pipeline builder can't express a query, such as filtering by _id equality. The command you pass is sent unchanged, so a real ObjectId in a pipeline document matches. aggregate<Row>() on db.raw.collection() handles _id filtering too, and it gives you a row type, so use rawCommand() when you are already working in db.query and need one raw command there. For the full treatment see the pipeline builder's rawCommand() section.

const plan = db.query.rawCommand(new RawAggregateCommand('posts', [{ $match: { _id: new ObjectId('6539a1f2c3d4e5f60718293c') } }]));
const rows = await runtime.query(plan);

• Error reference: Every structured error code Prisma ORM can emit, by namespace, with the condition that raises it.
• Migration API: Every method and helper a migration.ts can use: the Migration class, the PostgreSQL operations, the column and constraint helpers, rawSql, and the MongoDB operations.
• ORM client reference: Reference for the Prisma ORM client's query, mutation, filter, and aggregate methods.
• Pipeline builder reference: Reference for the Prisma ORM MongoDB pipeline builder's stages, accumulators, expression helpers, and write methods.
• SQL query builder reference: Reference for the Prisma ORM SQL query builder's select, mutation, and grouped query methods.