Drizzle ORM

Drizzle is a lightweight, headless TypeScript ORM designed for NodeJS, Bun, Deno, Cloudflare Workers, Edge runtimes, and browsers. It is tree-shakeable with zero dependencies and has a minimal footprint (~7.4kb minified+gzipped).

Key Features:

• Database Support: Supports all PostgreSQL, MySQL, and SQLite databases, including serverless providers like Turso, Neon, Xata, PlanetScale, Cloudflare D1, FlyIO LiteFS, Vercel Postgres, Supabase, and AWS Data API.
• Serverless-Ready: Designed to work in any major JavaScript runtime without requiring data proxies or Rust binaries.
• Querying Models: Allows you to declare SQL schemas and build both relational queries and SQL-like queries while maintaining high type-safety.

Use drizzle-zod to automatically generate Zod schemas from your Drizzle ORM table, view, or enum definitions. This allows you to validate API requests (inserts/updates) and API responses (selects) using the same source of truth as your database schema.

Supported dialects include:

• PostgreSQL
• MySQL
• SQLite

import { pgTable, serial, text } from 'drizzle-orm/pg-core';
import { createInsertSchema, createSelectSchema } from 'drizzle-zod';

const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

// Create schemas
const insertUserSchema = createInsertSchema(users);
const selectUserSchema = createSelectSchema(users);

drizzle-typebox is a plugin for Drizzle ORM that enables the generation of @sinclair/typebox schemas directly from your Drizzle ORM table, view, or enum definitions.

Key Features:

• Generate select schemas for tables, views, and enums.
• Generate insert and update schemas for tables.
• Full support for PostgreSQL, MySQL, and SQLite dialects.

By default, Drizzle query builders enforce SQL-like constraints where most methods (like .where()) can only be invoked once per statement. This is ideal for static queries but prevents dynamic query construction (e.g., passing a query to a function that adds clauses).

To enable dynamic mode, call the .$dynamic() method on your query builder. This removes the restriction on multiple method calls for the same clause.

function withPagination<T extends PgSelect>(qb: T, page: number, pageSize: number = 10) {
  return qb.limit(pageSize).offset(page * pageSize);
}

const query = db.select().from(users).where(eq(users.id, 1));
// query.$dynamic() is required before passing to functions that modify the builder
const dynamicQuery = query.$dynamic();
withPagination(dynamicQuery, 1); // ✅ OK

Starting from version 0.44.0, Drizzle introduces DrizzleQueryError. This error type wraps errors from database drivers to provide enhanced debugging information, including:

1. A proper stack trace identifying the exact Drizzle query that failed.
2. The generated SQL string and its associated parameters.
3. The original stack trace from the underlying database driver.

The Relational Query Builder (RQB) allows you to perform complex queries with automatic relation mapping, column inclusion/exclusion, and custom where conditions. Drizzle generates a single optimized SQL query for these operations and supports Prepared Statements.

To use RQB, you must first define your relations using the relations function in your schema and then pass the complete schema object to the drizzle initialization function.

import * as schema from './schema';
import { drizzle } from 'drizzle-orm/...';

const db = drizzle(client, { schema });

// Querying with relations
const posts = await db.query.posts.findMany({
	columns: {
		id: true,
		content: true,
	},
	with: {
		comments: true,
	}
});

Drizzle ORM uses a two-class pattern to implement database column types. This pattern allows for a fluent API when defining schemas and ensures correct behavior during query generation and migrations.

1. ColumnBuilder: Responsible for the schema definition phase. It stores the TypeScript return type (TData) and provides a build method that returns the actual Column instance.

   • For PostgreSQL: use PgColumnBuilder.
   • For MySQL: use MySqlColumnBuilder.
   • For SQLite: use SQLiteColumnBuilder.

2. Column: Represents the column itself. It is used during query generation, migration mapping, and data transformation.

   • For PostgreSQL: use PgColumn.
   • For MySQL: use MySqlColumn.
   • For SQLite: use SQLiteColumn.

drizzle-seed uses a seedable pseudorandom number generator (pRNG) to generate realistic fake data.

Deterministic Data Generation means that providing the same initial seed number will always produce the exact same sequence of fake data. This is useful for:

• Consistency: Ensuring tests run on the same data every time.
• Debugging: Reproducing bugs with a predictable data set.
• Collaboration: Allowing team members to share a seed number to work with identical data sets.

When modifying PostgreSQL enums—specifically dropping an enum value, reordering values, or changing a column's data type from an enum to another type—drizzle-kit performs a multi-step migration to ensure data integrity and correct default expressions.

In version 0.31.0 and later, the migration process follows these steps:

1. Change the column data types from the enum to text.
2. Set the default value using the ::text expression.
3. Drop the old enum type.
4. Add the new enum type.
5. Change the column data types back to the new enum type.
6. Set the default value using the ::<new_enum> expression.

This ensures that default expressions are correctly updated when the underlying data type changes.

Drizzle provides a suite of tools to enhance the developer experience beyond the core ORM:

• Drizzle Kit: A powerful CLI companion used for managing migrations. It can automatically generate SQL migration files from your schema or apply schema changes directly to your database.
• Drizzle Studio: A GUI tool that allows you to effortlessly browse and manipulate data within your database.
• Schema Validation Plugins: Drizzle integrates with popular validation libraries to generate schemas from your Drizzle ORM definitions:
  • drizzle-zod: For Zod schemas.
  • drizzle-typebox: For TypeBox schemas.
  • drizzle-valibot: For Valibot schemas.
  • drizzle-arktype: For Arktype schemas.