0
# Query Building
1

2
Powerful query builder providing fluent API for constructing complex SQL queries with type safety and database-agnostic syntax. TypeORM's query builder allows for dynamic query construction while maintaining type safety.
3

4
## Capabilities
5

6
### Select Query Builder
7

8
Primary query builder for SELECT operations with full SQL feature support.
9

10
```typescript { .api }
11
/**
12
 * Query builder for SELECT queries with fluent API
13
 * @template Entity - Entity type being queried
14
 */
15
class SelectQueryBuilder<Entity> {
16
  /**
17
   * Sets SELECT clause columns
18
   * @param selection - Columns to select
19
   * @returns SelectQueryBuilder for chaining
20
   */
21
  select(selection?: string | string[]): SelectQueryBuilder<Entity>;
22

23
  /**
24
   * Adds columns to existing SELECT clause
25
   * @param selection - Additional columns to select
26
   * @returns SelectQueryBuilder for chaining
27
   */
28
  addSelect(selection: string | string[]): SelectQueryBuilder<Entity>;
29

30
  /**
31
   * Sets FROM clause
32
   * @param entityTarget - Entity class or table name
33
   * @param alias - Table alias
34
   * @returns SelectQueryBuilder for chaining
35
   */
36
  from<T>(entityTarget: ObjectType<T> | EntitySchema<T> | string, alias: string): SelectQueryBuilder<T>;
37

38
  /**
39
   * Adds WHERE condition
40
   * @param where - WHERE condition string or object
41
   * @param parameters - Query parameters
42
   * @returns SelectQueryBuilder for chaining
43
   */
44
  where(
45
    where: string | ((qb: SelectQueryBuilder<Entity>) => string) | Brackets | ObjectLiteral | ObjectLiteral[],
46
    parameters?: ObjectLiteral
47
  ): SelectQueryBuilder<Entity>;
48

49
  /**
50
   * Adds AND WHERE condition
51
   * @param where - WHERE condition
52
   * @param parameters - Query parameters
53
   * @returns SelectQueryBuilder for chaining
54
   */
55
  andWhere(
56
    where: string | ((qb: SelectQueryBuilder<Entity>) => string) | Brackets | ObjectLiteral | ObjectLiteral[],
57
    parameters?: ObjectLiteral
58
  ): SelectQueryBuilder<Entity>;
59

60
  /**
61
   * Adds OR WHERE condition
62
   * @param where - WHERE condition
63
   * @param parameters - Query parameters
64
   * @returns SelectQueryBuilder for chaining
65
   */
66
  orWhere(
67
    where: string | ((qb: SelectQueryBuilder<Entity>) => string) | Brackets | ObjectLiteral | ObjectLiteral[],
68
    parameters?: ObjectLiteral
69
  ): SelectQueryBuilder<Entity>;
70

71
  /**
72
   * Adds INNER JOIN
73
   * @param property - Property to join or table name
74
   * @param alias - Join alias
75
   * @param condition - Join condition
76
   * @param parameters - Parameters for join condition
77
   * @returns SelectQueryBuilder for chaining
78
   */
79
  innerJoin(property: string, alias: string, condition?: string, parameters?: ObjectLiteral): SelectQueryBuilder<Entity>;
80

81
  /**
82
   * Adds LEFT JOIN
83
   * @param property - Property to join or table name
84
   * @param alias - Join alias
85
   * @param condition - Join condition
86
   * @param parameters - Parameters for join condition
87
   * @returns SelectQueryBuilder for chaining
88
   */
89
  leftJoin(property: string, alias: string, condition?: string, parameters?: ObjectLiteral): SelectQueryBuilder<Entity>;
90

91
  /**
92
   * Adds ORDER BY clause
93
   * @param sort - Column to sort by
94
   * @param order - Sort direction
95
   * @param nulls - NULL value ordering
96
   * @returns SelectQueryBuilder for chaining
97
   */
98
  orderBy(
99
    sort: string | ((alias: string) => string),
100
    order?: "ASC" | "DESC",
101
    nulls?: "NULLS FIRST" | "NULLS LAST"
102
  ): SelectQueryBuilder<Entity>;
103

104
  /**
105
   * Adds additional ORDER BY clause
106
   * @param sort - Column to sort by
107
   * @param order - Sort direction
108
   * @param nulls - NULL value ordering
109
   * @returns SelectQueryBuilder for chaining
110
   */
111
  addOrderBy(
112
    sort: string | ((alias: string) => string),
113
    order?: "ASC" | "DESC",
114
    nulls?: "NULLS FIRST" | "NULLS LAST"
115
  ): SelectQueryBuilder<Entity>;
116

117
  /**
118
   * Adds GROUP BY clause
119
   * @param groupBy - Columns to group by
120
   * @returns SelectQueryBuilder for chaining
121
   */
122
  groupBy(groupBy: string): SelectQueryBuilder<Entity>;
123

124
  /**
125
   * Adds additional GROUP BY clause
126
   * @param groupBy - Additional columns to group by
127
   * @returns SelectQueryBuilder for chaining
128
   */
129
  addGroupBy(groupBy: string): SelectQueryBuilder<Entity>;
130

131
  /**
132
   * Adds HAVING clause
133
   * @param having - HAVING condition
134
   * @param parameters - Condition parameters
135
   * @returns SelectQueryBuilder for chaining
136
   */
137
  having(having: string, parameters?: ObjectLiteral): SelectQueryBuilder<Entity>;
138

139
  /**
140
   * Adds AND HAVING condition
141
   * @param having - HAVING condition
142
   * @param parameters - Condition parameters
143
   * @returns SelectQueryBuilder for chaining
144
   */
145
  andHaving(having: string, parameters?: ObjectLiteral): SelectQueryBuilder<Entity>;
146

147
  /**
148
   * Adds OR HAVING condition
149
   * @param having - HAVING condition
150
   * @param parameters - Condition parameters
151
   * @returns SelectQueryBuilder for chaining
152
   */
153
  orHaving(having: string, parameters?: ObjectLiteral): SelectQueryBuilder<Entity>;
154

155
  /**
156
   * Sets LIMIT clause
157
   * @param limit - Maximum number of results
158
   * @returns SelectQueryBuilder for chaining
159
   */
160
  limit(limit?: number): SelectQueryBuilder<Entity>;
161

162
  /**
163
   * Sets OFFSET clause
164
   * @param offset - Number of results to skip
165
   * @returns SelectQueryBuilder for chaining
166
   */
167
  offset(offset?: number): SelectQueryBuilder<Entity>;
168

169
  /**
170
   * Executes query and returns single entity
171
   * @returns Promise resolving to entity or null
172
   */
173
  getOne(): Promise<Entity | null>;
174

175
  /**
176
   * Executes query and returns entity array
177
   * @returns Promise resolving to entity array
178
   */
179
  getMany(): Promise<Entity[]>;
180

181
  /**
182
   * Executes query and returns raw result object
183
   * @returns Promise resolving to raw result or null
184
   */
185
  getRawOne(): Promise<any>;
186

187
  /**
188
   * Executes query and returns raw result array
189
   * @returns Promise resolving to raw results array
190
   */
191
  getRawMany(): Promise<any[]>;
192

193
  /**
194
   * Executes query and returns entities with raw results
195
   * @returns Promise resolving to entities and raw results
196
   */
197
  getRawAndEntities(): Promise<{ entities: Entity[]; raw: any[] }>;
198

199
  /**
200
   * Gets count of matching entities
201
   * @returns Promise resolving to count
202
   */
203
  getCount(): Promise<number>;
204

205
  /**
206
   * Gets generated SQL query string
207
   * @returns SQL query string
208
   */
209
  getSql(): string;
210

211
  /**
212
   * Creates a subquery
213
   * @returns SelectQueryBuilder for subquery
214
   */
215
  subQuery(): SelectQueryBuilder<any>;
216

217
  /**
218
   * Sets query parameters
219
   * @param parameters - Parameters object
220
   * @returns SelectQueryBuilder for chaining
221
   */
222
  setParameters(parameters: ObjectLiteral): SelectQueryBuilder<Entity>;
223

224
  /**
225
   * Sets single query parameter
226
   * @param key - Parameter key
227
   * @param value - Parameter value
228
   * @returns SelectQueryBuilder for chaining
229
   */
230
  setParameter(key: string, value: any): SelectQueryBuilder<Entity>;
231
}
232
```
233

234
### Insert Query Builder
235

236
Builder for INSERT operations with support for bulk inserts and conflict resolution.
237

238
```typescript { .api }
239
/**
240
 * Query builder for INSERT operations
241
 * @template Entity - Entity type being inserted
242
 */
243
class InsertQueryBuilder<Entity> {
244
  /**
245
   * Sets INTO clause (target table/entity)
246
   * @param target - Entity target
247
   * @returns InsertQueryBuilder for chaining
248
   */
249
  into<T>(target: ObjectType<T> | EntitySchema<T> | string): InsertQueryBuilder<T>;
250

251
  /**
252
   * Sets VALUES clause with entity objects
253
   * @param values - Values to insert
254
   * @returns InsertQueryBuilder for chaining
255
   */
256
  values(values: QueryDeepPartialEntity<Entity> | QueryDeepPartialEntity<Entity>[]): InsertQueryBuilder<Entity>;
257

258
  /**
259
   * Sets ON CONFLICT clause for upsert operations
260
   * @param conflictPath - Conflict column(s)
261
   * @returns InsertQueryBuilder for chaining
262
   */
263
  onConflict(conflictPath: string): InsertQueryBuilder<Entity>;
264

265
  /**
266
   * Sets RETURNING clause (PostgreSQL)
267
   * @param returning - Columns to return
268
   * @returns InsertQueryBuilder for chaining
269
   */
270
  returning(returning: string | string[]): InsertQueryBuilder<Entity>;
271

272
  /**
273
   * Executes INSERT query
274
   * @returns Promise resolving to insert result
275
   */
276
  execute(): Promise<InsertResult>;
277

278
  /**
279
   * Gets generated SQL query string
280
   * @returns SQL query string
281
   */
282
  getSql(): string;
283
}
284

285
/**
286
 * Result of INSERT operation
287
 */
288
class InsertResult {
289
  /** Generated ID values */
290
  identifiers: ObjectLiteral[];
291
  /** Number of affected rows */
292
  affected?: number;
293
  /** Raw database result */
294
  raw: any;
295
}
296
```
297

298
### Update Query Builder
299

300
Builder for UPDATE operations with conditional updates and joins.
301

302
```typescript { .api }
303
/**
304
 * Query builder for UPDATE operations
305
 * @template Entity - Entity type being updated
306
 */
307
class UpdateQueryBuilder<Entity> {
308
  /**
309
   * Sets UPDATE clause (target table/entity)
310
   * @param target - Entity target
311
   * @returns UpdateQueryBuilder for chaining
312
   */
313
  update<T>(target: ObjectType<T> | EntitySchema<T> | string): UpdateQueryBuilder<T>;
314

315
  /**
316
   * Sets SET clause with values to update
317
   * @param values - Values to update
318
   * @returns UpdateQueryBuilder for chaining
319
   */
320
  set(values: QueryDeepPartialEntity<Entity>): UpdateQueryBuilder<Entity>;
321

322
  /**
323
   * Adds WHERE condition
324
   * @param where - WHERE condition
325
   * @param parameters - Query parameters
326
   * @returns UpdateQueryBuilder for chaining
327
   */
328
  where(
329
    where: string | Brackets | ObjectLiteral | ObjectLiteral[],
330
    parameters?: ObjectLiteral
331
  ): UpdateQueryBuilder<Entity>;
332

333
  /**
334
   * Adds AND WHERE condition
335
   * @param where - WHERE condition
336
   * @param parameters - Query parameters
337
   * @returns UpdateQueryBuilder for chaining
338
   */
339
  andWhere(
340
    where: string | Brackets | ObjectLiteral | ObjectLiteral[],
341
    parameters?: ObjectLiteral
342
  ): UpdateQueryBuilder<Entity>;
343

344
  /**
345
   * Sets RETURNING clause (PostgreSQL)
346
   * @param returning - Columns to return
347
   * @returns UpdateQueryBuilder for chaining
348
   */
349
  returning(returning: string | string[]): UpdateQueryBuilder<Entity>;
350

351
  /**
352
   * Executes UPDATE query
353
   * @returns Promise resolving to update result
354
   */
355
  execute(): Promise<UpdateResult>;
356

357
  /**
358
   * Gets generated SQL query string
359
   * @returns SQL query string
360
   */
361
  getSql(): string;
362
}
363

364
/**
365
 * Result of UPDATE operation
366
 */
367
class UpdateResult {
368
  /** Number of affected rows */
369
  affected?: number;
370
  /** Raw database result */
371
  raw: any;
372
  /** Updated entities (when using RETURNING) */
373
  entities?: Entity[];
374
}
375
```
376

377
### Delete Query Builder
378

379
Builder for DELETE operations with conditional deletes.
380

381
```typescript { .api }
382
/**
383
 * Query builder for DELETE operations
384
 * @template Entity - Entity type being deleted
385
 */
386
class DeleteQueryBuilder<Entity> {
387
  /**
388
   * Sets DELETE FROM clause
389
   * @param target - Entity target
390
   * @returns DeleteQueryBuilder for chaining
391
   */
392
  delete(): DeleteQueryBuilder<Entity>;
393

394
  /**
395
   * Sets FROM clause
396
   * @param target - Entity target
397
   * @returns DeleteQueryBuilder for chaining
398
   */
399
  from<T>(target: ObjectType<T> | EntitySchema<T> | string): DeleteQueryBuilder<T>;
400

401
  /**
402
   * Adds WHERE condition
403
   * @param where - WHERE condition
404
   * @param parameters - Query parameters
405
   * @returns DeleteQueryBuilder for chaining
406
   */
407
  where(
408
    where: string | Brackets | ObjectLiteral | ObjectLiteral[],
409
    parameters?: ObjectLiteral
410
  ): DeleteQueryBuilder<Entity>;
411

412
  /**
413
   * Sets RETURNING clause (PostgreSQL)
414
   * @param returning - Columns to return
415
   * @returns DeleteQueryBuilder for chaining
416
   */
417
  returning(returning: string | string[]): DeleteQueryBuilder<Entity>;
418

419
  /**
420
   * Executes DELETE query
421
   * @returns Promise resolving to delete result
422
   */
423
  execute(): Promise<DeleteResult>;
424

425
  /**
426
   * Gets generated SQL query string
427
   * @returns SQL query string
428
   */
429
  getSql(): string;
430
}
431

432
/**
433
 * Result of DELETE operation
434
 */
435
class DeleteResult {
436
  /** Number of affected rows */
437
  affected?: number;
438
  /** Raw database result */
439
  raw: any;
440
}
441
```
442

443
### Query Expression Utilities
444

445
Helper classes for building complex WHERE conditions.
446

447
```typescript { .api }
448
/**
449
 * Creates grouped WHERE conditions with parentheses
450
 */
451
class Brackets {
452
  constructor(whereFactory: (qb: WhereExpressionBuilder) => any);
453
}
454

455
/**
456
 * Creates negated grouped WHERE conditions
457
 */
458
class NotBrackets {
459
  constructor(whereFactory: (qb: WhereExpressionBuilder) => any);
460
}
461

462
/**
463
 * Interface for WHERE expression building
464
 */
465
interface WhereExpression {
466
  where(where: Function | string | Brackets, parameters?: ObjectLiteral): this;
467
  andWhere(where: Function | string | Brackets, parameters?: ObjectLiteral): this;
468
  orWhere(where: Function | string | Brackets, parameters?: ObjectLiteral): this;
469
}
470
```
471

472
**Query Builder Examples:**
473

474
```typescript
475
import { DataSource, SelectQueryBuilder, Brackets } from "typeorm";
476

477
const dataSource = new DataSource(/* options */);
478
await dataSource.initialize();
479

480
// Basic SELECT with joins
481
const users = await dataSource
482
  .getRepository(User)
483
  .createQueryBuilder("user")
484
  .innerJoinAndSelect("user.posts", "post")
485
  .leftJoinAndSelect("user.profile", "profile")
486
  .where("user.active = :active", { active: true })
487
  .orderBy("user.createdAt", "DESC")
488
  .limit(10)
489
  .getMany();
490

491
// Complex WHERE conditions with brackets
492
const results = await dataSource
493
  .getRepository(User)
494
  .createQueryBuilder("user")
495
  .where(new Brackets(qb => {
496
    qb.where("user.firstName = :firstName", { firstName: "John" })
497
      .orWhere("user.lastName = :lastName", { lastName: "Doe" });
498
  }))
499
  .andWhere("user.active = :active", { active: true })
500
  .getMany();
501

502
// Subquery
503
const averageAge = await dataSource
504
  .getRepository(User)
505
  .createQueryBuilder("user")
506
  .select("AVG(user.age)", "avg")
507
  .getRawOne();
508

509
const olderUsers = await dataSource
510
  .getRepository(User)
511
  .createQueryBuilder("user")
512
  .where(`user.age > (${averageAge.avg})`)
513
  .getMany();
514

515
// INSERT with query builder
516
await dataSource
517
  .createQueryBuilder()
518
  .insert()
519
  .into(User)
520
  .values([
521
    { firstName: "John", lastName: "Doe" },
522
    { firstName: "Jane", lastName: "Smith" }
523
  ])
524
  .execute();
525

526
// UPDATE with query builder
527
await dataSource
528
  .createQueryBuilder()
529
  .update(User)
530
  .set({ active: false })
531
  .where("lastLoginAt < :date", { date: thirtyDaysAgo })
532
  .execute();
533

534
// DELETE with query builder
535
await dataSource
536
  .createQueryBuilder()
537
  .delete()
538
  .from(User)
539
  .where("active = :active", { active: false })
540
  .execute();
541
```