Node-mssql: mssql uri connection string: enableArithAbort

[Vizzr]

I updated to the most recent node-mssql version and now I am getting a tedious deprecated warning: _tedious deprecated The default value for config.options.enableArithAbort will change from false to true in the next major version of tedious. Set the value to true or false explicitly to silence this message._

I have looked through this repository, but I only found the solution for passing a config to the ConnectionPool.

How would this work with the uri connection string?
mssql://username:password@ip:1433/database?encrypt=true&enableArithAbort=false

Expected behaviour:

The deprecated message goes away

Actual behaviour:

Message is still there, eventhough the enableArithAbort is passed
mssql://username:password@ip:1433/database?encrypt=true&enableArithAbort=false

Configuration:

mssql://username:password@ip:1433/database?encrypt=true&enableArithAbort=false

Software versions

• NodeJS: 10.5.0
• node-mssql: 6.1.0
• SQL Server: 13.0.4001.0

[jesben]

Works for me

return new sql.ConnectionPool({
      server: this.config.get('DATABASE_HOST'),
      database: this.config.get('DATABASE_DB'),
      user: this.config.get('DATABASE_USER'),
      password: this.config.get('DATABASE_PASSWORD'),
      options: {
        encrypt: false,
        enableArithAbort: false // Works message gone
      },
      pool: {
        min: 1
      }
    })
      .connect()
      .then(pool => {
        console.log('Connected to MSSQL');
        return pool
      })
      .catch(err => console.log('Database Connection Failed!', err));

[Vizzr]

That would indeed work, but this requires you to setup your configuration of your connection as a JSON object (like you have).

The question is for when you have a mssql uri connection string

[dhensby]

@Vizzr I don't think that option can be passed in using a URL string at the moment, that feature would need to be added.

[davidboothe]

the fact that this message throws an error instead of a warning is really annoying

[dhensby]

the fact that this message throws an error instead of a warning is really annoying

this originates from the tedious driver

[dhensby]

To give an update on this - I'm creating a standalone connection string library which will be more feature complete for parsing connection strings and URIs (https://github.com/tediousjs/connection-string).

At the moment it's just under development, but hopefully it will be part of v7 and I may back-port it to v6 too

[vitaly-t]

I'm creating a standalone connection string library which will be more feature complete for parsing connection strings and URIs

Such library already exists - connection-string. Use of the standard connection URI is a much cleaner approach, which has wider support these days.

[dhensby]

well that looks very useful - I might look at implementing it, then

[dhensby]

OK, unfortunately that library cannot parse Augmented Backus-Naur Form strings, so it's not suitable.

[vitaly-t]

OK, unfortunately that library cannot parse Augmented Backus-Naur Form strings, so it's not suitable.

Why would a connection-string library parse a web form? It is completely out of the scope.

[dhensby]

I'm not concerned if the parsing of ABNF strings is in or out of scope of that library, it's in scope for the parsing required by this library and that's because the SqlClient Connection String format is an implementation of ABNF.

[vitaly-t]

So you parse the custom data from the form, and then pass it on to the connection-string. Easier still, I'm sure there are already parsers out there for the form data, so you just need to integrate one of those with the existing connection-string.

In all, we are talking a simple integration module between form data and connection string.

[dhensby]

What are you talking about? If I need the connection string that MS gives for a DB connection to be parsed why would I write the logic to parse that and then pass it on to another library?!

[vitaly-t]

You started with the idea of writing your own connection-string parser, and I simply followed up on that, as something unnecessary today. As for the rest that's needed specifically for your library, you should be able to figure out yourself.

[webia1]

Just waiting longingly for a solution; thank you!

[nfantone]

FWIW, what I ended up doing, as a workaround, is re-using the internal resolve function from mssql/lib/connectionstring.

// The `resolve` function below is required from a "private" module in `mssql` - we use it to be able
// to override default options even when supplying a connection string instead of a full settings object
const { resolve: resolveConnectionString } = require('mssql/lib/connectionstring');

/**
 * Default `mssql` connection options.
 * @default
 * @const {readonly Object}
 */
const DEFAULT_OPTIONS = Object.freeze({
  // Suppress deprecation warning on `config.options.enableArithAbort`  and `config.options.validateBulkLoadParameters`
  // that gets written to stdout every time a connection is acquired if their values are unset
  enableArithAbort: true,
  validateBulkLoadParameters: true
});

const isString = value => typeof value === 'string';

// Same as JSON.parse(JSON.stringify(obj)), but shallow
const removeUndefined = obj => obj ? Object.keys(obj).reduce((o, key) => obj[key] === undefined ? o : ({ ...o, [key]: obj[key] }) , {}) : obj;

const defineOptions = configOrUri => {
  const config = isString(configOrUri) ? resolveConnectionString(configOrUri) : configOrUri;
  return {
     ...config,
     options: {
       ...DEFAULT_OPTIONS,
       // `resolveConnectionString` sets `enableArithAbort` to `undefined` explicitly preventing
       // the spread from overriding it, so we remove all `undefined` values from it
       ...removeUndefined(config.options)
     }
  }
}

function createConnection(configOrUri) {
  const options = defineOptions(configOrUri);
  // ...
}

[webia1]

Thank you, it works now, but that's a hack. It would most probably not work with the next library update.

[nfantone]

Yeah, I hear you. Mentioned it was "a workaround". Although, that particular module has been around for more than four years - and it's always going to be there for the current version. If it changes later on, it should be "as easy" as swapping the internal implementation for the external connection URI parsing tool.

[webia1]

Thank you very much. It simply works!

Here the TypeScript Version of the same (by @nfantone):

Step 1: Install Types

npm install @types/mssql -D

Step 2: Make Security Checks and fix them

npm audit fix

Step 3: Re-Use resolve

import { resolve as resolveConnectionString } from 'mssql/lib/connectionstring';

const DEFAULT_OPTIONS = Object.freeze({
  encrypt: true,
  enableArithAbort: true,
  validateBulkLoadParameters: true,
});

const removeUndefined = (obj: { [x: string]: any }) =>
  obj
    ? Object.keys(obj).reduce(
        (o, key) =>
          obj[key] === undefined ? o : { ...o, [key]: obj[key] },
        {},
      )
    : obj;

// In my case it is always a string
const defineOptions = (configOrUri: any) => {
  const config = resolveConnectionString(configOrUri);
  return {
    ...config,
    options: {
      ...DEFAULT_OPTIONS,
      ...removeUndefined(config.options),
    },
  };
};

const mssqlConnectionStringRaw = `mssql://${__YOUR__.db_user}:${__YOUR__.db_password}@${__YOUR__.db_server}/${__YOUR__.db_database}`;

export const  mssqlConnectionString: defineOptions(mssqlConnectionStringRaw);

[yarick123]

@webia1 the ts variant has the following compilation problem with the latest packages (ts 4.0.3, @types/mssql 6.0.5, mssql 6.2.3):

Could not find a declaration file for module 'mssql/lib/connectionstring'. '......./node_modules/mssql/lib/connectionstring.js' implicitly has an 'any' type.
If the 'mssql' package actually exposes this module, consider sending a pull request to amend 'https://github.com/DefinitelyTyped/DefinitelyTyped/tree/master/types/mssql'