konnosDashboard
indexes.test.ts200 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 withTestDatabase = (
15 name: string,
16 fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
17) => {
18 test(name, async () => {
19 const db = await createTestDatabase()
20 try {
21 await fn(db)
22 } finally {
23 await db.cleanup()
24 }
25 })
26}
27
28withTestDatabase('list indexes', async ({ executeQuery }) => {
29 // List indexes
30 const { sql: listSql, zod: listZod } = await pgMeta.indexes.list()
31 const indexes = listZod.parse(await executeQuery(listSql))
32 const usersPkeyIndex = indexes.find(
33 ({ index_definition }) =>
34 index_definition === 'CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)'
35 )!
36
37 expect(usersPkeyIndex).toMatchInlineSnapshot(
38 `
39 {
40 "access_method": "btree",
41 "check_xmin": false,
42 "class": [
43 3124,
44 ],
45 "collation": [
46 0,
47 ],
48 "comment": null,
49 "id": 16399,
50 "index_attributes": [
51 {
52 "attribute_name": "id",
53 "attribute_number": 1,
54 "data_type": "bigint",
55 },
56 ],
57 "index_definition": "CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)",
58 "index_predicate": null,
59 "is_clustered": false,
60 "is_exclusion": false,
61 "is_immediate": true,
62 "is_live": true,
63 "is_primary": true,
64 "is_ready": true,
65 "is_replica_identity": false,
66 "is_unique": true,
67 "is_valid": true,
68 "key_attributes": [
69 1,
70 ],
71 "number_of_attributes": 1,
72 "number_of_key_attributes": 1,
73 "options": [
74 0,
75 ],
76 "schema": "public",
77 "table_id": 16393,
78 }
79 `
80 )
81})
82
83withTestDatabase('retrieve index', async ({ executeQuery }) => {
84 // Retrieve specific index
85 const { sql: retrieveSql, zod: retrieveZod } = await pgMeta.indexes.retrieve({
86 id: 16399,
87 })
88 const index = retrieveZod.parse((await executeQuery(retrieveSql))[0])
89 expect(index).toMatchInlineSnapshot(
90 `
91 {
92 "access_method": "btree",
93 "check_xmin": false,
94 "class": [
95 3124,
96 ],
97 "collation": [
98 0,
99 ],
100 "comment": null,
101 "id": 16399,
102 "index_attributes": [
103 {
104 "attribute_name": "id",
105 "attribute_number": 1,
106 "data_type": "bigint",
107 },
108 ],
109 "index_definition": "CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)",
110 "index_predicate": null,
111 "is_clustered": false,
112 "is_exclusion": false,
113 "is_immediate": true,
114 "is_live": true,
115 "is_primary": true,
116 "is_ready": true,
117 "is_replica_identity": false,
118 "is_unique": true,
119 "is_valid": true,
120 "key_attributes": [
121 1,
122 ],
123 "number_of_attributes": 1,
124 "number_of_key_attributes": 1,
125 "options": [
126 0,
127 ],
128 "schema": "public",
129 "table_id": 16393,
130 }
131 `
132 )
133})
134
135withTestDatabase('list with filters', async ({ executeQuery }) => {
136 // Test includeSystemSchemas
137 const { sql: withSystemSql, zod } = await pgMeta.indexes.list({ includeSystemSchemas: true })
138 const withSystem = zod.parse(await executeQuery(withSystemSql))
139 expect(withSystem.some((idx) => idx.schema === 'pg_catalog')).toBe(true)
140
141 // Test without system schemas (default)
142 const { sql: withoutSystemSql, zod: withoutSystemZod } = await pgMeta.indexes.list()
143 const withoutSystem = withoutSystemZod.parse(await executeQuery(withoutSystemSql))
144 expect(withoutSystem.some((idx) => idx.schema === 'pg_catalog')).toBe(false)
145
146 // Test includedSchemas
147 const { sql: includedSchemasSql, zod: includedSchemasZod } = await pgMeta.indexes.list({
148 includedSchemas: ['public'],
149 })
150 const includedSchemas = includedSchemasZod.parse(await executeQuery(includedSchemasSql))
151 expect(includedSchemas.every((idx) => idx.schema === 'public')).toBe(true)
152
153 // Test excludedSchemas
154 const { sql: excludedSchemasSql, zod: excludedSchemasZod } = await pgMeta.indexes.list({
155 excludedSchemas: ['public'],
156 })
157 const excludedSchemas = excludedSchemasZod.parse(await executeQuery(excludedSchemasSql))
158 expect(excludedSchemas.some((idx) => idx.schema === 'public')).toBe(false)
159
160 // Test limit and offset
161 const { sql: limitSql, zod: limitZod } = await pgMeta.indexes.list({ limit: 1 })
162 const limited = limitZod.parse(await executeQuery(limitSql))
163 expect(limited).toHaveLength(1)
164
165 const { sql: offsetSql, zod: offsetZod } = await pgMeta.indexes.list({ offset: 1 })
166 const offset = offsetZod.parse(await executeQuery(offsetSql))
167 expect(offset).toHaveLength(withoutSystem.length - 1)
168})
169
170withTestDatabase('handles same index name across schemas correctly', async ({ executeQuery }) => {
171 // Create two schemas
172 await executeQuery(`CREATE SCHEMA schema_a;`)
173 await executeQuery(`CREATE SCHEMA schema_b;`)
174
175 // Create tables
176 await executeQuery(`CREATE TABLE schema_a.test (id int);`)
177 await executeQuery(`CREATE TABLE schema_b.test (id int);`)
178
179 // Create SAME index name in both schemas
180 await executeQuery(`CREATE INDEX idx_test ON schema_a.test (id);`)
181 await executeQuery(`CREATE INDEX idx_test ON schema_b.test (id);`)
182
183 // Fetch indexes
184 const { sql, zod } = await pgMeta.indexes.list({ includeSystemSchemas: true })
185 const indexes = zod.parse(await executeQuery(sql))
186
187 const schemaAIndex = indexes.find(
188 (i) => i.schema === 'schema_a' && i.index_definition.includes('schema_a.test')
189 )
190
191 const schemaBIndex = indexes.find(
192 (i) => i.schema === 'schema_b' && i.index_definition.includes('schema_b.test')
193 )
194
195 expect(schemaAIndex).toBeDefined()
196 expect(schemaBIndex).toBeDefined()
197
198 expect(schemaAIndex!.index_definition).toContain('schema_a.test')
199 expect(schemaBIndex!.index_definition).toContain('schema_b.test')
200})