Skip to main content

​
Overview

@effect/sql-mssql provides a Microsoft SQL Server driver for Effect’s SQL toolkit. It uses the tedious library and supports connection pooling, transactions, stored procedures, and SQL Server-specific features.

​
Installation

npm install @effect/sql-mssql

​
Basic Usage

​
Creating a Client

Use the MssqlClient.layer function to create a SQL Server client layer: 
import { MssqlClient } from "@effect/sql-mssql"
import { Effect, Layer, Redacted } from "effect"

const SqlLive = MssqlClient.layer({
  server: "localhost",
  port: 1433,
  database: "mydb",
  username: "sa",
  password: Redacted.make("YourStrong@Passw0rd")
})

​
Executing Queries

import { Effect } from "effect"
import { SqlClient } from "effect/unstable/sql"

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient

  // Simple query
  const users = yield* sql<{ id: number; name: string }>`
    SELECT id, name FROM users WHERE active = ${true}
  `

  // Insert with OUTPUT clause
  const inserted = yield* sql<{ id: number }>`
    INSERT INTO users ${sql.insert({ name: "Alice", email: "[email protected]" }).returning("*")}
  `

  // Update with helper
  yield* sql`
    UPDATE users SET ${sql.update({ name: "Bob" })} WHERE id = ${1}
  `
})

const runnable = program.pipe(Effect.provide(SqlLive))

​
Configuration Options

​
Connection Options

OptionTypeDescription
serverstringSQL Server hostname
portnumberDatabase port (default: 1433)
databasestringDatabase name
usernamestringUsername for authentication
passwordRedactedPassword for authentication
domainstringDomain for Windows authentication
authTypestringAuthentication type (e.g., “ntlm”, “azure-active-directory-password”)
instanceNamestringNamed instance of SQL Server
encryptbooleanEnable encryption (default: false)
trustServerbooleanTrust server certificate (default: true)
connectTimeoutDuration.InputConnection timeout (default: 5s)

​
Connection Pool Options

OptionTypeDescription
minConnectionsnumberMinimum number of connections (default: 1)
maxConnectionsnumberMaximum number of connections (default: 10)
connectionTTLDuration.InputTime-to-live for connections (default: 45 minutes)

​
Transform Options

OptionTypeDescription
transformResultNames(str: string) => stringTransform column names in results
transformQueryNames(str: string) => stringTransform identifiers in queries

​
Advanced Options

OptionTypeDescription
parameterTypesRecord<Statement.PrimitiveKind, DataType>Custom parameter type mapping
spanAttributesRecord<string, unknown>Custom telemetry attributes

​
SQL Server-Specific Features

​
OUTPUT Clause

SQL Server uses OUTPUT instead of RETURNING: 
const sql = yield* SqlClient.SqlClient

// Insert with OUTPUT
const inserted = yield* sql<{ id: number; name: string }>`
  INSERT INTO users ${sql.insert({ name: "Alice" }).returning("*")}
`

// Update with OUTPUT
const updated = yield* sql<{ id: number; name: string }>`
  UPDATE users SET ${sql.update({ name: "Bob" }).returning("id", "name")}
  WHERE id = ${1}
`

​
Custom Parameters

Use typed parameters for SQL Server-specific types: 
import { MssqlTypes } from "@effect/sql-mssql"

const sql = yield* MssqlClient.MssqlClient

// Use a specific SQL Server type
const result = yield* sql`
  SELECT * FROM users WHERE id = ${sql.param(MssqlTypes.UniqueIdentifier, "6F9619FF-8B86-D011-B42D-00C04FC964FF")}
`

​
Stored Procedures

Call stored procedures with input and output parameters: 
import { MssqlClient, Parameter, Procedure, MssqlTypes } from "@effect/sql-mssql"

const sql = yield* MssqlClient.MssqlClient

// Define procedure with parameters
const getUserById = Procedure.make("GetUserById", {
  input: {
    userId: Parameter.make(MssqlTypes.Int)
  },
  output: {
    userName: Parameter.make(MssqlTypes.NVarChar)
  }
})

// Call the procedure
const result = yield* sql.call(
  getUserById.withValues({ userId: 123 })
)

console.log(result.params.userName) // Output parameter
console.log(result.rows)             // Result set

​
Transactions

Automatic transaction management with savepoints: 
const sql = yield* SqlClient.SqlClient

const result = yield* sql.withTransaction(
  Effect.gen(function* () {
    yield* sql`INSERT INTO accounts (balance) VALUES (${100})`
    yield* sql`UPDATE accounts SET balance = balance - ${50} WHERE id = ${1}`
    return yield* sql<{ balance: number }>`SELECT balance FROM accounts WHERE id = ${1}`
  })
)

​
Parameter Type Mapping

Customize how JavaScript types map to SQL Server types: 
import { MssqlTypes } from "@effect/sql-mssql"

const SqlLive = MssqlClient.layer({
  server: "localhost",
  database: "mydb",
  parameterTypes: {
    string: MssqlTypes.NVarChar,
    number: MssqlTypes.Float,
    bigint: MssqlTypes.BigInt,
    boolean: MssqlTypes.Bit,
    Date: MssqlTypes.DateTime2,
    Uint8Array: MssqlTypes.VarBinary,
    Int8Array: MssqlTypes.VarBinary,
    null: MssqlTypes.Null
  }
})

​
Name Transformations

Automatically convert between naming conventions: 
import { String } from "effect"

const SqlLive = MssqlClient.layer({
  server: "localhost",
  database: "mydb",
  // Transform camelCase to PascalCase for queries
  transformQueryNames: (str) => str.charAt(0).toUpperCase() + str.slice(1),
  // Transform PascalCase to camelCase for results
  transformResultNames: (str) => str.charAt(0).toLowerCase() + str.slice(1)
})

​
Error Handling

All SQL errors are wrapped in SqlError: 
import { SqlError } from "effect/unstable/sql"

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient
  return yield* sql`SELECT * FROM users`
}).pipe(
  Effect.catchTag("SqlError", (error) =>
    Effect.log(`Database error: ${error.message}`)
  )
)

​
Windows Authentication

Use Windows authentication: 
const SqlLive = MssqlClient.layer({
  server: "localhost",
  database: "mydb",
  domain: "MYDOMAIN",
  authType: "ntlm",
  username: "myuser",
  password: Redacted.make("password")
})

​
Type Safety

The client is fully typed with Effect’s type system: 
interface User {
  id: number
  name: string
  email: string
}

const users = yield* sql<User>`SELECT * FROM users`
// users: ReadonlyArray<User>

Build docs developers (and LLMs) love

Get started for freeTalk to us