@strongnguyen/nestjs-mssql

A thin NestJS DynamicModule wrapper around the mssql driver — exposes a connection pool and a execSP helper for stored procedures, with optional JSON-structured logging.

2.0.0 is a breaking release. See MIGRATION.md for upgrade instructions from 1.x. Users who cannot migrate yet should pin ~1.1.0.

Requirements

• Node.js ≥ 22
• @nestjs/common ^11
• mssql ^11
• reflect-metadata ^0.2

Installation

npm install @strongnguyen/nestjs-mssql mssql reflect-metadata
npm install -D @types/mssql

Usage

Synchronous registration

import { MssqlModule } from '@strongnguyen/nestjs-mssql';

@Module({
  imports: [
    MssqlModule.register({
      server: '10.0.0.1',
      port: 1433,
      user: 'sa',
      password: 'pass',
      database: 'AppDb',
      options: {
        encrypt: false,                // mssql 11 defaults to true — explicitly disable for local SQL Server without TLS cert
        trustServerCertificate: true,  // dev only
      },
    }),
  ],
  providers: [MyService],
})
export class AppModule {}

Async registration

import { ConfigService } from '@nestjs/config';

MssqlModule.registerAsync({
  imports: [ConfigModule],
  useFactory: (cfg: ConfigService) => ({
    server: cfg.get('DB_HOST'),
    port: +cfg.get('DB_PORT'),
    user: cfg.get('DB_USER'),
    password: cfg.get('DB_PASS'),
    database: cfg.get('DB_NAME'),
    options: { encrypt: false, trustServerCertificate: true },
  }),
  inject: [ConfigService],
});

Calling stored procedures

import { Injectable } from '@nestjs/common';
import { MssqlService } from '@strongnguyen/nestjs-mssql';
import * as sql from 'mssql';

@Injectable()
export class UserService {
  constructor(private readonly mssql: MssqlService) {}

  async getUser(id: number) {
    const { recordset } = await this.mssql.getPool()
      .request()
      .input('id', sql.Int, id)
      .query('SELECT TOP 1 * FROM dbo.User WHERE Id = @id');
    return recordset[0];
  }

  async createUser(name: string) {
    const result = await this.mssql.execSP(
      'dbo.sp_CreateUser',
      [{ name: 'name', type: sql.NVarChar, value: name }],
      [{ name: 'newId', type: sql.Int }],
    );
    return result.output.newId;
  }
}

JSON-structured logging with traceId

Set debug: true and every execSP call emits 4-phase JSON logs (start / info / done / error). Each call has a fresh auto-generated traceId (UUID v4) for correlation:

{"traceId":"7f3a...","sp":"dbo.sp_CreateUser","phase":"start","inputs":[...],"outputs":[...]}
{"traceId":"7f3a...","sp":"dbo.sp_CreateUser","phase":"done","durationMs":42,"rowsAffected":[1],"returnValue":0,"recordsetCount":0}

Error logs fire regardless of debug so failures are never silenced.

Attaching outer-system context (HTTP trace, user ID, ...)

Provide a contextProvider to merge extra fields into every log payload. Typical pattern with AsyncLocalStorage:

import { AsyncLocalStorage } from 'node:async_hooks';

export const requestContext = new AsyncLocalStorage<{ traceId?: string; userId?: string }>();

MssqlModule.register({
  // ...connection options...
  debug: true,
  contextProvider: () => requestContext.getStore() ?? {},
});

// In a NestJS middleware/interceptor
@Injectable()
export class TraceMiddleware implements NestMiddleware {
  use(req: Request, _res: Response, next: NextFunction) {
    requestContext.run(
      { traceId: req.headers['x-trace-id'] as string, userId: (req as any).user?.id },
      () => next(),
    );
  }
}

If contextProvider returns { traceId }, it overrides the auto-generated UUID. If it throws, the library logs phase: 'context_error' and continues with the baseline UUID.

License

MIT