0
# Expression Builders
1

2
Query expression builders for raw SQL, column references, values, and functions.
3

4
## Capabilities
5

6
### Raw Expression Builder
7

8
Create raw SQL expressions with parameter binding.
9

10
```javascript { .api }
11
/**
12
 * Create a raw SQL expression
13
 * @param sql - SQL string with optional parameter placeholders
14
 * @param bindings - Values to bind to placeholders
15
 * @returns RawBuilder instance
16
 */
17
function raw(sql: string, ...bindings: any[]): RawBuilder;
18

19
interface RawBuilder {
20
  /** Add alias to raw expression */
21
  as(alias: string): RawBuilder;
22
}
23
```
24

25
**Usage Examples:**
26

27
```javascript
28
const { raw } = require('objection');
29

30
// Basic raw expression
31
const people = await Person.query()
32
  .select(raw('count(*) as total'))
33
  .groupBy('department');
34

35
// Raw with bindings
36
const people = await Person.query()
37
  .where(raw('age > ?', 18))
38
  .orderBy(raw('random()'));
39

40
// Raw in select with alias
41
const results = await Person.query()
42
  .select('firstName', 'lastName')
43
  .select(raw('age * 2').as('doubleAge'));
44

45
// Raw in joins
46
const results = await Person.query()
47
  .join('pets', raw('persons.id = pets.owner_id AND pets.species = ?', 'dog'));
48

49
// Complex raw expressions
50
const stats = await Person.query()
51
  .select(raw(`
52
    CASE 
53
      WHEN age < 18 THEN 'minor'
54
      WHEN age < 65 THEN 'adult'
55
      ELSE 'senior'
56
    END as ageGroup
57
  `))
58
  .groupBy(raw('ageGroup'));
59
```
60

61
### Reference Builder
62

63
Create column references for queries.
64

65
```javascript { .api }
66
/**
67
 * Create a column reference
68
 * @param expression - Column reference expression
69
 * @returns ReferenceBuilder instance
70
 */
71
function ref(expression: string): ReferenceBuilder;
72

73
interface ReferenceBuilder {
74
  /** Specify table for the reference */
75
  from(tableReference: string): ReferenceBuilder;
76
  
77
  /** Add alias to reference */
78
  as(alias: string): ReferenceBuilder;
79
  
80
  /** Cast reference to text type */
81
  castText(): ReferenceBuilder;
82
  
83
  /** Cast reference to integer type */
84
  castInt(): ReferenceBuilder;
85
  
86
  /** Cast reference to big integer type */
87
  castBigInt(): ReferenceBuilder;
88
  
89
  /** Cast reference to float type */
90
  castFloat(): ReferenceBuilder;
91
  
92
  /** Cast reference to decimal type */
93
  castDecimal(): ReferenceBuilder;
94
  
95
  /** Cast reference to real type */
96
  castReal(): ReferenceBuilder;
97
  
98
  /** Cast reference to boolean type */
99
  castBool(): ReferenceBuilder;
100
  
101
  /** Cast reference to JSON type */
102
  castJson(): ReferenceBuilder;
103
  
104
  /** Cast reference to array type */
105
  castArray(): ReferenceBuilder;
106
  
107
  /** Cast reference to custom SQL type */
108
  castType(sqlType: string): ReferenceBuilder;
109
  
110
  /** Alias for castType */
111
  castTo(sqlType: string): ReferenceBuilder;
112
}
113
```
114

115
**Usage Examples:**
116

117
```javascript
118
const { ref } = require('objection');
119

120
// Basic column reference
121
const people = await Person.query()
122
  .where(ref('age'), '>', 18);
123

124
// Reference with table specification
125
const results = await Person.query()
126
  .join('pets', 'persons.id', 'pets.ownerId')
127
  .where(ref('persons.age'), '>', ref('pets.age'));
128

129
// Reference from specific table
130
const results = await Person.query()
131
  .join('pets', 'persons.id', 'pets.ownerId')
132
  .where(ref('age').from('persons'), '>', 25);
133

134
// Reference with type casting
135
const results = await Person.query()
136
  .select(ref('metadata').castJson().as('metadataJson'))
137
  .where(ref('id').castText(), 'like', '123%');
138

139
// Reference in order by
140
const people = await Person.query()
141
  .orderBy(ref('lastName').castText());
142

143
// Reference in complex expressions
144
const people = await Person.query()
145
  .where(ref('firstName'), ref('preferredName'))
146
  .orWhere(ref('age').castInt(), '>', ref('retirementAge').castInt());
147
```
148

149
### Value Builder
150

151
Create parameterized values for queries.
152

153
```javascript { .api }
154
/**
155
 * Create a parameterized value
156
 * @param value - Value to parameterize
157
 * @returns ValueBuilder instance
158
 */
159
function val(value: any | any[] | object | object[]): ValueBuilder;
160

161
interface ValueBuilder {
162
  /** Add alias to value */
163
  as(alias: string): ValueBuilder;
164
  
165
  /** Cast value to text type */
166
  castText(): ValueBuilder;
167
  
168
  /** Cast value to integer type */
169
  castInt(): ValueBuilder;
170
  
171
  /** Cast value to big integer type */
172
  castBigInt(): ValueBuilder;
173
  
174
  /** Cast value to float type */
175
  castFloat(): ValueBuilder;
176
  
177
  /** Cast value to decimal type */
178
  castDecimal(): ValueBuilder;
179
  
180
  /** Cast value to real type */
181
  castReal(): ValueBuilder;
182
  
183
  /** Cast value to boolean type */
184
  castBool(): ValueBuilder;
185
  
186
  /** Cast value to JSON type */
187
  castJson(): ValueBuilder;
188
  
189
  /** Cast value to array type */
190
  castArray(): ValueBuilder;
191
  
192
  /** Cast value to custom SQL type */
193
  castType(sqlType: string): ValueBuilder;
194
  
195
  /** Alias for castType */
196
  castTo(sqlType: string): ValueBuilder;
197
}
198
```
199

200
**Usage Examples:**
201

202
```javascript
203
const { val } = require('objection');
204

205
// Basic value
206
const people = await Person.query()
207
  .where('status', val('active'));
208

209
// Value with type casting
210
const people = await Person.query()
211
  .where('age', '>', val('18').castInt());
212

213
// JSON value
214
const metadata = { department: 'engineering', level: 'senior' };
215
const people = await Person.query()
216
  .where('metadata', '@>', val(metadata).castJson());
217

218
// Array value
219
const tags = ['javascript', 'nodejs', 'objection'];
220
const people = await Person.query()
221
  .where('skills', '&&', val(tags).castArray());
222

223
// Value in select
224
const people = await Person.query()
225
  .select('firstName', 'lastName')
226
  .select(val('employee').as('type'));
227

228
// Value comparison
229
const people = await Person.query()
230
  .where(ref('salary'), '<', val(50000).castInt());
231
```
232

233
### Function Builder
234

235
Create SQL function calls.
236

237
```javascript { .api }
238
/**
239
 * Create a SQL function call
240
 * @param functionName - Name of the SQL function
241
 * @param args - Function arguments
242
 * @returns FunctionBuilder instance
243
 */
244
function fn(functionName: string, ...args: any[]): FunctionBuilder;
245

246
interface FunctionBuilder {
247
  /** Add alias to function call */
248
  as(alias: string): FunctionBuilder;
249
  
250
  /** Cast function result to text type */
251
  castText(): FunctionBuilder;
252
  
253
  /** Cast function result to integer type */
254
  castInt(): FunctionBuilder;
255
  
256
  /** Cast function result to big integer type */
257
  castBigInt(): FunctionBuilder;
258
  
259
  /** Cast function result to float type */
260
  castFloat(): FunctionBuilder;
261
  
262
  /** Cast function result to decimal type */
263
  castDecimal(): FunctionBuilder;
264
  
265
  /** Cast function result to real type */
266
  castReal(): FunctionBuilder;
267
  
268
  /** Cast function result to boolean type */
269
  castBool(): FunctionBuilder;
270
  
271
  /** Cast function result to JSON type */
272
  castJson(): FunctionBuilder;
273
  
274
  /** Cast function result to array type */
275
  castArray(): FunctionBuilder;
276
  
277
  /** Cast function result to custom SQL type */
278
  castType(sqlType: string): FunctionBuilder;
279
  
280
  /** Alias for castType */
281
  castTo(sqlType: string): FunctionBuilder;
282
  
283
  // Built-in function shortcuts
284
  /** Get current timestamp with optional precision */
285
  static now(precision?: number): FunctionBuilder;
286
  
287
  /** COALESCE function */
288
  static coalesce(...args: any[]): FunctionBuilder;
289
  
290
  /** CONCAT function */
291
  static concat(...args: any[]): FunctionBuilder;
292
  
293
  /** SUM function */
294
  static sum(column: string): FunctionBuilder;
295
  
296
  /** AVG function */
297
  static avg(column: string): FunctionBuilder;
298
  
299
  /** MIN function */
300
  static min(column: string): FunctionBuilder;
301
  
302
  /** MAX function */
303
  static max(column: string): FunctionBuilder;
304
  
305
  /** COUNT function */
306
  static count(column?: string): FunctionBuilder;
307
  
308
  /** UPPER function */
309
  static upper(column: string): FunctionBuilder;
310
  
311
  /** LOWER function */
312
  static lower(column: string): FunctionBuilder;
313
}
314
```
315

316
**Usage Examples:**
317

318
```javascript
319
const { fn } = require('objection');
320

321
// Basic function call
322
const people = await Person.query()
323
  .select(fn('upper', 'firstName').as('upperFirstName'));
324

325
// Function with multiple arguments
326
const people = await Person.query()
327
  .select(fn('concat', 'firstName', ' ', 'lastName').as('fullName'));
328

329
// Function in where clause
330
const people = await Person.query()
331
  .where(fn('length', 'firstName'), '>', 5);
332

333
// Built-in function shortcuts
334
const stats = await Person.query()
335
  .select(
336
    fn.count().as('totalPeople'),
337
    fn.avg('age').as('averageAge'),
338
    fn.min('age').as('minAge'),
339
    fn.max('age').as('maxAge')
340
  );
341

342
// Function with type casting
343
const people = await Person.query()
344
  .select(fn('extract', 'year', 'birthDate').castInt().as('birthYear'));
345

346
// COALESCE function
347
const people = await Person.query()
348
  .select(fn.coalesce('nickname', 'firstName').as('displayName'));
349

350
// Current timestamp
351
const people = await Person.query()
352
  .insert({
353
    firstName: 'John',
354
    lastName: 'Doe',
355
    createdAt: fn.now()
356
  });
357

358
// Complex function calls
359
const people = await Person.query()
360
  .select(
361
    'id',
362
    fn('case')
363
      .when(ref('age'), '<', 18).then('Minor')
364
      .when(ref('age'), '<', 65).then('Adult')
365
      .else('Senior')
366
      .as('ageGroup')
367
  );
368
```
369

370
### Expression Combination
371

372
Combine different expression builders for complex queries.
373

374
**Usage Examples:**
375

376
```javascript
377
const { raw, ref, val, fn } = require('objection');
378

379
// Combining different expression types
380
const results = await Person.query()
381
  .select(
382
    'id',
383
    fn('concat', ref('firstName'), val(' '), ref('lastName')).as('fullName'),
384
    raw('age * ?', 2).as('doubleAge')
385
  )
386
  .where(ref('department'), val('engineering'))
387
  .where(fn('lower', ref('status')), val('active'))
388
  .orderBy(raw('random()'));
389

390
// Complex where conditions
391
const people = await Person.query()
392
  .where(
393
    fn('date_part', val('year'), ref('birthDate')),
394
    '=',
395
    fn('date_part', val('year'), fn.now())
396
  );
397

398
// JSON operations with expressions
399
const people = await Person.query()
400
  .where(
401
    raw("metadata->>'department'"),
402
    val('engineering')
403
  )
404
  .where(
405
    fn('jsonb_array_length', ref('tags')),
406
    '>',
407
    val(3).castInt()
408
  );
409
```
410

411
### Type Casting
412

413
All expression builders support type casting for database compatibility.
414

415
**Usage Examples:**
416

417
```javascript
418
// PostgreSQL specific casting
419
const results = await Person.query()
420
  .select(ref('id').castText())
421
  .where(val('123').castInt(), ref('age').castInt())
422
  .where(fn('array_length', ref('tags'), val(1)).castInt(), '>', val(0));
423

424
// JSON casting
425
const people = await Person.query()
426
  .where(
427
    ref('metadata').castJson(),
428
    '@>',
429
    val({ active: true }).castJson()
430
  );
431

432
// Custom type casting
433
const people = await Person.query()
434
  .select(ref('created_at').castType('date').as('creationDate'));
435
```
436

437
## Types
438

439
```typescript { .api }
440
interface RawBuilder {
441
  as(alias: string): RawBuilder;
442
}
443

444
interface ReferenceBuilder {
445
  from(tableReference: string): ReferenceBuilder;
446
  as(alias: string): ReferenceBuilder;
447
  castText(): ReferenceBuilder;
448
  castInt(): ReferenceBuilder;
449
  castBigInt(): ReferenceBuilder;
450
  castFloat(): ReferenceBuilder;
451
  castDecimal(): ReferenceBuilder;
452
  castReal(): ReferenceBuilder;
453
  castBool(): ReferenceBuilder;
454
  castJson(): ReferenceBuilder;
455
  castArray(): ReferenceBuilder;
456
  castType(sqlType: string): ReferenceBuilder;
457
  castTo(sqlType: string): ReferenceBuilder;
458
}
459

460
interface ValueBuilder {
461
  as(alias: string): ValueBuilder;
462
  castText(): ValueBuilder;
463
  castInt(): ValueBuilder;
464
  castBigInt(): ValueBuilder;
465
  castFloat(): ValueBuilder;
466
  castDecimal(): ValueBuilder;
467
  castReal(): ValueBuilder;
468
  castBool(): ValueBuilder;
469
  castJson(): ValueBuilder;
470
  castArray(): ValueBuilder;
471
  castType(sqlType: string): ValueBuilder;
472
  castTo(sqlType: string): ValueBuilder;
473
}
474

475
interface FunctionBuilder {
476
  as(alias: string): FunctionBuilder;
477
  castText(): FunctionBuilder;
478
  castInt(): FunctionBuilder;
479
  castBigInt(): FunctionBuilder;
480
  castFloat(): FunctionBuilder;
481
  castDecimal(): FunctionBuilder;
482
  castReal(): FunctionBuilder;
483
  castBool(): FunctionBuilder;
484
  castJson(): FunctionBuilder;
485
  castArray(): FunctionBuilder;
486
  castType(sqlType: string): FunctionBuilder;
487
  castTo(sqlType: string): FunctionBuilder;
488
}
489

490
type Expression<T = any> = 
491
  | T 
492
  | RawBuilder 
493
  | ReferenceBuilder 
494
  | ValueBuilder 
495
  | FunctionBuilder 
496
  | QueryBuilder;
497
```