@kmhgmbh/kmh-db-connector

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')

Creates a connector object with an underlying MSSQL connection.

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' }]