Release Notes for MariaDB Connector/Node.js 3.0.0

Overview

MariaDB Connector/Node.js is the interface between Node.js applications and MariaDB Server. MariaDB Connector/Node.js enables development of Node.js applications.

MariaDB Connector/Node.js 3.0.0 was released on 2022-03-01. This release is of General Availability (GA) maturity.

Notable Changes

This first GA release of MariaDB Connector/Node.js 3.0 includes a variety of new features and fixes, compared to MariaDB Connector/Node.js 2.5.

Prepared Statements

  • Support added for prepared statements with mysql2 compatible methods.

    • the connection.prepare(sql) → Promise method can be used to prepare queries

    • the connection.execute(sql[, values]) → Promise method can be used to prepare and execute queries.

    These methods permit the use of streaming parameters and run execute() with a prepared cache length. The default for prepareCacheLength is 256.

connection.prepare(sql)

  • connection.prepare(sql) → Promise method will PREPARE a command that can be executed many times.

    • sql: string | JSON SQL string value or JSON object to supersede default connections options. JSON objects must have an sql property. For instance, { dateStrings: true, sql: 'SELECT now()' }

    Returns a promise that:

    • resolves with a PREPARE object

    • rejects with an Error

    The connection.prepare(sql) → Promise method requires prepare.close() to properly close the object:

    const prepare = await conn.prepare('INSERT INTO mytable(id,val) VALUES (?,?)');
await prepare.execute([1, 'val1'])
prepare.close();

connection.execute(sql[, values])

  • connection.execute(sql[, values]) → Promise method will PREPARE, EXECUTE, and CLOSE a command. The connection.execute method is most useful if the command will be used often and if prepare cache is enabled (default). If the PREPARE result is already in cache, only the EXECUTE command is executed. MariaDB server 10.6 will avoid re-sending result-set metadata if it has not changed, permitting even faster results.

    • sql: string | JSON SQL string or JSON object to supersede default connection options. When using JSON object, object must have a sql key. For instance, { dateStrings: true, sql: 'SELECT now()' }

    • values: array | object Defines placeholder values. Usually an array, but in cases of only one placeholder, can be given as a string.

    Returns a promise that:

    const res = await conn.execute('SELECT * FROM mytable WHERE someVal = ? and otherVal = ?', [1, 'val1']);

PREPARE Object

Property

Description

id

Prepared statement identifier

query

SQL statement that was prepared

database

The database that was selected when the SQL statement was prepared. The SQL statement can query other databases by fully qualifying the table names as DATABASE_NAME.TABLE_NAME

parameters

An array of input parameters that are bound to the prepared statement

columns

An array of column definitions that describe the result-set for the prepared statement

  • execute(values) → Promise

    • values: An array or object that defines parameters for the prepared statement. When multiple parameters are specified, this should be an array. When only one parameter is specified, a string object, or an array can be used.

    Returns a promise that:

  • close() → void

    • By default, this method closes the prepared statement.

    • This method MUST be called every time the prepared statement is used. Each time a Prepared object is used, it must be closed.

    • If the prepared cache is enabled (default), the prepared statement can be returned to the cache instead of being closed.

    const prepare = await conn.prepare('INSERT INTO mytable(id,val) VALUES (?,?)');
await prepare.execute([1, 'val1'])
prepare.close();

Data Types

  • DECIMALDECIMAL data types now return as JavaScript String objects, instead of Number objects.

  • BIGINTBIGINT data types now return as JavaScript BigInt objects, instead of Number objects.

Connection Options

  • Adds insertAsNumber connection option. For backwards compatibility, when set to true, insertAsNumber causes the last insert ID's from INSERTINSERT and UPDATEUPDATE statements to return as JavaScript Number objects, instead of the default BigInt objects.

  • Adds decimalAsNumber connection option. When set to true, decimalAsNumber causes DECIMALDECIMAL data types to return as Number objects instead of String objects, for backwards compatibility.

  • Adds bigIntAsNumber connection option. When set to true, bigIntAsNumber causes BIGINTBIGINT data types to return as Number objects, instead of the default BigInt object, for backwards compatibility.

  • Deprecates supportBigNumbers connection option.

  • Deprecates bigNumberStrings connection option.

  • Adds support in logger connection option for mapping logs to an external logger. This option uses the network() function to log each network exchange, query() to log each SQL query, and error() to log each error.

Streaming Result Set

  • Optimized handling and control back pressure for streaming result set (CONJS-168)

    Streaming a result set ensured the connection state before version 3.0, at the cost of not handling back pressure well. (CONJS-168)

    Since the goal of queryStream is to avoid the use of a large amount of memory, handling of back pressure has been optimized. If data handling takes some amount of time, the socket is paused to avoid the node socket buffer growing indefinitely.

    This has an impact on the use of function stream.pipeline because queryStream now must be closed explicitly to ensure that a connection is not in a wrong state (for example, with unhandled rows in the socket buffer).

    For example:

    const queryStream = connection.queryStream("SELECT * FROM mysql.user");
stream.pipeline(
   queryStream,
   transformStream,
   someWriterStream,
   (err) => { queryStream.close();
});

Custom Logging API

External logging can now be easily enabled using the new logging API, provided with MariaDB Connector/Node.js 3.0 via the three caller functions:

  • network(string): called for each network exchange.

  • query(string): called for each command.

  • error(Error): called for each error.

Simple example to log network exchanges, queries, and errors:

const pool = mariadb.createPool({
  host: 'mydb.com',
  user:'myUser',
  password: 'myPwd',
  logger: (msg) => console.log(msg)
});

Example of a more detailed use:

const mariadb = require('mariadb');
const winston = require('winston');

const logger = winston.createLogger({
  level: 'info',
  transports: [
    // - Write all logs with level `error` and below to `error.log`
    // - Write all logs with level `info` and below to `combined.log`
    new winston.transports.Console({ filename: 'error.log', level: 'error' }),
    new winston.transports.Console({ filename: 'combined.log' })
  ]
});

const pool = mariadb.createPool({
  host: 'mydb.com',
  user: 'myUser',
  password: 'myPwd',
  logger: {
    query: (msg) => logger.info(msg),
    error: (err) => logger.error(err),
  }
});

Optimized Defaults

  • The pool cluster option removeNodeErrorCount now defaults to infinity. This prevents a situation where part of the pool is removed from the cluster when a server temporarily fails. (CONJS-176, CONJS-179)

    The pool option resetAfterUse now defaults to false. This avoids the execution of a COM_RESET each time a connection is returned to the pool.

  • The default connectTimeout value has changed from 10000 (10 seconds) to 1000 (1 second). (CONJS-183)

  • BIT(1) is now considered to be type BOOLEAN. A new option 'bitOneIsBoolean', enabled by default, can be disabled for compatibility with the old behavior. (CONJS-185)

New stream Option

  • The option stream provides a way to execute a function with a callback parameter before each connection stream creation. (CONJS-180)

    Option        Description                                                             Type            Default
--------------------------------------------------------------------------------------------------------------
stream        permits to set a function with parameter to set stream (since 3.0)      function

    This can permit setting a SSH Tunnel, for example:

    const mariadb = require('mariadb');
const tunnel = require('tunnel-ssh');
const fs = require('fs');

const conn = await mariadb.createConnection({
   user: 'dbuser',
   password: 'dbpwd',
   port: 27000,
   stream: (cb) =>
     tunnel(
       {
         // remote connection ssh info
         username: 'sshuser',
         host: '157.230.123.7',
         port: 22,
         privateKey: fs.readFileSync('./key.ppk'),
         // database (here on ssh server)
         dstHost: '127.0.0.1',
         dstPort: 3306,
         // local interface
         localHost: '127.0.0.1',
         localPort: 27000
       },
       cb
     )
 });

Other Optimizations

  • Batch operations now support the returning clause (CONJS-125)

    • For example: INSERT INTO XXX VALUES (?,?,?) RETURNING id

  • Update code to Ecma version ES2022 supported by Node.js 12 (CONJS-178)

  • Performance improvement for multi-line result sets (CONJS-172)

  • Multi-row result sets now perform better (CONJS-188)

Upgrade

MariaDB Connector/Node.js 3.0 is fully compatible with MariaDB Connector/Node.js 3.3. As a new fully compatible major version, MariaDB Connector/Node.js 3.3 supersedes MariaDB Connector/Node.js 3.0, and users should upgrade to 3.3.