agent-reference.md

Agent reference

Identify the capability you need, then follow its linked guide; this page is an inventory, not a tutorial.

Use this page to recognize what drizzle-plus can add to an existing Drizzle ORM application. The linked feature pages own setup, options, result behavior, dialect caveats, and examples. Assume a Drizzle database instance with a schema and relations already exists.

Dialect boundaries

Most query and select extensions are available for all three dialects. These are the intentional exceptions:

Capability	PostgreSQL	MySQL	SQLite
create()	Yes	No	No
upsert()	Yes	No	Yes
$withMaterialized() / $withNotMaterialized()	Yes	No	No
updateMany({ returning })	Yes	No	Yes

MySQL's updateMany() type does not accept returning. The feature guides state any additional runtime constraints next to the affected capability.

Query-builder extensions

These methods extend db.query.<table>; they do not create a database connection or change the schema.

Capability	What it signals	Guide
$cursor()	Derive the next-page where and orderBy from an ordered cursor.	Cursor pagination
$findMany()	Type-check, reuse, and merge findMany() configs without executing a query.	Query composition
count()	Count all or relation-filtered rows.	Count rows
create()	Insert one or many rows, optionally skip duplicates, and optionally return fields.	Create rows
findUnique()	Require a primary-key or unique-constraint filter when finding one row.	Find one row
findManyAndCount()	Return page data and the unpaged count for the same filter.	Page and count
updateMany()	Update filtered rows with optional ordering, limit, and dialect-supported returning.	Bulk updates
upsert()	Insert on a new conflict key or update the existing row, with target and returning controls.	Upsert rows

Database, table, and select-builder extensions

Capability	What it signals	Guide
db.$select()	Convert columns, expressions, primitive values, subqueries, and JSON-safe values into a typed selection.	Selections with $select()
db.$values() / db.$withValues()	Represent in-memory rows as a typed VALUES relation or CTE.	VALUES relations
.as(alias)	Name a select or relational query and expose its selected fields to an outer query.	Named subqueries
db.$withRecursive()	Build a WITH RECURSIVE CTE whose callback can reference the CTE being defined.	Recursive CTEs
db.$withMaterialized() / db.$withNotMaterialized()	Override PostgreSQL CTE materialization or inlining.	CTE materialization
table.$without()	Derive a table-column selection while omitting named columns.	Omit columns
.fromSingle()	Start a select from one placeholder row when no table source should remove the result.	Single base row
.withoutFrom()	Build a select from expressions without a FROM clause.	Select without FROM

SQL expressions and functions

Shared syntax helpers

Export	What it signals	Guide
caseWhen()	Build a typed, incremental SQL CASE expression; optional conditions can be skipped.	caseWhen() expressions
nest()	Turn a one-column select or relational query into a decoder-preserving scalar subquery.	Scalar subqueries
toSQL()	Coerce a JavaScript value or SQL wrapper into a typed, parameterized SQL expression.	Coerce values with toSQL()

Universal SQL functions

These functions work across PostgreSQL, MySQL, and SQLite:

Family	Exports	Guide
Text	concatWithSeparator(), length(), lower(), substring(), trim(), upper()	Universal SQL functions
Numeric	abs(), ceil(), floor(), mod(), power(), round(), sqrt()	Universal SQL functions
Null/current time	coalesce(), nullif(), currentDate(), currentTime(), currentTimestamp()	Universal SQL functions
Date decoding	SQLTimestamp, .toDate() on timestamp expressions	Timestamps as Date

Dialect-specific SQL functions

The dialect function guide owns the database-specific null behavior, argument order, result decoding, and cast safety rules.

Dialect	Exports	Guide
PostgreSQL	cast(), concat(), jsonAgg(), jsonAggNotNull(), jsonBuildObject(), position(), rowToJson(), uuidExtractTimestamp(), uuidv7()	Dialect-specific functions
MySQL	cast(), concat(), jsonArrayAgg(), jsonObject(), position()	Dialect-specific functions
SQLite	cast(), concat(), instr(), jsonGroupArray(), jsonObject()	Dialect-specific functions

Utilities and type contracts

Capability	What it signals	Guide
orThrow()	Reject an empty query result and narrow the awaited result to a non-null value.	Throw on empty results
mergeFindManyArgs()	Merge reusable relational query options, including one-level columns, with, extras, and relation filters.	Merge query options
mergeRelationsFilter()	Combine compatible relational where filters.	Merge query options
toSelection()	Convert a reusable record of columns, SQL expressions, and JSON-safe values into a selection.	Selections with toSelection()
Relational query inference	Derive findMany/findFirst args, relation filters, relations, and ordering from a table query builder.	Relational query types
SQL/query result inference	Describe SQL inputs, nullability, query results, scalar subqueries, returning fields, and insert/update values.	Relational query types
JSON value contracts	Constrain values accepted as JSON-serializable selection or object input.	Selections with $select()

Advanced integrations can also use low-level helpers for selected fields, SQL and dialect extraction, relational query building, decoder preservation, JSON object/array decoding, and selected-field ordering. Prefer the documented feature APIs when they cover the use case.