konnosDashboard
functions.test.ts378 lines · main
1import { afterAll, expect, test } from 'vitest'
2
3import pgMeta, { safeSql } from '../src/index'
4import type { PGFunction, PGSavedFunction } from '../src/pg-meta-functions'
5import { cleanupRoot, createTestDatabase } from './db/utils'
6
7// Test fixtures originate from `executeQuery` results that match the
8// API/database boundary; brand the raw-SQL fields so they satisfy
9// `update`/`remove` parameter types.
10const asSavedFunction = (fn: PGFunction): PGSavedFunction => fn as unknown as PGSavedFunction
11
12afterAll(async () => {
13 await cleanupRoot()
14})
15
16const withTestDatabase = (
17 name: string,
18 fn: (db: Awaited<ReturnType<typeof createTestDatabase>>) => Promise<void>
19) => {
20 test(name, async () => {
21 const db = await createTestDatabase()
22 try {
23 await fn(db)
24 } finally {
25 await db.cleanup()
26 }
27 })
28}
29
30withTestDatabase('list functions', async ({ executeQuery }) => {
31 const { sql, zod } = pgMeta.functions.list()
32 const res = zod.parse(await executeQuery(sql))
33
34 // Test for the 'add' function created in init.sql
35 const addFunction = res.find(({ name }) => name === 'add')
36 expect(addFunction).toMatchInlineSnapshot(
37 { id: expect.any(Number) },
38 `
39 {
40 "args": [
41 {
42 "has_default": false,
43 "mode": "in",
44 "name": "",
45 "type_id": 23,
46 },
47 {
48 "has_default": false,
49 "mode": "in",
50 "name": "",
51 "type_id": 23,
52 },
53 ],
54 "argument_types": "integer, integer",
55 "behavior": "IMMUTABLE",
56 "complete_statement": "CREATE OR REPLACE FUNCTION public.add(integer, integer)
57 RETURNS integer
58 LANGUAGE sql
59 IMMUTABLE STRICT
60 AS $function$select $1 + $2;$function$
61 ",
62 "config_params": null,
63 "definition": "select $1 + $2;",
64 "id": Any<Number>,
65 "identity_argument_types": "integer, integer",
66 "is_set_returning_function": false,
67 "language": "sql",
68 "name": "add",
69 "return_type": "integer",
70 "return_type_id": 23,
71 "return_type_relation_id": null,
72 "schema": "public",
73 "security_definer": false,
74 }
75 `
76 )
77})
78
79withTestDatabase('list functions with included schemas', async ({ executeQuery }) => {
80 const { sql, zod } = pgMeta.functions.list({
81 includedSchemas: ['public'],
82 })
83 const res = zod.parse(await executeQuery(sql))
84
85 expect(res.length).toBeGreaterThan(0)
86 res.forEach((func) => {
87 expect(func.schema).toBe('public')
88 })
89})
90
91withTestDatabase('list functions with excluded schemas', async ({ executeQuery }) => {
92 const { sql, zod } = pgMeta.functions.list({
93 excludedSchemas: ['public'],
94 })
95 const res = zod.parse(await executeQuery(sql))
96
97 res.forEach((func) => {
98 expect(func.schema).not.toBe('public')
99 })
100})
101
102withTestDatabase(
103 'list functions with excluded schemas and include System Schemas',
104 async ({ executeQuery }) => {
105 const { sql, zod } = pgMeta.functions.list({
106 excludedSchemas: ['public'],
107 includeSystemSchemas: true,
108 })
109 const res = zod.parse(await executeQuery(sql))
110
111 expect(res.length).toBeGreaterThan(0)
112 res.forEach((func) => {
113 expect(func.schema).not.toBe('public')
114 })
115 }
116)
117
118withTestDatabase('retrieve, create, update, delete', async ({ executeQuery }) => {
119 // Create function
120 const { sql: createSql } = pgMeta.functions.create({
121 name: 'test_func',
122 schema: 'public',
123 args: [safeSql`a int2`, safeSql`b int2`],
124 definition: 'select a + b',
125 return_type: safeSql`integer`,
126 language: 'sql',
127 behavior: 'STABLE',
128 security_definer: true,
129 config_params: { search_path: safeSql`hooks, auth`, role: safeSql`postgres` },
130 })
131 await executeQuery(createSql)
132
133 // // Retrieve function
134 const { sql: retrieveSql, zod: retrieveZod } = pgMeta.functions.retrieve({
135 name: 'test_func',
136 schema: 'public',
137 args: ['a int2', 'b int2'],
138 })
139 const retrieve = await executeQuery(retrieveSql)
140 const res = retrieveZod.parse(retrieve[0])
141 const functionId = res!.id
142 expect({ data: res, error: null }).toMatchInlineSnapshot(
143 { data: { id: expect.any(Number) } },
144 `
145 {
146 "data": {
147 "args": [
148 {
149 "has_default": false,
150 "mode": "in",
151 "name": "a",
152 "type_id": 21,
153 },
154 {
155 "has_default": false,
156 "mode": "in",
157 "name": "b",
158 "type_id": 21,
159 },
160 ],
161 "argument_types": "a smallint, b smallint",
162 "behavior": "STABLE",
163 "complete_statement": "CREATE OR REPLACE FUNCTION public.test_func(a smallint, b smallint)
164 RETURNS integer
165 LANGUAGE sql
166 STABLE SECURITY DEFINER
167 SET search_path TO 'hooks', 'auth'
168 SET role TO 'postgres'
169 AS $function$select a + b$function$
170 ",
171 "config_params": {
172 "role": "postgres",
173 "search_path": "hooks, auth",
174 },
175 "definition": "select a + b",
176 "id": Any<Number>,
177 "identity_argument_types": "a smallint, b smallint",
178 "is_set_returning_function": false,
179 "language": "sql",
180 "name": "test_func",
181 "return_type": "integer",
182 "return_type_id": 23,
183 "return_type_relation_id": null,
184 "schema": "public",
185 "security_definer": true,
186 },
187 "error": null,
188 }
189 `
190 )
191 // create test_schema to move the function into:
192 const { sql: createSchemaSql } = pgMeta.schemas.create({ name: 'test_schema' })
193 await executeQuery(createSchemaSql)
194 const { sql: updateSql } = pgMeta.functions.update(asSavedFunction(res!), {
195 name: 'test_func_renamed',
196 schema: 'test_schema',
197 definition: 'select b - a',
198 })
199 await executeQuery(updateSql)
200
201 const { sql: retrieveRenamedSql } = pgMeta.functions.retrieve({ id: functionId })
202 const retrieveRenamed = await executeQuery(retrieveRenamedSql)
203 const resUpdated = retrieveZod.parse(retrieveRenamed[0])
204 expect({ data: resUpdated, error: null }).toMatchInlineSnapshot(
205 { data: { id: expect.any(Number) } },
206 `
207 {
208 "data": {
209 "args": [
210 {
211 "has_default": false,
212 "mode": "in",
213 "name": "a",
214 "type_id": 21,
215 },
216 {
217 "has_default": false,
218 "mode": "in",
219 "name": "b",
220 "type_id": 21,
221 },
222 ],
223 "argument_types": "a smallint, b smallint",
224 "behavior": "STABLE",
225 "complete_statement": "CREATE OR REPLACE FUNCTION test_schema.test_func_renamed(a smallint, b smallint)
226 RETURNS integer
227 LANGUAGE sql
228 STABLE SECURITY DEFINER
229 SET role TO 'postgres'
230 SET search_path TO 'hooks', 'auth'
231 AS $function$select b - a$function$
232 ",
233 "config_params": {
234 "role": "postgres",
235 "search_path": "hooks, auth",
236 },
237 "definition": "select b - a",
238 "id": Any<Number>,
239 "identity_argument_types": "a smallint, b smallint",
240 "is_set_returning_function": false,
241 "language": "sql",
242 "name": "test_func_renamed",
243 "return_type": "integer",
244 "return_type_id": 23,
245 "return_type_relation_id": null,
246 "schema": "test_schema",
247 "security_definer": true,
248 },
249 "error": null,
250 }
251 `
252 )
253
254 // Remove function
255 const { sql: removeSql } = pgMeta.functions.remove(asSavedFunction(resUpdated!))
256 await executeQuery(removeSql)
257 // Verify function is removed
258 const { sql: verifyRemoveSql } = pgMeta.functions.retrieve({ id: functionId })
259 const result = await executeQuery(verifyRemoveSql)
260 expect(result).toHaveLength(0)
261})
262
263withTestDatabase('retrieve set-returning function', async ({ executeQuery }) => {
264 // Retrieve function
265 const { sql: retrieveSql, zod: retrieveZod } = pgMeta.functions.retrieve({
266 schema: 'public',
267 name: 'function_returning_set_of_rows',
268 args: [],
269 })
270 const retrieve = await executeQuery(retrieveSql)
271 const res = retrieveZod.parse(retrieve[0])
272 expect(res).toMatchInlineSnapshot(
273 {
274 id: expect.any(Number),
275 return_type_id: expect.any(Number),
276 return_type_relation_id: expect.any(Number),
277 },
278 `
279 {
280 "args": [],
281 "argument_types": "",
282 "behavior": "STABLE",
283 "complete_statement": "CREATE OR REPLACE FUNCTION public.function_returning_set_of_rows()
284 RETURNS SETOF users
285 LANGUAGE sql
286 STABLE
287 AS $function$
288 select * from public.users;
289 $function$
290 ",
291 "config_params": null,
292 "definition": "
293 select * from public.users;
294 ",
295 "id": Any<Number>,
296 "identity_argument_types": "",
297 "is_set_returning_function": true,
298 "language": "sql",
299 "name": "function_returning_set_of_rows",
300 "return_type": "SETOF users",
301 "return_type_id": Any<Number>,
302 "return_type_relation_id": Any<Number>,
303 "schema": "public",
304 "security_definer": false,
305 }
306 `
307 )
308})
309
310withTestDatabase('create function with various config_params values', async ({ executeQuery }) => {
311 // Set initial application_name for consistent testing
312 await executeQuery("SET application_name = 'current-app-name'")
313
314 const { sql: createSql1 } = pgMeta.functions.create({
315 name: 'test_func_config_1',
316 schema: 'public',
317 definition: 'select 1',
318 return_type: safeSql`integer`,
319 language: 'sql',
320 config_params: {
321 search_path: safeSql`''`, // Quoted empty string
322 application_name: 'FROM CURRENT', // Special syntax: SET param FROM CURRENT
323 work_mem: safeSql`'8MB'`, // Regular syntax: SET param TO value
324 },
325 })
326 await executeQuery(createSql1)
327
328 // Verify the function was created correctly
329 const { sql: retrieveSql1, zod: retrieveZod } = pgMeta.functions.retrieve({
330 name: 'test_func_config_1',
331 schema: 'public',
332 args: [],
333 })
334 const result1 = retrieveZod.parse((await executeQuery(retrieveSql1))[0])
335
336 expect(result1).toBeDefined()
337 expect(result1!.config_params).toEqual({
338 search_path: '""',
339 application_name: 'current-app-name',
340 work_mem: '8MB',
341 })
342
343 // Clean up
344 const { sql: removeSql1 } = pgMeta.functions.remove(asSavedFunction(result1!))
345 await executeQuery(removeSql1)
346})
347
348withTestDatabase(
349 'create function with namespaced custom GUC config_params',
350 async ({ executeQuery }) => {
351 const { sql: createSql } = pgMeta.functions.create({
352 name: 'test_func_namespaced_guc',
353 schema: 'public',
354 definition: 'select 1',
355 return_type: safeSql`integer`,
356 language: 'sql',
357 config_params: {
358 'app.jwt_secret': safeSql`'top-secret'`,
359 },
360 })
361 await executeQuery(createSql)
362
363 const { sql: retrieveSql, zod: retrieveZod } = pgMeta.functions.retrieve({
364 name: 'test_func_namespaced_guc',
365 schema: 'public',
366 args: [],
367 })
368 const result = retrieveZod.parse((await executeQuery(retrieveSql))[0])
369
370 expect(result).toBeDefined()
371 expect(result!.config_params).toEqual({
372 'app.jwt_secret': 'top-secret',
373 })
374
375 const { sql: removeSql } = pgMeta.functions.remove(asSavedFunction(result!))
376 await executeQuery(removeSql)
377 }
378)