API reference
Drizzle ORM API reference
Drizzle ORM and type-safe SQL
- Identifiers
- 102
- With full guides
- 91
- Types
- 4
Topics covered
- Schema definition
- Queries
- Relations
- Migrations
- Joins
- Prepared statements
- Transactions
- Drizzle Kit
Function
| Name | What it does |
|---|---|
drizzle | |
pgTable | pgTable declares a typed PostgreSQL table from its database name and column definitions. Columns use drizzle-orm/pg-core builders, with optional callbacks for alternate column construction and table-level configuration. |
mysqlTable | mysqlTable declares a typed MySQL table schema in TypeScript. It combines a table name with MySQL column builders and optional table-level configuration for use in Drizzle queries and migrations. |
sqliteTable | sqliteTable declares a SQLite table schema in TypeScript from a table name and column definitions. The resulting typed table object is used for Drizzle queries, migrations, constraints, and inferred models. |
eq | eq creates an equality filter for a value or another column. Use it in query conditions such as where and joins. |
and | |
or | or combines Drizzle filter expressions into a logical SQL OR condition. Use the result with query methods such as .where() to match rows satisfying at least one alternative. |
relations | |
sql | Drizzle's sql template builds composable, type-safe SQL fragments for use inside query-builder clauses or direct execution. Schema objects are escaped and runtime interpolations are emitted as bound parameters. |
migrate | drizzle-kit migrate applies SQL migrations generated by drizzle-kit generate. It consults migration history, runs only new migrations, and records successful applications. |
integer | Drizzle ORM's PostgreSQL integer() builder defines a signed 4-byte integer column. Use it in pgTable schemas; choose serial instead when the integer must autoincrement. |
text | Drizzle ORM’s PostgreSQL text builder defines a variable-length, unlimited character string column. An optional enum configuration narrows inferred insert and select types but does not validate runtime values. |
pgEnum | pgEnum defines a reusable PostgreSQL enum from a type name and allowed string values. The resulting builder can be used as a table column type and as input to Drizzle's schema-generation integrations. |
uuid | uuid() defines a PostgreSQL UUID column in a Drizzle table schema. Combine it with defaultRandom() for database-generated UUIDs or default() for a fixed UUID value. |
timestamp | Drizzle's PostgreSQL timestamp builder declares date-and-time columns, optionally with timezone support and fractional-second precision. Its mode controls whether application values are inferred as Date objects or strings. |
boolean | Drizzle ORM's PostgreSQL boolean() column builder defines a column using PostgreSQL's standard boolean type. It supports chained defaults such as true or the SQL expression sqltrue. |
jsonb | |
primaryKey | primaryKey defines a table-level primary-key constraint, especially for composite keys. Pass the participating columns and optionally a custom constraint name. |
index | Drizzle’s index function declares a database index on one or more table columns or expressions. Configure it with methods such as .on(), .using(), .where(), and .concurrently() where supported. |
count | Drizzle ORM's count() aggregate counts all rows when called without an argument, or non-NULL values from a supplied column. Use it in a select query and combine it with filters, joins, and aggregations. |
desc | |
asc | asc creates an ascending ordering expression for a Drizzle column. Use it with .orderBy() for ordinary sorting or cursor-based pagination. |
inArray | inArray creates a Drizzle filter that checks whether a column matches one of several values. It accepts either an array or a subquery and produces an SQL IN condition. |
ne | ne creates a type-safe inequality condition for Drizzle ORM queries. It matches rows where the first operand is not equal to the second and generates SQL with <>. |
gt | gt builds a greater-than filter for Drizzle queries. It compares a column or SQL expression with a value or another expression and is typically passed to where. |
gte | gte builds a Drizzle filter for values greater than or equal to a threshold. Use it in where clauses with a literal or another column. |
lt | lt creates a strict less-than filter condition in Drizzle ORM. Use it in where clauses to select rows whose column value is below a supplied value. |
lte | lte creates a less-than-or-equal filter condition in Drizzle ORM. It supports comparing a column with a value or comparing two columns. |
isNull | isNull creates a Drizzle filter for rows where a column or SQL expression is SQL NULL. Use it inside a query's where clause to generate an IS NULL predicate. |
isNotNull | isNotNull creates a Drizzle filter that keeps rows whose value is not SQL NULL. Use it in where() to generate an IS NOT NULL predicate. |
notInArray | notInArray filters rows whose column value is not among a supplied array or the results of a subquery. It produces a SQL NOT IN condition. |
between | between is a built-in Drizzle filter operator for selecting rows whose column value lies within a supplied range. Import it from drizzle-orm and pass the resulting condition to .where(). |
notBetween | notBetween builds a Drizzle filter for values outside a two-bound range. Use it in a query’s where clause to generate SQL with NOT BETWEEN. |
like | ilike builds a case-insensitive SQL pattern-matching expression for use in Drizzle query filters. Pass it to .where() directly or compose it with other conditions. |
ilike | ilike is a Drizzle ORM filter operator for case-insensitive pattern matching. Use it in .where() with a column and pattern, and combine it with and() or or() for compound conditions. |
notLike | notLike() filters out rows whose column value matches a SQL LIKE pattern. The comparison is case-sensitive. |
not | not inverts a Drizzle filter condition, allowing a query to select rows that do not satisfy the wrapped predicate. It is imported from drizzle-orm and commonly used inside .where(). |
exists | exists turns a Drizzle subquery into an SQL EXISTS condition. Use it in where to retain outer rows for which the subquery finds at least one result. |
countDistinct | countDistinct is a Drizzle ORM aggregation helper that counts unique, non-duplicate values in an expression. Use it in a select projection to produce a count(distinct ...) SQL aggregate. |
sum | sum creates a SQL aggregate expression that adds all non-null values from a column or expression. Use it inside a select query, and group non-aggregate selected columns when necessary. |
avg | avg is Drizzle ORM’s aggregate helper for calculating the arithmetic mean of all non-null values in a selected expression. Use it in core select queries, and group non-aggregated columns when they appear alongside the average. |
min | |
max | max is Drizzle ORM's aggregate helper for selecting the greatest value of a column or SQL expression. It is used inside a select projection and corresponds to SQL MAX. |
serial | serial() declares a PostgreSQL auto-incrementing four-byte integer column. It is commonly used for generated identifier columns, often with .primaryKey(). |
varchar | Drizzle ORM's PostgreSQL varchar builder declares variable-length character columns. Configure an optional character limit or an enum array for narrower inferred TypeScript insert and select types. |
json | Drizzle's json() builder declares a PostgreSQL JSON column. $type<T>() adds compile-time inference and checking for JSON values across defaults, inserts, and selects. |
foreignKey | foreignKey declares a database-level foreign-key constraint in a Drizzle table definition. It maps one or more child-table columns to corresponding columns in a referenced parent table, with optional naming and referential actions. |
unique | Drizzle’s unique declaration adds a database constraint that rejects duplicate values in one column or a combination of columns. It can be named and is commonly used in a table callback for composite uniqueness. |
one | r.one.<table> defines a relation that returns one related object instead of an array. Configure its source and target columns, and optionally control requiredness, aliases, and conditional matching. |
many | many() defines a relation that returns multiple records from a target table. Configure it with source and target columns, and optionally an alias or filter. |
real | real() declares a PostgreSQL single-precision floating-point column in a Drizzle schema. PostgreSQL also names this type float4. |
numeric | Drizzle's PostgreSQL numeric builder defines exact numeric columns with optional precision, scale, and application modes. It supports JavaScript number or bigint representations when appropriate. |
date | Drizzle ORM’s MySQL date builder declares a calendar-date column using the SQL date type. Configure its infer mode to expose values as JavaScript Date objects or strings. |
char | |
decimal | Drizzle's MySQL decimal() builder defines an exact fixed-point DECIMAL column. You can set precision, scale, and the value mode used by the schema. |
bigint | Drizzle’s PostgreSQL bigint builder defines a signed 8-byte integer column. Its mode selects whether application code receives the value as a number, bigint, or string. |
Type Alias
| Name | What it does |
|---|---|
InferSelectModel | InferSelectModel derives a TypeScript select-row model from a Drizzle table schema. Pass the table type with typeof to avoid manually duplicating the row shape. |
InferInsertModel | InferInsertModel derives the TypeScript shape for inserting rows from a Drizzle table schema. It is used as InferInsertModel<typeof users> and has a table-property equivalent, $inferInsert. |
SQL | Drizzle’s sql template embeds parameterized SQL fragments in type-safe queries. The sql<T> form annotates the expected result type for TypeScript, while .as() names computed fields for later reference. |
PgTable | PgTable is the PostgreSQL-specific table type family used for Drizzle table objects created with pgTable. It is useful when typing helpers that accept PostgreSQL tables, while concrete schemas are declared with PostgreSQL builders. |
DrizzleConfig | DrizzleConfig describes the settings Drizzle Kit uses for database migrations and related operations. It is typically declared with defineConfig in drizzle.config.ts or a JavaScript configuration file. |
Module
| Name | What it does |
|---|---|
db.select | Drizzle’s select API fetches rows with SQL-like, type-safe queries. Use an empty selection for all table columns or a selection object for a custom projection. |
db.insert | Inserts one or more typed rows into a Drizzle table with an SQL-like builder. The same page also covers parameterization, insert-from-select, returned rows, and conflict-handling forms. |
db.update | Drizzle’s update builder changes rows in a table using schema-typed values and an optional predicate. It supports parameterized values, SQL expressions, PostgreSQL returning, and dialect-specific clauses such as limit and orderBy. |
db.delete | Drizzle’s delete builder removes rows from a table, optionally restricting them with conditions. PostgreSQL can return full deleted rows or selected fields with .returning(). |
db.query | Drizzle’s db.query API provides type-safe relational queries for nested data. Initialize Drizzle with the schema’s tables and relations, then use methods such as findMany and findFirst with with and columns options. |
db.execute | db.execute runs raw SQL through Drizzle ORM. Use the sql template to interpolate schema objects and runtime values while keeping values parameterized. |
db.transaction | db.transaction runs related SQL operations atomically: all changes commit together, or the transaction rolls back. Use the supplied tx handle for queries, nested callbacks for savepoints, and tx.rollback() to abort from business logic. |
db.with | db.with(...) attaches a named common table expression to a subsequent query. Define the expression with db.$with(...).as(...), then use it with insert, update, delete, or select operations. |
db.$count | db.$count() wraps SQL count(*) for a table and can optionally apply a filter. It also works as a correlated subquery for related-record counts. |
db.batch | db.batch runs multiple supported Drizzle query builders as one batch with the Neon HTTP PostgreSQL driver. The typed response preserves the input order. |
.from | .from() sets the table or query source for a Drizzle select statement. It works with projections, filters, joins, ordering, pagination, subqueries, and prepared statements while preserving type-safe results. |
.where | .where() filters rows in Drizzle ORM queries using SQL expressions such as eq(), and(), and or(). Custom sql expressions can express arbitrary predicates. |
.orderBy | .orderBy() controls result ordering in Drizzle's core query builder. Relational queries also support ordering objects, callbacks, and custom SQL expressions. |
.groupBy | .groupBy adds grouping criteria to a Drizzle select query and generates SQL GROUP BY. It is commonly used with aggregate expressions, with .having available to filter groups. |
.having | |
.limit | .limit() adds a SQL LIMIT clause and caps the rows returned by a query. Pair it with .offset() for pagination, and use a stable unique ordering for consistent pages. |
.offset | .offset() adds an offset clause to a Drizzle select query, skipping a specified number of rows. It is commonly paired with .limit() and ordering to implement pagination. |
.leftJoin | leftJoin combines a source table with another table while preserving source rows that have no match. Drizzle models the joined table, or its projected fields, as nullable. |
.rightJoin | rightJoin performs a SQL RIGHT JOIN in Drizzle’s type-safe query builder. It preserves every row from the joined table and makes the source-side table nullable when no match exists. |
.innerJoin | innerJoin combines rows from two Drizzle tables when a supplied SQL condition matches. It produces SQL INNER JOIN semantics and type-inferred, nested results keyed by the participating tables. |
.fullJoin | .fullJoin() combines two tables while retaining unmatched rows from both sides. Drizzle reflects this by making each joined table nullable in the inferred result. |
.values | .values() supplies one row object or an array of row objects to a Drizzle insert builder. Drizzle maps the objects to table columns and parameterizes the supplied values. |
.set | .set() defines the column assignments for a Drizzle ORM update query. It accepts schema-column keys with values or SQL expressions, and can be combined with conditions and returning clauses. |
.returning | .returning() lets PostgreSQL update statements return affected rows or a selected projection immediately. It supports named result fields and, with update joins, can return columns from joined tables. |
.onConflictDoUpdate | .onConflictDoUpdate() performs an upsert: it updates an existing row when an insert conflicts with a selected target. Use set, plus optional targetWhere and setWhere conditions, to control the conflict update. |
.onConflictDoNothing | onConflictDoNothing makes an insert a no-op when a conflict occurs. You can let the database infer the conflict or explicitly provide a target. |
.onDuplicateKeyUpdate | .onDuplicateKeyUpdate() adds MySQL duplicate-key upsert behavior to a Drizzle insert. MySQL automatically chooses conflicts from primary keys and unique indexes, while set defines the update values. |
.for | |
.as | Drizzle ORM’s .as() helper assigns an explicit alias to a selected field or SQL expression. It is useful for naming computed fields clearly, especially in complex queries. |
.prepare | .prepare converts a Drizzle query into a reusable prepared statement. Execute it repeatedly, supplying named placeholder values when runtime inputs are needed. |
.all | |
.get | |
.run | For SQLite, db.run executes a raw parameterized SQL statement and returns the driver's run result. Use it when Drizzle's higher-level query API cannot express the required query. |
.findMany | findMany retrieves multiple records through Drizzle’s relational Queries API. Configure its options to select fields, filter and paginate rows, and include typed nested relations. |
.findFirst | findFirst() retrieves the first matching record through Drizzle ORM’s relational query API. It limits the query to one result and can load related data. |
Command
| Name | What it does |
|---|---|
drizzle-kit generate | Generates SQL migrations from a Drizzle schema by comparing its current snapshot with the latest migration snapshot. It writes the resulting migration files to the configured output directory. |
drizzle-kit migrate | drizzle-kit migrate applies SQL migrations generated by drizzle-kit generate. It checks migration history, runs only pending migrations, and records successful applications. |
drizzle-kit push | drizzle-kit push synchronizes a Drizzle schema directly with a database without writing SQL migration files. It is intended for fast, code-first schema iteration. |
drizzle-kit studio | Starts a local Drizzle Studio server connected through the credentials in drizzle.config.ts. It defaults to 127.0.0.1:4983 and supports host, port, and verbose CLI options. |
drizzle-kit pull | drizzle-kit pull introspects an existing database and generates a Drizzle schema.ts file in the configured output directory. It supports database-first migrations and can mark the result as the initial migration with --init. |
drizzle-kit check | Checks generated SQL migration history for consistency, including race conditions or collisions. It requires a dialect and supports configuration through drizzle.config.ts or CLI flags. |
Search across every package and filter by type in your dashboard.
Start learning