@selesterkft/sel-db

Selester Ltd. T-SQL connection handler using tedious.

This is basically a wrapper that wraps promises around tedious' callbacks.

Currently only works with stored procedures!

Migrating from 1.x to 2.x

Can be used with @selesterkft/express-logger.

Installation

npm i @selesterkft/sel-db

or

yarn add @selesterkft/sel-db

Basic example

Considering a stored procedure as follows, running on a local sql database:

CREATE PROCEDURE [dbo].[countChar]
@inputVal varchar(30),
@outputCount int OUTPUT
AS
set @outputCount = LEN(@inputVal);
GO

(Example from tedious docs)

A basic implementation would be:

import { DB, StoredProcedure } from '@selesterkft/sel-db';

// Create a configuration object. See the API section for details.
const sqlConfig = {
  server: 'localhost',
  options: {
    // tedious ^15.0.0 defaults this to false
    trustServerCertificate: true,
  },
  authentication: {
    type: 'default',
    options: {
      userName: 'my-username',
      password: 'my-password',
    },
  },
};

// Create an instance of the class
const db = new DB(sqlConfig);

// Create a stored procedure
const sp = new StoredProcedure('countChar');
// Add parameters to the stored procedure
sp.input('inputVal', 'VARCHAR', 'something', { length: 30 });
sp.output('outputCount', 'int');

// Call the procedure and get the result
const result = await db.callSP(sp);
console.log(result);
// {
//   output: {
//     outputCount: 9
//   },
//   columns: [],
//   recordset: []
// }

Real-life implementation

Sel-db can be used with a logger, @selesterkft/express-logger is tested with the package. You can use anything else that has debug(), info() and error() methods.

Create a file that exports an instance of the DB class. To keep things organized, it can also contain the config object.

db.js:

import logger from '@selesterkft/express-logger';
import { DB } from '@selesterkft/sel-db';

const sqlConfig = {
  server: 'localhost',
  options: {},
  authentication: {
    type: 'default',
    options: {
      userName: 'my-username',
      password: 'my-password',
    },
  },
};

export const db = new DB(sqlConfig, logger);

Then make a function for calling the stored procedure. THe connection with the SQL server will be initialized on calling a stored procedure for the first time. If you need to drop the connection, use db.dropConnection().

countChar.js

import { StoredProcedure } from '@selesterkft/sel-db';
import { db } from './db';

export default async function countChar(str) {
  const sp = new StoredProcedure('countChar');
  sp.input('inputVal', 'VARCHAR', str, { length: 30 });
  sp.output('outputCount', 'int');

  const sqlResult = await db.callSP(sp);

  return sqlResult.output.outputCount;
}

Since this is async, use eg. const a = await countChar('a') to get the result.

Logging

Sel-db allows some control over logging behaviour. To change settings, use the SELDB_LOGLEVEL and SELDB_LOGTYPE environment variables. Put these in a .env file and use dotenv or similar to access them, since you should already be doing this for the connection parameters.

.env

SELDB_LOGLEVEL=error
SELDB_LOGTYPE=json

SELDB_LOGLEVEL

Sets the logging level, in ascending verbosity: error, info and debug. Defaults to error. Setting this to silent will disable all logging.

SELDB_LOGTYPE

Sets the format of the log messages. string and the default json are currently suppoprted.

Example string:

"sel-db: openConnection: Database successfully connected."

The same in JSON:

{
  "caller":"openConnection",
  "message":"Database successfully connected.",
  "module":"sel-db"
}

Note that @selesterkft/express-logger will output log files in JSON (or more precisely, a list of JSONs in a file). With string format:

{
  "level":"info",
  "message":"sel-db: openConnection: Database successfully connected.",
  "timestamp":"2022-09-14T11:03:00.201Z"
}

With json:

{
  "caller":"openConnection",
  "level":"info",
  "message":"Database successfully connected.",
  "module":"sel-db",
  "timestamp":"2022-09-14T11:04:20.308Z"
}

Queueing stored procedure calls - Queue Processor

If a procedure is called while another is still being processed by the database, an EINVALIDSTATE error will occur. This is the notorious 'SentClientRequest state' error. (Check out Tedious F.E.Q.)

RequestError: Requests can only be made in the LoggedIn state, not the SentClientRequest state

From version 1.2.0 onwards, sel-db implements a Queue Processor to execute called procedures sequentially, handling this problem. callSP can now be called repeatedly, and the procedures will fill up a waiting queue if the database is busy processing a previous call.

Info level logging will show if a procedure needs to be stalled. This can show that the previous call is too slow or can be helpful for other debugging purposes.

API

Connection

const db = new DB(sqlConfig[, logger])

Creates a new instance of the database connection object with the configuration parameters given in sqlConfig. logger is an optional parameter, a logger object that has a logger.debug, a logger.info and a logger.error method for logging infos and errors respectively. If no logger is provided, logs will be written to the console (via console.log() and console.error()).

callSP(sp)

Async method that calls a stored procedure. sp should be an instance of StoredProcedure, see below.

Returns an object containing the results of the call. The example stored procedure will return:

{ 
  output: {
    outputCount: 9
  },
  columns: [],
  recordset: []
}

Output variables of the stored procedure will be in the output: {} object, their keys will be the value given in the name parameter of output().

dropConnection()

Closes the connection and clears the connection object.

getState()

Returns a string containing the state of the connection. This, AFAIK can be as follows: |state|string| |---|---| |INITIALIZED|Initialized| |CONNECTING|Connecting| |SENT_PRELOGIN|SentPrelogin| |REROUTING|ReRouting| |TRANSIENT_FAILURE_RETRY|TRANSIENT_FAILURE_RETRY| |SENT_TLSSSLNEGOTIATION|SentTLSSSLNegotiation| |SENT_LOGIN7_WITH_STANDARD_LOGIN|SentLogin7WithStandardLogin| |SENT_LOGIN7_WITH_NTLM|SentLogin7WithNTLMLogin| |SENT_LOGIN7_WITH_FEDAUTH|SentLogin7Withfedauth| |LOGGED_IN_SENDING_INITIAL_SQL|LoggedInSendingInitialSql| |LOGGED_IN|LoggedIn| |SENT_CLIENT_REQUEST|SentClientRequest| |SENT_ATTENTION|SentAttention| |FINAL|Final|

Possibly important ones are bolded. The connection needs to be in the LoggedIn state in order to process a request, it cannot be done while in the Initialized, Connecting or SentClientRequest state. Sel-db will establish the connection on the first call to a stored procedure and will re-initiate it if the connection gets broken. If a previous call is being processed (SentClientRequest), a Queue Processor will make sure the next call will wait for its turn.

This method is not async, so it will return the state at the moment it was called.

Stored procedures

const sp = new StoredProcedure('procedureName')

Creates a new stored procedure with the name procedureName, which should be the equivalent of the procedure's name in your SQL server.

input(name, type, value, options)

Possible overrides: addParam(...), addParameter(...)

Adds an input parameter to the procedure, to be called on the instantiated stored procedure object.

• name: string, the name of the parameter. Case sensitive.

• type: string, the type of the parameter. It is case-insesitive and will be matched to a datatype from tedious.

• value: the value the parameter will take. Check the above link to datatypes to know which JavaScript variable type to use. Optional.

• options: an optional object to specify additional type-related options. Basically length, precision or scale. From tedious docs:

  length for VarChar, NVarChar, VarBinary. Use length as Infinity for VarChar(max), NVarChar(max) and VarBinary(max).

  precision for Numeric, Decimal

  scale for Numeric, Decimal, Time, DateTime2, DateTimeOffset

output(name, type, value, options)

Possible overrides: addOutputParam(...), addOutputParameter(...)

Adds an output parameter, uses the same syntax as above. If there are no options needed, value and options can be omitted, otherwise define value as an empty string.

  sp.output('out1', 'int');
  sp.output('out2', 'nvarchar', '', { length: 'max' });

Known issues

ECONNRESET, timeout (?) on Azure

After some time, connections to Azure databases are lost, they switch to the 'Final' state, possibly due to timeout settings.

If a new call is made to the database while the connection is in the 'Final' state, sel-db will close the connection and initiate a new one automatically. This ensures that calls are processed should this error happen.

Still, an error level logging event will occur.

error.log:

{"caller":"openConnection","level":"error","message":{"code":"ESOCKET"},"module":"sel-db","timestamp":"2022-09-29T11:26:21.274Z"}