Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore MariaDB Connector/Node.js, the official client library for Node.js. Connect applications to MariaDB/MySQL databases, leverage Promise/Callback APIs for efficient data access.
connection.beginTransaction();
connection.query("INSERT INTO BASKET(customerId) values (?)", [1], (err, res) => {
//must handle error if any
const basketId = res.insertId;
try {
connection.query("INSERT INTO basket_item(basketId, itemId) VALUES (?, ?)", [basketId, 100]);
connection.query("INSERT INTO basket_item(basketId, itemId) VALUES (?, ?)", [basketId, 101]);
connection.query("INSERT INTO basket_item(basketId, itemId) VALUES (?, ?)", [basketId, 102], (err) => {
//must handle error if any
connection.commit();
});
} catch (err) {
connection.rollback();
//handle error
}
});npm install mariadbconnection.beginTransaction();
connection.query("INSERT INTO BASKET(customerId) values (?)", [1], (err, res) => {
//must handle error if any
const basketId = res.insertId;
try {
connection.batch("INSERT INTO basket_item(basketId, itemId) VALUES (?, ?)",[
[basketId, 100],
[basketId, 101],
[basketId, 103],
[basketId, 104],
[basketId, 105]
]);
//must handle error if any
connection.commit();
} catch (err) {
connection.rollback();
//handle error
}
});
Complete Node.js connector guide for MariaDB. Complete reference for installation, connection pooling, query execution, and error handling for production use.
npm install mariadbconst mariadb = require('mariadb');
const pool = mariadb.createPool({
host: 'mydb.com',
user:'myUser',
password: 'myPassword',
connectionLimit: 5
});
pool.getConnection()
.then(conn => {
conn.query("SELECT 1 as val")
.then((rows) => {
console.log(rows); //[ {val: 1}, meta: ... ]
//Table must have been created before
// " CREATE TABLE myTable (id int, val varchar(255)) "
return conn.query("INSERT INTO myTable value (?, ?)", [1, "mariadb"]);
})
.then((res) => {
console.log(res); // { affectedRows: 1, insertId: 1, warningStatus: 0 }
conn.end();
pool.end();
})
.catch(err => {
//handle error
console.log(err);
conn.end();
pool.end();
})
}).catch(err => {
//not connected
pool.end();
});const mariadb = require('mariadb');
const pool = mariadb.createPool({
host: 'mydb.com',
user:'myUser',
password: 'myPassword',
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) conn.end();
}
}
asyncFunction().then(() => {
pool.end();
});npm install mariasqlconst mariadb = require('mariadb');
//passing argument as JSON object
mariadb.createConnection({
user: 'root',
password: 'pass',
port: 3307,
database: 'db',
metaAsArray: false,
ssl: true,
dateStrings: true
});
//passing argument as String
mariadb.createConnection('mariadb://root:pass@localhost:3307/db?metaAsArray=false&ssl=true&dateStrings=true');QUERY: insert into bigParameterInt8 values(?, ?) - parameters:['0000000...]
==> conn:57 Query(0,1031)
+--------------------------------------------------+
| 0 1 2 3 4 5 6 7 8 9 a b c d e f |
+--------------------------------------------------+------------------+
| 03 04 00 00 03 69 6E 73 65 72 74 20 69 6E 74 6F | .....insert into |
| 20 62 69 67 50 61 72 61 6D 65 74 65 72 49 6E 74 | bigParameterInt |
| 38 20 76 61 6C 75 65 73 28 27 30 30 30 30 30 30 | 8 values('000000 |
| 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | 0000000000000000 |...
+--------------------------------------------------+------------------+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: {
network: (msg) => logger.silly(msg),
query: (msg) => logger.info(msg),
error: (err) => logger.error(err),
}
});SHOW VARIABLES LIKE 'have_ssl';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_ssl | DISABLED |
+---------------+----------+CREATE USER 'johnSmith'@'%' IDENTIFIED BY PASSWORD('passwd');
GRANT ALL ON company.* TO 'johnSmith'@'%' REQUIRE SSL;const mariadb = require('mariadb');
mariadb
.createConnection({
host: 'myHost.com',
ssl: true,
user: 'myUser',
password:'MyPwd',
database:'db_name'
}).then(conn => {})//connecting
mariadb
.createConnection({
host: 'myHost.com',
ssl: {
rejectUnauthorized: false
},
user: 'myUser',
password:'MyPwd',
}).then(conn => {})const fs = require("fs");
const mariadb = require('mariadb');
//reading certificates from file
const serverCert = [fs.readFileSync("server.pem", "utf8")];
//connecting
mariadb
.createConnection({
user: "myUser",
host: "myHost.com",
ssl: {
ca: serverCert
}
}).then(conn => {})//connecting
mariadb
.createConnection({
user:"myUser",
host: "myHost.com",
ssl: {
ca: serverCert,
secureProtocol: "TLSv1_2_method",
ciphers:
"ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256"
}
}).then(conn => {})GRANT ALL ON company.* TO 'johnSmith'@'%' REQUIRE X509;SELECT ssl_type, ssl_cipher, x509_subject
FROM mysql.user
WHERE USER = 'johnSmith';CREATE USER 'X509testUser'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'X509testUser'@'%' REQUIRE X509;const fs = require("fs");
const mariadb = require('mariadb');
//reading certificates from file
const serverCert = [fs.readFileSync("server.pem", "utf8")];
const clientKey = [fs.readFileSync("client.key", "utf8")];
const clientCert = [fs.readFileSync("client.pem", "utf8")];
//connecting
mariadb
.createConnection({
user:"X509testUser",
host: "mariadb.example.com",
ssl: {
ca: serverCert,
cert: clientCert,
key: clientKey
}
}).then(conn => {})openssl pkcs12 \
-export \
-in "${clientCertFile}" \
-inkey "${clientKeyFile}" \
-out "${keystoreFile}" \
-name "mariadbAlias" \
-passout pass:kspassconst fs = require("fs");
const mariadb = require('mariadb');
//reading certificates from file (keystore must be read as binary)
const serverCert = fs.readFileSync("server.pem", "utf8");
const clientKeystore = fs.readFileSync("keystore.p12");
//connecting
mariadb.createConnection({
user:"X509testUser",
host: "mariadb.example.com",
ssl: {
ca: serverCert,
pfx: clientKeystore,
passphrase: "kspass"
}
}).then(conn => {});const conn = await mariadb.createConnection({
charset: 'utf8mb4',
collation: 'uca1400_ai_ci'
//...
});const conn = await mariadb.createConnection({
user: 'myUser',
password: 'mYpwd',
port: 27000,
stream: (cb) => {
const tunnel = require('tunnel-ssh');
tunnel(
{
// remote connection ssh info
username: 'root',
host: '157.230.123.7',
port: 22,
privateKey: fs.readFileSync('./pop_key.ppk'),
// database (here on ssh server)
dstHost: '127.0.0.1',
dstPort: 3306,
// local interface
localHost: '127.0.0.1',
localPort: 27000
},
cb
);
}
});Hostname/IP doesn't match certificate's altnames: "Host: other.example.com. is not cert's CN: mariadb.example.com"createConnection(options) → ConnectioncreatePool(options) → PoolcreatePoolCluster(options) → PoolClusterimportFile(options[, callback])version → StringdefaultOptions(options) → Jsonconnection.query(sql[, values][, callback]) -> Emitterconnection.batch(sql, values [, callback])connection.beginTransaction([callback])connection.commit([callback])connection.rollback([callback])connection.changeUser(options[, callback])connection.ping([callback])connection.end([callback])connection.reset([callback])connection.isValid() → booleanconnection.destroy()connection.escape(value) → Stringconnection.escapeId(value) → Stringconnection.pause()connection.resume()connection.serverVersion()connection.importFile(options[, callback])Erroreventspool.getConnection(callback)pool.query(sql[, values][, callback])pool.batch(sql, values[, callback])pool.end([callback])pool.escape(value) → Stringpool.escapeId(value) → Stringpool.importFile(options[, callback])poolCluster.add(id, config)poolCluster.remove(pattern)poolCluster.end([callback])poolCluster.getConnection([pattern, ][selector, ]callback)poolCluster eventspoolCluster.of(pattern, selector) → FilteredPoolClusterfiltered pool clusterExplore Node.js connectors beyond the official MariaDB Connector/Node.js. This section covers alternative drivers, ORMs, and methods to connect your Node.js applications to MariaDB.
importFile(options [, callback]) : import Sql fileconnection.commit([callback]): Commit the current transaction, if any.pool.end([callback]): Gracefully closes the connection.poolCluster.getConnection([pattern, ][selector, ]callback) : return a connection from cluster.$ npm install mariadb const mariadb = require('mariadb/callback');
const conn = mariadb.createConnection({host: 'mydb.com', user:'myUser', password: 'myPwd'});
conn.query("SELECT 1 as val", (err, rows) => {
console.log(rows); //[ {val: 1}, meta: ... ]
conn.query("INSERT INTO myTable value (?, ?)", [1, "mariadb"], (err, res) => {
console.log(res); // { affectedRows: 1, insertId: 1, warningStatus: 0 }
conn.end();
});
});$ npm install mariadbconst mariadb = require('mariadb/callback'); conn.query('call ??(?)', [myProc, 'myVal'], (err, res) => {}); Conn.query(`call ${conn.escapeId(myProc)}(?)`, ['myVal'], (err, res) => {});const mariadb = require('mariadb');
const conn = mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD,
timezone: 'America/Los_Angeles',
skipSetTimezone: true
}); const mariadb = require('mariadb');
const conn = mariadb.createConnection({host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PWD});$ DB_HOST=localhost DB_USER=test DB_PASSWORD=secretPasswrd node my-app.js$ npm install dotenvconst mariadb = require('mariadb');
require('dotenv').config()
const conn = await mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD
});DB_HOST=localhost
DB_USER=test
DB_PWD=secretPasswrdconst mariadb = require('mariadb');
const conn = await mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD
});const mariadb = require('mariadb/callback');
const conn = mariadb.createConnection({
host: 'mydb.com',
user:'myUser',
password: 'myPwd'
});
conn.connect(err => {
if (err) {
console.log("not connected due to error: " + err);
} else {
console.log("connected ! connection id is " + conn.threadId);
}
});SHOW VARIABLES LIKE 'socket';const mariadb = require('mariadb/callback');
const conn = mariadb.createConnection({ socketPath: '/tmp/mysql.sock', user: 'root' });
conn.connect(err => {
//do something with connection
conn.end();
});
const mariadb = require('mariadb/callback');
const conn = mariadb.createConnection({ socketPath: '\\\\.\\pipe\\MySQL', user: 'root' });const mariadb = require('mariadb/callback');
const pool = mariadb.createPool({ host: 'mydb.com', user: 'myUser', connectionLimit: 5 });
pool.getConnection((err, conn) => {
if (err) {
console.log("not connected due to error: " + err);
} else {
console.log("connected ! connection id is " + conn.threadId);
conn.end(); //release to pool
}
});pool.on('connection', (conn) => console.log(`connection ${conn.threadId} has been created in pool`));const mariadb = require('mariadb/callback');
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", (err, conn) => {
conn.query("SELECT 1", (err, rows) => {
conn.end();
return row[0]["@node"];
});
}); mariadb.importFile({ host: 'localhost', user: 'root', file: '/tmp/tools/data-dump.sql'}, (err) => {
if (err) console.log(err);
});const mariadb = require('mariadb');
console.log(mariadb.defaultOptions({ timezone: '+00:00' }));
/*
{
host: 'localhost',
port: 3306,
user: 'root',
password: undefined,
database: undefined,
collation: Collation { index: 224, name: 'UTF8MB4_UNICODE_CI', charset: 'utf8' },
timezone: '+00:00',
...
}
*/ connection.query("SELECT NOW()", (err, rows, meta) => {
if (err) throw err;
console.log(rows); //[ { 'now()': 2018-07-02T17:06:38.000Z } ]
});connection.query({dateStrings:true, sql:'SELECT now()'}, (err, rows, meta) => {
if (err) throw err;
console.log(rows); //[ { 'now()': '2018-07-02 19:06:38' } ]
});// Sends INSERT INTO someTable VALUES (1, _BINARY '.\'.st', 'mariadb')
connection.query(
"INSERT INTO someTable VALUES (?, ?, ?)",
[1, Buffer.from("c327a97374", "hex"), "mariadb"],
(err, result) => {
if (err) throw err;
console.log(result);
//log : { affectedRows: 1, insertId: 1, warningStatus: 0 }
}
);const https = require("https");
https.get("https://node.green/#ES2018-features-Promise-prototype-finally-basic-support",
readableStream => {
connection.query("INSERT INTO StreamingContent (b) VALUE (?)", [readableStream], (err, res) => {
if (err) throw err;
//inserted
});
}
)connection.query(
"CREATE TABLE animals (" +
"id MEDIUMINT NOT NULL AUTO_INCREMENT," +
"name VARCHAR(30) NOT NULL," +
"PRIMARY KEY (id))",
err => {
connection.query("INSERT INTO animals(name) value (?)", ["sea lions"], (err, res) => {
if (err) throw err;
console.log(res);
//log : { affectedRows: 1, insertId: 1, warningStatus: 0 }
});
}
);connection.query('select * from animals', (err, res, meta) => {
console.log(res);
// [
// { id: 1, name: 'sea lions' },
// { id: 2, name: 'bird' },
// meta: [ ... ]
// ]
});connection.query("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
});const logRes = new Writable({
objectMode: true,
decodeStrings: false,
write: (row, encoding, callback) => {
console.log(row);
callback();
}
});
connection.query("SELECT * FROM mysql.user")
.stream()
.pipe(logRes); 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"]], (err, res) => {
if (err) {
console.log('handle error');
} else {
console.log(res.affectedRows); // 2
}
});
conn.beginTransaction(err => {
if (err) {
//handle error
} else {
conn.query("INSERT INTO testTransaction values ('test')", (err) => {
if (err) {
//handle error
} else {
conn.query("INSERT INTO testTransaction values ('test2')", (err) => {
if (err) {
conn.rollback(err => {
if (err) {
//handle error
}
});
} else {
conn.commit(err => {
if (err) {
//handle error
}
});
}
});
}
})
}
});conn.changeUser({user: 'changeUser', password: 'mypassword'}, err => {
if (err) {
//handle error
} else {
//connection user is now changed.
}
});conn.ping(err => {
if (err) {
//handle error
} else {
//connection is valid
}
})conn.end(err => {
//handle error
})const myColVar = "let'go";
const myTable = "table:a"
const cmd = 'SELECT * FROM ' + conn.escapeId(myTable) + ' where myCol = ' + conn.escape(myColVar);
// cmd value will be:
// "SELECT * FROM `table:a` where myCol = 'let\\'s go'"const myColVar = "let'go";
const myTable = "table:a"
const cmd = 'SELECT * FROM ' + conn.escapeId(myTable) + ' where myCol = ' + conn.escape(myColVar);
// cmd value will be:
// "SELECT * FROM `table:a` where myCol = 'let\\'s go'"
// using template literals:
con.query(`SELECT * FROM ${con.escapeId(myTable)} where myCol = ?`, [myColVar], (err, rows) => { }); console.log(connection.serverVersion()); //10.2.14-MariaDB await conn.importFile({ file: '/tmp/someFile.sql', database: 'myDb'}, (err) => {
if (err) {
console.log(err);
}
});{ 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' } } const conn = mariadb.createConnection({user: 'root', password: 'myPwd', host: 'localhost', socketTimeout: 100})
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' }
});const mariadb = require('mariadb/callback');
const pool = mariadb.createPool({ host: 'mydb.com', user:'myUser' });
pool.getConnection((err, conn => {
if (err) {
console.log("not connected due to error: " + err);
} else {
console.log("connected ! connection id is " + conn.threadId);
conn.end(); //release to pool
}
}));const mariadb = require('mariadb/callback');
const pool = mariadb.createPool({ host: 'mydb.com', user:'myUser' });
pool.query("SELECT NOW()", (err, results, metadata) => {
if (err) {
//handle error
} else {
console.log(rows); //[ { 'NOW()': 2018-07-02T17:06:38.000Z }, meta: [ ... ] ]
}
});const mariadb = require('mariadb/callback');
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"]],
(err, res) => {
if (err) {
//handle error
} else {
//res = { affectedRows: 2, insertId: 1, warningStatus: 0 }
assert.equal(res.affectedRows, 2);
pool.query("select * from `parse`", (err, 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: ...
}
*/
});
}
});pool.end(err => {
if (err) {
//handle error
console.log(err);
} else {
//connections have been ended properly
}
}); pool.importFile({ file: '/tmp/someFile.sql', database: 'myDb'}, (err) => {
if (err) console.log(err);
});pool.on('connection', (conn) => console.log(`connection ${conn.threadId} has been created in pool`));const mariadb = require('mariadb/callback');
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(err => {
if (err) {
//handle error
console.log(err);
} else {
//pools have been ended properly
}
});const mariadb = require('mariadb/callback');
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*", (err, conn) => {
//use connection and handle possible error
})const mariadb = require('mariadb/callback');
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`);
})const mariadb = require('mariadb/callback')
const cluster = mariadb.createPoolCluster();
cluster.add("master-north", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("master-south", { host: 'mydb1.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1-north", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave2-north", { host: 'mydb3.com', user: 'myUser', connectionLimit: 5 });
cluster.add("slave1-south", { host: 'mydb2.com', user: 'myUser', connectionLimit: 5 });
const masterCluster = cluster.of('master*');
const northSlaves = cluster.of(/^slave?-north/, 'RANDOM');
northSlaves.getConnection((err, conn) => {
//use that connection
});createConnection(options) → PromisecreatePool(options) → PoolcreatePoolCluster(options) → PoolClusterimportFile(options) → Promiseversion → StringdefaultOptions(options) → Jsonconnection.query(sql[, values]) -> Promiseconnection.queryStream(sql[, values]) → Emitterconnection.prepare(sql) → Promiseconnection.execute(sql[, values]) → Promiseconnection.batch(sql, values) → Promiseconnection.beginTransaction() → Promiseconnection.commit() → Promiseconnection.release() → Promiseconnection.rollback() → Promiseconnection.changeUser(options) → Promiseconnection.ping() → Promiseconnection.reset() → Promiseconnection.isValid() → booleanconnection.end() → Promiseconnection.destroy()connection.escape(value) → Stringconnection.escapeId(value) → Stringconnection.pause()connection.resume()connection.serverVersion()connection.importFile(options) → PromiseErroreventspool.getConnection() → Promisepool.query(sql[, values]) → Promisepool.batch(sql, values) → Promisepool.end() → Promisepool.escape(value) → Stringpool.escapeId(value) → Stringpool.importFile(options) → PromisecreatePoolCluster(options) → PoolClusterpoolCluster.add(id, config)poolCluster.remove(pattern)poolCluster.getConnection([pattern], [selector]) → PromisepoolCluster.of(pattern, [selector]) → FilteredPoolClusterpoolCluster.end() → Promiseawait usingawait usingimportFile(options) → Promise: Import Sql fileconnection.execute(sql [, values]) → Promise: Prepare and Executes a query.pool.end() → Promise: Gracefully closes the connection.poolCluster.getConnection(pattern, selector) → Promise: Return a connection from cluster.0BLOB'ORDER'const pool = mariadb.createPool({
// ...connection options
connectionLimit: 10,
leakDetectionTimeout: 30000 // Log potential leaks after 30 seconds
});let conn;
try {
conn = await pool.getConnection();
// Use connection...
} catch (err) {
// Handle error...
} finally {
if (conn) conn.release(); // Always release in finally block
}const pool = mariadb.createPool({
// ...connection options
minDelayValidation: 500, // Only validate connections unused for 500ms
pingTimeout: 1000 // Timeout for ping validation
});const cluster = mariadb.createPoolCluster({
removeNodeErrorCount: 5, // Remove after 5 consecutive failures
restoreNodeTimeout: 10000, // Wait 10 seconds before retrying failed nodes
canRetry: true // Enable retry on different nodes
});// Track cluster health
let clusterHealth = {
removedNodes: [],
lastIncident: null
};
cluster.on('remove', (nodeId) => {
clusterHealth.removedNodes.push(nodeId);
clusterHealth.lastIncident = new Date();
// Alert operations team
alertOps(`Database node ${nodeId} removed from cluster at ${clusterHealth.lastIncident}`);
});$ npm install mariadbconst mariadb = require('mariadb');
async function asyncFunction() {
const conn = await mariadb.createConnection({
host: 'mydb.com',
user: 'myUser',
password: 'myPwd'
});
try {
const res = await conn.query('select 1');
console.log(res); // [{ "1": 1 }]
return res;
} finally {
conn.end();
}
}
asyncFunction();$ npm install mariadbconst mariadb = require('mariadb'); const res = await conn.query('call ??(?)', [myProc, 'myVal']); const res = await conn.query(`call ${conn.escapeId(myProc)}(?)`, ['myVal']);const pool = mariadb.createPool({
host: 'mydb.com',
user: 'myUser',
connectionLimit: 5,
trace: true
});
await pool.query('wrong query');
/* will throw an error like :
sql: wrong query - parameters:[]
at Object.module.exports.createError (C:\temp\mariadb-connector-nodejs2\lib\misc\errors.js:57:10)
at ...
From event:
at Function._PARAM (C:\temp\mariadb-connector-nodejs2\lib\connection-promise.js:104:30)
at PoolPromise.query (C:\temp\mariadb-connector-nodejs2\lib\pool-promise.js:102:40)
at Context.<anonymous> (C:\temp\mariadb-connector-nodejs2\test\integration\test-pool.js:60:18)
at callFn (C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runnable.js:366:21)
at Test.Runnable.run (C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runnable.js:354:5)
at Runner.runTest (C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runner.js:678:10)
at C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runner.js:801:12
at next (C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runner.js:593:14)
at C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runner.js:603:7
at next (C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runner.js:486:14)
at Immediate.<anonymous> (C:\temp\mariadb-connector-nodejs2\node_modules\mocha\lib\runner.js:571:5)
at processImmediate (internal/timers.js:464:21) {
text: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'wrong query' at line 1",
sql: 'wrong query - parameters:[]',
fatal: false,
errno: 1064,
sqlState: '42000',
code: 'ER_PARSE_ERROR'
}
*/const conn = await mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD,
timezone: 'America/Los_Angeles',
skipSetTimezone: true
});const conn = await mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD
});$ DB_HOST=localhost DB_USER=test DB_PASSWORD=secretPasswrd node my-app.js$ npm install dotenvrequire('dotenv').config();
const conn = await mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD
});DB_HOST=localhost
DB_USER=test
DB_PWD=secretPasswrdconst conn = await mariadb.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD
});try {
const conn = await mariadb.createConnection({
host: 'mydb.com',
user: 'myUser',
password: 'myPwd'
});
console.log("connected! connection id is " + conn.threadId);
} catch (err) {
console.log("not connected due to error: " + err);
}SHOW VARIABLES LIKE 'socket';const conn = await mariadb.createConnection({
socketPath: '/tmp/mysql.sock',
user: 'root'
});const conn = await mariadb.createConnection({
socketPath: '\\\\.\\pipe\\MySQL',
user: 'root'
});const pool = mariadb.createPool({
host: 'mydb.com',
user: 'myUser',
connectionLimit: 5
});
let conn;
try {
conn = await pool.getConnection();
console.log('connected! connection id is ' + conn.threadId);
conn.release(); //release to pool
} catch (err) {
console.log('not connected due to error: ' + err);
}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
const conn = await cluster.getConnection(/slave*/, "RR");
try {
const rows = await conn.query("SELECT 1");
return rows[0]["1"];
} finally {
conn.end();
}try {
await mariadb.importFile({ host: 'localhost', user: 'root', file: '/tmp/tools/data-dump.sql'});
} catch (e) {
// ...
}console.log(mariadb.defaultOptions({ timezone: '+00:00' }));
/*
{
host: 'localhost',
port: 3306,
user: 'root',
password: undefined,
database: undefined,
collation: Collation { index: 224, name: 'UTF8MB4_UNICODE_CI', charset: 'utf8' },
timezone: '+00:00',
...
}
*/ const rows = await conn.query('SELECT NOW()');
console.log(rows); //[ { 'NOW()': 2018-07-02T17:06:38.000Z } ]const rows = await conn.query({
dateStrings: true,
sql: 'SELECT NOW()'
});
console.log(rows); //[ { 'NOW()': '2018-07-02 19:06:38' } ]const res = await connection.query("INSERT INTO someTable VALUES (?, ?, ?)", [
1,
Buffer.from("c327a97374", "hex"),
"mariadb",
]);
//will send INSERT INTO someTable VALUES (1, _BINARY '.\'.st', 'mariadb')const https = require('https');
//3Mb page
https.get(
'https://node.green/#ES2018-features-Promise-prototype-finally-basic-support',
readableStream => conn.query('INSERT INTO StreamingContent (b) VALUE (?)', [readableStream])
);await connection.query('CREATE TABLE animals (' +
'id MEDIUMINT NOT NULL AUTO_INCREMENT,' +
'name VARCHAR(30) NOT NULL,' +
'PRIMARY KEY (id))');
const res = await connection.query('INSERT INTO animals(name) value (?)', ['sea lions']);
//res : { affectedRows: 1, insertId: 1, warningStatus: 0 }const res = await connection.query('select * from animals');
// res : [
// { id: 1, name: 'sea lions' },
// { id: 2, name: 'bird' },
// ]
const meta = res.meta;
// meta: [ ... ]try {
// Query that would normally take more than 100ms
await connection.query({
sql: 'SELECT * FROM information_schema.tables, information_schema.tables as t2',
timeout: 100
});
} catch (err) {
// Error will be:
// SqlError: (conn:2987, no: 1969, SQLState: 70100) Query execution was interrupted (max_statement_time exceeded)
// ...
}await connection.query(
{ namedPlaceholders: true, sql: 'INSERT INTO someTable VALUES (:id, :img, :db)' },
{ id: 1, img: Buffer.from('c327a97374', 'hex'), db: 'mariadb' }
);const res = await connection.query({ rowsAsArray: true, sql: 'select * from animals' });
// res = [
// [ 1, 'sea lions' ],
// [ 2, 'bird' ],
// ]
const meta = res.meta;
// meta: [...]const [rows, meta] = await connection.query({ metaAsArray: true, sql: 'select * from animals' });
// rows = [
// {'id': 1, 'name': 'sea lions' },
// {'id': 2, 'name': 'bird' },
// ]
// meta = [...]const res = await connection.query({
nestTables: true,
sql: 'select a.name, a.id, b.name from animals a, animals b where b.id=1'
});
// res = [
// {
// a: { name: 'sea lions', id: 1 },
// b: { name: 'sea lions' }
// },
// {
// a: { name: 'bird', id: 2 },
// b: { name: 'sea lions' }
// }
//]const res = await connection.query({
nestTables: '_',
sql: 'select a.name, a.id, b.name from animals a, animals b where b.id=1'
});
// res = [
// { a_name: 'sea lions', a_id: 1, b_name: 'sea lions' },
// { a_name: 'bird', a_id: 2, b_name: 'sea lions' }
//]await shareConn.query('CREATE TEMPORARY TABLE bigIntTable(id BIGINT)');
await shareConn.query("INSERT INTO bigIntTable value ('9007199254740993')");
const res = await shareConn.query('select * from bigIntTable');
// res : [{ id: 9007199254740993n }] (exact value)
const res2 = await shareConn.query({sql: 'select * from bigIntTable', supportBigInt: false});
// res : [{ id: 9007199254740992 }] (not exact value)const tinyToBoolean = (column, next) => {
if (column.type == 'TINY' && column.columnLength === 1) {
const val = column.tiny();
return val === null ? null : val === 1;
}
return next();
};
connection.query({ typeCast: tinyToBoolean, sql: '...' });const rows = await connection.query("SELECT 1, 'a'");
// rows = [
// { '1': 1, a: 'a' }
// ]
const meta = rows.meta;
// 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]
// }
// ]
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();
throw e;
}
}connection.queryStream('SELECT * FROM mysql.user')
.on("error", err => {
console.log(err); // handle error
})
.on("fields", meta => {
console.log(meta); // metadata array
})
.on("data", row => {
console.log(row); // process each row
})
.on("end", () => {
console.log("Query completed"); // all rows received
});const stream = require('stream');
const fs = require('fs');
// Create a transform stream to convert rows to JSON strings
const transformStream = new stream.Transform({
objectMode: true, // Important! queryStream produces objects
transform: function transformer(row, encoding, callback) {
callback(null, JSON.stringify(row) + '\n');
}
});
// Create output file stream
const fileStream = fs.createWriteStream('./query-results.jsonl');
// Start the query stream
const queryStream = connection.queryStream('SELECT * FROM mysql.user');
// Using pipeline (Node.js 10+) to handle errors and cleanup
stream.pipeline(
queryStream,
transformStream,
fileStream,
(err) => {
if (err) {
console.error('Pipeline failed:', err);
} else {
console.log('Pipeline succeeded');
}
queryStream.close(); // Always close the query stream
}
);async function streamingFunction() {
const prepare = await shareConn.prepare('SELECT * FROM mysql.user where host = ?');
const stream = prepare.executeStream(['localhost']);
try {
for await (const row of stream) {
console.log(row);
}
} catch (e) {
queryStream.close();
}
prepare.close();
} const prepare = await shareConn.prepare('SELECT * FROM mysql.user where host = ?');
prepare.executeStream(['localhost'])
.on("error", err => {
console.log(err); //if error
})
.on("fields", meta => {
console.log(meta); // [ ...]
})
.on("data", row => {
console.log(row);
})
.on("end", () => {
//ended
prepare.close();
});const prepare = await conn.prepare('INSERT INTO mytable(id,val) VALUES (?,?)');
await prepare.execute([1, 'val1'])
prepare.close();const res = await conn.execute('SELECT * FROM mytable WHERE someVal = ? and otherVal = ?', [1, 'val1']);connection.query(
'CREATE TEMPORARY TABLE batchExample(id int, id2 int, id3 int, t varchar(128), id4 int)'
);
const res = await connection.batch('INSERT INTO `batchExample` values (1, ?, 2, ?, 3)', [
[1, 'john'],
[2, 'jack']
]);
console.log(res.affectedRows); // 2// Get individual results for each insert operation,
let results = await connection.batch(
{sql :'INSERT INTO users(name, age) VALUES (?, ?)', fullResult: true },
[['John', 25], ['Jane', 26], ['Bob', 32]])
// results is an array of individual OkPacket objects
.forEach((res, i) => {
console.log(`Result ${i+1}:`, res);
});
// Output:
// Result 1: OkPacket { affectedRows: 1, insertId: 1, warningStatus: 0 }
// Result 2: OkPacket { affectedRows: 1, insertId: 2, warningStatus: 0 }
// Result 3: OkPacket { affectedRows: 1, insertId: 3, warningStatus: 0 }
// Get aggregate results for each insert operation,
let results = await connection.batch(
{sql :'INSERT INTO users(name, age) VALUES (?, ?)', fullResult: true },
[['Boby', 24], ['Rico', 20], ['Johnny', 321]])
// results is an array of individual OkPacket objects
.forEach((res, i) => {
console.log(`Result ${i+1}:`, res);
});
// Output:
// Result 1: OkPacket { affectedRows: 3, insertId: 1, warningStatus: 0 }const conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.query("INSERT INTO testTransaction values ('test')");
await conn.query("INSERT INTO testTransaction values ('test2')");
await conn.commit();
} finally {
await conn.release();
}await using conn = await pool.getConnection();
await conn.beginTransaction();
await conn.query("INSERT INTO testTransaction values ('test')");
await conn.query("INSERT INTO testTransaction values ('test2')");
await conn.commit();
// after the block, conn.release() will automatically be calledtry {
await conn.beginTransaction();
await conn.query("INSERT INTO testTransaction values ('test')");
await conn.query("INSERT INTO testTransaction values ('test2')");
await conn.commit();
} catch(err) {
await conn.rollback();
}try {
await conn.changeUser({
user: 'changeUser',
password: 'mypassword'
});
//connection user is now changed.
} catch (e) {
// ...
}conn.ping()
.then(() => {
//connection is valid
})
.catch(err => {
//connection is closed
})const conn = await mariadb.createConnection({
host: 'mydb.com',
user: 'myUser',
password: 'myPwd'
});
try {
const res = await conn.query('select 1');
console.log(res); // [{ "1": 1 }]
return res;
} finally {
await conn.end();
}await using conn = await mariadb.createConnection({
host: 'mydb.com',
user: 'myUser',
password: 'myPwd'
});
const res = await conn.query('select 1');
console.log(res); // [{ "1": 1 }]
return res;
// after the block, conn.end() will automatically be calledtry {
// long query > 20s
conn.query(
'select * from information_schema.columns as c1, information_schema.tables, information_schema.tables as t2'
);
conn.destroy(); //will immediately close the connection, before previous command end (no `await` in previous command)
} catch (err) {
//Error: Connection destroyed, command was killed
// ...
// fatal: true,
// errno: 45004,
// sqlState: '08S01',
// code: 'ER_CMD_NOT_EXECUTED_DESTROYED'
}const myColVar = "let'go";
const myTable = 'table:a'
const cmd = 'SELECT * FROM ' + conn.escapeId(myTable) + ' where myCol = ' + conn.escape(myColVar);
//or using template literals
const cmd2 = `SELECT * FROM ${conn.escapeId(myTable)} where myCol = ${conn.escape(myColVar)}`;
// cmd = cmd2 = "SELECT * FROM `table:a` where myCol = 'let\\'s go'"const myColVar = "let'go";
const myTable = "table:a"
const cmd = 'SELECT * FROM ' + conn.escapeId(myTable) + ' where myCol = ' + conn.escape(myColVar);
// cmd value will be:
// "SELECT * FROM `table:a` where myCol = 'let\\'s go'"
// using template literals:
const res = await con.query(`SELECT * FROM ${con.escapeId(myTable)} where myCol = ?`, [myColVar]); console.log(connection.serverVersion()); //10.2.14-MariaDBtry {
await conn.importFile({
file: '/tmp/someFile.sql',
database: 'myDb'
});
} catch (e) {
// ...
}async function executeQuery() {
let connection;
try {
connection = await mariadb.createConnection({
host: 'localhost',
user: 'root',
password: 'password'
});
return await connection.query('SELECT * FROM users WHERE id = ?', [userId]);
} catch (err) {
// Log the error with all available information
console.error('Database error:', {
message: err.message,
code: err.code,
sqlState: err.sqlState,
query: err.sql,
fatal: err.fatal
});
// Rethrow or handle appropriately based on error type
if (err.code === 'ER_ACCESS_DENIED_ERROR') {
throw new Error('Database authentication failed');
} else if (err.code === 'ER_BAD_DB_ERROR') {
throw new Error('Database does not exist');
} else {
throw new Error('An unexpected database error occurred');
}
} finally {
// Always close the connection to avoid leaks
if (connection) await connection.end();
}
}try {
await connection.query('INSERT INTO users (email) VALUES (?)', [email]);
} catch (err) {
if (err.code === 'ER_DUP_ENTRY') {
// Handle duplicate email error
return { success: false, message: 'Email already registered' };
}
// Handle other errors
throw err;
}try {
await connection.query('SELECT * FROM nonexistent_table');
} catch (err) {
if (err.fatal) {
// Connection is no longer usable
console.error('Fatal error, connection lost:', err.message);
// Reconnect or fail gracefully
} else {
// Connection is still valid despite the error
console.error('Non-fatal error:', err.message);
// Continue using the same connection
}
}{
Error: (conn:116, no: 1146, SQLState: 42S02) Table 'testdb.nonexistent_table' doesn't exist
sql: SELECT * FROM nonexistent_table - parameters:[]
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:559:20)
From event:
at Connection.query (/path/to/mariadb-connector-nodejs/lib/connection.js:183:12)
at async function (/path/to/your/app.js:25:16)
fatal: false,
errno: 1146,
sqlState: '42S02',
code: 'ER_NO_SUCH_TABLE'
}
const conn = await mariadb.createConnection({
user: 'root',
password: 'myPwd',
host: 'localhost',
socketTimeout: 100
});
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' }
});
// Create a pool
const pool = mariadb.createPool({
host: 'localhost',
user: 'root',
password: 'password',
connectionLimit: 5
});
async function transferFunds(fromAccount, toAccount, amount) {
let conn;
try {
// Get a connection from the pool
conn = await pool.getConnection();
// Use the connection for a transaction
await conn.query("START TRANSACTION");
// Verify sufficient funds
const [account] = await conn.query(
"SELECT balance FROM accounts WHERE id = ? FOR UPDATE",
[fromAccount]
);
if (account.balance < amount) {
await conn.query("ROLLBACK");
return { success: false, message: "Insufficient funds" };
}
// Perform the transfer
await conn.query(
"UPDATE accounts SET balance = balance - ? WHERE id = ?",
[amount, fromAccount]
);
await conn.query(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
[amount, toAccount]
);
// Commit the transaction
await conn.query("COMMIT");
return { success: true, message: "Transfer completed" };
} catch (err) {
// Handle errors
if (conn) await conn.query("ROLLBACK");
console.error('Transaction failed:', err);
return { success: false, error: err.message };
} finally {
// Always release the connection back to the pool
if (conn) conn.release();
}
}// Simple query using the pool directly
async function getProductsByCategory(category) {
try {
const rows = await pool.query(
'SELECT * FROM products WHERE category = ? ORDER BY price ASC',
[category]
);
console.log(`Found ${rows.length} products in ${category} category`);
return {
success: true,
count: rows.length,
products: rows
};
} catch (err) {
console.error('Query failed:', err);
return {
success: false,
error: err.message
};
}
}async function getRecentOrders(options) {
try {
const rows = await pool.query({
sql: 'SELECT * FROM orders WHERE created_at > ? LIMIT ?',
values: [options.since, options.limit || 10],
dateStrings: true, // Return dates as strings
nestTables: true // Group results by table
});
return rows;
} catch (err) {
console.error('Failed to fetch recent orders:', err);
throw err;
}
}async function addMultipleUsers(users) {
try {
// Format user data for batch insert
const userValues = users.map(user => [
user.name,
user.email,
user.password,
user.created_at || new Date()
]);
const result = await pool.batch(
'INSERT INTO users(name, email, password, created_at) VALUES (?, ?, ?, ?)',
userValues);
console.log(`Added ${result.affectedRows} users`);
return {
success: true,
insertCount: result.affectedRows,
insertIds: result.map(r => r.insertId)
};
} catch (err) {
console.error('Batch user creation failed:', err);
return {
success: false,
error: err.message
};
}
}// Application shutdown handler
async function gracefulShutdown() {
console.log('Application shutting down...');
try {
// Close database pool
console.log('Closing database connections...');
await pool.end();
console.log('All database connections closed successfully');
// Close other resources
// ...
console.log('Shutdown complete');
process.exit(0);
} catch (err) {
console.error('Error during shutdown:', err);
process.exit(1);
}
}
// Register shutdown handlers
process.on('SIGINT', gracefulShutdown);
process.on('SIGTERM', gracefulShutdown);const userId = "user's-id";
const query = `SELECT * FROM users WHERE id = ${pool.escape(userId)}`;
// query = "SELECT * FROM users WHERE id = 'user\\'s-id'"const tableName = "user-data";
const columnName = "last-login";
const query = `SELECT ${pool.escapeId(columnName)} FROM ${pool.escapeId(tableName)}`;
// query = "SELECT `last-login` FROM `user-data`"async function importDatabaseDump(filePath, targetDatabase) {
try {
await pool.importFile({
file: filePath,
database: targetDatabase
});
console.log(`Successfully imported ${filePath} into ${targetDatabase}`);
return { success: true };
} catch (err) {
console.error(`Import failed: ${err.message}`);
return {
success: false,
error: err.message
};
}
}pool.on('acquire', (connection) => {
console.log(`Connection ${connection.threadId} acquired from pool`);
});pool.on('connection', (connection) => {
console.log(`New connection ${connection.threadId} created in pool`);
// You can initialize connections with specific settings
connection.query("SET SESSION time_zone='+00:00'");
connection.query("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE'");
});pool.on('release', (connection) => {
console.log(`Connection ${connection.threadId} released back to pool`);
});pool.on('error', (err) => {
console.error('Pool error:', err);
// Implement monitoring or recovery logic
notifyAdministrator(`Database pool error: ${err.message}`);
});// Get current number of active connections
const active = pool.activeConnections();
// Get total number of connections (used and unused)
const total = pool.totalConnections();
// Get current number of unused connections
const idle = pool.idleConnections();
// Get size of pending connection requests queue
const queued = pool.taskQueueSize();
console.log(`Pool status: ${active}/${total} connections active, ${idle} idle, ${queued} requests queued`);const mariadb = require('mariadb');
// Create the cluster
const cluster = mariadb.createPoolCluster({
removeNodeErrorCount: 5, // Remove a node after 5 consecutive connection failures
restoreNodeTimeout: 1000, // Wait 1 second before trying a failed node again
defaultSelector: 'ORDER' // Use nodes in order (first working node in the list)
});
// Add database nodes to the cluster
cluster.add('primary', {
host: 'primary-db.example.com',
user: 'app_user',
password: 'password',
connectionLimit: 10
});
cluster.add('replica1', {
host: 'replica1-db.example.com',
user: 'app_user',
password: 'password',
connectionLimit: 20
});
cluster.add('replica2', {
host: 'replica2-db.example.com',
user: 'app_user',
password: 'password',
connectionLimit: 20
});// Create an empty cluster
const cluster = mariadb.createPoolCluster();
// Add a primary database node
cluster.add('primary', {
host: 'primary-db.example.com',
user: 'app_user',
password: 'password',
connectionLimit: 10
});
// Add multiple read-only replica nodes
cluster.add('replica-east', {
host: 'replica-east.example.com',
user: 'readonly_user',
password: 'password',
connectionLimit: 20
});
cluster.add('replica-west', {
host: 'replica-west.example.com',
user: 'readonly_user',
password: 'password',
connectionLimit: 20
});// Create a cluster with multiple nodes
const cluster = mariadb.createPoolCluster();
cluster.add('primary', { host: 'primary-db.example.com', user: 'app_user' });
cluster.add('replica1', { host: 'replica1.example.com', user: 'readonly_user' });
cluster.add('replica2', { host: 'replica2.example.com', user: 'readonly_user' });
cluster.add('analytics', { host: 'analytics-db.example.com', user: 'analytics_user' });
// Later, remove all replica nodes
cluster.remove('replica*');
// Remove a specific node
cluster.remove('analytics');async function executeQuery(sql, params) {
let conn;
try {
// For write operations, always use the primary
if (sql.toLowerCase().startsWith('insert') ||
sql.toLowerCase().startsWith('update') ||
sql.toLowerCase().startsWith('delete')) {
conn = await cluster.getConnection('primary');
}
// For read operations, use round-robin among replicas
else {
conn = await cluster.getConnection('replica*', 'RR');
}
const result = await conn.query(sql, params);
return result;
} finally {
if (conn) conn.release();
}
}
// Usage
const users = await executeQuery('SELECT * FROM users WHERE status = ?', ['active']);
await executeQuery('UPDATE users SET last_login = NOW() WHERE id = ?', [userId]);async function executeQueryWithRetry(sql, params, maxRetries = 3) {
let attempts = 0;
let lastError;
while (attempts < maxRetries) {
let conn;
attempts++;
try {
conn = await cluster.getConnection('*', 'ORDER'); // Try nodes in order
const result = await conn.query(sql, params);
return result;
} catch (err) {
lastError = err;
console.warn(`Query attempt ${attempts} failed:`, err.message);
// Only retry on connection errors, not query syntax errors
if (!err.fatal) throw err;
// Wait before retrying
await new Promise(resolve => setTimeout(resolve, 500));
} finally {
if (conn) conn.release();
}
}
throw new Error(`All ${maxRetries} query attempts failed. Last error: ${lastError.message}`);
}// Create interfaces for different database roles
const primaryPool = cluster.of('primary'); // Only the primary node
const replicaPool = cluster.of('replica*', 'RANDOM'); // All replicas with random selection
async function readData(userId) {
let conn;
try {
// Get connection from any replica randomly
conn = await replicaPool.getConnection();
return await conn.query('SELECT * FROM users WHERE id = ?', [userId]);
} finally {
if (conn) conn.release();
}
}
async function writeData(userData) {
let conn;
try {
// Always write to primary
conn = await primaryPool.getConnection();
await conn.query('INSERT INTO users SET ?', userData);
return { success: true };
} finally {
if (conn) conn.release();
}
}// Application shutdown handler
async function gracefulShutdown() {
console.log('Application shutting down...');
try {
// Close database connection pool cluster
console.log('Closing database connections...');
await cluster.end();
console.log('All database connections closed successfully');
// Close other resources
// ...
console.log('Shutdown complete');
process.exit(0);
} catch (err) {
console.error('Error during shutdown:', err);
process.exit(1);
}
}
// Register shutdown handlers
process.on('SIGINT', gracefulShutdown);
process.on('SIGTERM', gracefulShutdown);// Create a filtered cluster with only replica nodes
const replicas = cluster.of('replica*', 'RR'); // Round-robin among replicas
async function getReadOnlyData() {
let conn;
try {
// This will automatically use round-robin selection among replica nodes
conn = await replicas.getConnection();
return await conn.query('SELECT * FROM some_large_table LIMIT 1000');
} finally {
if (conn) conn.release();
}
}// Create filtered clusters for different roles
const primary = cluster.of('primary');
const replicas = cluster.of('replica*', 'RR');
// Read from replicas using the shorthand query method
async function getUserById(id) {
try {
return await replicas.query('SELECT * FROM users WHERE id = ?', [id]);
} catch (err) {
console.error('Failed to get user:', err);
throw err;
}
}
// Write to primary
async function updateUserStatus(id, status) {
try {
return await primary.query(
'UPDATE users SET status = ?, updated_at = NOW() WHERE id = ?',
[status, id]
);
} catch (err) {
console.error('Failed to update user status:', err);
throw err;
}
}cluster.on('remove', (nodeId) => {
console.warn(`Database node '${nodeId}' has been removed from the cluster`);
// You might want to send alerts or trigger monitoring
notifyAdministrators(`Database node ${nodeId} has been removed from the cluster due to repeated connection failures`);
});$ npm install mariadbimport mariadb from 'mariadb';import { createConnection, createPool, SqlError } from 'mariadb';import mariadb from 'mariadb';
async function main(): Promise<void> {
const conn = await mariadb.createConnection({
host: 'mydb.com',
user: 'myUser',
password: 'myPwd',
});
try {
const rows = await conn.query<{ now: Date }[]>('SELECT NOW() as now');
console.log(rows[0].now);
} finally {
await conn.end();
}
}
main();interface Animal {
id: number;
name: string;
}
// Typed result rows
const rows = await conn.query<Animal[]>('SELECT id, name FROM animals');
rows.forEach(row => console.log(row.name)); // row.name is string
// Typed result rows AND typed values (since 3.5.1)
const rows2 = await conn.query<Animal[], [number]>(
'SELECT id, name FROM animals WHERE id = ?',
[1]
);import { UpsertResult } from 'mariadb';
const result: UpsertResult = await conn.query(
"INSERT INTO animals (name) VALUES (?)",
['sea lion']
);
console.log(result.insertId); // bigint
console.log(result.affectedRows); // numberimport mariadb, { ConnectionConfig } from 'mariadb';
const config: ConnectionConfig = {
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PWD,
database: 'mydb',
connectionLimit: 5,
};
const conn = await mariadb.createConnection(config);import mariadb, { Pool, PoolConfig } from 'mariadb';
const config: PoolConfig = {
host: 'mydb.com',
user: 'myUser',
password: 'myPwd',
connectionLimit: 5,
};
const pool: Pool = mariadb.createPool(config);import { PoolConnection } from 'mariadb';
const conn: PoolConnection = await pool.getConnection();
try {
const rows = await conn.query<Animal[]>('SELECT id, name FROM animals');
console.log(rows);
} finally {
conn.release();
}await using conn = await mariadb.createConnection(config);
const rows = await conn.query<Animal[]>('SELECT id, name FROM animals');
// conn.end() is called automatically hereawait using conn = await pool.getConnection();
const rows = await conn.query<Animal[]>('SELECT id, name FROM animals');
// conn.release() is called automatically hereawait using conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.query("INSERT INTO testTransaction VALUES ('test')");
await conn.query("INSERT INTO testTransaction VALUES ('test2')");
await conn.commit();
} catch (err) {
await conn.rollback();
throw err;
}
// conn.release() is called automatically here// Before 3.5.1
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
await conn.query("INSERT INTO testTransaction VALUES ('test')");
await conn.query("INSERT INTO testTransaction VALUES ('test2')");
await conn.commit();
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release();
}import { SqlError } from 'mariadb';
try {
await conn.query('SELECT * FROM nonexistent_table');
} catch (err) {
if (err instanceof SqlError) {
console.error(err.code); // e.g. 'ER_NO_SUCH_TABLE'
console.error(err.errno); // e.g. 1146
console.error(err.sqlState); // e.g. '42S02'
console.error(err.fatal); // boolean: connection is no longer usable if true
console.error(err.sql); // the query that caused the error
}
}{
"compilerOptions": {
"target": "ES2022",
"lib": ["ES2022", "ESNext"],
"module": "Node16",
"moduleResolution": "Node16",
"strict": true
}
}// Direct write operations to primary
const primary = cluster.of('primary');
// Direct read operations to replicas
const replicas = cluster.of('replica*', 'RR');
async function saveData(data) {
// Writes go to primary
return await primary.query('INSERT INTO table SET ?', [data]);
}
async function getData(id) {
// Reads come from replicas
return await replicas.query('SELECT * FROM table WHERE id = ?', [id]);
}