0
# Query Building
1

2
Drizzle ORM provides type-safe query builders for all SQL operations with comprehensive support for complex queries, joins, subqueries, and database-specific features. All query builders maintain full type safety and return inferred types.
3

4
## Select Queries
5

6
### Basic Select
7

8
```typescript { .api }
9
interface Database {
10
  select(): SelectBuilder;
11
  select<TSelection>(selection: TSelection): SelectBuilder<TSelection>;
12
}
13

14
interface SelectBuilder<TSelection = any> {
15
  from<TTable extends AnyTable>(table: TTable): SelectWithTables<TSelection, [TTable]>;
16
}
17

18
interface SelectWithTables<TSelection, TTables extends AnyTable[]> {
19
  where(condition: SQL | undefined): this;
20
  having(condition: SQL): this;
21
  orderBy(...columns: (AnyColumn | SQL)[]): this;
22
  limit(limit: number): this;
23
  offset(offset: number): this;
24
  groupBy(...columns: (AnyColumn | SQL)[]): this;
25
  for(strength: LockStrength, config?: LockConfig): this; // PostgreSQL only
26
}
27
```
28

29
### Joins
30

31
```typescript { .api }
32
interface SelectWithTables<TSelection, TTables extends AnyTable[]> {
33
  leftJoin<TJoinedTable extends AnyTable>(
34
    table: TJoinedTable,
35
    on: SQL
36
  ): SelectWithTables<TSelection, [...TTables, TJoinedTable]>;
37

38
  rightJoin<TJoinedTable extends AnyTable>(
39
    table: TJoinedTable,
40
    on: SQL
41
  ): SelectWithTables<TSelection, [...TTables, TJoinedTable]>;
42

43
  innerJoin<TJoinedTable extends AnyTable>(
44
    table: TJoinedTable,
45
    on: SQL
46
  ): SelectWithTables<TSelection, [...TTables, TJoinedTable]>;
47

48
  fullJoin<TJoinedTable extends AnyTable>(
49
    table: TJoinedTable,
50
    on: SQL
51
  ): SelectWithTables<TSelection, [...TTables, TJoinedTable]>;
52
}
53
```
54

55
### Subqueries and CTEs
56

57
```typescript { .api }
58
interface Database {
59
  with(alias: string): WithBuilder;
60
}
61

62
interface WithBuilder {
63
  as<T extends AnySelect>(query: T): WithClause<T>;
64
}
65

66
interface SelectBuilder {
67
  with(...withs: WithClause[]): this;
68
}
69

70
function union<T extends AnySelect>(left: T, right: T): UnionBuilder<T>;
71
function unionAll<T extends AnySelect>(left: T, right: T): UnionBuilder<T>;
72
function intersect<T extends AnySelect>(left: T, right: T): IntersectBuilder<T>;
73
function except<T extends AnySelect>(left: T, right: T): ExceptBuilder<T>;
74
```
75

76
## Insert Queries
77

78
### Basic Insert
79

80
```typescript { .api }
81
interface Database {
82
  insert<T extends AnyTable>(table: T): InsertBuilder<T>;
83
}
84

85
interface InsertBuilder<TTable extends AnyTable> {
86
  values(values: InsertObject<TTable> | InsertObject<TTable>[]): InsertExecBuilder<TTable>;
87
}
88

89
interface InsertExecBuilder<TTable extends AnyTable> {
90
  returning(): InsertReturning<TTable, TTable>;
91
  returning<TSelection>(selection: TSelection): InsertReturning<TTable, TSelection>;
92
  onConflictDoNothing(config?: { target?: AnyColumn | AnyColumn[] }): this;
93
  onConflictDoUpdate(config: OnConflictDoUpdateConfig<TTable>): this;
94
}
95
```
96

97
### PostgreSQL-Specific Insert Features
98

99
```typescript { .api }
100
interface PgInsertBuilder<TTable extends AnyPgTable> extends InsertBuilder<TTable> {
101
  onConflictDoNothing(config?: {
102
    target?: AnyPgColumn | AnyPgColumn[];
103
    where?: SQL;
104
  }): this;
105

106
  onConflictDoUpdate(config: {
107
    target: AnyPgColumn | AnyPgColumn[];
108
    set: Partial<InsertObject<TTable>>;
109
    where?: SQL;
110
  }): this;
111
}
112

113
interface OnConflictDoUpdateConfig<TTable extends AnyTable> {
114
  target: AnyColumn | AnyColumn[];
115
  set: Partial<InsertObject<TTable>>;
116
  where?: SQL;
117
}
118
```
119

120
### MySQL-Specific Insert Features
121

122
```typescript { .api }
123
interface MySqlInsertBuilder<TTable extends AnyMySqlTable> extends InsertBuilder<TTable> {
124
  onDuplicateKeyUpdate(config: {
125
    set: Partial<InsertObject<TTable>>;
126
  }): this;
127

128
  ignore(): this;
129
}
130
```
131

132
## Update Queries
133

134
```typescript { .api }
135
interface Database {
136
  update<T extends AnyTable>(table: T): UpdateBuilder<T>;
137
}
138

139
interface UpdateBuilder<TTable extends AnyTable> {
140
  set(values: Partial<UpdateObject<TTable>>): UpdateExecBuilder<TTable>;
141
}
142

143
interface UpdateExecBuilder<TTable extends AnyTable> {
144
  where(condition: SQL): this;
145
  returning(): UpdateReturning<TTable, TTable>;
146
  returning<TSelection>(selection: TSelection): UpdateReturning<TTable, TSelection>;
147
}
148
```
149

150
## Delete Queries
151

152
```typescript { .api }
153
interface Database {
154
  delete<T extends AnyTable>(table: T): DeleteBuilder<T>;
155
}
156

157
interface DeleteBuilder<TTable extends AnyTable> {
158
  where(condition: SQL): DeleteExecBuilder<TTable>;
159
}
160

161
interface DeleteExecBuilder<TTable extends AnyTable> {
162
  returning(): DeleteReturning<TTable, TTable>;
163
  returning<TSelection>(selection: TSelection): DeleteReturning<TTable, TSelection>;
164
}
165
```
166

167
## SQL Expressions and Operators
168

169
### Comparison Operators
170

171
```typescript { .api }
172
function eq<T>(left: T, right: T): SQL;
173
function ne<T>(left: T, right: T): SQL;
174
function gt<T>(left: T, right: T): SQL;
175
function gte<T>(left: T, right: T): SQL;
176
function lt<T>(left: T, right: T): SQL;
177
function lte<T>(left: T, right: T): SQL;
178
function isNull(value: AnyColumn): SQL;
179
function isNotNull(value: AnyColumn): SQL;
180
function inArray<T>(column: T, values: (T | Placeholder)[]): SQL;
181
function notInArray<T>(column: T, values: (T | Placeholder)[]): SQL;
182
function exists(subquery: AnySelect): SQL;
183
function notExists(subquery: AnySelect): SQL;
184
function between<T>(column: T, min: T, max: T): SQL;
185
function notBetween<T>(column: T, min: T, max: T): SQL;
186
```
187

188
### Logical Operators
189

190
```typescript { .api }
191
function and(...conditions: (SQL | undefined)[]): SQL;
192
function or(...conditions: (SQL | undefined)[]): SQL;
193
function not(condition: SQL): SQL;
194
```
195

196
### String Operators
197

198
```typescript { .api }
199
function like(column: AnyColumn, value: string): SQL;
200
function notLike(column: AnyColumn, value: string): SQL;
201
function ilike(column: AnyColumn, value: string): SQL; // PostgreSQL only
202
function notIlike(column: AnyColumn, value: string): SQL; // PostgreSQL only
203
function regexp(column: AnyColumn, pattern: string): SQL; // MySQL only
204
function notRegexp(column: AnyColumn, pattern: string): SQL; // MySQL only
205
```
206

207
### Sorting
208

209
```typescript { .api }
210
function asc(column: AnyColumn): SQL;
211
function desc(column: AnyColumn): SQL;
212
function asc(expression: SQL): SQL;
213
function desc(expression: SQL): SQL;
214
```
215

216
## Aggregate Functions
217

218
```typescript { .api }
219
function count(): SQL<number>;
220
function count(column: AnyColumn): SQL<number>;
221
function countDistinct(column: AnyColumn): SQL<number>;
222
function avg(column: AnyColumn): SQL<string>;
223
function sum(column: AnyColumn): SQL<string>;
224
function max<T>(column: T): SQL<T>;
225
function min<T>(column: T): SQL<T>;
226
```
227

228
## SQL Functions
229

230
### String Functions
231

232
```typescript { .api }
233
function concat(column: AnyColumn, ...values: (AnyColumn | string)[]): SQL<string>;
234
function substring(column: AnyColumn, start: number, length?: number): SQL<string>;
235
function length(column: AnyColumn): SQL<number>;
236
function upper(column: AnyColumn): SQL<string>;
237
function lower(column: AnyColumn): SQL<string>;
238
function trim(column: AnyColumn, characters?: string): SQL<string>;
239
```
240

241
### Date Functions
242

243
```typescript { .api }
244
function now(): SQL<Date>;
245
function currentDate(): SQL<Date>;
246
function currentTime(): SQL<Date>;
247
function currentTimestamp(): SQL<Date>;
248
function extract(unit: 'year' | 'month' | 'day' | 'hour' | 'minute' | 'second', date: AnyColumn): SQL<number>;
249
```
250

251
### Math Functions
252

253
```typescript { .api }
254
function abs(column: AnyColumn): SQL<number>;
255
function round(column: AnyColumn, precision?: number): SQL<number>;
256
function floor(column: AnyColumn): SQL<number>;
257
function ceil(column: AnyColumn): SQL<number>;
258
function random(): SQL<number>;
259
```
260

261
## Custom SQL
262

263
```typescript { .api }
264
function sql<T = unknown>(strings: TemplateStringsArray, ...values: unknown[]): SQL<T>;
265

266
interface SQL<T = unknown> extends SQLWrapper {
267
  readonly queryChunks: Chunk[];
268
  append(sql: SQL): SQL<T>;
269
  toQuery(): Query;
270
}
271

272
interface SQLWrapper {
273
  getSQL(): SQL;
274
  shouldOmitSQLParens?(): boolean;
275
}
276
```
277

278
## Prepared Statements
279

280
```typescript { .api }
281
interface SelectBuilder {
282
  prepare(): PreparedQuery<SelectResult>;
283
  prepare(name: string): PreparedQuery<SelectResult>;
284
}
285

286
interface PreparedQuery<T = unknown> {
287
  execute(placeholderValues?: Record<string, unknown>): Promise<T>;
288
  all(placeholderValues?: Record<string, unknown>): Promise<T[]>;
289
  get(placeholderValues?: Record<string, unknown>): Promise<T | undefined>;
290
}
291

292
function placeholder(name: string): Placeholder;
293
```
294

295
## Relational Queries
296

297
When schema is provided to the database connection, relational query API becomes available:
298

299
```typescript { .api }
300
interface RelationalQueryBuilder<TSchema extends Record<string, unknown>> {
301
  query: {
302
    [K in keyof TSchema]: TSchema[K] extends AnyTable
303
      ? RelationalQueryBuilder<TSchema, TSchema[K]>
304
      : never;
305
  };
306
}
307

308
interface RelationalQueryBuilder<TSchema, TTable extends AnyTable> {
309
  findMany(config?: RelationalFindManyConfig<TSchema, TTable>): Promise<InferSelectModel<TTable>[]>;
310
  findFirst(config?: RelationalFindFirstConfig<TSchema, TTable>): Promise<InferSelectModel<TTable> | undefined>;
311
}
312

313
interface RelationalFindManyConfig<TSchema, TTable extends AnyTable> {
314
  where?: SQL;
315
  orderBy?: (AnyColumn | SQL)[];
316
  limit?: number;
317
  offset?: number;
318
  with?: RelationalWith<TSchema, TTable>;
319
  columns?: ColumnsSelection<TTable>;
320
  extras?: ExtrasSelection<TTable>;
321
}
322
```
323

324
## Database-Specific Features
325

326
### PostgreSQL
327

328
```typescript { .api }
329
// Row-level locking
330
interface PgSelect {
331
  for(strength: 'update' | 'no key update' | 'share' | 'key share', config?: {
332
    of?: AnyPgTable | AnyPgTable[];
333
    noWait?: boolean;
334
    skipLocked?: boolean;
335
  }): this;
336
}
337

338
// DISTINCT ON
339
interface PgSelectBuilder {
340
  distinctOn(columns: AnyPgColumn[]): this;
341
}
342

343
// Refresh materialized view
344
interface PgDatabase {
345
  refreshMaterializedView(view: AnyPgMaterializedView): RefreshMaterializedViewBuilder;
346
}
347
```
348

349
### MySQL
350

351
```typescript { .api }
352
// MySQL-specific modifiers
353
interface MySqlSelectBuilder {
354
  sqlCalcFoundRows(): this;
355
  sqlSmallResult(): this;
356
  sqlBigResult(): this;
357
  sqlBufferResult(): this;
358
  sqlNoCache(): this;
359
  sqlCache(): this;
360
}
361
```
362

363
### SQLite
364

365
```typescript { .api }
366
// SQLite-specific features automatically handled
367
interface SQLiteDatabase extends Database {
368
  // Standard CRUD operations with SQLite optimizations
369
}
370
```
371

372
## Usage Examples
373

374
### Complex Select with Joins
375

376
```typescript
377
const result = await db
378
  .select({
379
    userName: users.name,
380
    userEmail: users.email,
381
    postTitle: posts.title,
382
    postContent: posts.content,
383
    commentText: comments.text,
384
  })
385
  .from(users)
386
  .leftJoin(posts, eq(users.id, posts.authorId))
387
  .leftJoin(comments, eq(posts.id, comments.postId))
388
  .where(and(
389
    eq(users.isActive, true),
390
    gt(posts.publishedAt, new Date('2023-01-01'))
391
  ))
392
  .orderBy(desc(posts.publishedAt), asc(users.name))
393
  .limit(10);
394
```
395

396
### Insert with Conflict Resolution
397

398
```typescript
399
// PostgreSQL
400
await db.insert(users)
401
  .values({ name: "John", email: "john@example.com" })
402
  .onConflictDoUpdate({
403
    target: users.email,
404
    set: { name: excluded.name, updatedAt: now() }
405
  })
406
  .returning();
407

408
// MySQL
409
await db.insert(users)
410
  .values({ name: "John", email: "john@example.com" })
411
  .onDuplicateKeyUpdate({
412
    set: { name: sql`VALUES(name)`, updatedAt: now() }
413
  });
414
```
415

416
### Subqueries and CTEs
417

418
```typescript
419
const sq = db
420
  .select({ authorId: posts.authorId, postCount: count() })
421
  .from(posts)
422
  .groupBy(posts.authorId)
423
  .as("post_counts");
424

425
const result = await db
426
  .with(sq)
427
  .select({
428
    userName: users.name,
429
    postCount: sq.postCount,
430
  })
431
  .from(users)
432
  .leftJoin(sq, eq(users.id, sq.authorId));
433
```
434

435
### Custom SQL Expressions
436

437
```typescript
438
const result = await db
439
  .select({
440
    id: users.id,
441
    fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
442
    ageCategory: sql<string>`
443
      CASE
444
        WHEN ${users.age} < 18 THEN 'minor'
445
        WHEN ${users.age} < 65 THEN 'adult'
446
        ELSE 'senior'
447
      END
448
    `,
449
  })
450
  .from(users);
451
```
452

453
### Prepared Statements with Placeholders
454

455
```typescript
456
const prepared = db
457
  .select()
458
  .from(users)
459
  .where(eq(users.id, placeholder("userId")))
460
  .prepare();
461

462
const user = await prepared.execute({ userId: 1 });
463
```
464

465
### Relational Queries
466

467
```typescript
468
// With relational schema configured
469
const usersWithPosts = await db.query.users.findMany({
470
  with: {
471
    posts: {
472
      with: {
473
        comments: true
474
      }
475
    }
476
  },
477
  where: eq(users.isActive, true),
478
  limit: 10
479
});
480
```
481

482
## Types
483

484
```typescript { .api }
485
type InferSelectModel<T extends AnyTable> = T['$inferSelect'];
486
type InferInsertModel<T extends AnyTable> = T['$inferInsert'];
487

488
type SelectResult<TSelection, TTableSelection extends Record<string, any>> =
489
  TSelection extends undefined
490
    ? TTableSelection
491
    : TSelection;
492

493
type InsertObject<TTable extends AnyTable> = InferInsertModel<TTable>;
494
type UpdateObject<TTable extends AnyTable> = Partial<InferSelectModel<TTable>>;
495

496
interface Query {
497
  sql: string;
498
  params: unknown[];
499
}
500

501
type AnySelect = Select<any, any, any>;
502
type AnyPgSelect = PgSelect<any, any, any>;
503
type AnyMySqlSelect = MySqlSelect<any, any, any>;
504
type AnySQLiteSelect = SQLiteSelect<any, any, any>;
505

506
// SQL expression helpers
507
function placeholder<T = any>(name: string): Placeholder<T>;
508
function sql<T>(strings: TemplateStringsArray, ...values: any[]): SQL<T>;
509

510
// Aggregate functions
511
function count(): SQL<number>;
512
function count<T extends AnyColumn>(column: T): SQL<number>;
513
function countDistinct<T extends AnyColumn>(column: T): SQL<number>;
514
function sum<T extends AnyColumn>(column: T): SQL<number>;
515
function avg<T extends AnyColumn>(column: T): SQL<number>;
516
function min<T extends AnyColumn>(column: T): SQL<InferData<T>>;
517
function max<T extends AnyColumn>(column: T): SQL<InferData<T>>;
518

519
// Conditional expressions
520
function eq<T extends AnyColumn>(left: T, right: any): SQL<boolean>;
521
function ne<T extends AnyColumn>(left: T, right: any): SQL<boolean>;
522
function gt<T extends AnyColumn>(left: T, right: any): SQL<boolean>;
523
function gte<T extends AnyColumn>(left: T, right: any): SQL<boolean>;
524
function lt<T extends AnyColumn>(left: T, right: any): SQL<boolean>;
525
function lte<T extends AnyColumn>(left: T, right: any): SQL<boolean>;
526
function like<T extends AnyColumn>(left: T, right: string): SQL<boolean>;
527
function ilike<T extends AnyColumn>(left: T, right: string): SQL<boolean>;
528
function inArray<T extends AnyColumn>(column: T, values: any[]): SQL<boolean>;
529
function notInArray<T extends AnyColumn>(column: T, values: any[]): SQL<boolean>;
530
function exists(query: AnySelect): SQL<boolean>;
531
function notExists(query: AnySelect): SQL<boolean>;
532
function between<T extends AnyColumn>(column: T, min: any, max: any): SQL<boolean>;
533
function notBetween<T extends AnyColumn>(column: T, min: any, max: any): SQL<boolean>;
534
function isNull<T extends AnyColumn>(column: T): SQL<boolean>;
535
function isNotNull<T extends AnyColumn>(column: T): SQL<boolean>;
536

537
// Logical operators
538
function and(...conditions: SQL[]): SQL<boolean>;
539
function or(...conditions: SQL[]): SQL<boolean>;
540
function not(condition: SQL<boolean>): SQL<boolean>;
541

542
// Date/Time functions
543
function now(): SQL<Date>;
544
function extract(unit: string, date: SQL<Date> | AnyColumn): SQL<number>;
545
function dateAdd(date: SQL<Date> | AnyColumn, interval: string, unit: string): SQL<Date>;
546
function dateSub(date: SQL<Date> | AnyColumn, interval: string, unit: string): SQL<Date>;
547

548
// String functions
549
function concat(...values: (SQL<string> | AnyColumn | string)[]): SQL<string>;
550
function upper<T extends AnyColumn>(column: T): SQL<string>;
551
function lower<T extends AnyColumn>(column: T): SQL<string>;
552
function length<T extends AnyColumn>(column: T): SQL<number>;
553
function trim<T extends AnyColumn>(column: T): SQL<string>;
554
function substring<T extends AnyColumn>(column: T, start: number, length?: number): SQL<string>;
555

556
// Math functions
557
function abs<T extends AnyColumn>(column: T): SQL<number>;
558
function ceil<T extends AnyColumn>(column: T): SQL<number>;
559
function floor<T extends AnyColumn>(column: T): SQL<number>;
560
function round<T extends AnyColumn>(column: T, digits?: number): SQL<number>;
561

562
// JSON functions (PostgreSQL)
563
function jsonbExtract<T>(column: AnyColumn, path: string): SQL<T>;
564
function jsonbSet<T extends AnyColumn>(column: T, path: string, value: any): SQL<any>;
565
function jsonbArrayLength<T extends AnyColumn>(column: T): SQL<number>;
566

567
// Vector functions (PostgreSQL with pgvector)
568
function cosineDistance<T extends AnyColumn>(left: T, right: any): SQL<number>;
569
function l2Distance<T extends AnyColumn>(left: T, right: any): SQL<number>;
570
function innerProduct<T extends AnyColumn>(left: T, right: any): SQL<number>;
571

572
// Window functions
573
function rowNumber(): SQL<number>;
574
function rank(): SQL<number>;
575
function denseRank(): SQL<number>;
576
function lag<T extends AnyColumn>(column: T, offset?: number): SQL<InferData<T>>;
577
function lead<T extends AnyColumn>(column: T, offset?: number): SQL<InferData<T>>;
578
```