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

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](https://www.prisma.io/docs/orm/reference/orm-client) for everyday reads and writes, the [SQL query builder](https://www.prisma.io/docs/orm/reference/sql-query-builder) for PostgreSQL joins and aggregates, and the [pipeline builder](https://www.prisma.io/docs/orm/reference/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(...)`).

## PostgreSQL raw SQL [#postgresql-raw-sql]

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](#the-client-level-dbrawsql-tag), 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](https://www.prisma.io/docs/orm/reference/sql-query-builder#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.

```ts
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';
```

### `fns.raw` in a projection [#fnsraw-in-a-projection]

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()`](https://www.prisma.io/docs/orm/reference/sql-query-builder#select) for every form it accepts.

```ts
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()`](#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()`](https://www.prisma.io/docs/orm/reference/sql-query-builder#fnsraw-and-returns) for the full treatment.

### `fns.raw` as a `where()` predicate [#fnsraw-as-a-where-predicate]

`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(...)`.

```ts
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
```

### Interpolating a typed expression [#interpolating-a-typed-expression]

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.

```ts
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'
```

### Binding a bare value with `param()` [#binding-a-bare-value-with-param]

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:

| The value you interpolate                           | The type id it gets                               |
| --------------------------------------------------- | ------------------------------------------------- |
| a whole `number` from `-2147483648` to `2147483647` | `pg/int4@1`                                       |
| a larger whole `number`, up to `9007199254740991`   | `pg/int8number@1`, which reads back as a `number` |
| any other `number`, including a fractional one      | `pg/float8@1`                                     |
| a `bigint`                                          | `pg/int8@1`, which reads back as a `bigint`       |
| a `string`                                          | `pg/text@1`                                       |
| a `boolean`                                         | `pg/bool@1`                                       |
| a `Uint8Array`                                      | `pg/bytea@1`                                      |

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'`.

```ts
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
```

### Declaring a nullable result [#declaring-a-nullable-result]

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`.

```ts
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
```

### The client-level `db.raw.sql` tag [#the-client-level-dbrawsql-tag]

`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](#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()`](https://www.prisma.io/docs/orm/reference/sql-query-builder#select), which names each output column:

```ts
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.

```ts
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()`](https://www.prisma.io/docs/orm/reference/transactions-and-runtime#dbtransactioncallback) gives you a `tx`, and `tx.query(...)` and `tx.execute(...)` take the same built queries as `runtime.query(...)` and `runtime.execute(...)` do.

```ts
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.

```ts
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();
```

### Unsupported interpolation [#unsupported-interpolation]

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 })`](#binding-a-bare-value-with-param) with the right type id. For a `Date`, convert it to a `Temporal.Instant` first and bind that.

## MongoDB raw commands [#mongodb-raw-commands]

`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)](https://www.prisma.io/docs/orm/reference/transactions-and-runtime#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](https://www.prisma.io/docs/orm/reference/pipeline-builder#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:

```ts
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';
```

### `aggregate<Row>()` [#aggregaterow]

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.

```ts
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
```

### `insertOne()` and `insertMany()` [#insertone-and-insertmany]

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.

```ts
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>] }
```

### `updateOne()` and `updateMany()` [#updateone-and-updatemany]

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 }`.

#### Update document form [#update-document-form]

```ts
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, ... }
```

#### Pipeline form [#pipeline-form]

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

```ts
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, ... }
```

### `deleteOne()` and `deleteMany()` [#deleteone-and-deletemany]

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

```ts
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 }
```

### `findOneAndUpdate()` [#findoneandupdate]

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.

#### Remarks [#remarks]

* `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.

```ts
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));
```

##### Upsert counter pattern [#upsert-counter-pattern]

`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.

```ts
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 }]
```

### `findOneAndDelete()` [#findoneanddelete]

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`.

```ts
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()` [#dbqueryrawcommand]

`db.query.rawCommand(command)` runs a raw MongoDB command, sent exactly as you wrote it. See the [pipeline builder](https://www.prisma.io/docs/orm/reference/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.

```ts
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. 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()`](https://www.prisma.io/docs/orm/reference/pipeline-builder#rawcommand) section.

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

## Related pages

- [`Error reference`](https://www.prisma.io/docs/orm/reference/error-reference): The structured error codes of the Prisma ORM commands, runtime, and extensions, by namespace, with the condition that raises each one.
- [`Migration API`](https://www.prisma.io/docs/orm/reference/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`](https://www.prisma.io/docs/orm/reference/orm-client): Reference for the Prisma ORM client's query, mutation, filter, and aggregate methods.
- [`Pipeline builder reference`](https://www.prisma.io/docs/orm/reference/pipeline-builder): Reference for the Prisma ORM MongoDB pipeline builder's stages, accumulators, expression helpers, and write methods.
- [`SQL query builder reference`](https://www.prisma.io/docs/orm/reference/sql-query-builder): Reference for the Prisma ORM SQL query builder's select, mutation, and grouped query methods.