# Query options > The full argument surface for reads, projections, ordering, pagination, filter operators, and metadata. URL: https://better-drizzle.com/docs/reference/query-options This page is the reference for the arguments accepted by reads and (where noted) writes. ## Read arguments Accepted by `findMany`, `findFirst`, `findOne`, and `findUnique`: ```js { where: { description: 'Filter for which rows qualify. Structured input or a Drizzle SQL fragment.', type: 'WhereInput | SQL', }, select: { description: 'Projection: only the listed scalar/relation fields survive. Narrows the result type.', type: 'SelectInput', }, include: { description: 'Keep all scalar columns, attach relations, and optionally project relation counts through `count`.', type: 'IncludeInput', }, orderBy: { description: 'Sort by scalar columns, by fields of `one` relations (nested, any depth), or by the row count of `many` / many-to-many relations. Values can set direction and optional NULL placement; one field map or an array for multi-column ordering.', type: '{ [column]: "asc" | "desc" | { direction: "asc" | "desc"; nulls?: "first" | "last" }; [oneRelation]: OrderBy; [manyRelation]: { count: "asc" | "desc" } } | [...]', }, take: { description: 'Max rows to return. With cursor, a negative value reads preceding rows and preserves the requested order; otherwise its absolute value is used.', type: 'number', }, skip: { description: 'Rows to skip from the start.', type: 'number', }, cursor: { description: 'Cursor position (scalar column values) for cursor-style reads. Cannot be combined with a relation key in `orderBy`.', type: 'CursorInput', }, lock: { description: 'Row lock clause for PostgreSQL/MySQL reads (`FOR UPDATE`, `FOR SHARE`, `SKIP LOCKED`, `NOWAIT`).', type: 'LockOption', }, meta: { description: 'Custom metadata forwarded to hooks and plugins.', type: 'Record', }, comment: { description: 'Comment prefixed to every SQL statement of the call, for tracing. Accepted by every delegate method; see [query comments](/docs/querying/query-comments).', type: 'string', }, } ``` For example, `orderBy: { lastSeenAt: { direction: 'desc', nulls: 'last' } }` puts rows with a `NULL` value after non-`NULL` values. When `nulls` is omitted, the database default applies. PostgreSQL and SQLite use native `NULLS FIRST` / `NULLS LAST` ordering; MySQL gets equivalent behavior through an `IS NULL` ordering term. Better Drizzle rejects an unrecognized provider dialect during client initialization. Relation keys nest like relation filters. A `one` relation takes the related table's own `orderBy` field map, at any depth, and a `many` or many-to-many relation takes exactly `{ count: 'asc' | 'desc' }`: ```ts orderBy: [ { author: { profile: { bio: { direction: 'desc', nulls: 'last' } } } }, { id: 'asc' }, ] orderBy: { posts: { count: 'desc' } } ``` Each relation key becomes a correlated subquery in the same statement's `ORDER BY`. A missing `one` relation sorts as `NULL`; a to-many `count` with no related rows is `0`. An aggregate `count` on a `one` relation, a field map on a to-many relation, or a non-object relation value throws `INVALID_ARGS`, as does a relation key combined with `cursor` or `cursor()`. A real scalar column named `count` on a `one` relation remains sortable. SQL expressions in `orderBy` still require Drizzle directly. See [ordering by relations](/docs/querying/reads#ordering-by-relations) and [limitations](/docs/guides/limitations). `count` and `exists` accept only `where`, `cursor`, and `meta`. `findUnique` accepts the same query-argument shape as the other reads, but its `where` must pin one row: an equality on every primary key column, on a unique column, or on every column of a composite unique key. Otherwise it throws `UNIQUE_WHERE_REQUIRED` before any SQL runs. See [reads](/docs/querying/reads#findunique). **PostgreSQL, MySQL only:** ## Row locks Locks control concurrent row access during reads. See the dedicated [row locks page](/docs/advanced/locks) for the full guide, including dialect support, error handling, and transaction-only enforcement. ```ts await client.transaction(async (tx) => { return tx.users.findMany({ where: { id: { gt: 0 } }, lock: { mode: 'update', skipLocked: true, }, }); }); ``` * Supported on PostgreSQL and MySQL read helpers only. * `count`, `exists`, and all write operations do not accept `lock`. * `skipLocked` and `noWait` are mutually exclusive. * `tables` is PostgreSQL-only. * General relation loading with `include` / relation `select` is intentionally rejected when `lock` is present. ## Filter operators Operators available inside `where`, by column type. A bare value means `equals`; `null` matches nullable columns. | Column type | Operators | | ---------------------- | ---------------------------------------------------------------------------- | | String | `equals`, `in`, `notIn`, `contains`, `startsWith`, `endsWith`, `mode`, `not` | | Number / bigint / Date | `equals`, `in`, `notIn`, `lt`, `lte`, `gt`, `gte`, `not` | | Boolean | `equals`, `not` | | Logical | `AND`, `OR`, `NOT` | | To-one relation | `is`, `isNot` | | To-many relation | `some`, `every`, `none` | `mode` is `'default'` (`LIKE` / `=`, case sensitivity follows the dialect) or `'insensitive'` (patterns use `ILIKE` on PostgreSQL and `lower(col) LIKE lower(?)` elsewhere; `equals`, `in`, `notIn`, and a scalar `not` compare `lower()` on both sides). See [filters](/docs/querying/filters) and [relations](/docs/querying/relations) for examples. ## Projections ```ts // select - narrows, scalars + relations await client.posts.findMany({ select: { id: true, author: { select: { name: true } } }, }); // include - keeps all scalars, adds relations await client.posts.findMany({ include: { author: true } }); // relation counts - available only in include await client.posts.findMany({ include: { count: { select: { comments: true } } }, }); ``` A relation value can be `true` or a nested read-args object (with its own `where`, `select`, `orderBy`, `take`). See [selecting fields](/docs/querying/selecting-fields). ## Pagination arguments `paginate` accepts everything above plus `page`, `perPage`, and `limit`: ```js { page: { description: '1-based page number. Computes `skip`; cannot be combined with it.', type: 'number', }, perPage: { description: 'Rows per page. Alias of `limit`.', type: 'number', default: '10', }, limit: { description: 'Rows per page. Used when `take` is not set.', type: 'number', default: '10', }, take: { description: 'Rows per page. Wins over `perPage` and `limit`.', type: 'number', }, skip: { description: 'Rows to skip before the page starts.', type: 'number', default: '0', }, } ``` It returns `{ data, pagination: { type, page, perPage, total, pageCount, hasNext, hasPrevious } }`, where `perPage` is the resolved page size and `page` is `Math.floor(skip / perPage) + 1`. `cursor` accepts everything above plus: ```js { limit: { description: 'Rows per page. Falls back to `take`.', type: 'number', default: '10', }, after: { description: 'Cursor object to read rows after, for example a previous `nextCursor`.', type: 'CursorInput', }, before: { description: 'Cursor object to read rows before, for example a previous `previousCursor`.', type: 'CursorInput', }, } ``` Pass `after` or `before`, never both. Both take a cursor object, such as a previous `nextCursor` / `previousCursor`. Without `orderBy`, pages are ordered by primary key ascending. `orderBy` must use scalar columns only: a relation key throws `INVALID_ARGS`, including on the first page. It returns `{ data, pagination: { type, hasNext, hasPrevious, nextCursor, previousCursor } }`. See [pagination](/docs/querying/pagination). ## Write-specific arguments Read args are only part of the API surface. The write methods add a few important options that deserve their own reference. ### Single-row writes | Method | Extra args | | -------- | ----------------------------------------------------------- | | `create` | `data`, `skipDuplicates?`, `select?`, `include?`, `meta?` | | `update` | `where`, `data`, `select?`, `include?`, `meta?` | | `delete` | `where`, `select?`, `include?`, `meta?` | | `upsert` | `where`, `create`, `update`, `select?`, `include?`, `meta?` | ### Batch writes | Method | Extra args | | ------------ | ------------------------------------------------------------------------- | | `createMany` | `data[]`, `skipDuplicates?`, `select?`, `include?`, `batchSize?`, `meta?` | | `updateMany` | `where?`, `data`, `select?`, `meta?` | | `updateEach` | `by`, `data[]`, `update`, `where?`, `select?`, `onEmpty?`, `meta?` | | `deleteMany` | `where?`, `select?`, `meta?` | | `upsertMany` | `data[]`, `target`, `update`, `select?`, `batchSize?`, `where?`, `meta?` | `updateMany` and `deleteMany` return `BatchResult` (`{ count, data? }`). PostgreSQL and SQLite return complete affected rows by default, or the scalar fields requested by `select`, using native `RETURNING`. Empty results omit `data`. MySQL returns only `{ count }`. Relation projections are unsupported. ### `updateEach` details ```js { by: { description: 'Drizzle column used to match each input row. Values must be present and unique across `data`.', type: 'Column', required: true, }, data: { description: 'Source rows. Each row must include the `by` value.', type: 'Row[]', required: true, }, update: { description: 'Map of column to a function returning the new value (or a Drizzle `SQL`) for a row. At least one column is required.', type: '{ [column]: (row) => value | SQL }', required: true, }, where: { description: 'Extra filter combined with the generated `by in (...)` filter.', type: 'WhereInput | SQL', }, select: { description: 'Scalar projection for the returned rows.', type: 'SelectInput', }, onEmpty: { description: 'What to do when `data` is empty: return `{ count: 0 }` or throw.', type: '"return" | "throw"', default: '"return"', }, meta: { description: 'Custom metadata forwarded to hooks and plugins.', type: 'Record', }, } ``` ```ts const { count } = await client.users.updateEach({ by: users.id, data: [ { id: 1, active: false }, { id: 2, active: true }, ], update: { active: (row) => row.active, }, }); ``` ### `upsertMany` details ```ts import { sql } from 'drizzle-orm'; await client.users.upsertMany({ data: [ { email: 'a@example.com', name: 'A', active: true }, { email: 'b@example.com', name: 'B', active: false }, ], target: ['email'], update: (ctx) => ({ name: ctx.excluded.name, active: ctx.excluded.active, updatedAt: sql`now()`, }), batchSize: 1000, where: sql`users.disabled = false`, }); ``` `update` accepts four shapes: * `'all'` * an explicit column list * an explicit update object * a function receiving `{ excluded, sql, table }` `target` is a single column name (`'email'`) or an array of column names (`['email']`). `batchSize` splits `data` into chunks and must be a positive integer, otherwise the call throws `INVALID_ARGS`. `where` accepts the same `WhereArg` as other operations: a structured filter or a Drizzle `SQL` fragment. It controls whether the existing conflicting row is updated; it does not filter incoming inserts. PostgreSQL and SQLite support it, while MySQL rejects it.