konnosDashboard
triggers.test.ts365 lines · main
1import { afterAll, beforeAll, expect, test } from 'vitest'
2
3import pgMeta, { safeSql } 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('retrieve, create, update, delete', async ({ executeQuery }) => {
29 // Create trigger
30 const { sql: createSql } = pgMeta.triggers.create({
31 name: 'test_trigger',
32 schema: 'public',
33 table: 'users_audit',
34 function_schema: 'public',
35 function_name: 'audit_action',
36 function_args: ['test1', 'test2'],
37 activation: 'AFTER',
38 events: ['UPDATE'],
39 orientation: 'ROW',
40 condition: safeSql`(old.* IS DISTINCT FROM new.*)`,
41 })
42 await executeQuery(createSql)
43
44 const { sql: listTriggers, zod: listZod } = pgMeta.triggers.list()
45 const listedTriggers = await listZod.parse(await executeQuery(listTriggers))
46 const createdTriggers = listedTriggers.find(
47 (t) => t.name === 'test_trigger' && t.table === 'users_audit' && t.schema === 'public'
48 )
49 expect(createdTriggers!).toMatchInlineSnapshot(
50 { id: expect.any(Number), table_id: expect.any(Number) },
51 `
52{
53 "activation": "AFTER",
54 "condition": "(old.* IS DISTINCT FROM new.*)",
55 "enabled_mode": "ORIGIN",
56 "events": [
57 "UPDATE",
58 ],
59 "function_args": [
60 "test1",
61 "test2",
62 ],
63 "function_name": "audit_action",
64 "function_schema": "public",
65 "id": Any<Number>,
66 "name": "test_trigger",
67 "orientation": "ROW",
68 "schema": "public",
69 "table": "users_audit",
70 "table_id": Any<Number>,
71}
72 `
73 )
74
75 // Retrieve created trigger by name
76 const { sql: retrieveSqlByName, zod: retrieveZod } = pgMeta.triggers.retrieve({
77 name: 'test_trigger',
78 table: 'users_audit',
79 schema: 'public',
80 })
81 const trigger = retrieveZod.parse((await executeQuery(retrieveSqlByName))[0])
82 expect(trigger!).toMatchInlineSnapshot(
83 { id: expect.any(Number), table_id: expect.any(Number) },
84 `
85{
86 "activation": "AFTER",
87 "condition": "(old.* IS DISTINCT FROM new.*)",
88 "enabled_mode": "ORIGIN",
89 "events": [
90 "UPDATE",
91 ],
92 "function_args": [
93 "test1",
94 "test2",
95 ],
96 "function_name": "audit_action",
97 "function_schema": "public",
98 "id": Any<Number>,
99 "name": "test_trigger",
100 "orientation": "ROW",
101 "schema": "public",
102 "table": "users_audit",
103 "table_id": Any<Number>,
104}
105 `
106 )
107
108 // Retrieve created trigger by id
109 const { sql: retrieveSqlById } = pgMeta.triggers.retrieve({
110 id: trigger!.id,
111 })
112 const triggerById = retrieveZod.parse((await executeQuery(retrieveSqlById))[0])
113 expect(triggerById!).toMatchInlineSnapshot(
114 { id: expect.any(Number), table_id: expect.any(Number) },
115 `
116{
117 "activation": "AFTER",
118 "condition": "(old.* IS DISTINCT FROM new.*)",
119 "enabled_mode": "ORIGIN",
120 "events": [
121 "UPDATE",
122 ],
123 "function_args": [
124 "test1",
125 "test2",
126 ],
127 "function_name": "audit_action",
128 "function_schema": "public",
129 "id": Any<Number>,
130 "name": "test_trigger",
131 "orientation": "ROW",
132 "schema": "public",
133 "table": "users_audit",
134 "table_id": Any<Number>,
135}
136 `
137 )
138
139 // Update trigger
140 const { sql: updateSql } = pgMeta.triggers.update(trigger!, {
141 name: 'test_trigger_renamed',
142 enabled_mode: 'DISABLED',
143 })
144 await executeQuery(updateSql)
145
146 // Verify update
147 const { sql: retrieveUpdatedSql } = pgMeta.triggers.retrieve({
148 id: trigger!.id,
149 })
150 const updatedTrigger = retrieveZod.parse((await executeQuery(retrieveUpdatedSql))[0])
151 expect(updatedTrigger).toMatchInlineSnapshot(
152 { id: expect.any(Number), table_id: expect.any(Number) },
153 `
154{
155 "activation": "AFTER",
156 "condition": "(old.* IS DISTINCT FROM new.*)",
157 "enabled_mode": "DISABLED",
158 "events": [
159 "UPDATE",
160 ],
161 "function_args": [
162 "test1",
163 "test2",
164 ],
165 "function_name": "audit_action",
166 "function_schema": "public",
167 "id": Any<Number>,
168 "name": "test_trigger_renamed",
169 "orientation": "ROW",
170 "schema": "public",
171 "table": "users_audit",
172 "table_id": Any<Number>,
173}
174`
175 )
176
177 // Update trigger again
178 const { sql: updateSql2 } = pgMeta.triggers.update(updatedTrigger!, {
179 enabled_mode: 'REPLICA',
180 })
181 await executeQuery(updateSql2)
182
183 // Verify second update
184 const { sql: retrieveUpdated2Sql } = pgMeta.triggers.retrieve({
185 name: 'test_trigger_renamed',
186 table: 'users_audit',
187 schema: 'public',
188 })
189 const updatedTrigger2 = retrieveZod.parse((await executeQuery(retrieveUpdated2Sql))[0])
190 expect(updatedTrigger2).toMatchObject({
191 enabled_mode: 'REPLICA',
192 })
193
194 // Remove trigger
195 const { sql: removeSql } = pgMeta.triggers.remove(updatedTrigger2!)
196 await executeQuery(removeSql)
197
198 // Verify removal
199 const { sql: verifyRemoveSql } = pgMeta.triggers.retrieve({
200 name: 'test_trigger_renamed',
201 table: 'users_audit',
202 schema: 'public',
203 })
204 const result = retrieveZod.parse((await executeQuery(verifyRemoveSql))[0])
205 expect(result).toBeUndefined()
206})
207
208withTestDatabase('multi event', async ({ executeQuery }) => {
209 // Create trigger
210 const { sql: createSql } = pgMeta.triggers.create({
211 name: 'test_multi_event_trigger',
212 schema: 'public',
213 table: 'users_audit',
214 function_schema: 'public',
215 function_name: 'audit_action',
216 function_args: ['test1', 'test2'],
217 activation: 'AFTER',
218 events: ['insert', 'update', 'delete'],
219 orientation: 'ROW',
220 })
221 await executeQuery(createSql)
222
223 // Verify created trigger
224 const { sql: retrieveSql, zod: retrieveZod } = pgMeta.triggers.retrieve({
225 name: 'test_multi_event_trigger',
226 table: 'users_audit',
227 schema: 'public',
228 })
229 const trigger = retrieveZod.parse((await executeQuery(retrieveSql))[0])
230 expect(trigger).toMatchInlineSnapshot(
231 { id: expect.any(Number), table_id: expect.any(Number) },
232 `
233 {
234 "activation": "AFTER",
235 "condition": null,
236 "enabled_mode": "ORIGIN",
237 "events": [
238 "INSERT",
239 "DELETE",
240 "UPDATE",
241 ],
242 "function_args": [
243 "test1",
244 "test2",
245 ],
246 "function_name": "audit_action",
247 "function_schema": "public",
248 "id": Any<Number>,
249 "name": "test_multi_event_trigger",
250 "orientation": "ROW",
251 "schema": "public",
252 "table": "users_audit",
253 "table_id": Any<Number>,
254 }
255 `
256 )
257
258 // Remove trigger
259 const { sql: removeSql } = pgMeta.triggers.remove(trigger!)
260 await executeQuery(removeSql)
261
262 // Verify removal
263 const { sql: verifyRemoveSql } = pgMeta.triggers.retrieve({
264 name: 'test_multi_event_trigger',
265 table: 'users_audit',
266 schema: 'public',
267 })
268 const result = retrieveZod.parse((await executeQuery(verifyRemoveSql))[0])
269 expect(result).toBeUndefined()
270})
271
272withTestDatabase('triggers with the same name on different schemas', async ({ executeQuery }) => {
273 // Create test schemas and triggers
274 await executeQuery(`
275 create function tr_f() returns trigger language plpgsql as 'begin end';
276 create schema s1; create table s1.t(); create trigger tr before insert on s1.t execute function tr_f();
277 create schema s2; create table s2.t(); create trigger tr before insert on s2.t execute function tr_f();
278 `)
279
280 // List and verify triggers
281 const { sql: listSql, zod: listZod } = pgMeta.triggers.list()
282 const triggers = listZod.parse(await executeQuery(listSql))
283 expect(triggers.map(({ id, table_id, ...trigger }) => trigger)).toMatchInlineSnapshot(`
284 [
285 {
286 "activation": "BEFORE",
287 "condition": null,
288 "enabled_mode": "ORIGIN",
289 "events": [
290 "INSERT",
291 ],
292 "function_args": [],
293 "function_name": "tr_f",
294 "function_schema": "public",
295 "name": "tr",
296 "orientation": "STATEMENT",
297 "schema": "s1",
298 "table": "t",
299 },
300 {
301 "activation": "BEFORE",
302 "condition": null,
303 "enabled_mode": "ORIGIN",
304 "events": [
305 "INSERT",
306 ],
307 "function_args": [],
308 "function_name": "tr_f",
309 "function_schema": "public",
310 "name": "tr",
311 "orientation": "STATEMENT",
312 "schema": "s2",
313 "table": "t",
314 },
315 ]
316 `)
317})
318
319withTestDatabase('triggers on capitalized schema and table names', async ({ executeQuery }) => {
320 // Create test schema and trigger
321 await executeQuery(`
322 CREATE SCHEMA "MySchema";
323 CREATE TABLE "MySchema"."MyTable" (
324 id SERIAL PRIMARY KEY,
325 name TEXT NOT NULL,
326 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
327 updated_at TIMESTAMP
328 );
329 CREATE OR REPLACE FUNCTION "MySchema"."my_trigger_function"()
330 RETURNS TRIGGER AS $$
331 BEGIN
332 NEW.updated_at := CURRENT_TIMESTAMP;
333 RETURN NEW;
334 END;
335 $$ LANGUAGE plpgsql;
336
337 CREATE TRIGGER "my_trigger"
338 BEFORE INSERT ON "MySchema"."MyTable"
339 FOR EACH ROW
340 EXECUTE FUNCTION "MySchema"."my_trigger_function"();
341 `)
342
343 // List and verify triggers
344 const { sql: listSql, zod: listZod } = pgMeta.triggers.list()
345 const triggers = listZod.parse(await executeQuery(listSql))
346 expect(triggers.map(({ id, table_id, ...trigger }) => trigger)).toMatchInlineSnapshot(`
347 [
348 {
349 "activation": "BEFORE",
350 "condition": null,
351 "enabled_mode": "ORIGIN",
352 "events": [
353 "INSERT",
354 ],
355 "function_args": [],
356 "function_name": "my_trigger_function",
357 "function_schema": "MySchema",
358 "name": "my_trigger",
359 "orientation": "ROW",
360 "schema": "MySchema",
361 "table": "MyTable",
362 },
363 ]
364 `)
365})