konnosDashboard
query.test.ts822 lines · main
1import { describe, expect, test } from 'vitest'
2
3import { ident, joinSqlFragments, safeSql } from '../../src/pg-format'
4import { Query } from '../../src/query/Query'
5import * as QueryUtils from '../../src/query/Query.utils'
6import { QueryAction } from '../../src/query/QueryAction'
7import { QueryFilter } from '../../src/query/QueryFilter'
8import { QueryModifier } from '../../src/query/QueryModifier'
9import type { Filter, QueryTable, Sort } from '../../src/query/types'
10
11describe('Query', () => {
12 test('from() should create a QueryAction with the correct table', () => {
13 const query = new Query()
14 const action = query.from('users', 'public')
15
16 expect(action).toBeInstanceOf(QueryAction)
17 expect(action['table']).toEqual({ name: 'users', schema: 'public' })
18 })
19
20 test('from() should use "public" as the default schema when not provided', () => {
21 const query = new Query()
22 const action = query.from('users')
23
24 expect(action['table']).toEqual({ name: 'users', schema: 'public' })
25 })
26})
27
28describe('QueryAction', () => {
29 const table: QueryTable = { name: 'users', schema: 'public' }
30
31 test('count() should create a QueryFilter with the correct action', () => {
32 const action = new QueryAction(table)
33 const filter = action.count()
34
35 expect(filter).toBeInstanceOf(QueryFilter)
36 expect(filter['table']).toEqual(table)
37 expect(filter['actionConfig'].action).toBe('count')
38 })
39
40 test('delete() should create a QueryFilter with the correct action and options', () => {
41 const action = new QueryAction(table)
42 const filter = action.delete({ returning: true })
43
44 expect(filter).toBeInstanceOf(QueryFilter)
45 expect(filter['table']).toEqual(table)
46 expect(filter['actionConfig'].action).toBe('delete')
47 expect(filter['actionOptions']).toEqual({ returning: true })
48 })
49
50 test('insert() should create a QueryFilter with the correct action, values and options', () => {
51 const action = new QueryAction(table)
52 const values = [{ id: 1, name: 'John' }]
53 const filter = action.insert(values, { returning: true })
54
55 expect(filter).toBeInstanceOf(QueryFilter)
56 expect(filter['table']).toEqual(table)
57 expect(filter['actionConfig']).toEqual({ action: 'insert', actionValue: values })
58 expect(filter['actionOptions']).toEqual({ returning: true })
59 })
60
61 test('select() should create a QueryFilter with the correct action and columns', () => {
62 const action = new QueryAction(table)
63 const cols = joinSqlFragments([ident('id'), ident('name')], ', ')
64 const filter = action.select(cols)
65
66 expect(filter).toBeInstanceOf(QueryFilter)
67 expect(filter['table']).toEqual(table)
68 expect(filter['actionConfig']).toEqual({ action: 'select', actionValue: 'id, name' })
69 })
70
71 test('update() should create a QueryFilter with the correct action, value and options', () => {
72 const action = new QueryAction(table)
73 const value = { name: 'John' }
74 const filter = action.update(value, { returning: true })
75
76 expect(filter).toBeInstanceOf(QueryFilter)
77 expect(filter['table']).toEqual(table)
78 expect(filter['actionConfig']).toEqual({ action: 'update', actionValue: value })
79 expect(filter['actionOptions']).toEqual({ returning: true })
80 })
81
82 test('truncate() should create a QueryFilter with the correct action and options', () => {
83 const action = new QueryAction(table)
84 const filter = action.truncate({ returning: true })
85
86 expect(filter).toBeInstanceOf(QueryFilter)
87 expect(filter['table']).toEqual(table)
88 expect(filter['actionConfig'].action).toBe('truncate')
89 expect(filter['actionOptions']).toEqual({ returning: true })
90 })
91})
92
93describe('QueryFilter', () => {
94 const table: QueryTable = { name: 'users', schema: 'public' }
95
96 test('filter() should add a filter and return the filter instance', () => {
97 const queryFilter = new QueryFilter(table, {
98 action: 'select',
99 actionValue: joinSqlFragments([ident('id'), ident('name')], ', '),
100 })
101 const result = queryFilter.filter('id', '=', 1)
102
103 expect(result).toBe(queryFilter)
104 expect(queryFilter['filters']).toEqual([{ column: 'id', operator: '=', value: 1 }])
105 })
106
107 test('match() should add multiple filters and return the filter instance', () => {
108 const queryFilter = new QueryFilter(table, {
109 action: 'select',
110 actionValue: joinSqlFragments([ident('id'), ident('name')], ', '),
111 })
112 const result = queryFilter.match({ id: 1, name: 'John' })
113
114 expect(result).toBe(queryFilter)
115 expect(queryFilter['filters']).toEqual([
116 { column: 'id', operator: '=', value: 1 },
117 { column: 'name', operator: '=', value: 'John' },
118 ])
119 })
120
121 test('order() should add a sort and return the filter instance', () => {
122 const queryFilter = new QueryFilter(table, {
123 action: 'select',
124 actionValue: joinSqlFragments([ident('id'), ident('name')], ', '),
125 })
126 const result = queryFilter.order('users', 'name', false, true)
127
128 expect(result).toBe(queryFilter)
129 expect(queryFilter['sorts']).toEqual([
130 { table: 'users', column: 'name', ascending: false, nullsFirst: true },
131 ])
132 })
133
134 test('range() should delegate to QueryModifier.range() and return the result', () => {
135 const queryFilter = new QueryFilter(table, {
136 action: 'select',
137 actionValue: joinSqlFragments([ident('id'), ident('name')], ', '),
138 })
139 const result = queryFilter.range(0, 10)
140
141 expect(result).toBeInstanceOf(QueryModifier)
142 // The pagination gets set in the QueryModifier
143 expect(result['pagination']).toEqual({ offset: 0, limit: 11 })
144 })
145
146 test('toSql() should delegate to QueryModifier.toSql() and return the SQL string', () => {
147 const queryFilter = new QueryFilter(table, {
148 action: 'select',
149 actionValue: joinSqlFragments([ident('id'), ident('name')], ', '),
150 })
151 queryFilter.filter('id', '=', 1)
152
153 const result = queryFilter.toSql()
154
155 // Expected SQL should match the pattern from QueryUtils.selectQuery()
156 expect(result).toBe('select id, name from public.users where id = 1;')
157 })
158})
159
160describe('QueryModifier', () => {
161 const table: QueryTable = { name: 'users', schema: 'public' }
162
163 test('range() should set the pagination and return the modifier instance', () => {
164 const queryModifier = new QueryModifier(table, {
165 action: 'select',
166 actionValue: joinSqlFragments([ident('id'), ident('name')], ', '),
167 })
168 const result = queryModifier.range(0, 10)
169
170 expect(result).toBe(queryModifier)
171 expect(queryModifier['pagination']).toEqual({ offset: 0, limit: 11 })
172 })
173
174 test('toSql() should generate the correct SQL for a count query', () => {
175 const queryModifier = new QueryModifier(table, { action: 'count' })
176 const result = queryModifier.toSql()
177
178 expect(result).toBe('select count(*) from public.users;')
179 })
180
181 test('toSql() should generate the correct SQL for a delete query with filters', () => {
182 const queryModifier = new QueryModifier(
183 table,
184 { action: 'delete' },
185 {
186 filters: [{ column: 'id', operator: '=', value: 1 }],
187 actionOptions: { returning: true },
188 }
189 )
190 const result = queryModifier.toSql()
191
192 expect(result).toBe('delete from public.users where id = 1 returning *;')
193 })
194
195 test('toSql() should generate the correct SQL for a select query with filters, sorts and pagination', () => {
196 const queryModifier = new QueryModifier(
197 table,
198 { action: 'select', actionValue: joinSqlFragments([ident('id'), ident('name')], ', ') },
199 {
200 filters: [{ column: 'id', operator: '>', value: 10 }],
201 sorts: [{ table: 'users', column: 'name', ascending: true, nullsFirst: false }],
202 }
203 )
204 queryModifier.range(0, 5)
205 const result = queryModifier.toSql()
206 expect(result).toMatchInlineSnapshot(
207 `"select id, name from public.users where id > 10 order by users.name asc nulls last limit 6 offset 0;"`
208 )
209 })
210
211 test('toSql() should generate the correct SQL for a truncate query', () => {
212 const queryModifier = new QueryModifier(table, { action: 'truncate' })
213 const result = queryModifier.toSql()
214
215 expect(result).toBe('truncate public.users;')
216 })
217
218 test('toSql() should generate the correct SQL for a truncate query with cascade', () => {
219 const queryModifier = new QueryModifier(
220 table,
221 { action: 'truncate' },
222 {
223 actionOptions: { cascade: true },
224 }
225 )
226 const result = queryModifier.toSql()
227
228 expect(result).toBe('truncate public.users cascade;')
229 })
230})
231
232describe('Query.utils', () => {
233 const table: QueryTable = { name: 'users', schema: 'public' }
234
235 describe('countQuery', () => {
236 test('should generate a correct count query without filters', () => {
237 const result = QueryUtils.countQuery(table)
238 expect(result).toBe('select count(*) from public.users;')
239 })
240
241 test('should generate a correct count query with filters', () => {
242 const filters = [{ column: 'id', operator: '>' as const, value: 1 }]
243 const result = QueryUtils.countQuery(table, { filters: filters })
244 expect(result).toBe('select count(*) from public.users where id > 1;')
245 })
246 })
247
248 describe('truncateQuery', () => {
249 test('should generate a correct truncate query without cascade', () => {
250 const result = QueryUtils.truncateQuery(table)
251 expect(result).toBe('truncate public.users;')
252 })
253
254 test('should generate a correct truncate query with cascade', () => {
255 const result = QueryUtils.truncateQuery(table, { cascade: true })
256 expect(result).toBe('truncate public.users cascade;')
257 })
258 })
259
260 describe('deleteQuery', () => {
261 test('should throw an error if no filters are provided', () => {
262 expect(() => QueryUtils.deleteQuery(table)).toThrow()
263 })
264
265 test('should generate a correct delete query with filters', () => {
266 const filters = [{ column: 'id', operator: '=' as const, value: 1 }]
267 const result = QueryUtils.deleteQuery(table, filters)
268 expect(result).toBe('delete from public.users where id = 1;')
269 })
270
271 test('should include returning clause when specified', () => {
272 const filters = [{ column: 'id', operator: '=' as const, value: 1 }]
273 const result = QueryUtils.deleteQuery(table, filters, { returning: true })
274 expect(result).toBe('delete from public.users where id = 1 returning *;')
275 })
276
277 test('should include enum array columns in returning clause when specified', () => {
278 const filters = [{ column: 'id', operator: '=' as const, value: 1 }]
279 const result = QueryUtils.deleteQuery(table, filters, {
280 returning: true,
281 enumArrayColumns: ['tags'],
282 })
283 expect(result).toBe('delete from public.users where id = 1 returning *, tags::text[];')
284 })
285 })
286
287 describe('insertQuery', () => {
288 test('should throw an error if no values are provided', () => {
289 expect(() => QueryUtils.insertQuery(table, [])).toThrow()
290 })
291
292 test('should generate a correct insert query with values', () => {
293 const values = [{ id: 1, name: 'John' }]
294 const result = QueryUtils.insertQuery(table, values)
295 expect(result).toMatchInlineSnapshot(
296 `"insert into public.users (id,name) select id,name from jsonb_populate_recordset(null::public.users, '[{"id":1,"name":"John"}]');"`
297 )
298 })
299
300 test('should include returning clause when specified', () => {
301 const values = [{ id: 1, name: 'John' }]
302 const result = QueryUtils.insertQuery(table, values, { returning: true })
303 expect(result).toMatchInlineSnapshot(
304 `"insert into public.users (id,name) select id,name from jsonb_populate_recordset(null::public.users, '[{"id":1,"name":"John"}]') returning *;"`
305 )
306 })
307
308 test('should include enum array columns in returning clause when specified', () => {
309 const values = [{ id: 1, name: 'John' }]
310 const result = QueryUtils.insertQuery(table, values, {
311 returning: true,
312 enumArrayColumns: ['tags'],
313 })
314 expect(result).toMatchInlineSnapshot(
315 `"insert into public.users (id,name) select id,name from jsonb_populate_recordset(null::public.users, '[{"id":1,"name":"John"}]') returning *, tags::text[];"`
316 )
317 })
318 })
319
320 describe('selectQuery', () => {
321 test('should generate a correct select query without options', () => {
322 const result = QueryUtils.selectQuery(table)
323 expect(result).toBe('select * from public.users;')
324 })
325
326 test('should generate a correct select query with custom columns', () => {
327 const result = QueryUtils.selectQuery(
328 table,
329 joinSqlFragments([ident('id'), ident('name')], ', ')
330 )
331 expect(result).toBe('select id, name from public.users;')
332 })
333
334 test('should generate a correct select query with filters', () => {
335 const filters = [{ column: 'id', operator: '>' as const, value: 1 }]
336 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
337 expect(result).toBe('select * from public.users where id > 1;')
338 })
339
340 test('should generate a correct select query with sorts', () => {
341 const sorts = [{ table: 'users', column: 'name', ascending: true, nullsFirst: false }]
342 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
343 expect(result).toBe('select * from public.users order by users.name asc nulls last;')
344 })
345
346 test('should generate a correct select query with pagination', () => {
347 const pagination = { limit: 10, offset: 0 }
348 const result = QueryUtils.selectQuery(table, safeSql`*`, { pagination: pagination })
349 expect(result).toBe('select * from public.users limit 10 offset 0;')
350 })
351
352 test('should ignore sorts with undefined column', () => {
353 const sorts: Sort[] = [{ table: 'users', column: '', ascending: true, nullsFirst: false }]
354 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
355 expect(result).toMatchInlineSnapshot(`"select * from public.users;"`)
356 })
357 })
358
359 describe('updateQuery', () => {
360 test('should throw an error if no filters are provided', () => {
361 const value = { name: 'John' }
362 expect(() => QueryUtils.updateQuery(table, value)).toThrow()
363 })
364
365 test('should generate a correct update query with filters', () => {
366 const value = { name: 'John' }
367 const filters = [{ column: 'id', operator: '=' as const, value: 1 }]
368 const result = QueryUtils.updateQuery(table, value, { filters: filters })
369 expect(result).toMatchInlineSnapshot(
370 `"update public.users set (name) = (select name from json_populate_record(null::public.users, '{"name":"John"}')) where id = 1;"`
371 )
372 })
373
374 test('should include returning clause when specified', () => {
375 const value = { name: 'John' }
376 const filters = [{ column: 'id', operator: '=' as const, value: 1 }]
377 const result = QueryUtils.updateQuery(table, value, {
378 filters: filters,
379 returning: true,
380 })
381 expect(result).toMatchInlineSnapshot(
382 `"update public.users set (name) = (select name from json_populate_record(null::public.users, '{"name":"John"}')) where id = 1 returning *;"`
383 )
384 })
385
386 test('should include enum array columns in returning clause when specified', () => {
387 const value = { name: 'John' }
388 const filters = [{ column: 'id', operator: '=' as const, value: 1 }]
389 const result = QueryUtils.updateQuery(table, value, {
390 filters: filters,
391 returning: true,
392 enumArrayColumns: ['tags'],
393 })
394 expect(result).toMatchInlineSnapshot(
395 `"update public.users set (name) = (select name from json_populate_record(null::public.users, '{"name":"John"}')) where id = 1 returning *, tags::text[];"`
396 )
397 })
398 })
399
400 describe('Query.utils internal functions', () => {
401 describe('applyFilters', () => {
402 test('should correctly apply equality filters', () => {
403 const filters: Filter[] = [{ column: 'name', operator: '=', value: 'John' }]
404 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
405 expect(result).toBe("select * from public.users where name = 'John';")
406 })
407
408 test('should correctly apply multiple filters with AND logic', () => {
409 const filters: Filter[] = [
410 { column: 'name', operator: '=', value: 'John' },
411 { column: 'age', operator: '>', value: 25 },
412 ]
413 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
414 expect(result).toBe("select * from public.users where name = 'John' and age > 25;")
415 })
416
417 test('should correctly handle "in" operator with array values', () => {
418 const filters: Filter[] = [{ column: 'id', operator: 'in', value: [1, 2, 3] }]
419 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
420 expect(result).toBe('select * from public.users where id in (1,2,3);')
421 })
422
423 test('should correctly handle "in" operator with comma-separated string', () => {
424 const filters: Filter[] = [{ column: 'id', operator: 'in', value: '1,2,3' }]
425 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
426 expect(result).toBe("select * from public.users where id in ('1','2','3');")
427 })
428
429 test('should correctly handle "is" operator with null value', () => {
430 const filters: Filter[] = [{ column: 'email', operator: 'is', value: 'null' }]
431 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
432 expect(result).toBe('select * from public.users where email is null;')
433 })
434
435 test('should correctly handle "is" operator with not null value', () => {
436 const filters: Filter[] = [{ column: 'email', operator: 'is', value: 'not null' }]
437 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
438 expect(result).toBe('select * from public.users where email is not null;')
439 })
440
441 test('should correctly handle "is" operator with boolean values', () => {
442 const filters: Filter[] = [{ column: 'active', operator: 'is', value: 'true' }]
443 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
444 expect(result).toBe('select * from public.users where active is true;')
445 })
446
447 test('should correctly escape string values in filters', () => {
448 const filters: Filter[] = [{ column: 'name', operator: '=', value: "O'Reilly" }]
449 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
450 expect(result).toContain("where name = 'O''Reilly'")
451 })
452
453 test('should error if tuple filter value length does not match column length', () => {
454 const filters: Filter[] = [{ column: ['id', 'version'], operator: '=', value: [1] }]
455 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError(
456 'Tuple filter value must have the same length as the column array'
457 )
458 })
459
460 test('should error if tuple filter value is not an array', () => {
461 const filters: Filter[] = [{ column: ['id', 'version'], operator: '=', value: 1 }]
462 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError(
463 'Tuple filter value must be an array'
464 )
465 })
466
467 test('should correctly handle tuple filters with equality operator', () => {
468 const filters: Filter[] = [{ column: ['id', 'version'], operator: '=', value: [1, 2] }]
469 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
470 expect(result).toBe('select * from public.users where (id, version) = (1, 2);')
471 })
472
473 test('should correctly handle tuple filters with greater than operator', () => {
474 const filters: Filter[] = [{ column: ['id', 'version'], operator: '>', value: [1, 2] }]
475 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
476 expect(result).toBe('select * from public.users where (id, version) > (1, 2);')
477 })
478
479 test('should correctly handle tuple filters with greater than or equal operator', () => {
480 const filters: Filter[] = [{ column: ['id', 'version'], operator: '>=', value: [1, 2] }]
481 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
482 expect(result).toBe('select * from public.users where (id, version) >= (1, 2);')
483 })
484
485 test('should correctly handle tuple filters with less than operator', () => {
486 const filters: Filter[] = [{ column: ['id', 'version'], operator: '<', value: [10, 5] }]
487 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
488 expect(result).toBe('select * from public.users where (id, version) < (10, 5);')
489 })
490
491 test('should correctly handle tuple filters with less than or equal operator', () => {
492 const filters: Filter[] = [{ column: ['id', 'version'], operator: '<=', value: [10, 5] }]
493 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
494 expect(result).toBe('select * from public.users where (id, version) <= (10, 5);')
495 })
496
497 test('should correctly handle tuple filters with not equal operator (<>)', () => {
498 const filters: Filter[] = [{ column: ['id', 'version'], operator: '<>', value: [1, 2] }]
499 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
500 expect(result).toBe('select * from public.users where (id, version) <> (1, 2);')
501 })
502
503 test('should correctly handle tuple filters with in operator', () => {
504 const filters: Filter[] = [
505 {
506 column: ['id', 'version'],
507 operator: 'in',
508 value: [
509 [1, 2],
510 [3, 4],
511 [5, 6],
512 ],
513 },
514 ]
515 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
516 expect(result).toBe(
517 'select * from public.users where (id, version) in ((1, 2), (3, 4), (5, 6));'
518 )
519 })
520
521 test('should error if tuple filters with in operator do not have matching number of array values', () => {
522 const filters: Filter[] = [
523 {
524 column: ['id', 'version'],
525 operator: 'in',
526 value: [[1, 2], [3, 4], [5]],
527 },
528 ]
529 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
530 })
531
532 test('should correctly handle tuple filters with in operator using strings', () => {
533 const filters: Filter[] = [
534 {
535 column: ['id', 'version'],
536 operator: 'in',
537 value: ['one,two', 'three,four', 'five,six'],
538 },
539 ]
540 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
541 expect(result).toBe(
542 `select * from public.users where (id, version) in (('one', 'two'), ('three', 'four'), ('five', 'six'));`
543 )
544 })
545
546 test('should error if tuple filters with in operator do not have matching number of stringified values', () => {
547 const filters: Filter[] = [
548 {
549 column: ['id', 'version'],
550 operator: 'in',
551 value: ['one,two', 'three,four', 'five'],
552 },
553 ]
554 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
555 })
556
557 test('should correctly handle tuple filters with string values', () => {
558 const filters: Filter[] = [
559 { column: ['first_name', 'last_name'], operator: '=', value: ['John', 'Doe'] },
560 ]
561 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
562 expect(result).toBe(
563 "select * from public.users where (first_name, last_name) = ('John', 'Doe');"
564 )
565 })
566
567 test('should correctly handle mixed tuple and regular filters', () => {
568 const filters: Filter[] = [
569 { column: ['id', 'version'], operator: '>', value: [1, 2] },
570 { column: 'active', operator: '=', value: true },
571 ]
572 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
573 expect(result).toBe(
574 'select * from public.users where (id, version) > (1, 2) and active = true;'
575 )
576 })
577
578 test('should error when trying to use "is" operator as a tuple filter', () => {
579 const filters: Filter[] = [
580 {
581 column: ['id', 'version'],
582 operator: 'is',
583 value: [null, null],
584 },
585 ]
586 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
587 })
588
589 test('should error when trying to use "~~" operator as a tuple filter', () => {
590 const filters: Filter[] = [
591 {
592 column: ['first_name', 'last_name'],
593 operator: '~~',
594 value: ['%John%', '%Doe%'],
595 },
596 ]
597 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
598 })
599
600 test('should error when trying to use "~~*" operator as a tuple filter', () => {
601 const filters: Filter[] = [
602 { column: ['first_name', 'last_name'], operator: '~~*', value: ['%john%', '%doe%'] },
603 ]
604 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
605 })
606
607 test('should error when trying to use "!~~" operator as a tuple filter', () => {
608 const filters: Filter[] = [
609 { column: ['first_name', 'last_name'], operator: '!~~', value: ['%Admin%', '%System%'] },
610 ]
611 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
612 })
613
614 test('should error when trying to use "!~~*" operator as a tuple filter', () => {
615 const filters: Filter[] = [
616 { column: ['first_name', 'last_name'], operator: '!~~*', value: ['%admin%', '%system%'] },
617 ]
618 expect(() => QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })).toThrowError()
619 })
620 })
621
622 describe('applySorts', () => {
623 test('should correctly apply a single sort with default options', () => {
624 const sorts: Sort[] = [
625 { table: 'users', column: 'name', ascending: true, nullsFirst: false },
626 ]
627 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
628 expect(result).toBe('select * from public.users order by users.name asc nulls last;')
629 })
630
631 test('should correctly apply a descending sort', () => {
632 const sorts: Sort[] = [
633 { table: 'users', column: 'name', ascending: false, nullsFirst: false },
634 ]
635 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
636 expect(result).toBe('select * from public.users order by users.name desc nulls last;')
637 })
638
639 test('should correctly apply nulls first option', () => {
640 const sorts: Sort[] = [
641 { table: 'users', column: 'name', ascending: true, nullsFirst: true },
642 ]
643 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
644 expect(result).toBe('select * from public.users order by users.name asc nulls first;')
645 })
646
647 test('should correctly apply multiple sorts', () => {
648 const sorts: Sort[] = [
649 { table: 'users', column: 'last_name', ascending: true, nullsFirst: false },
650 { table: 'users', column: 'first_name', ascending: true, nullsFirst: false },
651 ]
652 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
653 expect(result).toBe(
654 'select * from public.users order by users.last_name asc nulls last, users.first_name asc nulls last;'
655 )
656 })
657
658 test('should ignore sorts with undefined column', () => {
659 const sorts: Sort[] = [{ table: 'users', column: '', ascending: true, nullsFirst: false }]
660 const result = QueryUtils.selectQuery(table, safeSql`*`, { sorts: sorts })
661 expect(result).toMatchInlineSnapshot(`"select * from public.users;"`)
662 })
663 })
664
665 describe('filterLiteral', () => {
666 test('should correctly handle array literal syntax', () => {
667 const filters: Filter[] = [{ column: 'tags', operator: '=', value: "ARRAY['tag1','tag2']" }]
668 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
669 expect(result).toBe("select * from public.users where tags = ARRAY['tag1','tag2'];")
670 })
671
672 test('should correctly handle non-string values', () => {
673 const filters: Filter[] = [{ column: 'active', operator: '=', value: true }]
674 const result = QueryUtils.selectQuery(table, safeSql`*`, { filters: filters })
675 expect(result).toBe('select * from public.users where active = true;')
676 })
677 })
678
679 describe('queryTable', () => {
680 test('should correctly format the table name with schema', () => {
681 const result = QueryUtils.selectQuery({ name: 'orders', schema: 'shop' })
682 expect(result).toBe('select * from shop.orders;')
683 })
684 })
685 })
686})
687
688describe('End-to-end query chaining', () => {
689 test('should correctly build a simple select query', () => {
690 const query = new Query()
691 const sql = query
692 .from('users', 'public')
693 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
694 .toSql()
695
696 expect(sql).toBe('select id, name, email from public.users;')
697 })
698
699 test('should correctly build a filtered select query', () => {
700 const query = new Query()
701 const sql = query
702 .from('users', 'public')
703 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
704 .filter('id', '>', 10)
705 .toSql()
706
707 expect(sql).toBe('select id, name, email from public.users where id > 10;')
708 })
709
710 test('should correctly build a select query with multiple filters', () => {
711 const query = new Query()
712 const sql = query
713 .from('users', 'public')
714 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
715 .filter('id', '>', 10)
716 .filter('name', '~~', '%John%')
717 .toSql()
718
719 expect(sql).toBe(
720 "select id, name, email from public.users where id > 10 and name::text ~~ '%John%';"
721 )
722 })
723
724 test('should correctly build a select query with match criteria', () => {
725 const query = new Query()
726 const sql = query
727 .from('users', 'public')
728 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
729 .match({ active: true, role: 'admin' })
730 .toSql()
731
732 expect(sql).toBe(
733 "select id, name, email from public.users where active = true and role = 'admin';"
734 )
735 })
736
737 test('should correctly build a select query with sorting', () => {
738 const query = new Query()
739 const sql = query
740 .from('users', 'public')
741 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
742 .order('users', 'name', true, false)
743 .toSql()
744
745 expect(sql).toBe('select id, name, email from public.users order by users.name asc nulls last;')
746 })
747
748 test('should correctly build a select query with pagination', () => {
749 const query = new Query()
750 const sql = query
751 .from('users', 'public')
752 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
753 .range(0, 9)
754 .toSql()
755
756 expect(sql).toBe('select id, name, email from public.users limit 10 offset 0;')
757 })
758
759 test('should correctly build a complete select query with filters, sorting and pagination', () => {
760 const query = new Query()
761 const sql = query
762 .from('users', 'public')
763 .select(joinSqlFragments([ident('id'), ident('name'), ident('email')], ', '))
764 .filter('id', '>', 10)
765 .match({ active: true })
766 .order('users', 'name', true, false)
767 .range(0, 9)
768 .toSql()
769
770 expect(sql).toBe(
771 'select id, name, email from public.users where id > 10 and active = true order by users.name asc nulls last limit 10 offset 0;'
772 )
773 })
774
775 test('should correctly build an insert query', () => {
776 const query = new Query()
777 const sql = query
778 .from('users', 'public')
779 .insert([{ name: 'John', email: 'john@example.com' }], { returning: true })
780 .toSql()
781 expect(sql).toMatchInlineSnapshot(
782 `"insert into public.users (name,email) select name,email from jsonb_populate_recordset(null::public.users, '[{"name":"John","email":"john@example.com"}]') returning *;"`
783 )
784 })
785
786 test('should correctly build an update query', () => {
787 const query = new Query()
788 const sql = query
789 .from('users', 'public')
790 .update({ name: 'Updated Name' }, { returning: true })
791 .filter('id', '=', 1)
792 .toSql()
793 expect(sql).toMatchInlineSnapshot(
794 `"update public.users set (name) = (select name from json_populate_record(null::public.users, '{"name":"Updated Name"}')) where id = 1 returning *;"`
795 )
796 })
797
798 test('should correctly build a delete query', () => {
799 const query = new Query()
800 const sql = query
801 .from('users', 'public')
802 .delete({ returning: true })
803 .filter('id', '=', 1)
804 .toSql()
805
806 expect(sql).toBe('delete from public.users where id = 1 returning *;')
807 })
808
809 test('should correctly build a count query', () => {
810 const query = new Query()
811 const sql = query.from('users', 'public').count().filter('active', '=', true).toSql()
812
813 expect(sql).toBe('select count(*) from public.users where active = true;')
814 })
815
816 test('should correctly build a truncate query', () => {
817 const query = new Query()
818 const sql = query.from('users', 'public').truncate().toSql()
819
820 expect(sql).toBe('truncate public.users;')
821 })
822})