0
# Querying
1

2
Data querying, creation, updating, and deletion operations with advanced filtering, ordering, and aggregation capabilities.
3

4
## Capabilities
5

6
### Finding Records
7

8
Query operations for retrieving data from the database.
9

10
```typescript { .api }
11
/**
12
 * Find all records matching the query
13
 * @param options - Query options
14
 * @returns Promise resolving to array of model instances
15
 */
16
static findAll(options?: FindOptions): Promise<Model[]>;
17

18
/**
19
 * Find one record matching the query
20
 * @param options - Query options
21
 * @returns Promise resolving to model instance or null
22
 */
23
static findOne(options?: FindOptions): Promise<Model | null>;
24

25
/**
26
 * Find record by primary key
27
 * @param identifier - Primary key value
28
 * @param options - Query options
29
 * @returns Promise resolving to model instance or null
30
 */
31
static findByPk(identifier: Identifier, options?: Omit<FindOptions, 'where'>): Promise<Model | null>;
32

33
/**
34
 * Find and count all records
35
 * @param options - Query options
36
 * @returns Promise resolving to object with rows and count
37
 */
38
static findAndCountAll(options?: FindOptions): Promise<{ rows: Model[]; count: number }>;
39

40
interface FindOptions {
41
  /** WHERE clause conditions */
42
  where?: WhereOptions;
43
  /** Attributes to select */
44
  attributes?: FindAttributeOptions;
45
  /** Include related models */
46
  include?: Includeable | Includeable[];
47
  /** ORDER BY clause */
48
  order?: Order;
49
  /** GROUP BY clause */
50
  group?: GroupOption;
51
  /** HAVING clause */
52
  having?: WhereOptions;
53
  /** LIMIT clause */
54
  limit?: number;
55
  /** OFFSET clause */
56
  offset?: number;
57
  /** Subquery options */
58
  subQuery?: boolean;
59
  /** Include paranoid (soft-deleted) records */
60
  paranoid?: boolean;
61
  /** Use raw queries */
62
  raw?: boolean;
63
  /** Transaction */
64
  transaction?: Transaction;
65
  /** Query logging */
66
  logging?: boolean | ((sql: string, timing?: number) => void);
67
}
68

69
type Identifier = string | number | Buffer;
70
type Order = string | Fn | Col | [string | Fn | Col, string] | [string | Fn | Col, string, string];
71
type GroupOption = string | Fn | Col | (string | Fn | Col)[];
72
```
73

74
**Usage Examples:**
75

76
```typescript
77
import { Op } from "sequelize";
78

79
// Find all users
80
const users = await User.findAll();
81

82
// Find with WHERE conditions
83
const activeUsers = await User.findAll({
84
  where: {
85
    isActive: true,
86
    age: {
87
      [Op.gte]: 18
88
    }
89
  }
90
});
91

92
// Find with complex conditions
93
const users = await User.findAll({
94
  where: {
95
    [Op.or]: [
96
      { firstName: 'John' },
97
      { lastName: 'Doe' }
98
    ],
99
    email: {
100
      [Op.like]: '%@company.com'
101
    }
102
  }
103
});
104

105
// Find with specific attributes
106
const users = await User.findAll({
107
  attributes: ['id', 'firstName', 'email']
108
});
109

110
// Find with ordering and pagination
111
const users = await User.findAll({
112
  order: [['createdAt', 'DESC']],
113
  limit: 10,
114
  offset: 20
115
});
116

117
// Find by primary key
118
const user = await User.findByPk(123);
119

120
// Find one with conditions
121
const user = await User.findOne({
122
  where: { email: 'user@example.com' }
123
});
124

125
// Find and count
126
const result = await User.findAndCountAll({
127
  where: { isActive: true },
128
  limit: 10
129
});
130
console.log(`Found ${result.count} users, showing ${result.rows.length}`);
131
```
132

133
### Creating Records
134

135
Operations for inserting new data into the database.
136

137
```typescript { .api }
138
/**
139
 * Build new model instance without saving
140
 * @param values - Attribute values
141
 * @param options - Build options
142
 * @returns New model instance
143
 */
144
static build(values?: CreationAttributes, options?: BuildOptions): Model;
145

146
/**
147
 * Create and save new record
148
 * @param values - Attribute values
149
 * @param options - Create options
150
 * @returns Promise resolving to created model instance
151
 */
152
static create(values?: CreationAttributes, options?: CreateOptions): Promise<Model>;
153

154
/**
155
 * Create multiple records in bulk
156
 * @param records - Array of attribute objects
157
 * @param options - Bulk create options
158
 * @returns Promise resolving to array of created instances
159
 */
160
static bulkCreate(records: CreationAttributes[], options?: BulkCreateOptions): Promise<Model[]>;
161

162
/**
163
 * Find existing record or build new one (without saving)
164
 * @param options - Find or build options
165
 * @returns Promise resolving to [instance, created] tuple
166
 */
167
static findOrBuild(options: FindOrBuildOptions): Promise<[Model, boolean]>;
168

169
/**
170
 * Find existing record or create new one
171
 * @param options - Find or create options
172
 * @returns Promise resolving to [instance, created] tuple
173
 */
174
static findOrCreate(options: FindOrCreateOptions): Promise<[Model, boolean]>;
175

176
interface CreateOptions {
177
  /** Fields to include in INSERT */
178
  fields?: string[];
179
  /** Skip validation */
180
  validate?: boolean;
181
  /** Include hooks */
182
  hooks?: boolean;
183
  /** Transaction */
184
  transaction?: Transaction;
185
  /** Query logging */
186
  logging?: boolean | ((sql: string, timing?: number) => void);
187
}
188

189
interface BulkCreateOptions extends CreateOptions {
190
  /** Skip duplicate errors */
191
  ignoreDuplicates?: boolean;
192
  /** Update on duplicate key */
193
  updateOnDuplicate?: string[];
194
  /** Return created instances */
195
  returning?: boolean | string[];
196
}
197

198
interface FindOrBuildOptions {
199
  /** WHERE clause for finding record */
200
  where: WhereOptions;
201
  /** Default values for building new instance */
202
  defaults?: CreationAttributes;
203
  /** Transaction */
204
  transaction?: Transaction;
205
}
206
```
207

208
**Usage Examples:**
209

210
```typescript
211
// Build instance without saving
212
const user = User.build({
213
  firstName: 'John',
214
  lastName: 'Doe',
215
  email: 'john@example.com'
216
});
217
await user.save();
218

219
// Create and save in one step
220
const user = await User.create({
221
  firstName: 'Jane',
222
  lastName: 'Smith',
223
  email: 'jane@example.com'
224
});
225

226
// Bulk create multiple records
227
const users = await User.bulkCreate([
228
  { firstName: 'Alice', email: 'alice@example.com' },
229
  { firstName: 'Bob', email: 'bob@example.com' },
230
  { firstName: 'Charlie', email: 'charlie@example.com' }
231
]);
232

233
// Find or build (without saving)
234
const [user, built] = await User.findOrBuild({
235
  where: { email: 'user@example.com' },
236
  defaults: {
237
    firstName: 'New',
238
    lastName: 'User'
239
  }
240
});
241

242
if (built) {
243
  console.log('Built new user instance (not saved)');
244
  await user.save(); // Manual save required
245
} else {
246
  console.log('Found existing user');
247
}
248

249
// Find or create
250
const [user2, created] = await User.findOrCreate({
251
  where: { email: 'user2@example.com' },
252
  defaults: {
253
    firstName: 'New',
254
    lastName: 'User'
255
  }
256
});
257

258
if (created) {
259
  console.log('Created new user');
260
} else {
261
  console.log('Found existing user');
262
}
263
```
264

265
### Updating Records
266

267
Operations for modifying existing data in the database.
268

269
```typescript { .api }
270
/**
271
 * Update multiple records
272
 * @param values - Values to update
273
 * @param options - Update options
274
 * @returns Promise resolving to [affectedCount, affectedRows]
275
 */
276
static update(values: Partial<Attributes>, options: UpdateOptions): Promise<[number, Model[]]>;
277

278
/**
279
 * Update or create record (upsert)
280
 * @param values - Values to insert or update
281
 * @param options - Upsert options
282
 * @returns Promise resolving to [instance, created] tuple
283
 */
284
static upsert(values: CreationAttributes, options?: UpsertOptions): Promise<[Model, boolean]>;
285

286
/**
287
 * Increment numeric fields
288
 * @param fields - Fields to increment
289
 * @param options - Increment options
290
 * @returns Promise resolving to updated instances
291
 */
292
static increment(fields: string | string[] | { [key: string]: number }, options: IncrementDecrementOptions): Promise<Model[]>;
293

294
/**
295
 * Decrement numeric fields
296
 * @param fields - Fields to decrement
297
 * @param options - Decrement options
298
 * @returns Promise resolving to updated instances
299
 */
300
static decrement(fields: string | string[] | { [key: string]: number }, options: IncrementDecrementOptions): Promise<Model[]>;
301

302
interface UpdateOptions {
303
  /** WHERE clause for records to update */
304
  where: WhereOptions;
305
  /** Include paranoid records */
306
  paranoid?: boolean;
307
  /** Fields to update */
308
  fields?: string[];
309
  /** Skip validation */
310
  validate?: boolean;
311
  /** Include hooks */
312
  hooks?: boolean;
313
  /** Return updated instances */
314
  returning?: boolean | string[];
315
  /** Transaction */
316
  transaction?: Transaction;
317
}
318
```
319

320
**Usage Examples:**
321

322
```typescript
323
// Update multiple records
324
const [affectedCount] = await User.update(
325
  { isActive: false },
326
  { where: { lastLoginAt: { [Op.lt]: new Date('2023-01-01') } } }
327
);
328

329
// Upsert (insert or update)
330
const [user, created] = await User.upsert({
331
  id: 1,
332
  firstName: 'John',
333
  email: 'john@example.com'
334
});
335

336
// Increment fields
337
await User.increment(['loginCount'], {
338
  where: { id: 1 }
339
});
340

341
// Increment with custom amounts
342
await User.increment({
343
  loginCount: 1,
344
  score: 10
345
}, {
346
  where: { id: 1 }
347
});
348
```
349

350
### Deleting Records
351

352
Operations for removing data from the database.
353

354
```typescript { .api }
355
/**
356
 * Delete records from database
357
 * @param options - Destroy options
358
 * @returns Promise resolving to number of deleted records
359
 */
360
static destroy(options: DestroyOptions): Promise<number>;
361

362
/**
363
 * Restore soft-deleted records
364
 * @param options - Restore options
365
 * @returns Promise resolving when records are restored
366
 */
367
static restore(options: RestoreOptions): Promise<void>;
368

369
/**
370
 * Truncate table (delete all records)
371
 * @param options - Truncate options
372
 * @returns Promise resolving when table is truncated
373
 */
374
static truncate(options?: TruncateOptions): Promise<void>;
375

376
interface DestroyOptions {
377
  /** WHERE clause for records to delete */
378
  where?: WhereOptions;
379
  /** Force delete (ignore paranoid) */
380
  force?: boolean;
381
  /** Include hooks */
382
  hooks?: boolean;
383
  /** Truncate instead of delete */
384
  truncate?: boolean;
385
  /** CASCADE delete */
386
  cascade?: boolean;
387
  /** Transaction */
388
  transaction?: Transaction;
389
}
390

391
interface RestoreOptions {
392
  /** WHERE clause for records to restore */
393
  where?: WhereOptions;
394
  /** Include hooks */
395
  hooks?: boolean;
396
  /** Transaction */
397
  transaction?: Transaction;
398
}
399
```
400

401
**Usage Examples:**
402

403
```typescript
404
// Delete records matching condition
405
const deletedCount = await User.destroy({
406
  where: {
407
    isActive: false,
408
    lastLoginAt: { [Op.lt]: new Date('2022-01-01') }
409
  }
410
});
411

412
// Force delete (ignore paranoid mode)
413
await User.destroy({
414
  where: { id: 1 },
415
  force: true
416
});
417

418
// Restore soft-deleted records
419
await User.restore({
420
  where: { id: 1 }
421
});
422

423
// Truncate table
424
await User.truncate();
425
```
426

427
### Aggregation Operations
428

429
Operations for calculating aggregate values.
430

431
```typescript { .api }
432
/**
433
 * Count records
434
 * @param options - Count options
435
 * @returns Promise resolving to count
436
 */
437
static count(options?: CountOptions): Promise<number>;
438

439
/**
440
 * Find maximum value
441
 * @param field - Field to find max
442
 * @param options - Aggregate options
443
 * @returns Promise resolving to maximum value
444
 */
445
static max(field: string, options?: AggregateOptions): Promise<any>;
446

447
/**
448
 * Find minimum value
449
 * @param field - Field to find min
450
 * @param options - Aggregate options
451
 * @returns Promise resolving to minimum value
452
 */
453
static min(field: string, options?: AggregateOptions): Promise<any>;
454

455
/**
456
 * Sum values
457
 * @param field - Field to sum
458
 * @param options - Aggregate options
459
 * @returns Promise resolving to sum
460
 */
461
static sum(field: string, options?: AggregateOptions): Promise<number>;
462

463
interface CountOptions {
464
  /** WHERE clause */
465
  where?: WhereOptions;
466
  /** Include related models */
467
  include?: Includeable | Includeable[];
468
  /** Count distinct values */
469
  distinct?: boolean;
470
  /** Field to count */
471
  col?: string;
472
}
473

474
interface AggregateOptions {
475
  /** WHERE clause */
476
  where?: WhereOptions;
477
  /** Include related models */
478
  include?: Includeable | Includeable[];
479
  /** Data type for result */
480
  dataType?: DataType;
481
}
482
```
483

484
**Usage Examples:**
485

486
```typescript
487
// Count all users
488
const userCount = await User.count();
489

490
// Count with conditions
491
const activeUserCount = await User.count({
492
  where: { isActive: true }
493
});
494

495
// Count distinct values
496
const uniqueEmails = await User.count({
497
  col: 'email',
498
  distinct: true
499
});
500

501
// Aggregate functions
502
const maxAge = await User.max('age');
503
const minAge = await User.min('age');
504
const totalSalary = await User.sum('salary', {
505
  where: { department: 'Engineering' }
506
});
507
```
508

509
### Advanced Querying
510

511
Complex query patterns and raw SQL execution.
512

513
```typescript { .api }
514
/**
515
 * Execute raw SQL query
516
 * @param sql - SQL query string
517
 * @param options - Query options
518
 * @returns Promise resolving to query results
519
 */
520
query(sql: string, options?: QueryOptions): Promise<any>;
521

522
interface QueryOptions {
523
  /** Query type */
524
  type?: QueryTypes;
525
  /** Bind parameters */
526
  bind?: { [key: string]: any };
527
  /** Replacement parameters */
528
  replacements?: { [key: string]: any };
529
  /** Model to map results to */
530
  model?: typeof Model;
531
  /** Map to instances */
532
  mapToModel?: boolean;
533
  /** Include raw results */
534
  raw?: boolean;
535
  /** Transaction */
536
  transaction?: Transaction;
537
}
538
```
539

540
**Usage Example:**
541

542
```typescript
543
// Raw SQL query
544
const results = await sequelize.query(
545
  'SELECT * FROM users WHERE age > :age',
546
  {
547
    replacements: { age: 25 },
548
    type: QueryTypes.SELECT
549
  }
550
);
551

552
// Raw query with model mapping
553
const users = await sequelize.query(
554
  'SELECT * FROM users WHERE department = ?',
555
  {
556
    replacements: ['Engineering'],
557
    model: User,
558
    mapToModel: true
559
  }
560
);
561
```