0
# Query Operators
1

2
Advanced query operators for complex WHERE clauses and raw SQL execution for custom database operations.
3

4
## Capabilities
5

6
### Comparison Operators
7

8
Basic comparison operators for WHERE clauses.
9

10
```typescript { .api }
11
interface OpTypes {
12
  /** Equals (=) */
13
  eq: symbol;
14
  /** Not equals (!=) */
15
  ne: symbol;
16
  /** Greater than (>) */
17
  gt: symbol;
18
  /** Greater than or equal (>=) */
19
  gte: symbol;
20
  /** Less than (<) */
21
  lt: symbol;
22
  /** Less than or equal (<=) */
23
  lte: symbol;
24
  /** IS (for null checks) */
25
  is: symbol;
26
  /** NOT */
27
  not: symbol;
28
  /** BETWEEN */
29
  between: symbol;
30
  /** NOT BETWEEN */
31
  notBetween: symbol;
32
  /** IN */
33
  in: symbol;
34
  /** NOT IN */
35
  notIn: symbol;
36
}
37

38
const Op: OpTypes;
39
```
40

41
**Usage Examples:**
42

43
```typescript
44
import { Op } from "sequelize";
45

46
// Equals
47
const users = await User.findAll({
48
  where: {
49
    age: { [Op.eq]: 25 }
50
    // Or simply: age: 25
51
  }
52
});
53

54
// Not equals
55
const users = await User.findAll({
56
  where: {
57
    status: { [Op.ne]: 'inactive' }
58
  }
59
});
60

61
// Greater than / Less than
62
const users = await User.findAll({
63
  where: {
64
    age: { [Op.gt]: 18 },
65
    salary: { [Op.lte]: 50000 }
66
  }
67
});
68

69
// Between
70
const users = await User.findAll({
71
  where: {
72
    age: { [Op.between]: [18, 65] },
73
    salary: { [Op.notBetween]: [30000, 40000] }
74
  }
75
});
76

77
// In / Not In
78
const users = await User.findAll({
79
  where: {
80
    role: { [Op.in]: ['admin', 'moderator'] },
81
    status: { [Op.notIn]: ['banned', 'suspended'] }
82
  }
83
});
84

85
// IS / NOT (for null values)
86
const users = await User.findAll({
87
  where: {
88
    deletedAt: { [Op.is]: null },
89
    profilePicture: { [Op.not]: null }
90
  }
91
});
92
```
93

94
### String Operators
95

96
Operators for string pattern matching and text search.
97

98
```typescript { .api }
99
interface OpTypes {
100
  /** LIKE */
101
  like: symbol;
102
  /** NOT LIKE */
103
  notLike: symbol;
104
  /** ILIKE (case insensitive, PostgreSQL) */
105
  iLike: symbol;
106
  /** NOT ILIKE */
107
  notILike: symbol;
108
  /** LIKE 'value%' */
109
  startsWith: symbol;
110
  /** LIKE '%value' */
111
  endsWith: symbol;
112
  /** LIKE '%value%' */
113
  substring: symbol;
114
  /** REGEXP (MySQL/PostgreSQL) */
115
  regexp: symbol;
116
  /** NOT REGEXP */
117
  notRegexp: symbol;
118
  /** ~* (case insensitive regexp, PostgreSQL) */
119
  iRegexp: symbol;
120
  /** !~* (case insensitive not regexp, PostgreSQL) */
121
  notIRegexp: symbol;
122
}
123
```
124

125
**Usage Examples:**
126

127
```typescript
128
// LIKE patterns
129
const users = await User.findAll({
130
  where: {
131
    firstName: { [Op.like]: 'John%' },      // Starts with "John"
132
    lastName: { [Op.notLike]: '%test%' },   // Doesn't contain "test"
133
    email: { [Op.iLike]: '%@COMPANY.COM' }  // Case insensitive (PostgreSQL)
134
  }
135
});
136

137
// Convenience string operators
138
const users = await User.findAll({
139
  where: {
140
    firstName: { [Op.startsWith]: 'John' },     // LIKE 'John%'
141
    lastName: { [Op.endsWith]: 'son' },         // LIKE '%son'
142
    bio: { [Op.substring]: 'developer' }        // LIKE '%developer%'
143
  }
144
});
145

146
// Regular expressions
147
const users = await User.findAll({
148
  where: {
149
    phone: { [Op.regexp]: '^\\d{3}-\\d{3}-\\d{4}$' },  // MySQL/PostgreSQL
150
    username: { [Op.iRegexp]: '^[a-z]+$' }             // PostgreSQL only
151
  }
152
});
153
```
154

155
### Logical Operators
156

157
Operators for combining multiple conditions.
158

159
```typescript { .api }
160
interface OpTypes {
161
  /** AND */
162
  and: symbol;
163
  /** OR */
164
  or: symbol;
165
}
166
```
167

168
**Usage Examples:**
169

170
```typescript
171
// AND (implicit by default)
172
const users = await User.findAll({
173
  where: {
174
    age: { [Op.gte]: 18 },
175
    isActive: true
176
    // Implicit AND between conditions
177
  }
178
});
179

180
// Explicit AND
181
const users = await User.findAll({
182
  where: {
183
    [Op.and]: [
184
      { age: { [Op.gte]: 18 } },
185
      { isActive: true }
186
    ]
187
  }
188
});
189

190
// OR
191
const users = await User.findAll({
192
  where: {
193
    [Op.or]: [
194
      { firstName: 'John' },
195
      { lastName: 'Doe' }
196
    ]
197
  }
198
});
199

200
// Complex combinations
201
const users = await User.findAll({
202
  where: {
203
    [Op.and]: [
204
      { isActive: true },
205
      {
206
        [Op.or]: [
207
          { role: 'admin' },
208
          { 
209
            [Op.and]: [
210
              { role: 'user' },
211
              { verified: true }
212
            ]
213
          }
214
        ]
215
      }
216
    ]
217
  }
218
});
219
```
220

221
### Array Operators
222

223
Operators for array operations (PostgreSQL primarily).
224

225
```typescript { .api }
226
interface OpTypes {
227
  /** @> (array contains) */
228
  contains: symbol;
229
  /** <@ (array contained by) */
230
  contained: symbol;
231
  /** && (array overlap) */
232
  overlap: symbol;
233
  /** ANY */
234
  any: symbol;
235
  /** ALL */
236
  all: symbol;
237
}
238
```
239

240
**Usage Examples:**
241

242
```typescript
243
// PostgreSQL array operations
244
const users = await User.findAll({
245
  where: {
246
    tags: { [Op.contains]: ['developer', 'javascript'] },  // Array contains values
247
    skills: { [Op.contained]: ['js', 'node', 'react'] },   // Array is subset
248
    interests: { [Op.overlap]: ['music', 'sports'] }       // Arrays have common elements
249
  }
250
});
251

252
// ANY operator
253
const users = await User.findAll({
254
  where: {
255
    age: { [Op.gt]: { [Op.any]: [18, 21, 25] } }  // age > ANY(18,21,25)
256
  }
257
});
258

259
// ALL operator  
260
const users = await User.findAll({
261
  where: {
262
    score: { [Op.gt]: { [Op.all]: [80, 85, 90] } }  // score > ALL(80,85,90)
263
  }
264
});
265
```
266

267
### Range Operators
268

269
Operators for range types (PostgreSQL).
270

271
```typescript { .api }
272
interface OpTypes {
273
  /** -|- (adjacent ranges) */
274
  adjacent: symbol;
275
  /** << (strictly left of) */
276
  strictLeft: symbol;
277
  /** >> (strictly right of) */
278
  strictRight: symbol;
279
  /** &< (does not extend right of) */
280
  noExtendRight: symbol;
281
  /** &> (does not extend left of) */
282
  noExtendLeft: symbol;
283
}
284
```
285

286
**Usage Example:**
287

288
```typescript
289
// PostgreSQL range operations
290
const events = await Event.findAll({
291
  where: {
292
    dateRange: { [Op.overlap]: '[2023-01-01,2023-12-31)' },
293
    timeSlot: { [Op.adjacent]: '[09:00,10:00)' }
294
  }
295
});
296
```
297

298
### Special Operators
299

300
Special operators for advanced use cases.
301

302
```typescript { .api }
303
interface OpTypes {
304
  /** Column reference */
305
  col: symbol;
306
  /** @@ (full text search, PostgreSQL) */
307
  match: symbol;
308
  /** VALUES clause */
309
  values: symbol;
310
  /** Internal placeholder */
311
  placeholder: symbol;
312
}
313
```
314

315
**Usage Examples:**
316

317
```typescript
318
// Column references
319
const users = await User.findAll({
320
  where: {
321
    firstName: { [Op.col]: 'lastName' }  // WHERE firstName = lastName
322
  }
323
});
324

325
// Full text search (PostgreSQL)
326
const articles = await Article.findAll({
327
  where: {
328
    searchVector: { 
329
      [Op.match]: sequelize.fn('plainto_tsquery', 'javascript programming')
330
    }
331
  }
332
});
333

334
// VALUES clause
335
const users = await User.findAll({
336
  where: {
337
    id: { [Op.in]: { [Op.values]: [[1], [2], [3]] } }
338
  }
339
});
340
```
341

342
### Raw SQL Utilities
343

344
Utilities for building custom SQL expressions.
345

346
```typescript { .api }
347
/**
348
 * Create SQL function call
349
 * @param fn - Function name
350
 * @param args - Function arguments
351
 * @returns Fn instance
352
 */
353
fn(fn: string, ...args: any[]): Fn;
354

355
/**
356
 * Reference table column
357
 * @param col - Column name (can include table prefix)
358
 * @returns Col instance
359
 */
360
col(col: string): Col;
361

362
/**
363
 * Create raw SQL literal
364
 * @param val - Raw SQL string
365
 * @returns Literal instance
366
 */
367
literal(val: string): Literal;
368

369
/**
370
 * Type casting
371
 * @param val - Value to cast
372
 * @param type - Target type
373
 * @returns Cast instance
374
 */
375
cast(val: any, type: string): Cast;
376

377
/**
378
 * WHERE condition builder
379
 * @param attr - Attribute or expression
380
 * @param comparator - Comparison operator
381
 * @param logic - Value to compare against
382
 * @returns Where instance
383
 */
384
where(attr: any, comparator: any, logic?: any): Where;
385

386
/**
387
 * JSON path operations
388
 * @param conditionsOrPath - JSON path or conditions
389
 * @param value - Value to compare (if path provided)
390
 * @returns JSON query condition
391
 */
392
json(conditionsOrPath: string | object, value?: any): object;
393
```
394

395
**Usage Examples:**
396

397
```typescript
398
import { fn, col, literal, cast, where, json } from "sequelize";
399

400
// SQL functions
401
const users = await User.findAll({
402
  where: {
403
    age: { [Op.gt]: fn('AVG', col('age')) }
404
  }
405
});
406

407
// Column references
408
const users = await User.findAll({
409
  where: where(col('user.created_at'), Op.gt, col('user.updated_at'))
410
});
411

412
// Raw SQL literals
413
const users = await User.findAll({
414
  where: {
415
    balance: { [Op.gt]: literal('(SELECT AVG(balance) FROM users)') }
416
  }
417
});
418

419
// Type casting
420
const users = await User.findAll({
421
  where: {
422
    score: { [Op.gt]: cast('85.5', 'INTEGER') }
423
  }
424
});
425

426
// JSON operations
427
const users = await User.findAll({
428
  where: {
429
    preferences: json('theme', 'dark'),
430
    metadata: json({ 'user.settings.notifications': true })
431
  }
432
});
433

434
// Complex example combining utilities
435
const users = await User.findAll({
436
  where: where(
437
    fn('DATE', col('created_at')),
438
    Op.eq,
439
    literal('CURRENT_DATE')
440
  ),
441
  attributes: [
442
    'id',
443
    'firstName',
444
    [fn('COUNT', col('posts.id')), 'postCount'],
445
    [cast(col('age'), 'TEXT'), 'ageString']
446
  ],
447
  include: [{
448
    model: Post,
449
    attributes: []
450
  }],
451
  group: ['user.id']
452
});
453
```
454

455
### Raw Query Execution
456

457
Execute raw SQL queries directly.
458

459
```typescript { .api }
460
/**
461
 * Execute raw SQL query
462
 * @param sql - SQL query string
463
 * @param options - Query execution options
464
 * @returns Promise resolving to query results
465
 */
466
query(sql: string, options?: QueryOptions): Promise<any>;
467

468
interface QueryOptions {
469
  /** Bind parameters (named placeholders) */
470
  bind?: { [key: string]: any };
471
  /** Replacement parameters (positional placeholders) */
472
  replacements?: { [key: string]: any } | any[];
473
  /** Query type */
474
  type?: QueryTypes;
475
  /** Model to map results to */
476
  model?: typeof Model;
477
  /** Map results to model instances */
478
  mapToModel?: boolean;
479
  /** Return raw results */
480
  raw?: boolean;
481
  /** Nest results */
482
  nest?: boolean;
483
  /** Plain objects instead of instances */
484
  plain?: boolean;
485
  /** Transaction */
486
  transaction?: Transaction;
487
  /** Query logging */
488
  logging?: boolean | ((sql: string, timing?: number) => void);
489
}
490
```
491

492
**Usage Examples:**
493

494
```typescript
495
// Basic raw query
496
const results = await sequelize.query(
497
  'SELECT * FROM users WHERE age > 25',
498
  { type: QueryTypes.SELECT }
499
);
500

501
// With replacements
502
const results = await sequelize.query(
503
  'SELECT * FROM users WHERE age > :age AND city = :city',
504
  {
505
    replacements: { age: 25, city: 'New York' },
506
    type: QueryTypes.SELECT
507
  }
508
);
509

510
// With bind parameters (safer for repeated queries)
511
const results = await sequelize.query(
512
  'SELECT * FROM users WHERE age > $1 AND city = $2',
513
  {
514
    bind: [25, 'New York'],
515
    type: QueryTypes.SELECT
516
  }
517
);
518

519
// Map to model instances
520
const users = await sequelize.query(
521
  'SELECT * FROM users WHERE active = true',
522
  {
523
    model: User,
524
    mapToModel: true
525
  }
526
);
527

528
// Insert/Update/Delete operations
529
await sequelize.query(
530
  'UPDATE users SET last_login = NOW() WHERE id = :userId',
531
  {
532
    replacements: { userId: 123 },
533
    type: QueryTypes.UPDATE
534
  }
535
);
536
```