konnosDashboard
tables.test.ts1493 lines · main
1import { afterAll, beforeAll, expect, test } from 'vitest'
2
3import pgMeta from '../src/index'
4import { cleanupRoot, createTestDatabase } from './db/utils'
5
6beforeAll(async () => {
7 // Any global setup if needed
8})
9
10afterAll(async () => {
11 await cleanupRoot()
12})
13
14const cleanNondet = (x: any) => {
15 const { columns, primary_keys, relationships, ...rest2 } = x
16
17 return {
18 columns: columns.map(({ id, table_id, ...rest }: any) => rest),
19 primary_keys: primary_keys.map(({ table_id, ...rest }: any) => rest),
20 relationships: relationships.map(({ id, ...rest }: any) => rest),
21 ...rest2,
22 }
23}
24
25type TestDb = Awaited<ReturnType<typeof createTestDatabase>>
26
27const withTestDatabase = (name: string, fn: (db: TestDb) => Promise<void>) => {
28 test(name, async () => {
29 const db = await createTestDatabase()
30 try {
31 await fn(db)
32 } finally {
33 await db.cleanup()
34 }
35 })
36}
37
38/** Original tests ported from postgres-meta */
39withTestDatabase('list tables', async ({ executeQuery }) => {
40 const { sql, zod } = await pgMeta.tables.list()
41 const res = zod.parse(await executeQuery(sql))
42
43 const { columns, primary_keys, relationships, ...rest } = res.find(
44 ({ name }) => name === 'users'
45 )!
46
47 expect({
48 columns: columns!.map(({ id, table_id, ...rest }) => rest),
49 primary_keys: primary_keys.map(({ table_id, ...rest }) => rest),
50 relationships: relationships.map(({ id, ...rest }) => rest),
51 ...rest,
52 }).toMatchInlineSnapshot(
53 {
54 bytes: expect.any(Number),
55 dead_rows_estimate: expect.any(Number),
56 id: expect.any(Number),
57 live_rows_estimate: expect.any(Number),
58 size: expect.any(String),
59 },
60 `
61 {
62 "bytes": Any<Number>,
63 "columns": [
64 {
65 "check": null,
66 "comment": null,
67 "data_type": "bigint",
68 "default_value": null,
69 "enums": [],
70 "format": "int8",
71 "format_schema": "pg_catalog",
72 "identity_generation": "BY DEFAULT",
73 "is_generated": false,
74 "is_identity": true,
75 "is_nullable": false,
76 "is_unique": false,
77 "is_updatable": true,
78 "name": "id",
79 "ordinal_position": 1,
80 "schema": "public",
81 "table": "users",
82 },
83 {
84 "check": null,
85 "comment": null,
86 "data_type": "text",
87 "default_value": null,
88 "enums": [],
89 "format": "text",
90 "format_schema": "pg_catalog",
91 "identity_generation": null,
92 "is_generated": false,
93 "is_identity": false,
94 "is_nullable": true,
95 "is_unique": false,
96 "is_updatable": true,
97 "name": "name",
98 "ordinal_position": 2,
99 "schema": "public",
100 "table": "users",
101 },
102 {
103 "check": null,
104 "comment": null,
105 "data_type": "USER-DEFINED",
106 "default_value": "'ACTIVE'::user_status",
107 "enums": [
108 "ACTIVE",
109 "INACTIVE",
110 ],
111 "format": "user_status",
112 "format_schema": "public",
113 "identity_generation": null,
114 "is_generated": false,
115 "is_identity": false,
116 "is_nullable": true,
117 "is_unique": false,
118 "is_updatable": true,
119 "name": "status",
120 "ordinal_position": 3,
121 "schema": "public",
122 "table": "users",
123 },
124 ],
125 "comment": null,
126 "dead_rows_estimate": Any<Number>,
127 "id": Any<Number>,
128 "live_rows_estimate": Any<Number>,
129 "name": "users",
130 "primary_keys": [
131 {
132 "name": "id",
133 "schema": "public",
134 "table_name": "users",
135 },
136 ],
137 "relationships": [
138 {
139 "constraint_name": "todos_user-id_fkey",
140 "source_column_name": "user-id",
141 "source_schema": "public",
142 "source_table_name": "todos",
143 "target_column_name": "id",
144 "target_table_name": "users",
145 "target_table_schema": "public",
146 },
147 {
148 "constraint_name": "user_details_user_id_fkey",
149 "source_column_name": "user_id",
150 "source_schema": "public",
151 "source_table_name": "user_details",
152 "target_column_name": "id",
153 "target_table_name": "users",
154 "target_table_schema": "public",
155 },
156 ],
157 "replica_identity": "DEFAULT",
158 "rls_enabled": false,
159 "rls_forced": false,
160 "schema": "public",
161 "size": Any<String>,
162 }
163 `
164 )
165})
166
167withTestDatabase('list tables without columns', async ({ executeQuery }) => {
168 const { sql, zod } = await pgMeta.tables.list({ includeColumns: false })
169 const res = zod.parse(await executeQuery(sql))
170
171 //@ts-expect-error columns doesn't exist at type level if includeColumns is false
172 const { columns, primary_keys, relationships, ...rest } = res.find(
173 ({ name }) => name === 'users'
174 )!
175
176 expect({
177 primary_keys: primary_keys.map(({ table_id, ...rest }) => rest),
178 relationships: relationships.map(({ id, ...rest }) => rest),
179 ...rest,
180 }).toMatchInlineSnapshot(
181 {
182 bytes: expect.any(Number),
183 dead_rows_estimate: expect.any(Number),
184 id: expect.any(Number),
185 live_rows_estimate: expect.any(Number),
186 size: expect.any(String),
187 },
188 `
189 {
190 "bytes": Any<Number>,
191 "comment": null,
192 "dead_rows_estimate": Any<Number>,
193 "id": Any<Number>,
194 "live_rows_estimate": Any<Number>,
195 "name": "users",
196 "primary_keys": [
197 {
198 "name": "id",
199 "schema": "public",
200 "table_name": "users",
201 },
202 ],
203 "relationships": [
204 {
205 "constraint_name": "todos_user-id_fkey",
206 "source_column_name": "user-id",
207 "source_schema": "public",
208 "source_table_name": "todos",
209 "target_column_name": "id",
210 "target_table_name": "users",
211 "target_table_schema": "public",
212 },
213 {
214 "constraint_name": "user_details_user_id_fkey",
215 "source_column_name": "user_id",
216 "source_schema": "public",
217 "source_table_name": "user_details",
218 "target_column_name": "id",
219 "target_table_name": "users",
220 "target_table_schema": "public",
221 },
222 ],
223 "replica_identity": "DEFAULT",
224 "rls_enabled": false,
225 "rls_forced": false,
226 "schema": "public",
227 "size": Any<String>,
228 }
229 `
230 )
231})
232
233withTestDatabase('list tables with included schemas', async ({ executeQuery }) => {
234 const { sql, zod } = await pgMeta.tables.list({
235 includedSchemas: ['public'],
236 })
237 const res = zod.parse(await executeQuery(sql))
238
239 expect(res.length).toBeGreaterThan(0)
240
241 res.forEach((table) => {
242 expect(table.schema).toBe('public')
243 })
244})
245
246withTestDatabase('list tables with excluded schemas', async ({ executeQuery }) => {
247 const { sql, zod } = await pgMeta.tables.list({
248 excludedSchemas: ['public'],
249 })
250 const res = zod.parse(await executeQuery(sql))
251
252 res.forEach((table) => {
253 expect(table.schema).not.toBe('public')
254 })
255})
256
257withTestDatabase(
258 'list tables with excluded schemas and include System Schemas',
259 async ({ executeQuery }) => {
260 const { sql, zod } = await pgMeta.tables.list({
261 excludedSchemas: ['public'],
262 includeSystemSchemas: true,
263 })
264 const res = zod.parse(await executeQuery(sql))
265
266 expect(res.length).toBeGreaterThan(0)
267
268 res.forEach((table) => {
269 expect(table.schema).not.toBe('public')
270 })
271 }
272)
273
274withTestDatabase('create, retrieve, update, and delete table', async ({ executeQuery }) => {
275 // Create table
276 const { sql: createSql } = await pgMeta.tables.create({
277 name: 'test',
278 comment: 'foo',
279 })
280 await executeQuery(createSql)
281
282 // Retrieve the created table
283 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.tables.retrieve({
284 name: 'test',
285 schema: 'public',
286 })
287 const retrieveRes = retrieveZod.parse((await executeQuery(retrieveSql))[0])
288
289 expect(retrieveRes).toMatchInlineSnapshot(
290 {
291 bytes: expect.any(Number),
292 dead_rows_estimate: expect.any(Number),
293 id: expect.any(Number),
294 live_rows_estimate: expect.any(Number),
295 size: expect.any(String),
296 },
297 `
298 {
299 "bytes": Any<Number>,
300 "columns": [],
301 "comment": "foo",
302 "dead_rows_estimate": Any<Number>,
303 "id": Any<Number>,
304 "live_rows_estimate": Any<Number>,
305 "name": "test",
306 "primary_keys": [],
307 "relationships": [],
308 "replica_identity": "DEFAULT",
309 "rls_enabled": false,
310 "rls_forced": false,
311 "schema": "public",
312 "size": Any<String>,
313 }
314 `
315 )
316
317 // Update table
318 const { sql: updateSql } = await pgMeta.tables.update(retrieveRes!, {
319 name: 'test a',
320 rls_enabled: true,
321 rls_forced: true,
322 replica_identity: 'NOTHING',
323 comment: 'foo',
324 })
325 await executeQuery(updateSql)
326
327 // Retrieve the updated table
328 const { sql: retrieveUpdatedSql, zod: retrieveUpdatedZod } = await pgMeta.tables.retrieve({
329 name: 'test a',
330 schema: 'public',
331 })
332 const updateRes = retrieveUpdatedZod.parse((await executeQuery(retrieveUpdatedSql))[0])
333
334 expect(updateRes).toMatchInlineSnapshot(
335 {
336 bytes: expect.any(Number),
337 dead_rows_estimate: expect.any(Number),
338 id: expect.any(Number),
339 live_rows_estimate: expect.any(Number),
340 size: expect.any(String),
341 },
342 `
343 {
344 "bytes": Any<Number>,
345 "columns": [],
346 "comment": "foo",
347 "dead_rows_estimate": Any<Number>,
348 "id": Any<Number>,
349 "live_rows_estimate": Any<Number>,
350 "name": "test a",
351 "primary_keys": [],
352 "relationships": [],
353 "replica_identity": "NOTHING",
354 "rls_enabled": true,
355 "rls_forced": true,
356 "schema": "public",
357 "size": Any<String>,
358 }
359 `
360 )
361
362 // Remove table
363 const { sql: removeSql } = await pgMeta.tables.remove(updateRes!)
364 await executeQuery(removeSql)
365
366 // Verify table is deleted
367 const { sql: verifyDeleteSql } = await pgMeta.tables.retrieve(updateRes!)
368 const verifyDeleteRes = await executeQuery(verifyDeleteSql)
369 expect(verifyDeleteRes).toHaveLength(0)
370})
371
372withTestDatabase('update with name unchanged', async ({ executeQuery }) => {
373 // Create table
374 const { sql: createSql } = await pgMeta.tables.create({ name: 't' })
375 await executeQuery(createSql)
376
377 // Get the created table
378 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.tables.retrieve({
379 name: 't',
380 schema: 'public',
381 })
382 const table = retrieveZod.parse((await executeQuery(retrieveSql))[0])
383
384 // Update table with same name
385 const { sql: updateSql } = await pgMeta.tables.update(table!, { name: 't' })
386 await executeQuery(updateSql)
387
388 // Verify update
389 const { sql: verifySQL, zod: verifyZod } = await pgMeta.tables.retrieve({
390 name: 't',
391 schema: 'public',
392 })
393 const res = verifyZod.parse((await executeQuery(verifySQL))[0])
394
395 expect(res).toMatchInlineSnapshot(
396 {
397 bytes: expect.any(Number),
398 dead_rows_estimate: expect.any(Number),
399 id: expect.any(Number),
400 live_rows_estimate: expect.any(Number),
401 size: expect.any(String),
402 },
403 `
404 {
405 "bytes": Any<Number>,
406 "columns": [],
407 "comment": null,
408 "dead_rows_estimate": Any<Number>,
409 "id": Any<Number>,
410 "live_rows_estimate": Any<Number>,
411 "name": "t",
412 "primary_keys": [],
413 "relationships": [],
414 "replica_identity": "DEFAULT",
415 "rls_enabled": false,
416 "rls_forced": false,
417 "schema": "public",
418 "size": Any<String>,
419 }
420 `
421 )
422})
423
424withTestDatabase("allow ' in comments", async ({ executeQuery }) => {
425 // Create table with single quote in comment
426 const { sql: createSql } = await pgMeta.tables.create({
427 name: 't',
428 comment: "'",
429 })
430 await executeQuery(createSql)
431
432 // Verify creation
433 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.tables.retrieve({
434 name: 't',
435 schema: 'public',
436 })
437 const res = retrieveZod.parse((await executeQuery(retrieveSql))[0])
438
439 expect(res).toMatchInlineSnapshot(
440 {
441 bytes: expect.any(Number),
442 dead_rows_estimate: expect.any(Number),
443 id: expect.any(Number),
444 live_rows_estimate: expect.any(Number),
445 size: expect.any(String),
446 },
447 `
448 {
449 "bytes": Any<Number>,
450 "columns": [],
451 "comment": "'",
452 "dead_rows_estimate": Any<Number>,
453 "id": Any<Number>,
454 "live_rows_estimate": Any<Number>,
455 "name": "t",
456 "primary_keys": [],
457 "relationships": [],
458 "replica_identity": "DEFAULT",
459 "rls_enabled": false,
460 "rls_forced": false,
461 "schema": "public",
462 "size": Any<String>,
463 }
464 `
465 )
466})
467
468withTestDatabase('primary keys', async ({ executeQuery }) => {
469 // Create table with columns
470 const { sql: createSql } = await pgMeta.tables.create({ name: 't' })
471 await executeQuery(createSql)
472 await executeQuery(`
473 ALTER TABLE t
474 ADD COLUMN c bigint,
475 ADD COLUMN cc text
476 `)
477
478 // Get the created table
479 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.tables.retrieve({
480 name: 't',
481 schema: 'public',
482 })
483 const table = retrieveZod.parse((await executeQuery(retrieveSql))[0])
484
485 // Update table with primary keys
486 const { sql: updateSql } = await pgMeta.tables.update(table!, {
487 primary_keys: [{ name: 'c' }, { name: 'cc' }],
488 })
489 await executeQuery(updateSql)
490
491 // Verify update
492 const { sql: verifySQL, zod: verifyZod } = await pgMeta.tables.retrieve({
493 name: 't',
494 schema: 'public',
495 })
496 const res = verifyZod.parse((await executeQuery(verifySQL))[0])
497
498 expect(cleanNondet(res)).toMatchInlineSnapshot(
499 {
500 bytes: expect.any(Number),
501 dead_rows_estimate: expect.any(Number),
502 id: expect.any(Number),
503 live_rows_estimate: expect.any(Number),
504 size: expect.any(String),
505 },
506 `
507 {
508 "bytes": Any<Number>,
509 "columns": [
510 {
511 "check": null,
512 "comment": null,
513 "data_type": "bigint",
514 "default_value": null,
515 "enums": [],
516 "format": "int8",
517 "format_schema": "pg_catalog",
518 "identity_generation": null,
519 "is_generated": false,
520 "is_identity": false,
521 "is_nullable": false,
522 "is_unique": false,
523 "is_updatable": true,
524 "name": "c",
525 "ordinal_position": 1,
526 "schema": "public",
527 "table": "t",
528 },
529 {
530 "check": null,
531 "comment": null,
532 "data_type": "text",
533 "default_value": null,
534 "enums": [],
535 "format": "text",
536 "format_schema": "pg_catalog",
537 "identity_generation": null,
538 "is_generated": false,
539 "is_identity": false,
540 "is_nullable": false,
541 "is_unique": false,
542 "is_updatable": true,
543 "name": "cc",
544 "ordinal_position": 2,
545 "schema": "public",
546 "table": "t",
547 },
548 ],
549 "comment": null,
550 "dead_rows_estimate": Any<Number>,
551 "id": Any<Number>,
552 "live_rows_estimate": Any<Number>,
553 "name": "t",
554 "primary_keys": [
555 {
556 "name": "c",
557 "schema": "public",
558 "table_name": "t",
559 },
560 {
561 "name": "cc",
562 "schema": "public",
563 "table_name": "t",
564 },
565 ],
566 "relationships": [],
567 "replica_identity": "DEFAULT",
568 "rls_enabled": false,
569 "rls_forced": false,
570 "schema": "public",
571 "size": Any<String>,
572 }
573 `
574 )
575})
576
577// /** Additional tests */
578withTestDatabase('retrieve table by id', async ({ executeQuery }) => {
579 const { sql, zod } = await pgMeta.tables.retrieve({ id: 16510 })
580 const res = zod.parse((await executeQuery(sql))[0])
581
582 expect(res).toMatchInlineSnapshot(
583 {
584 bytes: expect.any(Number),
585 dead_rows_estimate: expect.any(Number),
586 id: expect.any(Number),
587 live_rows_estimate: expect.any(Number),
588 size: expect.any(String),
589 },
590 `
591 {
592 "bytes": Any<Number>,
593 "columns": [
594 {
595 "check": null,
596 "comment": null,
597 "data_type": "text",
598 "default_value": null,
599 "enums": [],
600 "format": "text",
601 "format_schema": "pg_catalog",
602 "id": "16510.2",
603 "identity_generation": null,
604 "is_generated": false,
605 "is_identity": false,
606 "is_nullable": false,
607 "is_unique": false,
608 "is_updatable": true,
609 "name": "name",
610 "ordinal_position": 2,
611 "schema": "public",
612 "table": "memes",
613 "table_id": 16510,
614 },
615 {
616 "check": null,
617 "comment": null,
618 "data_type": "integer",
619 "default_value": null,
620 "enums": [],
621 "format": "int4",
622 "format_schema": "pg_catalog",
623 "id": "16510.3",
624 "identity_generation": null,
625 "is_generated": false,
626 "is_identity": false,
627 "is_nullable": true,
628 "is_unique": false,
629 "is_updatable": true,
630 "name": "category",
631 "ordinal_position": 3,
632 "schema": "public",
633 "table": "memes",
634 "table_id": 16510,
635 },
636 {
637 "check": null,
638 "comment": null,
639 "data_type": "jsonb",
640 "default_value": null,
641 "enums": [],
642 "format": "jsonb",
643 "format_schema": "pg_catalog",
644 "id": "16510.4",
645 "identity_generation": null,
646 "is_generated": false,
647 "is_identity": false,
648 "is_nullable": true,
649 "is_unique": false,
650 "is_updatable": true,
651 "name": "metadata",
652 "ordinal_position": 4,
653 "schema": "public",
654 "table": "memes",
655 "table_id": 16510,
656 },
657 {
658 "check": null,
659 "comment": null,
660 "data_type": "timestamp without time zone",
661 "default_value": null,
662 "enums": [],
663 "format": "timestamp",
664 "format_schema": "pg_catalog",
665 "id": "16510.5",
666 "identity_generation": null,
667 "is_generated": false,
668 "is_identity": false,
669 "is_nullable": false,
670 "is_unique": false,
671 "is_updatable": true,
672 "name": "created_at",
673 "ordinal_position": 5,
674 "schema": "public",
675 "table": "memes",
676 "table_id": 16510,
677 },
678 {
679 "check": null,
680 "comment": null,
681 "data_type": "integer",
682 "default_value": "nextval('memes_id_seq'::regclass)",
683 "enums": [],
684 "format": "int4",
685 "format_schema": "pg_catalog",
686 "id": "16510.1",
687 "identity_generation": null,
688 "is_generated": false,
689 "is_identity": false,
690 "is_nullable": false,
691 "is_unique": false,
692 "is_updatable": true,
693 "name": "id",
694 "ordinal_position": 1,
695 "schema": "public",
696 "table": "memes",
697 "table_id": 16510,
698 },
699 {
700 "check": null,
701 "comment": null,
702 "data_type": "USER-DEFINED",
703 "default_value": "'old'::meme_status",
704 "enums": [
705 "new",
706 "old",
707 "retired",
708 ],
709 "format": "meme_status",
710 "format_schema": "public",
711 "id": "16510.6",
712 "identity_generation": null,
713 "is_generated": false,
714 "is_identity": false,
715 "is_nullable": true,
716 "is_unique": false,
717 "is_updatable": true,
718 "name": "status",
719 "ordinal_position": 6,
720 "schema": "public",
721 "table": "memes",
722 "table_id": 16510,
723 },
724 ],
725 "comment": null,
726 "dead_rows_estimate": Any<Number>,
727 "id": Any<Number>,
728 "live_rows_estimate": Any<Number>,
729 "name": "memes",
730 "primary_keys": [
731 {
732 "name": "id",
733 "schema": "public",
734 "table_id": 16510,
735 "table_name": "memes",
736 },
737 ],
738 "relationships": [
739 {
740 "constraint_name": "memes_category_fkey",
741 "id": 16519,
742 "source_column_name": "category",
743 "source_schema": "public",
744 "source_table_name": "memes",
745 "target_column_name": "id",
746 "target_table_name": "category",
747 "target_table_schema": "public",
748 },
749 ],
750 "replica_identity": "DEFAULT",
751 "rls_enabled": false,
752 "rls_forced": false,
753 "schema": "public",
754 "size": Any<String>,
755 }
756 `
757 )
758})
759
760withTestDatabase('retrieve table by name and schema', async ({ executeQuery }) => {
761 const { sql, zod } = await pgMeta.tables.retrieve({ name: 'memes', schema: 'public' })
762 const res = zod.parse((await executeQuery(sql))[0])
763
764 expect(res).toMatchInlineSnapshot(
765 {
766 bytes: expect.any(Number),
767 dead_rows_estimate: expect.any(Number),
768 id: expect.any(Number),
769 live_rows_estimate: expect.any(Number),
770 size: expect.any(String),
771 },
772 `
773 {
774 "bytes": Any<Number>,
775 "columns": [
776 {
777 "check": null,
778 "comment": null,
779 "data_type": "text",
780 "default_value": null,
781 "enums": [],
782 "format": "text",
783 "format_schema": "pg_catalog",
784 "id": "16510.2",
785 "identity_generation": null,
786 "is_generated": false,
787 "is_identity": false,
788 "is_nullable": false,
789 "is_unique": false,
790 "is_updatable": true,
791 "name": "name",
792 "ordinal_position": 2,
793 "schema": "public",
794 "table": "memes",
795 "table_id": 16510,
796 },
797 {
798 "check": null,
799 "comment": null,
800 "data_type": "integer",
801 "default_value": null,
802 "enums": [],
803 "format": "int4",
804 "format_schema": "pg_catalog",
805 "id": "16510.3",
806 "identity_generation": null,
807 "is_generated": false,
808 "is_identity": false,
809 "is_nullable": true,
810 "is_unique": false,
811 "is_updatable": true,
812 "name": "category",
813 "ordinal_position": 3,
814 "schema": "public",
815 "table": "memes",
816 "table_id": 16510,
817 },
818 {
819 "check": null,
820 "comment": null,
821 "data_type": "jsonb",
822 "default_value": null,
823 "enums": [],
824 "format": "jsonb",
825 "format_schema": "pg_catalog",
826 "id": "16510.4",
827 "identity_generation": null,
828 "is_generated": false,
829 "is_identity": false,
830 "is_nullable": true,
831 "is_unique": false,
832 "is_updatable": true,
833 "name": "metadata",
834 "ordinal_position": 4,
835 "schema": "public",
836 "table": "memes",
837 "table_id": 16510,
838 },
839 {
840 "check": null,
841 "comment": null,
842 "data_type": "timestamp without time zone",
843 "default_value": null,
844 "enums": [],
845 "format": "timestamp",
846 "format_schema": "pg_catalog",
847 "id": "16510.5",
848 "identity_generation": null,
849 "is_generated": false,
850 "is_identity": false,
851 "is_nullable": false,
852 "is_unique": false,
853 "is_updatable": true,
854 "name": "created_at",
855 "ordinal_position": 5,
856 "schema": "public",
857 "table": "memes",
858 "table_id": 16510,
859 },
860 {
861 "check": null,
862 "comment": null,
863 "data_type": "integer",
864 "default_value": "nextval('memes_id_seq'::regclass)",
865 "enums": [],
866 "format": "int4",
867 "format_schema": "pg_catalog",
868 "id": "16510.1",
869 "identity_generation": null,
870 "is_generated": false,
871 "is_identity": false,
872 "is_nullable": false,
873 "is_unique": false,
874 "is_updatable": true,
875 "name": "id",
876 "ordinal_position": 1,
877 "schema": "public",
878 "table": "memes",
879 "table_id": 16510,
880 },
881 {
882 "check": null,
883 "comment": null,
884 "data_type": "USER-DEFINED",
885 "default_value": "'old'::meme_status",
886 "enums": [
887 "new",
888 "old",
889 "retired",
890 ],
891 "format": "meme_status",
892 "format_schema": "public",
893 "id": "16510.6",
894 "identity_generation": null,
895 "is_generated": false,
896 "is_identity": false,
897 "is_nullable": true,
898 "is_unique": false,
899 "is_updatable": true,
900 "name": "status",
901 "ordinal_position": 6,
902 "schema": "public",
903 "table": "memes",
904 "table_id": 16510,
905 },
906 ],
907 "comment": null,
908 "dead_rows_estimate": Any<Number>,
909 "id": Any<Number>,
910 "live_rows_estimate": Any<Number>,
911 "name": "memes",
912 "primary_keys": [
913 {
914 "name": "id",
915 "schema": "public",
916 "table_id": 16510,
917 "table_name": "memes",
918 },
919 ],
920 "relationships": [
921 {
922 "constraint_name": "memes_category_fkey",
923 "id": 16519,
924 "source_column_name": "category",
925 "source_schema": "public",
926 "source_table_name": "memes",
927 "target_column_name": "id",
928 "target_table_name": "category",
929 "target_table_schema": "public",
930 },
931 ],
932 "replica_identity": "DEFAULT",
933 "rls_enabled": false,
934 "rls_forced": false,
935 "schema": "public",
936 "size": Any<String>,
937 }
938 `
939 )
940})
941
942withTestDatabase('retrieve error if missing identifiers', async ({}) => {
943 await expect(async () => {
944 //@ts-expect-error use with missing params
945 await pgMeta.tables.retrieve({ name: 'memes' })
946 }).rejects.toThrow('Must provide either id or name and schema')
947})
948
949withTestDatabase('remove table by id', async ({ executeQuery }) => {
950 // First create a test table
951 await executeQuery(`
952 CREATE TABLE test_remove_table (
953 id SERIAL PRIMARY KEY,
954 name TEXT
955 );
956 `)
957
958 // Get the table's id
959 const { sql: listSql, zod: listZod } = await pgMeta.tables.list()
960 const tables = listZod.parse(await executeQuery(listSql))
961 const tableId = tables.find((t) => t.name === 'test_remove_table')!
962
963 // Remove the table
964 const { sql } = await pgMeta.tables.remove(tableId)
965 await executeQuery(sql)
966
967 // Verify the table is gone
968 const tablesAfter = listZod.parse(await executeQuery(listSql))
969 expect(tablesAfter.find((t) => t.name === 'test_remove_table')).toBeUndefined()
970})
971
972withTestDatabase('remove table by name and schema', async ({ executeQuery }) => {
973 // First create a test table
974 await executeQuery(`
975 CREATE TABLE test_remove_table_2 (
976 id SERIAL PRIMARY KEY,
977 name TEXT
978 );
979 `)
980
981 // Remove the table
982 const { sql } = await pgMeta.tables.remove({
983 name: 'test_remove_table_2',
984 schema: 'public',
985 })
986 await executeQuery(sql)
987
988 // Verify the table is gone
989 const { sql: listSql, zod: listZod } = await pgMeta.tables.list()
990 const tables = listZod.parse(await executeQuery(listSql))
991 expect(tables.find((t) => t.name === 'test_remove_table_2')).toBeUndefined()
992})
993
994withTestDatabase('remove throws error for non-existent table', async ({ executeQuery }) => {
995 const { sql } = await pgMeta.tables.remove({
996 name: 'non_existent_table',
997 schema: 'public',
998 })
999
1000 // With schema and name
1001 await expect(executeQuery(sql)).rejects.toThrow(
1002 `Failed to execute query: table "non_existent_table" does not exist`
1003 )
1004})
1005
1006withTestDatabase('remove throws error with missing identifiers', async ({}) => {
1007 await expect(async () => {
1008 //@ts-expect-error use with missing params
1009 await pgMeta.tables.remove({ name: 'some_table' })
1010 }).rejects.toThrow('SQL identifier cannot be null or undefined')
1011})
1012
1013withTestDatabase('update table - rename', async ({ executeQuery }) => {
1014 // Create test table
1015 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_rename' })
1016 await executeQuery(createSql)
1017
1018 // Update table name
1019 const { sql: updateSql } = await pgMeta.tables.update(
1020 { id: 0, name: 'test_rename', schema: 'public' },
1021 { name: 'test_renamed' }
1022 )
1023 await executeQuery(updateSql)
1024
1025 // Verify update
1026 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1027 name: 'test_renamed',
1028 schema: 'public',
1029 })
1030 const res = zod.parse((await executeQuery(retrieveSql))[0])
1031 expect(res!.name).toBe('test_renamed')
1032})
1033
1034withTestDatabase('update table - change schema', async ({ executeQuery }) => {
1035 // Create test schema and table
1036 await executeQuery('CREATE SCHEMA test_schema')
1037 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_schema_move' })
1038 await executeQuery(createSql)
1039
1040 // Move table to new schema
1041 const { sql: updateSql } = await pgMeta.tables.update(
1042 { id: 0, name: 'test_schema_move', schema: 'public' },
1043 { schema: 'test_schema' }
1044 )
1045 await executeQuery(updateSql)
1046
1047 // Verify update
1048 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1049 name: 'test_schema_move',
1050 schema: 'test_schema',
1051 })
1052 const res = zod.parse((await executeQuery(retrieveSql))[0])
1053 expect(res!.schema).toBe('test_schema')
1054})
1055
1056withTestDatabase('update table - row level security', async ({ executeQuery }) => {
1057 // Create test table
1058 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_rls' })
1059 await executeQuery(createSql)
1060
1061 // Enable RLS
1062 const { sql: updateSql } = await pgMeta.tables.update(
1063 { id: 0, name: 'test_rls', schema: 'public' },
1064 { rls_enabled: true, rls_forced: true }
1065 )
1066 await executeQuery(updateSql)
1067
1068 // Verify update
1069 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1070 name: 'test_rls',
1071 schema: 'public',
1072 })
1073 const res = zod.parse((await executeQuery(retrieveSql))[0])
1074 expect(res!.rls_enabled).toBe(true)
1075 expect(res!.rls_forced).toBe(true)
1076})
1077
1078withTestDatabase('update table - replica identity', async ({ executeQuery }) => {
1079 // Create test table
1080 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_replica' })
1081 await executeQuery(createSql)
1082
1083 // Change replica identity
1084 const { sql: updateSql } = await pgMeta.tables.update(
1085 { id: 0, name: 'test_replica', schema: 'public' },
1086 { replica_identity: 'NOTHING' }
1087 )
1088 await executeQuery(updateSql)
1089
1090 // Verify update
1091 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1092 name: 'test_replica',
1093 schema: 'public',
1094 })
1095 const res = zod.parse((await executeQuery(retrieveSql))[0])
1096 expect(res!.replica_identity).toBe('NOTHING')
1097})
1098
1099withTestDatabase('update table - replica identity INDEX requires index name', async () => {
1100 expect(() =>
1101 pgMeta.tables.update({ id: 1, name: 'test', schema: 'public' }, { replica_identity: 'INDEX' })
1102 ).toThrow('replica_identity_index is required')
1103})
1104
1105withTestDatabase('update table - primary keys', async ({ executeQuery }) => {
1106 // Create test table with a column
1107 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_pk' })
1108 await executeQuery(createSql)
1109 await executeQuery('ALTER TABLE test_pk ADD COLUMN id INT')
1110
1111 // Add primary key
1112 const { sql: updateSql } = await pgMeta.tables.update(
1113 { id: 0, name: 'test_pk', schema: 'public' },
1114 { primary_keys: [{ name: 'id' }] }
1115 )
1116 await executeQuery(updateSql)
1117
1118 // Verify update
1119 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1120 name: 'test_pk',
1121 schema: 'public',
1122 })
1123 const res = zod.parse((await executeQuery(retrieveSql))[0])
1124 expect(res!.primary_keys).toHaveLength(1)
1125 expect(res!.primary_keys[0].name).toBe('id')
1126})
1127
1128withTestDatabase('update table - remove primary keys', async ({ executeQuery }) => {
1129 // Create test table with primary key
1130 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_pk_remove' })
1131 await executeQuery(createSql)
1132 await executeQuery('ALTER TABLE test_pk_remove ADD COLUMN id INT PRIMARY KEY')
1133
1134 const { sql: tableSql, zod: tableZod } = await pgMeta.tables.retrieve({
1135 name: 'test_pk_remove',
1136 schema: 'public',
1137 })
1138 const table = tableZod.parse((await executeQuery(tableSql))[0])
1139
1140 // Remove primary key
1141 const { sql: updateSql } = await pgMeta.tables.update(table!, { primary_keys: [] })
1142 await executeQuery(updateSql)
1143
1144 // Verify update
1145 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1146 name: 'test_pk_remove',
1147 schema: 'public',
1148 })
1149 const res = zod.parse((await executeQuery(retrieveSql))[0])
1150 expect(res!.primary_keys).toHaveLength(0)
1151})
1152
1153withTestDatabase('update table - comment', async ({ executeQuery }) => {
1154 // Create test table
1155 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_comment' })
1156 await executeQuery(createSql)
1157
1158 // Add comment
1159 const { sql: updateSql } = await pgMeta.tables.update(
1160 { id: 0, name: 'test_comment', schema: 'public' },
1161 { comment: 'Test comment' }
1162 )
1163 await executeQuery(updateSql)
1164
1165 // Verify update
1166 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1167 name: 'test_comment',
1168 schema: 'public',
1169 })
1170 const res = zod.parse((await executeQuery(retrieveSql))[0])
1171 expect(res!.comment).toBe('Test comment')
1172})
1173
1174withTestDatabase('update table - multiple changes', async ({ executeQuery }) => {
1175 // Create test table
1176 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_multiple' })
1177 await executeQuery(createSql)
1178 await executeQuery('ALTER TABLE test_multiple ADD COLUMN id INT')
1179
1180 // Make multiple changes
1181 const { sql: updateSql } = await pgMeta.tables.update(
1182 { id: 0, name: 'test_multiple', schema: 'public' },
1183 {
1184 name: 'test_multiple_updated',
1185 comment: 'Updated table',
1186 rls_enabled: true,
1187 primary_keys: [{ name: 'id' }],
1188 replica_identity: 'FULL',
1189 }
1190 )
1191 await executeQuery(updateSql)
1192
1193 // Verify all updates
1194 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1195 name: 'test_multiple_updated',
1196 schema: 'public',
1197 })
1198 const res = zod.parse((await executeQuery(retrieveSql))[0])
1199 expect(res).toMatchObject({
1200 name: 'test_multiple_updated',
1201 comment: 'Updated table',
1202 rls_enabled: true,
1203 replica_identity: 'FULL',
1204 primary_keys: [expect.objectContaining({ name: 'id' })],
1205 })
1206})
1207
1208withTestDatabase('update table - by id', async ({ executeQuery }) => {
1209 // Create test table
1210 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_by_id' })
1211 await executeQuery(createSql)
1212
1213 // Get table id
1214 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1215 name: 'test_by_id',
1216 schema: 'public',
1217 })
1218 const table = zod.parse((await executeQuery(retrieveSql))[0])
1219
1220 // Update by id
1221 const { sql: updateSql } = await pgMeta.tables.update(table!, { name: 'test_by_id_updated' })
1222 await executeQuery(updateSql)
1223
1224 // Verify update
1225 const { sql: verifySQL, zod: verifyZod } = await pgMeta.tables.retrieve({
1226 name: 'test_by_id_updated',
1227 schema: 'public',
1228 })
1229 const res = verifyZod.parse((await executeQuery(verifySQL))[0])
1230 expect(res!.name).toBe('test_by_id_updated')
1231})
1232
1233withTestDatabase('update table - error on non-existent table', async ({ executeQuery }) => {
1234 const { sql: updateSql } = await pgMeta.tables.update(
1235 { id: 0, name: 'non_existent', schema: 'public' },
1236 { name: 'new_name' }
1237 )
1238
1239 await expect(executeQuery(updateSql)).rejects.toThrow()
1240})
1241
1242withTestDatabase('update table - rename with schema change', async ({ executeQuery }) => {
1243 // Create test schema and table
1244 await executeQuery('CREATE SCHEMA test_schema')
1245 const { sql: createSql } = await pgMeta.tables.create({ name: 'test_rename_schema' })
1246 await executeQuery(createSql)
1247
1248 // Update both name and schema
1249 const { sql: updateSql } = await pgMeta.tables.update(
1250 { id: 0, name: 'test_rename_schema', schema: 'public' },
1251 {
1252 name: 'test_renamed_schema',
1253 schema: 'test_schema',
1254 }
1255 )
1256 await executeQuery(updateSql)
1257
1258 // Verify update
1259 const { sql: retrieveSql, zod } = await pgMeta.tables.retrieve({
1260 name: 'test_renamed_schema',
1261 schema: 'test_schema',
1262 })
1263 const res = zod.parse((await executeQuery(retrieveSql))[0])
1264 expect(res!.name).toBe('test_renamed_schema')
1265 expect(res!.schema).toBe('test_schema')
1266})
1267
1268// Table creation test cases
1269const tableCreationTests = [
1270 {
1271 name: 'create table with default schema',
1272 input: {
1273 name: 'test_table_1',
1274 },
1275 expectedSchema: 'public',
1276 expectedComment: null,
1277 },
1278 {
1279 name: 'create table with explicit public schema',
1280 input: {
1281 name: 'test_table_2',
1282 schema: 'public',
1283 },
1284 expectedSchema: 'public',
1285 expectedComment: null,
1286 },
1287 {
1288 name: 'create table with custom schema',
1289 input: {
1290 name: 'test_table_3',
1291 schema: 'custom_schema',
1292 },
1293 expectedSchema: 'custom_schema',
1294 expectedComment: null,
1295 beforeTest: async (
1296 executeQuery: Awaited<ReturnType<typeof createTestDatabase>>['executeQuery']
1297 ) => {
1298 await executeQuery('CREATE SCHEMA IF NOT EXISTS custom_schema')
1299 },
1300 },
1301 {
1302 name: 'create table with comment',
1303 input: {
1304 name: 'test_table_4',
1305 comment: 'Test comment',
1306 },
1307 expectedSchema: 'public',
1308 expectedComment: 'Test comment',
1309 },
1310 {
1311 name: 'create table with empty string comment',
1312 input: {
1313 name: 'test_table_5',
1314 comment: '',
1315 },
1316 expectedSchema: 'public',
1317 expectedComment: null, // PostgreSQL treats empty string comments as NULL
1318 },
1319 {
1320 name: 'create table with null comment',
1321 input: {
1322 name: 'test_table_6',
1323 comment: null,
1324 },
1325 expectedSchema: 'public',
1326 expectedComment: null,
1327 },
1328 {
1329 name: 'create table with uppercase name',
1330 input: {
1331 name: 'UPPERCASE_TABLE',
1332 },
1333 expectedSchema: 'public',
1334 expectedComment: null,
1335 },
1336 {
1337 name: 'create table with mixed case name',
1338 input: {
1339 name: 'MixedCase_Table_Name',
1340 },
1341 expectedSchema: 'public',
1342 expectedComment: null,
1343 },
1344 {
1345 name: 'create table with quoted name',
1346 input: {
1347 name: 'table "with" quotes',
1348 },
1349 expectedSchema: 'public',
1350 expectedComment: null,
1351 },
1352 {
1353 name: 'create table with special characters',
1354 input: {
1355 name: 'table$with#special@chars',
1356 },
1357 expectedSchema: 'public',
1358 expectedComment: null,
1359 },
1360 {
1361 name: 'create table with name at maximum length',
1362 input: {
1363 name: 'a'.repeat(63), // PostgreSQL has a 63-byte limit for identifiers
1364 },
1365 expectedSchema: 'public',
1366 expectedComment: null,
1367 },
1368 {
1369 name: 'create table with schema containing special characters',
1370 input: {
1371 name: 'normal_table',
1372 schema: 'Special.Schema$Name',
1373 },
1374 expectedSchema: 'Special.Schema$Name',
1375 expectedComment: null,
1376 beforeTest: async (executeQuery: TestDb['executeQuery']) => {
1377 await executeQuery('CREATE SCHEMA IF NOT EXISTS "Special.Schema$Name"')
1378 },
1379 },
1380 {
1381 name: 'create table with very long name',
1382 input: {
1383 name: 'a'.repeat(63),
1384 },
1385 expectedSchema: 'public',
1386 expectedComment: null,
1387 },
1388 {
1389 name: 'create table with special characters in name',
1390 input: {
1391 name: 'table,name',
1392 },
1393 expectedSchema: 'public',
1394 expectedComment: null,
1395 },
1396]
1397
1398// SQL injection test cases
1399const sqlInjectionTests = [
1400 {
1401 name: 'prevent SQL injection in table name',
1402 input: {
1403 name: "table_name'; DROP TABLE users; --",
1404 },
1405 },
1406 {
1407 name: 'prevent SQL injection in comment',
1408 input: {
1409 name: 'safe_table',
1410 comment: "normal comment'; DROP TABLE users; --",
1411 },
1412 },
1413]
1414
1415// Error test cases
1416const errorTests = [
1417 {
1418 name: 'fail on duplicate table name',
1419 input: { name: 'duplicate_table' },
1420 setup: async (executeQuery: TestDb['executeQuery']) => {
1421 await executeQuery(pgMeta.tables.create({ name: 'duplicate_table' }).sql)
1422 },
1423 expectedError: /relation.*already exists/,
1424 },
1425 {
1426 name: 'fail on invalid schema',
1427 input: { name: 'test_table', schema: 'nonexistent_schema' },
1428 expectedError: /schema.*does not exist/,
1429 },
1430 {
1431 name: 'fail on empty table name',
1432 input: {
1433 name: '',
1434 },
1435 expectedError: /zero-length delimited identifier/,
1436 },
1437 {
1438 name: 'fail on schema name exceeding maximum length',
1439 input: {
1440 name: 'table',
1441 schema: 'a'.repeat(64),
1442 },
1443 expectedError: /schema.*does not exist/,
1444 },
1445]
1446
1447// Generate individual test cases for successful table creation
1448for (const testCase of tableCreationTests) {
1449 withTestDatabase(testCase.name, async ({ executeQuery }) => {
1450 if (testCase.beforeTest) {
1451 await testCase.beforeTest(executeQuery)
1452 }
1453
1454 const { sql } = pgMeta.tables.create(testCase.input)
1455 await executeQuery(sql)
1456
1457 const { sql: listSql, zod: listZod } = await pgMeta.tables.list()
1458 const tables = listZod.parse(await executeQuery(listSql))
1459 const createdTable = tables.find((t) => t.name === testCase.input.name)
1460
1461 expect(createdTable).toBeDefined()
1462 expect(createdTable?.schema).toBe(testCase.expectedSchema)
1463 expect(createdTable?.comment).toBe(testCase.expectedComment)
1464 })
1465}
1466
1467// Generate individual test cases for SQL injection prevention
1468for (const testCase of sqlInjectionTests) {
1469 withTestDatabase(`create - ${testCase.name}`, async ({ executeQuery }) => {
1470 const { sql } = pgMeta.tables.create(testCase.input)
1471 await executeQuery(sql)
1472
1473 // Verify table was created with correct name
1474 const { sql: listSql, zod: listZod } = await pgMeta.tables.list()
1475 const tables = listZod.parse(await executeQuery(listSql))
1476 expect(tables.find((t) => t.name === testCase.input.name)).toBeDefined()
1477
1478 // Verify users table still exists (wasn't dropped)
1479 expect(tables.find((t) => t.name === 'users')).toBeDefined()
1480 })
1481}
1482
1483// Generate individual test cases for error conditions
1484for (const testCase of errorTests) {
1485 withTestDatabase(`create - ${testCase.name}`, async ({ executeQuery }) => {
1486 if (testCase.setup) {
1487 await testCase.setup(executeQuery)
1488 }
1489
1490 const { sql } = pgMeta.tables.create(testCase.input)
1491 await expect(executeQuery(sql)).rejects.toThrow(testCase.expectedError)
1492 })
1493}