What’s new in MariaDB Connector/Node.js 3.0

Blog contributors: Diego Dupin and Ralf Gebhardt 

We are pleased to announce the general availability (GA) of MariaDB Connector/Node.js 3.0. MariaDB Connector/Node.js 3.0 is the newest version of our interface for building Node.js applications on top of MariaDB Community Server, MariaDB Enterprise, and MariaDB SkySQL.

MariaDB Connector/Node.js 3.0 includes the following enhancements:

• • Data exchange improvements
    • Prepared statement implementation with caching
    • Faster network performance using the new MariaDB Server 10.6 feature to skip sending metadata
  • Exact decoding for data types BIGINT and DECIMAL
  • Custom logging API
  • Handling and control back pressure for streaming result set
  • Optimized defaults
  • New ‘stream‘ option

Data exchange improvements

Prepared statement implementation

MariaDB Connector/Node.js now supports prepared statements.  These methods are compatible with the mysql2 driver, while also including enhancements:

• Parameters provided for a prepared statement can be a stream.
• Prepared statements are cached and can be reused although the prepared statement is getting closed by the application, to avoid that often used queries need to be prepared again. The option ‘prepareCacheLength’ is used to define the cache size, set to 256 cached prepared statements by default). Least recently used (LRU) prepared statements will be removed from the cache.
• MariaDB Server 10.6 provides a new feature to skip sending metadata. This new connector can take advantage of this feature, improving performance by reducing network traffic and eliminating redundant metadata parsing.

The connector provides two new connection methods:

• connection.prepare(sql) → Promise: Prepare a query.
• connection.execute(sql[, values]) → Promise: Prepare and Execute a query.

Example:

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

Or directly :

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

If reusing query multiple times, this permits to perform faster, specifically using MariaDB Server 10.6:

Example for command ‘select * from mysql.user limit 1’

To compare with mysql2 (mysql doesn’t implement execute API):

Faster network performance using MariaDB Server 10.6 metadata skip

MariaDB Server 10.6 provides a new feature to skip sending metadata when possible if the binary protocol is used.

When using connection.execute, which means that the binary protocol is used, the Connector is taking advantage of this. For SQL commands, which return a resultset, the server will only send metadata once while receiving the data.

Metadata skip improves performance by reducing network traffic and eliminating redundant metadata parsing.

Exact decoding for data types BIGINT / DECIMAL

The default behavior for MariaDB Connector/Node.js 2.x (and for other existing drivers like mysql2) for decoding the data types BIGINT and DECIMAL is to return a JavaScript Number object. JavaScript uses double-precision floating-point format numbers as specified in IEEE 754, which is returning approximate results for big values.

Integers can only be safely represented between -(2^53 – 1) and 2^53 – 1.

MariaDB Connector/Node.js 3.0, to return precise values, per default returns the objects:

• Decimal => JavaScript String object
• BigInt => JavaScript BigInt object

For compatibility reasons three options have been added to return BIGINT/DECIMAL as a number object:

Previous options supportBigNumbers and bigNumberStrings are included for compatibility, but are now deprecated.

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),
  }
});

Handling and control back pressure for streaming result set

Streaming a resultset ensured the connection state before MariaDB Connector/Node.js 3.0, at the cost of not handling back-pressure well. Since the goal of queryStream is to avoid the use of a large amount of memory, handling of backpressure 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 as queryStream now needs to be closed explicitly to ensure that a connection is not in a wrong state ( unhandled rows in the socket buffer).

Example:

async function streamingFunction() {
  const queryStream = connection.queryStream('SELECT * FROM mysql.user');
  try {
    for await (const row of queryStream) {
      console.log(row);
    }
  } catch (e) {
    queryStream.close();
  }
}

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.

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

New ‘stream’ option

The option `stream` provides a way to execute a function with a callback parameter before each connection stream creation.

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
          )
      });

Obtaining Connector/Node.js 3.0

MariaDB Connector/Node.js 3.0 is available from the mariadb.com download page.

Customers who have been using earlier releases of MariaDB Connector/Node.js should consult our Upgrade page for guidance on updating application code to reflect changes in connector behavior.

More information about using MariaDB Connector/Node.js is available in the Enterprise documentation.