@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 executeProcedure<T>(name: string, parameters: string[], database: string): Promise<T[]>

Accepts a stored procedure name, a list of parameters, and a database name. Returns an array of objects or an empty array if the stored procedure returns no records.

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

// result: [...]

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

Accepts a stored procedure name, a list of parameters, and a database name. Returns an array of normalized objects or an empty array if the stored procedure returns no records.

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 executeProcedureRaw<T>(name: string, parameters: string[], database: string): Promise<mssql.IResult<T>[]>

Accepts a stored procedure name, a list of parameters, and a database name. Returns the raw IResult object of the SQL request.

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

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

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

Accepts a stored procedure name, a parameter object, and a database name. Returns an array of objects or an empty array if the stored procedure returns no records.

Will use mssql.PreparedStatement for request execution.

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

// result: [...]

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

Accepts a stored procedure name, a parameter object, and a database name. Returns an array of objects or an empty array if the stored procedure returns no records.

Will use mssql.PreparedStatement for request execution.

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

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

Accepts a stored procedure name, a parameter object, and a database name. Returns the raw IProcedureResult object of the SQL request.

Will use mssql.PreparedStatement for request execution.

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

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