1import { assert, describe, it } from "@effect/vitest"
2import { Effect } from "effect"
3import * as Reactivity from "effect/reactivity/Reactivity"
4import type * as SqlError from "effect/sql/SqlError"
5import { vi } from "vitest"
6
7const state: {
8 connectCause: unknown
9 requestCauses: Array<unknown>
10} = {
11 connectCause: null,
12 requestCauses: []
13}
14
15class MockRequest {
16 callback: (cause: unknown, rowCount: number, rows: ReadonlyArray<any>) => void
17
18 constructor(
19 _sql: string,
20 callback: (cause: unknown, rowCount: number, rows: ReadonlyArray<any>) => void
21 ) {
22 this.callback = callback
23 }
24
25 addParameter() {
26 return
27 }
28
29 addOutputParameter() {
30 return
31 }
32
33 on() {
34 return
35 }
36}
37
38class MockConnection {
39 connect(callback: (cause: unknown) => void) {
40 callback(state.connectCause)
41 }
42
43 close() {
44 return
45 }
46
47 on() {
48 return
49 }
50
51 beginTransaction(callback: (cause: unknown) => void) {
52 callback(null)
53 }
54
55 commitTransaction(callback: (cause: unknown) => void) {
56 callback(null)
57 }
58
59 saveTransaction(callback: (cause: unknown) => void) {
60 callback(null)
61 }
62
63 rollbackTransaction(callback: (cause: unknown) => void) {
64 callback(null)
65 }
66
67 cancel() {
68 return
69 }
70
71 execSql(request: MockRequest) {
72 const cause = state.requestCauses.length > 0 ? state.requestCauses.shift() : null
73 request.callback(cause, 0, [])
74 }
75
76 callProcedure(request: MockRequest) {
77 this.execSql(request)
78 }
79}
80
81vi.mock("tedious", () => ({
82 Connection: MockConnection,
83 Request: MockRequest,
84 TYPES: {
85 VarChar: {},
86 Int: {},
87 BigInt: {},
88 Bit: {},
89 DateTime: {},
90 VarBinary: {}
91 }
92}))
93
94const queryFailureReason = (cause: unknown) =>
95 Effect.gen(function*() {
96 state.connectCause = null
97 state.requestCauses = [null, cause]
98 const { MssqlClient } = yield* Effect.promise(() => import("@effect/sql-mssql"))
99 const client = yield* MssqlClient.make({ server: "localhost" })
100 const error = yield* Effect.flip(client`SELECT 1`)
101 return error.reason
102 }).pipe(
103 Effect.scoped,
104 Effect.provide(Reactivity.layer)
105 )
106
107const queryFailureReasonTag = (number: number) => Effect.map(queryFailureReason({ number }), (reason) => reason._tag)
108
109const assertUniqueViolation = (reason: SqlError.SqlErrorReason, constraint: string) => {
110 assert.strictEqual(reason._tag, "UniqueViolation")
111 if (reason._tag === "UniqueViolation") {
112 assert.strictEqual(reason.constraint, constraint)
113 }
114}
115
116describe("MssqlClient SqlError classification", { concurrent: false }, () => {
117 it.effect("maps representative error numbers to reasons", () =>
118 Effect.gen(function*() {
119 const cases = [
120 [233, "ConnectionError"],
121 [18456, "AuthenticationError"],
122 [229, "AuthorizationError"],
123 [102, "SqlSyntaxError"],
124 [2601, "UniqueViolation"],
125 [2627, "UniqueViolation"],
126 [547, "ConstraintError"],
127 [1205, "DeadlockError"],
128 [3960, "SerializationError"],
129 [1222, "LockTimeoutError"]
130 ] as const
131
132 for (const [number, expectedTag] of cases) {
133 const tag = yield* queryFailureReasonTag(number)
134 assert.strictEqual(tag, expectedTag)
135 }
136 }))
137
138 it.effect("falls back to UnknownError for unmapped error numbers", () =>
139 Effect.gen(function*() {
140 const tag = yield* queryFailureReasonTag(99999)
141 assert.strictEqual(tag, "UnknownError")
142 }))
143
144 it.effect("classifies duplicate-key number 2601 as UniqueViolation and extracts the unique index", () =>
145 Effect.gen(function*() {
146 const reason = yield* queryFailureReason({
147 number: 2601,
148 message:
149 "Cannot insert duplicate key row in object 'dbo.Users' with unique index 'IX_Users_Email'. The duplicate key value is (user@example.com)."
150 })
151 assertUniqueViolation(reason, "IX_Users_Email")
152 }))
153
154 it.effect("classifies constraint number 2627 as UniqueViolation and extracts the constraint", () =>
155 Effect.gen(function*() {
156 const reason = yield* queryFailureReason({
157 number: 2627,
158 message:
159 "Violation of UNIQUE KEY constraint 'UQ_Users_Email'. Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (user@example.com)."
160 })
161 assertUniqueViolation(reason, "UQ_Users_Email")
162 }))
163
164 it.effect("prefers structured constraints and trims whitespace", () =>
165 Effect.gen(function*() {
166 const reason = yield* queryFailureReason({
167 number: 2601,
168 constraint: " IX_Structured_Email ",
169 message:
170 "Cannot insert duplicate key row in object 'dbo.Users' with unique index 'IX_Users_Email'. The duplicate key value is (user@example.com)."
171 })
172 assertUniqueViolation(reason, "IX_Structured_Email")
173 }))
174
175 it.effect("uses unknown for blank, missing, malformed, or non-string unique violation metadata", () =>
176 Effect.gen(function*() {
177 const missing = yield* queryFailureReason({ number: 2601 })
178 assertUniqueViolation(missing, "unknown")
179
180 const blank = yield* queryFailureReason({
181 number: 2627,
182 constraint: " ",
183 message: "Violation of UNIQUE KEY constraint ' '. Cannot insert duplicate key in object 'dbo.Users'."
184 })
185 assertUniqueViolation(blank, "unknown")
186
187 const malformed = yield* queryFailureReason({
188 number: 2601,
189 message: "Cannot insert duplicate key row in object 'dbo.Users'."
190 })
191 assertUniqueViolation(malformed, "unknown")
192
193 const nonString = yield* queryFailureReason({
194 number: 2627,
195 constraint: 2627,
196 message: { text: "Violation of UNIQUE KEY constraint 'UQ_Users_Email'." }
197 })
198 assertUniqueViolation(nonString, "unknown")
199 }))
200
201 it.effect("keeps non-unique constraint number 547 classified as ConstraintError", () =>
202 Effect.gen(function*() {
203 const tag = yield* queryFailureReasonTag(547)
204 assert.strictEqual(tag, "ConstraintError")
205 }))
206})