konnosDashboard
advanced-query.test.ts600 lines · main
1import { afterAll, describe, expect, test } from 'vitest'
2
3import { ident, joinSqlFragments, safeSql } from '../../src/pg-format'
4import { Query } from '../../src/query/Query'
5import { cleanupRoot, createTestDatabase } from '../db/utils'
6
7type TestDb = Awaited<ReturnType<typeof createTestDatabase>>
8
9async function validateSql(db: TestDb, sql: string): Promise<any> {
10 try {
11 const result = await db.executeQuery(sql)
12 return result
13 } catch (error) {
14 throw new Error(`Invalid SQL generated: ${sql}\nError: ${error}`)
15 }
16}
17
18const withTestDatabase = (name: string, fn: (db: TestDb) => Promise<void>) => {
19 test(name, async () => {
20 const db = await createTestDatabase()
21 try {
22 // Setup test tables with special characters, spaces, and quotes
23 await db.executeQuery(`
24 CREATE TABLE "public"."normal_table" (
25 id SERIAL PRIMARY KEY,
26 name TEXT
27 );
28
29 CREATE TABLE "public"."table with spaces" (
30 id SERIAL PRIMARY KEY,
31 "column with spaces" TEXT,
32 "quoted""column" TEXT,
33 "quoted'column" TEXT,
34 "camelCaseColumn" TEXT,
35 "special#$%^&Column" TEXT
36 );
37
38 CREATE TABLE "public"."quoted""table" (
39 id SERIAL PRIMARY KEY,
40 name TEXT
41 );
42
43 CREATE TABLE "public"."quoted'table" (
44 id SERIAL PRIMARY KEY,
45 name TEXT
46 );
47
48 CREATE TABLE "public"."camelCaseTable" (
49 id SERIAL PRIMARY KEY,
50 name TEXT
51 );
52
53 CREATE TABLE "public"."special#$%^&Table" (
54 id SERIAL PRIMARY KEY,
55 name TEXT
56 );
57 `)
58
59 // Insert test data into each table
60 await db.executeQuery(`
61 -- Add data to normal_table
62 INSERT INTO "public"."normal_table" (name)
63 VALUES
64 ('John Doe'),
65 ('Jane Smith'),
66 ('O''Reilly Books'),
67 (NULL);
68
69 -- Add data to table with spaces
70 INSERT INTO "public"."table with spaces" (
71 "column with spaces",
72 "quoted""column",
73 "quoted'column",
74 "camelCaseColumn",
75 "special#$%^&Column"
76 )
77 VALUES
78 ('value with spaces', 'value with "quotes"', 'value with ''quotes''', 'camelCaseValue', 'special#$%^&Value'),
79 ('another value', 'another "quoted" value', 'another ''quoted'' value', 'anotherCamelCase', 'another#$%^&');
80
81 -- Add data to quoted"table
82 INSERT INTO "public"."quoted""table" (name)
83 VALUES
84 ('quoted table row 1'),
85 ('quoted table row 2');
86
87 -- Add data to quoted'table
88 INSERT INTO "public"."quoted'table" (name)
89 VALUES
90 ('single quoted table row 1'),
91 ('single quoted table row 2');
92
93 -- Add data to camelCaseTable
94 INSERT INTO "public"."camelCaseTable" (name)
95 VALUES
96 ('camel case table row 1'),
97 ('camel case table row 2');
98
99 -- Add data to special#$%^&Table
100 INSERT INTO "public"."special#$%^&Table" (name)
101 VALUES
102 ('special char table row 1'),
103 ('special char table row 2');
104 `)
105
106 await fn(db)
107 } finally {
108 await db.cleanup()
109 }
110 })
111}
112
113describe('Advanced Query Tests', () => {
114 afterAll(async () => {
115 await cleanupRoot()
116 })
117
118 describe('Special Table and Column Names', () => {
119 withTestDatabase('should handle tables with spaces', async (db) => {
120 const query = new Query()
121 const sql = query.from('table with spaces', 'public').select().toSql()
122
123 expect(sql).toMatchInlineSnapshot(`"select * from public."table with spaces";"`)
124 const result = await validateSql(db, sql)
125 expect(result.length).toBe(2)
126 expect(result[0]['column with spaces']).toBe('value with spaces')
127 expect(result[1]['column with spaces']).toBe('another value')
128 })
129
130 withTestDatabase('should handle tables with double quotes', async (db) => {
131 const query = new Query()
132 const sql = query.from('quoted"table', 'public').select().toSql()
133
134 expect(sql).toMatchInlineSnapshot(`"select * from public."quoted""table";"`)
135 const result = await validateSql(db, sql)
136 expect(result.length).toBe(2)
137 expect(result[0].name).toBe('quoted table row 1')
138 expect(result[1].name).toBe('quoted table row 2')
139 })
140
141 withTestDatabase('should handle tables with single quotes', async (db) => {
142 const query = new Query()
143 const sql = query.from("quoted'table", 'public').select().toSql()
144
145 expect(sql).toMatchInlineSnapshot(`"select * from public."quoted'table";"`)
146 const result = await validateSql(db, sql)
147 expect(result.length).toBe(2)
148 expect(result[0].name).toBe('single quoted table row 1')
149 expect(result[1].name).toBe('single quoted table row 2')
150 })
151
152 withTestDatabase('should handle camelCase table names', async (db) => {
153 const query = new Query()
154 const sql = query.from('camelCaseTable', 'public').select().toSql()
155
156 expect(sql).toMatchInlineSnapshot(`"select * from public."camelCaseTable";"`)
157 const result = await validateSql(db, sql)
158 expect(result.length).toBe(2)
159 expect(result[0].name).toBe('camel case table row 1')
160 expect(result[1].name).toBe('camel case table row 2')
161 })
162
163 withTestDatabase('should handle tables with special characters', async (db) => {
164 const query = new Query()
165 const sql = query.from('special#$%^&Table', 'public').select().toSql()
166
167 expect(sql).toMatchInlineSnapshot(`"select * from public."special#$%^&Table";"`)
168 const result = await validateSql(db, sql)
169 expect(result.length).toBe(2)
170 expect(result[0].name).toBe('special char table row 1')
171 expect(result[1].name).toBe('special char table row 2')
172 })
173
174 withTestDatabase('should handle columns with spaces', async (db) => {
175 const query = new Query()
176 const sql = query
177 .from('table with spaces', 'public')
178 .select(safeSql`"column with spaces"`)
179 .toSql()
180
181 expect(sql).toMatchInlineSnapshot(
182 `"select "column with spaces" from public."table with spaces";"`
183 )
184 const result = await validateSql(db, sql)
185 expect(result.length).toBe(2)
186 expect(result[0]['column with spaces']).toBe('value with spaces')
187 expect(result[1]['column with spaces']).toBe('another value')
188 })
189
190 withTestDatabase('should handle columns with double quotes', async (db) => {
191 const query = new Query()
192 const sql = query
193 .from('table with spaces', 'public')
194 .select(safeSql`"quoted""column"`)
195 .toSql()
196
197 expect(sql).toMatchInlineSnapshot(
198 `"select "quoted""column" from public."table with spaces";"`
199 )
200 const result = await validateSql(db, sql)
201 expect(result.length).toBe(2)
202 expect(result[0]['quoted"column']).toBe('value with "quotes"')
203 expect(result[1]['quoted"column']).toBe('another "quoted" value')
204 })
205
206 withTestDatabase('should handle columns with single quotes', async (db) => {
207 const query = new Query()
208 const sql = query
209 .from('table with spaces', 'public')
210 .select(safeSql`"quoted'column"`)
211 .toSql()
212
213 expect(sql).toMatchInlineSnapshot(`"select "quoted'column" from public."table with spaces";"`)
214 const result = await validateSql(db, sql)
215 expect(result.length).toBe(2)
216 expect(result[0]["quoted'column"]).toBe("value with 'quotes'")
217 expect(result[1]["quoted'column"]).toBe("another 'quoted' value")
218 })
219
220 withTestDatabase('should handle camelCase column names', async (db) => {
221 const query = new Query()
222 const sql = query
223 .from('table with spaces', 'public')
224 .select(safeSql`"camelCaseColumn"`)
225 .toSql()
226
227 expect(sql).toMatchInlineSnapshot(
228 `"select "camelCaseColumn" from public."table with spaces";"`
229 )
230 const result = await validateSql(db, sql)
231 expect(result.length).toBe(2)
232 expect(result[0].camelCaseColumn).toBe('camelCaseValue')
233 expect(result[1].camelCaseColumn).toBe('anotherCamelCase')
234 })
235
236 withTestDatabase('should handle columns with special characters', async (db) => {
237 const query = new Query()
238 const sql = query
239 .from('table with spaces', 'public')
240 .select(safeSql`"special#$%^&Column"`)
241 .toSql()
242
243 expect(sql).toMatchInlineSnapshot(
244 `"select "special#$%^&Column" from public."table with spaces";"`
245 )
246 const result = await validateSql(db, sql)
247 expect(result.length).toBe(2)
248 expect(result[0]['special#$%^&Column']).toBe('special#$%^&Value')
249 expect(result[1]['special#$%^&Column']).toBe('another#$%^&')
250 })
251 })
252
253 describe('Complex Queries with Special Names', () => {
254 withTestDatabase('should handle filtering on columns with spaces', async (db) => {
255 // First ensure the table exists with the right column
256 await db.executeQuery(`
257 DROP TABLE IF EXISTS "public"."table with spaces";
258 CREATE TABLE "public"."table with spaces" (
259 id SERIAL PRIMARY KEY,
260 "column with spaces" TEXT
261 );
262
263 -- Insert test data
264 INSERT INTO "public"."table with spaces" ("column with spaces")
265 VALUES ('test value'), ('other value');
266 `)
267
268 const query = new Query()
269
270 // Specify the column name without extra quotes in the filter
271 // The Query class handles the proper quoting
272 const sql = query
273 .from('table with spaces', 'public')
274 .select()
275 .filter('column with spaces', '=', 'test value')
276 .toSql()
277
278 expect(sql).toMatchInlineSnapshot(
279 `"select * from public."table with spaces" where "column with spaces" = 'test value';"`
280 )
281
282 // Validate the generated SQL directly against the database
283 const result = await validateSql(db, sql)
284 expect(result.length).toBe(1)
285 expect(result[0]['column with spaces']).toBe('test value')
286 })
287
288 withTestDatabase('should handle filtering with values containing quotes', async (db) => {
289 await db.executeQuery(`
290 INSERT INTO "public"."normal_table" (name)
291 VALUES ('O''Reilly');
292 `)
293
294 const query = new Query()
295 const sql = query
296 .from('normal_table', 'public')
297 .select()
298 .filter('name', '=', "O'Reilly")
299 .toSql()
300
301 expect(sql).toMatchInlineSnapshot(
302 `"select * from public.normal_table where name = 'O''Reilly';"`
303 )
304
305 const result = await validateSql(db, sql)
306 expect(result.length).toBe(1)
307 expect(result[0].name).toBe("O'Reilly")
308 })
309
310 withTestDatabase('should handle updating with values containing quotes', async (db) => {
311 const query = new Query()
312 const sql = query
313 .from('normal_table', 'public')
314 .update({ name: "John O'Reilly" }, { returning: true })
315 .filter('id', '=', 1)
316 .toSql()
317
318 expect(sql).toMatchInlineSnapshot(
319 `"update public.normal_table set (name) = (select name from json_populate_record(null::public.normal_table, '{"name":"John O''Reilly"}')) where id = 1 returning *;"`
320 )
321 await validateSql(db, sql)
322 })
323
324 withTestDatabase('should handle inserting with values containing quotes', async (db) => {
325 const query = new Query()
326 const sql = query
327 .from('normal_table', 'public')
328 .insert([{ name: "John O'Reilly" }], { returning: true })
329 .toSql()
330
331 expect(sql).toMatchInlineSnapshot(
332 `"insert into public.normal_table (name) select name from jsonb_populate_recordset(null::public.normal_table, '[{"name":"John O''Reilly"}]') returning *;"`
333 )
334 await validateSql(db, sql)
335 })
336 })
337
338 describe('Advanced SQL Generation and Validation', () => {
339 withTestDatabase(
340 'should generate valid select with multiple filters and sorting',
341 async (db) => {
342 await db.executeQuery(`
343 DELETE FROM "public"."normal_table";
344 INSERT INTO "public"."normal_table" (id, name)
345 VALUES
346 (11, 'John Smith'),
347 (12, 'John Doe'),
348 (13, 'Jane Smith'),
349 (14, 'Someone Else');
350 `)
351
352 const query = new Query()
353 const sql = query
354 .from('normal_table', 'public')
355 .select(safeSql`id, name`)
356 .filter('id', '>', 10)
357 .filter('name', '~~', '%John%')
358 .order('normal_table', 'name', true, false)
359 .range(0, 9)
360 .toSql()
361
362 expect(sql).toMatchInlineSnapshot(
363 `"select id, name from public.normal_table where id > 10 and name::text ~~ '%John%' order by normal_table.name asc nulls last limit 10 offset 0;"`
364 )
365
366 const result = await validateSql(db, sql)
367 expect(result.length).toBe(2)
368 expect(result[0].name).toBe('John Doe') // Alphabetically first
369 expect(result[1].name).toBe('John Smith')
370 expect(result.every((row: any) => row.id > 10)).toBe(true)
371 }
372 )
373
374 withTestDatabase('should generate valid insert with returning clause', async (db) => {
375 const query = new Query()
376 const sql = query
377 .from('normal_table', 'public')
378 .insert([{ name: 'John Doe' }], { returning: true })
379 .toSql()
380
381 expect(sql).toMatchInlineSnapshot(
382 `"insert into public.normal_table (name) select name from jsonb_populate_recordset(null::public.normal_table, '[{"name":"John Doe"}]') returning *;"`
383 )
384
385 const result = await validateSql(db, sql)
386 expect(result.length).toBe(1)
387 expect(result[0].name).toBe('John Doe')
388 })
389
390 withTestDatabase('should generate valid update with filtering', async (db) => {
391 await db.executeQuery(`
392 -- Clear and insert test data
393 DELETE FROM "public"."normal_table";
394 INSERT INTO "public"."normal_table" (id, name)
395 VALUES (1, 'Original Name') ON CONFLICT (id) DO UPDATE SET name = 'Original Name';
396 `)
397
398 const query = new Query()
399 const sql = query
400 .from('normal_table', 'public')
401 .update({ name: 'Updated Name' }, { returning: true })
402 .filter('id', '=', 1)
403 .toSql()
404
405 expect(sql).toMatchInlineSnapshot(
406 `"update public.normal_table set (name) = (select name from json_populate_record(null::public.normal_table, '{"name":"Updated Name"}')) where id = 1 returning *;"`
407 )
408
409 const result = await validateSql(db, sql)
410 expect(result.length).toBe(1)
411 expect(result[0].id).toBe(1)
412 expect(result[0].name).toBe('Updated Name')
413
414 // Verify the update was actually persisted
415 const verifyResult = await db.executeQuery('SELECT * FROM public.normal_table WHERE id = 1')
416 expect(verifyResult[0].name).toBe('Updated Name')
417 })
418
419 withTestDatabase('should generate valid delete with filtering', async (db) => {
420 await db.executeQuery(`
421 -- Clear and insert test data
422 DELETE FROM "public"."normal_table";
423 INSERT INTO "public"."normal_table" (id, name)
424 VALUES (1, 'To Be Deleted') ON CONFLICT (id) DO UPDATE SET name = 'To Be Deleted';
425 `)
426
427 const query = new Query()
428 const sql = query
429 .from('normal_table', 'public')
430 .delete({ returning: true })
431 .filter('id', '=', 1)
432 .toSql()
433
434 expect(sql).toMatchInlineSnapshot(
435 '"delete from public.normal_table where id = 1 returning *;"'
436 )
437
438 const result = await validateSql(db, sql)
439 expect(result.length).toBe(1)
440 expect(result[0].id).toBe(1)
441 expect(result[0].name).toBe('To Be Deleted')
442
443 // Verify the row was actually deleted
444 const verifyResult = await db.executeQuery('SELECT * FROM public.normal_table WHERE id = 1')
445 expect(verifyResult.length).toBe(0)
446 })
447
448 withTestDatabase('should generate valid count with filtering', async (db) => {
449 await db.executeQuery(`
450 -- Clear and insert test data
451 DELETE FROM "public"."normal_table";
452 INSERT INTO "public"."normal_table" (name)
453 VALUES ('John Smith'), ('John Doe'), ('Jane Doe');
454 `)
455
456 const query = new Query()
457 const sql = query
458 .from('normal_table', 'public')
459 .count()
460 .filter('name', '~~', '%John%')
461 .toSql()
462
463 expect(sql).toMatchInlineSnapshot(
464 '"select count(*) from public.normal_table where name::text ~~ \'%John%\';"'
465 )
466
467 const result = await validateSql(db, sql)
468 expect(result[0].count).toBe(2) // PostgreSQL returns count as string
469 })
470
471 withTestDatabase('should generate valid truncate query', async (db) => {
472 await db.executeQuery(`
473 INSERT INTO "public"."normal_table" (name)
474 VALUES ('Test Row 1'), ('Test Row 2');
475 `)
476
477 // Verify data exists
478 const beforeCount = await db.executeQuery(`SELECT COUNT(*) FROM "public"."normal_table"`)
479 expect(parseInt(beforeCount[0].count)).toBeGreaterThan(0)
480
481 const query = new Query()
482 const sql = query.from('normal_table', 'public').truncate().toSql()
483
484 expect(sql).toMatchInlineSnapshot('"truncate public.normal_table;"')
485 await validateSql(db, sql)
486
487 // Verify truncate worked
488 const afterCount = await db.executeQuery(`SELECT COUNT(*) FROM "public"."normal_table"`)
489 expect(parseInt(afterCount[0].count)).toBe(0)
490 })
491 })
492
493 describe('Corner Cases and Error Handling', () => {
494 withTestDatabase('should throw error for delete without filters', async () => {
495 const query = new Query()
496 const action = query.from('normal_table', 'public').delete({ returning: true })
497
498 expect(() => action.toSql()).toThrow(/no filters/)
499 })
500
501 withTestDatabase('should throw error for update without filters', async () => {
502 const query = new Query()
503 const action = query.from('normal_table', 'public').update({ name: 'Updated Name' })
504
505 expect(() => action.toSql()).toThrow(/no filters/)
506 })
507
508 withTestDatabase('should throw error for insert without values', async () => {
509 const query = new Query()
510 // We're passing an empty array to test the runtime error
511 const action = query.from('normal_table', 'public').insert([] as any, { returning: true })
512
513 expect(() => action.toSql()).toThrow(/no value to insert/)
514 })
515
516 withTestDatabase('should handle special characters in values', async (db) => {
517 const query = new Query()
518 const sql = query
519 .from('normal_table', 'public')
520 .select()
521 .filter('name', '=', 'Special $ ^ & * ( ) _ + { } | : < > ? characters')
522 .toSql()
523
524 expect(sql).toMatchInlineSnapshot(
525 `"select * from public.normal_table where name = 'Special $ ^ & * ( ) _ + { } | : < > ? characters';"`
526 )
527 await validateSql(db, sql)
528 })
529 })
530
531 describe('Advanced Filtering', () => {
532 withTestDatabase('should handle "in" operator with array values', async (db) => {
533 await db.executeQuery(`
534 DELETE FROM "public"."normal_table";
535 INSERT INTO "public"."normal_table" (id, name)
536 VALUES
537 (1, 'Row 1'),
538 (2, 'Row 2'),
539 (3, 'Row 3'),
540 (4, 'Row 4');
541 `)
542
543 const query = new Query()
544 const sql = query
545 .from('normal_table', 'public')
546 .select()
547 .filter('id', 'in', [1, 2, 3])
548 .toSql()
549
550 expect(sql).toMatchInlineSnapshot(`"select * from public.normal_table where id in (1,2,3);"`)
551 const result = await validateSql(db, sql)
552 expect(result.length).toBe(3)
553 expect(result.map((row: any) => row.id).sort()).toEqual([1, 2, 3])
554 })
555
556 withTestDatabase('should handle "is" operator with null value', async (db) => {
557 await db.executeQuery(`
558 DELETE FROM "public"."normal_table";
559 INSERT INTO "public"."normal_table" (id, name)
560 VALUES
561 (1, 'Not Null'),
562 (2, NULL);
563 `)
564
565 const query = new Query()
566 const sql = query.from('normal_table', 'public').select().filter('name', 'is', 'null').toSql()
567
568 expect(sql).toMatchInlineSnapshot(`"select * from public.normal_table where name is null;"`)
569 const result = await validateSql(db, sql)
570 expect(result.length).toBe(1)
571 expect(result[0].id).toBe(2)
572 expect(result[0].name).toBeNull()
573 })
574
575 withTestDatabase('should handle "is" operator with not null value', async (db) => {
576 await db.executeQuery(`
577 DELETE FROM "public"."normal_table";
578 INSERT INTO "public"."normal_table" (id, name)
579 VALUES
580 (1, 'Not Null'),
581 (2, NULL);
582 `)
583
584 const query = new Query()
585 const sql = query
586 .from('normal_table', 'public')
587 .select()
588 .filter('name', 'is', 'not null')
589 .toSql()
590
591 expect(sql).toMatchInlineSnapshot(
592 `"select * from public.normal_table where name is not null;"`
593 )
594 const result = await validateSql(db, sql)
595 expect(result.length).toBe(1)
596 expect(result[0].id).toBe(1)
597 expect(result[0].name).toBe('Not Null')
598 })
599 })
600})