DBF MSSQL (dbf-mssql)

Server-side MSSQL helpers for Node.js:

• Load connection settings from .env / process.env
• Create and reuse a connection pool
• Execute stored procedures (and raw queries if needed)

This package is intentionally self-contained: it ships with mssql and dotenv so consumers do not need to install extra dependencies.

Installation

npm install dbf-mssql

dbf-mssql is Node.js-only (do not bundle it into the browser).

Environment variables

By default, dbf-mssql reads variables with the DBF_MSSQL_ prefix.

Required:

• DBF_MSSQL_SERVER
• DBF_MSSQL_DATABASE
• DBF_MSSQL_USER
• DBF_MSSQL_PASSWORD

Optional:

• DBF_MSSQL_PORT
• DBF_MSSQL_ENCRYPT (default: true)
• DBF_MSSQL_TRUST_SERVER_CERTIFICATE (default: false)
• DBF_MSSQL_REQUEST_TIMEOUT_MS
• DBF_MSSQL_CONNECT_TIMEOUT_MS
• DBF_MSSQL_POOL_MAX
• DBF_MSSQL_POOL_MIN
• DBF_MSSQL_POOL_IDLE_TIMEOUT_MS

Example .env:

DBF_MSSQL_SERVER=localhost
DBF_MSSQL_DATABASE=MyDb
DBF_MSSQL_USER=sa
DBF_MSSQL_PASSWORD=YourStrong(!)Password

# optional
DBF_MSSQL_PORT=1433
DBF_MSSQL_ENCRYPT=true
DBF_MSSQL_TRUST_SERVER_CERTIFICATE=true
DBF_MSSQL_REQUEST_TIMEOUT_MS=30000
DBF_MSSQL_CONNECT_TIMEOUT_MS=15000
DBF_MSSQL_POOL_MAX=10
DBF_MSSQL_POOL_MIN=0
DBF_MSSQL_POOL_IDLE_TIMEOUT_MS=30000

Custom prefix:

createMssqlClientFromEnv({ prefix: "MYAPP_MSSQL_" })

Quick start: execute a stored procedure (one-shot)

import { execProcFromEnv, param, sql } from "dbf-mssql";

const result = await execProcFromEnv("dbo.MyProc", {
  UserId: 123,
  Name: param("Ada", sql.NVarChar(50)),
});

console.log(result.recordset);
console.log(result.output);
console.log(result.returnValue);

execProcFromEnv(...) creates a client, executes the proc, then closes the pool.

Reuse the pool with a client

import { createMssqlClientFromEnv } from "dbf-mssql";

const client = createMssqlClientFromEnv();

const r1 = await client.execProc("dbo.MyProc", { UserId: 123 });
const r2 = await client.query("select 1 as ok");

await client.close();

Typed input params

If you need to force a SQL type, use param(value, type) and sql.* types:

import { execProcFromEnv, param, sql } from "dbf-mssql";

await execProcFromEnv("dbo.SaveUser", {
  Id: param(123, sql.Int),
  Name: param("Ada", sql.NVarChar(50)),
  Bio: param("...", sql.NVarChar(sql.MAX)),
});

API

• mssqlConfigFromEnv(options?): load + validate config from env (optionally calls dotenv.config())
• createMssqlClientFromEnv(envOptions?, clientOptions?): create a DbfMssqlClient from env
• execProcFromEnv(procName, inputs?, outputs?, envOptions?): execute proc and close pool
• DbfMssqlClient: connect(), close(), query(), execProc()
• param(value, type?): build a typed input param
• sql: normalized mssql module exports (works in ESM and CJS)

Additional helpers on DbfMssqlClient:

• ping() – simple select 1 health check
• withTransaction(fn) – runs a function inside a SQL transaction (auto commit/rollback)

Security / architecture note

Do not connect to MSSQL from the browser. Use:

• Frontend → calls your backend API
• Backend (Node.js) → uses dbf-mssql to run procs/queries

Transactions (example)

import { createMssqlClientFromEnv } from "dbf-mssql";

const client = createMssqlClientFromEnv();

await client.withTransaction(async (tx) => {
  const request = tx.request();
  await request.query("update Users set active = 1 where id = 42");
  await request.query("insert into Logs(message) values('user activated')");
});

Optional logging hook

import { createMssqlClientFromEnv } from "dbf-mssql";

const client = createMssqlClientFromEnv({}, {
  log: (event) => {
    if (event.type.endsWith(":error")) {
      console.error(event);
    }
  },
});