Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Batch processing groups multiple queries into one unit and passes it in a single network trip to a database. There are different implementations according to server type and version.
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 instance, say you want to create a basket with five items.
Some benchmark to do some 100 inserts with one parameter of 100 characters: (benchmark source - see and )
There is one thing to pay attention to: MySQL / MariaDB servers have a global option that limit the maximum packet exchange size. If the connector sends more data than these limits, the socket will be immediately dropped.
default server values :
since MariaDB 10.2.4 : 16M
since MariaDB 10.1.7 : 4M
before MariaDB 10.1.7 : 1M
You can check server value using query select @@max_allowed_packet.
Connection option "maxAllowedPacket" permits to connector behaving accordingly: if maxAllowedPacket is set to 1048576 (=1M), the packet sent to the server will be split in packet less than 1M to avoid any issue.
connection.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
}
});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.
MariaDB Connector/Node.js is used to connect applications developed on Node.js to MariaDB and MySQL databases. The library is LGPL licensed.
MariaDB Connector/Node.js is a native JavaScript driver.
The required files can be downloaded from:
The source code is available on GitHub:
MariaDB Connector/Node.js on npm, the package manager for JavaScript:
The driver can be installed using npm:
Driver versions are compatible with all MariaDB servers and MySQL 5.x (>= 5.5.3). Tested with all active MariaDB server versions with Node.js 14+ (see on ubuntu/windows/macOS).
MariaDB Connector/Node.js requires Node.js 14 or above, since it is based on Promise.
GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License or (at your option) any later version.
The MariaDB Connector can use different APIs on the back-end: and . The default API is Promise. The callback API is provided for compatibility with the mysql and mysql2 APIs.
npm install mariadbWith traditional database drivers, queries issued from the application are sent one by one to the server, waiting on the results of the first query before sending the next. Communication with the server follows this synchronous request-response messaging pattern. While this may be sufficient for some applications, it isn't very efficient when you need to process a large volume of queries at the same time.
Node.js provides good support for asynchronous processing, which you can utilize with the MariaDB Connector using the Pipelining option.
When Pipelining, the Connector uses an optimistic send, sending queries one after another, preserving the FIFO order. This is particularly efficient when the client is some distance from the server.
For instance, say you want to create a basket with three items.
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
}
});Using the standard client-server protocol, the Connector communicates with the database following a request-response messaging pattern. The Connector sends a command, then doesn't send another until it receives a response from the input socket.
When using Pipelining, the Connector sends commands in bulk, reducing network latency. The catch is that the process is optimistic, meaning that if an error occurs on the first or second command, the following commands have already been sent to the database.
mariasql is a node.js binding to the non-blocking client library provided with MariaDB. It is compatible with MySQL.
This binding is different from other libmysqlclient bindings in that it uses the non-blocking functions available in MariaDB's client library.
Install it with the npm package installer:
npm install mariasqlIn benchmarks, mariasql performs much better than libmysqlclient.
The source code is located at github:node-mariasql.
The MariaDB Node.js Connector is available through the Node.js repositories. You can install it using npm:
npm install mariadbUsing ECMAScript, prior to 2017:
const 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();
});Using ECMAScript 2017:
The MariaDB Connector can use different APIs on the back-end: Promise and Callback. The default API is Promise. The callback API is provided for compatibility with the mysql and mysql2 APIs.
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();
});Explore 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.
There are two different connection implementations: one, the default, uses Promise, and the other uses Callback, allowing for compatibility with the MySQL and mysql2 API's. The documentation provided on this page follows Callback. If you want information on the Promise API, see the PROMISE API.
Install the MariaDB Connector using npm
You can then use the Connector in your application code with the Callback API. For instance,
In order to use the Connector, you first need to install it on your system. The installation process for Promise and Callback API is managed with the same package through npm.
To use the Connector, you need to import the package into your application code. Given that the Callback API is not the default, the require() statement is a little different.
This initializes the constant mariadb, which is set to use the Callback API rather than the default Promise API.
The default behavior for decoding BIGINT/DECIMAL datatypes in 2.x versions and MySQL/MySQL2 drivers returns / datatype for 2.x versions, and When/mysql2 drivers return a JavaScript object. BIGINT/DECIMAL values might not be in the safe range, resulting in approximate results.
Since the 3.x version, the driver has a reliable default, returning:
DECIMAL => JavaScript String
BIGINT => JavaScript object
For compatibility with the previous version or MySQL/mysql driver, four options have been added to return BIGINT/DECIMAL as a number, as the previous defaults.
Previous options supportBigNumbers and bigNumberStrings still exist for compatibility, but are now deprecated.
Other considerations
MySQL has an experimental syntax permitting the use of ?? characters as a placeholder to escape ID. This isn't implemented in the mariMariaDBadb driver, permitting the same query syntax for uery and .
Example:
To use explicit escapeId:
Cluster configuration removeNodeErrorCount defaults to Infinity when mysql/mysql2 defaults to the value 5. This avoids removing nodes without explicitly saying so.
The client and database can have different timezone.
The connector has different solutions when this is the case. The timezone option can have the following value:
'local' (default): connector doesn't do any conversion. If the database has a different timezone, there will be an offset issue.
'auto' : connector retrieve server timezone. Dates will be converted if the server timezone differs from the client.
IANA timezone/offset, example 'America/New_York' or '+06:00'.
IANA timezone/offset
When using an IANA timezone, the connector will set the connection timezone to the timezone. This can throw an error on connection if the timezone is unknown by the server (see , timezone tables might not be initialized). If you are sure the server is using that timezone, this step can be skipped with the option skipSetTimezone.
If the timezone corresponds to the JavaScript default timezone, then no conversion will be done.
Timezone setting recommendation.
The best is to have the same timezone on the client and database, then keep the 'local' default value.
If different, then either the client or server has to convert the date. In general, it is best to use client conversion to avoid putting any unneeded stress on the database. Timezone has to be set to the IANA timezone corresponding to the server timezone, and disabled skipSetTimezone option since you are sure that the server has the corresponding timezone.
Example: a client uses 'America/New_York' by default, and server 'America/Los_Angeles'. Execute 'SELECT @@system_time_zone' on the server it will give the server default timezone. The server caa n return POSIX timezone like 'PDT' (Pacific Daylight Time). IANA timezone correspondence must be found: (see ) and configure client-side. This will ensure DST (automatic date saving time change will be handled)
Connection details such as URL, username, and password are better hidden in environment variables. using code like :
Then, for example, run node.js setting those environment variables :
Another solution is using dotenv package. Dotenv loads environment variables from .env files into the process.env variable in Node.js :
then configure dotenv to load all .env files
with a .env file containing
.env files must NOT be pushed into the repository, using .gitignore
Alternatively, Node.js 20.0 introduced the experimental feature of using the node --env-file=.env syntax to load environment variables without the need for external dependencies. WE can then simply write.
Assuming the presence of the same .env file as previously described.
The Connector with the Callback API is similar to the one using Promise, but with a few differences.
Base:
: Creates a connection to a MariaDB Server.
: Creates a new Pool.
: Creates a new pool cluster.
: import Sql file
Connection:
: Executes a .
: fast batch processing.
: Begins a transaction
: Commit the current transaction, if any.
Pool:
: Creates a new connection.
: Executes a query.
: Executes a batch
: Gracefully closes the connection.
PoolCluster
: add a pool to cluster.
: remove and end pool according to pattern.
: end cluster.
: return a connection from cluster.
createConnection(options) → Connection
options: JSON/String Uses the same options as Promise API. For a complete list, see .Returns a Connection object
Creates a new connection.
The difference between this method and the same with the Promise API is that this method returns a Connection object, rather than a Promise that resolves to a Connection object.
Essential options list:
For more information, see the documentation.
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 server system variable. If you don't know it offhand, you can retrieve it from the server.
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:
It has a similar syntax on Windows:
createPool(options) → Pool
options: JSON/stringReturns a object.
Creates a new pool.
Example:
Pool options includes that will be used when creating new connections.
Specific options for pools are :
Example:
createPoolCluster(options) → PoolCluster
options: JSONReturns a object,
Creates a new pool cluster. Cluster handles multiple pools, giving high availability / distributing load (using round robin / random / ordered ).
Example:
Pool cluster options include that will be used when creating new pools.
Specific options for the pool cluster are :
importFile(options[, callback])
options: JSON/String + one additional optionsfilecallback function that returns an error if fails or nothing if success.
Import an sql file
Example:
version → StringReturns a String that is the library version. example '2.1.2'.
defaultOptions(options) → Json
options: JSON/String (non-mandatory)Returns a JSON value containing options default value.
permit listing the default options that will be used.
connection.query(sql[, values][, callback]) -> Emitter
sql: string | JSON An SQL string value or JSON object to supersede default connections options. If a JSON object, it must have an"sql"property. For example:{dateStrings:true, sql:'SELECT NOW()'}
values: array | object Placeholder values. Usually an array, but in cases of just one placeholder, it can be given as is.
Sends query to the database with a Callback function to call when done.
In cases where the query returns huge result-sets, this means that all data is stored in memory. You may find it more practical to use the Emitter object to handle the rows one by one, to avoid overloading memory resources.
For example, issuing a query with an SQL string:
Using JSON objects:
To avoid SQL Injection attacks, queries permit the use of a question mark as a placeholder. The Connector escapes values according to their type. You can use any native JavaScript type, Buffer, Readable, or any object with a toSqlString method in these values. All other objects are stringified using the JSON.stringify method.
The Connector automatically streams objects that implement Readable. In these cases, check the values on the following server system variables, as they may interfere:
: The server must receive the query in full from the Connector before timing out. The default value for this system variable is 30 seconds.
: Using this system variable, you can control the maximum amount of data the Connector can send to the server.
You can also issue the same query using Streaming.
Queries issued from the Connector return two different kinds of results: a JSON object and an array, depending on the type of query you issue. Queries that write to the database, such as INSERT, DELETE and UPDATE commands return a JSON object with the following properties:
affectedRows: An integer listing the number of affected rows.
insertId: An integer noting the auto-increment ID. In case multiple rows have been inserted, this corresponds to the FIRST auto-increment value.
warningStatus: An integer indicating whether the query ended with a warning.
Result-set array
Queries issued from the Connector return two different kinds of results: a JSON object and an array, depending on the type of query you issue. When the query returns multiple rows, the Connector returns an array, representing the data for each row in the array. It also returns a meta object, containing query metadata.
You can format the data results using the nestTables and rowsAsArray options. By default, it returns a JSON object for each row.
Piping
piping can be used using the.stream () function on a query that returns a Readable object that will emit rows objects.
connection.batch(sql, values [, callback])
sql: string | JSON SQL string value or JSON object to supersede default connections options. JSON objects must have an"sql"property. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array Array of parameter (array of array or array of object if using named placeholders).
Implementation depends of server type and version. for MariaDB server version 10.2.7+, the implementation uses 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 a query cannot be re-writen will execute a query for each value.
the result difference compared to executing multiple single query inserts is that only the first generated insert id will be returned.
For instance,
connection.beginTransaction([callback])
callback: function Callback function with argument if any error.
Begins a new transaction.
connection.commit([callback])
callback: function callback function with argument if any error.
Commits the current transaction if there is one active. The Connector keeps track of the current transaction state on the server. When there isn't an active transaction, this method sends no commands to the server.
connection.rollback([callback])
callback: function Callback function with argument if any error.
Rolls back the current transaction if there is one active. The Connector keeps track of the current transaction state on the server. Where there isn't an active transaction, this method sends no commands to the server.
connection.changeUser(options[, callback])
options: JSON, subset of = database / charset / password / user
callback: function callback function with argument if any error.
Resets the connection and re-authenticates with the given credentials. This is the equivalent of creating a new connection with a new user, reusing the existing open socket.
connection.ping([callback])
callback: function Callback function with argument if any error.
Sends a one-byte packet to the server to check that the connection is still active.
connection.end([callback])
callback: function Callback function with argument if any error.
Closes the connection gracefully. That is, the Connector waits for current queries to finish their execution, then closes the connection.
connection.reset([callback])
callback: function Callback function with argument if any error.
reset the connection. Reset will:
rollback any open transaction
reset transaction isolation level
reset session variables
delete user variables
This command is only available for MariaDB >=10.2.4 or MySQL >= 5.7.3. the function will be rejected with the error "Reset command not permitted for server XXX" if the server version doesn't permit reset.
For previous MariaDB version, reset connection can be done using that do the same + redo authentication phase.
connection.isValid() → booleanReturns a 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.destroy()Closes the connection without waiting for any currently executing queries. These queries are interrupted. MariaDB logs the event as an unexpected socket close.
connection.escape(value) → StringThis function permits escaping a parameter properly, according to a parameter type, to avoid injection. See for escaping.
Escaping has some limitations:
doesn't permit parameters
this is less efficient compared to using standard conn.query(), it will stream data to socket, avoiding string concatenation and using memory unnecessary
escape per type:
boolean: explicit true or false
number: string representation. ex: 123 => '123'
Date: String representation using YYYY-MM-DD HH:mm:ss.SSS format
Escape is done for value without NO_BACKSLASH_ESCAPES that disable \ escaping (default); Escaping API are meant to prevent . However, privilege the use of and avoid building the command manually.
connection.escapeId(value) → StringThis function permits escaping an Identifier properly. See for escaping. Value will be enclosed by '`' character if content doesn't satisfy:
ASCII: [0-9,a-z,A-Z$_] (numerals 0–9, basic Latin letters, both lowercase and uppercase, dollar sign, underscore)
Extended: U+0080 .. U+FFFF and escaping '`' character if needed.
connection.pause()Pauses data reads.
connection.resume()Resumes data reads from a pause.
connection.serverVersion()Returns a string
Retrieves the version of the currently connected server. Throws an error when not connected to a server.
connection.importFile(options[, callback])
optionsJSON: > ** file: file path (mandatory) > ** database: database if different that current connection database (optional)callback function that returns an error if fails, nothing if success
Import sql file. If database is set, database will be use, then after file import, database will be reverted
ErrorWhen the Connector encounters an error, Promise returns an 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.
Example on console.log(error):
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 documentation.
eventsConnection object that inherits from the Node.js . Emits an error event when the connection closes unexpectedly.
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(callback)
callback: function Callback function with arguments (, ).
Creates a new object. Connection must be given back to pool with the connection.end() method.
Example:
pool.query(sql[, values][, callback])
sql: string | JSON SQL string or JSON object to supersede default connection options. When using JSON object, object must have an "sql" key. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array | object Placeholder values. Usually an array, but in cases of only one placeholder, it can be given as is.
callback
This is a shortcut to get a connection from the pool, execute a query, and release the connection.
pool.batch(sql, values[, callback])
sql: string | JSON SQL string or JSON object to supersede default connection options. When using JSON object, object must have an "sql" key. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array array of Placeholder values. Usually an array of array, but in cases of only one placeholder per value, it can be given as a single array.
This is a shortcut to get a connection from pool, execute a batch and release the connection.
pool.end([callback])
callback: function Callback function with argument ().
Closes the pool and underlying connections gracefully.
pool.escape(value) → StringThis is an alias for to escape parameters
pool.escapeId(value) → StringThis is an alias for to escape Identifier
pool.importFile(options[, callback])
options: > ** file: file path (mandatory) > ** database: database if different that current connection database (optional)callback function that returns an error if fails, nothing if success
Import SQL file. If a database is set, the database will be used, then after file import, the database will be reverted to the initial value.
Example:
Cluster handles multiple pools according to patterns and handles failover / distributed load (round-robin / random / ordered).
poolCluster.add(id, config)
id: string node identifier. example : 'master'
config: JSON to create pool.
Add a new Pool to the cluster.
Example:
poolCluster.remove(pattern)
pattern: string regex pattern to select pools. Example,"slave*"
remove and end pool(s) configured in the cluster.
poolCluster.end([callback])
callback: function Callback function with argument ().
Closes the pool cluster and underlying pools.
poolCluster.getConnection([pattern, ][selector, ]callback)
pattern: string regex pattern to select pools. Example,"slave*". default'*'
selector: string pools selector. Can be 'RR' (round-robin), 'RANDOM' or 'ORDER' (use in sequence = always use first pools unless fails). default to the cluster optiondefaultSelectorif set, 'RR' if not
Creates a new object. Connection must be given back to pool with the connection.end() method.
Example:
poolCluster eventsPoolCluster object inherits from the Node.js . 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)
poolCluster.of(pattern, selector) → FilteredPoolCluster
pattern: string regex pattern to select pools. Example,"slave*". default'*'
selector: string pools selector. Can be 'RR' (round-robin), 'RANDOM' or 'ORDER' (use in sequence = always use first pools unless fails). default to theReturns :
Creates a new object that is a subset of cluster.
Example:
filtered pool clusterfilteredPoolCluster.getConnection(callback) : Creates a new connection from pools that corresponds to pattern.
filteredPoolCluster.query(sql[, values][, callback]) : this is a shortcut to get a connection from pools that corresponds to pattern, execute a query and release connection.
checkNumberRange
when used in conjunction with decimalAsNumber, insertIdAsNumber, or bigIntAsNumber, if conversion to a number is not exact, the connector will throw an error (since 3.0.1)
function
version → String : Return library version.
defaultOptions(options) → Json : list options with default values
connection.rollback([callback]): Rolls back the current transaction, if any.
connection.changeUser(options [, callback]): Changes the current connection user.
connection.ping([callback]): Sends an empty packet to the server to check that connection is active.
connection.end([callback]): Gracefully closes the connection.
connection.reset([callback]): reset current connection state.
connection.isValid() → boolean: Checks that the connection is active without checking socket state.
connection.destroy(): Forces the connection to close.
connection.escape(value) → String: escape parameter
connection.escapeId(value) → String: escape identifier
connection.pause(): Pauses the socket output.
connection.resume(): Resumes the socket output.
connection.serverVersion(): Retrieves the current server version.
connection.importFile(options [, callback]) : import Sql file
events: Subscribes to connection error events.
pool.escape(value) → String: escape parameter
pool.escapeId(value) → String: escape identifier
pool.importFile(options [, callback]) : import Sql file
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 events: Subscribes to pool cluster events.
poolCluster.of(pattern, selector) → FilteredPoolCluster : return a subset of cluster.
port
Database server port number. Not used when using option socketPath
integer
3306
ssl
Enables TLS support. For more information, see the documentation.
mixed
database
Default database to use when establishing the connection.
string
socketPath
Permits connections to the database through the Unix domain socket or named pipe.
string
compress
Compresses the exchange with the database through gzip. This permits better performance when the database is not in the same location.
boolean
false
connectTimeout
Sets the connection timeout in milliseconds.
integer
1 000
socketTimeout
Sets the socket timeout in milliseconds after connection succeeds. A value of 0 disables the timeout.
integer
0
queryTimeout
Set maximum query time in ms (an error will be thrown if limit is reached). 0 or undefined meaning no timeout. This can be superseded for a query using timeout option
int
0
rowsAsArray
Returns result-sets as arrays, rather than JSON. This is a faster way to get results. For more information, see Query.
boolean
false
initializationTimeout
Pool will retry creating connection in loop, emitting 'error' event when reaching this timeout. In milliseconds
integer
acquireTimeout value
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
minDelayValidation
When asking a connection to pool, the pool will validate the connection state. "minDelayValidation" permits disabling this validation if the connection has been borrowed recently avoiding useless verifications in case of frequent reuse of connections. 0 means validation is done each time the connection is asked. (in ms)
integer
500
noControlAfterUse
After giving back connection to pool (connection.end) connector will reset or rollback connection to ensure a valid state. This option permit to disable those controls
boolean
false
resetAfterUse
When a connection is given back to pool, reset the connection if the server allows it (only for MariaDB version >= 10.2.22 /10.3.13). If disabled or server version doesn't allows reset, pool will only rollback open transaction if any
boolean
true
leakDetectionTimeout
Permit to indicate a timeout to log connection borrowed from pool. When a connection is borrowed from pool and this timeout is reached, a message will be logged to console indicating a possible connection leak. Another message will tell if the possible logged leak has been released. A value of 0 (default) meaning Leak detection is disable
integer
0
defaultSelector
default pools selector. Can be 'RR' (round-robin), 'RANDOM' or 'ORDER' (use in sequence = always use first pools unless fails)
string
'RR'
callback: function Callback function with arguments (error, results, metadata).Returns an Emitter object that can emit four different types of event:
error: Emits an Error object, when query failed.
columns: Emits when columns metadata from result-set are received (parameter is an array of Metadata fields).
data: Emits each time a row is received (parameter is a row).
end: Emits when the query ends (no parameter).
callback: function Callback function with arguments (error, results, metadata).callback either returns an [[#error|Error]] with results/metadata null or with error empty and results/metadata
remove all PREPARE statement
object with toSqlString function: String an escaped result of toSqlString
Array: list of escaped values. ex: [true, "o'o"] => ('true', 'o\'o')
geoJson: MariaDB transformation to corresponding geotype. ex: { type: 'Point', coordinates: [20, 10] } => "ST_PointFromText('POINT(20 10)')"
JSON: Stringification of JSON, or if permitSetMultiParamEntries is enable, key escaped as identifier + value
String: escaped value, (\u0000, ', ", \b, \n, \r, \t, \u001A, and \ characters are escaped with '')
code: The error code.callbackcallback: function Callback function with arguments (Error, Connection).
resolves with a filtered pool cluster object,
raises an Error.
insertIdAsNumber
Whether the query should return the last insert ID from the INSERT/UPDATE command as BigInt or Number. default return BigInt
boolean
false
decimalAsNumber
Whether the query should return a decimal as a number. If enabled, it might return approximate values.
boolean
false
bigIntAsNumber
Whether the query should return the BigInt data type as a number. If enabled, it might return approximate values.
boolean
user
User to access database.
string
password
User password.
string
host
IP address or DNS of the database server. Not used when using option socketPath.
string
acquireTimeout
Timeout to get a new connection from pool in ms.
integer
10000
connectionLimit
Maximum number of connection in pool.
integer
10
idleTimeout
Indicate idle time after which a pool connection is released. Value must be lower than . In seconds (0 means never release)
integer
acquire
This event emits a connection is acquired from pool.
connection
This event is emitted when a new connection is added to the pool. Has a connection object parameter
enqueue
This event is emitted when a command cannot be satisfied immediately by the pool and is queued.
release
This event is emitted when a connection is released back into the pool. Has a connection object parameter
error
When pool fails to create new connection after reaching initializationTimeout timeout
canRetry
When getting a connection from pool fails, can cluster retry with other pools
boolean
true
removeNodeErrorCount
Maximum number of consecutive connection fail from a pool before pool is removed from cluster configuration. Infinity means node won't be removed. Default to Infinity since 3.0, was 5 before
integer
Infinity
restoreNodeTimeout
delay before a pool can be reused after a connection fails. 0 = can be reused immediately (in ms)
integer
acquire
This event emits a connection is acquired from pool.
connection
This event is emitted when a new connection is added to the pool. Has a connection object parameter
enqueue
This event is emitted when a command cannot be satisfied immediately by the pool and is queued.
release
This event is emitted when a connection is released back into the pool. Has a connection object parameter
false
"localhost"
1800
1000
$ 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
});There are two different connection implementations: one, the default, uses Promise, and the other uses Callback, allowing for compatibility with the MySQL and mysql2 API's.
The documentation provided on this page is the promise API (default). If you want information on the Callback API, see the CALLBACK API.
Install the MariaDB Connector using npm
You can then use the Connector in your application code with the Promise API. For instance,
To use the Connector, you first need to install it on your system. The installation process for Promise and Callback API is managed with the same package through npm.
To use the Connector, you need to import the package into your application code.
Default behaviour for decoding / datatype for 2.x version and mysql/mysql2 drivers return a JavaScript object. BIGINT/DECIMAL values might not be in threturns range, resulting in approximate results.
Since 3.x version, driver has reliable default, returning:
DECIMAL => javascript String
BIGINT => javascript object
For compatibility with previous version or mysql/mysql driver, 4 options have been added to return BIGINT/DECIMAL as number, as previous defaults.
Previous options supportBigNumbers and bigNumberStrings still exist for compatibility but are now deprecated.
Other considerations
mysql has an experimental syntax permitting the use of ?? characters as placeholder to escape id. This isn't implemented in the MariaDB driver, permitting the same query syntax for and .
example:
has to use explicit escapeId:
Cluster configuration removeNodeErrorCount default to Infinity when mysql/mysql2 default to value 5. This avoids removing nodes without explicitly saying so.
It is recommended to activate the trace option in development. Since the driver is asynchronous, enabling this option permits saving initial stack when calling any driver methods. This allows having interesting debugging information: example:
The caller method and line are now in the error stack, permitting easy error debugging.
The problem is this error stack is created using that is super slow (hoping ). To give an idea, this slows down by 10% a query like 'select * from mysql.user LIMIT 1', so not recommended in production.
If Client and Server share the same timezone, default behavior (timezone='local') is the solution.
The problem resides when client and server don't share the same timezone.
The timezone option can have the following value:
'local' (default): connector doesn't do any conversion. If the database has a different timezone, there will be offset issues.
'auto': connector retrieves server timezone, and if client timezone differs from server, connector will set session timezone to client timezone
IANA timezone / offset, example 'America/New_York' or '+06:00'. Connector will set session timezone to indicated timezone. It is expected that this timezone corresponds to client tz.
Using 'auto' or setting specific timezone solves timezone correction. Please be careful for fixed timezone: Etc./GMT+12 = GMT-12:00 = -12:00 = offset -12. Etc./GMT have opposite sign!!
(Before 3.1, the connector was converting date to server timezone, but these were not correcting all timezone issues)
IANA timezone / offset
When using IANA timezone, the connector will set the connection timezone to the timezone. This can throw an error on connection if timezone is unknown by the server (see , timezone tables might be not initialized) If you are sure the server is using that timezone, this step can be skipped with the option skipSetTimezone.
If the timezone corresponds to JavaScript default timezone, then no conversion will be done.
Timezone setting recommendation
The best is to have the same timezone on client and database, then keep the 'local' default value.
If different, then either client or server has to convert the date. In general, it is best to use client conversion to avoid putting any unneeded stress on the database. Timezone has to be set to the IANA timezone corresponding to server timezone and disabled skipSetTimezone option since you are sure that the server has the corresponding timezone.
Example: The client uses 'America/New_York' by default, and server 'America/Los_Angeles'. Execute 'SELECT @@system_time_zone' on the server. That will give the server default timezone. The server can return a POSIX timezone like 'PDT' (Pacific Daylight Time). IANA timezone correspondence must be found (see ) and configure client-side. This will ensure DST (automatic daylight saving time change will be handled).
Connection details such as URL, username, and password are better hidden into environment variables. Using code like:
Then for example, run node.js setting those environment variables:
Another solution is using dotenv package. Dotenv loads environment variables from .env files into the process.env variable in Node.js:
Then configure dotenv to load all .env files:
with an .env file containing:
.env files must NOT be pushed into the repository, using .gitignore.
Alternatively, Node.js 20.0 introduced the experimental feature of using the node --env-file=.env syntax to load environment variables without the need for external dependencies. We can then simply write:
Assuming the presence of the same .env file as previously described.
For new projects, enabling option supportBigInt is recommended (It will be in a future 3.x version).
This option permits to avoid exact value for big integer (value > 2^53) (see )
Base:
: Creates a new connection.
: Creates a new Pool.
: Creates a new pool cluster.
: Import Sql file
Connection:
: Executes a query.
: Executes a query, returning an emitter object to stream rows.
: Prepares a query.
: Prepare and Executes a query.
Pool:
: Creates a new connection.
: Executes a query.
: Executes a batch
: Gracefully closes the connection.
PoolCluster
: Add a pool to cluster.
: Remove and end pool according to pattern.
: End cluster.
: Return a connection from cluster.
createConnection(options) → Promise
options: JSON/StringReturns a promise that:
resolves with a object,
Raises an .
Creates a new object.
Example:
Essential options list:
For more information, see the documentation.
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 server system variable. If you don't know it offhand, you can retrieve it from the server.
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:
It has a similar syntax on Windows:
createPool(options) → Pool
options: JSON/StringReturns a object,
Creates a new pool.
Example:
Pool options include that will be used when creating new connections.
Specific options for pools are:
createPoolCluster(options) → PoolCluster
options: JSONReturns a object,
Creates a new pool cluster. Cluster handle multiple pools, giving high availability / distributing load (using round robin / random / ordered).
Example:
Pool cluster options include that will be used when creating new pools.
Specific options for a pool cluster are:
importFile(options) → Promise
options: JSON/String + one additional optionsfileReturns a promise that:
resolves with an empty result,
raises an .
Import an sql file
Example:
version → StringReturns a String that is a library version. example '2.1.2'.
defaultOptions(options) → Json
options: JSON/String (non-mandatory)Returns a JSON value containing options default value.
Permits listing the default options that will be used.
connection.query(sql[, values]) -> Promise
sql: string | JSON SQL string or JSON object to supersede default connection options. When using JSON object, object must have a "sql" key. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array | object Placeholder values. Usually an array, but in cases of only one placeholder, it can be given as is.Returns a promise that:
Sends a query to a database and return a result as a Promise.
For instance, when using an SQL string:
Alternatively, you could use the JSON object:
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.stringify).
When streaming, objects that implement Readable are streamed automatically. But there are two server system variables that may interfere:
: The server must receive queries before reaching this timeout, which defaults to 30 seconds.
: This system variable defines the maximum amount of data the Connector can send to the server.
For instance,
In the case of streaming,
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: The number of rows affected by the operation
insertId: The auto-increment ID generated by the operation (for the first inserted row when multiple rows are inserted)
warningStatus: A flag indicating whether the query generated warnings
When executing a SELECT statement, the method returns the result-set as an array of JSON objects. Each object in the array represents a row from the result-set, with column names as property keys.
The result also includes a special non-enumerable meta property containing an array of information.
The following options can be set at either the query level or the connection level. When set at the connection level, they apply to all subsequent queries.
timeout
number, timeout in ms
Sets a timeout for query execution. Only available for MariaDB server >= 10.1.2.
The driver implements this using SET STATEMENT max_statement_time=<timeout> FOR <command>, which allows the server to cancel operations that exceed the specified timeout.
Important limitation: When using multiple statements (with the multipleStatements option enabled), only the first query will be subject to the timeout.
The implementation of max_statement_time is engine-dependent and may behave differently across storage engines. For example, with the Galera engine, commits ensure replication to other nodes is completed, which might exceed the timeout to maintain proper server state.
namedPlaceholders
boolean, default false
Enables the use of named placeholders instead of question mark placeholders. When enabled, the values parameter must be an object with keys matching the placeholder names in the query.
rowsAsArray
boolean, default false
Returns rows as arrays instead of objects, which can improve performance by 5-10% with local databases and reduces memory usage by avoiding the need to parse column metadata completely.
metaAsArray
boolean, default false
A compatibility option that causes the Promise to return an array [rows, metadata] instead of rows with a meta property. This option is primarily for mysql2 compatibility.
nestTables
boolean / string, default false
Helps resolve column name conflicts in joins by grouping data by table. When set to true, results are grouped by table name. When set to a string value, it's used as a separator between table name and column name.
With boolean value:
With string value:
dateStrings
boolean, default: false
Whether you want the Connector to retrieve date values as strings, rather than Date objects.
bigIntAsNumber
boolean, default: true
Whether the query should return JavaScript ES2020 for data type. This ensures having the expected value even for value > 2^53 (see range). This option can be set to query level, supplanting connection option supportBigInt value.
this option is for compatibility for driver version < 3
decimalAsNumber
boolean, default: false
Whether the query should return decimal as Number. If enabled, this might return approximate values.
typeCast
Experimental
function(column, next)
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:
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
When using typeCast, additional function are available on Column, in order to decode value :
string(): string : decode // value
buffer(): Buffer : decode / value
float(): float : decode value
connection.queryStream(sql[, values]) → Emitter
sql: string | JSON SQL string value or JSON object to supersede default connections options. JSON objects must have an"sql"property. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array | object Defines placeholder values. This is usually an array, but in cases of only one placeholder, it can be given as a string.Returns an Emitter object that emits different types of events:
When using the query() method, the Connector returns the entire result-set with all its data in a single call. While this works well for small result sets, it can become problematic for queries returning millions of rows, potentially causing memory issues.
The queryStream() method solves this by using Node.js's event-driven architecture to process rows one by one, significantly reducing memory usage for large result sets.
Important: The stream handles backpressure automatically, pausing the socket when data handling takes time to prevent Node.js socket buffers from growing indefinitely. If you're using a pipeline and your data handling throws an error, you must explicitly call queryStream.close() to prevent connection hangs.
There are several ways to implement streaming:
Using for-await-of (Node.js 10+)
The simplest approach using modern JavaScript syntax:
Using event listeners
Traditional Node.js event-based approach:
Using Node.js streams
For advanced use cases, you can integrate with Node.js streams API:
connection.prepare(sql) → Promise
sql: string | JSON SQL string value or JSON object to supersede default connections options. JSON objects must have an"sql"property. For instance,{ dateStrings: true, sql: 'SELECT now()' }Returns a promise that :
resolves with a object.
This permits to a command that permits to be executed many times. After use, prepare.close() method MUST be call, in order to properly close object.
Public variables :
id: Prepare statement Identifier
query: sql command
database: database it applies to.
Public methods :
execute(values) → Promise
values: array | object Defines placeholder values. This is usually an array, but in cases of only one placeholder, it can be given as a string.Returns a promise that :
resolves with a JSON object for update/insert/delete or a object for a result-set.
rejects with an .
executeStream(values) → Promise
values: array | object Defines placeholder values. This is usually an array, but in cases of only one placeholder, it can be given as a string.Returns an Emitter object that emits different types of events:
error: Emits an object when the query fails. (No
"end"event will then be emitted).
This is the equivalent of queryStream using execute.
When using the execute() 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 the data into memory, you can use the executeStream() method, which uses the event drive architecture to process rows one by one, which allows you to avoid putting too much strain on memory.
You may want to consider updating the server system variable. The resultSet must be totally received before this timeout, which defaults to 30 seconds.
for-await-of
simple use with for-await-of only available since Node.js 10 (note that this must be use within async function) :
Events
close() → void
This closes the prepared statement. Each time a Prepared object is used, it must be closed.
In case prepare cache is enabled (having option prepareCacheLength > 0 (default)), Driver will either really close Prepare or keep it in cache.
connection.execute(sql[, values]) → Promise
sql: string | JSON SQL string or JSON object to supersede default connection options. When using JSON object, an object must have a "sql" key. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array | object Placeholder values. Usually an array, but in cases of only one placeholder, it can be given as is.Returns a promise that :
This is quite similar to method, with a few differences: Execute will in fact + + command.
It makes sense to use this only if the command often is used and if prepare cache is enabled (default). If a PREPARE result is already in cache, only The command is executed. MariaDB server 10.6 even avoids resending result-set metadata if not changed since, permitting even faster results.
connection.batch(sql, values) → Promise
sql: string | JSON SQL string value or JSON object to supersede default connections options. JSON objects must have an"sql"property. For instance,{ dateStrings: true, sql: 'SELECT now()' }
values: array Array of parameter (array of array or array of objects if using named placeholders).Returns a promise that :
Implementation depends on the server type and version. for MariaDB server version 10.2.7+, the implementation uses 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 a query cannot be re-writen will execute a query for each value.
An option fullResult permit to indicate if user wants to retrieve individual batch results (to retrieve generated ids). This might change the performance of bathing if set, depending on a server version (for server 11.5.1 and above with , bulk will be use, or pipelining if not)
For instance,
fullResult optionBy default, batch operations aggregate results, combining all individual operations into a single result. You can use the fullResult: true option to retrieve individual results for each parameter set.
When to use fullResult
The fullResult option is particularly useful when:
You need to know which specific parameter sets succeeded or failed
You need to access individual insertId values for each inserted row.
Performance considerations
For MariaDB servers that support it (version 10.2.7+), the connector will use the optimized COM_STMT_BULK_EXECUTE protocol for better performance when possible. The fullResult option with bulk protocol requires 11.5.1.
connection.beginTransaction() → PromiseReturns a promise that :
resolves (no argument)
rejects with an .
Begins a new transaction.
connection.commit() → PromiseReturns a promise that :
resolves (no argument)
rejects with an .
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.release() → PromiseWhen a .connection comes from pool, only connection.release() is an async method returning an empty promise success. This function will never throw an error. The default behavior is that if there is a transaction still open, a rollback command will be issued, and the connection will be released to pool.
2 options might interfere:
When resetAfterUse set, the connection will completely be reset like a fresh connection
noControlAfterUse when set, no control (rollback or reset) will be done on release
connection.rollback() → PromiseReturns a promise that :
resolves (no argument)
Rejects with an .
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.
connection.changeUser(options) → Promise
options: JSON, subset of atabase/charset = database/charset / password/userReturns a promise that :
resolves without result
Rejects with an .
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.
connection.ping() → PromiseReturns a promise that :
resolves (no argument)
rejects with an .
Sends a packet to the database containing one byte to check that the connection is still active.
connection.reset() → PromiseReturns a promise that :
resolves (no argument)
rejects with an .
reset the connection. Reset will:
rollback any open transaction
reset transaction isolation level
reset session variables
delete user variables
This command is only available for MariaDB >=10.2.4 or MySQL >= 5.7.3. the function will be rejected with the error "Reset command not permitted for server XXX" if the server version doesn't permit reset.
For previous MariaDB version, reset connection can be done using that do the same + redo authentication phase.
connection.isValid() → booleanReturns a 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() → PromiseReturns a promise that :
resolves (no argument)
rejects with an .
Closes the connection gracefully, after waiting for any currently executing queries to finish.
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.
connection.escape(value) → StringThis function permits escaping a parameter properly according to a parameter type to avoid injection. See for escaping.
Escaping has some limitations:
doesn't permit parameters
this is less efficient compared to using standard conn.query(), that will stream data to socket, avoiding string concatenation and using memory unnecessary
escape per type:
boolean: explicit true or false
number: string representation. ex: 123 => '123'
Date: String representation using YYYY-MM-DD HH:mm:ss.SSS format
Escape is done for value without NO_BACKSLASH_ESCAPES that disable \ escaping (default); Escaping API are meant to prevent . However, privilege the use of and avoid building the command manually.
connection.escapeId(value) → StringThis function permits escaping an Identifier properly. See for escaping. Value will be enclosed by '`' character if content doesn't satisfy:
ASCII: [0-9,a-z,A-Z$_] (numerals 0–9, basic Latin letters, both lowercase and uppercase, dollar sign, underscore)
Extended: U+0080 .. U+FFFF and escaping '`' character if needed.
connection.pause()Pauses data reads.
connection.resume()Resumes data reads from a pause.
connection.serverVersion()Returns a string
Retrieves the version of the currently connected server. Throws an error when not connected to a server.
connection.importFile(options) → Promise
optionsJSON: > ** file: file path (mandatory) > ** database: database if different that current connection database (optional)Returns a promise that :
resolves without a result
rejects with an .
Import SQL file. If a database is set, the database will be used, then after file import, the database will be reverted to the previous value.
ErrorWhen the Connector encounters an error, Promise returns an 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 corresponding to the MariaDB/MySQL error code.
sqlState: The SQL state code following the ANSI SQL standard.
When working with the MariaDB connector, implementing proper error handling is crucial for building robust applications. Here are some recommended practices:
1. Always use try/catch with async/await
2. Check for specific error codes
The connector provides detailed error information that you can use to handle specific error scenarios:
3. Distinguish between fatal and non-fatal errors
The fatal property indicates whether the connection is still usable:
Here's an example of what an error object might look like when logged:
When the trace option is enabled, errors include the original stack trace, which helps identify where in your code the query was executed.
For a complete list of error codes and their meanings, see the documentation.
eventsConnection object that inherits from the Node.js . Emits an error evthe ent when the connection closes unexpectedly.
A connection pool is a cache of database connections maintained so that connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database.
Each time a connection is requested, if the pool contains an available connection, the pool will validate the connection by exchanging an empty MySQL packet with the server to ensure the connection is still valid, then provide the connection.
The pool reuses connections intensively to improve performance. This validation is only performed if a connection has been idle for a period (specified by the minDelayValidation option, which defaults to 500ms).
If no connection is available, the request will be queued until either:
A connection becomes available (through creation or release)
The connection timeout (acquireTimeout) is reached
When a connection is released back to the pool, any remaining transactions will be automatically rolled back to ensure a clean state for the next use.
pool.getConnection() → Promise
Returns a promise that: > * resolves with a object
rejects with an
Retrieves a connection from the pool. If the pool is at its connection limit, the promise will wait until a connection becomes available or the acquireTimeout is reached.
Example: Using a pooled connection with transactions
pool.query(sql[, values]) → Promise
sql: string | JSON SQL string or JSON object with query options
values: array | object Placeholder valuesReturns a promise that:
resolves with query results (same as )
Executes a query using a connection from the pool. The connection is automatically acquired and released, making this method ideal for simple queries.
Example: Simple query with error handling
Example: Using query options
pool.batch(sql, values) → Promise
sql: string | JSON SQL string or JSON object with query options
values: array Array of parameter sets (array of arrays or array of objects for named placeholders)Returns a promise that:
resolves with batch operation results
Executes a batch operation using a connection from the pool. The pool automatically handles connection acquisition and release.
For MariaDB server version 10.2.7+, this implementation uses a dedicated bulk protocol for improved performance.
Example: Batch insert with generated IDs
pool.end() → PromiseReturns a promise that:
resolves when all connections are closed
rejects with an if closing fails
Gracefully closes all connections in the pool and ends the pool. This should be called when your application is shutting down to ensure all database resources are properly released.
Example: Application shutdown handler
pool.escape(value) → StringThis is an alias for to escape parameters when building queries manually.
Example:
pool.escapeId(value) → StringThis is an alias for to escape identifiers like table or column names.
Example:
pool.importFile(options) → Promise
options: > *file: file path (mandatory)
database: database if different from current connection database (optional)Returns a promise that:
Imports an SQL file. If a database is specified, it will be used for the import and then reverted to the original database afterward.
Example: Import a database dump
The pool object inherits from Node.js and emits the following events:
acquireEmitted when a connection is acquired from the pool.
connectionEmitted when a new connection is created within the pool.
releaseEmitted when a connection is released back to the pool.
errorEmitted when an error occurs in the pool, such as failure to create a connection.
The pool provides several methods to monitor its state:
Right-size your connection pool:
Set connectionLimit based on your application's concurrency needs and database server capacity
Too few connections can create bottlenecks
Too many connections can overload the database server
A pool cluster manages multiple database connection pools and provides high availability and load balancing capabilities. It allows your application to:
Connect to multiple database servers (for primary/replica setups)
Automatically handle failover if a database server goes down
Distribute queries across multiple servers
Group servers by pattern for targeted operations
The cluster manages a collection of connection pools, each identified by a name. You can select pools using pattern matching and specify different load balancing strategies (selectors) to determine which pool to use for each connection request.
When a connection fails, the cluster can automatically retry with another pool matching the same pattern. If a pool fails consistently, it can be temporarily blacklisted or even removed from the cluster configuration.
createPoolCluster(options) → PoolCluster
options: JSONReturns a object
Creates a new pool cluster to manage multiple database connection pools.
Example: Creating a basic primary/replica setup
poolCluster.add(id, config)
id: string node identifier. Example:'primary','replica1'
config: JSON to create the poolReturns: void
Adds a new connection pool to the cluster with the specified identifier and configuration.
Example: Adding nodes with descriptive identifiers
poolCluster.remove(pattern)
pattern: string Regex pattern to select pools. Example:'replica*'Returns: void
Removes and ends all pools whose identifiers match the specified pattern.
Example: Removing nodes from the cluster
poolCluster.getConnection([pattern], [selector]) → Promise
pattern: string Regex pattern to select pools. Default:'*'(all pools)
selector: string Selection strategy: 'RR' (round-robin), 'RANDOM', or 'ORDER'. Default: value of thedefaultSelectoroptionReturns a promise that:
Gets a connection from a pool in the cluster that matches the pattern using the specified selection strategy.
Example: Using different selectors for different connection patterns
Example: Handling failover gracefully
poolCluster.of(pattern, [selector]) → FilteredPoolCluster
pattern: string Regex pattern to select pools. Example:'replica*'
selector: string Selection strategy: 'RR' (round-robin), 'RANDOM', or 'ORDER'Returns a object
Creates a new filtered pool cluster that only includes pools matching the specified pattern. This allows you to create specialized interfaces for different database roles.
Example: Creating dedicated interfaces for read and write operations
poolCluster.end() → PromiseReturns a promise that:
resolves when all pools in the cluster are closed
rejects with an if closing fails
Gracefully closes all connection pools in the cluster.
Example: Application shutdown with clustered connections
A filtered pool cluster is a subset of the main cluster that only includes pools matching a specific pattern. It provides a simplified interface for working with logically grouped database nodes.
filteredPoolCluster.getConnection() → PromiseReturns a promise that:
resolves with a object
rejects with an
Gets a connection from one of the pools in the filtered cluster using the selector specified when the filtered cluster was created.
Example:
filteredPoolCluster.query(sql[, values]) → Promise
sql: string | JSON SQL string or JSON object with query options
values: array | object Placeholder valuesReturns a promise that:
resolves with query results
Shorthand method to get a connection from the filtered cluster, execute a query, and release the connection.
Example:
The pool cluster inherits from Node.js and emits the following events:
removeEmitted when a node is removed from the cluster configuration. This happens when a node fails to connect more than removeNodeErrorCount times (if this option is defined).
The pool cluster supports three different selection strategies for choosing which database node to use:
Round-Robin ('RR'): Uses pools in rotation, ensuring an even distribution of connections.
Random ('RANDOM'): Selects a random pool for each connection request.
Order ('ORDER'): Always tries pools in sequence, using the first available one. Useful for primary/fallback setups.
Use meaningful node identifiers:
Choose clear identifiers that indicate the node's role (e.g., 'primary', 'replica1')
This makes pattern matching more intuitive and maintenance easier
Implement role-based access with patterns:
checkNumberRange
when used in conjunction of decimalAsNumber, insertIdAsNumber or bigIntAsNumber, if conversion to number is not exact, connector will throw an error (since 3.0.1)
function
version → String: Return library version.
defaultOptions(options) → Json: List options with default values
connection.batch(sql, values) → Promise: Fast batch processing.
connection.beginTransaction() → Promise: Begins a transaction.
connection.commit() → Promise: Commits the current transaction, if any.
connection.release() → Promise: Release connection to pool if connection comes from pool.
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.escape(value) → String: Escape parameter
connection.escapeId(value) → String: Escape identifier
connection.pause(): Pauses the socket output.
connection.resume(): Resumes the socket output.
connection.serverVersion(): Retrieves the current server version.
connection.importFile(options) → Promise: Import Sql file
events: Subscribes to connection error events.
pool.escape(value) → String: Escape parameter
pool.escapeId(value) → String: Escape identifier
pool.importFile(options) → Promise: Import Sql file
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.of(pattern, selector) → FilteredPoolCluster: Return a subset of cluster.
poolCluster events: Subscribes to pool cluster events.
port
Database server port number. Not used when using the option socketPath
integer
3306
ssl
Enables TLS support. For more information, see the documentation.
mixed
database
Default database to use when establishing the connection.
string
socketPath
Permits connections to the database through the Unix domain socket or named pipe.
string
compress
Compresses the exchange with the database through gzip. This permits better performance when the database is not in the same location.
boolean
false
connectTimeout
Sets the connection timeout in milliseconds.
integer
1000
socketTimeout
Sets the socket timeout in milliseconds after connection succeeds. A value of 0 disables the timeout.
integer
0
queryTimeout
Set maximum query time in ms (an error will be thrown if limit is reached). 0 or undefined meaning no timeout. This can be superseded for a query using the option
int
0
rowsAsArray
Returns result-sets as arrays, rather than JSON. This is a faster way to get results. For more information, see Query.
boolean
false
logger
Configure logger. For more information, see the documentation.
mixed
initializationTimeout
Pool will retry creating connection in loop, emitting 'error' event when reaching this timeout. In milliseconds.
integer
acquireTimeout value
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
minDelayValidation
When asking a connection to pool, the pool will validate the connection state. "minDelayValidation" permits disabling this validation if the connection has been borrowed recently avoiding useless verifications in case of frequent reuse of connections. In milliseconds. 0 means validation is done each time the connection is asked.
integer
500
noControlAfterUse
After giving back connection to pool (connection.end) connector will reset or rollback connection to ensure a valid state. This option permit to disable those controls
boolean
false
resetAfterUse
When a connection is given back to pool, reset the connection if the server allows it (only for MariaDB version >= 10.2.22 /10.3.13). If disabled or server version doesn't allows reset, pool will only rollback open transaction if any
boolean
true before version 3, false since
leakDetectionTimeout
Permit to indicate a timeout to log connection borrowed from pool. When a connection is borrowed from pool and this timeout is reached, a message will be logged to console indicating a possible connection leak. Another message will tell if the possible logged leak has been released. In milliseconds. 0 means leak detection is disabled.
integer
0
pingTimeout
Validation timeout (ping) for checking an connection not used recently from pool. In milliseconds.
integer
500
defaultSelector
default pools selector. Can be 'RR' (round-robin), 'RANDOM' or 'ORDER' (use in sequence = always use first pools unless fails)
string
'RR'
resolves with a JSON object for update/insert/delete or a result-set object for a result-set.
rejects with an Error.
BLOBtype: Shows the column type as a String value. For more information on the relevant values, see field-type.js
columnType: Shows the column type as an integer 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.
tiny(): int : decode value
short(): int : decode value
int(): int : decode value
long(): bigint : decode value
decimal(): string : decode value
date(): date : decode value
datetime(): date : decode / value
geometry(): geojson : decode value
error: Emits an Error object when the query fails. (No "end" event will then be emitted).
fields: Emits when column metadata from the result-set are received (the parameter is an array of Metadata fields).
data: Emits each time a row is received (parameter is a row).
end: Emits when the query ends (no parameter). > a method: close(): permits closing stream (since 3.0)
parameters: parameter array information.columns: column array information.
end: Emits when the query ends (no parameter). > a method: close(): permits closing stream (since 3.0)
resolves with a JSON object for update/insert/delete or a result-set object for a result-set.
rejects with an Error.
resolves with a JSON object.
rejects with an Error.
remove all PREPARE statement
object with toSqlString function: String an escaped result of toSqlString
Array: list of escaped values. ex: [true, "o'o"] => ('true', 'o\'o')
geoJson: MariaDB transformation to corresponding geotype. ex: { type: 'Point', coordinates: [20, 10] } => "ST_PointFromText('POINT(20 10)')"
JSON: Stringification of JSON, or if permitSetMultiParamEntries is enabled, key escaped as identifier + value
String: escaped value, (\u0000, ', ", \b, \n, \r, \t, \u001A, and \ characters are escaped with '')
code: The error code as a string identifier.rejects with an Error
rejects with an Error
rejects with an Error
Start with a connection limit of 10–20 and adjust based on performance testing
Handle connection leaks:
Always release connections:
Use connection validation wisely:
Prefer pool.query() for simple operations:
For single queries, use pool.query() instead of manually acquiring and releasing connections
Only use getConnection() when you need to maintain context across multiple queries
Implement proper error handling:
Listen for 'error' events on the pool
Implement reconnection strategies for fatal errors
Consider using a circuit breaker pattern for persistent database issues
Close the pool during application shutdown:
Always call pool.end() when your application terminates
Use process signal handlers (SIGINT, SIGTERM) to ensure proper cleanup
resolves with a Connection object
rejects with an Error
rejects with an Error
Use appropriate selectors for different scenarios:
'ORDER' for high availability with failover (tries primary first, then fallbacks)
'RR' for load balancing across equivalent nodes (like replicas)
'RANDOM' when pure distribution is needed
Configure node removal thresholds appropriately:
Monitor removed nodes:
Implement graceful degradation:
Design your application to function with reduced capabilities when some nodes are unavailable.
Use fallback strategies when specific node patterns become unavailable
Always close the cluster during application shutdown:
Call cluster.end() to properly release all resources
Use process signal handlers to ensure cleanup.
insertIdAsNumber
Whether the query should return last insert id from INSERT/UPDATE command as BigInt or Number. default return BigInt
boolean
false
decimalAsNumber
Whether the query should return decimal as Number. If enabled, this might return approximate values.
boolean
false
bigIntAsNumber
Whether the query should return BigInt data type as Number. If enabled, this might return approximate values.
boolean
user
User to access database.
string
password
User password.
string
host
IP address or DNS of the database server. Not used when using option socketPath.
string
acquireTimeout
Timeout to get a new connection from pool. In order to have connection error information, must be higher than connectTimeout. In milliseconds.
integer
10000
connectionLimit
Maximum number of connection in pool.
integer
10
idleTimeout
Indicate idle time after which a pool connection is released. Value must be lower than . In seconds. 0 means never release.
integer
canRetry
When getting a connection from pool fails, can cluster retry with other pools
boolean
true
removeNodeErrorCount
Maximum number of consecutive connection fail from a pool before pool is removed from cluster configuration. Infinity means node won't be removed. Default to Infinity since 3.0, was 5 before
integer
Infinity
restoreNodeTimeout
delay before a pool can be reused after a connection fails. 0 = can be reused immediately (in ms)
integer
false
"localhost"
1800
1000
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 {
conn.release();
}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();
}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
})conn.end()
.then(() => {
//connection has ended properly
})
.catch(err => {
//connection was closed but not due of current end command
})try {
// 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`);
});// 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]);
}user
User to access database
string
password
User password
string
host
IP address or DNS of database server. Not used when using the socketPath option
string
Options can be set as a JSON Object, or a using a String.
String format is :
mariadb://[<user>[:<password>]@]<host>[:<port>]/[<db>[?<opt1>=<value1>[&<optx>=<valuex>]]]
example:
Driver permits mapping the logs to an external logger. There are 4 caller functions:
network(string): called for each network exchanges.
query(string): called for each command
error(Error): called for each error.
warning(string): called for each warning (configuration warning, leak message), default to console.log if not set.
if setting one function, function will be used for all loggers. (i.e., logger: console.log === logger: { network: console.log, query: console.log, error: console.log})
2 options defined what will be logged : debugLen and logParam. query and network logs are truncated to debugLen length (default to 256). truncated trace finish by '...': example :
Example:
The Connector can encrypt data during transfer using the Transport Layer Security (TLS) protocol. TLS/SSL allows for transfer encryption, and can optionally use identity validation for the server and client.
The term SSL (Secure Sockets Layer) is often used interchangeably with TLS, although strictly-speaking the SSL protocol is the predecessor of TLS, and is not implemented as it is now considered insecure.
There are two different kinds of SSL authentication:
One-Way SSL Authentication: The client verifies the certificate of the server. This allows you to encrypt all exchanges and make sure that you are connecting to the expected server, (to avoid a man-in-the-middle attack).
Two-Way SSL Authentication The client verifies the certificate of the server, the server verifies the certificate of the client. This is also called mutual authentication or client authentication. When using this system, the client also requires a dedicated certificate.
In order to use SSL, you need to ensure that the MariaDB Server is correctly configured. You can determine this using the have_ssl system variable.
A value of NO indicates that MariaDB was compiled without support for TLS. DISABLED means that it was compiled with TLS support, but it's currently turned off. In order to use SSL with the Connector, the server must return YES, indicating that TLS support is available and turned on. For more information, see the documentation.
Enabling the on the server, the Connector uses one-way SSL authentication to connect to the server. Additionally, it's recommended that you also configure your users to connect through SSL. This ensures that their accounts can only be used with an SSL connection.
For , use the REQUIRE SSL option for one-way SSL authentication and the REQUIRE X509 option for two-way SSL authentication. For more information, see the documentation.
Now when this user attempts to connect to MariaDB without SSL, the server rejects the connection.
ssl: boolean/JSON object.
JSON object:
checkServerIdentity
function(servername, cert) to replace SNI default function
Function
minDHSize
Minimum size of the DH parameter in bits to accept a TLS connection
number
1024
pfx
Optional PFX or PKCS12 encoded private key and certificate chain. Encrypted PFX will be decrypted with passphrase if provided
*string / string[] / Buffer / Buffer[] / Object[]
The Connector uses the Node.js implementation of TLS. For more information, see the Node.js TLS API documentation.
By default, Node.js trusts the well-known root Certificate Authorities (CA), based on Mozilla. For a complete list, (including the popular and free Let's Encrypt), see the CA Certificate List.
When using a certificate signed with a certificate chain from a root CA known to Node.js, the only configuration you need to do is enable the ssl option.
A certificate chain is a list of certificates that were issued from the same Certification Authority hierarchy. In order for any certificate to be validated, all certificates in the chain have to be validated.
In cases where the Connector does not trust intermediate or root certificates, the Connector rejects the connection and issues an error.
Certificates can provide hostname verification to the driver. By default, this is done against the certificate's subjectAlternativeName DNS name field.
When the server certificate is signed using the certificate chain that uses a root CA known in the JavaScript trust store, setting the ssl option enables one-way SSL authentication.
For example,
Since MariaDB 11.4, server supports "zero configuration ssl" that permits avoiding having to set any other information than ssl: true.
Previously to this version or using non-MariaDB server, when the server uses a self-signed certificate or uses an intermediate certificate, there are two different possibilities:
In non-production environments, you can tell the Connector to trust all certificates by setting rejectUnauthorized to false. Do NOT use this in production.
A more secure alternative is to provide the certificate chain to the Connector.
In situations where you don't like the default TLS protocol or cipher or where you would like to use a specific version, you force he Connector to use the one you want using the secureProtocol and cipher options.
For instance, say you want to connect using TLS version 1.2:
For more information on what's available, see possible protocol values.
Mutual SSL authentication or certificate-based mutual authentication refers to two parties authenticating each other by verifying the provided digital certificates. This allows both parties to be assured of the other's identity. In order to use mutual authentication, you must set the REQUIRE X509 option in the GRANT statement. For instance,
This option causes the server to ask the Connector for a client certificate. If the user is not set with REQUIRE X509, the server defaults to one-way authentication
When using mutual authentication, you need a certificate, (and its related private key), for the Connector as well as the server. If the Connector doesn't provide a certificate and the user is set to REQUIRE X509, the server returns a basic Access denied for user message.
In the event that you would like to see how users are defined, you can find this information by querying the mysql.user table on the server. For instance, say you wanted information on the johnSmith user.
You can test it by creating a user with REQUIRE X509 for testing:
Then use its credentials in your application:
Keystores allow you to store private keys and certificate chains encrypted with a password to file. For instance, using OpenSSL you can generate a keystore using PKCS12 format:
You can then use the keystore in your application:
charset
Protocol character set used with the server. Connection collation will be the default collation associated with charset. It's mainly used for micro-optimizations. The default is often sufficient.
string
UTF8MB4
collation
(used in replacement of charset) Permit to defined collation used for connection. This will defined the charset encoding used for exchanges with database and defines the order used when comparing strings. It's mainly used for micro-optimizations
string
UTF8MB4_UNICODE_CI
dateStrings
Whether to retrieve dates as strings or as Date objects.
boolean
In some cases, the server is only available through an SSH tunnel. (This is, of course, not a recommended solution for production)
The option stream permit defined a tunnel. stream function has a callback (optional parameters: error, stream).
Example using tunnel-ssh:
Clients verify certificate SAN (subject alternative names) and CN to ensure that the certificate corresponds to the hostname. If the certificate's SAN/CN does not correspond to the host option, it returns an error such as:
To fix this, correct the host value to correspond to the host identified in the certificate.
Since Node.js 12, the minimum TLS version is set to 1.2. MariaDB server can be built with many different SSL libraries, the old version supporting only TLS up to 1.1. The error "1976:error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol" can occur if MariaDB SSL implementation doesn't support TLSv1.2. This can be solved by :
Server side: update MariaDB to a recent version
Client side: permit a lesser version with "tls.DEFAULT_MIN_VERSION = 'TLSv1.1';" or with connection configuration: using option `ssl: { secureProtocol: 'TLSv1_1_method' }'
const 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({
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""localhost"
port
Database server port number
integer
3306
database
Default database to use when establishing the connection
string
socketPath
Permit connecting to the database via Unix domain socket or named pipe, if the server allows it
string
compress
Compress exchanges with database using gzip. This can give you better performance when accessing a database in a different location.
boolean
false
connectTimeout
Connection timeout in milliseconds (default changed from 10000 to 1000 in 2.5.6)
integer
1000
socketTimeout
Socket timeout in milliseconds after the connection is established
integer
0
rowsAsArray
boolean
false
maxAllowedPacket
permit to indicate server global variable value to ensure efficient batching. default is 4Mb. see batch documentation
integer
4196304
insertIdAsNumber
Whether the query should return last insert id from INSERT/UPDATE command as BigInt or Number. default return BigInt
boolean
false
decimalAsNumber
Whether the query should return decimal as Number. If enable, this might return approximate values.
boolean
false
bigIntAsNumber
Whether the query should return BigInt data type as Number. If enable, this might return approximate values.
boolean
false
logger
Permit custom logger configuration. For more information, see the logger option documentation.
mixed
prepareCacheLength
Define prepare LRU cache length. 0 means no cache
int
256
fullResult
indicate if user wants to retrieve individual batch results (in order to retrieve generated ids). This might change the performance of batching if set, depending on server version: if set, for server 11.5.1 and above, bulk will be use, pipelining if not
boolean
key
Optional private keys in PEM format. Encrypted keys are decrypted with passphrase if provided
*string / string[] / Buffer / Buffer[] / Object[]
passphrase
Optional shared passphrase used for a single private key and/or a PFX
string
cert
Optional cert chains in PEM format. One cert chain should be provided per private key
string / string[] / Buffer / Buffer[]
ca
Optionally override the trusted CA certificates. Default is to trust the well-known CAs curated by Mozilla. For self-signed certificates, the certificate is its own CA, and must be provided
string / string[] / Buffer / Buffer[]
ciphers
Optional cipher suite specification, replacing the default
string
honorCipherOrder
Attempt to use the server's cipher suite preferences instead of the client's
boolean
ecdhCurve
A string describing a named curve or a colon separated list of curve NIDs or names, for example P-521:P-384:P-256, to use for ECDH key agreement, or false to disable ECDH. Set to auto to select the curve automatically
string
tls.DEFAULT_ECDH_CURVE
clientCertEngine
Optional name of an OpenSSL engine which can provide the client certificate
string
crl
Optional PEM formatted CRLs (Certificate Revocation Lists)
string / string[] / Buffer / Buffer[]
dhparam
Diffie Hellman parameters, required for Perfect Forward Secrecy
string / Buffer
secureProtocol
Optional SSL method to use, default is "SSLv23_method"
string
false
debug
Logs all exchanges with the server. Displays in hexa.
boolean
false
debugLen
String length of logged message / error or trace
int
256
logParam
indicate if parameters must be logged by query logger.
boolean
false
foundRows
When enabled, the update number corresponds to update rows. When disabled, it indicates the real rows changed.
boolean
true
multipleStatements
Allows you to issue several SQL statements in a single quer() call. (That is, INSERT INTO a VALUES('b'); INSERT INTO c VALUES('d');).
This may be a security risk as it allows for SQL Injection attacks.
boolean
false
namedPlaceholders
Allows the use of named placeholders.
boolean
false
permitLocalInfile
Allows the use of LOAD DATA INFILE statements.
Loading data from a file from the client may be a security issue, as a man-in-the-middle proxy server can change the actual file the server loads. Being able to execute a query on the client gives you access to files on the client.
boolean
false
timezone
Forces use of the indicated timezone, rather than the current Node.js timezone. This has to be set when database timezone differ from Node.js timezone. Possible values are IANA time zone (ex: 'America/New_York')
string
nestTables
Presents result-sets by table to avoid results with colliding fields. See the query() description for more information.
boolean
false
pipelining
Sends queries one by one without waiting on the results of the previous entry. For more information, see Pipelining
boolean
true
trace
Adds the stack trace at the time of query creation to the error stack trace, making it easier to identify the part of the code that issued the query. Note: This feature is disabled by default due to the performance cost of stack creation. Only turn it on when you need to debug issues.
boolean
false
typeCast
Allows you to cast result types.
function
connectAttributes
Sends information, (client name, version, operating system, Node.js version, and so on) to the . When enabled, the Connector sends JSON attributes in addition to the defaults.
boolean/json
false
metaAsArray
Compatibility option causes Promise to return an array object, [rows, metadata] rather than the rows as JSON objects with a meta property.
boolean
false
permitSetMultiParamEntries
Compatibility option to permit setting multiple value by a JSON object to replace one question mark. key values will replace the question mark with format like key1=val,key2='val2'. Since it doesn't respect the usual prepared statement format that one value is for one question mark, this can lead to incomprehension, even if badly use to possible injection. this only works using query function (not compatible with batch and execute functions)
boolean
false
sessionVariables
Permit to set session variables when connecting. Example: sessionVariables:{'idle_transaction_timeout':10000}
json
initSql
When a connection is established, permit to execute commands before using connection
*string
array*
bulk
disabled bulk command in batch
boolean
permitConnectionWhenExpired
Permit a user with expired password to connect. Only possible operation in this case will be to change password ('SET PASSWORD=PASSWORD('XXX')')
boolean
false
forceVersionCheck
Force server version check by explicitly using SELECT VERSION(), not relying on server initial packet. Since version 2.2.0
boolean
false
checkDuplicate
Indicate to throw an exception if result-set will not contain some data due to having duplicate identifier.
JSON cannot have multiple identical key, so query like SELECT 1 as i, 2 as i cannot result in { i:1, i:2 }, 'i:1' would be skipped.
When checkDuplicate is enable (default) driver will throw an error if some data are skipped. Duplication error can be avoided by multiple ways, like using unique aliases or using options rowsAsArray / nestTables for example
Since version 2.3.0
boolean
true
arrayParenthesis
Indicate if array are included in parenthesis. This option permit compatibility with version < 2.5
boolean
false
autoJsonMap
indicate if JSON fields for MariaDB server 10.5.2+ results in JSON format (or String if disabled)
boolean
true
jsonStrings
force JSON fields as string (MySQL JSON field or MariaDB server 10.5.2+ results in JSON format). When set, autoJsonMap is forced to false
boolean
false
keepAliveDelay
permit to enable socket keep alive, setting delay. 0 means not enabled. Keep in mind that this don't reset server @@wait_timeout (use pool option idleTimeout for that). in ms. For mysql2 compatibility, setting enableKeepAlive and keepAliveInitialDelay alias is permitted. i.e. enableKeepAlive=true&keepAliveInitialDelay=1000 corresponds to setting keepAliveDelay=1000 directly
int
rsaPublicKey
Indicate path/content to MySQL server RSA public key. use requires Node.js v11.6+
string
cachingRsaPublicKey
Indicate path/content to MySQL server caching RSA public key. use requires Node.js v11.6+
string
allowPublicKeyRetrieval
Indicate that if rsaPublicKey or cachingRsaPublicKey public key are not provided, if client can ask server to send public key.
boolean
false
restrictedAuth
if set, restrict authentication plugin to secure list. Default provided plugins are mysql_native_password, mysql_clear_password, client_ed25519, dialog, sha256_password and caching_sha2_password
*Array
String*
supportBigNumbers
(deprecated) DECIMAL/BIGINT data type will be returned as number if in safe integer range, as string if not.
boolean
false
bigNumberStrings
(deprecated) if set with supportBigNumbers DECIMAL/BIGINT data type will be returned as string
boolean
false
stream
permits to set a function with parameter to set stream (since 3.0)
function
bitOneIsBoolean
return BIT(1) values as boolean
boolean
true
checkNumberRange
when used in conjunction of decimalAsNumber, insertIdAsNumber or bigIntAsNumber, if BigInt conversion to number is not exact, connector will throw an error (since 3.0.1)
function
metaEnumerable
make resultset meta property enumerable (since 3.0.2)
boolean
false
infileStreamFactory
When LOAD LOCAL command executed, permit to set a callback function of type (filepath?: string) => stream.Readable. Connector will then not send file from LOAD LOCAL, but Readable content. This can permit to set extra validation of file path for example.
function