0
# Drizzle ORM Integration
1

2
Complete Drizzle ORM functionality re-exported by @payloadcms/db-postgres for direct database operations, schema definitions, and raw SQL queries. This provides full access to Drizzle's type-safe query builder and schema definition capabilities.
3

4
## Import Paths
5

6
The package re-exports Drizzle functionality through multiple entry points:
7

8
- `@payloadcms/db-postgres/drizzle` - Core Drizzle ORM functionality
9
- `@payloadcms/db-postgres/drizzle/pg-core` - PostgreSQL-specific schema definitions
10
- `@payloadcms/db-postgres/drizzle/node-postgres` - Node.js PostgreSQL driver integration
11
- `@payloadcms/db-postgres/drizzle/relations` - Database relationship definitions
12

13
## Capabilities
14

15
### Core Drizzle Exports
16

17
All core Drizzle ORM functionality for building type-safe queries and handling database operations.
18

19
```typescript { .api }
20
// SQL template literal for raw queries
21
const sql: SQL;
22

23
// Comparison operators
24
function eq<T>(left: T, right: T): SQLWrapper;
25
function ne<T>(left: T, right: T): SQLWrapper;
26
function gt<T>(left: T, right: T): SQLWrapper;
27
function gte<T>(left: T, right: T): SQLWrapper;
28
function lt<T>(left: T, right: T): SQLWrapper;
29
function lte<T>(left: T, right: T): SQLWrapper;
30

31
// Logical operators
32
function and(...conditions: SQLWrapper[]): SQLWrapper;
33
function or(...conditions: SQLWrapper[]): SQLWrapper;
34
function not(condition: SQLWrapper): SQLWrapper;
35

36
// Pattern matching
37
function like(column: any, pattern: string): SQLWrapper;
38
function ilike(column: any, pattern: string): SQLWrapper;
39

40
// Array operations
41
function inArray<T>(column: T, values: T[]): SQLWrapper;
42
function notInArray<T>(column: T, values: T[]): SQLWrapper;
43

44
// Null checks
45
function isNull(column: any): SQLWrapper;
46
function isNotNull(column: any): SQLWrapper;
47

48
// Aggregation functions
49
function count(column?: any): SQLWrapper;
50
function sum(column: any): SQLWrapper;
51
function avg(column: any): SQLWrapper;
52
function min(column: any): SQLWrapper;
53
function max(column: any): SQLWrapper;
54

55
// Utility functions
56
function placeholder(name: string): SQLWrapper;
57
function param(value: any): SQLWrapper;
58
```
59

60
**Usage Examples:**
61

62
```typescript
63
import { sql, eq, and, or, gt, count } from '@payloadcms/db-postgres/drizzle';
64

65
// Raw SQL queries
66
const result = await db.execute(sql`
67
  SELECT * FROM users 
68
  WHERE created_at > ${new Date('2023-01-01')}
69
`);
70

71
// Type-safe queries with operators
72
const users = await db
73
  .select()
74
  .from(usersTable)
75
  .where(
76
    and(
77
      eq(usersTable.active, true),
78
      gt(usersTable.age, 18),
79
      or(
80
        eq(usersTable.role, 'admin'),
81
        eq(usersTable.role, 'user')
82
      )
83
    )
84
  );
85

86
// Aggregation queries
87
const userCount = await db
88
  .select({ count: count() })
89
  .from(usersTable)
90
  .where(eq(usersTable.active, true));
91
```
92

93
### PostgreSQL Schema Definitions
94

95
PostgreSQL-specific column types, table definitions, and schema utilities.
96

97
```typescript { .api }
98
// Table definition
99
function pgTable(
100
  name: string, 
101
  columns: Record<string, AnyPgColumn>
102
): PgTable;
103

104
// Column types
105
function text(name?: string): PgText;
106
function varchar(name?: string, config?: { length: number }): PgVarchar;
107
function char(name?: string, config?: { length: number }): PgChar;
108
function integer(name?: string): PgInteger;
109
function bigint(name?: string, mode: 'number' | 'bigint'): PgBigint;
110
function serial(name?: string): PgSerial;
111
function bigserial(name?: string, mode: 'number' | 'bigint'): PgBigSerial;
112
function boolean(name?: string): PgBoolean;
113
function date(name?: string): PgDate;
114
function timestamp(
115
  name?: string, 
116
  config?: { withTimezone?: boolean; precision?: number }
117
): PgTimestamp;
118
function time(
119
  name?: string,
120
  config?: { withTimezone?: boolean; precision?: number }
121
): PgTime;
122
function json<T>(name?: string): PgJson<T>;
123
function jsonb<T>(name?: string): PgJsonb<T>;
124
function uuid(name?: string): PgUuid;
125
function numeric(
126
  name?: string,
127
  config?: { precision?: number; scale?: number }
128
): PgNumeric;
129
function real(name?: string): PgReal;
130
function doublePrecision(name?: string): PgDoublePrecision;
131

132
// Array types
133
function array<T>(baseColumn: T): PgArray<T>;
134

135
// Enum types
136
function pgEnum<T extends string>(
137
  name: string, 
138
  values: readonly [T, ...T[]]
139
): PgEnum<T>;
140

141
// Schema definition
142
function pgSchema(name: string): PgSchema;
143

144
// Column constraints
145
function primaryKey(...columns: AnyPgColumn[]): PrimaryKeyBuilder;
146
function foreignKey(config: ForeignKeyConfig): ForeignKeyBuilder;
147
function unique(...columns: AnyPgColumn[]): UniqueConstraintBuilder;
148
function check(name: string, value: SQL): CheckConstraintBuilder;
149
function index(name?: string): IndexBuilder;
150
```
151

152
**Usage Examples:**
153

154
```typescript
155
import { 
156
  pgTable, 
157
  text, 
158
  integer, 
159
  timestamp, 
160
  boolean, 
161
  uuid, 
162
  pgEnum,
163
  pgSchema 
164
} from '@payloadcms/db-postgres/drizzle/pg-core';
165

166
// Define enum
167
const roleEnum = pgEnum('role', ['admin', 'user', 'guest']);
168

169
// Define schema
170
const mySchema = pgSchema('my_app');
171

172
// Define table
173
const users = pgTable('users', {
174
  id: uuid('id').primaryKey().defaultRandom(),
175
  email: text('email').notNull().unique(),
176
  name: text('name').notNull(),
177
  age: integer('age'),
178
  role: roleEnum('role').default('user'),
179
  active: boolean('active').default(true),
180
  createdAt: timestamp('created_at').defaultNow(),
181
  updatedAt: timestamp('updated_at').defaultNow(),
182
});
183

184
// Table with schema
185
const posts = mySchema.table('posts', {
186
  id: uuid('id').primaryKey().defaultRandom(),
187
  title: text('title').notNull(),
188
  content: text('content'),
189
  authorId: uuid('author_id').references(() => users.id),
190
  published: boolean('published').default(false),
191
});
192
```
193

194
### Node.js PostgreSQL Driver Integration
195

196
Drizzle integration with the node-postgres driver for connection management and query execution.
197

198
```typescript { .api }
199
// Database instance creation
200
function drizzle<T>(config: DrizzleConfig<T>): NodePgDatabase<T>;
201

202
interface DrizzleConfig<T> {
203
  /** pg.Pool or pg.Client instance */
204
  client: Pool | Client;
205
  
206
  /** Schema definitions */
207
  schema?: T;
208
  
209
  /** Query logger */
210
  logger?: boolean | DrizzleLogger;
211
}
212

213
interface DrizzleLogger {
214
  logQuery(query: string, params: unknown[]): void;
215
}
216

217
// Connection types
218
interface Pool {
219
  connect(): Promise<PoolClient>;
220
  end(): Promise<void>;
221
  query(text: string, params?: any[]): Promise<QueryResult>;
222
}
223

224
interface Client {
225
  connect(): Promise<void>;
226
  end(): Promise<void>;
227
  query(text: string, params?: any[]): Promise<QueryResult>;
228
}
229
```
230

231
**Usage Examples:**
232

233
```typescript
234
import { drizzle } from 'drizzle-orm/node-postgres';
235
import { Pool } from 'pg';
236

237
// Create connection pool
238
const pool = new Pool({
239
  connectionString: process.env.DATABASE_URL,
240
});
241

242
// Create Drizzle instance
243
const db = drizzle({
244
  client: pool,
245
  schema: { users, posts },
246
  logger: true, // Enable query logging
247
});
248

249
// Use the database
250
const allUsers = await db.select().from(users);
251
const userWithPosts = await db.query.users.findFirst({
252
  with: {
253
    posts: true,
254
  },
255
});
256
```
257

258
### Database Relationships
259

260
Relationship definitions for expressing table relationships and enabling relational queries.
261

262
```typescript { .api }
263
// Relationship definition functions
264
function relations<T>(
265
  table: T,
266
  relations: RelationConfig<T>
267
): Relations<T>;
268

269
function one<T>(
270
  table: T,
271
  config?: RelationConfig
272
): OneRelation<T>;
273

274
function many<T>(
275
  table: T,
276
  config?: RelationConfig
277
): ManyRelation<T>;
278

279
interface RelationConfig {
280
  fields?: AnyColumn[];
281
  references?: AnyColumn[];
282
  relationName?: string;
283
}
284
```
285

286
**Usage Examples:**
287

288
```typescript
289
import { relations, one, many } from '@payloadcms/db-postgres/drizzle/relations';
290

291
// Define relationships
292
const usersRelations = relations(users, ({ many }) => ({
293
  posts: many(posts),
294
  profile: one(profiles),
295
}));
296

297
const postsRelations = relations(posts, ({ one }) => ({
298
  author: one(users, {
299
    fields: [posts.authorId],
300
    references: [users.id],
301
  }),
302
  category: one(categories, {
303
    fields: [posts.categoryId],
304
    references: [categories.id],
305
  }),
306
}));
307

308
// Use relationships in queries
309
const usersWithPosts = await db.query.users.findMany({
310
  with: {
311
    posts: {
312
      with: {
313
        category: true,
314
      },
315
    },
316
    profile: true,
317
  },
318
});
319
```
320

321
### Additional PostgreSQL Utilities
322

323
PostgreSQL-specific utilities and functions.
324

325
```typescript { .api }
326
// Geometry column for PostGIS
327
function geometryColumn(
328
  name: string,
329
  type: 'POINT' | 'LINESTRING' | 'POLYGON' | 'MULTIPOINT' | 'MULTILINESTRING' | 'MULTIPOLYGON',
330
  srid?: number
331
): PgGeometry;
332

333
// Custom column types
334
function interval(name?: string): PgInterval;
335
function bytea(name?: string): PgBytea;
336
function inet(name?: string): PgInet;
337
function cidr(name?: string): PgCidr;
338
function macaddr(name?: string): PgMacaddr;
339

340
// Advanced features
341
function generated<T>(
342
  expression: SQL,
343
  config?: { stored?: boolean }
344
): GeneratedColumn<T>;
345
```
346

347
**Usage Examples:**
348

349
```typescript
350
import { geometryColumn } from '@payloadcms/db-postgres';
351
import { pgTable, text, timestamp, uuid } from '@payloadcms/db-postgres/drizzle/pg-core';
352

353
// Table with PostGIS geometry
354
const locations = pgTable('locations', {
355
  id: uuid('id').primaryKey().defaultRandom(),
356
  name: text('name').notNull(),
357
  point: geometryColumn('point', 'POINT', 4326),
358
  createdAt: timestamp('created_at').defaultNow(),
359
});
360

361
// Query spatial data
362
const nearbyLocations = await db.execute(sql`
363
  SELECT * FROM locations 
364
  WHERE ST_DWithin(point, ST_MakePoint(${longitude}, ${latitude})::geometry, 1000)
365
`);
366
```
367

368
## Integration with Payload Adapter
369

370
The Drizzle integration works seamlessly with the Payload PostgreSQL adapter:
371

372
```typescript
373
import { postgresAdapter } from '@payloadcms/db-postgres';
374
import { sql, eq } from '@payloadcms/db-postgres/drizzle';
375

376
export default buildConfig({
377
  db: postgresAdapter({
378
    pool: { connectionString: process.env.DATABASE_URI },
379
  }),
380
  
381
  // Access Drizzle instance in hooks
382
  hooks: {
383
    afterOperation: [
384
      async ({ operation, result, req }) => {
385
        // Direct database access with Drizzle
386
        const { payload } = req;
387
        const db = payload.db.drizzle;
388
        
389
        // Execute custom queries
390
        await db.execute(sql`
391
          INSERT INTO audit_log (operation, result, timestamp)
392
          VALUES (${operation}, ${JSON.stringify(result)}, NOW())
393
        `);
394
      },
395
    ],
396
  },
397
});
398
```