Drizzle FAQ & Answers

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

The 'ne' operator (not equal) generates the '<>' operator in SQL.

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

Serverless functions like AWS Lambdas or Vercel Server functions provide immense benefits, since they can live up to 15 minutes and reuse both database connections and prepared statements.

Starting from version 0.29.0, you can only invoke most query builder methods once (e.g., .where() can only be called once). For dynamic queries, use .$dynamic() mode, but note that multiple .where() calls won't combine - use and() or or() instead.

Use exists(subquery) in a where clause. Example: const sq = db.select({ id: sql1 }).from(posts).where(eq(posts.userId, users.id)); await db.select().from(users).where(exists(sq)). Import exists from 'drizzle-orm'.

Use db.delete(table).where(condition). Example: await db.delete(users).where(eq(users.name, 'Dan')). The .where() clause specifies which rows to delete.

The .returning() method returns an array (model[]), so you need to access the first element with model[0] to get the inserted data. Example: const newUser = await db.insert(users).values({ name: 'Dan' }).returning(); // Access with newUser[0]

Use the .where() method with a filter operator. Example: await db.select().from(users).where(eq(users.id, 1)). You must import the eq operator from 'drizzle-orm'.

Use intersect(query1, query2) or chain with .intersect(). Example: await db.select({ courseName: depA.courseName }).from(depA).intersect(db.select({ courseName: depB.courseName }).from(depB)). Import from the dialect module like 'drizzle-orm/sqlite-core'.

Yes, Drizzle provides limit & offset API for both top-level queries and nested entities. Example: await db.query.posts.findMany({ limit: 5, offset: 2, with: { comments: { offset: 3, limit: 3 } } }).

Use db.update(table).set(object).where(condition). Example: await db.update(users).set({ name: 'Mr. Dan' }).where(eq(users.name, 'Dan')). The .set() method is required and specifies the columns and values to update.

Use the and() function and pass multiple conditions as arguments. Example: where(and(eq(users.id, 1), gt(users.age, 18))). Import and from 'drizzle-orm'.

The formula is: offset: (page - 1) * pageSize. For example, for page 2 with pageSize 10: offset would be (2 - 1) * 10 = 10.

Use .onConflictDoNothing() after .values(). Example: await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoNothing(). This prevents errors when inserting duplicate keys.

Drizzle provides these filter operators: eq, ne, gt, gte, lt, lte, exists, notExists, isNull, isNotNull, inArray, notInArray, between, notBetween, like, ilike, notIlike, not, and, or. All can be imported from 'drizzle-orm'.

Use .innerJoin(table, condition). Example: await db.select().from(users).innerJoin(pets, eq(users.id, pets.ownerId)). This generates SQL like: select ... from "users" inner join "pets" on "users"."id" = "pets"."owner_id"

Yes, the selectDistinctOn() method supports multiple Drizzle PG Columns in the array. Example: .selectDistinctOn([table.column1, table.column2], { fields }).

Use sqlexcluded.columnName. Example: .onConflictDoUpdate({ target: users.id, set: { name: sqlexcluded.name, updatedAt: sqlnow() } }). The 'excluded' keyword refers to the values that would have been inserted.

Pass undefined to and() and it will be ignored. Example: where(and(term ? ilike(posts.title, term) : undefined, categories.length > 0 ? inArray(posts.category, categories) : undefined)). Undefined conditions are automatically filtered out.

Use the 'columns' parameter with an object where true includes the column and false excludes it. Example: await db.query.posts.findMany({ columns: { id: true, content: true } }). For nested relations: with: { comments: { columns: { authorId: false } } }

Drizzle provides .findMany() to get all records with relations, and .findFirst() to get the first record with relations. Both use the relational query syntax with db.query.tableName.

The basic syntax is: await db.select().from(users). This selects all columns from the users table.

Pass multiple CTEs to the .with() method. Example: const cte1 = db.$with('cte1').as(...); const cte2 = db.$with('cte2').as(...); await db.with(cte1, cte2).select().from(cte2).leftJoin(cte1, ...).

Use the 'with' property with an object specifying which relations to include. Example: await db.query.posts.findMany({ with: { comments: true } }). This loads posts with their related comments.

If you pass just the column without asc() or desc(), it orders by that column in ascending order by default. Example: .orderBy(users.name) is equivalent to .orderBy(asc(users.name)).

Call tx.rollback() inside the transaction callback. This throws an exception that rollbacks the transaction. Example: await db.transaction(async (tx) => { if (condition) { tx.rollback(); } }).

Use except(query1, query2) or chain with .except(). Example: await db.select({ courseName: depA.projectsName }).from(depA).except(db.select({ courseName: depB.projectsName }).from(depB)). Import from the dialect module.

Values of undefined are ignored in the .set() method. To set a column to null, you must explicitly use null, not undefined.

Use .groupBy(column) with aggregate functions in select. Example: await db.select({ age: users.age, count: sqlcast(count(${users.id}) as int) }).from(users).groupBy(users.age). Import sql from 'drizzle-orm'.

Use db.$with('name').as(query). Example: const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result = await db.with(sq).select().from(sq). This generates: with sq as (select ... from users where id = 42) select ... from sq;

Use the alias() function. Example: import { alias } from 'drizzle-orm'; const parent = alias(user, 'parent'); const result = db.select().from(user).leftJoin(parent, eq(parent.id, user.parentId)).

No, currently the Column type has no .as() method. For column aliasing, you must use workarounds like sqlexpression.as('alias') or column.getSQL().mapWith(column.mapFromDriverValue).as('alias').

Use db.transaction(async (tx) => { ... }). Transactions automatically commit when the callback completes successfully. If an error is thrown, the transaction automatically rolls back.

Pass an array of objects to the .values() method. Example: await db.insert(users).values([{ name: 'John' }, { name: 'Jane' }, { name: 'Bob' }]). Each object represents a row to insert.

Use .limit(number) and .offset(number) methods. Example: await db.select().from(users).orderBy(asc(users.id)).limit(4).offset(4). The offset specifies the number of rows to skip, and limit specifies the number of rows to return.

Use sql.placeholder('name') and call .prepare('statementName'). Example: const p1 = db.select().from(customers).where(eq(customers.id, sql.placeholder('id'))).prepare('p1'). Execute with: await p1.execute({ id: 10 }).

Drizzle always outputs exactly one SQL query for relational queries, making it efficient for serverless databases. It avoids multiple joins and complex data mappings.

Use .onConflictDoUpdate({ target: column, set: updates }). Example: await db.insert(users).values({ id: 1, name: 'John' }).onConflictDoUpdate({ target: users.id, set: { name: 'John Updated' } }).

Use db.insert(table).values(object). Example: await db.insert(users).values({ name: 'Andrew' }). The values method accepts an object with keys matching column names.

The .returning() method is available for PostgreSQL and SQLite. MySQL does not provide returning support - you always have to make an extra select query. For MSSQL, use .output() instead of .returning().

If you call .where() multiple times without using .$dynamic(), only the last one gets applied. Drizzle has never supported combining multiple .where() calls automatically. Use and() or or() to combine multiple conditions.

1. Import approach: import { unionAll } from 'drizzle-orm/pg-core'; await unionAll(query1, query2). 2) Builder approach: await db.select().from(table1).unionAll(db.select().from(table2)). Both produce the same SQL UNION ALL statement.

With prepared statements, SQL concatenation happens once on the Drizzle ORM side, then the database driver can reuse precompiled binary SQL instead of parsing the query each time. This has extreme performance benefits on large SQL queries.

1. Callback syntax: where: (users, { eq }) => eq(users.id, 1). 2) Object-based syntax (newer): where: { ownerId: 1 }. Both can be used in findMany and findFirst.

Yes, Drizzle ORM supports savepoints with nested transactions API. You can nest transaction callbacks within each other.

MSSQL uses .output() instead of .returning(). Example: await db.insert(users).values({ name: 'Dan' }).output(). The syntax is otherwise similar to .returning().

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.

varchar({ length: 'max' }) for MSSQL only.

numeric({ precision: 100, scale: 20 }) where precision is the total number of digits and scale is the count of digits in the fractional part.

mode: 'number' can be used: bigserial({ mode: 'number' })

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.

255 characters, which is the limit imposed by MySQL.

A table must be defined with at least 1 column in Drizzle ORM.

.references(() => users.id, { onDelete: 'cascade' })

Use char for fixed-length data where all values are exactly the same length (e.g., country codes with length 2). Use varchar for fields with known maximum length where values vary (e.g., emails, usernames).

Virtual columns are computed during read operations, while stored columns are computed during write operations and persisted to disk.

In the table's second parameter callback: primaryKey({ columns: [table.userId, table.roleId] })

Use pgEnum.values with 'as const': export const moodValues = moodEnum.values as const. Then create a type with typeof moodValues[number].

char is fixed-length blank padded up to n characters, varchar is variable-length up to n characters, and text is unlimited variable-length with no length restriction.

They are essentially the same - both prevent deletion of a parent row if there are dependent rows in the child table. RESTRICT is included for compatibility reasons.

It names them as ${enumName}In${schemaName} to avoid collision of enums with same naming in different schemas.

export const statusEnum = pgEnum('status', ['pending', 'active', 'inactive']). The first parameter is the enum type name in PostgreSQL, the second is an array of string values.

For single column: text('name').unique('custom_name'). For multiple columns: unique('custom_name').on(t.name, t.state) in the table's second parameter callback.

mode: 'string' (default), mode: 'number', or mode: 'bigint'

text('column').generatedAlwaysAs((): SQL => sqlexpression, { mode: "stored" }) or { mode: "virtual" }

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

When a row in the parent table is deleted, all corresponding rows in the child table will also be deleted.

No, PostgreSQL only supports STORED generated columns. Generated columns in PostgreSQL are created with GENERATED ALWAYS AS and are STORED by default.

Drizzle ORM represents numeric/decimal values as strings by default to maintain precision and store values with huge precision/number of digits.

Set casing: 'snake_case' in the drizzle configuration during database initialization. This allows you to write camelCase in TypeScript while maintaining snake_case in the database.

MySQL doesn't have a native uuid column type. The recommended approach is to use varchar and generate UUIDs server-side with crypto.randomUUID().

Either explicitly type the reference callback using AnyPgColumn as return type: references((): AnyPgColumn => table.id), or use the foreignKey operator: foreignKey({ columns: [table.parentId], foreignColumns: [table.id] })

uuid().defaultRandom() or uuid().default(sqlgen_random_uuid())

No, relations do not affect the database schema in any way and do not create foreign keys implicitly. Foreign keys and relations are separate concepts in Drizzle.

smallserial (serial2, 2-byte auto-incrementing), serial (4-byte auto-incrementing), and bigserial (serial8, 8-byte auto-incrementing).

You can chain .primaryKey(), .notNull(), .unique(), .default(), .$defaultFn(), and constraint modifiers. Example: serial('id').primaryKey() or text('name').notNull().unique()

'cascade', 'restrict', 'no action' (default), 'set null', and 'set default'

You must export all the models defined in your schema files so that Drizzle-Kit can import them and use them in the migration diff process.

.default() sets a static default value, while .$defaultFn() allows you to generate defaults at runtime using functions (e.g., for uuid, cuid, cuid2). They are different names for the same functionality.

Either named imports like 'import { integer, pgTable, varchar } from "drizzle-orm/pg-core"' or namespace imports like 'import * as p from "drizzle-orm/pg-core"'

Use .array() after a column type: text('tags').array() for a text array, or integer('scores').array() for an integer array. For multi-dimensional arrays, chain .array() calls.

Use the schema option with an array of strings or glob patterns: schema: ["./user.sql.ts", "./post.sql.ts"] or schema: "./schema/.ts" or schema: "./src/**/.sql.ts"

GENERATED ALWAYS means the database always generates the value and you cannot override it. GENERATED BY DEFAULT means the database generates by default but you can provide your own value if needed.

No, the length parameter is optional. You can use varchar() without specifying a length, though behavior depends on the database.

By default, Drizzle uses the TypeScript key names for columns in database queries.

integer('id').generatedAlwaysAsIdentity() for GENERATED ALWAYS, or integer('id').generatedByDefaultAsIdentity() for GENERATED BY DEFAULT.

export const customSchema = pgSchema('custom'); then use customSchema.table('users', { id: integer() }) instead of pgTable.

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/libsql';

import { drizzle } from 'drizzle-orm/node-postgres';

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.

import { drizzle } from 'drizzle-orm/neon-http';

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.

You need to install: drizzle-orm and better-sqlite3.

You can access the underlying driver instance via db.$client if necessary.

1. Let Drizzle create the connection by passing connection options: drizzle({ connection: { url, ssl } }), or 2) Provide your own driver instance: drizzle({ client: yourClient }).

import { drizzle } from 'drizzle-orm/better-sqlite3';

import { drizzle } from 'drizzle-orm/vercel-postgres';

No, all 20+ supported database drivers are marked as "optional": true in the peerDependenciesMeta section, so you only need to install the specific driver you're using.

import { defineConfig } from 'drizzle-kit';

const db = drizzle({ connection: { url: process.env.DATABASE_URL, ssl: true } }); - Drizzle will create the driver instance internally.

Drizzle supports 20+ database drivers across PostgreSQL, MySQL, and SQLite database families.

import { drizzle } from 'drizzle-orm/bun-sqlite'; import { Database } from 'bun:sqlite'; const sqlite = new Database('sqlite.db'); const db = drizzle(sqlite);

Use the format 'file:<>' in the url field, such as: url: 'file:./example.db'

It's required to at least provide SQL dialect and schema path for Drizzle Kit to know how to generate migrations.

No, await client.connect() is typically NOT required. Drizzle and the underlying drivers handle connections automatically when you execute queries.

npm install drizzle-orm @neondatabase/serverless

Yes, you can use an array of specific file paths like: schema: ["./user.sql.ts", "./post.sql.ts"]

No, Drizzle is not doing any connection pool destroy/create actions - all connection pool management is handled by the underlying driver you choose to use with Drizzle.

You need to install: drizzle-orm and postgres.

Drizzle supports Neon (@neondatabase/serverless), Vercel Postgres (@vercel/postgres), and can connect via HTTP or WebSockets instead of TCP.

You need to install: drizzle-orm and pg. For development: drizzle-kit, typescript, @types/node, and @types/pg.

import { drizzle } from 'drizzle-orm/better-sqlite3'; import Database from 'better-sqlite3'; const sqlite = new Database('sqlite.db'); const db = drizzle({ client: sqlite });

By default, drizzle-kit push and drizzle-kit pull manage all tables in the public schema unless you configure tablesFilter to limit which tables are synced.

tablesFilter lets you specify glob-based table name filters to include or exclude specific tables from Drizzle Kit operations. Useful for multi-project setups. Example: tablesFilter: ["project1_*"] to only manage tables starting with project1_.

import { drizzle } from 'drizzle-orm/bun-sqlite';

import { drizzle } from 'drizzle-orm/mysql2';

You need to install: drizzle-orm and mysql2.

The dialect field accepts: "postgresql", "mysql", "sqlite", "turso", "singlestore", or "mssql".

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() for compile-time type protection. For example: json().$type<{ foo: string }>() for objects or json().$type<string[]>() for string arrays.

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

SQLite does not have a native boolean data type. Drizzle stores boolean values as 0 and 1 integer values in the database using integer columns with mode: 'boolean', while allowing you to operate with boolean values in your code.

No. For SQLite and MySQL, you cannot change the generated constraint type from virtual to stored, but you can change from stored to virtual.

CASCADE means that when a row in the parent table is deleted, all corresponding rows in the child table will also be deleted.

MySQL tinyint supports signed values from -128 to 127. With the unsigned option, it supports values from 0 to 255.

Precision is the total number of digits, and scale is the number of digits after the decimal point. For example, numeric(100, 20) has 100 total digits with 20 digits in the fractional part.

For multi-dimensional PostgreSQL arrays, use the .array().exactlyLength(size) syntax. Drizzle Kit tracks array dimensions when pulling schema from database, showing array_dimensions: 1 for single-dimensional arrays.

customType can be imported from 'drizzle-orm/pg-core', 'drizzle-orm/mysql-core', or 'drizzle-orm/sqlite-core' depending on your database dialect.

The available vector distance functions are: l2Distance, l1Distance, innerProduct, cosineDistance, hammingDistance, and jaccardDistance. All are importable from 'drizzle-orm'.

MySQL enums are defined using mysqlEnum with the syntax: mysqlEnum('column_name', ['value1', 'value2', 'value3']). If you provide a TypeScript enum, all types will be inferred as that enum.

Custom types require: (1) dataType - a function that returns the SQL data type string, (2) toDriver - optional mapping function between user input and database driver, (3) fromDriver - optional mapping function for transforming data returned by driver to desired output format.

The interval type supports fields and precision parameters. For example: interval({ fields: 'day' }) or interval({ fields: 'month', precision: 6 }).

The four modes are: 'number' (default), 'boolean', 'timestamp', and 'timestamp_ms'. The 'boolean' mode stores values as 0 and 1 in the database while allowing boolean operations in code. The 'timestamp' mode uses seconds, while 'timestamp_ms' uses milliseconds.

If you're expecting values above 2^31 but below 2^53, you can utilize mode: 'number' to work with JavaScript numbers instead of bigints.

PostgreSQL data types can be converted to arrays using the .array() method. For example: text('tags').array() or integer('numbers').array().

MySQL mediumint with the unsigned option supports values from 0 to 16,777,215 (24-bit integer).

The available actions are: 'cascade', 'restrict', 'no action', 'set null', and 'set default'. These can be used with onDelete and onUpdate options.

Use the sql operator with '{}' or ARRAY[] syntax. For example: text('tags').array().notNull().default(sql'{}'::text[]) or integer('numbers').array().default(sqlARRAY[]::integer[]).

No. PostgreSQL's real (4 bytes) and double precision (8 bytes) floating-point types do not support precision and scale parameters. For precise numeric values with precision and scale, use the numeric() type instead.

PostgreSQL generated columns are stored by default when using .generatedAlwaysAs().

No. The enum: ["value1", "value2"] configuration only provides TypeScript type inference and does not check runtime values.

The two modes are 'stored' and 'virtual'. Stored columns are physically stored in the database, while virtual columns are computed on-the-fly when queried.

bigserial (also known as serial8) is an auto-incrementing 8-bytes integer in PostgreSQL.

The .defaultNow() method uses PostgreSQL's now() function to set the current date and time as the default value.

smallserial is an auto-incrementing 2-bytes integer in PostgreSQL.

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

Use the --config flag: npx drizzle-kit push --config=drizzle-dev.config.ts

npm install drizzle-orm better-sqlite3
npm install -D @types/better-sqlite3
npm install -D drizzle-kit

The default migrations tracking table name is 'drizzle_migrations'.

npm i drizzle-orm pg
npm i -D drizzle-kit @types/pg

npm install drizzle-orm mysql2
npm install -D drizzle-kit

Use the --verbose flag: npx drizzle-kit studio --verbose

npx drizzle-kit push

import { drizzle } from 'drizzle-orm/better-sqlite3';

Yes, drizzle-orm includes TypeScript type definitions in the package, so you don't need to install separate @types/drizzle-orm packages.

import { defineConfig } from "drizzle-kit";

The three valid dialect values are: 'postgresql', 'mysql', and 'sqlite'.

import { pgTable } from 'drizzle-orm/pg-core';

You also import column types from the same module, e.g., import { pgTable, serial, text, varchar } from 'drizzle-orm/pg-core';

import { drizzle } from 'drizzle-orm/mysql2';

The default output directory is './drizzle'.

Drizzle ORM supports 20+ database drivers across three database families (PostgreSQL, MySQL, and SQLite).

The three required fields are: 1) dialect (the SQL dialect: 'postgresql', 'mysql', or 'sqlite'), 2) schema (path to your schema file), and 3) out (output directory for migrations, defaults to './drizzle').

Yes, drizzle-orm is tree-shakeable, allowing bundlers to eliminate unused code.

Use the --port flag: npx drizzle-kit studio --port=3000

npm i drizzle-orm postgres
npm i -D drizzle-kit

drizzle-orm is approximately 7.4kb minified+gzipped.

npx drizzle-kit generate

Drizzle Studio server runs on 127.0.0.1:4983 by default, and is accessed via https://local.drizzle.studio.

import from 'drizzle-orm/sqlite-core', for example: import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core';

npm install drizzle-orm @libsql/client
npm install -D drizzle-kit

drizzle.config.ts located in the project's root folder.

drizzle-orm has exactly 0 runtime dependencies. It uses optional peer dependencies for database drivers instead.

Drizzle ORM works in Node.js, Bun, Deno, Cloudflare Workers, Supabase functions, any Edge runtime, and even in browsers.

import { drizzle } from 'drizzle-orm/node-postgres';

import from 'drizzle-orm/mysql-core', for example: import { mysqlTable, serial, text, varchar } from 'drizzle-orm/mysql-core';

drizzle-kit is installed as a dev dependency (-D flag) to keep your production build lightweight, as it's a CLI tool for managing migrations and development tasks.

Yes, Drizzle Kit can be configured via CLI params in addition to the drizzle.config.ts configuration file.

The default prefix format is 'timestamp', which creates migration files with a timestamp prefix.

npx drizzle-kit studio

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() method on column builders, for example: id: int().$type().primaryKey() where UserId is a branded type.

Use typeof users.$inferInsert or InferInsertModel (imported from 'drizzle-orm').

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

An optional callback function that receives the table and returns an array or object of constraints like indexes, foreign keys, primary keys, and check constraints.

First parameter: a string representing the table name in the database. Second parameter: an object defining the columns of the table.

No, tables defined with custom schemas (using pgSchema or mysqlSchema) won't be detected by drizzle-kit or included in the migration flow automatically.

Use uniqueIndex() in the table callback: (table) => ({ emailIdx: uniqueIndex('email_idx').on(table.email) }) or for multiple columns: uniqueIndex('email_idx').on(table.email, table.username).

Use foreignKey() in the callback: (table) => [foreignKey({ columns: [table.parentId], foreignColumns: [table.id], name: 'custom_fk' })].

No, SQLite does not have a concept of schemas, so you can only define tables within a single SQLite file context.

Use .$defaultFn() or .$default() (aliases for the same function): varchar({ length: 128 }).$defaultFn(() => createId()).

A table can have only ONE primary key, which can consist of single or multiple columns (composite primary key).

getTableName() imported from 'drizzle-orm', which accepts a table object and returns its name as a string.

Use the .default() method: varchar('name', { length: 255 }).default('default_name').

Use check() in the table callback with a constraint name and SQL expression: (table) => [check('age_check1', sql${table.age} > 21)].

Use the 'casing' option during Drizzle database initialization: drizzle({ connection: process.env.DATABASE_URL, casing: 'snake_case' }).

Use primaryKey() in the table's constraint callback: (table) => ({ cpk: primaryKey({ columns: [table.id, table.name] }) }) or return it in an array.

Use index() in the table callback: (table) => ({ emailIdx: index('email_idx').on(table.email) }).

Use pgSchema() to create a schema instance, then call .table() on it: const customSchema = pgSchema('custom'); const users = customSchema.table('users', { id: integer() });

getTableConfig() imported from database-specific core modules (e.g., 'drizzle-orm/pg-core'), which returns an object with columns, indexes, foreignKeys, checks, primaryKeys, name, and schema properties.

Use mysqlSchema() to create a schema instance, then call .table() on it: const customSchema = mysqlSchema('custom'); const users = customSchema.table('users', { id: int() });

.onDelete() and .onUpdate() methods, for example: foreignKey({...}).onDelete('cascade').onUpdate('cascade').

pgTable() for PostgreSQL (from 'drizzle-orm/pg-core'), mysqlTable() for MySQL (from 'drizzle-orm/mysql-core'), and sqliteTable() for SQLite (from 'drizzle-orm/sqlite-core').

Pass an object with the length property: varchar({ length: 256 }) or varchar('column_name', { length: 256 }).

No, check constraints can only be added at the end of the table definition in the constraints callback, not inline with column definitions.

Chain .primaryKey() on the column definition: id: serial('id').primaryKey().

Use a column alias by passing the database column name as a parameter: firstName: varchar('first_name') where 'firstName' is used in TypeScript and 'first_name' in the database.

Use typeof users.$inferSelect or InferSelectModel (imported from 'drizzle-orm').

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 two modes are 'date' (infers as Date object) and 'string' (infers as string). Date mode is 10-15% faster than string mode.

.default() sets a database-level default value that gets added to the SQL schema using the DEFAULT clause (static value). .$default() and .$defaultFn() are aliases for the same function that generate defaults at runtime in your application code - this value does not affect drizzle-kit behavior, it is only used at runtime in drizzle-orm.

.defaultNow() generates: column_name timestamp default now()

You can specify: name (custom sequence name), startWith (starting value), increment (increment value), minValue (minimum value), maxValue (maximum value), and cache (cache size for the sequence).

.defaultNow() generates: column_name timestamp DEFAULT (now())

Column modifiers modify the actual column object instead of returning a new object. This means reusing column definitions across tables and applying modifiers will mutate the original object, affecting all tables that use it.

No, the length parameter is optional according to PostgreSQL, MySQL, and MSSQL docs. You can define varchar() without a length or varchar({ length: 256 }) with a specific length.

String mode does not perform any mappings for you - it was added to provide developers with the possibility to handle dates and date mappings themselves. Values are treated as strings in the application but stored as timestamps in the database.

text('column_name').generatedAlwaysAs((): SQL => sqlexpression, { mode: 'stored' }) which generates SQL: column_name text GENERATED ALWAYS AS (expression) STORED

.$type() specifies type inference for JSON objects - it provides compile time protection for default values, insert and select schemas. It won't check runtime values, only TypeScript types. Example: json().$type<{ foo: string }>()

In the table callback: (self) => [foreignKey({ columns: [self.parentId], foreignColumns: [self.id] }).onDelete('cascade')]

Use unique().on() in the table callback: (t) => [unique().on(t.id, t.name)] or with a custom name: (t) => [unique('custom_name').on(t.id, t.name)]

The valid values for UpdateDeleteAction are: 'cascade', 'restrict', 'no action', 'set null', and 'set default'.

No. Generated columns cannot specify default values. This is a fundamental limitation of SQL generated columns across all databases.

No, you can't change the generated constraint type from virtual to stored. However, you can change from stored to virtual.

integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }) which generates: id integer PRIMARY KEY AUTOINCREMENT NOT NULL

int('id').autoincrement().primaryKey() - note that .autoincrement() is a method call specific to MySQL.

Precision 3 (millisecond precision) provides the best balance between accuracy and storage efficiency. Example: timestamp({ precision: 3 })

All column types are automatically set as nullable by default unless specifically marked with .notNull(). If there is no explicit DEFAULT clause, the default value of the column is NULL.

The migrate() function requires the migrationsFolder parameter specifying the path to the folder containing migration files (e.g., { migrationsFolder: './drizzle' }).