mariadb

What is mariadb?

The mariadb npm package is a Node.js connector for MariaDB, a popular open-source relational database. It allows you to interact with MariaDB databases using JavaScript, providing functionalities for connecting to the database, executing queries, managing transactions, and handling connection pools.

What are mariadb's main functionalities?

Connecting to the Database

This code demonstrates how to create a connection pool and connect to a MariaDB database using the mariadb package. It establishes a connection and logs a message upon successful connection.

const mariadb = require('mariadb');
const pool = mariadb.createPool({
  host: 'localhost', 
  user: 'yourUsername', 
  password: 'yourPassword', 
  database: 'yourDatabase'
});

async function connect() {
  let conn;
  try {
    conn = await pool.getConnection();
    console.log('Connected to the database');
  } catch (err) {
    throw err;
  } finally {
    if (conn) conn.end();
  }
}

connect();

Executing Queries

This code sample shows how to execute a simple SELECT query using the mariadb package. It retrieves all rows from a specified table and logs them to the console.

const mariadb = require('mariadb');
const pool = mariadb.createPool({
  host: 'localhost', 
  user: 'yourUsername', 
  password: 'yourPassword', 
  database: 'yourDatabase'
});

async function executeQuery() {
  let conn;
  try {
    conn = await pool.getConnection();
    const rows = await conn.query('SELECT * FROM yourTable');
    console.log(rows);
  } catch (err) {
    throw err;
  } finally {
    if (conn) conn.end();
  }
}

executeQuery();

Managing Transactions

This code demonstrates how to manage transactions using the mariadb package. It begins a transaction, executes an INSERT query, and commits the transaction. If an error occurs, the transaction is rolled back.

const mariadb = require('mariadb');
const pool = mariadb.createPool({
  host: 'localhost', 
  user: 'yourUsername', 
  password: 'yourPassword', 
  database: 'yourDatabase'
});

async function manageTransaction() {
  let conn;
  try {
    conn = await pool.getConnection();
    await conn.beginTransaction();
    await conn.query('INSERT INTO yourTable (column1, column2) VALUES (?, ?)', [value1, value2]);
    await conn.commit();
    console.log('Transaction committed');
  } catch (err) {
    if (conn) await conn.rollback();
    console.error('Transaction rolled back', err);
  } finally {
    if (conn) conn.end();
  }
}

manageTransaction();

Handling Connection Pools

This code sample shows how to handle connection pools with the mariadb package. It sets a connection limit and demonstrates how to use the pool to execute a query.

const mariadb = require('mariadb');
const pool = mariadb.createPool({
  host: 'localhost', 
  user: 'yourUsername', 
  password: 'yourPassword', 
  database: 'yourDatabase',
  connectionLimit: 5
});

async function usePool() {
  let conn;
  try {
    conn = await pool.getConnection();
    const rows = await conn.query('SELECT * FROM yourTable');
    console.log(rows);
  } catch (err) {
    throw err;
  } finally {
    if (conn) conn.end();
  }
}

usePool();

Other packages similar to mariadb

mysql

The mysql package is a popular Node.js connector for MySQL databases. It offers similar functionalities to mariadb, such as connecting to the database, executing queries, and managing transactions. However, it is specifically designed for MySQL databases, whereas mariadb is tailored for MariaDB.

mysql2

The mysql2 package is another Node.js connector for MySQL databases. It is a more modern and faster alternative to the mysql package, with support for Promises and async/await. Like mariadb, it provides functionalities for connecting to the database, executing queries, and managing transactions.

pg

The pg package is a Node.js connector for PostgreSQL databases. While it serves a different database system, it offers similar functionalities to mariadb, such as connecting to the database, executing queries, and managing transactions. It is a good alternative if you are using PostgreSQL instead of MariaDB.

README

MariaDB Node.js connector

Non-blocking MariaDB and MySQL client for Node.js.

MariaDB and MySQL client, 100% JavaScript, compatible with Node.js 6+, with the Promise API.

Why a New Client?

While there are existing MySQL clients that work with MariaDB, (such as the mysql and mysql2 clients), the MariaDB Node.js Connector offers new functionality, like Insert Streaming and Pipelining while making no compromises on performance.

Insert Streaming

Using a Readable stream in your application, you can stream INSERT statements to MariaDB through the Connector.

    
    https.get('https://someContent', readableStream => {
        //readableStream implement Readable, driver will stream data to database 
        connection.query("INSERT INTO myTable VALUE (?)", [readableStream]);
    });

Pipelining

With Pipelining, the Connector sends commands without waiting for server results, preserving order. For instance, consider the use of executing two INSERT statements.

The Connector doesn't wait for query results before sending the next INSERT statement. Instead, it sends queries one after the other, avoiding much of the network latency.

For more information, see the Pipelining documentation.

Bulk insert

Some use cases require a large amount of data to be inserted into a database table. By using batch processing, these queries can be sent to the database in one call, thus improving performance.

For more information, see the Batch documentation.

Benchmarks

MariaDB provides benchmarks comparing the Connector with popular Node.js MySQL clients, including:

• promise-mysql version 3.3.1 + mysql version 2.15.0
• mysql2 version 1.5.3

promise-mysql  : 1,366 ops/sec ±1.42%
mysql2         : 1,469 ops/sec ±1.63%
mariadb        : 1,802 ops/sec ±1.19%

For more information, see the Benchmarks page.

Quick Start

The MariaDB Connector is available through the Node.js repositories. You can install it using npm :

$ npm install mariadb

Using ECMAScript < 2017:

const mariadb = require('mariadb');
const pool = mariadb.createPool({host: 'mydb.com', user: 'myUser', connectionLimit: 5});
pool.getConnection()
    .then(conn => {
    
      conn.query("SELECT 1 as val")
        .then((rows) => {
          console.log(rows); //[ {val: 1}, meta: ... ]
          return conn.query("INSERT INTO myTable value (?, ?)", [1, "mariadb"]);
        })
        .then((res) => {
          console.log(res); // { affectedRows: 1, insertId: 1, warningStatus: 0 }
          conn.end();
        })
        .catch(err => {
          //handle error
          conn.end();
        })
        
    }).catch(err => {
      //not connected
    });

Using ECMAScript 2017:

const mariadb = require('mariadb');
const pool = mariadb.createPool({host: 'mydb.com', user: 'myUser', connectionLimit: 5});

async function asyncFunction() {
  let conn;
  try {
	conn = await pool.getConnection();
	const rows = await conn.query("SELECT 1 as val");
	console.log(rows); //[ {val: 1}, meta: ... ]
	const res = await conn.query("INSERT INTO myTable value (?, ?)", [1, "mariadb"]);
	console.log(res); // { affectedRows: 1, insertId: 1, warningStatus: 0 }

  } catch (err) {
	throw err;
  } finally {
	if (conn) return conn.end();
  }
}

Documentation

The MariaDB Node.js Connector can use different APIs on the back-end: Promise and Callback.
The default API is Promise API.

Callback API is provided for compatibility with the mysql and mysql2 APIs.

Road Map

The Connector remains in development. Here's a list of features being developed for future releases:

• MariaDB ed25519 plugin authentication
• Query Timeouts

Contributing

If you would like to contribute to the MariaDB Node.js Connector, please follow the instructions given in the Developers Guide.

To file an issue or follow the development, see JIRA.

Changelog

2.0.4 (07 May 2019)

Full Changelog

• [CONJS-69] permit set numeric parameter bigger than javascript 2^53-1 limitation
• [CONJS-68] error when reading datetime data and timezone option is set
• [CONJS-58] parse Query when receiving LOAD LOCAL INFILE, to prevent man in the middle attack
• [CONJS-62] support named timezones and daylight savings time
• [CONJS-63] add type definitions for typescript
• [CONJS-64] handle Error packet during resultset to permit query timeout with SET STATEMENT max_statement_time=<val> FOR <query>
• [CONJS-66] SET datatype handling (returning array)
• [CONJS-67] Changing user does not takes in account connector internal state (transaction status)

Pool improvement

New Options

|option|description|type|default| |---:|---|:---:|:---:| | idleTimeout | Indicate idle time after which a pool connection is released. Value must be lower than @@wait_timeout. In seconds (0 means never release) |integer | 1800 | | minimumIdle | Permit to set a minimum number of connection in pool. Recommendation is to use fixed pool, so not setting this value.|integer | set to connectionLimit value |

This permits to set a minimum pool size, meaning that after a period of inactivity, pool will decrease inner number of connection to a minimum number of connection (defined with minimumIdle). By default, connections not used after idleTimeout (default to 30 minutes) will be discarded, avoiding reaching server @@wait_timeout.

Pool handle connection creation automatically, with now some delayed after failing to establish a connection, to avoid using CPU unnecessary. Authentication error in pool have now a better handling.