ORM client reference

The ORM client gives you model-level methods for reading and writing data across PostgreSQL and MongoDB. This page documents every method, its availability on each database, and the behavior that differs between the two. Each method's Remarks say which databases it works on, how it behaves differently on one of them, and where TypeScript rejects something the database itself would accept.

This page assumes your tables already exist. Coming from Prisma ORM 7 has the packages to install and the prisma orm init command. It also covers prisma db update, which replaces prisma migrate dev, and prisma contract emit, which replaces prisma generate. How migrations work shows how the tables get created. For task-oriented walkthroughs, see the Fundamentals guides: Reading data, Writing data, Relations and joins, and Transactions.

All examples on this page run against the schema below, except the grouped-aggregate ones, which use a separate Customer / Order schema shown under Grouped aggregates. contract.prisma replaces schema.prisma, and this page calls it the contract. Running npx prisma contract emit compiles it into two generated files: contract.json, read at run time, and contract.d.ts, read by TypeScript. You import both, as the examples below do. Run npx prisma contract emit again after every change to contract.prisma. A new project keeps all three files in src/prisma/.

types {

  Embedding1536 = pgvector.Vector(1536)

}

type Address {

  street  String

  city    String

  zip     String?

  country String

}

enum user_type {

  @@type("pg/text@1")

  admin

  user

}

enum Priority {

  @@type("pg/text@1")

  Low    = "low"

  High   = "high"

  Urgent = "urgent"

}

model User {

  id          Uuid      @id @default(uuid())

  email       String

  displayName String

  createdAt   DateTime  @default(now())

  kind        user_type

  address     Address?

  posts       Post[]

  tasks       Task[]

  @@map("user")

}

model Post {

  id        Uuid           @id @default(uuid())

  title     String

  userId    Uuid

  priority  Priority       @default(Low)

  createdAt DateTime       @default(now())

  embedding Embedding1536?

  user User  @relation(fields: [userId], references: [id])

  tags Tag[]

  @@map("post")

}

model Tag {

  id    Uuid   @id @default(uuid())

  label String @unique

  posts Post[]

  @@map("tag")

}

model PostTag {

  postId Uuid

  tagId  Uuid

  post Post @relation(fields: [postId], references: [id])

  tag  Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])

  @@map("post_tag")

}

model Task {

  id          Uuid     @id @default(uuid())

  title       String

  description String?

  status      String   @default("open")

  type        String

  userId      Uuid

  createdAt   DateTime @default(now())

  user User @relation(fields: [userId], references: [id])

  @@discriminator(type)

  @@map("task")

}

model Bug {

  severity     String

  stepsToRepro String?

  @@base(Task, "bug")

  @@map("bug")

}

model Feature {

  priority      String

  targetRelease String?

  @@base(Task, "feature")

  @@map("feature")

}

enum UserRole {
  @@type("mongo/string@1")
  Admin  = "admin"
  Author = "author"
  Reader = "reader"
}

type Address {
  street  String
  city    String
  zip     String?
  country String
}

model User {
  id      ObjectId @id @map("_id")
  name    String
  email   String
  bio     String?
  role    UserRole
  address Address?
  posts   Post[]
  @@map("users")
}

model Post {
  id        ObjectId @id @map("_id")
  title     String
  content   String
  kind      String
  authorId  ObjectId
  createdAt Date
  author    User @relation(fields: [authorId], references: [id])
  @@discriminator(kind)
  @@index([authorId])
  @@index([createdAt(sort: Desc), authorId])
  @@map("posts")
}

model Article {
  summary   String
  @@base(Post, "article")
  @@unique([summary])
}

model Tutorial {
  difficulty String
  duration   Int32
  @@base(Post, "tutorial")
}

Some parts of the schema the examples rely on:

• The types block at the top gives a name to a type from an extension package, so your models can use that name. Embedding1536 comes from the pgvector extension, and pgvector. is that package's prefix. See PostgreSQL for the extra line it needs when you create the client.

• @@map("user") sets the table or collection name. Without it, the table name is the model name exactly as written, User.

• On MongoDB, id ObjectId @id @map("_id") renames the field to _id everywhere: you filter on _id, and the returned document has an _id key. That is why the MongoDB examples never say id.

• Uuid is a built-in type for a PostgreSQL uuid column. You do not import it. @default(uuid()) fills the id in when you insert a row.

• @@type("pg/text@1") on an enum block stores the enum as a text column. The @1 is the version of that storage format, and only version 1 exists today, so write it exactly like that. The MongoDB form of the same attribute is @@type("mongo/string@1"). This page calls an enum written this way a text-backed enum.

• A native_enum block stores the members as a PostgreSQL enum type, created with CREATE TYPE. Prisma ORM 7 stored every enum this way, so a database that Prisma ORM 7 created already has these types, and you keep them by declaring each one with native_enum, as Coming from Prisma ORM 7 shows. A plain enum block in Prisma ORM 8 is stored as text. Give every native_enum member a value, because a member with no value is rejected. A field that uses it is typed pg.enum(Priority). This page calls an enum written this way a native enum. The two sort differently; see orderBy().

  native_enum Priority {
  
    Low    = "low"
  
    High   = "high"
  
    Urgent = "urgent"
  
  }
  
  model Post {
  
    id       Uuid              @id @default(uuid())
  
    priority pg.enum(Priority)
  
  }

• An enum member's stored value is the string after =, or the member name when there is no =. That string is what you pass to .eq(). So Urgent = "urgent" is matched by .eq('urgent'), and a bare admin member is matched by .eq('admin').

• A variant is a model that reuses another model's fields and rows. @@base(Task, "bug") makes Bug a variant of Task. Its second argument, "bug", is the value stored in the discriminator column for a Bug row. @@discriminator(type) names that column. db.orm.public.Task.variant('Bug') returns only the Bug rows. Pass the model name, 'Bug', not the string in @@base.

• PostTag is required. To join Post.tags Tag[] and Tag.posts Post[], write a third model with one foreign key to each side and an @@id of exactly those two columns. Prisma ORM finds it for you, and neither Post nor Tag names it. A pair of list fields with no such model is rejected. See Relations and joins.

• On PostgreSQL, a DateTime field comes back as a Temporal.Instant, the standard JavaScript object for a point in time. A filter on that field takes the same type, so you can pass a value straight back in.

Create a client with postgres(...) or mongo(...), then read and write your models through db.orm. The same db also has a query builder, db.sql on PostgreSQL and db.query on MongoDB, plus db.raw for raw queries. This page covers none of those three: see Advanced queries. Create db once per process, and close it with await db.close() when the process shuts down. The two databases have different entry points, and they name the models differently.

Create a PostgreSQL client with postgres(...). db.orm holds your models by model name, grouped by database schema: db.orm.public.User, db.orm.public.Post. public is the PostgreSQL schema your tables are in unless you put them in another one. Import types from ./contract.d, which resolves to contract.d.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 users = await db.orm.public.User.all();

If your contract uses a type from an extension package, pass that package to extensions when you create the client. The example schema's Embedding1536 type comes from pgvector, so it needs this:

import pgvector from '@prisma/orm-extension-pgvector/runtime';

const db = postgres<Contract>({

  contractJson,

  url: process.env.DATABASE_URL,

  extensions: [pgvector],

});

To give a model your own methods on top of the built-in ones, import Collection from @prisma/orm-postgres/orm-client, subclass it, and register the subclass with orm(...) from that same package. You still create the client with postgres(...). orm(...) takes two things from it: the runtime argument, which is what await client.connect() gives you, and the context argument, which is client.context. The code is under Custom Collection subclass.

Create a MongoDB client with mongo(...). db.orm holds your models by collection name, with no schema in between. The collection name is the one you set with @@map, or the model name exactly as written when you did not set one. The example schema maps User to users, so it is db.orm.users, and Post is db.orm.posts. dbName is the MongoDB database name.

import mongo from '@prisma/orm-mongo/runtime';

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 users = await db.orm.users.all();

These methods narrow a query, and each returns a collection. On this page, a collection is the query object you chain more methods on, such as db.orm.public.User or db.orm.public.User.where(...). When this page says "a MongoDB collection", it means that query object. MongoDB itself calls its store of documents a collection too, and the name is the same one. You run the query with a read method such as all() or first(). MongoDB supports fewer of these methods than PostgreSQL, and each method's Remarks say which databases it works on.

Some examples import a filter helper you can use on its own. On PostgreSQL those come from @prisma/orm-postgres/orm-client, on MongoDB from @prisma/orm-mongo/query-ast/execution. See Filter conditions and operators.

The examples below use these ids, which stand for rows inserted before the query runs:

const aliceId = '00000000-0000-4000-8000-000000000001';

const carolId = '00000000-0000-4000-8000-000000000003';

const postId = '00000000-0000-4000-8000-000000000010';

const acmeId = '00000000-0000-4000-8000-000000000020';

Restrict a query to rows matching a filter.

• Available for PostgreSQL and MongoDB, but the accepted filter shapes differ.
• On PostgreSQL, where() takes either a callback that calls an operator on a column (u.email.eq(...)) or an object of field-and-value pairs that must match exactly.
• On MongoDB, where() takes either an object of field-and-value pairs that must match exactly or a MongoFieldFilter. The object form can only test for equality. For anything else, such as greater-than, use MongoFieldFilter (see MongoFieldFilter).
• Calling where() more than once on the same query requires a row to match all of the filters.
• .eq() is one operator of many. Filter conditions and operators, further down this page, lists them all for both databases.

const admins = await db.orm.public.User.where((u) => u.kind.eq('admin')).all();

const bob = await db.orm.public.User.where({ email: 'bob@example.com' }).first();

const authors = await db.orm.users.where({ role: 'author' }).all();

const carolUrgentPosts = await db.orm.public.Post.where({ userId: carolId })
  .where((p) => p.priority.eq('urgent'))
  .all();

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

const alice = await db.orm.users.where(MongoFieldFilter.eq('email', 'alice@example.com')).first();

const recentPosts = await db.orm.posts
  .where(MongoFieldFilter.gte('createdAt', new Date('2024-01-02T00:00:00.000Z')))
  .all();

const carolUrgentPosts = await db.orm.public.Post.where({ userId: carolId })

  .where((p) => p.priority.eq('urgent'))

  .all();

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

const alice = await db.orm.users.where(MongoFieldFilter.eq('email', 'alice@example.com')).first();

const recentPosts = await db.orm.posts

  .where(MongoFieldFilter.gte('createdAt', new Date('2024-01-02T00:00:00.000Z')))

  .all();

For a task-oriented guide to filtering, see Reading data.

Return only the scalar fields you name.

• Available for PostgreSQL and MongoDB.
• On PostgreSQL, select() narrows the returned row shape at the type level: fields you didn't select are absent from the result type.
• On MongoDB, select() changes what the database returns but not the TypeScript type. Only the fields you named come back. The type still lists every field on the model. Read a field you did not name and you get undefined, because the returned document does not carry it.

const summaries = await db.orm.public.User.select('id', 'email').orderBy((u) => u.email.asc()).all();

// summaries[0] is { id, email }, with no displayName

const summaries = await db.orm.users.select('name', 'email').all();

Eagerly load a relation onto the returned rows.

• Available for PostgreSQL and MongoDB, with differences noted below.
• On PostgreSQL, include(relationName, refineFn?) loads both to-one and to-many relations. The optional callback receives the related rows as a collection, so you can filter, order, limit, and aggregate them. See Refinements, aggregates, and combine.
• On MongoDB, wrap the _id of a related document loaded by include() in String(...) before you compare it. It comes back as an ObjectId object, while a document's own _id comes back as a hex string.
• On MongoDB, include() loads a relation that is stored as a reference. It takes the relation name and nothing else, and TypeScript rejects a second argument.

const posts = await db.orm.public.Post.include('user').where({ id: postId }).all();

// posts[0].user is the related User

const users = await db.orm.public.User.include('posts').where({ id: aliceId }).all();

// users[0].posts is an array of the user's posts

const posts = await db.orm.posts.include('author').where({ title: 'Hello world' }).all();

// posts[0].author._id is an ObjectId; compare it as String(posts[0].author._id)

For a task-oriented guide to loading related records, see Relations and joins.

On PostgreSQL, the callback you pass to include() receives the related rows as a collection. This page calls that callback a refinement, which is the word in the heading above. You can filter, order, and paginate the related rows in it, and you can also reduce them to a single value, or use combine() to return several results at once. To count or sum a whole model instead of a relation, see Grouped aggregates, where aggregate((agg) => ({ n: agg.count() })) counts every row.

• PostgreSQL only. On MongoDB, include() takes no callback, and count, sum, avg, min, max, and combine do not exist on a collection.
• On PostgreSQL, those six are only callable inside an include() callback. Called anywhere else, they throw an error whose code is ORM.INCLUDE_INVALID.
• sum() and avg() take a numeric column: an integer, a floating-point number, or a decimal. They also take an interval or a Time column, the time-of-day type. They do not take a date or a timestamp, and TypeScript rejects a date or timestamp field name here.
• min() and max() take more: numeric and text columns, dates, times, timestamps, intervals, IP addresses, and text arrays. They do not take a boolean, a uuid, binary data, a bit string, or JSON.
• count() returns a number. With no argument it counts rows; with a field name it counts rows where that field is not null. countBigInt() returns a bigint instead, for a count too large for a JavaScript number.
• sum() over an integer column returns a number. A total too large for a number throws an error whose code is RUNTIME.DECODE_FAILED. Use sumBigInt(field) for totals that large. It returns a bigint.
• avg() over an integer column returns a JavaScript floating-point number. avgDecimal(field) returns the exact average as a decimal string. Pass that string to a decimal library, or call Number(...) on it when a floating-point value is fine.
• When a to-many relation has no rows, sum(), avg(), min(), and max() come back as null, not 0.
• combine(shape) takes an object. You choose the keys. Call posts.combine({ ... }), and inside the object write posts again for each value. The posts inside the object is the same collection the callback received, with nothing chained on it yet. A value that chains more methods on posts comes back as an array of rows. A value that is an aggregate, such as posts.count(), comes back as that single value.

const users = await db.orm.public.User.include('posts', (posts) =>

  posts

    .where((p) => p.priority.eq('low'))

    .orderBy((p) => p.createdAt.desc())

    .limit(1),

)

  .where({ id: aliceId })

  .all();

const users = await db.orm.public.User.include('posts', (posts) => posts.count())

  .where({ id: aliceId })

  .all();

// users[0].posts is the number 2

This example uses the Customer and Order models, which are in the separate schema shown under Grouped aggregates.

const customers = await db.orm.public.Customer.include('orders', (orders) => orders.sum('amount'))

  .where({ id: acmeId })

  .all();

// customers[0].orders is 1500

const avgCustomers = await db.orm.public.Customer.include('orders', (orders) => orders.avg('amount'))

  .where({ id: acmeId })

  .all();

// avgCustomers[0].orders is 300

const users = await db.orm.public.User.include('posts', (posts) =>

  posts.combine({

    recent: posts.orderBy((p) => p.createdAt.desc()).limit(1),

    total: posts.count(),

  }),

)

  .where({ id: aliceId })

  .all();

// users[0].posts.total is 2; users[0].posts.recent is a one-element array

Sort the result set.

• Available for PostgreSQL and MongoDB, with different argument shapes.
• On PostgreSQL, orderBy() takes a callback that returns .asc() or .desc() on one column. Pass an array of such callbacks to sort by more than one column.
• On PostgreSQL, the callback can also sort by a field of a related record, for a relation to one record and one relation deep, as in (p) => p.user.displayName.asc().
• On PostgreSQL, the callback can also sort by the number of related records, for a relation to many records, as in (u) => u.posts.count().desc(). count() takes an optional filter, the same one some() takes.
• On PostgreSQL, .asc() and .desc() take { nulls: "first" } or { nulls: "last" }. Without it, PostgreSQL puts nulls last for .asc() and first for .desc().
• On MongoDB, orderBy() takes an object instead: { field: 1 } sorts ascending and { field: -1 } sorts descending.
• The example schema declares Priority text-backed, so on PostgreSQL it sorts by its stored text: 'high', then 'low', then 'urgent'. A native enum column sorts in the order its members are declared instead, so the same members in a native_enum Priority block sort Low, High, Urgent. Use a native enum when the sort order matters.

const newestFirst = await db.orm.public.Post.where({ userId: aliceId })

  .orderBy((p) => p.createdAt.desc())

  .all();

const newestFirst = await db.orm.posts.orderBy({ createdAt: -1 }).all();

const byPriorityThenDate = await db.orm.public.Post.orderBy([
  (p) => p.priority.asc(),
  (p) => p.createdAt.asc(),
]).all();
// Priority is text-backed here, so it sorts as 'high', 'low', 'urgent'

const byPriorityThenDate = await db.orm.public.Post.orderBy([

  (p) => p.priority.asc(),

  (p) => p.createdAt.asc(),

]).all();

// Priority is text-backed here, so it sorts as 'high', 'low', 'urgent'

Limit the number of returned rows.

• Available for PostgreSQL and MongoDB.

const firstTwo = await db.orm.public.Post.orderBy((p) => p.createdAt.asc()).limit(2).all();

const firstOne = await db.orm.posts.orderBy({ createdAt: 1 }).limit(1).all();

Offset into the ordered result set.

• Available for PostgreSQL and MongoDB.
• Combine with orderBy() and limit() for pagination.

const page2 = await db.orm.public.Post.orderBy((p) => p.createdAt.asc()).offset(2).limit(2).all();

const secondPost = await db.orm.posts.orderBy({ createdAt: 1 }).offset(1).limit(1).all();

const byAuthor = await db.orm.public.Post.orderBy([
  (p) => p.user.displayName.asc(),
  (p) => p.id.asc(),
]).all();

const mostPosts = await db.orm.public.User.orderBy((u) => u.posts.count().desc()).all();

const described = await db.orm.public.Task.orderBy((t) => t.description.desc({ nulls: "last" })).all();

const byAuthor = await db.orm.public.Post.orderBy([

  (p) => p.user.displayName.asc(),

  (p) => p.id.asc(),

]).all();

const mostPosts = await db.orm.public.User.orderBy((u) => u.posts.count().desc()).all();

const described = await db.orm.public.Task.orderBy((t) => t.description.desc({ nulls: "last" })).all();

Resume pagination from a known position.

• PostgreSQL only. cursor() does not exist on a MongoDB collection.
• Every sort in the orderBy() must be a plain column of the model. A sort by a related record, a count, an extension operation such as a vector distance, or a sort with nulls makes cursor() throw an error whose code is ORM.ARGUMENT_INVALID. Page such a query with limit() and offset().
• Always call orderBy() before cursor(). TypeScript rejects the call if you do not. Nothing checks this while the query runs, so without the orderBy() the cursor is ignored and every row comes back.
• The cursor object must name every column the orderBy() sorts on. Leave one out and running the query throws an error whose code is ORM.CURSOR_VALUE_MISSING. With two sort columns, give both keys. The cursor compares the sort columns in order: first by the first column, then by the second for rows that tie on the first.

const page1 = await db.orm.public.Post.orderBy((p) => p.createdAt.asc()).limit(2).all();

const last = page1[page1.length - 1];

const page2 = await db.orm.public.Post.orderBy((p) => p.createdAt.asc())

  .cursor({ createdAt: last.createdAt })

  .limit(2)

  .all();

Remove duplicate rows, comparing only the fields you name.

• PostgreSQL only. distinct() does not exist on a MongoDB collection.
• You do not need select(). distinct('priority') keeps one whole row per distinct priority value, whatever the query returns.
• Which of the tied rows it keeps is not defined. Call orderBy() first to choose, as distinctOn() does.

const priorities = await db.orm.public.Post.distinct('priority').all();

// priorities holds one whole Post row per distinct priority value

Keep the first row per key according to orderBy().

• PostgreSQL only. distinctOn() does not exist on a MongoDB collection.
• Call orderBy() first, so that "the first row per key" means something. TypeScript rejects the call if you do not.
• Start the orderBy() with the same columns you pass to distinctOn(), as the example does. Sort by anything else first and the query fails in the database. If one of those first sorts is not a plain column, the call throws an error whose code is ORM.ARGUMENT_INVALID; a sort by a related record or a count may follow them.

const latestPerUser = await db.orm.public.Post.orderBy([(p) => p.userId.asc(), (p) => p.createdAt.desc()])

  .distinctOn('userId')

  .all();

// latestPerUser holds one post per userId: the newest one, because of the orderBy

Narrow a model to one of the variants declared with @@base.

• Available for PostgreSQL and MongoDB.
• Pass the variant's model name, such as 'Bug', not the string in its @@base attribute. On MongoDB you pass the model name too, even though the accessor before it is the collection name.
• On PostgreSQL, a variant that sets its own @@map, as Bug and Feature do, is stored in its own table. Read createAndCount() and upsert() before you write to such a variant.
• On MongoDB, every variant is a document in the one collection, told apart by the field named in @@discriminator.

const bugs = await db.orm.public.Task.variant('Bug').all();

const tutorials = await db.orm.posts.variant('Tutorial').all();

A read method runs the query and gives you the rows. all() and first() are available on both databases, while aggregate() and groupBy() are PostgreSQL only and are documented under Grouped aggregates.

There is no count() method on either database, so on PostgreSQL, count with aggregate((a) => ({ n: a.count() })). On MongoDB, counting takes two steps: db.query, the pipeline builder, builds the pipeline, and runtime.query(...) runs it.

const built = db.query.from('posts').count('total').build();

const runtime = await db.runtime();

const [counted] = await runtime.query(built); // counted.total is the count

For a task-oriented walkthrough, see Reading data.

Resolve the query to every matching row.

• Available for PostgreSQL and MongoDB.
• all() returns an AsyncIterableResult: you can await it to collect an array, or use for await to stream rows one at a time.
• awaiting a result you have already awaited is safe. You get the same array back, and the query does not run again. Each result can be used one way only, so awaiting it and then looping it with for await, or the reverse, throws an error whose code is RUNTIME.ITERATOR_CONSUMED. See Single consumption and mode switching.
• orderBy() is written differently on each database. See orderBy().

all() takes no required arguments.

Row is the model's row type, from the emitted contract.d.ts.

const users = await db.orm.public.User.all();

const users = await db.orm.users.all();

for await (const user of db.orm.public.User.orderBy((u) => u.email.asc()).all()) {

  console.log(user.email);

}

for await (const post of db.orm.posts.orderBy({ createdAt: 1 }).all()) { // 1 ascending, -1 descending
  console.log(post.title);
}

For Prisma ORM 7 users, findMany maps onto all():

- const users = await prisma.user.findMany({ where: { kind: 'admin' } });

+ const users = await db.orm.public.User.where({ kind: 'admin' }).all();

Resolve the query to the first matching row, or null if none matches.

• Available for PostgreSQL and MongoDB.
• On PostgreSQL, first() accepts an inline filter: a shorthand object or a callback (first((p) => p.priority.eq('urgent'))). An inline filter is added to any earlier where() call, and both have to match.
• On MongoDB, first() takes no filter argument. Filter with where(...) first, then call first().

const alice = await db.orm.public.User.first({ email: 'alice@example.com' });

const urgentPost = await db.orm.public.Post.first((p) => p.priority.eq('urgent'));

const bob = await db.orm.users.where({ name: 'Bob' }).first();

For Prisma ORM 7 users, findUnique and findFirst map onto first():

- const alice = await prisma.user.findUnique({ where: { email } });

+ const alice = await db.orm.public.User.first({ email });

- const alice = await prisma.user.findUniqueOrThrow({ where: { email } });

+ const alice = await db.orm.public.User.where({ email }).all().firstOrThrow();

first() returns null on no match and does not check that only one row matched. findUniqueOrThrow and findFirstOrThrow both map onto all().firstOrThrow(), as the last line above shows.

firstOrThrow() is a method on the result that all() gives you, so calling it is not a second use of that result. It reads every matching row into memory and returns the first one, and it throws an error whose code is RUNTIME.NO_ROWS when nothing matched.

On PostgreSQL you can subclass Collection to add your own methods to a model, and register the subclass when you create the client.

• PostgreSQL only. You cannot subclass a collection on MongoDB.
• db.orm cannot use your subclass. Call the orm(...) function with a collections object to get a second accessor with the same methods as db.orm.public. Use that accessor for the models you subclassed, and keep db.orm.public for every other model. Your existing db.orm.public.X calls keep working.
• client in the example below is what postgres(...) returns, the same object Setting up the client calls db. The example creates one so that it runs on its own.
• Collection<Contract, 'Task'> takes two type arguments: your contract type, and the name of the model this collection is for.
• A method that returns this.variant('Bug') gives back a collection narrowed to the Bug model. It is chainable, so you finish it with all(), first(), or any other method.

// postgres() comes from /runtime; Collection and orm() come from /orm-client.

import postgres from '@prisma/orm-postgres/runtime';

import { Collection, orm } from '@prisma/orm-postgres/orm-client';

import type { Contract } from './contract.d';

import contractJson from './contract.json' with { type: 'json' };

class TaskCollection extends Collection<Contract, 'Task'> {

  bugs() {

    return this.variant('Bug');

  }

  features() {

    return this.variant('Feature');

  }

}

const client = postgres<Contract>({ contractJson, url: process.env.DATABASE_URL });

const runtime = await client.connect();

const tasks = orm({

  runtime,

  context: client.context,

  collections: { Task: TaskCollection },

}).public; // .public is the PostgreSQL schema

const bugs = await tasks.Task.bugs().all();

const features = await tasks.Task.features().all();

A write method changes the database. create, createAll, createAndCount, update, updateAll, updateAndCount, delete, deleteAll, deleteAndCount, and upsert are available on both databases, and the differences between the two are listed under each method.

For Prisma ORM 7 users: createMany is createAndCount(), which returns the count as a number instead of { count }, and createManyAndReturn is createAll(). In the same way, updateMany is updateAndCount() and updateManyAndReturn is updateAll(). deleteMany is deleteAndCount(), and deleteAll() returns the deleted rows, which no Prisma ORM 7 method did. count is aggregate() on PostgreSQL, or the two steps shown under Read methods on MongoDB. skipDuplicates: true is now { onConflict: 'skip' }, which you pass to createAll() or createAndCount() on PostgreSQL as a second argument.

To catch one of the errors named below, wrap the call in try and catch, then compare error.code with the string given here. For a task-oriented walkthrough, see Writing data. On MongoDB, a write method rejects a chain that already has orderBy(), limit(), or offset() on it, and every write except updateAll() and deleteAll() also rejects include(). Both throw an error whose code is ORM.OPERATION_UNSUPPORTED. For grouping several writes into one unit, see Transactions.

The examples below use aliceId, bobId, carolId, postId, tagId, tutorialId, and userId for the ids of rows that already exist. Substitute your own.

Insert a single row and return it.

• Available for PostgreSQL and MongoDB.
• On PostgreSQL, you may leave out any field your contract gives a default, including the id. Passing an explicit id is accepted. Nullable fields can be left out too. Every other field is required.
• On MongoDB, _id is the only field you can leave out. Give every other field a value, and write null for a field you want empty.
• On MongoDB, create() returns the values you passed plus the _id the server assigned, not the stored document. Read the row back if you need a value the database filled in. On PostgreSQL the returned row is read from the database already.
• On PostgreSQL, create() can create or link related rows in the same transaction. Nest create() on the side that does not hold the foreign key, and connect() on the side that does.
• Nested create() and connect() are not available on MongoDB.

On PostgreSQL, that object can also carry a create or connect callback for a related row, as shown below. create() and connect() each take one object or an array of objects, on any relation. disconnect() takes an array, and on a many-to-many relation the array is required.

const tag = await db.orm.public.Tag.create({ label: 'typescript-2' });

const user = await db.orm.users.create({
  name: 'Carol',
  email: 'carol@example.com',
  bio: null,
  role: 'reader',
  address: null,
});
// user._id is the server-assigned id

const author = await db.orm.public.User.create({
  email: 'dana@example.com',
  displayName: 'Dana',
  kind: 'user',
  posts: (posts) => posts.create([{ title: 'Dana post one' }]),
});

const post = await db.orm.public.Post.create({
  title: 'Connected to Bob',
  user: (user) => user.connect({ id: bobId }),
});

- const tag = await prisma.tag.create({ data: { label: 'typescript-2' } });
+ const tag = await db.orm.public.Tag.create({ label: 'typescript-2' });

const author = await db.orm.public.User.create({

  email: 'dana@example.com',

  displayName: 'Dana',

  kind: 'user',

  posts: (posts) => posts.create([{ title: 'Dana post one' }]),

});

const post = await db.orm.public.Post.create({

  title: 'Connected to Bob',

  user: (user) => user.connect({ id: bobId }),

});

For Prisma ORM 7 users, create() drops the data wrapper:

- const tag = await prisma.tag.create({ data: { label: 'typescript-2' } });

+ const tag = await db.orm.public.Tag.create({ label: 'typescript-2' });

Insert multiple rows and return them.

• Available for PostgreSQL and MongoDB.
• Returns an AsyncIterableResult: await for an array, or for await to stream inserted rows.
• On PostgreSQL every row is inserted by one statement before the first row reaches you. Stopping the loop early does not undo an insert.
• To skip each row that would break a unique constraint, instead of failing the whole insert, pass { onConflict: 'skip' } as the second argument. The result then holds only the rows the database wrote. This replaces skipDuplicates: true from Prisma ORM 7.
• onConflict works on PostgreSQL, and on SQLite through the experimental @prisma/orm-sqlite package. On MongoDB, createAll() takes only the rows, so a second argument is a type error.
• By default, a row is skipped when it breaks any unique constraint. To check only one constraint, list its fields in conflictOn. In the example schema, Tag.label is unique, so conflictOn: ['label'] skips a tag whose label already exists. A row that breaks a different unique constraint is then not skipped: the database rejects it, and the whole insert fails, so no row is written.
• onConflict does not work on a variant stored in its own table, such as Bug, which maps to bug while its base model Task maps to task. Passing it there throws an error whose code is ORM.OPERATION_UNSUPPORTED. For such a variant, call createAll() with only the rows. A row that breaks a unique constraint then makes the call fail instead of being skipped.
• If your contract was emitted before 8.0.0-rc.12, run npx prisma contract emit again before you use onConflict. Otherwise the call throws an error whose code is ORM.CAPABILITY_MISSING.

conflictOn is an array of field names, such as ['label']. upsert() also takes a conflictOn, but there it is an object of field values, such as { label: 'typescript' }.

const created = await db.orm.public.Tag.createAll([{ label: 'alpha' }, { label: 'beta' }]);

// Skip rows whose label already exists instead of failing.

const added = await db.orm.public.Tag.createAll([{ label: 'alpha' }, { label: 'gamma' }], {

  onConflict: 'skip',

  conflictOn: ['label'],

});

// added holds only the gamma row

const created = await db.orm.users.createAll([
  { name: 'Dana', email: 'dana@example.com', bio: null, role: 'author', address: null },
  { name: 'Eve', email: 'eve@example.com', bio: null, role: 'reader', address: null },
]);

Insert rows and return how many were inserted, without reading them back.

• Available for PostgreSQL and MongoDB.
• On PostgreSQL, createAndCount() does not work on a variant that is stored in its own table, meaning its @@map names a different table than its base model does. Bug in the example schema is one: it maps to bug while Task maps to task. Use createAll() instead.
• Calling it on such a variant throws an error whose code is ORM.OPERATION_UNSUPPORTED, with the message createAndCount() is not supported for MTI variant "Bug" on model "Task". Use createAll() instead. MTI in the message means multi-table inheritance, the variant stored in its own table described above.
• On MongoDB, a variant is one document in one collection, so createAndCount() works on a variant as usual.
• It returns the number of rows the database inserted. With { onConflict: 'skip' }, the skipped rows are not counted. The option works as on createAll().

conflictOn is an array of field names, as on createAll().

const inserted = await db.orm.public.Tag.createAndCount([{ label: 'epsilon' }, { label: 'zeta' }]);

// 2

const inserted = await db.orm.posts.variant('Tutorial').createAndCount([
  {
    title: 'Variant createAndCount',
    content: 'body',
    authorId: aliceId,
    createdAt: new Date('2024-02-01T00:00:00.000Z'),
    difficulty: 'beginner',
    duration: 10,
  },
]);
// 1

Update the matched row and return it, or null if none matches.

• Available for PostgreSQL and MongoDB. Requires a prior where() (see the warning at the top of this section).

• On MongoDB, update() takes an object of field values, or a callback that returns field operations, such as (p) => [p.content.set('Rewritten')]. See Field update operations.

• On MongoDB, the update input covers the base model's fields only. Putting a field declared on a variant in the update, such as duration on Tutorial, makes TypeScript complain even though the update runs correctly. Put // @ts-expect-error on the line directly above the line TypeScript flags. When the complaint goes away, TypeScript reports the comment itself as unused, so delete it then. The same is true of updateAll() and updateAndCount().

  // @ts-expect-error duration is a Tutorial field, not a Post field
  
  .update((t) => [t.duration.inc(10)]);

• Take care on PostgreSQL: update() takes an object of field values only, and passing a function instead writes nothing, reports nothing, and returns null.

• On PostgreSQL, update() can also relink related rows. connect() points the foreign key at a different row, and disconnect() removes a many-to-many link without deleting either row.

• Returns null when no row matches. On PostgreSQL it also returns null when the object you pass is empty and has no keys at all. An object whose values already match the row is different: that update runs and returns the row.

• On PostgreSQL, select() and include() before .update() choose which fields and relations come back on the returned row. They change nothing about what is written.

On PostgreSQL that object can also carry connect or disconnect for a related row, as shown below.

const updated = await db.orm.public.User.where({ id: bobId }).update({ displayName: 'Bob Updated' });

const updated = await db.orm.users.where({ _id: bobId }).update({ bio: 'Now with a bio' });

const updated = await db.orm.posts
  .where({ _id: postId })
  .update((p) => [p.title.set('Updated title'), p.content.set('Rewritten')]);

const relinked = await db.orm.public.Post.where({ id: postId }).update({
  user: (user) => user.connect({ id: carolId }),
});

const updated = await db.orm.public.Post.where({ id: postId })
  .select('id', 'title')
  .include('tags', (tag) => tag.select('id', 'label').orderBy((t) => t.label.asc()))
  .update({
    tags: (tag) => tag.disconnect([{ id: tagId }]),
  });
// only the join-table row is removed. The Tag row itself still exists

- const updated = await prisma.user.update({ where: { id: bobId }, data: { displayName: 'Bob Updated' } });
+ const updated = await db.orm.public.User.where({ id: bobId }).update({ displayName: 'Bob Updated' });

const updated = await db.orm.posts

  .where({ _id: postId })

  .update((p) => [p.title.set('Updated title'), p.content.set('Rewritten')]);

const relinked = await db.orm.public.Post.where({ id: postId }).update({

  user: (user) => user.connect({ id: carolId }),

});

const updated = await db.orm.public.Post.where({ id: postId })

  .select('id', 'title')

  .include('tags', (tag) => tag.select('id', 'label').orderBy((t) => t.label.asc()))

  .update({

    tags: (tag) => tag.disconnect([{ id: tagId }]),

  });

// only the join-table row is removed. The Tag row itself still exists

For Prisma ORM 7 users, update() moves the filter into where() and drops the data wrapper:

- const updated = await prisma.user.update({ where: { id: bobId }, data: { displayName: 'Bob Updated' } });

+ const updated = await db.orm.public.User.where({ id: bobId }).update({ displayName: 'Bob Updated' });

Update every matching row and collect the results.

• Available for PostgreSQL and MongoDB. Call where() first. Without a filter this changes every row. See the warning at the top of this section.
• On PostgreSQL, updateAll() runs as one statement, so every matching row changes together.
• On MongoDB, updateAll() is not a single operation, and two things follow. If a document begins matching your filter while the call is running, it is updated but not included in the returned rows. If someone else changes a matched document while the call is running, you get their version back, not yours.
• Prisma ORM has no db.transaction() on MongoDB, so you cannot avoid this from the client. To control it, share one MongoClient with the MongoDB driver and group the writes in one of the driver's own sessions, shown under Transactions on MongoDB.

const updated = await db.orm.public.Post.where({ userId: aliceId }).updateAll({ priority: 'urgent' });

const updated = await db.orm.users.where({ role: 'author' }).updateAll({ role: 'admin' });
// see the remark above: this is not one operation

Update every matching row and return the count.

• Available for PostgreSQL and MongoDB. Call where() first. Without a filter this changes every row. See the warning at the top of this section.
• On MongoDB the count leaves out documents that already held the new values. On PostgreSQL it counts every matched row.

const count = await db.orm.public.Post.where({ userId: carolId }).updateAndCount({ priority: 'low' });

const count = await db.orm.users.where({ role: 'author' }).updateAndCount({ role: 'admin' });

Remove the matched row and return it, or null if none matches.

• Available for PostgreSQL and MongoDB. Requires a prior where() (see the section warning above). Returns null when no row matches.

delete() takes no required arguments. Filter with where() first.

const created = await db.orm.public.Tag.create({ label: 'throwaway' });

const deleted = await db.orm.public.Tag.where({ id: created.id }).delete();

const deleted = await db.orm.users.where({ _id: userId }).delete();

For Prisma ORM 7 users, delete() moves the filter into where():

- const deleted = await prisma.tag.delete({ where: { id } });

+ const deleted = await db.orm.public.Tag.where({ id }).delete();

Remove every matching row and collect the results.

• Available for PostgreSQL and MongoDB. Call where() first. Without a filter this deletes every row. See the warning at the top of this section.
• Every matching row is deleted before the first row reaches you. Stopping the loop early does not save any of them.

deleteAll() takes no required arguments. Filter with where() first.

const deleted = await db.orm.public.Post.where({ userId: carolId }).deleteAll();

const deleted = await db.orm.users.where({ role: 'reader' }).deleteAll();

Remove every matching row and return the count.

• Available for PostgreSQL and MongoDB. Call where() first. Without a filter this deletes every row. See the warning at the top of this section. Returns 0 when no row matches.

deleteAndCount() takes no required arguments. Filter with where() first.

const count = await db.orm.public.Post.where({ userId: carolId }).deleteAndCount();

const count = await db.orm.users.where({ role: 'reader' }).deleteAndCount();

Insert a row if none matches, otherwise update the existing row.

• On MongoDB, call where() first. The filter decides which document is updated.
• On PostgreSQL, upsert() never reads the filters. A where() before it is ignored, without an error. conflictOn decides which row counts as already existing.
• conflictOn names the unique column that decides insert or update, such as conflictOn: { label: 'typescript' }. Only the column name is used. The value is required by the type and is then ignored, so pass any value of the right type.
• For a unique constraint on several columns together, name every one of those columns, as in conflictOn: { orgId: '', email: '' }. The set you name has to match one @@unique constraint.
• When a row is inserted on PostgreSQL, the create side is used as you wrote it.
• When a row is inserted on MongoDB, a field that appears in both create and update takes the update value.
• On MongoDB, the update side takes an object of field values or a field-operations callback.
• On PostgreSQL, upsert() does not work on a variant stored in its own table, the same restriction as createAndCount(). It throws an error whose code is ORM.OPERATION_UNSUPPORTED, with the message upsert() is not supported for MTI variant "Bug" on model "Task". Use createAll() instead.
• There is no upsert for such a variant. Read the row with first(), then call create() or update().
• On MongoDB, the _id on the returned row is a hex string, not the driver's ObjectId.

// Insert: no existing row has label 'brand-new', so the row is inserted

// exactly as the create side describes it.

const inserted = await db.orm.public.Tag.upsert({

  create: { label: 'brand-new' },

  update: { label: 'brand-new-updated' },

  conflictOn: { label: 'brand-new' }, // only the column name `label` is used

});

// Update: a row with label 'typescript' already exists, so the update runs against it.

const updated = await db.orm.public.Tag.upsert({

  create: { label: 'typescript' },

  update: { label: 'typescript-renamed' },

  conflictOn: { label: 'typescript' },

});

const user = await db.orm.users.where({ email: 'newperson@example.com' }).upsert({

  create: {

    name: 'New Person',

    email: 'newperson@example.com',

    bio: null,

    role: 'reader',

    address: null,

  },

  update: { bio: 'set on upsert' },

});

// on insert, bio is 'set on upsert': the `update` value wins on MongoDB

const post = await db.orm.posts

  .variant('Tutorial')

  .where({ _id: tutorialId })

  .upsert({

    create: {

      title: 'Should not be used',

      content: 'unused',

      authorId: bobId,

      createdAt: new Date('2024-01-01T00:00:00.000Z'),

      difficulty: 'beginner',

      duration: 0,

    },

    update: (t) => [t.content.set('Updated content')],

  });

For Prisma ORM 7 users, upsert() replaces the where argument with conflictOn (PostgreSQL):

- const tag = await prisma.tag.upsert({

-   where: { label: 'typescript' },

-   update: { label: 'typescript-renamed' },

-   create: { label: 'typescript' },

- });

+ const tag = await db.orm.public.Tag.upsert({

+   update: { label: 'typescript-renamed' },

+   create: { label: 'typescript' },

+   conflictOn: { label: 'typescript' },

+ });

These methods exist only on PostgreSQL collections. aggregate() treats every row the query matches as one group, while groupBy().aggregate() aggregates per group and having() filters those groups. A MongoDB model's collection has none of them, so to aggregate on MongoDB, use the pipeline builder, which is Prisma ORM's API for MongoDB's aggregation pipeline and is described in MongoDB: Pipeline builder.

The examples below use a separate Customer / Order schema, where Order.amount is a numeric column:

model Customer {

  id      Uuid   @id @default(uuid())

  name    String

  segment String

  orders Order[]

  @@map("customer")

}

model Order {

  id         Uuid     @id @default(uuid())

  customerId Uuid

  channel    String

  amount     Int

  quantity   Int?

  placedAt   DateTime @default(now())

  customer Customer @relation(fields: [customerId], references: [id])

  @@map("order")

}

Compute aggregates over the rows the query matches. aggregate() runs the query, so you await it directly instead of calling .all().

• PostgreSQL only. Not available on MongoDB.
• count() counts rows and returns a number, 0 when no rows match.
• count(field) counts the rows where that field is not null.
• To count or sum the related rows of each parent row, use the include() callback instead; see Refinements, aggregates, and combine.
• sum(), avg(), min(), and max() return null, not 0, when no rows match. Handle the null.
• A limit() or offset() earlier in the chain is applied first. The aggregate then covers only the rows they kept.
• What sum() and avg() give you back depends on the column type, as the table below shows.

Use sumBigInt() when the total can pass Number.MAX_SAFE_INTEGER, because a sum() whose total passes it throws an error whose code is RUNTIME.DECODE_FAILED, thrown when the query result is read. Use avgDecimal() when you need the exact mean as a string, such as '10.3333333333333333'. Over a Decimal column sum() and avg() are already exact, and sumBigInt() is not available.

The callback's agg argument gives you count(), count(field), sum(field), avg(field), min(field), and max(field), plus countBigInt(), sumBigInt(field), and avgDecimal(field). agg.countBigInt() counts the same rows as agg.count() and returns a bigint.

const stats = await db.orm.public.Order.aggregate((agg) => ({

  total: agg.count(),

  withQuantity: agg.count('quantity'), // rows where quantity is not null

}));

// { total: 10, withQuantity: 8 }

const stats = await db.orm.public.Order.where({

  customerId: '20000000-0000-0000-0000-000000000001',

}).aggregate((agg) => ({

  totalAmount: agg.sum('amount'),

  avgAmount: agg.avg('amount'),

}));

// { totalAmount: 900, avgAmount: 300 }

const stats = await db.orm.public.Order.aggregate((agg) => ({

  cheapest: agg.min('amount'),

  priciest: agg.max('amount'),

}));

// { cheapest: 10, priciest: 500 }

const exact = await db.orm.public.Order.aggregate((agg) => ({

  rows: agg.countBigInt(),

  total: agg.sumBigInt('amount'),

  average: agg.avgDecimal('amount'),

}));

// { rows: 10n, total: 1500n, average: '150.0000000000000000' }

const stats = await db.orm.public.Order.where((o) => o.amount.gt(999_999)).aggregate((agg) => ({

  total: agg.sum('amount'),

  average: agg.avg('amount'),

  count: agg.count(),

}));

// { total: null, average: null, count: 0 }

For Prisma ORM 7 users, aggregate() takes a selector callback instead of _sum/_avg keys:

- const stats = await prisma.order.aggregate({ _sum: { amount: true }, _avg: { amount: true } });

+ const stats = await db.orm.public.Order.aggregate((agg) => ({ total: agg.sum('amount'), average: agg.avg('amount') }));

Group rows by one or more fields, then aggregate per group.

• PostgreSQL only. Not available on MongoDB.
• Pass field names as separate arguments: groupBy('customerId', 'channel').
• groupBy(...) returns a GroupedCollection. You cannot await it. Call .aggregate(...) on it, which runs the query and gives you one row per group. Each row holds the grouping fields, typed as they are on your model, plus the keys you returned from the aggregate callback.
• Chain order: where(...) comes before groupBy(...) and filters the rows that get grouped. After groupBy(...) come having(...) and orderBy(...), then limit(...) and offset(...). aggregate(...) comes last.
• orderBy(...) on a grouped collection sorts the groups by a field you grouped on. You cannot sort groups by an aggregate value. To do that, write the query with the SQL query builder instead. See Advanced queries.
• limit(n) and offset(n) take a slice of the groups, and both require an orderBy(...) before them. Without one they are a TypeScript error.

const perCustomer = await db.orm.public.Order.groupBy('customerId').aggregate((agg) => ({

  orderCount: agg.count(),

  totalAmount: agg.sum('amount'),

}));

// one row per customer, each { customerId, orderCount, totalAmount }

const firstTwoChannels = await db.orm.public.Order.groupBy('channel')

  .orderBy((group) => group.channel.desc()) // desc() sorts descending, asc() ascending

  .limit(2)

  .aggregate((agg) => ({ orderCount: agg.count() }));

For Prisma ORM 7 users, groupBy() chains into aggregate() instead of taking a by array with aggregate keys:

- const perCustomer = await prisma.order.groupBy({ by: ['customerId'], _sum: { amount: true } });

+ const perCustomer = await db.orm.public.Order.groupBy('customerId').aggregate((agg) => ({ totalAmount: agg.sum('amount') }));

Filter groups by an aggregate comparison.

• PostgreSQL only. Not available on MongoDB. Call it on the result of groupBy().
• The having() callback gives you count(), count(field), sum(field), avg(field), min(field), and max(field). Call a comparison on the result: h.sum('amount').gt(1000). The comparisons are eq, neq, gt, lt, gte, and lte.
• For more than one condition, call .having(...) again. The conditions are combined with AND.
• You cannot refer to a key you returned from aggregate() here. Write the aggregate out again in having().

const bigSpenders = await db.orm.public.Order.groupBy('customerId')

  .having((h) => h.sum('amount').gt(1000))

  .aggregate((agg) => ({ totalAmount: agg.sum('amount') }));

A condition says which rows match. You write one inside where(), and inside a relation filter such as u.posts.some(...). If you call where() more than once, the conditions are ANDed, and one call can take the object form and the next the callback form. PostgreSQL and MongoDB write conditions differently.

• PostgreSQL compares a field with a method on that field, such as u.email.eq(...), inside a callback. You name the callback's argument yourself, so the u and p below are only names. To combine conditions, import and, or, not, and all from @prisma/orm-postgres/orm-client.
• MongoDB has no callback form. where() takes either the shorthand object or a filter such as MongoFieldFilter.eq('name', 'Alice'). The full list is under MongoFieldFilter. Import MongoFieldFilter, MongoAndExpr, and MongoOrExpr from @prisma/orm-mongo/query-ast/execution. To combine conditions, use .and(), .not(), and MongoOrExpr.of([...]), described under Combinators.

Comparison methods on a field.

• PostgreSQL. Which comparison methods a field has depends on its type. Autocomplete shows which ones apply.
• isNull() and isNotNull() are on every field.
• eq, neq, in, and notIn are on a field whose values can be compared for equality.
• gt, lt, gte, and lte are on a field whose values can be ordered.
• like and ilike are on a text field.
• String fields and text-backed enums have all of them. A native enum field, typed pg.enum(...) in the contract, has all but like and ilike, because PostgreSQL has no pattern match for an enum type. Int, BigInt, Float, Decimal, Uuid, and DateTime fields also have all but like and ilike. A Boolean field has only the equality methods, isNull(), and isNotNull(). A Json field has only isNull() and isNotNull().
• like() matches a SQL pattern case-sensitively. ilike() matches the same pattern case-insensitively.
• in([]) matches no rows. notIn([]) matches every row.

const alice = await db.orm.public.User.where((u) => u.email.eq('alice@example.com')).first();

const notAlice = await db.orm.public.User.where((u) => u.email.neq('alice@example.com')).all();

const after = await db.orm.public.Post.where((p) =>

  p.createdAt.gt(Temporal.Instant.from('2024-01-02T10:00:00.000Z')),

).all();

// gte() is the same comparison, including the boundary value.

const matches = await db.orm.public.User.where((u) => u.email.like('%@example.com')).all(); // case-sensitive

const caseInsensitive = await db.orm.public.User.where((u) => u.email.ilike('%@EXAMPLE.COM')).all();

const lowOrHigh = await db.orm.public.Post.where((p) => p.priority.in(['low', 'high'])).all();

const notLowOrHigh = await db.orm.public.Post.where((p) => p.priority.notIn(['low', 'high'])).all();

const withoutDescription = await db.orm.public.Task.where((t) => t.description.isNull()).all(); // Task.description is String? in the example schema

PostgreSQL full-text search on a text field.

• PostgreSQL. A text field has fullTextMatches(query, options?) to filter and fullTextRank(query, options?) to sort by relevance.

• The SQL query builder has both, as fns.fullTextMatches(column, query) and fns.fullTextRank(column, query). It also has fns.fullTextHeadline(column, query), which returns the matching text with each match marked. Use the SQL query builder when you want that text in your results, because the ORM's select() takes only field names.

• The query argument is a parsed search query, which PostgreSQL calls a tsquery. Passing a plain string is a type error. Build the query with one of these functions from @prisma/orm-postgres/target/full-text: websearchToTsquery(text) reads text the way a web search box does: the user puts a phrase in quotes, writes or between choices, and puts - before a word to leave it out. plaintoTsquery(text) requires every word, and phrasetoTsquery(text) requires the words in order.

• toTsquery(text) reads PostgreSQL's own search syntax, in which & means and, | means or, ! means not, and :* after a word matches any word that starts with it. Text that breaks this syntax makes the query fail when it runs, so never pass user input to toTsquery().

• To add an operator to user text, such as :* for a prefix match while the user types, use the tsquery template tag from the same package: tsquery`${term}:*`. Text inside ${} is searched as plain words, so the user's text cannot add operators of its own. When it holds several words, they must appear next to each other and in that order, and :* applies to each word, so tsquery`${'new y'}:*` finds new followed by a word that starts with y. Do not put quotes around the ${} yourself.

• language is the language whose rules PostgreSQL uses to match words, so that in english a search for run also finds running. It defaults to english. Pass the same language to fullTextMatches() or fullTextRank() and to the function that builds the query, as in the example below. The tag takes it as tsquery({ language: 'german' })`${input}:*`.

  const q = websearchToTsquery(input, { language: 'german' });
  
  const posts = await db.orm.public.Post.where((p) => p.title.fullTextMatches(q, { language: 'german' })).all();

• Add @@fullTextIndex to the model, with the same language, so PostgreSQL can use an index for the search. Without it the query still works, but PostgreSQL reads every row. The attribute takes the one field to index and a name: for the index:

  model Post {
  
    id    Uuid   @id @default(uuid())
  
    title String
  
    @@fullTextIndex([title], name: "post_title_search", language: "german")
  
    @@map("post")
  
  }

  Then run npx prisma contract emit and create the index the way you apply any other contract change: npx prisma db update while you are iterating, or npx prisma migration plan followed by npx prisma db migrate. See How migrations work.

• A native enum field, typed pg.enum(...) in the contract, has no full-text operations.

• normalization sets how the length of the text changes the rank. It is a whole number from 0 to 63, made by adding PostgreSQL's flags together. The default, 0, ignores length. 2 divides the rank by the length of the text, so of two titles with the same matches, the shorter one ranks higher. The PostgreSQL manual lists every flag under Ranking search results.
• coverDensity: true also counts how close together the matching words are, so a row where they appear near each other ranks higher.

import { tsquery, websearchToTsquery } from '@prisma/orm-postgres/target/full-text';

const q = websearchToTsquery('"Prisma ORM" -draft');

const posts = await db.orm.public.Post.select('id', 'title')

  .where((p) => p.title.fullTextMatches(q))

  .orderBy((p) => p.title.fullTextRank(q).desc())

  .limit(10)

  .all();

const suggestions = await db.orm.public.Post.select('id', 'title')

  .where((p) => p.title.fullTextMatches(tsquery`${input}:*`))

  .limit(5)

  .all();

Combine or negate conditions.

• On PostgreSQL, import and, or, not, and all from @prisma/orm-postgres/orm-client. You call them as functions, as in and(a, b). They are not methods on a field.
• On PostgreSQL, and(...) and or(...) take any number of conditions, so and(a, b, c) is fine. not(...) takes one.
• On PostgreSQL, all() takes no arguments and returns a condition that matches every row. This all() is not the .all() you call at the end of a chain to run the query.
• On MongoDB, call .and(other) and .not() on a condition you already built, as in MongoFieldFilter.eq('name', 'Alice').and(...). There is no .or() method. To OR conditions, build MongoOrExpr.of([...]).
• On MongoDB, .and(other) takes exactly one condition. For three, chain it: a.and(b).and(c). You can also write all three at once with MongoAndExpr.of([a, b, c]). MongoAndExpr and MongoOrExpr come from the same module as MongoFieldFilter.

import { and, or, not } from '@prisma/orm-postgres/orm-client';

const both = await db.orm.public.Post.where((p) =>

  and(p.priority.eq('low'), p.userId.eq('00000000-0000-0000-0000-000000000003')),

).all();

const either = await db.orm.public.Post.where((p) => or(p.priority.eq('urgent'), p.priority.eq('high'))).all();

const negated = await db.orm.public.Post.where((p) => not(p.priority.eq('low'))).all();

import { all } from '@prisma/orm-postgres/orm-client';

const everyPost = await db.orm.public.Post.where(() => all()).all();

import { MongoAndExpr, MongoFieldFilter, MongoOrExpr } from '@prisma/orm-mongo/query-ast/execution';

const both = await db.orm.users

  .where(MongoFieldFilter.eq('role', 'author').and(MongoFieldFilter.eq('name', 'Alice')))

  .all();

const either = await db.orm.users

  .where(MongoOrExpr.of([MongoFieldFilter.eq('name', 'Alice'), MongoFieldFilter.eq('name', 'Bob')]))

  .all();

const negated = await db.orm.users.where(MongoFieldFilter.eq('name', 'Alice').not()).all();

const bothAtOnce = await db.orm.users

  .where(MongoAndExpr.of([MongoFieldFilter.eq('role', 'author'), MongoFieldFilter.eq('name', 'Alice')]))

  .all();

const authorsNamedAliceOrBob = await db.orm.users

  .where(

    MongoOrExpr.of([MongoFieldFilter.eq('name', 'Alice'), MongoFieldFilter.eq('name', 'Bob')]).and(

      MongoOrExpr.of([MongoFieldFilter.eq('role', 'author'), MongoFieldFilter.eq('role', 'editor')]),

    ),

  )

  .all();

Filter parents by their related rows.

• PostgreSQL. some(), every(), and none() are methods on a to-many relation, which is a relation that holds many related rows, such as User.posts.
• Each of the three takes either a callback or a plain object of equality matches: u.posts.some((p) => p.priority.eq('urgent')) and u.posts.some({ priority: 'urgent' }) mean the same thing.
• some() with no argument matches parents with at least one related row.
• A user with no posts matches every().
• not(u.posts.some(...)) and u.posts.none(...) match the same rows. Prefer none(...).
• All three are also methods on a to-one relation, such as Post.user, which holds a single related row. There some() means the related row matches, and none() means it does not. every() matches when the related row matches, and also when there is no related row.

const withUrgentPost = await db.orm.public.User.where((u) => u.posts.some((p) => p.priority.eq('urgent'))).all();

const withAnyTag = await db.orm.public.Tag.where((t) => t.posts.some()).all();

const allLowPriority = await db.orm.public.User.where((u) => u.posts.every((p) => p.priority.eq('low'))).all();

const noUrgentPost = await db.orm.public.User.where((u) => u.posts.none((p) => p.priority.eq('urgent'))).all();

const postsByAlice = await db.orm.public.Post.where((p) => p.user.some({ email: 'alice@example.com' })).all();

const postsByAnyoneElse = await db.orm.public.Post.where((p) => p.user.none({ email: 'alice@example.com' })).all();

For Prisma ORM 7 users, some() and none() on a to-one relation replace is and isNot:

- const postsByAlice = await prisma.post.findMany({ where: { user: { is: { email: 'alice@example.com' } } } });

+ const postsByAlice = await db.orm.public.Post.where((p) => p.user.some({ email: 'alice@example.com' })).all();

A plain object of equality matches, ANDed together.

• Available for PostgreSQL and MongoDB.
• Multiple keys are combined with implicit AND. A key set to undefined is skipped.
• On PostgreSQL, a key set to null becomes an IS NULL check. On MongoDB it matches documents where the field is null and documents where the field is missing.
• On PostgreSQL, the shorthand object cannot filter a field whose type cannot be compared for equality, such as Json. Using that field as a key compiles, but fails at run time with an error whose code is ORM.FILTER_UNSUPPORTED. Use the callback form, which has whatever methods that type does support. On a Json field those are isNull() and isNotNull().

const row = await db.orm.public.Post.where({

  userId: '00000000-0000-0000-0000-000000000001',

  priority: 'high',

}).first();

const alice = await db.orm.users.where({ name: 'Alice', role: 'author' }).first();

Helpers that build one MongoDB filter condition. Use them for anything other than equality on a top-level field, which .where({ name: 'Alice' }) already covers. Chaining two of them, as in .where(a).where(b), joins them with AND. For OR, see Combinators.

• MongoDB. Import from @prisma/orm-mongo/query-ast/execution. MongoExistsExpr is in the same module.
• The helpers are of, eq, neq, gt, lt, gte, lte, in, nin, isNull, and isNotNull. Each returns one filter, which you can pass straight to .where() or hold in a variable. There is no ne; use neq.
• of(field, operator, value): pass the MongoDB operator as a string, including the $, for example '$regex'. Use it for operators the named helpers do not cover, such as MongoFieldFilter.of('tags', '$size', 3) on an array field tags. A misspelled operator such as '$regexp' is not caught until the query runs.
• isNull(field) matches documents where the field is null and documents where the field is missing. isNotNull(field) matches everything else. To test only whether a field is present, use MongoExistsExpr.exists(field) or MongoExistsExpr.notExists(field). Those two are all MongoExistsExpr offers.
• There is no regex, elemMatch, all, or size helper. Write those operators through of.

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

const alice = await db.orm.users.where(MongoFieldFilter.eq('name', 'Alice')).first();

const notAlice = await db.orm.users.where(MongoFieldFilter.neq('name', 'Alice')).all();

const strictlyAfter = await db.orm.posts

  .where(MongoFieldFilter.gt('createdAt', new Date('2024-01-01T10:00:00.000Z')))

  .all();

const articlesOrTutorials = await db.orm.posts

  .where(MongoFieldFilter.in('kind', ['article', 'tutorial']))

  .all();

const notArticles = await db.orm.posts.where(MongoFieldFilter.nin('kind', ['article'])).all();

const noBio = await db.orm.users.where(MongoFieldFilter.isNull('bio')).all();

const hasBio = await db.orm.users.where(MongoFieldFilter.isNotNull('bio')).all();

import { MongoExistsExpr, MongoFieldFilter } from '@prisma/orm-mongo/query-ast/execution';

const matching = await db.orm.posts.where(MongoFieldFilter.of('title', '$regex', '^Hello')).all();

const withBioField = await db.orm.users.where(MongoExistsExpr.exists('bio')).all();

Filter into a field of an object stored inside the document, rather than in its own collection, using a dot path.

• MongoDB. Use MongoFieldFilter.eq('address.city', 'San Francisco') for a dotted path. It takes the path as a plain string and needs no cast.
• You can also write .where({ 'address.city': 'San Francisco' }). That object form runs correctly, but it does not type-check. .where() only knows your model's top-level field names, so you must cast.

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

const usersInSf = await db.orm.users.where(MongoFieldFilter.eq('address.city', 'San Francisco')).all();

MongoFieldFilter.eq is the recommended form, because the object form needs a cast, and the cast drops type checking for every key in that object:

const usersInSf = await db.orm.users

  .where({ 'address.city': 'San Francisco' } as unknown as Record<string, unknown>)

  .all();

On MongoDB, update(), updateAll(), updateAndCount(), and upsert() take a callback in place of a data object. The callback gets one argument, the update builder, written u in these examples, and returns an array of the operations you want. updateAndCount() takes the callback in the same place as update(), while upsert() takes it as its update key, beside create. PostgreSQL has no callback form: pass an object to update() instead.

• For a top-level field, read it off the update builder as a property: u.bio.set('Hello').
• For a field inside an embedded object, call the update builder as a function with the dot path: u('address.city').set('San Francisco'). The upsert() callback cannot use a dot path. Given one, upsert() throws an error whose code is ORM.OPERATION_UNSUPPORTED. Set the whole embedded object on its top-level field instead, as in u.address.set({ street: '1 Market St', city: 'San Francisco', zip: '94105', country: 'US' }). Give every field of the embedded object, including the optional ones.

All four methods require .where(), and without one they throw an error whose code is ORM.WHERE_MISSING. .where({}) does not satisfy that rule, because an empty object adds no condition. To change every document, pass a filter that matches them all, such as MongoFieldFilter.isNotNull('_id'). A _id filter takes the hex string that a read gives you back, as the examples below show. On upsert(), .where() picks the document to update, exactly as on update(), and the full example is under upsert(). update() returns the updated document, or null when nothing matched, and updateAll() returns an AsyncIterableResult of the changed documents. updateAndCount() exists on both databases, and it returns the number of changed documents.

• MongoDB only. These are the four operations the update builder gives you for single-value fields: set(), unset(), inc(), and mul(). The four array operations follow under Array operations.

  • set(value) assigns a field.
  • unset() removes a field.
  • inc(n) increments a numeric field. TypeScript offers inc and mul on numeric fields only. Applied to a missing field, inc sets the field to n.
  • mul(n) multiplies a numeric field. Applied to a missing field, it sets the field to 0 whatever n is.

• After variant(...), u.duration.inc(10) is a type error even though it runs correctly. Put // @ts-expect-error on the line directly above the line TypeScript flags. When the type error goes away, TypeScript reports the comment as unused, so delete the comment then.

These examples use the MongoDB accessors from Setting up the client.

Return as many operations as you want, and they are applied together, in one write.

const updated = await db.orm.users

  .where({ _id: '6650f1c2a1b2c3d4e5f60002' })

  .update((u) => [u.bio.set('Set via field op'), u.name.set('Bob R.')]);

// A field inside the embedded `address` object, by dot path:

const moved = await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60002' }).update((u) => [u('address.city').set('San Francisco')]);

duration is declared on Tutorial, not on the base Post model, so each .update(...) line that touches it needs its own // @ts-expect-error directly above it.

const incremented = await db.orm.posts

  .variant('Tutorial')

  .where({ _id: '6650f1c2a1b2c3d4e5f60003' })

  // @ts-expect-error duration is a Tutorial field, not a Post field

  .update((u) => [u.duration.inc(10)]);

const multiplied = await db.orm.posts

  .variant('Tutorial')

  .where({ _id: '6650f1c2a1b2c3d4e5f60003' })

  // @ts-expect-error duration is a Tutorial field, not a Post field

  .update((u) => [u.duration.mul(3)]);

const updated = await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.bio.unset()]);

const changed = await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60002' }).updateAndCount((u) => [u.bio.unset()]);

• MongoDB. push(), pull(), addToSet(), and pop() are available on the update builder. push(value) appends one element. addToSet(value) appends it only if it is not already there. pop(1) removes the last element, and pop(-1) removes the first.
• pull(match) removes every element equal to the argument. If the array holds objects, pass part of an object to remove every element that matches it, as in u.tags.pull({ kind: 'draft' }).
• These operations need an array field, declared with [] in your contract, as in tags String[]. Applied to any other field, the write fails. There is no error.code to match, because the error is MongoDB's own.
• The four lines below are separate calls because one update cannot apply two operations to the same field. One update can still change two different fields, such as pushing to tags and setting bio. The example schema has no array field, so these four lines assume tags String[] on User and will not run against the schema on this page.

await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.push('admin')]);

await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.addToSet('admin')]);

await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.pull('draft')]);

await db.orm.users.where({ _id: '6650f1c2a1b2c3d4e5f60001' }).update((u) => [u.tags.pop(1)]);

contract.d.ts exports a type for every model, under Models. On PostgreSQL the schema is part of the name: Models.public_User. The same file exports a models constant, so typeof models.public.User is the same type. A model type lists every scalar field and every relation, and no query returns exactly that, so three types derive what a query does return:

• Scalars<M> is the model without its relations. It is what all() and first() return when you do not call select() or include().
• Shape<M, Spec> is a row with the fields and relations you choose. In Spec, "+" names the scalar fields and relations to keep, "-" names scalar fields to drop, and any other key is a relation with its own nested spec. If "+" names only relations, every scalar field stays. A relation named in "+" comes with all of its scalar fields and none of its own relations.
• ResultType<typeof query> is one row of a query you have already written.

Import Scalars and Shape from @prisma/orm-postgres/family-contract/types, or @prisma/orm-mongo/family-contract/types on MongoDB. Import ResultType from @prisma/orm-postgres/components/runtime. A contract emitted before 8.0.0-rc.10 has no Models; run npx prisma contract emit again after upgrading.

import type { Models } from './contract.d';

import type { Scalars, Shape } from '@prisma/orm-postgres/family-contract/types';

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

type UserRow = Scalars<Models.public_User>;

// every scalar field of User, no posts and no tasks

type UserResponse = Shape<Models.public_User, { '-': 'email'; posts: { '+': 'id' | 'title' } }>;

// every scalar field of User except email, plus posts as an array of { id, title }

const usersWithPosts = db.orm.public.User.include('posts');

type UserWithPosts = ResultType<typeof usersWithPosts>;

// the same type as Shape<Models.public_User, { '+': 'posts' }>

Use Shape to declare a function's return type once, and TypeScript checks the body at the return. A wrong field name, a relation under "-", or a scalar field in "+" next to a "-" is a compile error on that key. For Prisma ORM 7 users, these replace Prisma.User and Prisma.UserGetPayload<...>.

The methods all() / createAll() / updateAll() / deleteAll() return an AsyncIterableResult. You can use it two ways:

• await the result (or call .toArray()) to get every row in one array.
• Loop over it with for await ... of to take rows one at a time.

createAll(), updateAll(), and deleteAll() exist on PostgreSQL as well as MongoDB, and Write methods documents what each one takes, with examples. If you need the type by name, import it from @prisma/orm-postgres/components/runtime on PostgreSQL, or from @prisma/orm-mongo/components/runtime on MongoDB.

Each result is read once, one way:

• Re-awaiting (or calling .toArray() on) a result you already awaited is safe. It returns the same array.
• Looping a result a second time with for await throws an error whose code is RUNTIME.ITERATOR_CONSUMED. So does switching from one way to the other, such as awaiting a result and then looping it. The message contains already been consumed. To recognise the error, import isRuntimeError from @prisma/orm-postgres/components/runtime on PostgreSQL, or from @prisma/orm-mongo/components/runtime on MongoDB, and check error.code.

PostgreSQL and MongoDB behave the same here, and the example below uses the PostgreSQL accessor. On MongoDB the same methods are on db.orm.users, see Setting up the client. all() returns the result object straight away, and the await is what runs the query, so the example holds the result in a variable.

import { isRuntimeError } from '@prisma/orm-postgres/components/runtime';

const result = db.orm.public.User.all();

const first = await result;

const again = await result.toArray(); // safe: same array

try {

  for await (const user of result) {

  }

} catch (error) {

  if (isRuntimeError(error) && error.code === 'RUNTIME.ITERATOR_CONSUMED') {

    // the result was already read as an array

  }

}

• aggregate((agg) => ({ ... })) resolves to a single object keyed by your aliases. agg is the callback's argument, and it carries the aggregate functions. db.orm.public.Order.aggregate((agg) => ({ total: agg.count() })) resolves to one object, such as { total: 10 }.
• Grouped aggregates documents aggregate(), groupBy() with more than one field, and every function agg gives you. The bullets here cover only the shape of what you get back.
• groupBy(...).aggregate((agg) => ({ ... })) resolves to an array, one object per group. Each object carries the fields you grouped by plus your aliases. db.orm.public.Order.groupBy('customerId').aggregate((agg) => ({ orderCount: agg.count(), totalAmount: agg.sum('amount') })) resolves to [{ customerId, orderCount, totalAmount }, ...].
• count() is always a number (0 over an empty set). sum(), avg(), min(), and max() resolve to null, not 0, over an empty result set.