KMH Database Connector

Simple abstraction layer for mssql.

Installation

This package supports Node v18+.

npm install --save @kmhgmbh/kmh-db-connector

Usage

import DbConnector from '@kmhgmbh/kmh-db-connector';

const conf = {
    user: 'username',
    password: 'password',
    server: 'host',
    database: 'database name',
    port: 1433,
}

const connector = new DbConnector(config);

const result = await connector.executeProcedureSafe(
    'SCHEMA.GET_USER',
    {
        FIRST_NAME: 'Martina',
        LAST_NAME: 'Mustermann',
    },
    'TEST_DATABASE',
);

Docs

class DbConnector

constructor(configuration: DbConnectorConfiguration, language: string = 'deutsch', poolLogging: boolean = false)

Creates a connector object with an underlying MSSQL connection.

The optional poolLogging flag enables structured logging of the connection pool state (in AWS CloudWatch Embedded Metric Format) on each acquireSuccess and release event. It is disabled by default to avoid noise in environments where the metrics are not needed.

// Enable connection pool logging
const connector = new DbConnector(config, 'deutsch', true);

Configuration fields

The DbConnectorConfiguration type picks a subset of fields from mssql.config.

| Name | Type | Description | |--|--|--| | server | string | Required - database host | | port | number | TCP port to use for connection | | user | string | Username | | password | string | Password for specified user | | database | string | Specifies the database to USE when connecting to the server | | connectionTimeout | number | Maximum amount of time to connect to the server (milliseconds) | | requestTimeout | number | Maximum duration of a request to the database (milliseconds) | | options | mssql.IOptions | Additional options. Will automatically add options.trustServerCertificate: true before connecting | | pool | mssql.PoolOpts<mssql.Connection>| connection pool options |

async tryConnect(): Promise<void>

Actively tries to connect to the configured database server. Does nothing if connection is already established.

async tryDisconnect(): Promise<void>

Actively tries to close the connection to the configured database server. Does nothing if no connection exists.

async executeQueryRequest<T>(query: string): Promise<mssql.IResult<T>>

Accepts a raw query string which will be directly executed, and returns the raw MSSQL result.

const result = await connector.executeQueryRequest<FooObj>('SELECT * FROM DATABASE.TEST.FOO');

async executeProcedureRaw<T>(name: string, parameters: string[], database: string): Promise<mssql.IResult<T>[]>

This method is deprecated and will be removed in v5.0.0. Use executeProcedureSafeRaw instead.

Constructs a stored procedure execution query from given parameters and passes it to executeQueryRequest.

const result = await connector.executeProcedureRaw<FooObj>(
    'TEST.GET_FOO',
    [
        '@FOO = bar',
    ],
    'DATABASE',
);

// result: { recordset: [...], recordsets: [...], ... }

async executeProcedure<T>(name: string, parameters: string[], database: string): Promise<T[]>

This method is deprecated and will be removed in v5.0.0. Use executeProcedureSafe instead.

Calls executeProcedureRaw and extracs the recordSet property from the IResult object as an array.

const result = await connector.executeProcedure<FooObj>(
    'TEST.GET_FOO',
    [
        '@FOO = bar',
    ],
    'DATABASE',
);

// result: [...]

async executeProcedureNormalized<T>(name: string, parameters: string[], database: string): Promise<T[]>

This method is deprecated and will be removed in v5.0.0. Use executeProcedureSafeNormalized instead.

Calls executeProcedure and normalizes the resulting array (a normalized object has its keys lowercased).

type FooObj = {
    FOO: string;
}

const result = await connector.executeProcedureNormalized<FooObj>(
    'TEST.GET_FOO',
    [
        '@FOO = bar',
    ],
    'DATABASE',
);

//result = [{ foo: 'bar' }]

async executeProcedureSafeRaw<T>(name: string, parameters: string[], database: string): Promise<mssql.IProcedureResult<T>[]>

Constructs a mssql.PreparedStatement from the given parameters and executes it.

const result = await connector.executeProcedureSafeRaw<FooObj>(
    'TEST.GET_FOO',
    { FOO: 'bar' },
    'DATABASE',
);

// result = { returnValue: ..., recordset: [...], recordsets: [...], ... }

async executeProcedureSafe<T>(name: string, parameters: string[], database: string): Promise<T[]>

Calls executeProcedureSafeRaw and extracts the recordset property from the IProcedureResult object as an array.

const result = await connector.executeProcedureSafe<FooObj>(
    'TEST.GET_FOO',
    { FOO: 'bar' },
    'DATABASE',
);

// result: [...]

async executeProcedureSafeNormalized<T>(name: string, parameters: string[], database: string): Promise<T[]>

Calls executeProcedureSafe and normalizes the resulting array (a normalized object has its keys lowercased).

type FooObj = {
    FOO: string;
}

const result = await connector.executeProcedureSafeNormalized<FooObj>(
    'TEST.GET_FOO',
    { FOO: 'bar' },
    'DATABASE',
);

//result = [{ foo: 'bar' }]