sql-parameters.test.ts78 lines · main

1	import { describe, expect, it } from 'vitest'
2
3	import { parseParameters, processParameterizedSql } from './sql-parameters'
4
5	describe('parseParameters', () => {
6	it('parses simple :param without @set', () => {
7	const result = parseParameters('SELECT * FROM table WHERE id = :userId')
8	expect(result).toEqual([
9	{
10	name: 'userId',
11	value: '',
12	defaultValue: undefined,
13	type: undefined,
14	possibleValues: undefined,
15	occurrences: 1,
16	},
17	])
18	})
19
20	it('parses @set with type and value', () => {
21	const result = parseParameters('@set userId:int = 123\nSELECT * FROM table WHERE id = :userId')
22
23	const userIdParam = result.find((p) => p.name === 'userId')
24	expect(userIdParam?.defaultValue).toBe('123')
25	expect(userIdParam?.type).toBe('int')
26	})
27
28	it('parses union type', () => {
29	const result = parseParameters(
30	'@set status:open|closed = open\nSELECT * FROM users WHERE status = :status'
31	)
32
33	const statusParam = result.find((p) => p.name === 'status')
34	expect(statusParam?.type).toBe('enum')
35	expect(statusParam?.possibleValues).toEqual(['open', 'closed'])
36	})
37
38	it('counts multiple occurrences', () => {
39	const result = parseParameters('SELECT * FROM table WHERE id = :userId OR owner_id = :userId')
40
41	const userIdParam = result.find((p) => p.name === 'userId')
42	expect(userIdParam?.occurrences).toBe(2)
43	})
44	})
45
46	describe('processParameterizedSql', () => {
47	it('replaces :param with value from parameters', () => {
48	const sql = 'SELECT * FROM users WHERE id = :userId'
49	const result = processParameterizedSql(sql, { userId: '42' })
50	expect(result).toBe('SELECT * FROM users WHERE id = 42')
51	})
52
53	it('uses default from @set if param not provided', () => {
54	const sql = '@set userId:int = 123\nSELECT * FROM users WHERE id = :userId'
55	const result = processParameterizedSql(sql, {})
56	expect(result).toBe('SELECT * FROM users WHERE id = 123')
57	})
58
59	it('overrides @set default if param is provided', () => {
60	const sql = '@set userId:int = 123\nSELECT * FROM users WHERE id = :userId'
61	const result = processParameterizedSql(sql, { userId: '999' })
62	expect(result).toBe('SELECT * FROM users WHERE id = 999')
63	})
64
65	it('throws if no param value is provided and no default exists', () => {
66	const sql = 'SELECT * FROM users WHERE id = :userId'
67	expect(() => processParameterizedSql(sql, {})).toThrowError(
68	'Missing value for parameter: userId'
69	)
70	})
71
72	it('removes @set lines from final SQL', () => {
73	const sql = '@set status:open|closed = open\nSELECT * FROM items WHERE status = :status'
74	const result = processParameterizedSql(sql, {})
75	expect(result).toBe('SELECT * FROM items WHERE status = open')
76	expect(result).not.toContain('@set')
77	})
78	})