100 expert Drizzle answers researched from official documentation. Every answer cites authoritative sources you can verify.
Use notExists(subquery) in a where clause. Example: const query = db.select().from(table2); db.select().from(table).where(notExists(query)). This generates: SELECT * FROM table WHERE NOT EXISTS (SELECT * from table2). Import notExists from 'drizzle-orm'.
Set operations can be imported from dialect-specific modules: 'drizzle-orm/pg-core' (PostgreSQL), 'drizzle-orm/mysql-core' (MySQL), 'drizzle-orm/sqlite-core' (SQLite), 'drizzle-orm/singlestore-core' (SingleStore).
Chain nested 'with' statements. Example: await db.query.users.findMany({ with: { posts: { with: { comments: true } } } }). This loads users with their posts, and each post with its comments.
Drizzle provides two main ways for querying your database: SQL-like syntax (using methods like db.select().from(table)) or Relational Syntax (using methods like db.query.users.findMany()).
Use .orderBy() with asc() or desc() functions. For single column: .orderBy(asc(users.name)) or .orderBy(desc(users.name)). For multiple columns: .orderBy(asc(users.name), desc(users.age)). Import asc and desc from 'drizzle-orm'.
Use .having() with a callback that references the aggregated result. Example: .having(({ count }) => gt(count, 1)). The callback receives the selected fields as parameters.
Use selectDistinctOn([columns], selectObject). Example: await db.selectDistinctOn([users.id], { name: users.name }).from(users).orderBy(users.id). This is PostgreSQL-only and generates: select distinct on ("id") "name" from "users" order by "id".
Drizzle supports: innerJoin (returns only matching records from both tables), leftJoin (returns all records from left table and matching from right), rightJoin (returns all records from right table and matching from left), and fullJoin (returns all records when there's a match in either table).
If you don't provide an alias using .as('name'), the field type will become DrizzleTypeError. You must add aliases to arbitrary SQL values in CTEs.
No, Drizzle ORM does not have native CASE WHEN support. You must use the sql template function to write CASE statements. Example: sqlcase when ${table.type} = 'a' then ${someTable.name} else ${otherTable.title} end.
.$dynamic() enables dynamic mode, which removes the restriction of invoking methods only once. This allows building queries dynamically, though it does NOT combine multiple .where() calls - use and() or or() instead.
MySQL uses .$returningId() which returns an array of objects with the primary key fields. MySQL does not support the standard .returning() method.
You can import count, sum, avg (and other aggregate functions) from 'drizzle-orm'. Example: import { count, sum } from 'drizzle-orm'. Use them with .mapWith() to specify the return type: sum(purchases.netPrice).mapWith(Number)
Drizzle supports: UNION, INTERSECT, EXCEPT, UNION ALL, INTERSECT ALL, and EXCEPT ALL. All operations maintain full type safety and have dialect-specific support.
Yes, CTEs can include insert, update, and delete statements. Example: const sq = db.$with('sq').as(db.delete(users).where(eq(users.name, 'John')).returning()); const result = await db.with(sq).select().from(sq).
In MySQL, SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
No, the length parameter is optional for varchar columns according to PostgreSQL docs. Without a specified length, PostgreSQL accepts it as varchar without a length constraint (essentially unlimited).
No, the length parameter is optional for char columns according to PostgreSQL docs.
Use json().$type
The available modes are: 'buffer', 'bigint', and 'json'. For example: blob({ mode: 'buffer' }), blob({ mode: 'bigint' }), or blob({ mode: 'json' }).
It creates a GENERATED ALWAYS AS IDENTITY column, meaning the database always generates a value. Manual insertion or updates are not allowed unless the OVERRIDING SYSTEM VALUE clause is used. You can specify sequence properties like startWith.
When fetching numeric type data from PostgreSQL, Drizzle ORM represents numbers as strings to maintain precision.
PostgreSQL offers JSON (stores text as-is) and JSONB (converts data into binary format when stored). JSONB is typically preferred for better performance and indexing capabilities.
NO ACTION is the default action. It prevents the deletion of a row in the parent table if there are related rows in the child table.
The .defaultRandom() method generates random UUIDs as default values using PostgreSQL's gen_random_uuid() function.
The 'timestamp' mode uses seconds instead of milliseconds. The 'timestamp_ms' mode stores and operates with the date as a number of milliseconds. Both are treated as Date objects in the application but stored as integers in the database.
For timestamp mode, use .default(sql(unixepoch())). For timestamp_ms mode, use .default(sql(unixepoch() * 1000)).
The two core packages are: 1) drizzle-orm (the core library with query builder, schema definitions, and type-safe interactions), and 2) drizzle-kit (a CLI tool for managing migrations and other development tasks, installed as a dev dependency).
npm install drizzle-orm better-sqlite3
npm install -D @types/better-sqlite3
npm install -D drizzle-kit
Yes, drizzle-orm includes TypeScript type definitions in the package, so you don't need to install separate @types/drizzle-orm packages.
Yes, you can use glob patterns like "./schema/*.ts" or an array of globs to include multiple schema files.
Configure it in the node-postgres Pool: const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 }); where max specifies the maximum number of connections.
drizzle-orm is the core ORM library for runtime database operations and queries. drizzle-kit is a CLI tool for managing database migrations and schema management, typically installed as a dev dependency.
import { drizzle } from 'drizzle-orm/node-postgres'; import { Pool } from 'pg'; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); const db = drizzle({ client: pool });
import { drizzle } from 'drizzle-orm/libsql'; import { createClient } from '@libsql/client'; const client = createClient({ url: process.env.DATABASE_URL, authToken: process.env.DATABASE_AUTH_TOKEN }); const db = drizzle(client);
@planetscale/database - PlanetScale's serverless driver allows you to access MySQL over an HTTP connection.
The default value is "drizzle" - this is the folder where migrations, JSON snapshots, and schema.ts files are stored.
Drizzle ORM is ~7.4kb minified+gzipped, and it's tree shakeable with exactly 0 dependencies.
Use the .overridingSystemValue() method on the insert statement: await db.insert(table).values([...]).overridingSystemValue(). This feature was added in version 0.36.4.
No, you can't add a stored generated expression to an existing column in SQLite. However, you can add a virtual expression to an existing column.
If no default (or $defaultFn) value is provided, the $onUpdate() function will be called when the row is inserted as well, and the returned value will be used as the column value.
The two modes are 'virtual' and 'stored'. Virtual columns are computed during read operations and do not occupy storage space. Stored columns are computed when a row is inserted or updated and their values are stored in the database.
No. PostgreSQL currently lacks support for virtual generated columns. PostgreSQL only supports stored generated columns.
integer('author').references(() => users.id, { onDelete: 'cascade' }).notNull() - the second parameter is an options object with onDelete and/or onUpdate properties.
Define it in the table's second parameter callback: (table) => ({ pk: primaryKey({ columns: [table.userId, table.roleId] }) })
PostgreSQL now recommends identity columns over serial types, and Drizzle has fully embraced this change.
nullsNotDistinct() restricts having more than one NULL value in a unique constraint. Example: unique('custom_name').on(t.name, t.state).nullsNotDistinct()
No. Currently, $default($defaultFn) and $onUpdate() don't have access to their adjacent insert/update values. This is a known limitation tracked in GitHub Issue #3044.
No, you can't change the generated constraint expression and type using push - Drizzle-kit will ignore this change. To make it work, you would need to drop the column, push, and then add a column with a new expression.
The migrate() function requires the migrationsFolder parameter specifying the path to the folder containing migration files (e.g., { migrationsFolder: './drizzle' }).
You should add driver to drizzle.config.ts ONLY if you are using aws-data-api, turso, d1-http, or expo. Otherwise, you can remove the driver parameter.
drizzle-kit migrate reads migration.sql files in the migrations folder, fetches migration history from the __drizzle_migrations table in the database, picks previously unapplied migrations, and applies only the new migrations to the database.
Yes, you can have multiple config files in the project and specify which one to use with the --config option. Example: drizzle-kit generate --config=drizzle-dev.config.ts
drizzle-kit push pushes schema changes directly to the database while omitting SQL files generation. drizzle-kit generate creates timestamped SQL migration files with JSON snapshots for version control.
The breakpoints parameter is true by default. It embeds --> statement-breakpoint comments into generated SQL migration files.
Use drizzle-kit generate --custom or drizzle-kit generate --custom --name="migration_name" to create an empty migration file that you can populate with custom SQL statements.
The optional parameters are: (1) migrationsTable - custom table name for migration history (default: __drizzle_migrations), and (2) migrationsSchema - PostgreSQL-specific schema for the migrations table (default: drizzle).
The default output folder is ./drizzle. The out parameter is optional and defaults to drizzle.
Drizzle Kit stores: (1) migration SQL files, (2) meta folder containing JSON snapshots, and (3) journal file (_journal.json) for tracking migration history.
Up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.
You can change from stored to virtual, but you cannot change from virtual to stored. Also, you cannot change the generated constraint expression and type using push - Drizzle-kit will ignore this change.
The function is called when the row is updated. If no default or $defaultFn value is provided, the $onUpdate function will also be called when the row is inserted.
numeric({ precision: 100, scale: 20 }) where precision is the total number of digits and scale is the count of digits in the fractional part.
No, these values do not affect drizzle-kit behavior - they are only used at runtime in drizzle-orm.
pgTable from 'drizzle-orm/pg-core' for PostgreSQL, mysqlTable from 'drizzle-orm/mysql-core' for MySQL, and sqliteTable from 'drizzle-orm/sqlite-core' for SQLite.
Drizzle Kit is configured through drizzle.config.ts configuration file or via CLI params.
Yes, Drizzle ORM is tree-shakeable, allowing applications to include only the code they actually use.
Based on your schema, Drizzle Kit lets you generate and run SQL migration files, push schema directly to the database, pull schema from database, spin up Drizzle Studio, and has utility commands.
No, Drizzle ORM does not require code generation. Its API's typing is fully powered by TypeScript's type inference, and changes made on the schema side are immediately reflected on the database client API side.
No, with Drizzle you can be fast out of the box and save time and costs while never introducing any data proxies into your infrastructure.
Drizzle Studio supports explicit null and empty string values, booleans, numbers and big integers, JSON objects and JSON arrays.
Currently, only 'postgis' is supported. When specified as extensionsFilters: ['postgis'], Drizzle will skip the geography_columns, geometry_columns, and spatial_ref_sys tables.
The verbose option prints all SQL statements during drizzle-kit push command execution.
No, there is a known limitation where you cannot use both url and ssl keys together in the same configuration. If you need SSL with a URL connection, you may need to use individual connection parameters instead or include SSL parameters in the URL string itself.
The default value is ./drizzle. This is where migration files are stored if not explicitly specified.
Turso is a SQLite-compatible database built on libSQL (Open Contribution fork of SQLite) that can connect to both local SQLite files and remote Turso databases, while 'sqlite' dialect is for standard SQLite with local file-based databases only. LibSQL offers more ALTER statements and additional features like encryption at rest.
Use ES module syntax with defineConfig: import { defineConfig } from 'drizzle-kit'; export default defineConfig({ ... }). This provides better TypeScript type checking and autocomplete compared to plain object exports.
AWS Data API driver requires: database, resourceArn, and secretArn instead of standard connection parameters. Example: { database: 'database', resourceArn: 'resourceArn', secretArn: 'secretArn' }
Chain the .notNull() method on the column builder: varchar('name', { length: 255 }).notNull().
Yes, starting from version 0.29.0, you can specify custom names using the 'name' parameter: primaryKey({ name: 'composite_key', columns: [table.id, table.name] }).
The .$type
Use typeof users.$inferInsert or InferInsertModel
It returns an array or object of constraint definitions such as indexes, foreign keys, primary keys, unique constraints, and check constraints.
Use the .references() method on the column: authorId: int4('author_id').references(() => user.id).