0
# Drizzle Integration
1

2
Direct access to Drizzle ORM functionality for advanced database operations, custom queries, and low-level database management. The adapter provides seamless integration with Drizzle ORM while maintaining Payload's abstraction layer.
3

4
## Capabilities
5

6
### Core Drizzle Access
7

8
Access to the complete Drizzle ORM functionality through re-exported modules.
9

10
```typescript { .api }
11
/**
12
 * Main Drizzle ORM module re-export
13
 * Provides access to core Drizzle functionality including SQL template literals,
14
 * query builders, and utility functions
15
 */
16
import * as drizzle from "@payloadcms/db-sqlite/drizzle";
17

18
/**
19
 * SQL template literal function for raw query construction
20
 * Provides type-safe parameter binding and query building
21
 */
22
import { sql } from "@payloadcms/db-sqlite";
23
```
24

25
**Usage Examples:**
26

27
```typescript
28
import { sql } from "@payloadcms/db-sqlite";
29
import * as drizzle from "@payloadcms/db-sqlite/drizzle";
30

31
// In a migration or custom operation
32
export async function customQuery({ db }: { db: any }) {
33
  // Raw SQL with parameters
34
  const result = await db.run(sql`
35
    SELECT COUNT(*) as total 
36
    FROM posts 
37
    WHERE status = ${'published'}
38
  `);
39
  
40
  // Using Drizzle query builder methods
41
  const { eq, and, or } = drizzle;
42
  
43
  // Complex conditional queries
44
  const complexQuery = sql`
45
    SELECT * FROM users 
46
    WHERE ${and(
47
      eq(sql`active`, true),
48
      or(
49
        eq(sql`role`, 'admin'),
50
        eq(sql`role`, 'editor')
51
      )
52
    )}
53
  `;
54
}
55
```
56

57
### SQLite Core Integration
58

59
Access to SQLite-specific Drizzle functionality for table definitions, column types, and database operations.
60

61
```typescript { .api }
62
/**
63
 * SQLite-specific Drizzle ORM functionality
64
 * Includes table creation, column definitions, and SQLite-specific features
65
 */
66
import * as sqliteCore from "@payloadcms/db-sqlite/drizzle/sqlite-core";
67
```
68

69
**Usage Examples:**
70

71
```typescript
72
import * as sqliteCore from "@payloadcms/db-sqlite/drizzle/sqlite-core";
73

74
// In schema hooks or custom table definitions
75
const { 
76
  sqliteTable, 
77
  text, 
78
  integer, 
79
  real, 
80
  blob,
81
  primaryKey,
82
  foreignKey,
83
  unique,
84
  index
85
} = sqliteCore;
86

87
// Custom table definition
88
const customTable = sqliteTable('custom_analytics', {
89
  id: integer('id').primaryKey({ autoIncrement: true }),
90
  event_name: text('event_name').notNull(),
91
  user_id: integer('user_id').references(() => usersTable.id),
92
  timestamp: integer('timestamp').notNull(),
93
  metadata: text('metadata', { mode: 'json' }),
94
  value: real('value'),
95
}, (table) => ({
96
  // Composite indexes
97
  eventUserIdx: index('event_user_idx').on(table.event_name, table.user_id),
98
  timestampIdx: index('timestamp_idx').on(table.timestamp),
99
  // Unique constraints
100
  uniqueEventUser: unique('unique_event_user').on(table.event_name, table.user_id, table.timestamp),
101
}));
102
```
103

104
### LibSQL Client Integration
105

106
Direct access to LibSQL-specific functionality for Turso databases and edge deployments.
107

108
```typescript { .api }
109
/**
110
 * LibSQL-specific Drizzle integration
111
 * Provides access to LibSQL client features and remote database functionality
112
 */
113
import * as libsql from "@payloadcms/db-sqlite/drizzle/libsql";
114
```
115

116
**Usage Examples:**
117

118
```typescript
119
import * as libsql from "@payloadcms/db-sqlite/drizzle/libsql";
120

121
// Access LibSQL-specific features
122
const { createClient } = libsql;
123

124
// Custom LibSQL client configuration (if needed)
125
export function createCustomClient() {
126
  return createClient({
127
    url: process.env.TURSO_DATABASE_URL!,
128
    authToken: process.env.TURSO_AUTH_TOKEN!,
129
    // LibSQL-specific options
130
    syncUrl: process.env.TURSO_SYNC_URL,
131
    syncInterval: 5000,
132
  });
133
}
134
```
135

136
### Relations and Foreign Keys
137

138
Access to Drizzle's relation system for defining and managing database relationships.
139

140
```typescript { .api }
141
/**
142
 * Drizzle relations system for defining table relationships
143
 * Enables type-safe joins and relationship queries
144
 */
145
import * as relations from "@payloadcms/db-sqlite/drizzle/relations";
146
```
147

148
**Usage Examples:**
149

150
```typescript
151
import * as relations from "@payloadcms/db-sqlite/drizzle/relations";
152
import { sqliteTable, integer, text } from "@payloadcms/db-sqlite/drizzle/sqlite-core";
153

154
const { relations, one, many } = relations;
155

156
// Define tables
157
const users = sqliteTable('users', {
158
  id: integer('id').primaryKey(),
159
  name: text('name').notNull(),
160
});
161

162
const posts = sqliteTable('posts', {
163
  id: integer('id').primaryKey(),
164
  title: text('title').notNull(),
165
  authorId: integer('author_id').references(() => users.id),
166
});
167

168
// Define relations
169
const usersRelations = relations(users, ({ many }) => ({
170
  posts: many(posts),
171
}));
172

173
const postsRelations = relations(posts, ({ one }) => ({
174
  author: one(users, {
175
    fields: [posts.authorId],
176
    references: [users.id],
177
  }),
178
}));
179

180
// Use in queries (within migrations or hooks)
181
export async function getPostsWithAuthors({ db }: { db: any }) {
182
  return await db.query.posts.findMany({
183
    with: {
184
      author: true,
185
    },
186
  });
187
}
188
```
189

190
### Advanced Query Building
191

192
Complex query construction using Drizzle's query builder with SQLite optimizations.
193

194
```typescript { .api }
195
/**
196
 * Advanced query building utilities
197
 * Provides access to Drizzle's full query construction capabilities
198
 */
199
interface DrizzleQueryBuilder {
200
  /** Conditional operators for WHERE clauses */
201
  eq: (column: any, value: any) => any;
202
  ne: (column: any, value: any) => any;
203
  gt: (column: any, value: any) => any;
204
  gte: (column: any, value: any) => any;
205
  lt: (column: any, value: any) => any;
206
  lte: (column: any, value: any) => any;
207
  like: (column: any, pattern: string) => any;
208
  notLike: (column: any, pattern: string) => any;
209
  isNull: (column: any) => any;
210
  isNotNull: (column: any) => any;
211
  inArray: (column: any, values: any[]) => any;
212
  notInArray: (column: any, values: any[]) => any;
213
  
214
  /** Logical operators */
215
  and: (...conditions: any[]) => any;
216
  or: (...conditions: any[]) => any;
217
  not: (condition: any) => any;
218
  
219
  /** Aggregation functions */
220
  count: (column?: any) => any;
221
  sum: (column: any) => any;
222
  avg: (column: any) => any;
223
  min: (column: any) => any;
224
  max: (column: any) => any;
225
}
226
```
227

228
**Usage Examples:**
229

230
```typescript
231
import { sql } from "@payloadcms/db-sqlite";
232
import { eq, and, or, count, sum } from "@payloadcms/db-sqlite/drizzle";
233

234
// In custom operations or hooks
235
export async function advancedQueries({ db, schema }: { db: any, schema: any }) {
236
  const { posts, users } = schema.tables;
237
  
238
  // Complex filtering
239
  const activeUserPosts = await db
240
    .select({
241
      id: posts.id,
242
      title: posts.title,
243
      authorName: users.name,
244
    })
245
    .from(posts)
246
    .innerJoin(users, eq(posts.authorId, users.id))
247
    .where(
248
      and(
249
        eq(posts.status, 'published'),
250
        or(
251
          eq(users.role, 'admin'),
252
          eq(users.role, 'editor')
253
        ),
254
        isNotNull(posts.featuredImage)
255
      )
256
    )
257
    .orderBy(posts.createdAt)
258
    .limit(10);
259
  
260
  // Aggregation queries
261
  const postStats = await db
262
    .select({
263
      authorId: posts.authorId,
264
      authorName: users.name,
265
      postCount: count(posts.id),
266
      totalViews: sum(posts.viewCount),
267
      avgRating: avg(posts.rating),
268
    })
269
    .from(posts)
270
    .innerJoin(users, eq(posts.authorId, users.id))
271
    .groupBy(posts.authorId, users.name)
272
    .having(gt(count(posts.id), 5));
273
  
274
  // Subqueries
275
  const topAuthors = db
276
    .select({ id: users.id })
277
    .from(users)
278
    .where(
279
      inArray(
280
        users.id,
281
        db
282
          .select({ authorId: posts.authorId })
283
          .from(posts)
284
          .groupBy(posts.authorId)
285
          .having(gt(count(posts.id), 10))
286
      )
287
    );
288
  
289
  return { activeUserPosts, postStats, topAuthors };
290
}
291
```
292

293
### Transaction Management
294

295
Direct access to Drizzle's transaction capabilities for complex operations.
296

297
```typescript { .api }
298
/**
299
 * Transaction management utilities
300
 * Provides low-level transaction control for complex operations
301
 */
302
interface DrizzleTransactions {
303
  /** Execute operations within a transaction */
304
  transaction: <T>(
305
    callback: (tx: any) => Promise<T>
306
  ) => Promise<T>;
307
}
308
```
309

310
**Usage Examples:**
311

312
```typescript
313
import { sql } from "@payloadcms/db-sqlite";
314

315
// Complex transaction with multiple operations
316
export async function complexTransaction({ db }: { db: any }) {
317
  return await db.transaction(async (tx) => {
318
    // Create user
319
    const [user] = await tx
320
      .insert(users)
321
      .values({ name: 'John Doe', email: 'john@example.com' })
322
      .returning();
323
    
324
    // Create profile
325
    await tx
326
      .insert(profiles)
327
      .values({ 
328
        userId: user.id, 
329
        bio: 'Software developer',
330
        avatar: 'avatar.jpg'
331
      });
332
    
333
    // Update statistics
334
    await tx.run(sql`
335
      UPDATE site_stats 
336
      SET user_count = user_count + 1,
337
          updated_at = CURRENT_TIMESTAMP
338
    `);
339
    
340
    // Send welcome email (external service)
341
    await sendWelcomeEmail(user.email);
342
    
343
    return user;
344
  });
345
}
346
```
347

348
### Custom Operators
349

350
Access to SQLite-specific operators and functions through Drizzle integration.
351

352
```typescript { .api }
353
/**
354
 * SQLite-specific operators and functions
355
 * Optimized for SQLite's case-insensitive LIKE behavior
356
 */
357
interface SQLiteOperators {
358
  /** Case-insensitive LIKE (native SQLite behavior) */
359
  like: (column: any, pattern: string) => any;
360
  /** Case-insensitive NOT LIKE */
361
  notLike: (column: any, pattern: string) => any;
362
  /** JSON operations for JSON columns */
363
  jsonExtract: (column: any, path: string) => any;
364
  jsonArrayLength: (column: any) => any;
365
}
366
```
367

368
**Usage Examples:**
369

370
```typescript
371
import { like, notLike } from "@payloadcms/db-sqlite/drizzle";
372
import { sql } from "@payloadcms/db-sqlite";
373

374
// SQLite-optimized queries
375
export async function searchOperations({ db, schema }: { db: any, schema: any }) {
376
  const { posts } = schema.tables;
377
  
378
  // Case-insensitive search (SQLite's native LIKE behavior)
379
  const searchResults = await db
380
    .select()
381
    .from(posts)
382
    .where(
383
      or(
384
        like(posts.title, '%javascript%'),
385
        like(posts.content, '%typescript%')
386
      )
387
    );
388
  
389
  // JSON operations
390
  const postsWithTags = await db
391
    .select({
392
      id: posts.id,
393
      title: posts.title,
394
      tagCount: sql`json_array_length(${posts.metadata}, '$.tags')`,
395
      firstTag: sql`json_extract(${posts.metadata}, '$.tags[0]')`,
396
    })
397
    .from(posts)
398
    .where(sql`json_extract(${posts.metadata}, '$.tags') IS NOT NULL`);
399
  
400
  return { searchResults, postsWithTags };
401
}
402
```