0
# Drizzle ORM
1

2
Drizzle ORM is a modern, headless TypeScript ORM for Node.js, TypeScript, and JavaScript that provides a thin typed layer on top of SQL. It supports PostgreSQL, MySQL, and SQLite databases with comprehensive type safety, zero dependencies, and excellent performance across multiple runtime environments.
3

4
## Package Information
5

6
- **Package Name**: drizzle-orm
7
- **Package Type**: npm
8
- **Language**: TypeScript
9
- **Installation**: `npm install drizzle-orm`
10

11
## Core Imports
12

13
```typescript
14
import { drizzle } from "drizzle-orm/node-postgres";
15
import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";
16
import { eq, and, or } from "drizzle-orm";
17
```
18

19
For different database providers:
20

21
```typescript
22
// PostgreSQL
23
import { drizzle } from "drizzle-orm/node-postgres";
24
import { drizzle } from "drizzle-orm/postgres-js";
25
import { drizzle } from "drizzle-orm/neon-http";
26
import { drizzle } from "drizzle-orm/neon-serverless";
27
import { drizzle } from "drizzle-orm/vercel-postgres";
28
import { drizzle } from "drizzle-orm/supabase";
29
import { drizzle } from "drizzle-orm/aws-data-api/pg";
30
import { drizzle } from "drizzle-orm/pglite";
31

32
// MySQL
33
import { drizzle } from "drizzle-orm/mysql2";
34
import { drizzle } from "drizzle-orm/planetscale-serverless";
35
import { drizzle } from "drizzle-orm/tidb-serverless";
36

37
// SQLite
38
import { drizzle } from "drizzle-orm/better-sqlite3";
39
import { drizzle } from "drizzle-orm/d1";
40
import { drizzle } from "drizzle-orm/libsql";
41
import { drizzle } from "drizzle-orm/bun-sqlite";
42
import { drizzle } from "drizzle-orm/expo-sqlite";
43
import { drizzle } from "drizzle-orm/op-sqlite";
44
import { drizzle } from "drizzle-orm/sql-js";
45

46
// Proxy drivers
47
import { drizzle } from "drizzle-orm/pg-proxy";
48
import { drizzle } from "drizzle-orm/mysql-proxy";
49
import { drizzle } from "drizzle-orm/sqlite-proxy";
50
```
51

52
## Basic Usage
53

54
```typescript
55
import { drizzle } from "drizzle-orm/node-postgres";
56
import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";
57
import { eq } from "drizzle-orm";
58
import { Pool } from "pg";
59

60
// Define schema
61
const users = pgTable("users", {
62
  id: serial("id").primaryKey(),
63
  name: text("name").notNull(),
64
  email: varchar("email", { length: 255 }).unique(),
65
});
66

67
// Create database connection
68
const pool = new Pool({
69
  connectionString: "postgresql://username:password@localhost:5432/db"
70
});
71
const db = drizzle(pool);
72

73
// Query data
74
const allUsers = await db.select().from(users);
75
const user = await db.select().from(users).where(eq(users.id, 1));
76

77
// Insert data
78
const newUser = await db.insert(users).values({
79
  name: "John Doe",
80
  email: "john@example.com"
81
}).returning();
82

83
// Update data
84
await db.update(users)
85
  .set({ name: "Jane Doe" })
86
  .where(eq(users.id, 1));
87
```
88

89
## Architecture
90

91
Drizzle ORM is built around several key components:
92

93
- **Schema Definition System**: TypeScript-first schema definitions with `pgTable`, `mysqlTable`, `sqliteTable` for type-safe table structures
94
- **Query Builder**: Chainable, type-safe query builders for all SQL operations (select, insert, update, delete)
95
- **Database Drivers**: Dedicated driver adapters for 20+ database providers across multiple runtimes including serverless, edge, and mobile environments
96
- **Type Safety**: Complete TypeScript integration with inferred types throughout the query building process
97
- **SQL Expression System**: Comprehensive SQL expression building with operators, functions, and custom SQL
98
- **Relational Queries**: Simplified relational data fetching with automatic joins and nested results
99

100
## Capabilities
101

102
### Schema Definition
103

104
Comprehensive schema definition system for creating type-safe database tables, columns, indexes, and constraints with support for PostgreSQL, MySQL, and SQLite-specific features.
105

106
```typescript { .api }
107
function pgTable<TTableName extends string>(
108
  name: TTableName,
109
  columns: Record<string, AnyPgColumn>
110
): PgTable<{ name: TTableName; columns: typeof columns; }>;
111

112
function mysqlTable<TTableName extends string>(
113
  name: TTableName,
114
  columns: Record<string, AnyMySqlColumn>
115
): MySqlTable<{ name: TTableName; columns: typeof columns; }>;
116

117
function sqliteTable<TTableName extends string>(
118
  name: TTableName,
119
  columns: Record<string, AnySQLiteColumn>
120
): SQLiteTable<{ name: TTableName; columns: typeof columns; }>;
121
```
122

123
[Schema Definition](./schema-definition.md)
124

125
### Database Connections
126

127
Database connection management and driver configuration for all supported database providers with connection pooling, transactions, and runtime-specific optimizations.
128

129
```typescript { .api }
130
function drizzle<TSchema extends Record<string, unknown>>(
131
  client: NodePgClient,
132
  config?: DrizzleConfig<TSchema>
133
): NodePgDatabase<TSchema>;
134

135
interface DrizzleConfig<TSchema extends Record<string, unknown>> {
136
  schema?: TSchema;
137
  logger?: Logger;
138
  cache?: Cache;
139
}
140
```
141

142
[Database Connections](./database-connections.md)
143

144
### Query Building
145

146
Type-safe query builders for all SQL operations with comprehensive support for complex queries, joins, subqueries, and database-specific features.
147

148
```typescript { .api }
149
interface PgSelect<T> {
150
  from<TTable extends AnyTable>(table: TTable): PgSelectWithTables<T, [TTable]>;
151
  where(condition: SQL): this;
152
  orderBy(...columns: (AnyColumn | SQL)[]): this;
153
  limit(limit: number): this;
154
  offset(offset: number): this;
155
}
156

157
interface PgInsert<T> {
158
  values(values: T | T[]): this;
159
  returning<TSelection>(selection?: TSelection): PgInsertReturning<TSelection>;
160
  onConflictDoNothing(): this;
161
  onConflictDoUpdate(config: { target: AnyColumn[], set: any }): this;
162
}
163
```
164

165
[Query Building](./query-building.md)
166

167
### Advanced Features
168

169
Advanced ORM capabilities including schema migrations, query result caching, batch operations, relational queries, vector operations, and custom SQL expressions.
170

171
```typescript { .api }
172
function migrate(
173
  db: AnyPgDatabase,
174
  config: MigrationConfig
175
): Promise<void>;
176

177
interface MigrationConfig {
178
  migrationsFolder: string;
179
  migrationsTable?: string;
180
  migrationsSchema?: string;
181
}
182

183
function sql<T>(
184
  strings: TemplateStringsArray,
185
  ...values: any[]
186
): SQL<T>;
187

188
function batch<T extends AnyDatabase>(db: T): BatchBuilder<T>;
189

190
function relations<T extends Record<string, AnyTable>>(
191
  table: T,
192
  config: RelationsConfig<T>
193
): Relations<T>;
194
```
195

196
[Advanced Features](./advanced-features.md)
197

198
### Relational Queries
199

200
Powerful relational query system with automatic joins, nested results, and type-safe relationship definitions for complex data fetching patterns.
201

202
```typescript { .api }
203
interface RelationalQueryBuilder<TSchema> {
204
  [K in keyof TSchema]: TSchema[K] extends AnyTable
205
    ? TableRelationalQuery<TSchema[K], TSchema>
206
    : never;
207
}
208

209
interface TableRelationalQuery<T extends AnyTable, TSchema> {
210
  findFirst(config?: RelationalQueryConfig<T, TSchema>): Promise<InferSelectModel<T> | undefined>;
211
  findMany(config?: RelationalQueryConfig<T, TSchema>): Promise<InferSelectModel<T>[]>;
212
}
213
```
214

215
[Relational Queries](./relational-queries.md)
216

217
## Types
218

219
```typescript { .api }
220
interface TableConfig<TColumn extends Column = Column<any>> {
221
  name: string;
222
  schema: string | undefined;
223
  columns: Record<string, TColumn>;
224
  dialect: string;
225
}
226

227
interface ColumnBaseConfig<TDataType extends ColumnDataType, TColumnType extends string> {
228
  name: string;
229
  dataType: TDataType;
230
  columnType: TColumnType;
231
  data: any;
232
  driverParam: any;
233
  notNull: boolean;
234
  hasDefault: boolean;
235
  enumValues: any[];
236
}
237

238
type ColumnDataType = 'string' | 'number' | 'boolean' | 'date' | 'json' | 'buffer' | 'array' | 'bigint' | 'custom';
239

240
type Chunk = string | Table | Column | SQL;
241

242
interface Table<T extends TableConfig = TableConfig> extends SQLWrapper {
243
  readonly [Table.Symbol.Name]: T['name'];
244
  readonly [Table.Symbol.Schema]: T['schema'];
245
  readonly [Table.Symbol.Columns]: T['columns'];
246
}
247

248
interface Column<T extends ColumnBaseConfig<ColumnDataType, string> = ColumnBaseConfig<ColumnDataType, string>> extends SQLWrapper {
249
  readonly name: T['name'];
250
  readonly dataType: T['dataType'];
251
  readonly columnType: T['columnType'];
252
  readonly notNull: T['notNull'];
253
  readonly hasDefault: T['hasDefault'];
254
}
255

256
interface SQL<T = unknown> extends SQLWrapper {
257
  readonly queryChunks: Chunk[];
258
  getSQL(): SQL<T>;
259
}
260

261
interface SQLWrapper {
262
  getSQL(): SQL;
263
}
264

265
type AnyColumn = Column<any>;
266
type AnyTable = Table<any>;
267
type AnyPgColumn = Column<ColumnBaseConfig<any, 'PgColumn'>>;
268
type AnyMySqlColumn = Column<ColumnBaseConfig<any, 'MySqlColumn'>>;
269
type AnySQLiteColumn = Column<ColumnBaseConfig<any, 'SQLiteColumn'>>;
270

271
// Database-specific table types
272
interface PgTable<T extends TableConfig = TableConfig> extends Table<T> {}
273
interface MySqlTable<T extends TableConfig = TableConfig> extends Table<T> {}
274
interface SQLiteTable<T extends TableConfig = TableConfig> extends Table<T> {}
275

276
// Database connection types
277
type NodePgClient = any; // pg.Pool | pg.Client
278
type MySqlClient = any; // mysql2.Connection | mysql2.Pool
279
type SQLiteClient = any; // Database instance
280

281
// Database instance types
282
interface NodePgDatabase<TSchema extends Record<string, unknown> = Record<string, never>> {
283
  select(): any;
284
  insert<T extends AnyTable>(table: T): any;
285
  update<T extends AnyTable>(table: T): any;
286
  delete<T extends AnyTable>(table: T): any;
287
  query: TSchema extends Record<string, never> ? never : any;
288
}
289

290
interface MySqlDatabase<TSchema extends Record<string, unknown> = Record<string, never>> {
291
  select(): any;
292
  insert<T extends AnyTable>(table: T): any;
293
  update<T extends AnyTable>(table: T): any;
294
  delete<T extends AnyTable>(table: T): any;
295
  query: TSchema extends Record<string, never> ? never : any;
296
}
297

298
interface SQLiteDatabase<TSchema extends Record<string, unknown> = Record<string, never>> {
299
  select(): any;
300
  insert<T extends AnyTable>(table: T): any;
301
  update<T extends AnyTable>(table: T): any;
302
  delete<T extends AnyTable>(table: T): any;
303
  query: TSchema extends Record<string, never> ? never : any;
304
}
305

306
// Configuration types
307
interface DrizzleConfig<TSchema extends Record<string, unknown>> {
308
  schema?: TSchema;
309
  logger?: Logger | boolean;
310
  cache?: Cache;
311
  casing?: any;
312
}
313

314
interface Logger {
315
  logQuery(query: string, params: unknown[]): void;
316
}
317

318
interface Cache {
319
  get<T>(key: string): Promise<T | null>;
320
  set(key: string, value: unknown, ttl?: number): Promise<void>;
321
  delete(key: string): Promise<void>;
322
  clear(): Promise<void>;
323
}
324

325
interface MigrationConfig {
326
  migrationsFolder: string;
327
  migrationsTable?: string;
328
  migrationsSchema?: string;
329
}
330
```