0
# Advanced Features
1

2
Drizzle ORM provides advanced capabilities including schema migrations, query result caching, batch operations, OpenTelemetry tracing, and custom SQL expressions for sophisticated database operations.
3

4
## Schema Migrations
5

6
### Migration Configuration
7

8
```typescript { .api }
9
interface MigrationConfig {
10
  migrationsFolder: string;
11
  migrationsTable?: string;
12
  migrationsSchema?: string;
13
}
14

15
interface MigrationMeta {
16
  sql: string[];
17
  folderMillis: number;
18
  hash: string;
19
  bps: boolean;
20
}
21
```
22

23
### Migration Functions
24

25
```typescript { .api }
26
function readMigrationFiles(config: MigrationConfig): MigrationMeta[];
27

28
function migrate<TDatabase extends Database>(
29
  db: TDatabase,
30
  config: MigrationConfig
31
): Promise<void>;
32
```
33

34
### Database-Specific Migration Support
35

36
```typescript { .api }
37
// PostgreSQL
38
function migrate(db: NodePgDatabase, config: MigrationConfig): Promise<void>;
39
function migrate(db: PostgresJsDatabase, config: MigrationConfig): Promise<void>;
40

41
// MySQL
42
function migrate(db: MySql2Database, config: MigrationConfig): Promise<void>;
43
function migrate(db: PlanetScaleDatabase, config: MigrationConfig): Promise<void>;
44

45
// SQLite
46
function migrate(db: BetterSQLite3Database, config: MigrationConfig): Promise<void>;
47
function migrate(db: LibSQLDatabase, config: MigrationConfig): Promise<void>;
48
```
49

50
## Query Result Caching
51

52
### Cache Interface
53

54
```typescript { .api }
55
interface Cache {
56
  get<T>(key: string): Promise<T | null>;
57
  set(key: string, value: unknown, ttl?: number): Promise<void>;
58
  delete(key: string): Promise<void>;
59
  clear(): Promise<void>;
60
  onMutate?: () => Promise<void>;
61
}
62
```
63

64
### Upstash Redis Cache Implementation
65

66
```typescript { .api }
67
function createCache(config: {
68
  redis: Redis;
69
  keyPrefix?: string;
70
  ttl?: number;
71
}): Cache;
72
```
73

74
### Cache Configuration
75

76
```typescript
77
import { createCache } from "drizzle-orm/cache/upstash";
78
import { Redis } from "@upstash/redis";
79

80
const redis = new Redis({
81
  url: "UPSTASH_REDIS_REST_URL",
82
  token: "UPSTASH_REDIS_REST_TOKEN"
83
});
84

85
const cache = createCache({
86
  redis,
87
  keyPrefix: "drizzle-cache:",
88
  ttl: 300 // 5 minutes
89
});
90

91
const db = drizzle(client, { cache });
92
```
93

94
## Batch Operations
95

96
### Batch Interface
97

98
```typescript { .api }
99
interface Database {
100
  batch<T extends readonly [...QueryBuilder[]]>(
101
    queries: T
102
  ): Promise<BatchResult<T>>;
103
}
104

105
type BatchResult<T extends readonly QueryBuilder[]> = {
106
  [K in keyof T]: T[K] extends QueryBuilder<infer U> ? U : never;
107
};
108
```
109

110
### Batch Execution
111

112
```typescript
113
const batchResult = await db.batch([
114
  db.select().from(users).where(eq(users.id, 1)),
115
  db.insert(posts).values({ title: "New Post", authorId: 1 }),
116
  db.update(users).set({ lastLogin: new Date() }).where(eq(users.id, 1))
117
]);
118

119
const [selectedUser, insertResult, updateResult] = batchResult;
120
```
121

122
## OpenTelemetry Tracing
123

124
### Tracing Configuration
125

126
```typescript { .api }
127
interface TracingConfig {
128
  enabled?: boolean;
129
  spanName?: string;
130
  attributes?: Record<string, string | number | boolean>;
131
}
132
```
133

134
### Automatic Tracing
135

136
When OpenTelemetry is configured in your application, Drizzle ORM automatically creates spans for database operations:
137

138
```typescript
139
import { trace } from "@opentelemetry/api";
140
import { drizzle } from "drizzle-orm/node-postgres";
141

142
// Tracing is automatically enabled when OpenTelemetry is present
143
const db = drizzle(client);
144

145
// Each query will create a span
146
const users = await db.select().from(usersTable); // Creates span "drizzle:select"
147
```
148

149
## Custom Column Types
150

151
### Creating Custom Types
152

153
```typescript { .api }
154
function customType<T>(config: {
155
  dataType: () => string;
156
  toDriver: (value: T) => unknown;
157
  fromDriver?: (value: unknown) => T;
158
}): CustomTypeBuilder<T>;
159

160
interface CustomTypeBuilder<T> {
161
  (name: string): CustomColumn<T>;
162
}
163

164
interface CustomColumn<T> extends Column<ColumnBaseConfig<T, 'custom'>> {
165
  getSQLType(): string;
166
}
167
```
168

169
### Custom Type Examples
170

171
```typescript
172
// PostgreSQL Point type
173
const point = customType<{ x: number; y: number }>({
174
  dataType() {
175
    return 'point';
176
  },
177
  toDriver(value) {
178
    return `(${value.x},${value.y})`;
179
  },
180
  fromDriver(value) {
181
    const [x, y] = (value as string).slice(1, -1).split(',').map(Number);
182
    return { x, y };
183
  },
184
});
185

186
// UUID type with validation
187
const uuid = customType<string>({
188
  dataType() {
189
    return 'uuid';
190
  },
191
  toDriver(value) {
192
    if (!/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(value)) {
193
      throw new Error('Invalid UUID format');
194
    }
195
    return value;
196
  },
197
});
198
```
199

200
## Database Introspection
201

202
### Schema Introspection
203

204
```typescript { .api }
205
interface Database {
206
  getTableData(): Promise<TableData[]>;
207
  getTableStructure(tableName: string): Promise<TableStructure>;
208
}
209

210
interface TableData {
211
  name: string;
212
  schema?: string;
213
  columns: ColumnData[];
214
  indexes: IndexData[];
215
  foreignKeys: ForeignKeyData[];
216
}
217
```
218

219
## Connection Management
220

221
### Connection Pooling Configuration
222

223
```typescript
224
// PostgreSQL with advanced pool configuration
225
const pool = new Pool({
226
  connectionString: "postgresql://...",
227
  max: 20,
228
  idleTimeoutMillis: 30000,
229
  connectionTimeoutMillis: 2000,
230
  allowExitOnIdle: true,
231
});
232

233
// MySQL with connection limits
234
const pool = mysql.createPool({
235
  host: "localhost",
236
  user: "root",
237
  password: "password",
238
  database: "test",
239
  connectionLimit: 10,
240
  acquireTimeout: 60000,
241
  timeout: 60000,
242
});
243
```
244

245
### Connection Events and Monitoring
246

247
```typescript
248
// Monitor connection events
249
pool.on('connect', (client) => {
250
  console.log('New client connected');
251
});
252

253
pool.on('error', (err) => {
254
  console.error('Database pool error:', err);
255
});
256
```
257

258
## Performance Optimization
259

260
### Query Performance
261

262
```typescript { .api }
263
interface SelectBuilder {
264
  // Query hints for optimization
265
  $dynamic(): DynamicSelectBuilder;
266
}
267

268
interface DynamicSelectBuilder {
269
  where(condition: SQL | undefined): this;
270
  orderBy(...columns: (AnyColumn | SQL)[]): this;
271
}
272
```
273

274
### Prepared Statement Caching
275

276
```typescript
277
// Named prepared statements for reuse
278
const getUserById = db
279
  .select()
280
  .from(users)
281
  .where(eq(users.id, placeholder("id")))
282
  .prepare("getUserById");
283

284
// Reuse across multiple executions
285
const user1 = await getUserById.execute({ id: 1 });
286
const user2 = await getUserById.execute({ id: 2 });
287
```
288

289
## Error Handling
290

291
### Custom Error Classes
292

293
```typescript { .api }
294
class DrizzleError extends Error {
295
  readonly cause?: unknown;
296
  constructor(message: string, cause?: unknown);
297
}
298

299
class TransactionRollbackError extends DrizzleError {
300
  constructor(cause?: unknown);
301
}
302

303
class DriverError extends DrizzleError {
304
  constructor(message: string, cause?: unknown);
305
}
306
```
307

308
### Error Handling Patterns
309

310
```typescript
311
import { DrizzleError, TransactionRollbackError } from "drizzle-orm";
312

313
try {
314
  await db.transaction(async (tx) => {
315
    await tx.insert(users).values({ name: "John" });
316
    await tx.insert(posts).values({ title: "Post", authorId: 1 });
317
  });
318
} catch (error) {
319
  if (error instanceof TransactionRollbackError) {
320
    console.error("Transaction failed:", error.cause);
321
  } else if (error instanceof DrizzleError) {
322
    console.error("Database error:", error.message);
323
  } else {
324
    throw error;
325
  }
326
}
327
```
328

329
## Development and Debugging
330

331
### Logging and Debugging
332

333
```typescript { .api }
334
class DefaultLogger implements Logger {
335
  logQuery(query: string, params: unknown[]): void;
336
}
337

338
interface Logger {
339
  logQuery(query: string, params: unknown[]): void;
340
}
341
```
342

343
### Custom Logger Implementation
344

345
```typescript
346
class CustomLogger implements Logger {
347
  logQuery(query: string, params: unknown[]) {
348
    console.log(`[${new Date().toISOString()}] Query:`, query);
349
    if (params.length > 0) {
350
      console.log("Parameters:", params);
351
    }
352
  }
353
}
354

355
const db = drizzle(client, {
356
  logger: new CustomLogger()
357
});
358
```
359

360
### Query Debugging
361

362
```typescript
363
// Log all queries in development
364
const db = drizzle(client, {
365
  logger: process.env.NODE_ENV === 'development'
366
});
367

368
// Custom query inspection
369
const query = db.select().from(users).getSQL();
370
console.log("Generated SQL:", query.sql);
371
console.log("Parameters:", query.params);
372
```
373

374
## Database-Specific Advanced Features
375

376
### PostgreSQL Extensions
377

378
```typescript
379
// PostGIS geometry support
380
import { geometry } from "drizzle-orm/pg-core";
381

382
const locations = pgTable("locations", {
383
  id: serial("id").primaryKey(),
384
  name: text("name"),
385
  coordinates: geometry("coordinates", {
386
    type: "point",
387
    srid: 4326
388
  }),
389
});
390

391
// Vector search support
392
import { vector } from "drizzle-orm/pg-core";
393

394
const documents = pgTable("documents", {
395
  id: serial("id").primaryKey(),
396
  content: text("content"),
397
  embedding: vector("embedding", { dimensions: 1536 }),
398
});
399
```
400

401
### MySQL Full-Text Search
402

403
```typescript
404
// Full-text search indexes
405
const articles = mysqlTable("articles", {
406
  id: int("id").primaryKey().autoincrement(),
407
  title: varchar("title", { length: 255 }),
408
  content: text("content"),
409
}, (table) => ({
410
  titleContentFts: index("title_content_fts")
411
    .on(table.title, table.content)
412
    .fulltext(),
413
}));
414

415
// Full-text search queries
416
const results = await db
417
  .select()
418
  .from(articles)
419
  .where(sql`MATCH(${articles.title}, ${articles.content}) AGAINST(${searchTerm} IN BOOLEAN MODE)`);
420
```
421

422
### SQLite Extensions
423

424
```typescript
425
// JSON operations in SQLite
426
const settings = sqliteTable("settings", {
427
  id: integer("id").primaryKey(),
428
  config: text("config", { mode: "json" }),
429
});
430

431
const result = await db
432
  .select()
433
  .from(settings)
434
  .where(sql`json_extract(${settings.config}, '$.theme') = ${'dark'}`);
435
```
436

437
## Usage Examples
438

439
### Complete Migration Setup
440

441
```typescript
442
import { migrate } from "drizzle-orm/node-postgres/migrator";
443

444
await migrate(db, {
445
  migrationsFolder: "./migrations",
446
  migrationsTable: "__drizzle_migrations__",
447
  migrationsSchema: "public"
448
});
449
```
450

451
### Advanced Caching Strategy
452

453
```typescript
454
const cache = createCache({
455
  redis,
456
  keyPrefix: "app-cache:",
457
  ttl: 300
458
});
459

460
const db = drizzle(client, {
461
  cache,
462
  schema: { users, posts, usersRelations, postsRelations }
463
});
464

465
// Cached queries are automatically handled
466
const users = await db.query.users.findMany({
467
  with: { posts: true }
468
});
469
```
470

471
### Complex Custom Type
472

473
```typescript
474
const money = customType<{ amount: number; currency: string }>({
475
  dataType() {
476
    return 'decimal(10,2)';
477
  },
478
  toDriver(value) {
479
    return value.amount;
480
  },
481
  fromDriver(value) {
482
    return {
483
      amount: Number(value),
484
      currency: 'USD' // Could be stored separately or configured
485
    };
486
  },
487
});
488

489
const products = pgTable("products", {
490
  id: serial("id").primaryKey(),
491
  name: text("name"),
492
  price: money("price"),
493
});
494
```
495

496
## Types
497

498
```typescript { .api }
499
interface MigrationConfig {
500
  migrationsFolder: string;
501
  migrationsTable?: string;
502
  migrationsSchema?: string;
503
}
504

505
interface MigrationMeta {
506
  sql: string[];
507
  folderMillis: number;
508
  hash: string;
509
  bps: boolean;
510
}
511

512
interface Cache {
513
  get<T>(key: string): Promise<T | null>;
514
  set(key: string, value: unknown, ttl?: number): Promise<void>;
515
  delete(key: string): Promise<void>;
516
  clear(): Promise<void>;
517
  onMutate?: () => Promise<void>;
518
}
519

520
interface Logger {
521
  logQuery(query: string, params: unknown[]): void;
522
}
523

524
type BatchResult<T extends readonly QueryBuilder[]> = {
525
  [K in keyof T]: T[K] extends QueryBuilder<infer U> ? U : never;
526
};
527

528
interface CustomTypeParams<T> {
529
  dataType: () => string;
530
  toDriver: (value: T) => unknown;
531
  fromDriver?: (value: unknown) => T;
532
}
533
```