1/**
2 * Microsoft SQL Server client implementation for Effect SQL, backed by the
3 * `tedious` driver.
4 *
5 * This module provides the `MssqlClient` service, constructors, layers, and SQL
6 * Server statement compiler. `make` creates a pooled Tedious client, checks the
7 * connection with `SELECT 1`, maps SQL Server failures to `SqlError`, and
8 * supports transactions with savepoints. The SQL Server-specific service adds
9 * typed Tedious parameters with `param`, stored procedure calls with `call`,
10 * direct or config-backed layers, and default parameter type mappings.
11 * Streaming queries are not implemented by this driver.
12 *
13 * @since 4.0.0
14 */
15import * as Config from "effect/Config"
16import * as Context from "effect/Context"
17import * as Deferred from "effect/Deferred"
18import * as Duration from "effect/Duration"
19import * as Effect from "effect/Effect"
20import { identity } from "effect/Function"
21import * as Layer from "effect/Layer"
22import * as Pool from "effect/Pool"
23import * as Reactivity from "effect/reactivity/Reactivity"
24import * as Rec from "effect/Record"
25import * as Redacted from "effect/Redacted"
26import * as Scope from "effect/Scope"
27import * as Client from "effect/sql/SqlClient"
28import type { Connection } from "effect/sql/SqlConnection"
29import {
30 AuthenticationError,
31 AuthorizationError,
32 ConnectionError,
33 ConstraintError,
34 DeadlockError,
35 LockTimeoutError,
36 SerializationError,
37 SqlError,
38 SqlSyntaxError,
39 UniqueViolation,
40 UnknownError
41} from "effect/sql/SqlError"
42import * as Statement from "effect/sql/Statement"
43import * as Stream from "effect/Stream"
44import { Buffer } from "node:buffer"
45import * as Tedious from "tedious"
46import type { ConnectionOptions } from "tedious/lib/connection.ts"
47import type { DataType } from "tedious/lib/data-type.ts"
48import type { ParameterOptions } from "tedious/lib/request.ts"
49import type { Parameter } from "./Parameter.ts"
50import type * as Procedure from "./Procedure.ts"
51
52const ATTR_DB_SYSTEM_NAME = "db.system.name"
53const ATTR_DB_NAMESPACE = "db.namespace"
54const ATTR_SERVER_ADDRESS = "server.address"
55const ATTR_SERVER_PORT = "server.port"
56
57const mssqlNumberFromCause = (cause: unknown): number | undefined => {
58 if (typeof cause !== "object" || cause === null || !("number" in cause)) {
59 return undefined
60 }
61 const number = cause.number
62 return typeof number === "number" ? number : undefined
63}
64
65const mssqlConnectionErrorCodes = new Set([233, 10054])
66const mssqlAuthenticationErrorCodes = new Set([4060, 18452, 18456])
67const mssqlAuthorizationErrorCodes = new Set([229, 230, 262, 297, 300])
68const mssqlSyntaxErrorCodes = new Set([102, 207, 208, 2714])
69const mssqlConstraintErrorCodes = new Set([515, 547])
70
71const UNKNOWN_CONSTRAINT = "unknown"
72
73const normalizeConstraintIdentifier = (identifier: unknown): string => {
74 if (typeof identifier !== "string") {
75 return UNKNOWN_CONSTRAINT
76 }
77 const trimmed = identifier.trim()
78 return trimmed.length === 0 ? UNKNOWN_CONSTRAINT : trimmed
79}
80
81const mssqlCauseProperty = (cause: unknown, property: "constraint" | "message"): unknown => {
82 if (typeof cause !== "object" || cause === null || !(property in cause)) {
83 return undefined
84 }
85 return (cause as Record<string, unknown>)[property]
86}
87
88const mssqlUniqueViolationConstraintFromMessage = (number: 2601 | 2627, message: unknown): string => {
89 if (typeof message !== "string") {
90 return UNKNOWN_CONSTRAINT
91 }
92 const match = number === 2627 ?
93 /\bconstraint\s+'([^']*)'/i.exec(message) :
94 /\bunique index\s+'([^']*)'/i.exec(message)
95 return match === null ? UNKNOWN_CONSTRAINT : normalizeConstraintIdentifier(match[1])
96}
97
98const mssqlUniqueViolationConstraintFromCause = (number: 2601 | 2627, cause: unknown): string => {
99 const constraint = normalizeConstraintIdentifier(mssqlCauseProperty(cause, "constraint"))
100 if (constraint !== UNKNOWN_CONSTRAINT) {
101 return constraint
102 }
103 return mssqlUniqueViolationConstraintFromMessage(number, mssqlCauseProperty(cause, "message"))
104}
105
106const classifyError = (
107 cause: unknown,
108 message: string,
109 operation: string,
110 fallback: "connection" | "unknown" = "unknown"
111) => {
112 const props = { cause, message, operation }
113 const number = mssqlNumberFromCause(cause)
114 if (number !== undefined) {
115 if (mssqlConnectionErrorCodes.has(number)) {
116 return new ConnectionError(props)
117 }
118 if (mssqlAuthenticationErrorCodes.has(number)) {
119 return new AuthenticationError(props)
120 }
121 if (mssqlAuthorizationErrorCodes.has(number)) {
122 return new AuthorizationError(props)
123 }
124 if (mssqlSyntaxErrorCodes.has(number)) {
125 return new SqlSyntaxError(props)
126 }
127 if (number === 2601 || number === 2627) {
128 return new UniqueViolation({ ...props, constraint: mssqlUniqueViolationConstraintFromCause(number, cause) })
129 }
130 if (mssqlConstraintErrorCodes.has(number)) {
131 return new ConstraintError(props)
132 }
133 if (number === 1205) {
134 return new DeadlockError(props)
135 }
136 if (number === 3960) {
137 return new SerializationError(props)
138 }
139 if (number === 1222) {
140 return new LockTimeoutError(props)
141 }
142 }
143 return fallback === "connection" ? new ConnectionError(props) : new UnknownError(props)
144}
145
146/**
147 * Runtime type identifier used to mark `MssqlClient` values.
148 *
149 * @category type IDs
150 * @since 4.0.0
151 */
152export const TypeId: unique symbol = Symbol.for("@effect/sql-mssql/MssqlClient")
153
154/**
155 * Type-level identifier used to mark `MssqlClient` values.
156 *
157 * @category type IDs
158 * @since 4.0.0
159 */
160export type TypeId = typeof TypeId
161
162/**
163 * Microsoft SQL Server client service, extending `SqlClient` with typed parameter fragments and stored procedure calls.
164 *
165 * @category services
166 * @since 4.0.0
167 */
168export interface MssqlClient extends Client.SqlClient {
169 readonly [TypeId]: TypeId
170
171 readonly config: MssqlClientConfig
172
173 /**
174 * Creates a statement parameter with an explicit `tedious` data type.
175 *
176 * @stability unstable
177 */
178 readonly param: (
179 type: DataType,
180 value: unknown,
181 options?: ParameterOptions
182 ) => Statement.Fragment
183
184 readonly call: <
185 I extends Record<string, Parameter<any>>,
186 O extends Record<string, Parameter<any>>,
187 A extends object
188 >(
189 procedure: Procedure.ProcedureWithValues<I, O, A>
190 ) => Effect.Effect<Procedure.Procedure.Result<O, A>, SqlError>
191}
192
193/**
194 * Service tag for the Microsoft SQL Server client service.
195 *
196 * **When to use**
197 *
198 * Use to access or provide a Microsoft SQL Server client through the Effect
199 * context.
200 *
201 * @category services
202 * @since 4.0.0
203 */
204export const MssqlClient = Context.Service<MssqlClient>("@effect/sql-mssql/MssqlClient")
205
206/**
207 * Configuration for a Microsoft SQL Server client, including connection, authentication, pool, parameter type, span attribute, and query/result name transform options.
208 *
209 * @category models
210 * @since 4.0.0
211 */
212export interface MssqlClientConfig {
213 readonly domain?: string | undefined
214 readonly server: string
215 readonly instanceName?: string | undefined
216 /**
217 * Whether to encrypt traffic between the client and server. Defaults to `true`. Setting this to `false` disables transport encryption and transmits credentials in cleartext.
218 */
219 readonly encrypt?: boolean | undefined
220 /**
221 * Whether to trust the server certificate without validating it. Defaults to `false`. Setting this to `true` disables TLS certificate validation.
222 */
223 readonly trustServer?: boolean | undefined
224 readonly port?: number | undefined
225 readonly authType?: string | undefined
226 readonly database?: string | undefined
227 readonly username?: string | undefined
228 readonly password?: Redacted.Redacted | undefined
229 readonly connectTimeout?: Duration.Input | undefined
230 readonly cancelTimeout?: Duration.Input | undefined
231 readonly connectionRetryInterval?: Duration.Input | undefined
232 readonly multiSubnetFailover?: boolean | undefined
233 readonly maxRetriesOnTransientErrors?: number | undefined
234
235 readonly minConnections?: number | undefined
236 readonly maxConnections?: number | undefined
237 readonly connectionTTL?: Duration.Input | undefined
238
239 /**
240 * Overrides the `tedious` data type used for each primitive parameter kind.
241 *
242 * @stability unstable
243 */
244 readonly parameterTypes?: Record<Statement.PrimitiveKind, DataType> | undefined
245
246 readonly spanAttributes?: Record<string, unknown> | undefined
247
248 readonly transformResultNames?: ((str: string) => string) | undefined
249 readonly transformQueryNames?: ((str: string) => string) | undefined
250}
251
252interface MssqlConnection extends Connection {
253 readonly call: (
254 procedure: Procedure.ProcedureWithValues<any, any, any>,
255 transformRows: ((rows: ReadonlyArray<any>) => ReadonlyArray<any>) | undefined
256 ) => Effect.Effect<any, SqlError>
257
258 readonly begin: Effect.Effect<void, SqlError>
259 readonly commit: Effect.Effect<void, SqlError>
260 readonly savepoint: (name: string) => Effect.Effect<void, SqlError>
261 readonly rollback: (name?: string) => Effect.Effect<void, SqlError>
262}
263
264const TransactionConnection = Client.TransactionConnection as unknown as (clientId: number) => Context.Service<
265 readonly [conn: MssqlConnection, counter: number],
266 readonly [conn: MssqlConnection, counter: number]
267>
268
269let clientIdCounter = 0
270
271/**
272 * Creates a scoped Microsoft SQL Server client backed by a connection pool, with transaction and stored procedure support. Streaming queries are not implemented.
273 *
274 * @category constructors
275 * @since 4.0.0
276 */
277export const make = (
278 options: MssqlClientConfig
279): Effect.Effect<MssqlClient, SqlError, Scope.Scope | Reactivity.Reactivity> =>
280 Effect.gen(function*() {
281 const parameterTypes = options.parameterTypes ?? defaultParameterTypes
282 const compiler = makeCompiler(options.transformQueryNames)
283
284 const transformRows = options.transformResultNames ?
285 Statement.defaultTransforms(
286 options.transformResultNames
287 ).array :
288 undefined
289 const spanAttributes: ReadonlyArray<[string, unknown]> = [
290 ...(options.spanAttributes ? Object.entries(options.spanAttributes) : []),
291 [ATTR_DB_SYSTEM_NAME, "microsoft.sql_server"],
292 [ATTR_DB_NAMESPACE, options.database ?? "master"],
293 [ATTR_SERVER_ADDRESS, options.server],
294 [ATTR_SERVER_PORT, options.port ?? 1433]
295 ]
296
297 // oxlint-disable-next-line prefer-const
298 let pool: Pool.Pool<MssqlConnection, SqlError>
299 const connectionEnded = new WeakMap<MssqlConnection, Deferred.Deferred<void>>()
300 // Invalidation can run before acquisition publishes the pool item. Check
301 // the latch on every lease so an early event cannot leave it reusable.
302 const acquireConnection: Effect.Effect<MssqlConnection, SqlError, Scope.Scope> = Effect.suspend(() =>
303 Effect.flatMap(Pool.get(pool), (connection) =>
304 Deferred.isDoneUnsafe(connectionEnded.get(connection)!)
305 ? Effect.andThen(Pool.invalidate(pool, connection), acquireConnection)
306 : Effect.succeed(connection))
307 )
308
309 const makeConnection = Effect.gen(function*() {
310 const conn = new Tedious.Connection({
311 options: {
312 port: options.port,
313 database: options.database,
314 trustServerCertificate: options.trustServer ?? false,
315 multiSubnetFailover: options.multiSubnetFailover,
316 connectTimeout: options.connectTimeout
317 ? Duration.toMillis(Duration.fromInputUnsafe(options.connectTimeout))
318 : undefined,
319 rowCollectionOnRequestCompletion: true,
320 useColumnNames: false,
321 instanceName: options.instanceName,
322 encrypt: options.encrypt ?? true,
323 cancelTimeout: options.cancelTimeout
324 ? Duration.toMillis(Duration.fromInputUnsafe(options.cancelTimeout))
325 : undefined,
326 connectionRetryInterval: options.connectionRetryInterval
327 ? Duration.toMillis(Duration.fromInputUnsafe(options.connectionRetryInterval))
328 : undefined,
329 maxRetriesOnTransientErrors: options.maxRetriesOnTransientErrors
330 } as ConnectionOptions,
331 server: options.server,
332 authentication: {
333 type: (options.authType as any) ?? "default",
334 options: {
335 domain: options.domain,
336 userName: options.username,
337 password: options.password
338 ? Redacted.value(options.password)
339 : undefined
340 }
341 }
342 })
343
344 const ended = Deferred.makeUnsafe<void>()
345 let closing = false
346 let endEmitted = false
347 const onError = () => {
348 Deferred.doneUnsafe(ended, Effect.void)
349 }
350 const removeListeners = () => {
351 conn.removeListener("error", onError)
352 conn.removeListener("end", onEnd)
353 }
354 const onEnd = () => {
355 endEmitted = true
356 Deferred.doneUnsafe(ended, Effect.void)
357 if (closing) removeListeners()
358 }
359
360 // Install before connect and keep the error handler through asynchronous
361 // close. The latch also records events before the pool item exists.
362 conn.on("error", onError)
363 conn.on("end", onEnd)
364 yield* Effect.addFinalizer(() =>
365 Effect.sync(() => {
366 closing = true
367 conn.close()
368 if (endEmitted) removeListeners()
369 })
370 )
371
372 yield* Effect.callback<void, SqlError>((resume) => {
373 conn.connect((cause) => {
374 if (cause) {
375 resume(
376 Effect.fail(new SqlError({ reason: classifyError(cause, "Failed to connect", "connect", "connection") }))
377 )
378 } else {
379 resume(Effect.void)
380 }
381 })
382 })
383
384 const run = (
385 sql: string,
386 values?: ReadonlyArray<any>,
387 rowsAsArray = false
388 ) =>
389 Effect.callback<any, SqlError>((resume) => {
390 const req = new Tedious.Request(sql, (cause, _rowCount, result) => {
391 if (cause) {
392 resume(
393 Effect.fail(new SqlError({ reason: classifyError(cause, "Failed to execute statement", "execute") }))
394 )
395 return
396 }
397
398 if (rowsAsArray) {
399 result = result.map((row: any) => row.map((_: any) => _.value))
400 } else {
401 result = rowsToObjects(result)
402 }
403
404 resume(Effect.succeed(result))
405 })
406
407 if (values) {
408 for (let i = 0, len = values.length; i < len; i++) {
409 const value = values[i]
410 const name = numberToParamName(i)
411
412 if (isMssqlParam(value)) {
413 req.addParameter(name, value.paramA, value.paramB, value.paramC)
414 } else {
415 const kind = Statement.primitiveKind(value)
416 const type = parameterTypes[kind]
417 req.addParameter(name, type, value)
418 }
419 }
420 }
421
422 conn.cancel()
423 conn.execSql(req)
424 return Effect.sync(() => conn.cancel())
425 })
426
427 const runProcedure = (
428 procedure: Procedure.ProcedureWithValues<any, any, any>,
429 transformRows: ((rows: ReadonlyArray<any>) => ReadonlyArray<any>) | undefined
430 ) =>
431 Effect.callback<any, SqlError>((resume) => {
432 const result: Record<string, any> = {}
433
434 const req = new Tedious.Request(
435 escape(procedure.name),
436 (cause, _, rows) => {
437 if (cause) {
438 resume(
439 Effect.fail(new SqlError({ reason: classifyError(cause, "Failed to execute statement", "execute") }))
440 )
441 } else {
442 rows = rowsToObjects(rows)
443 if (transformRows) {
444 rows = transformRows(rows) as any
445 }
446 resume(
447 Effect.succeed({
448 output: result,
449 rows
450 })
451 )
452 }
453 }
454 )
455
456 for (const name in procedure.params) {
457 const param = procedure.params[name]
458 const value = procedure.values[name]
459 req.addParameter(name, param.type, value, param.options)
460 }
461
462 for (const name in procedure.outputParams) {
463 const param = procedure.outputParams[name]
464 req.addOutputParameter(name, param.type, undefined, param.options)
465 }
466
467 req.on("returnValue", (name, value) => {
468 Rec.assignProperty(result, name, value)
469 })
470
471 conn.cancel()
472 conn.callProcedure(req)
473 return Effect.sync(() => conn.cancel())
474 })
475
476 const connection = identity<MssqlConnection>({
477 execute(sql, params, transformRows) {
478 return transformRows
479 ? Effect.map(run(sql, params), transformRows)
480 : run(sql, params)
481 },
482 executeRaw(sql, params) {
483 return run(sql, params)
484 },
485 executeValues(sql, params) {
486 return run(sql, params, true)
487 },
488 executeValuesUnprepared(sql, params) {
489 return run(sql, params, true)
490 },
491 executeUnprepared(sql, params, transformRows) {
492 return this.execute(sql, params, transformRows)
493 },
494 executeStream() {
495 return Stream.die("executeStream not implemented")
496 },
497 call(procedure, transformRows) {
498 return runProcedure(procedure, transformRows)
499 },
500 begin: Effect.callback<void, SqlError>((resume) => {
501 conn.beginTransaction((cause) => {
502 if (cause) {
503 resume(
504 Effect.fail(
505 new SqlError({
506 reason: classifyError(cause, "Failed to begin transaction", "beginTransaction")
507 })
508 )
509 )
510 } else {
511 resume(Effect.void)
512 }
513 })
514 }),
515 commit: Effect.callback<void, SqlError>((resume) => {
516 conn.commitTransaction((cause) => {
517 if (cause) {
518 resume(
519 Effect.fail(
520 new SqlError({
521 reason: classifyError(cause, "Failed to commit transaction", "commitTransaction")
522 })
523 )
524 )
525 } else {
526 resume(Effect.void)
527 }
528 })
529 }),
530 savepoint: (name: string) =>
531 Effect.callback<void, SqlError>((resume) => {
532 conn.saveTransaction((cause) => {
533 if (cause) {
534 resume(
535 Effect.fail(
536 new SqlError({ reason: classifyError(cause, "Failed to create savepoint", "createSavepoint") })
537 )
538 )
539 } else {
540 resume(Effect.void)
541 }
542 }, name)
543 }),
544 rollback: (name?: string) =>
545 Effect.callback<void, SqlError>((resume) => {
546 conn.rollbackTransaction((cause) => {
547 if (cause) {
548 resume(
549 Effect.fail(
550 new SqlError({
551 reason: classifyError(cause, "Failed to rollback transaction", "rollbackTransaction")
552 })
553 )
554 )
555 } else {
556 resume(Effect.void)
557 }
558 }, name)
559 })
560 })
561
562 connectionEnded.set(connection, ended)
563 yield* Deferred.await(ended).pipe(
564 Effect.andThen(Effect.suspend(() => pool ? Pool.invalidate(pool, connection) : Effect.void)),
565 Effect.interruptible,
566 Effect.forkScoped
567 )
568
569 return connection
570 })
571
572 pool = yield* Pool.makeWithTTL({
573 acquire: makeConnection,
574 min: options.minConnections ?? 1,
575 max: options.maxConnections ?? 10,
576 timeToLive: options.connectionTTL ?? Duration.minutes(45),
577 timeToLiveStrategy: "creation"
578 })
579
580 yield* acquireConnection.pipe(
581 Effect.tap((connection) => connection.executeUnprepared("SELECT 1", [], undefined)),
582 Effect.mapError((cause) =>
583 new SqlError({ reason: classifyError(cause, "MssqlClient: Failed to connect", "connect", "connection") })
584 ),
585 Effect.scoped,
586 Effect.timeoutOrElse({
587 duration: options.connectTimeout ?? Duration.seconds(5),
588 orElse: () =>
589 Effect.fail(
590 new SqlError({
591 reason: new ConnectionError({
592 message: "MssqlClient: Connection timeout",
593 cause: new Error("connection timeout"),
594 operation: "connect"
595 })
596 })
597 )
598 })
599 )
600
601 const transactionService = TransactionConnection(clientIdCounter++)
602
603 const withTransaction = Client.makeWithTransaction({
604 transactionService,
605 spanAttributes,
606 acquireConnection: Effect.gen(function*() {
607 const scope = Scope.makeUnsafe()
608 const conn = yield* Scope.provide(acquireConnection, scope)
609 return [scope, conn] as const
610 }),
611 begin: (conn) => conn.begin,
612 savepoint: (conn, id) => conn.savepoint(`effect_sql_${id}`),
613 commit: (conn) => conn.commit,
614 rollback: (conn) => conn.rollback(),
615 rollbackSavepoint: (conn, id) => conn.rollback(`effect_sql_${id}`)
616 })
617
618 return identity<MssqlClient>(Object.assign(
619 yield* Client.make({
620 acquirer: acquireConnection,
621 compiler,
622 transactionService: transactionService as any,
623 spanAttributes,
624 transformRows
625 }),
626 {
627 [TypeId]: TypeId as TypeId,
628 config: options,
629 withTransaction,
630 param: (
631 type: DataType,
632 value: unknown,
633 options: ParameterOptions = {}
634 ) => Statement.fragment([mssqlParam(type, value, options)]),
635 call: <
636 I extends Record<string, Parameter<any>>,
637 O extends Record<string, Parameter<any>>,
638 A
639 >(
640 procedure: Procedure.ProcedureWithValues<I, O, A>
641 ) => Effect.scoped(Effect.flatMap(acquireConnection, (_) => _.call(procedure, transformRows))),
642 withoutTransforms() {
643 const statement = Statement.make(acquireConnection, compiler.withoutTransform, spanAttributes, undefined)
644 const client = Object.assign(
645 statement,
646 this,
647 statement,
648 {
649 call: <
650 I extends Record<string, Parameter<any>>,
651 O extends Record<string, Parameter<any>>,
652 A
653 >(
654 procedure: Procedure.ProcedureWithValues<I, O, A>
655 ) => Effect.scoped(Effect.flatMap(acquireConnection, (_) => _.call(procedure, undefined)))
656 }
657 )
658 ;(client as any).safe = client
659 ;(client as any).withoutTransforms = () => client
660 return client
661 }
662 }
663 ))
664 })
665
666/**
667 * Creates a layer from a `Config`-wrapped SQL Server client configuration, providing both `MssqlClient` and `SqlClient`.
668 *
669 * @category layers
670 * @since 4.0.0
671 */
672export const layerConfig: (
673 config: Config.Wrap<MssqlClientConfig>
674) => Layer.Layer<Client.SqlClient | MssqlClient, Config.ConfigError | SqlError> = (
675 config: Config.Wrap<MssqlClientConfig>
676): Layer.Layer<Client.SqlClient | MssqlClient, Config.ConfigError | SqlError> =>
677 Layer.effectContext(
678 Config.unwrap(config).pipe(
679 Effect.flatMap(make),
680 Effect.map((client) =>
681 Context.make(MssqlClient, client).pipe(
682 Context.add(Client.SqlClient, client)
683 )
684 )
685 )
686 ).pipe(Layer.provide(Reactivity.layer))
687
688/**
689 * Creates a layer from a concrete SQL Server client configuration, providing both `MssqlClient` and `SqlClient`.
690 *
691 * @category layers
692 * @since 4.0.0
693 */
694export const layer = (
695 config: MssqlClientConfig
696): Layer.Layer<Client.SqlClient | MssqlClient, never | SqlError> =>
697 Layer.effectContext(
698 Effect.map(make(config), (client) =>
699 Context.make(MssqlClient, client).pipe(
700 Context.add(Client.SqlClient, client)
701 ))
702 ).pipe(Layer.provide(Reactivity.layer))
703
704/**
705 * Creates the SQL Server statement compiler, using `@1`-style placeholders, bracket-escaped identifiers, and SQL Server `OUTPUT INSERTED` returning clauses.
706 *
707 * @category constructors
708 * @since 4.0.0
709 */
710export const makeCompiler = (transform?: (_: string) => string) =>
711 Statement.makeCompiler<MssqlCustom>({
712 dialect: "mssql",
713 placeholder(_) {
714 return `@${numberToParamName(_ - 1)}`
715 },
716 onIdentifier: transform ?
717 function(value, withoutTransform) {
718 return withoutTransform ? escape(value) : escape(transform(value))
719 } :
720 escape,
721 onRecordUpdate(placeholders, valueAlias, valueColumns, values, returning) {
722 const returningSql = returning ? returning[0] === "*" ? "OUTPUT INSERTED.* " : `OUTPUT ${returning[0]} ` : ""
723 return [
724 `${returningSql}FROM (values ${placeholders}) AS ${valueAlias}${valueColumns}`,
725 returning ?
726 returning[1].concat(values.flat()) :
727 values.flat()
728 ]
729 },
730 onCustom(type, placeholder) {
731 switch (type.kind) {
732 case "MssqlParam": {
733 return [placeholder(undefined), [type] as any]
734 }
735 }
736 },
737 onInsert(columns, placeholders, values, returning) {
738 const returningSql = returning ? returning[0] === "*" ? " OUTPUT INSERTED.*" : ` OUTPUT ${returning[0]}` : ""
739 return [
740 `(${columns.join(",")})${returningSql} VALUES ${placeholders}`,
741 returning ?
742 returning[1].concat(values.flat()) :
743 values.flat()
744 ]
745 }
746 })
747
748// compiler helpers
749
750const escape = (str: string) => "[" + str.replace(/\]/g, "]]").replace(/\./g, "].[") + "]"
751
752function numberToParamName(n: number) {
753 return `${Math.ceil(n + 1)}`
754}
755
756const byteArrayParameterType: DataType = {
757 ...Tedious.TYPES.VarBinary,
758 validate(value, collation, options) {
759 return Tedious.TYPES.VarBinary.validate(
760 Buffer.isBuffer(value) ? value : Buffer.from(value.buffer, value.byteOffset, value.byteLength),
761 collation,
762 options
763 )
764 }
765}
766
767/**
768 * Default mapping from Effect SQL primitive value kinds to Tedious SQL Server parameter data types.
769 *
770 * @stability unstable
771 * @category constants
772 * @since 4.0.0
773 */
774export const defaultParameterTypes: Record<Statement.PrimitiveKind, DataType> = {
775 string: Tedious.TYPES.NVarChar,
776 number: Tedious.TYPES.Float,
777 bigint: Tedious.TYPES.BigInt,
778 boolean: Tedious.TYPES.Bit,
779 Date: Tedious.TYPES.DateTime,
780 Uint8Array: byteArrayParameterType,
781 Int8Array: byteArrayParameterType,
782 null: Tedious.TYPES.Bit
783}
784
785// custom types
786
787type MssqlCustom = MssqlParam
788
789interface MssqlParam extends
790 Statement.Custom<
791 "MssqlParam",
792 DataType,
793 unknown,
794 ParameterOptions
795 >
796{}
797
798const mssqlParam = Statement.custom<MssqlParam>("MssqlParam")
799const isMssqlParam = Statement.isCustom<MssqlParam>("MssqlParam")
800
801function rowsToObjects(rows: ReadonlyArray<any>) {
802 const newRows = new Array(rows.length)
803
804 for (let i = 0, len = rows.length; i < len; i++) {
805 const row = rows[i]
806 const newRow: any = {}
807 for (let j = 0, columnLen = row.length; j < columnLen; j++) {
808 const column = row[j]
809 Rec.assignProperty(newRow, column.metadata.colName, column.value)
810 }
811 newRows[i] = newRow
812 }
813
814 return newRows
815}