DML with MariaDB Connector/Node.js and Promise API

Overview

Node.js developers can connect to MariaDB database products using MariaDB Connector/Node.js to perform DML (Data Manipulation Language) operations with the Promise API. DML means all SQL-data statements (ISO/IEC 9075-2:2016). Promise API is the default API.

DML Operations with Promise API

With Promise API, DML operations can be performed by invoking the following methods:

Promise API

Description

query()

Executes an SQL query.

batch()

Executes an SQL query on multiple rows of data.

queryStream()

Executes an SQL query, calling different functions for the return values.

Any of these methods could be used to perform DML operations.

The query() method is covered here.

The batch() method is covered in Batch Operations and Query Pipelining.

The queryStream() method is covered in Query Stream.

The connection.query(sql[, values]) -> Promise function is used to run a single SQL statement or a prepared statement. 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.query() function returns a Promise object that resolves with a JSON object for DML operations insert/update/replace/delete, and a result set array for select queries. The Promise object rejects with an Error object if an error condition occurs.

Code Example: INSERT, UPDATE, DELETE

INSERTINSERT, UPDATEUPDATE, and DELETEDELETE are DML (Data Manipulation Language) operations that modify the data in a table.

The following code demonstrates how to execute INSERTINSERT on the example table:

To update or delete data, replace the INSERT statement in the code example with an UPDATEUPDATE or DELETEDELETE statement:

// Required Modules
const mariadb = require("mariadb");

// The 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",
      });

      // Use Connection

      // Add Contacts
      const contact = {
         first_name: "John",
         last_name: "Smith",
         email: "john.smith@example.com",
      };

      await add_contact(conn, contact);

      const contact2 = {
         first_name: "Jon",
         last_name: "Smith",
         email: "jon.smith@example.com",
      };

      await add_contact(conn, contact2);

      const contact3 = {
         first_name: "Johnny",
         last_name: "Smith",
         email: "johnny.smith@example.com",
      };

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

//Function to add a single contact
async function add_contact(conn, data) {
  // Insert Data
  return new Promise((resolve) => {
    setTimeout(function () {
      resolve(
        conn.query(
          "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
          [data.first_name, data.last_name, data.email]
        )
      );
      console.log("SQL statement run");
    }, 1000);
  });

  //  return conn.query("INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",[data.first_name, data.last_name, data.email]);
}

main();

// Required Modules
const mariadb = require("mariadb");

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

// The 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",
      });

      // Use Connection

      // Add Contacts
      const contact = {
         first_name: "John",
         last_name: "Smith",
         email: "john.smith@example.com",
      };

      await add_contact(conn, contact);

      const contact2 = {
         first_name: "Jon",
         last_name: "Smith",
         email: "jon.smith@example.com",
      };

      await add_contact(conn, contact2);

      const contact3 = {
         first_name: "Johnny",
         last_name: "Smith",
         email: "johnny.smith@example.com",
      };

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

//Function to add a single contact
async function add_contact(conn, data) {
  // Insert Data
  return new Promise((resolve) => {
    setTimeout(function () {
      resolve(
        conn.query(
          "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
          [data.first_name, data.last_name, data.email]
        )
      );
      console.log("SQL statement run");
    }, 1000);
  });

  //  return conn.query("INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",[data.first_name, data.last_name, data.email]);
}

main();

  • Add an async function add_contact(conn, data) for adding a single row of data or single contact to the test.contacts table.

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

  • As an example of Promise customization, add a delay for resolving a value or the Promise action using window.setTimeout function.

  • Promise is returned only after the delay (milliseconds) set with setTimeout has elapsed. Setting a timeout could be useful for several reasons, for example, result sets from consecutive queries need to be displayed to a user after an interval of time.

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

  • Declare a constant called contact for defining an object that represents a single contact.

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

SELECT * from test.contacts;

+----+------------+-----------+------------------------+
| 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|
+----+------------+-----------+------------------------+

Code Example: SELECT

SELECTSELECT is a DML (Data Manipulation Language) operation that reads the data from a table.

The following code demonstrates how to execute SELECTSELECT on 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",
      });

      // Use Connection to get contacts data
      var rows = await get_contacts(conn);

      //Print list of contacts
      for (i = 0, len = rows.length; i < len; i++) {
         console.log(`${rows[i].first_name} ${rows[i].last_name <${rows[i].email}>`);
      }
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Get list of contacts
function get_contacts(conn) {
   return conn.query("SELECT first_name, last_name, email FROM test.contacts");
}

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

      // Use Connection to get contacts data
      var rows = await get_contacts(conn);

      //Print list of contacts
      for (i = 0, len = rows.length; i < len; i++) {
         console.log(`${rows[i].first_name} ${rows[i].last_name <${rows[i].email}>`);
      }
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Get list of contacts
function get_contacts(conn) {
   return conn.query("SELECT first_name, last_name, email FROM test.contacts");
}

main();

  • Add an async function get_contacts(conn) to the same script for getting contacts data from the test.contacts table.

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

  • The get_contacts(conn) function returns a Promise object because it is an async function.

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

  • Loop over the contacts data returned by the SQL query using a for loop, and print the first_name, last_name, and email in each row of data.

When the script is run, the contacts data is logged to the console:

John Smith <john.smith@example.com>
Jon Smith <jon.smith@example.com>
Johnny Smith <johnny.smith@example.com>

MariaDB Connector/Node.js also supports the queryStream() function, which allows designating different function calls depending on what the SQL returns. Additional information is available in Query Stream.