Promise API Documentation

The MariaDB Node.js Connector can use different APIs on the back-end: Promise and Callback. The default API is Promise. Callback is provided for compatibility with the mysql and mysql2 APIs.

Documentation provided on this page uses the Promise API. If you would like to develop an application with the Callback API or have an existing application that you want to switch from the MySQL APIs to the MariaDB Connector, see the Callback API documentation.

Installation

Install the mariadb Connector using npm

$ npm install mariadb

To use the Connector, you need to import the package into your application code.

const mariadb = require('mariadb');

You can then uses the Connector in your application code with the Promise API. For instance,

  const mariadb = require('mariadb');

  mariadb.createConnection({host: 'mydb.com', user: 'myUser', password: 'myPwd'})
    .then(conn => {
      conn.query("select 1", [2])
        .then(rows => {
          console.log(rows); // [{ "1": 1 }]
          conn.end();
        })
        .catch(err => { 
          //handle query error
        });
    })
    .catch(err => {
      //handle connection error
    });

Timezone consideration

It's not recommended, but in some cases, Node.js and database are configured with different timezone.

By default, `timezone` option is set to 'local' value, indicating to use client timezone, so no conversion will be done.

If client and server timezone differ, `timezone` option has to be set to server timezone. - 'auto' value means client will request server timezone when creating a connection, and use server timezone afterwhile. - To avoid this additional command on connection, `timezone` can be set to IANA time zone.

Connector will then convert date to server timezone, rather than the current Node.js timezone.

Promise API

Base:

• createConnection(options) → Promise : Creates a new connection.
• createPool(options) → Pool : Creates a new Pool.
• createPoolCluster(options) → PoolCluster : Creates a new pool cluster.

Connection:

• connection.query(sql[, values]) → Promise: Executes a query.
• `connection.queryStream(sql[, values]) → Emitter`: Executes a query, returning an emitter object to stream rows.
• connection.batch(sql, values) → Promise: fast batch processing.
• connection.beginTransaction() → Promise: Begins a transaction.
• connection.commit() → Promise: Commits the current transaction, if any.
• connection.rollback() → Promise: Rolls back the current transaction, if any.
• connection.changeUser(options) → Promise: Changes the current connection user.
• connection.ping() → Promise: Sends a 1 byte packet to the database to validate the connection.
• connection.reset() → Promise: reset current connection state.
• connection.isValid() → boolean: Checks that the connection is active without checking socket state.
• connection.end() → Promise: Gracefully close the connection.
• connection.destroy(): Forces the connection to close.
• connection.pause(): Pauses the socket output.
• connection.resume(): Resumes the socket output.
• connection.serverVersion(): Retrieves the current server version.
• events: Subscribes to connection error events.

Pool:

• pool.getConnection() → Promise : Creates a new connection.
• pool.query(sql[, values]) → Promise: Executes a query.
• pool.batch(sql, values) → Promise: Executes a batch
• pool.end() → Promise: Gracefully closes the connection.
• pool.activeConnections() → Number`: Gets current active connection number.
• pool.totalConnections() → Number`: Gets current total connection number.
• pool.idleConnections() → Number`: Gets current idle connection number.
• pool.taskQueueSize() → Number`: Gets current stacked request.
• pool events: Subscribes to pool events.

PoolCluster

• poolCluster.add(id, config) : add a pool to cluster.
• poolCluster.remove(pattern) : remove and end pool according to pattern.
• poolCluster.end() → Promise : end cluster.
• poolCluster.getConnection(pattern, selector) → Promise : return a connection from cluster.
• poolCluster.of(pattern, selector) → FilteredPoolCluster : return a subset of cluster.
• poolCluster events: Subscribes to pool cluster events.

Base API

createConnection(options) → Promise

Creates a new Connection object.

Example:

const mariadb = require('mariadb');
mariadb.createConnection({
      host: 'mydb.com', 
      user:'myUser',
      password: 'myPwd'
    })
    .then(conn => {
      console.log("connected ! connection id is " + conn.threadId);
    })
    .catch(err => {
      console.log("not connected due to error: " + err);
    });

Connection options

Essential options list:

For more information, see the Connection option documentation.

Connecting to Local Databases

When working with a local database (that is, cases where MariaDB and your Node.js application run on the same host), you can connect to MariaDB through the Unix socket or Windows named pipe for better performance, rather than using the TCP/IP layer.

In order to set this up, you need to assign the connection a `socketPath` value. When this is done, the Connector ignores the `host` and `port` options.

The specific socket path you need to set is defined by the `socket` server system variable. If you don't know it off hand, you can retrieve it from the server.

SHOW VARIABLES LIKE 'socket';

It defaults to `/tmp/mysql.sock` on Unix-like operating systems and `MySQL` on Windows. Additionally, on Windows, this feature only works when the server is started with the `--enable-named-pipe` option.

For instance, on Unix a connection might look like this:

const mariadb = require('mariadb');
mariadb.createConnection({ socketPath: '/tmp/mysql.sock', user: 'root' })
    .then(conn => { ... })
    .catch(err => { ... });

It has a similar syntax on Windows:

const mariadb = require('mariadb');
mariadb.createConnection({ socketPath: '\\\\.\\pipe\\MySQL', user: 'root' })
    .then(conn => { ... })
    .catch(err => { ... });

createPool(options) → Pool

Creates a new pool.

Example:

const mariadb = require('mariadb');
const pool = mariadb.createPool({ host: 'mydb.com', user: 'myUser', connectionLimit: 5 });
pool.getConnection()
    .then(conn => {
      console.log("connected ! connection id is " + conn.threadId);
      conn.release(); //release to pool
    })
    .catch(err => {
      console.log("not connected due to error: " + err);
    });

Pool options

Pool options includes Connection option that will be used when creating new connections.

Specific options for pools are :

createPoolCluster(options) → PoolCluster

Creates a new pool cluster. Cluster handle multiple pools, giving high availability / distributing load (using round robin / random / ordered ).

Example:

const mariadb = require('mariadb');

const cluster = mariadb.createPoolCluster();
cluster.add("master", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave2", { host: 'mydb3.com', user: 'myUser', connectionLimit: 5 });

//getting a connection from slave1 or slave2 using round-robin
cluster.getConnection(/^slave*$, "RR")
  .then(conn => {
    return conn.query("SELECT 1")
       .then(row => {
           conn.end();
           return row[0]["@node"];
       })
       .finally(() => {
           conn.end();
       });
  });

PoolCluster options

Pool cluster options includes pool option documentation that will be used when creating new pools.

Specific options for pool cluster are :

Connection API

connection.query(sql[, values]) -> Promise

Sends a query to database and return result as a Promise.

For instance, when using an SQL string:

connection
  .query("SELECT NOW()")
  .then(rows => {
	console.log(rows); //[ { 'NOW()': 2018-07-02T17:06:38.000Z }, meta: [ ... ] ]
  })
  .catch(err => {
	//handle error
  });

Alternatively, you could use the JSON object:

connection
   .query({dateStrings:true, sql:'SELECT NOW()'})
   .then(rows => {
	  console.log(rows); //[ { 'NOW()': '2018-07-02 19:06:38' }, meta: [ ... ] ]
	})
	.catch(...)

Placeholder

To prevent SQL Injection attacks, queries permit the use of question marks as placeholders. The Connection escapes values according to their type. Values can be of native JavaScript types, Buffers, Readables, objects with `toSQLString` methods, or objects that can be stringified (that is, `JSON.stringfy`).

When streaming, objects that implement Readable are streamed automatically. But, there are two server system variables that may interfere:

- `net_read_timeout`: The server must receive queries before reaching this timeout, which defaults to 30 seconds. - `max_allowed_packet`: This system variable defines the maximum amount of data the Connector can send to the server.

For instance,

connection
  .query(
	 "INSERT INTO someTable VALUES (?, ?, ?)", 
	 [1,Buffer.from("c327a97374", "hex"),"mariadb"]
  )
  .then(...)
  .catch(...);
  //will send INSERT INTO someTable VALUES (1, _BINARY '.\'.st', 'mariadb')

In the case of streaming,

const https = require("https");
//3Mb page
https.get("https://node.green/#ES2018-features-Promise-prototype-finally-basic-support",
  readableStream => {
    connection.query("INSERT INTO StreamingContent (b) VALUE (?)", [readableStream]);
      .then(res => {
        //inserted
      })
      .catch(console.log);
  }
)

JSON Result-sets

Queries return two different kinds of results, depending on the type of query you execute. When you execute write statements, (such as `INSERT`, `DELETE` and `UPDATE`), the method returns a JSON object with the following properties:

• `affectedRows`: An integer listing the number of affected rows.
• `insertId`: An integer noting the auto-increment ID of the last row written to the table.
• `warningStatus`: An integer indicating whether the query ended with a warning.

connection.query('CREATE TABLE animals (' +
                       'id MEDIUMINT NOT NULL AUTO_INCREMENT,' +
                       'name VARCHAR(30) NOT NULL,' +
                       'PRIMARY KEY (id))');
connection.query('INSERT INTO animals(name) value (?)', ['sea lions'])
    .then(res => {
      console.log(res); 
      //log : { affectedRows: 1, insertId: 1, warningStatus: 0 }
    })
    .catch(...);

Array Result-sets

When the query executes a `SELECT` statement, the method returns the result-set as an array. Each value in the array is a returned row as a JSON object. Additionally, the method returns a special `meta` array that contains the column metadata information.

The rows default to JSON objects, but two other formats are also possible with the `nestTables` and `rowsAsArray` options.

connection.query('select * from animals')
    .then(res => {
      console.log(res); 
      // [ 
      //    { id: 1, name: 'sea lions' }, 
      //    { id: 2, name: 'bird' }, 
      //    meta: [ ... ]
      // ]
    });

Query options

• `namedPlaceholders`
• `typeCast`
• `rowsAsArray`
• `nestTables`
• `dateStrings`
• `supportBigNumbers`
• `bigNumberStrings`²

Those options can be set on the query level, but are usually set at the connection level, and will then apply to all queries.

namedPlaceholders

While the recommended method is to use the question mark placeholder, you can alternatively allow named placeholders by setting this query option. Values given in the query must contain keys corresponding to the placeholder names.

connection
  .query(
	{ namedPlaceholders: true, sql: "INSERT INTO someTable VALUES (:id, :img, :db)" },
	{ id: 1, img: Buffer.from("c327a97374", "hex"), db: "mariadb" }
  )
  .then(...)
  .catch(...);

rowsAsArray

Using this option causes the Connector to format rows in the result-set as arrays, rather than JSON objects. Doing so allows you to save memory and avoid having the Connector parse column metadata completely. It is the fastest row format, (by 5-10%), with a local database.

Default format : `{ id: 1, name: 'sea lions' }` with option `rowsAsArray` : `[ 1, 'sea lions' ]`

connection.query({ rowsAsArray: true, sql: 'select * from animals' })
    .then(res => {
      console.log(res); 
      // [ 
      //    [ 1, 'sea lions' ], 
      //    [ 2, 'bird' ],
      //    meta: [...]
      // ]
    });

nestTables

Occasionally, you may have issue with queries that return columns with the same name. The standard JSON format does not permit key duplication. To get around this, you can set the `nestTables` option to `true`. This causes the Connector to group data by table. When using string parameters, it prefixes the JSON field name with the table name and the `nestTables` value.

For instance, when using a boolean value:

connection.query({nestTables:true, 
                sql:'select a.name, a.id, b.name from animals a, animals b where b.id=1'})
    .then(res => {
      console.log(res); 
      //[ 
      //  { 
      //     a: { name: 'sea lions', id: 1 }, 
      //     b: { name: 'sea lions' } 
      //  },
      //  { 
      //     a: { name: 'bird', id: 2 }, 
      //     b: { name: 'sea lions' } 
      //  },
      //  meta: [...]
      //]
    });

Alternatively, using a string value:

connection.query({nestTables: '_', 
                sql:'select a.name, a.id, b.name from animals a, animals b where b.id=1'})
    .then(res => {
      console.log(res); 
      //[ 
      //  { a_name: 'sea lions', a_id: 1, b_name: 'sea lions' }, 
      //  { a_name: 'bird', a_id: 2, b_name: 'sea lions' },
      //  meta: [...]
      //]
    });

dateStrings

Whether you want the Connector to retrieve date values as strings, rather than `Date` objects.

supportBigNumbers

Whether the query should return integers as `Long` objects when they are not in the safe range.

bigNumberStrings

Whether the query should return integers as strings when they are not in the safe range.

typeCast

• Experimental*

In the event that you need certain values returned as a different type, you can use this function to cast the value into that type yourself.

For instance, casting all `TINYINT(1)` values as boolean values:

const tinyToBoolean = (column, next) => {
  if (column.type == "TINY" && column.length === 1) {
    const val = column.int();
    return val === null ? null : val === 1;
  }
  return next();
};
connection.query({typeCast: tinyToBoolean, sql:"..."});

Column Metadata

• `collation`: Object indicates the column collation. It has the properties: `index`, `name`, `encoding`, and `maxlen`. For instance, `33, "UTF8_GENERAL_CI", "utf8", 3`
• `columnLength`: Shows the column's maximum length if there's a limit and `0` if there is no limit, (such as with a `BLOB` column).
• `type`: Shows the column type as an integer value. For more information on the relevant values, see `field-type.js`
• `columnType`: Shows the column type as a string value. For more information on the relevant values, see `field-type.js`
• `scale`: Provides the decimal part length.
• `flags`: Shows the byte-encoded flags. For more information, see `field-detail.js`.
• `db()`: Name of the database schema. You can also retrieve this using `schema()`.
• `table()`: Table alias.
• `orgTable()`: Real table name.
• `name()`: Column alias.
• `orgName()`: Real column name.

connection
  .query("SELECT 1, 'a'")
  .then(rows => {
	console.log(rows);
	// [ 
	//   { '1': 1, a: 'a' },
	//   meta: [ 
	//     { 
	//       collation: [Object],
	//       columnLength: 1,
	//       columnType: 8,
	//       scale: 0,
	//       type: 'LONGLONG',
	//       flags: 129,
	//       db: [Function],
	//       schema: [Function],
	//       table: [Function],
	//       orgTable: [Function],
	//       name: [Function],
	//       orgName: [Function] 
	//     },
	//     { 
	//       collation: [Object],
	//       columnLength: 4,
	//       columnType: 253,
	//       scale: 39,
	//       type: 'VAR_STRING',
	//       flags: 1,
	//       db: [Function],
	//       schema: [Function],
	//       table: [Function],
	//       orgTable: [Function],
	//       name: [Function],
	//       orgName: [Function] 
	//     } 
	//   ] 
	// ]
	assert.equal(rows.length, 1);
  })

connection.queryStream(sql[, values]) → Emitter

When using the `query()` method, documented above, the Connector returns the entire result-set with all its data in a single call. While this is fine for queries that return small result-sets, it can grow unmanageable in cases of huge result-sets. Instead of retrieving all of the data into memory, you can use the `queryStream()` method, which uses the event drive architecture to process rows one by one, which allows you to avoid putting too much strain on memory.

Query times and result handlers take the same amount of time, but you may want to consider updating the `net_read_timeout` server system variable. The query must be totally received before this timeout, which defaults to 30 seconds.

There is 2 differents methods to implement streaming:

• Events

connection.queryStream("SELECT * FROM mysql.user")
      .on("error", err => {
        console.log(err); //if error
      })
      .on("fields", meta => {
        console.log(meta); // [ ...]
      })
      .on("data", row => {
        console.log(row);
      })
      .on("end", () => {
        //ended
      });

• streams

Note that queryStream produced Object data, so Transform/Writable implementation must be created with `objectMode` set to true.<br/> (example use `stream.pipeline` only available since Node.js 10)

const stream = require("stream");
const fs = require("fs");

//...create connection...

const someWriterStream = fs.createWriteStream("./someFile.txt");

const transformStream = new stream.Transform({
  objectMode: true,
  transform: function transformer(chunk, encoding, callback) {
    callback(null, JSON.stringify(chunk));
  }
});

const queryStream = connection.queryStream("SELECT * FROM mysql.user");

stream.pipeline(queryStream, transformStream, someWriterStream);

connection.batch(sql, values) → Promise

Implementation depend of server type and version. for MariaDB server version 10.2.7+, implementation use dedicated bulk protocol.

For other, insert queries will be rewritten for optimization. example: insert into ab (i) values (?) with first batch values = 1, second = 2 will be rewritten insert into ab (i) values (1), (2).

If query cannot be re-writen will execute a query for each values.

result difference compared to execute multiple single query insert is that only first generated insert id will be returned.

For instance,

  connection.query(
    "CREATE TEMPORARY TABLE batchExample(id int, id2 int, id3 int, t varchar(128), id4 int)"
  );
  connection
    .batch("INSERT INTO `batchExample` values (1, ?, 2, ?, 3)", [[1, "john"], [2, "jack"]])
    .then(res => {
      console.log(res.affectedRows); // 2
    });

connection.beginTransaction() → Promise

Begins a new transaction.

connection.commit() → Promise

Commits the current transaction, if there is one active. The Connector tracks the current transaction state on the server. In the event that you issue the `commit()` method when there's no active transaction, it ignores the method and sends no commands to MariaDB.

connection.rollback() → Promise

Rolls back the current transaction, if there is one active. The Connector tracks the current transaction state on the server. In the event that you issue the `rollback()` method when there's no active transaction, it ignores the method and sends no commands to MariaDB.

conn.beginTransaction()
  .then(() => {
    conn.query("INSERT INTO testTransaction values ('test')");
    return conn.query("INSERT INTO testTransaction values ('test2')");
  })
  .then(() => {
    conn.commit();
  })
  .catch((err) => {
    conn.rollback();
  })

connection.changeUser(options) → Promise

Resets the connection and re-authorizes it using the given credentials. It is the equivalent of creating a new connection with a new user, reusing the open socket.

conn.changeUser({user: 'changeUser', password: 'mypassword'})
   .then(() => {
      //connection user is now changed. 
   })
   .catch(err => {
      //error
   });

connection.ping() → Promise

Sends a packet to the database containing one byte to check that the connection is still active.

conn.ping()
  .then(() => {
    //connection is valid
  })
  .catch(err => {
    //connection is closed
  })

connection.reset() → Promise

reset the connection. Reset will:

• rollback any open transaction
• reset transaction isolation level
• reset session variables
• delete user variables
• remove temporary tables
• remove all PREPARE statement

connection.isValid() → boolean

Indicates the connection state as the Connector knows it. If it returns false, there is an issue with the connection, such as the socket disconnected without the Connector knowing about it.

connection.end() → Promise

Closes the connection gracefully, after waiting for any currently executing queries to finish.

conn.end()
  .then(() => {
    //connection has ended properly
  })
  .catch(err => {
    //connection was closed but not due of current end command
  })

connection.destroy()

Closes the connection without waiting for any currently executing queries. These queries are interrupted. MariaDB logs the event as an unexpected socket close.

conn.query(
  "select * from information_schema.columns as c1, " +
   "information_schema.tables, information_schema.tables as t2"
)
.then(rows => {
  //won't occur
})
.catch(err => {
  console.log(err);
  //Error: Connection destroyed, command was killed
  //    ...
  //  fatal: true,
  //  errno: 45004,
  //  sqlState: '08S01',
  //  code: 'ER_CMD_NOT_EXECUTED_DESTROYED' 
  done();
});
conn.destroy(); //will immediately close the connection, even if query above would have take a minute

connection.pause()

Pauses data reads.

connection.resume()

Resumes data reads from a pause.

connection.serverVersion()

Retrieves the version of the currently connected server. Throws an error when not connected to a server.

  console.log(connection.serverVersion()); //10.2.14-MariaDB

Error

When the Connector encounters an error, Promise returns an `Error` object. In addition to the standard properties, this object has the following properties:

• `fatal`: A boolean value indicating whether the connection remains valid.
• `errno`: The error number.
• `sqlState`: The SQL state code.
• `code`: The error code.

Example on `console.log(error)`:

{ Error: (conn=116, no: 1146, SQLState: 42S02) Table 'testn.falsetable' doesn't exist
  sql: INSERT INTO falseTable(t1, t2, t3, t4, t5) values (?, ?, ?, ?, ?)  - parameters:[1,0x01ff,'hh','01/01/2001 00:00:00.000',null]
      ...
      at Socket.Readable.push (_stream_readable.js:134:10)
      at TCP.onread (net.js:559:20)
    From event:
      at C:\mariadb-connector-nodejs\lib\connection.js:185:29
      at Connection.query (C:\mariadb-connector-nodejs\lib\connection.js:183:12)
      at Context.<anonymous> (C:\mariadb-connector-nodejs\test\integration\test-error.js:250:8)
    fatal: false,
    errno: 1146,
    sqlState: '42S02',
    code: 'ER_NO_SUCH_TABLE' } }

Errors contain an error stack, query and parameter values (the length of which is limited to 1,024 characters, by default). To retrieve the initial stack trace (shown as `From event...` in the example above), you must have the Connection option `trace` enabled.

For more information on error numbers and SQL state signification, see the MariaDB Error Code documentation.

events

Connection object that inherits from the Node.js `EventEmitter`. Emits an error event when the connection closes unexpectedly.

const mariadb = require('mariadb');
mariadb.createConnection({user: 'root', password: 'myPwd', host: 'localhost', socketTimeout: 100})
.then(conn => {
conn.on('error', err => {
  //will be executed after 100ms due to inactivity, socket has closed. 
  console.log(err);
  //log : 
  //{ Error: (conn=6283, no: 45026, SQLState: 08S01) socket timeout
  //    ...
  //    at Socket.emit (events.js:208:7)
  //    at Socket._onTimeout (net.js:410:8)
  //    at ontimeout (timers.js:498:11)
  //    at tryOnTimeout (timers.js:323:5)
  //    at Timer.listOnTimeout (timers.js:290:5)
  //  fatal: true,
  //  errno: 45026,
  //  sqlState: '08S01',
  //  code: 'ER_SOCKET_TIMEOUT' }
});
})
.catch(done);

Pool API

Each time a connection is asked, if the pool contains a connection that is not used, the pool will validate the connection, exchanging an empty MySQL packet with the server to ensure the connection state, then give the connection. The pool reuses connection intensively, so this validation is done only if a connection has not been used for a period (specified by the "minDelayValidation" option with the default value of 500ms).

If no connection is available, the request for a connection will be put in a queue until connection timeout. When a connection is available (new creation or released to the pool), it will be used to satisfy queued requests in FIFO order.

When a connection is given back to the pool, any remaining transactions will be rolled back.

pool.getConnection() → Promise

> Returns a promise that :

• resolves with a Connection object,
• raises an Error. <</style>>

Creates a new Connection object with an additional release method. Calling connection.release() will give back connection to pool.

Connection must be given back to pool using this connection.release() method.

Example:

const mariadb = require('mariadb');
const pool = mariadb.createPool({ host: 'mydb.com', user:'myUser' });
pool.getConnection()
    .then(conn => {
      console.log("connected ! connection id is " + conn.threadId);
      conn.release(); //release to pool
    })
    .catch(err => {
      console.log("not connected due to error: " + err);
    });

pool.query(sql[, values]) -> Promise

This is a shortcut to get a connection from pool, execute a query and release connection.

const mariadb = require('mariadb');
const pool = mariadb.createPool({ host: 'mydb.com', user:'myUser' });
pool
   .query("SELECT NOW()")
   .then(rows => {
    console.log(rows); //[ { 'NOW()': 2018-07-02T17:06:38.000Z }, meta: [ ... ] ]
   })
   .catch(err => {
    //handle error
   });

pool.batch(sql, values) -> Promise

This is a shortcut to get a connection from pool, execute a batch and release connection.

const mariadb = require('mariadb');
const pool = mariadb.createPool({ host: 'mydb.com', user:'myUser' });
pool.query(
  "CREATE TABLE parse(autoId int not null primary key auto_increment, c1 int, c2 int, c3 int, c4 varchar(128), c5 int)"
);
pool
  .batch("INSERT INTO `parse`(c1,c2,c3,c4,c5) values (1, ?, 2, ?, 3)", 
    [[1, "john"], [2, "jack"]])
  .then(res => {
    //res = { affectedRows: 2, insertId: 1, warningStatus: 0 }

    assert.equal(res.affectedRows, 2);
    pool
      .query("select * from `parse`")
      .then(res => {
        /*
        res = [ 
            { autoId: 1, c1: 1, c2: 1, c3: 2, c4: 'john', c5: 3 },
            { autoId: 2, c1: 1, c2: 2, c3: 2, c4: 'jack', c5: 3 },
            meta: ...
          }
        */ 
      })
      .catch(done);
  });

pool.end() → Promise

Closes the pool and underlying connections gracefully.

pool.end()
  .then(() => {
    //connections have been ended properly
  })
  .catch(err => {});

Pool events

Example:

pool.on('connection', (conn) => console.log(`connection ${conn.threadId} has been created in pool`);

Pool cluster API

Cluster handle multiple pools according to patterns and handle failover / distributed load (round robin / random / ordered ).

poolCluster.add(id, config)

Add a new Pool to cluster.

Example:

const mariadb = require('mariadb');
const cluster = mariadb.createPoolCluster();
cluster.add("master", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave2", { host: 'mydb3.com', user: 'myUser', connectionLimit: 5 });

poolCluster.remove(pattern)

remove and end pool(s) configured in cluster.

poolCluster.end() → Promise

Closes the pool cluster and underlying pools.

poolCluster.end()
  .then(() => {
    //pools have been ended properly
  })
  .catch(err => {});

poolCluster.getConnection(pattern, selector) → Promise

Creates a new Connection object. Connection must be given back to pool with the connection.end() method.

Example:

const mariadb = require('mariadb');
const cluster = mariadb.createPoolCluster();
cluster.add("master", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave2", { host: 'mydb3.com', user: 'myUser', connectionLimit: 5 });
cluster.getConnection("slave*")

poolCluster events

PoolCluster object inherits from the Node.js `EventEmitter`. Emits 'remove' event when a node is removed from configuration if the option `removeNodeErrorCount` is defined (default to 5) and connector fails to connect more than `removeNodeErrorCount` times. (if other nodes are present, each attemps will wait for value of the option `restoreNodeTimeout`)

const mariadb = require('mariadb');
const cluster = mariadb.createPoolCluster({ removeNodeErrorCount: 20, restoreNodeTimeout: 5000 });
cluster.add("master", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave2", { host: 'mydb3.com', user: 'myUser', connectionLimit: 5 });*
cluster.on('remove', node => {
  console.log(`node ${node} was removed`);
})

poolCluster.of(pattern, selector) → FilteredPoolCluster

Creates a new filtered pool cluster object that is a subset of cluster.

Example:

const mariadb = require('mariadb');

const cluster = mariadb.createPoolCluster();
cluster.add("master-north", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("master-south", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1-north", { host: 'mydb3.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave2-north", { host: 'mydb4.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1-south", { host: 'mydb5.com', user: 'myUser', connectionLimit: 5 });

const masterCluster = cluster.of('master*');
const northSlaves = cluster.of(/^slave?-north/, 'RANDOM');
northSlaves.getConnection()
  .then(conn => {
    //use that connection
  })

filtered pool cluster

• `filteredPoolCluster.getConnection() → Promise` : Creates a new connection from pools that corresponds to pattern .
• `filteredPoolCluster.query(sql[, values]) → Promise` : this is a shortcut to get a connection from pools that corresponds to pattern, execute a query and release connection.