SQL query builder reference

The SQL query builder builds typed queries from a table, with one method per SQL clause: select(), innerJoin(), groupBy(), and the rest. It is closer to SQL than the ORM client is, and the same db gives you both. Reach for it when you need a join, aggregate, or SQL feature the ORM client doesn't expose, or when you want direct control over the generated SQL.

Some methods work only on some databases, and some fail only when the query runs. For a task-oriented guide to when and how to reach for the builder, see Advanced queries.

The examples run against the schema below and use the user, post, post_tag, and tag tables. The grouped-query examples use a separate customer and order schema, shown under Grouped queries. PostTag is the join table. The two list fields and the PostTag model together are how Prisma ORM 8 declares a many-to-many, which Relations and joins explains. For the schema syntax itself, see Contract authoring.

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")

}

In Prisma ORM 8 your schema.prisma is called contract.prisma. Coming from Prisma ORM 7 gets you from a Prisma ORM 7 project to that file, and has the rest of the setup steps. Install the client with npm install @prisma/orm-postgres. Run npx prisma contract emit to write contract.json and contract.d.ts beside contract.prisma. The examples below assume your own file is in src/prisma/, next to those two files, and they write the type import path as ./contract.d, exactly like that.

You build queries from the client's sql property. Create a Postgres client with postgres(...), then reach tables through db.sql.public. public is the PostgreSQL schema your tables are in. To put a model's table in another PostgreSQL schema, such as auth, wrap the model in a namespace auth { ... } block in your contract, and you then reach its table through db.sql.auth instead. See Namespaces.

Table accessors are keyed by table name, not model name. A table name is the model's @@map value, or the model name exactly as written when the model has no @@map. In the example schema, the User model is mapped to user, so you write db.sql.public.user. The PostTag model for the join table is mapped to post_tag, so you write db.sql.public.post_tag. Column names are the field names unless a field sets @map, and for a field that does, you write the @map name everywhere in the builder. A field declared userId Uuid @map("user_id") is select('user_id') and f.user_id, and each row you get back has a user_id key.

db.runtime() returns the connection that runs a built query. On PostgreSQL it is synchronous, so no await is needed. Hold it in a variable and reuse it. When your app shuts down, close the client with db.close().

A select(), insert(), update(), or delete() call starts a query. build() turns the chain into a query object, which the examples name plan. Nothing touches the database until you pass that object to runtime.query(...). You can run the same built query more than once.

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 plan = db.sql.public.user.select('id', 'email').build();

const users = await runtime.query(plan);

The example schema's Embedding1536 column is a pgvector type, so a client for that schema also needs pgvector in extensions, as Built-in functions shows.

SQLite works the same way: create the client with sqlite(...) from @prisma/orm-sqlite/runtime. SQLite has no schemas, so there is no public in the path and the same table is db.sql.user. Of the SELECT methods on this page, lateralJoin(), outerLateralJoin(), and distinctOn() are not available on SQLite, but every other one is. The rest of this page shows PostgreSQL and writes the root as db.sql.public.

Every table on db.sql.public exposes the query-building methods.

db.sql.public.user.select('id', 'email'); // start a SELECT

db.sql.public.tag.insert([{ label: 'typescript' }]); // start an INSERT

Call .as(alias) on a SELECT chain before build() to use that query as a subquery. The aliased query is then something you can pass to innerJoin(), outerLeftJoin(), and the other join methods. Do not call .as() inside a lateralJoin() callback. For the example, see Subquery via .as().

These methods build and refine a SELECT. They chain, and you resolve the query with build() followed by runtime.query(...). Chain the methods in any order, with two exceptions: a join must come before any call that names the joined table's columns, and the columns you pass to distinctOn() must be the first sort keys in orderBy(). SelectQuery in the tables below is the type they return.

The set of column names a query can use is its scope: it starts as the columns of the table you began with, and every join adds the joined table's columns to it.

The where(), select(), orderBy(), update(), and other callback forms receive two arguments. The first is f, which has one property per column in scope. After a join, every column is on f under its table name, such as f.user.email or f.post.id. A column name that only one table has is also on f directly. The second is fns, an object of expression helpers such as fns.eq() and fns.raw. For an enum column you pass the value stored in the database, which is the string after =, so High = "high" is matched by fns.eq(f.priority, 'high'). A member with no =, such as admin in user_type, is matched by its own name, fns.eq(f.kind, 'admin').

Reduce each row to a chosen set of columns or computed expressions.

select() has three forms:

Every form adds to what the query already returns: calling select() a second time never replaces the first call's columns, whichever form you use. To return fewer columns, start a new query instead.

A computed expression's result type comes from .returns(...) on fns.raw, or from the fns.* function you called. Every fns.raw fragment needs .returns(). A type id is pg/ plus the PostgreSQL type name plus a version, always @1 today, such as pg/int4@1 or pg/uuid@1. A few use a longer name, such as pg/timestamptz-temporal@1 for DateTime. The common ones are pg/text@1, pg/int4@1, pg/int8@1, pg/float8@1, pg/bool@1, pg/uuid@1, and pg/timestamptz-temporal@1. The full list is what your contract uses. Read any column's id from db.sql.public.<table>.columns.<column>.codecId, and see Binding a bare value with param() for which JavaScript type becomes which id. See fns.raw and .returns().

The examples on this page use these ids, which stand for rows that exist:

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

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

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

const untaggedPostId = '00000000-0000-4000-8000-000000000011';

const newestPostId = '00000000-0000-4000-8000-000000000012';

const plan = db.sql.public.user.select('id', 'email').build();

const rows = await runtime.query(plan);

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

const plan = db.sql.public.user

  .select('id', 'displayName')

  .select('emailLength', (f, fns) => fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'))

  .where((f, fns) => fns.eq(f.id, aliceId))

  .build();

const rows = await runtime.query(plan);

const plan = db.sql.public.user

  .select((f, fns) => ({

    id: f.id,

    upperEmail: fns.raw`UPPER(${f.email})`.returns('pg/text@1'),

    emailLength: fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'),

  }))

  .where((f, fns) => fns.eq(f.id, aliceId))

  .build();

const rows = await runtime.query(plan);

// rows === [{ id: aliceId, upperEmail: 'ALICE@EXAMPLE.COM', emailLength: 17 }]

Restrict a query to rows matching an expression.

const plan = db.sql.public.post

  .select('id', 'title', 'priority')

  .where((f, fns) =>

    fns.or(

      fns.and(fns.eq(f.userId, aliceId), fns.eq(f.priority, 'high')),

      fns.eq(f.userId, carolId),

    ),

  )

  .build();

const rows = await runtime.query(plan);

Combine matching rows from two tables. Every column is then on f under its table name, such as f.user.email or f.post.id. A column name that only one table has is also on f directly.

const plan = db.sql.public.post

  .innerJoin(db.sql.public.user, (f, fns) => fns.eq(f.post.userId, f.user.id))

  .select((f) => ({ postId: f.post.id, authorEmail: f.user.email }))

  .where((f, fns) => fns.eq(f.post.id, helloWorldId))

  .build();

const rows = await runtime.query(plan);

// rows === [{ postId: helloWorldId, authorEmail: 'alice@example.com' }]

Keep unmatched rows from one or both sides, filling the missing side's columns with null. Each takes the same (other, on) arguments and returns a SelectQuery as innerJoin() does. outerLeftJoin() is SQL's LEFT OUTER JOIN and keeps every left-table row. outerRightJoin() is RIGHT OUTER JOIN and keeps every right-table row. outerFullJoin() is FULL OUTER JOIN and keeps unmatched rows from both sides.

const plan = db.sql.public.post

  .outerLeftJoin(db.sql.public.post_tag, (f, fns) => fns.eq(f.post.id, f.post_tag.postId))

  .select((f) => ({ postId: f.post.id, tagId: f.post_tag.tagId }))

  .where((f, fns) => fns.eq(f.post.id, untaggedPostId))

  .build();

const rows = await runtime.query(plan);

// rows === [{ postId: untaggedPostId, tagId: null }]

Correlate a per-row subquery against the outer row: for each outer row, the joined subquery can reference that row's columns. Use it for a query such as the newest post for each user.

• Availability: PostgreSQL only. On SQLite TypeScript rejects the call. If you cast, or call it from plain JavaScript, it throws an error whose code is ORM.CAPABILITY_MISSING.
• The callback receives a lateral builder. Start its subquery with lateral.from(otherTable), then chain the usual SELECT methods. Inside the callback, the outer table's columns are on f under its table name, such as f.user.id, so the subquery can filter on the outer row.
• Return the query chain directly from the callback. Do not call .as(...) on it. The first argument of lateralJoin() already names the subquery.
• Every column is on f under its table name, and a column name that only one table has is also on f directly. Outside the callback, the subquery's columns are under the alias you gave it, such as f.latestPost.id. Inside the callback, the joined table is still post, so the same column is f.post.id. Because both tables have id and createdAt, a bare select('id') or orderBy((f) => f.createdAt, ...) throws.
• outerLateralJoin(alias, callback) is the LEFT JOIN LATERAL form: same arguments, and rows with no match keep the outer row.

const plan = db.sql.public.user

  .lateralJoin('latestPost', (lateral) =>

    lateral

      .from(db.sql.public.post)

      .select((f) => ({ id: f.post.id, title: f.post.title }))

      .where((f, fns) => fns.eq(f.post.userId, f.user.id))

      .orderBy((f) => f.post.createdAt, { direction: 'desc' })

      .limit(1),

  )

  .select((f) => ({ userId: f.user.id, latestPostId: f.latestPost.id }))

  .where((f, fns) => fns.eq(f.user.id, aliceId))

  .build();

const rows = await runtime.query(plan);

// rows === [{ userId: aliceId, latestPostId: newestPostId }]

Sort the result set by a column or a computed expression, in a direction you choose.

TypeScript also accepts a nulls option, but it does nothing, and no NULLS FIRST or NULLS LAST reaches the SQL. To put nulls last, sort by a computed value first, then by the column:

db.sql.public.task

  .select('id', 'description')

  .orderBy((f, fns) => fns.raw`CASE WHEN ${f.description} IS NULL THEN 1 ELSE 0 END`.returns('pg/int4@1'))

  .orderBy('description', { direction: 'asc' });

const plan = db.sql.public.user.select('id', 'email').orderBy('email', { direction: 'asc' }).build();

const rows = await runtime.query(plan);

const plan = db.sql.public.user

  .select('id', 'email')

  .orderBy((f, fns) => fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'), { direction: 'asc' })

  .build();

const rows = await runtime.query(plan);

De-duplicate identical projected rows (SELECT DISTINCT).

const plan = db.sql.public.post.select('priority').distinct().build();

const rows = await runtime.query(plan);

// distinct priorities: ['high', 'low', 'urgent']

Keep the first row per distinct key, according to the query's orderBy() (DISTINCT ON).

• Availability: PostgreSQL only. On SQLite TypeScript rejects the call. If you cast, or call it from plain JavaScript, it throws an error whose code is ORM.CAPABILITY_MISSING.
• Sort keys that come after the distinctOn() columns in orderBy() decide which row is kept.

const plan = db.sql.public.post

  .select('id', 'userId', 'createdAt')

  .orderBy('userId', { direction: 'asc' })

  .orderBy('createdAt', { direction: 'asc' })

  .distinctOn('userId')

  .build();

const rows = await runtime.query(plan);

// one row per user, each the earliest post by createdAt

Cap the number of returned rows and skip rows in the ordered result set.

const plan = db.sql.public.user

  .select('id')

  .orderBy('email', { direction: 'asc' })

  .limit(1)

  .offset(1)

  .build();

const rows = await runtime.query(plan);

Call .as(alias) on a SELECT chain before build() to use it as a subquery. Pass the result to any join method as the other argument. SELECT queries and grouped queries have .as(), but queries started with insert(), update(), or delete() do not, so you cannot join against the rows they return.

const highPriorityPosts = db.sql.public.post

  .select('id', 'userId')

  .where((f, fns) => fns.eq(f.priority, 'high'))

  .as('hp');

const plan = db.sql.public.user

  .innerJoin(highPriorityPosts, (f, fns) => fns.eq(f.user.id, f.hp.userId))

  .select((f) => ({ userId: f.user.id, postId: f.hp.id }))

  .build();

const rows = await runtime.query(plan);

Grouping starts with groupBy(), which turns a select query into a GroupedQuery. Write your aggregates in select() first, then call groupBy(). After that you can add having(), orderBy(), limit(), offset(), and distinct().

The examples below use an extra table, order, whose amount column is an integer:

model Order {

  id         Uuid     @id @default(uuid())

  customerId Uuid

  amount     Int

  placedAt   DateTime @default(now())

  @@map("order")

}

Group rows by one or more columns or by a computed expression, producing one row per distinct group.

The examples below reuse the runtime created here, and name each built query plan, the object build() returns.

const runtime = db.runtime();

const plan = db.sql.public.order

  .select('customerId')

  .select('orderCount', (f, fns) => fns.count(f.id))

  .groupBy('customerId')

  .build();

const rows = await runtime.query(plan);

// rows === [{ customerId: '<a customer id>', orderCount: 5 }]

try { await runtime.query(plan); } catch (error) {

  if (error instanceof Error && 'code' in error && error.code === 'RUNTIME.DECODE_FAILED') { /* the count is too big for a number */ }

}

const plan = db.sql.public.order

  .select('yearPlaced', (f, fns) => fns.raw`EXTRACT(YEAR FROM ${f.placedAt})`.returns('pg/numeric@1'))

  .select('orderCount', (f, fns) => fns.count(f.id))

  .groupBy('yearPlaced')

  .build();

const rows = await runtime.query(plan);

// rows === [{ yearPlaced: '2024', orderCount: 10 }]. PostgreSQL computes EXTRACT as numeric, which arrives as the string '2024'. Write Number(row.yearPlaced) for 2024.

Annotate with the type PostgreSQL computes, not the type you want. Here pg/numeric@1 types yearPlaced as string, which is what you receive, while .returns('pg/int4@1') would type it number and still hand you a string. .groupBy('yearPlaced') groups by the alias given to the selected value. To group by the expression itself, repeat the fragment inside groupBy(), .returns() and all:

  .groupBy((f, fns) => fns.raw`EXTRACT(YEAR FROM ${f.placedAt})`.returns('pg/numeric@1'))

A name that is neither a column of the table you started from nor an alias you selected throws an error whose code is ORM.COLUMN_UNKNOWN, at the moment you call groupBy(). If you select a column you did not group or aggregate, the query fails when it runs, not when you build it, and that failure comes back as PostgreSQL's own error, not a Prisma ORM code.

Filter groups by an aggregate comparison. Build the predicate from the aggregate functions (fns.count, fns.sum, fns.avg, fns.min, fns.max) and the comparison functions, comparing against a JavaScript literal.

• Compare against a plain JavaScript number, as in fns.gt(fns.count(), 1). Passing a bigint literal such as 1n throws an error whose code is RUNTIME.ENCODE_FAILED when the query runs. fns.countBigInt() and fns.sumBigInt() are the other way round: compare those against a bigint literal, as in fns.gt(fns.countBigInt(), 1n).
• Do not use a selected alias in having(). TypeScript will let you, but PostgreSQL rejects it and the query fails when it runs. Write the aggregate again instead.

const plan = db.sql.public.order

  .select('customerId')

  .select('totalAmount', (f, fns) => fns.sum(f.amount))

  .groupBy('customerId')

  .having((f, fns) => fns.gt(fns.sum(f.amount), 1000))

  .build();

const rows = await runtime.query(plan); // only the customer whose orders sum over 1000, with totalAmount 1500

const plan = db.sql.public.order

  .select('customerId')

  .select('avgAmount', (f, fns) => fns.avg(f.amount))

  .select('exactAvg', (f, fns) => fns.avgDecimal(f.amount))

  .select('minAmount', (f, fns) => fns.min(f.amount))

  .select('maxAmount', (f, fns) => fns.max(f.amount))

  .groupBy('customerId')

  .having((f, fns) => fns.and(fns.gt(fns.count(), 1), fns.gt(fns.avg(f.amount), 100)))

  .build();

const rows = await runtime.query(plan); // avgAmount is 300, exactAvg is the exact string '300.0000000000000000', minAmount is 100, maxAmount is 500

A GroupedQuery supports the same orderBy(), limit(), offset(), distinct(), and distinctOn() methods as a SelectQuery. Sort by an aggregate alias to order groups. A name that is neither a column of the table you started from nor an alias you selected throws an error whose code is ORM.COLUMN_UNKNOWN, at the moment you call orderBy().

const plan = db.sql.public.order

  .select('customerId')

  .select('totalAmount', (f, fns) => fns.sum(f.amount))

  .groupBy('customerId')

  .orderBy('totalAmount', { direction: 'desc' })

  .limit(1)

  .build();

const rows = await runtime.query(plan); // the single highest-spending customer

Mutations start from a table with insert(), update(), or delete(), and end with build(). Add returning() to get the affected rows back. Run a mutation with no returning() using runtime.execute(plan), which resolves to { affectedRows }. With returning(), run it with runtime.query(plan). The examples below go back to this page's example models, so they use db.sql.public.tag and db.sql.public.user again.

Insert one or more rows in a single statement.

• insert() always takes an array. A single-row insert is a one-element array. There is no separate single-row overload. An empty array throws an error whose code is ORM.MUTATION_DATA_MISSING.
• Leave a column out and the @default(...) in contract.prisma is applied. Prisma ORM generates @default(uuid()) and @default(now()) values when you call build(), not when the query runs. The built query then holds fixed values, so running it twice inserts the same id twice, which fails on a unique column. Call .build() again for each insert.
• There is no ON CONFLICT and no upsert here. For "insert or update", use the ORM client's upsert() or write the statement with db.raw.sql.

PostgreSQL still rejects a missing required column that has no default. Never call .returns() or param() on an insert value.

const plan = db.sql.public.tag.insert([{ label: 'single-row-tag' }]).build();

await runtime.execute(plan); // execute runs a write that returns no rows. id is filled in from @default(uuid())

const plan = db.sql.public.tag.insert([{ label: 'multi-row-a' }, { label: 'multi-row-b' }]).build();

await runtime.execute(plan);

Return columns from the rows affected by an insert(), update(), or delete().

• Availability: PostgreSQL and SQLite.
• returning() takes column names only. There is no returning('*'), and you cannot return a computed expression.

const plan = db.sql.public.tag.insert([{ label: 'returned-tag' }]).returning('id', 'label').build();

const rows = await runtime.query(plan); // rows === [{ id: '<the generated uuid>', label: 'returned-tag' }]

Update matched rows. Set columns with a values object, or derive new values from existing columns with an expression callback. Choose the rows with where(), and use returning() to get the updated rows.

update() accepts a values object or an expression callback, but you cannot mix the two forms in one call.

Every value the callback returns must be an expression. To set a column to a fixed value in the callback form, wrap that value in a fns.raw fragment, as the second example below does.

const plan = db.sql.public.user

  .update({ displayName: 'Bobby' })

  .where((f, fns) => fns.eq(f.email, 'bob@example.com'))

  .returning('id', 'displayName')

  .build();

const rows = await runtime.query(plan); // one row, with displayName now 'Bobby'

const plan = db.sql.public.user

  .update((f, fns) => ({

    displayName: fns.raw`UPPER(${f.displayName})`.returns('pg/text@1'),

    email: fns.raw`${'carol@archived.example.com'}`.returns('pg/text@1'),

  }))

  .where((f, fns) => fns.eq(f.email, 'carol@example.com'))

  .returning('id', 'displayName')

  .build();

const rows = await runtime.query(plan); // one row, with displayName now 'CAROL' and a fixed new email

Delete matched rows. delete() takes no arguments. Choose the rows with where(), and use returning() to get the deleted rows back.

const plan = db.sql.public.tag

  .delete()

  .where((f, fns) => fns.eq(f.label, 'to-delete'))

  .returning('id', 'label')

  .build();

const rows = await runtime.query(plan); // one row, the tag that was deleted

Give a value its type by hand, for a value with no column to take the type from, such as a literal inside fns.raw. param() works in any query, not only in mutations.

• Import param from @prisma/orm-postgres/relational-core/expression.
• Values passed to insert(), update(), and comparison functions such as fns.eq(f.col, value) each take their type from the column they are used with, so you rarely need param(). Reach for it when the type the builder would pick is not the one the column needs. Inside fns.raw no column lends its type, so a bare string becomes pg/text@1 and a value compared there with a uuid column needs param(id, { codecId: 'pg/uuid@1' }). A Temporal.Instant cannot be interpolated bare at all.
• The option is called codecId, but it takes the same type id used everywhere else on this page. Binding a bare value with param() lists the type id each kind of JavaScript value gets, which is where to look for a date, a uuid, a boolean, or an array.

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

const targetId = param('11111111-1111-1111-1111-111111111111', { codecId: 'pg/uuid@1' });

const plan = db.sql.public.user

  .select('id', 'email')

  .where((f, fns) => fns.raw`${f.id} = ${targetId}`.returns('pg/bool@1'))

  .build();

const rows = await runtime.query(plan);

Callback forms receive fns alongside the f argument. fns is an object of helper functions: comparisons, boolean combinations, aggregates, and raw SQL.

These exist on every database:

For in() and notIn(), values is an array or a subquery. All four of in(), notIn(), exists(), and notExists() take a select query that you do not call build() on.

const named = db.sql.public.user

  .select('id', 'email')

  .where((f, fns) => fns.in(f.email, ['bob@example.com', 'carol@example.com']))

  .build();

const authorsById = db.sql.public.user

  .select('id', 'email')

  .where((f, fns) => fns.in(f.id, db.sql.public.post.select('userId')))

  .build();

const authors = db.sql.public.user

  .select('id', 'email')

  .where((f, fns) => fns.exists(db.sql.public.post.select('id').where((inner, innerFns) => innerFns.eq(inner.post.userId, f.user.id))))

  .build();

Which aggregate functions exist depends on your database, and PostgreSQL gives you these eight. The contract type is the one you write in contract.prisma, and the PostgreSQL type is in brackets:

On PostgreSQL, fns also has ilike(expr, pattern), a case-insensitive SQL LIKE match for text columns, as in fns.ilike(f.displayName, '%alice%'). There is no fns.like, so write a case-sensitive LIKE in a fns.raw fragment that returns a boolean:

const plan = db.sql.public.user

  .select('id', 'displayName')

  .where((f, fns) => fns.raw`${f.displayName} LIKE ${'%Alice%'}`.returns('pg/bool@1'))

  .build();

ilike does not work on a native_enum column, typed pg.enum(...), because PostgreSQL cannot pattern-match an enum type. Passing one is a type error. See Enums.

Full-text search on PostgreSQL uses fns.fullTextMatches(column, query) to filter, fns.fullTextRank(column, query) to score, and fns.fullTextHeadline(column, query) to return the matching text with each match marked. Build query from the search text with fns.websearchToTsquery(text), fns.plaintoTsquery(text), fns.phrasetoTsquery(text), or fns.toTsquery(text), or with the tsquery template tag from @prisma/orm-postgres/target/full-text. Never pass user input to fns.toTsquery(), because text that breaks PostgreSQL's search syntax makes the query fail when it runs; use fns.websearchToTsquery() or the tsquery tag for user input. Full-text search on the ORM client reference explains what each of these does, and the index that lets PostgreSQL search without reading every row.

Each of the three full-text functions takes an options object as its last argument, and each accepts language. Pass the same language to the function that builds query, as in fns.fullTextMatches(f.title, fns.websearchToTsquery(q, { language: 'german' }), { language: 'german' }). fullTextRank also takes normalization and coverDensity, which the ORM client reference describes. fullTextHeadline also takes these options:

• startSel and stopSel: the text put before and after each match. PostgreSQL uses <b> and </b> when you leave them out.
• maxWords and minWords: the longest and shortest headline, in words. minWords must be lower than maxWords.
• highlightAll: true: return the whole text with every match marked, instead of a short excerpt around the matches.

This query returns each matching post with its rank and a headline that marks the matches with <mark>:

const q = 'prisma orm';

const plan = db.sql.public.post

  .select('id', 'title')

  .select('rank', (f, fns) => fns.fullTextRank(f.title, fns.websearchToTsquery(q)))

  .select('headline', (f, fns) =>

    fns.fullTextHeadline(f.title, fns.websearchToTsquery(q), { startSel: '<mark>', stopSel: '</mark>' }),

  )

  .where((f, fns) => fns.fullTextMatches(f.title, fns.websearchToTsquery(q)))

  .orderBy((f, fns) => fns.fullTextRank(f.title, fns.websearchToTsquery(q)), { direction: 'desc' })

  .limit(10)

  .build();

const results = await runtime.query(plan);

// { id: string; title: string; rank: number; headline: string }[]

rank is a number, and a higher rank is a better match. headline is a string: the title with each match between <mark> and </mark>.

pgvector is the PostgreSQL extension for vector columns. If your contract uses a pgvector type, such as Embedding1536 in the example schema, pass pgvector in extensions when you create the client, because without it postgres(...) throws an error whose code is RUNTIME.MISSING_EXTENSION_PACK:

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

const db = postgres<Contract>({

  contractJson,

  url: process.env.DATABASE_URL,

  extensions: [pgvector],

});

fns then also has fns.cosineDistance(a, b), which returns the cosine distance between two vectors as a number. Each argument is a vector column or an array of numbers, as in fns.cosineDistance(f.embedding, queryEmbedding). See Extension types.

Write a raw SQL fragment as a tagged template, and interpolate columns, typed expressions, and bare JavaScript values with ${...}. For a bare value, the builder picks the type id from its JavaScript type. Binding a bare value with param() lists which JavaScript type becomes which type id, and shows how to choose a different one with param(...). Call .returns(typeId) to declare the fragment's result type.

• Always call .returns(). Nothing in the builder accepts a fragment without it.
• .returns() only tells TypeScript what type to expect. It does not convert the value. Convert the value in JavaScript, or run the statement with db.raw.sql and .returnsRow(), which does decode.
• Use fns.raw for any SQL feature without a dedicated helper: COALESCE, CAST, LENGTH, UPPER, EXTRACT, and so on. There is no fns.coalesce and no fns.cast.

const plan = db.sql.public.user

  .select('emailLength', (f, fns) => fns.raw`LENGTH(${f.email})`.returns('pg/int4@1'))

  .build();

Compile a query so you can run it.

• build() takes zero arguments on every query type: select, insert, update, delete, and grouped. You supply parameter values where you write them, inside insert([...]), a where() callback, or a param() call.

Run a built query with runtime, which is db.runtime().

Recover a built query's row type at the type level.

• Import ResultType from @prisma/orm-postgres/components/runtime. It works with the ORM client as well as the SQL builder.
• ResultType<typeof plan> is one row, not an array. await runtime.query(plan) resolves to Row[], but ResultType<typeof plan> is Row.
• It also works on ORM queries: ResultType<typeof db.orm.public.User.include('posts')>. db.orm is keyed by model name, so the model is User there and the table is user under db.sql. See Model and result types.

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

const plan = db.sql.public.user.select('id', 'email').build();

type Row = ResultType<typeof plan>; // { id: string; email: string }

const rows = await runtime.query(plan); // Row[]

await runtime.query(plan) collects every row into an array, which is the common case and what every example on this page uses. You can also loop the same result with for await to handle one row at a time. On PostgreSQL every row is loaded before the loop starts, so for await saves nothing there. Use await.

Keep the result in a variable, as in const result = runtime.query(plan), and you can await result as many times as you like. You cannot mix await and for await on the same result, and you cannot for await it twice. Either one throws an error whose code is RUNTIME.ITERATOR_CONSUMED. See AsyncIterableResult.