Which Node.js connector is best to use?

spacer

There are 3 different MariaDB/MySQL drivers for node.js: mysql, mysql2 and mariadb.

The mysql connector was historically the first connector available to the ecosystem.

The mysql2 and mariadb connectors share the same API than the mysql connector with some additions:

  • Providing a promise API (promise is possible using mysql with additional promise-mysql package)
  • Permit use of prepared statement

And using the mariadb connector only adds:

  • Batch support
  • Pipelining
  • Permits streaming

The principle of connectors is to have fast and reliable results. All these drivers are mature and reliable. So what about performance?

Here are the benchmark results for common operations or specific features.

Operations are:

  • Simple insert
  • Select returning one row
  • Select returning one row using prepare statement
  • Select with 1000 rows
  • Batch insert

Benchmarks runs on two DigitalOcean hosts with 16GB of memory and 4 CPU’s, running Ubuntu 22.04.

  • Server Host: MariaDB Server 10.6 under the default configuration, just commenting bind-address to permit access from client server.
  • Client Host: Node.js version v18.13.0

The MariaDB Node.js Connector was then tested alongside the following MySQL connectors (latest version at the time of writing this post):

(benchmark done using benchmark.js: see source and complete results of the benchmarks)

Simple insert

The simplest insert is done using command ‘DO 1’ that returns the same results as an INSERT without disturbance.

const res = await conn.query('do 1');

Results:

mysql :  7,063.6 ops/s ± 0.4%

mysql2 :  6,465.3 ops/s ± 0.6%

mariadb :  8,531.9 ops/s ± 0.3%

 

Select returning one row

Returning one row of a table containing 100 integer fields.

DDL:

CREATE TABLE test100 (i1 int,i2 int, ..., i100 int);
INSERT INTO test100 value (1,2,..., 100);

Benchmark:

const res = await conn.query('select * FROM test100');

Result:

mysql :  2,738.7 ops/s ± 1.3%

mysql2 :  2,404.9 ops/s ± 1.3%

mariadb :  5,650.8 ops/s ± 1.4%

 

 

Select returning one row using prepare statement

The mysql2 and mariadb connectors permit using prepared statements.

Executing the same command using connection.execute in place of connection.query.

Benchmark:

const res = await conn.execute('select * FROM test100');

Result:

mysql2 :  2,473.4 ops/s ± 1.3%

mariadb :   10,533 ops/s ± 1.7%

 

 

MariaDB Server 10.6 with MDEV-19237 avoids resending metadata when they haven’t changed. The server will send metadata only if they have changed (DDL change for example). This avoids useless information transiting on the network and parsing the metadata.

The MariaDB connector implements this MariaDB behavior while mysql2 doesn’t.

Select with 1000 rows

DDL:

CREATE TABLE 1000rows(id INT not null primary key auto_increment, val VARCHAR(32));
# 1000 x
INSERT INTO 1000rows(val) VALUES ('abcdefghijabcdefghijabcdefghijaa');

Benchmark:

const res = await conn.query('select * from 1000rows');

Result:

mysql :  1,140.7 ops/s ± 0.9%

mysql2 :  1,474.5 ops/s ± 1.5%

mariadb :  2,185.2 ops/s ± 1.1%

 

 

Batch insert

Benchmark:

mysql and mysql2 do not have batching capabilities, so using simple loop:

const queries = [];
for (let i = 0; i < 100; i++) {
 queries.push(conn.query('INSERT INTO perfTestTextBatch(t0) VALUES (?)', param));
}
const res = await Promise.all(queries);

mariadb connector:

const res = await conn.batch(
'INSERT INTO perfTestTextBatch(t0) VALUES (?)', [
  [param], //1
  [param], //2
  ..., 
  [param] //100
]
);

Result:

mysql :      219 ops/s ± 0.9%

mysql2 :    173.6 ops/s ± 1.4%

mariadb :  6,489.8 ops/s ± 1.2%

 

The MariaDB connector implements BULK command if the server supports it, pipelining if the server doesn’t.

Conclusion

The data show that which connector is used can change the performance results of your database server. We released the MariaDB Node.js connector 4 years ago and today its receiving 300K downloads per month. We hope you’ll give it a try with your MariaDB Server deployment on prem or in the cloud on MariaDB SkySQL.