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