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

Overview

Node.js developers can connect to MariaDB database products using MariaDB Connector/Node.js to perform batch operations with the Promise API. Promise API is the default API.

Batch Operations

The connection.batch(sql, values) → Promise 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.

The connection.batch() function returns a Promise object that resolves with a JSON object if no error occurs, and rejects with an Error object if an error condition occurs.

Code Example: Batching 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");

// Main function
async function main() {
   let conn;

   try {
      conn = await 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"],
      ];

      // Use Connection to Add Contacts in Batch
      await add_contacts(conn, contacts);

   } catch (err) {
       // Manage Errors
       console.log(err);
   } finally {
       // Close Connection
       if (conn) conn.close();
   }
}

async  function add_contacts(conn, data) {
   return conn.batch(
      "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
       data
     );
}

main();

const mariadb = require("mariadb");

// Certificate Authority (CA)",
var serverCert = [fs.readFileSync(process.env.SKYSQL_CA_PEM, "utf8")];

// Main function
async function main() {
   let conn;

   try {
      conn = await 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"],
      ];

      // Use Connection to Add Contacts in Batch
      await add_contacts(conn, contacts);

   } catch (err) {
       // Manage Errors
       console.log(err);
   } finally {
       // Close Connection
       if (conn) conn.close();
   }
}

async  function add_contacts(conn, data) {
   return conn.batch(
      "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
       data
     );
}

main();

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

  • Add an async function add_contacts(conn, data) for adding a batch of data.

  • The async keyword declares the function to be an asynchronous function.

  • The add_contacts() function should return a Promise object because it is an async function.

  • Declare a variable contacts for defining a JSON object that represents multiple contacts.

  • The batch() function sends a query to the database and returns the result as a Promise.

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

SELECT * from test.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|
+----+------------+-----------+------------------------+