connect-from-typescript-and-node

Connect from TypeScript and Node.js

The package is mssql, the pool belongs to the module and not the request handler, and the types come from a second package.

Verified on 2026-09-03 against mssql 12.7.0, tedious 20.0.0 and @types/mssql 12.3.0 on Node 24.15.0, resolved in a running process.

The stack

Package	What it is
tedious	The TDS protocol implementation. Microsoft names it the Node.js driver for SQL Server and contributes to it, but states it is community-supported with no Microsoft support
mssql	The wrapper almost every application uses: pooling, parameterised requests and transactions, over tedious
@types/mssql	The TypeScript definitions. mssql publishes no types field of its own

npm install mssql
npm install --save-dev @types/mssql

mssql 12.7.0 accepts tedious ^19.2.2 || ^20.0.0 and declares engines.node >= 18.19.0; tedious 20.0.0 declares engines.node >= 22. Node 22 or later resolves to tedious 20; Node 18 quietly stays on 19. Pin the Node version in package.json and CI.

The config object

import sql from 'mssql';

export const config: sql.config = {
  server: process.env.AZURE_SQL_SERVER!,     // <server>.database.windows.net
  port: 1433,                                // a number, never a string
  database: process.env.AZURE_SQL_DATABASE!,
  options: { encrypt: true, trustServerCertificate: false },
  pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },
};

• port must be a number. The config is not coerced, and a string port fails in a way that reads like a network fault.
• At these versions both packages default encrypt to true and trustServerCertificate to false. mssql seeds the pair before handing options to tedious, and tedious 20 resolves the same two on its own, so a pool built with an empty options still resolves to that pair. Earlier majors disagreed, so pin the versions rather than trust the default. At these versions a wrong value is something a person typed. When either may legitimately change is connect-to-azure-sql.
• The pool values above are the defaults. Ten is a floor for a toy and a ceiling for a real API, so set max deliberately. Choosing the number, and why the binding limit is workers rather than sessions, is connect-to-azure-sql.

The pool is a module-level object

This is the failure this skill exists for.

sql.connect(config) does not open a connection. It constructs a process-wide ConnectionPool the first time it is called and returns that same pool on every later call.

// db.ts: one pool, created once, awaited by everything else
import sql from 'mssql';
import { config } from './config.js';

export const poolPromise = new sql.ConnectionPool(config).connect();

Every handler does const pool = await poolPromise; and nothing more.

What goes wrong when the pool moves into the handler:

• A new ConnectionPool per invocation pays a TCP connect, a TLS handshake and a login every request, and each pool holds its own sockets, so the process ends far over its intended count.
• sql.close() at the end of a handler destroys the global pool under requests still using it, and against a new sql.ConnectionPool it closes only the pool sql.connect() built, returns null and leaves the sockets open. Use pool.close(), on a shutdown path and nowhere else.
• In a serverless host, module scope survives warm invocations and the handler body does not, which is what makes reuse possible.

Microsoft Entra ID without a secret

mssql passes authentication straight through to tedious. The types tedious 20 accepts, from the shipped connection.d.ts:

authentication.type	Use it for
azure-active-directory-default	Local development and most Azure hosts. Walks the @azure/identity chain
azure-active-directory-msi-app-service, azure-active-directory-msi-vm	A managed identity
azure-active-directory-service-principal-secret	An app registration and client secret
azure-active-directory-access-token	A token the app acquired itself
token-credential	An @azure/identity credential supplied directly
azure-active-directory-password	Nothing. Microsoft has deprecated this flow

export const entraConfig: sql.config = {
  server: process.env.AZURE_SQL_SERVER!,
  database: process.env.AZURE_SQL_DATABASE!,
  options: { encrypt: true, trustServerCertificate: false },
  authentication: {
    type: 'azure-active-directory-default',
    options: { clientId: process.env.AZURE_CLIENT_ID },  // user-assigned identity only
  },
};

@azure/identity is a dependency of tedious, so there is nothing extra to install. This config holds no secret and is safe in source control; the SQL authentication variant is not. The identity still needs a database principal, which is entra-id-auth.

Typed results

interface Person { id: number; first_name: string; }

const result = await pool.request()
  .input('id', sql.Int, id)
  .query<Person>('SELECT id, first_name FROM person WHERE id = @id');

const rows: Person[] = result.recordset;   // recordsets for every set, rowsAffected[0] for counts

The generic on query<T>() is a compile-time assertion, not runtime validation. Nothing checks the columns against Person; a renamed column yields undefined at run time while the types compile. If the shape matters, validate the rows or generate the types from the schema.

Use request.input(name, type, value) for every value; concatenating into the SQL text is how injection gets in.

Check it worked

Ask through mssql itself: what a command line client proves about encryption is true of its own session, not of the pool your process opened. Save this beside db.ts as check-connection.mjs.

import { poolPromise } from './db.js';

const pool = await poolPromise;
const { recordset: [r] } = await pool.request().query(`
  SELECT c.encrypt_option, DB_NAME() AS db,
         SUSER_NAME() AS login, USER_NAME() AS db_user
  FROM sys.dm_exec_connections AS c WHERE c.session_id = @@SPID`);
console.log(r, 'pool:', pool.size, pool.available, pool.borrowed);
await pool.close();

node check-connection.mjs

Read	Expect	A wrong value means
encrypt_option	TRUE	the session is in clear text; someone overrode options.encrypt
db	your database, never master	database is missing from the config
login, db_user	the principal you granted rights to	the credential chain picked another one
pool.size	at most pool.max, and flat as requests arrive	a pool per handler, the failure above

Two things this check trips over, measured 2026-09-03:

• pool.size throws before the pool is awaited, TypeError: Cannot read properties of undefined (reading 'numFree'), while @types/mssql declares it readonly size: number. Read it after the await or the check dies on its own instrumentation.
• A wrong host or port stops the script first, as ConnectionError: Failed to connect to <host>:<port>.

sys.dm_exec_connections needs VIEW DATABASE STATE, and on Basic, S0, S1 and elastic pools the server administrator, the Microsoft Entra administrator or ##MS_ServerStateReader##. Grant it for the check, then revoke it. CONNECTIONPROPERTY is no substitute: its documented property list has no encrypt_option.

Do not

• Do not call sql.connect() or construct a pool inside a request handler, route or serverless function body.
• Do not call pool.close() outside a deliberate shutdown path.
• Do not set trustServerCertificate: true to clear a certificate error. Read connect-to-azure-sql.
• Do not use azure-active-directory-password. Microsoft has deprecated the flow it uses.
• Do not write retry loops here. Transient faults are one policy for every language, in connect-to-azure-sql.

References

• Node.js driver for SQL Server: what tedious is and what support it carries. Read it when asked which driver is official.
• Connect and query with Node.js and mssql: the first-party quickstart, passwordless configuration included. Read it for a working end-to-end sample.
• node-mssql documentation: the config, pool and request API in full. Read it when a config key is in question.
• sys.dm_exec_connections: the columns and the permission the check needs. Read it when the check returns no row.
• connect-to-azure-sql: encryption doctrine, retry and transient faults, pool sizing, and the first-connect error on a paused database.
• For a runtime without TCP sockets, mssql cannot run; use an HTTP-based data or API layer that the runtime supports instead.