Batch Operations with MariaDB Connector/Node.js (Callback API)

Overview

Node.js developers can connect to MariaDB database products using MariaDB Connector/Node.js to perform batch operations with the Callback API. Callback API provides compatibility with the mysql and mysql2 APIs.

Batch Operations with Callback API

The connection.batch(sql, values [, callback]) function is used to run a batch of SQL statements. The function parameters are discussed in following table:

Parameter

Type

Description

sql

string or JSON

The SQL string or JSON object for the SQL statement to run, and optionally, connection options that will supersede the default options. If a JSON object is supplied it must include a property called sql for the SQL statement to run.

values

array or object

Placeholder values.

callback

function

The callback function to supply with a function call. All of the function arguments (error, results, metadata) are optional and their order is significant but name is arbitrary. These arguments may be accessed after the query() function call completes. The first argument is for an Error object that provides information about an error condition if any. The second argument is for the SQL statement result. The third argument is for the metadata associated with the result. As an example, only the error and results arguments may be supplied as (err, res). All callback function arguments may be omitted by using ().

Code Example: Batch DML

The following batch DML (Data Manipulation Language) example shows how to use a batch operation to add multiple contacts' details to the example table:

const mariadb = require("mariadb/callback");

function main() {
   let conn;
   try {
      conn = mariadb.createConnection({
            host: "192.0.2.50",
            user: "db_user",
            password: "db_user_password",
            database: "test"
         });

      //Declare a JSON array for data to add
      var contacts = [
         ["John", "Smith", "john.smith@example.com"],
         ["Jon", "Smith", "jon.smith@example.com"],
         ["Johnny", "Smith", "johnny.smith@example.com"],
      ];

      conn.batch("INSERT INTO test.contacts(first_name, last_name, email) VALUES(?, ?, ?)",contacts, (err,res,meta) => {
            if (err) {
               console.error("Error loading data, reverting changes: ", err);
            } else {
               console.log(res);
               console.log(meta);
            }});
      } catch (err) {
         // Manage Errors
         console.log("SQL error in establishing a connection: ", err);
      } finally {
         if (conn) {
            conn.end(err => {
                  if(err) {
                     console.log("SQL error in closing connection: ", err);
                  }
               })
         }
      }
   }

   main();

const mariadb = require("mariadb/callback");

function main() {
   let conn;
   try {
      conn = mariadb.createConnection({
            host: "example.skysql.net",
            port: 5009,
            ssl: { ca: serverCert },
            user: "db_user",
            password: "db_user_password",
            database: "test"
         });

      //Declare a JSON array for data to add
      var contacts = [
         ["John", "Smith", "john.smith@example.com"],
         ["Jon", "Smith", "jon.smith@example.com"],
         ["Johnny", "Smith", "johnny.smith@example.com"],
      ];

      conn.batch("INSERT INTO test.contacts(first_name, last_name, email) VALUES(?, ?, ?)",contacts, (err,res,meta) => {
            if (err) {
               console.error("Error loading data, reverting changes: ", err);
            } else {
               console.log(res);
               console.log(meta);
            }});
      } catch (err) {
         // Manage Errors
         console.log("SQL error in establishing a connection: ", err);
      } finally {
         if (conn) {
            conn.end(err => {
                  if(err) {
                     console.log("SQL error in closing connection: ", err);
                  }
               })
         }
      }
   }

   main();

  • MariaDB Connector/Node.js adds data to the database with INSERTINSERT statement.

  • The callback function in the batch() function call is (err,res,meta) => {}, which is supplied with three arguments err, res, and meta for the Error object(if thrown), result, and metadata respectively.

  • If an error condition is encountered, the callback function outputs the error message to the console.

The callback function outputs the result and the metadata fields to the console.

OkPacket { affectedRows: 3, insertId: 1, warningStatus: 0 }

Confirm that the data was properly added by using MariaDB Client to execute a SELECTSELECT statement:

SELECT * FROM contacts;

Example output:

+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
|  1 | John       | Smith     | john.smith@example.com   |
|  2 | Jon        | Smith     | jon.smith@example.com    |
|  3 | Johnny     | Smith     | johnny.smith@example.com |
+----+------------+-----------+--------------------------+