konnosDashboard
columns.test.ts967 lines · main
1import { afterAll, beforeAll, describe, expect, test } from 'vitest'
2
3import pgMeta from '../src/index'
4import { rawSql } from '../src/pg-format'
5import { cleanupRoot, createTestDatabase } from './db/utils'
6
7beforeAll(async () => {
8 // Any global setup if needed
9})
10
11afterAll(async () => {
12 await cleanupRoot()
13})
14
15const withTestDatabase = (
16 name: string,
17 fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
18) => {
19 test(name, async () => {
20 const db = await createTestDatabase()
21 try {
22 await fn(db)
23 } finally {
24 await db.cleanup()
25 }
26 })
27}
28
29withTestDatabase('list columns', async ({ executeQuery }) => {
30 const { sql, zod } = await pgMeta.columns.list()
31 const res = zod.parse(await executeQuery(sql))
32
33 const userIdColumn = res.find(({ name }) => name === 'user-id')
34 expect(userIdColumn).toMatchInlineSnapshot(
35 {
36 id: expect.stringMatching(/^\d+\.3$/),
37 table_id: expect.any(Number),
38 },
39 `
40 {
41 "check": null,
42 "comment": null,
43 "data_type": "bigint",
44 "default_value": null,
45 "enums": [],
46 "format": "int8",
47 "format_schema": "pg_catalog",
48 "id": StringMatching /\\^\\\\d\\+\\\\\\.3\\$/,
49 "identity_generation": null,
50 "is_generated": false,
51 "is_identity": false,
52 "is_nullable": false,
53 "is_unique": false,
54 "is_updatable": true,
55 "name": "user-id",
56 "ordinal_position": 3,
57 "schema": "public",
58 "table": "todos",
59 "table_id": Any<Number>,
60 }
61 `
62 )
63})
64
65withTestDatabase('list columns from a single table', async ({ executeQuery }) => {
66 // Create test table
67 await executeQuery('CREATE TABLE t (c1 text, c2 text)')
68
69 // Get the table ID directly
70 const tableId = Number(
71 (
72 await executeQuery(
73 "SELECT oid FROM pg_class WHERE relname = 't' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')"
74 )
75 )[0].oid
76 )
77 const testTable = { id: tableId, name: 't', schema: 'public' }
78
79 // List columns
80 const { sql, zod } = await pgMeta.columns.list({ tableId: testTable.id })
81 const res = zod.parse(await executeQuery(sql))
82
83 expect(res).toMatchInlineSnapshot(
84 [
85 {
86 id: expect.stringMatching(/^\d+\.\d+$/),
87 table_id: expect.any(Number),
88 },
89 {
90 id: expect.stringMatching(/^\d+\.\d+$/),
91 table_id: expect.any(Number),
92 },
93 ],
94 `
95 [
96 {
97 "check": null,
98 "comment": null,
99 "data_type": "text",
100 "default_value": null,
101 "enums": [],
102 "format": "text",
103 "format_schema": "pg_catalog",
104 "id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
105 "identity_generation": null,
106 "is_generated": false,
107 "is_identity": false,
108 "is_nullable": true,
109 "is_unique": false,
110 "is_updatable": true,
111 "name": "c1",
112 "ordinal_position": 1,
113 "schema": "public",
114 "table": "t",
115 "table_id": Any<Number>,
116 },
117 {
118 "check": null,
119 "comment": null,
120 "data_type": "text",
121 "default_value": null,
122 "enums": [],
123 "format": "text",
124 "format_schema": "pg_catalog",
125 "id": StringMatching /\\^\\\\d\\+\\\\\\.\\\\d\\+\\$/,
126 "identity_generation": null,
127 "is_generated": false,
128 "is_identity": false,
129 "is_nullable": true,
130 "is_unique": false,
131 "is_updatable": true,
132 "name": "c2",
133 "ordinal_position": 2,
134 "schema": "public",
135 "table": "t",
136 "table_id": Any<Number>,
137 },
138 ]
139 `
140 )
141})
142
143withTestDatabase('list columns with included schemas', async ({ executeQuery }) => {
144 const { sql, zod } = await pgMeta.columns.list({
145 includedSchemas: ['public'],
146 })
147 const res = zod.parse(await executeQuery(sql))
148
149 expect(res.length).toBeGreaterThan(0)
150 res.forEach((column) => {
151 expect(column.schema).toBe('public')
152 })
153})
154
155withTestDatabase('list columns with excluded schemas', async ({ executeQuery }) => {
156 const { sql, zod } = await pgMeta.columns.list({
157 excludedSchemas: ['public'],
158 })
159 const res = zod.parse(await executeQuery(sql))
160
161 res.forEach((column) => {
162 expect(column.schema).not.toBe('public')
163 })
164})
165
166withTestDatabase(
167 'list columns with excluded schemas and include System Schemas',
168 async ({ executeQuery }) => {
169 const { sql, zod } = await pgMeta.columns.list({
170 excludedSchemas: ['public'],
171 includeSystemSchemas: true,
172 })
173 const res = zod.parse(await executeQuery(sql))
174
175 expect(res.length).toBeGreaterThan(0)
176 res.forEach((column) => {
177 expect(column.schema).not.toBe('public')
178 })
179 }
180)
181
182withTestDatabase('retrieve, create, update, delete column', async ({ executeQuery }) => {
183 // Create test table using pure SQL
184 await executeQuery('CREATE TABLE t ()')
185
186 // Create column
187 const { sql: createColumnSql } = await pgMeta.columns.create({
188 schema: 'public',
189 table: 't',
190 name: 'c',
191 type: { name: 'int2' },
192 default_value: 42,
193 comment: 'foo',
194 })
195 await executeQuery(createColumnSql)
196
197 // Retrieve and verify created column
198 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.columns.retrieve({
199 schema: 'public',
200 table: 't',
201 name: 'c',
202 })
203 let column = retrieveZod.parse((await executeQuery(retrieveSql))[0])
204 expect(column).toMatchInlineSnapshot(
205 {
206 id: expect.stringMatching(/^\d+\.1$/),
207 table_id: expect.any(Number),
208 },
209 `
210 {
211 "check": null,
212 "comment": "foo",
213 "data_type": "smallint",
214 "default_value": "42",
215 "enums": [],
216 "format": "int2",
217 "format_schema": "pg_catalog",
218 "id": StringMatching /\\^\\\\d\\+\\\\\\.1\\$/,
219 "identity_generation": null,
220 "is_generated": false,
221 "is_identity": false,
222 "is_nullable": true,
223 "is_unique": false,
224 "is_updatable": true,
225 "name": "c",
226 "ordinal_position": 1,
227 "schema": "public",
228 "table": "t",
229 "table_id": Any<Number>,
230 }
231 `
232 )
233
234 // Update column
235 const { sql: updateSql } = await pgMeta.columns.update(column!, {
236 name: 'c1',
237 type: { name: 'int4' },
238 drop_default: true,
239 is_identity: true,
240 identity_generation: 'ALWAYS',
241 is_nullable: false,
242 comment: 'bar',
243 })
244 await executeQuery(updateSql)
245
246 // Verify updated column
247 const { sql: retrieveUpdatedSql, zod: retrieveUpdatedZod } = await pgMeta.columns.retrieve({
248 schema: 'public',
249 table: 't',
250 name: 'c1',
251 })
252 column = retrieveUpdatedZod.parse((await executeQuery(retrieveUpdatedSql))[0])
253 expect(column).toMatchInlineSnapshot(
254 {
255 id: expect.stringMatching(/^\d+\.1$/),
256 table_id: expect.any(Number),
257 },
258 `
259 {
260 "check": null,
261 "comment": "bar",
262 "data_type": "integer",
263 "default_value": null,
264 "enums": [],
265 "format": "int4",
266 "format_schema": "pg_catalog",
267 "id": StringMatching /\\^\\\\d\\+\\\\\\.1\\$/,
268 "identity_generation": "ALWAYS",
269 "is_generated": false,
270 "is_identity": true,
271 "is_nullable": false,
272 "is_unique": false,
273 "is_updatable": true,
274 "name": "c1",
275 "ordinal_position": 1,
276 "schema": "public",
277 "table": "t",
278 "table_id": Any<Number>,
279 }
280 `
281 )
282
283 // Remove column
284 const { sql: removeSql } = await pgMeta.columns.remove(column!)
285 await executeQuery(removeSql)
286
287 // Verify column was removed
288 const { sql: retrieveRemovedSql, zod: retrieveRemovedZod } = await pgMeta.columns.retrieve({
289 schema: 'public',
290 table: 't',
291 name: 'c1',
292 })
293 const removedColumn = retrieveRemovedZod.parse((await executeQuery(retrieveRemovedSql))[0])
294 expect(removedColumn).toBeUndefined()
295})
296
297withTestDatabase('enum column with quoted name', async ({ executeQuery }) => {
298 await executeQuery('CREATE TYPE "T" AS ENUM (\'v\'); CREATE TABLE t ( c "T" );')
299
300 const { sql, zod } = await pgMeta.columns.list()
301 const res = zod.parse(await executeQuery(sql))
302
303 expect(res.find(({ table }) => table === 't')).toMatchInlineSnapshot(
304 {
305 id: expect.stringMatching(/^\d+\.1$/),
306 table_id: expect.any(Number),
307 },
308 `
309 {
310 "check": null,
311 "comment": null,
312 "data_type": "USER-DEFINED",
313 "default_value": null,
314 "enums": [
315 "v",
316 ],
317 "format": "T",
318 "format_schema": "public",
319 "id": StringMatching /\\^\\\\d\\+\\\\\\.1\\$/,
320 "identity_generation": null,
321 "is_generated": false,
322 "is_identity": false,
323 "is_nullable": true,
324 "is_unique": false,
325 "is_updatable": true,
326 "name": "c",
327 "ordinal_position": 1,
328 "schema": "public",
329 "table": "t",
330 "table_id": Any<Number>,
331 }
332 `
333 )
334})
335
336withTestDatabase('primary key column', async ({ executeQuery }) => {
337 // Create test table using pure SQL
338 await executeQuery('CREATE TABLE t ()')
339
340 // Create column with primary key
341 const { sql: createColumnSql } = await pgMeta.columns.create({
342 schema: 'public',
343 table: 't',
344 name: 'c',
345 type: { name: 'int2' },
346 is_primary_key: true,
347 })
348 await executeQuery(createColumnSql)
349
350 // Verify primary key using pure SQL
351 const primaryKeyResult = await executeQuery(`
352 SELECT a.attname
353 FROM pg_index i
354 JOIN pg_attribute a ON a.attrelid = i.indrelid
355 AND a.attnum = ANY(i.indkey)
356 WHERE i.indrelid = 't'::regclass
357 AND i.indisprimary;
358 `)
359
360 expect(primaryKeyResult).toMatchInlineSnapshot(`
361 [
362 {
363 "attname": "c",
364 },
365 ]
366 `)
367})
368
369withTestDatabase('unique column', async ({ executeQuery }) => {
370 // Create test table using pure SQL
371 await executeQuery('CREATE TABLE t ()')
372
373 // Create column with unique constraint
374 const { sql: createColumnSql } = await pgMeta.columns.create({
375 schema: 'public',
376 table: 't',
377 name: 'c',
378 type: { name: 'int2' },
379 is_unique: true,
380 })
381 await executeQuery(createColumnSql)
382
383 // Verify unique constraint using pure SQL
384 const uniqueResult = await executeQuery(`
385 SELECT a.attname
386 FROM pg_index i
387 JOIN pg_constraint c ON c.conindid = i.indexrelid
388 JOIN pg_attribute a ON a.attrelid = i.indrelid
389 AND a.attnum = ANY(i.indkey)
390 WHERE i.indrelid = 't'::regclass
391 AND i.indisunique;
392 `)
393
394 expect(uniqueResult).toMatchInlineSnapshot(`
395 [
396 {
397 "attname": "c",
398 },
399 ]
400 `)
401})
402
403describe('array column', async () => {
404 const db = await createTestDatabase()
405 await db.executeQuery('CREATE TABLE t ()')
406
407 afterAll(async () => {
408 await db.cleanup()
409 })
410
411 test.concurrent.for([
412 // numerical types
413 { type: 'int2', etype: '_int2' },
414 { type: 'int4', etype: '_int4' },
415 { type: 'int8', etype: '_int8' },
416 { type: 'float4', etype: '_float4' },
417 { type: 'float8', etype: '_float8' },
418 { type: 'numeric', etype: '_numeric' },
419 // json types
420 { type: 'json', etype: '_json' },
421 { type: 'jsonb', etype: '_jsonb' },
422 // text types
423 { type: 'text', etype: '_text' },
424 { type: 'varchar', etype: '_varchar' },
425 // datetime types
426 { type: 'timestamp', etype: '_timestamp' },
427 { type: 'timestamptz', etype: '_timestamptz' },
428 { type: 'date', etype: '_date' },
429 { type: 'time', etype: '_time' },
430 { type: 'timetz', etype: '_timetz' },
431 // other types
432 { type: 'uuid', etype: '_uuid' },
433 { type: 'bool', etype: '_bool' },
434 { type: 'bytea', etype: '_bytea' },
435 ])('$type[] -> $etype', async (c, { expect, task }) => {
436 const id = { schema: 'public', table: 't', name: `c${task.id}` }
437
438 // Create column with default value
439 const { sql } = pgMeta.columns.create({
440 ...id,
441 type: { name: c.type, isArray: true },
442 default_value: null,
443 })
444 await db.executeQuery(sql)
445
446 // Retrieve and verify the created column
447 const expected = pgMeta.columns.retrieve(id)
448 const result = await db.executeQuery(expected.sql)
449 const column = expected.zod.parse(result[0])
450
451 expect(column).toStrictEqual({
452 ...id,
453 data_type: 'ARRAY',
454 default_value: null,
455 format: c.etype,
456 format_schema: 'pg_catalog',
457 id: expect.stringMatching(/^\d+\.\d+$/),
458 ordinal_position: expect.any(Number),
459 table_id: expect.any(Number),
460 check: null,
461 comment: null,
462 enums: [],
463 identity_generation: null,
464 is_generated: false,
465 is_identity: false,
466 is_nullable: true,
467 is_unique: false,
468 is_updatable: true,
469 })
470 })
471})
472
473describe('column with default value', async () => {
474 const db = await createTestDatabase()
475 await db.executeQuery('CREATE TABLE t ()')
476
477 afterAll(async () => {
478 await db.cleanup()
479 })
480
481 test.concurrent.for([
482 // numerical types
483 { type: 'int2', value: 0, etype: 'smallint', evalue: '0' },
484 { type: 'int4', value: 1, etype: 'integer', evalue: '1' },
485 { type: 'int8', value: -1, etype: 'bigint', evalue: `'-1'::integer` },
486 { type: 'float4', value: 0.1, etype: 'real', evalue: '0.1' },
487 { type: 'float8', value: -0.1, etype: 'double precision', evalue: `'-0.1'::numeric` },
488 { type: 'numeric', value: 1e2, etype: 'numeric', evalue: '100' },
489 // json types
490 {
491 type: 'json',
492 value: { a: 0, b: '1', c: true },
493 etype: 'json',
494 evalue: `'{\"a\": 0, \"b\": \"1\", \"c\": true}'::jsonb`,
495 },
496 // json array must be stringified, otherwise it will be converted to pg array literal
497 { type: 'jsonb', value: JSON.stringify([null]), etype: 'jsonb', evalue: `'[null]'::jsonb` },
498 // text types
499 { type: 'text', value: `quote's`, etype: 'text', evalue: `'quote''s'::text` },
500 { type: 'varchar', value: '\n', etype: 'character varying', evalue: `'\n'::character varying` },
501 // datetime types
502 {
503 type: 'timestamp',
504 value: `now() - INTERVAL '1 day'`,
505 etype: 'timestamp without time zone',
506 evalue: `(now() - '1 day'::interval)`,
507 exp: true,
508 },
509 {
510 type: 'timestamptz',
511 value: 'NOW()',
512 etype: 'timestamp with time zone',
513 evalue: 'now()',
514 exp: true,
515 },
516 { type: 'date', value: '2025-05-09', etype: 'date', evalue: `'2025-05-09'::date` },
517 {
518 type: 'time',
519 value: '11:22:33',
520 etype: 'time without time zone',
521 evalue: `'11:22:33'::time without time zone`,
522 },
523 {
524 type: 'timetz',
525 value: '11:22:33+0800',
526 etype: 'time with time zone',
527 evalue: `'11:22:33+08'::time with time zone`,
528 },
529 // other types
530 {
531 type: 'uuid',
532 value: 'gen_random_uuid()',
533 etype: 'uuid',
534 evalue: 'gen_random_uuid()',
535 exp: true,
536 },
537 { type: 'bool', value: true, etype: 'boolean', evalue: 'true' },
538 // https://www.postgresql.org/docs/current/datatype-binary.html#DATATYPE-BINARY-BYTEA-ESCAPE-FORMAT
539 { type: 'bytea', value: `\\000`, etype: 'bytea', evalue: `'\\x00'::bytea` },
540 ])('$type -> $value', async (c, { expect, task }) => {
541 const id = { schema: 'public', table: 't', name: `c${task.id}` }
542
543 // Create column with default value
544 const { sql } = pgMeta.columns.create(
545 c.exp
546 ? {
547 ...id,
548 type: { name: c.type },
549 default_value_format: 'expression',
550 default_value: rawSql(String(c.value)),
551 }
552 : { ...id, type: { name: c.type }, default_value_format: 'literal', default_value: c.value }
553 )
554 await db.executeQuery(sql)
555
556 // Retrieve and verify the created column
557 const expected = pgMeta.columns.retrieve(id)
558 const result = await db.executeQuery(expected.sql)
559 const column = expected.zod.parse(result[0])
560
561 expect(column).toStrictEqual({
562 ...id,
563 data_type: c.etype,
564 default_value: c.evalue,
565 format: c.type,
566 format_schema: 'pg_catalog',
567 id: expect.stringMatching(/^\d+\.\d+$/),
568 ordinal_position: expect.any(Number),
569 table_id: expect.any(Number),
570 check: null,
571 comment: null,
572 enums: [],
573 identity_generation: null,
574 is_generated: false,
575 is_identity: false,
576 is_nullable: true,
577 is_unique: false,
578 is_updatable: true,
579 })
580 })
581})
582
583// https://github.com/supabase/supabase/issues/3553
584withTestDatabase('alter column to type with uppercase', async ({ executeQuery }) => {
585 // Setup: Create table and type
586 await executeQuery('CREATE TABLE t ()')
587 await executeQuery('CREATE TYPE "T" AS ENUM ()')
588
589 // Create and then update column
590 const { sql: createSql } = await pgMeta.columns.create({
591 schema: 'public',
592 table: 't',
593 name: 'c',
594 type: { name: 'text' },
595 is_unique: false,
596 })
597 await executeQuery(createSql)
598
599 // Get column ID
600 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.columns.retrieve({
601 schema: 'public',
602 table: 't',
603 name: 'c',
604 })
605 const column = retrieveZod.parse((await executeQuery(retrieveSql))[0])
606
607 // Update column
608 const { sql: updateSql } = await pgMeta.columns.update(column!, { type: { name: 'T' } })
609 await executeQuery(updateSql)
610
611 // Verify updated column
612 const { sql: verifySQL, zod: verifyZod } = await pgMeta.columns.retrieve({
613 schema: 'public',
614 table: 't',
615 name: 'c',
616 })
617 const updatedColumn = verifyZod.parse((await executeQuery(verifySQL))[0])
618
619 expect(updatedColumn).toMatchInlineSnapshot(
620 {
621 id: expect.stringMatching(/^\d+\.1$/),
622 table_id: expect.any(Number),
623 },
624 `
625 {
626 "check": null,
627 "comment": null,
628 "data_type": "USER-DEFINED",
629 "default_value": null,
630 "enums": [],
631 "format": "T",
632 "format_schema": "public",
633 "id": StringMatching /\\^\\\\d\\+\\\\\\.1\\$/,
634 "identity_generation": null,
635 "is_generated": false,
636 "is_identity": false,
637 "is_nullable": true,
638 "is_unique": false,
639 "is_updatable": true,
640 "name": "c",
641 "ordinal_position": 1,
642 "schema": "public",
643 "table": "t",
644 "table_id": Any<Number>,
645 }
646 `
647 )
648})
649
650withTestDatabase('enums are populated in enum array columns', async ({ executeQuery }) => {
651 // Setup: Create type and table
652 await executeQuery(`CREATE TYPE test_enum AS ENUM ('a')`)
653 await executeQuery('CREATE TABLE t ()')
654
655 // Create column
656 const { sql: createSql } = pgMeta.columns.create({
657 schema: 'public',
658 table: 't',
659 name: 'c',
660 type: { name: 'test_enum', isArray: true },
661 })
662 await executeQuery(createSql)
663
664 // Verify created column
665 const { sql: verifySql, zod: verifyZod } = pgMeta.columns.retrieve({
666 schema: 'public',
667 table: 't',
668 name: 'c',
669 })
670 const column = verifyZod.parse((await executeQuery(verifySql))[0])
671
672 expect(column).toMatchInlineSnapshot(
673 {
674 id: expect.stringMatching(/^\d+\.1$/),
675 table_id: expect.any(Number),
676 },
677 `
678 {
679 "check": null,
680 "comment": null,
681 "data_type": "ARRAY",
682 "default_value": null,
683 "enums": [
684 "a",
685 ],
686 "format": "_test_enum",
687 "format_schema": "public",
688 "id": StringMatching /\\^\\\\d\\+\\\\\\.1\\$/,
689 "identity_generation": null,
690 "is_generated": false,
691 "is_identity": false,
692 "is_nullable": true,
693 "is_unique": false,
694 "is_updatable": true,
695 "name": "c",
696 "ordinal_position": 1,
697 "schema": "public",
698 "table": "t",
699 "table_id": Any<Number>,
700 }
701 `
702 )
703})
704
705withTestDatabase('drop with cascade', async ({ executeQuery }) => {
706 // Setup table with generated column
707 await executeQuery(`
708 create table public.t (
709 id int8 primary key,
710 t_id int8 generated always as (id) stored
711 );
712 `)
713
714 // Retrieve column
715 const { sql: retrieveSql, zod: retrieveZod } = pgMeta.columns.retrieve({
716 schema: 'public',
717 table: 't',
718 name: 'id',
719 })
720 const column = retrieveZod.parse((await executeQuery(retrieveSql))[0])
721
722 // Remove column with cascade
723 const { sql: removeSql } = pgMeta.columns.remove(column!, { cascade: true })
724 await executeQuery(removeSql)
725
726 // Verify original column was removed
727 const { sql: verifyIdSql, zod: verifyIdZod } = pgMeta.columns.retrieve({
728 schema: 'public',
729 table: 't',
730 name: 'id',
731 })
732 const removedColumn = verifyIdZod.parse((await executeQuery(verifyIdSql))[0])
733 expect(removedColumn).toBeUndefined()
734
735 // Verify dependent column was also removed
736 const { sql: verifyTIdSql, zod: verifyTIdZod } = pgMeta.columns.retrieve({
737 schema: 'public',
738 table: 't',
739 name: 't_id',
740 })
741 const dependentColumn = verifyTIdZod.parse((await executeQuery(verifyTIdSql))[0])
742 expect(dependentColumn).toBeUndefined()
743})
744
745withTestDatabase('column with multiple checks', async ({ executeQuery }) => {
746 // Setup table with multiple check constraints
747 await executeQuery('create table t(c int8 check (c != 0) check (c != -1))')
748
749 // List columns
750 const { sql, zod } = pgMeta.columns.list()
751 const res = zod.parse(await executeQuery(sql))
752
753 const columns = res
754 .filter((c) => c.schema === 'public' && c.table === 't')
755 .map(({ id, table_id, ...c }) => c)
756
757 expect(columns).toMatchInlineSnapshot(`
758 [
759 {
760 "check": "c <> 0",
761 "comment": null,
762 "data_type": "bigint",
763 "default_value": null,
764 "enums": [],
765 "format": "int8",
766 "format_schema": "pg_catalog",
767 "identity_generation": null,
768 "is_generated": false,
769 "is_identity": false,
770 "is_nullable": true,
771 "is_unique": false,
772 "is_updatable": true,
773 "name": "c",
774 "ordinal_position": 1,
775 "schema": "public",
776 "table": "t",
777 },
778 ]
779 `)
780})
781
782withTestDatabase('column with multiple unique constraints', async ({ executeQuery }) => {
783 // Setup table with multiple unique constraints
784 await executeQuery(`create table t(c int8 unique); alter table t add unique (c);`)
785
786 // List columns
787 const { sql, zod } = await pgMeta.columns.list()
788 const res = zod.parse(await executeQuery(sql))
789
790 const columns = res
791 .filter((c) => c.schema === 'public' && c.table === 't')
792 .map(({ id, table_id, ...c }) => c)
793
794 expect(columns).toMatchInlineSnapshot(`
795 [
796 {
797 "check": null,
798 "comment": null,
799 "data_type": "bigint",
800 "default_value": null,
801 "enums": [],
802 "format": "int8",
803 "format_schema": "pg_catalog",
804 "identity_generation": null,
805 "is_generated": false,
806 "is_identity": false,
807 "is_nullable": true,
808 "is_unique": true,
809 "is_updatable": true,
810 "name": "c",
811 "ordinal_position": 1,
812 "schema": "public",
813 "table": "t",
814 },
815 ]
816 `)
817})
818
819withTestDatabase('dropping column checks', async ({ executeQuery }) => {
820 // Setup table with check constraint
821 await executeQuery(`create table public.t(c int8 check (c != 0))`)
822
823 // Retrieve column
824 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.columns.retrieve({
825 schema: 'public',
826 table: 't',
827 name: 'c',
828 })
829 const column = retrieveZod.parse((await executeQuery(retrieveSql))[0])
830
831 // Update column to remove check
832 const { sql: updateSql } = await pgMeta.columns.update(column!, { check: null })
833 await executeQuery(updateSql)
834
835 // Verify updated column
836 const { sql: verifySql, zod: verifyZod } = await pgMeta.columns.retrieve({
837 schema: 'public',
838 table: 't',
839 name: 'c',
840 })
841 const updatedColumn = verifyZod.parse((await executeQuery(verifySql))[0])
842
843 expect(updatedColumn!.check).toMatchInlineSnapshot(`null`)
844})
845
846withTestDatabase('column with fully-qualified type', async ({ executeQuery }) => {
847 // Setup: Create table and type in separate schema
848 await executeQuery(`
849 create table public.t();
850 create schema s;
851 create type s.my_type as enum ();
852 `)
853
854 // Create column with fully-qualified type
855 const { sql: createColumnSql } = await pgMeta.columns.create({
856 schema: 'public',
857 table: 't',
858 name: 'c',
859 type: { schema: 's', name: 'my_type' },
860 })
861 await executeQuery(createColumnSql)
862
863 // Retrieve and verify the created column
864 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.columns.retrieve({
865 schema: 'public',
866 table: 't',
867 name: 'c',
868 })
869 const column = retrieveZod.parse((await executeQuery(retrieveSql))[0])
870
871 expect(column).toMatchInlineSnapshot(
872 {
873 id: expect.stringMatching(/^\d+\.1$/),
874 table_id: expect.any(Number),
875 },
876 `
877 {
878 "check": null,
879 "comment": null,
880 "data_type": "USER-DEFINED",
881 "default_value": null,
882 "enums": [],
883 "format": "my_type",
884 "format_schema": "s",
885 "id": StringMatching /\\^\\\\d\\+\\\\\\.1\\$/,
886 "identity_generation": null,
887 "is_generated": false,
888 "is_identity": false,
889 "is_nullable": true,
890 "is_unique": false,
891 "is_updatable": true,
892 "name": "c",
893 "ordinal_position": 1,
894 "schema": "public",
895 "table": "t",
896 "table_id": Any<Number>,
897 }
898 `
899 )
900})
901
902withTestDatabase('format_schema for built-in type', async ({ executeQuery }) => {
903 await executeQuery(`create table t (c int4)`)
904
905 const { sql, zod } = pgMeta.columns.retrieve({ schema: 'public', table: 't', name: 'c' })
906 const column = zod.parse((await executeQuery(sql))[0])
907
908 expect(column?.format).toBe('int4')
909 expect(column?.format_schema).toBe('pg_catalog')
910})
911
912withTestDatabase('format_schema for public-schema enum', async ({ executeQuery }) => {
913 await executeQuery(`create type public_enum as enum ('a'); create table t (c public_enum);`)
914
915 const { sql, zod } = pgMeta.columns.retrieve({ schema: 'public', table: 't', name: 'c' })
916 const column = zod.parse((await executeQuery(sql))[0])
917
918 expect(column?.format).toBe('public_enum')
919 expect(column?.format_schema).toBe('public')
920})
921
922withTestDatabase('format_schema for non-public-schema enum', async ({ executeQuery }) => {
923 await executeQuery(`
924 create schema s;
925 create type s.my_enum as enum ('a');
926 create table t (c s.my_enum);
927 `)
928
929 const { sql, zod } = pgMeta.columns.retrieve({ schema: 'public', table: 't', name: 'c' })
930 const column = zod.parse((await executeQuery(sql))[0])
931
932 expect(column?.format).toBe('my_enum')
933 expect(column?.format_schema).toBe('s')
934})
935
936withTestDatabase('format_schema for array of non-public-schema enum', async ({ executeQuery }) => {
937 await executeQuery(`
938 create schema s;
939 create type s.my_enum as enum ('a');
940 create table t (c s.my_enum[]);
941 `)
942
943 const { sql, zod } = pgMeta.columns.retrieve({ schema: 'public', table: 't', name: 'c' })
944 const column = zod.parse((await executeQuery(sql))[0])
945
946 expect(column?.data_type).toBe('ARRAY')
947 expect(column?.format).toBe('_my_enum')
948 expect(column?.format_schema).toBe('s')
949})
950
951withTestDatabase(
952 'format_schema for domain over non-public-schema base type',
953 async ({ executeQuery }) => {
954 await executeQuery(`
955 create schema s;
956 create domain s.my_domain as int4;
957 create table t (c s.my_domain);
958 `)
959
960 const { sql, zod } = pgMeta.columns.retrieve({ schema: 'public', table: 't', name: 'c' })
961 const column = zod.parse((await executeQuery(sql))[0])
962
963 // Domain's `format` resolves to the base type, so `format_schema` follows the base type.
964 expect(column?.format).toBe('int4')
965 expect(column?.format_schema).toBe('pg_catalog')
966 }
967)