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
});