DML with MariaDB Connector/Node.js and Callback 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 Callback API.

DML (Data Manipulation Language) refers to all SQL-data statements in the SQL standard (ISO/IEC 9075-2:2016), for example, DELETEDELETE, INSERTINSERT, REPLACEREPLACE, SELECTSELECT, and UPDATEUPDATE.

Callback API provides compatibility with the mysql and mysql2 APIs.

DML Operations with Callback API

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

Method

Description

connection.query(sql[, values][, callback]) -> Emitter

Execute any DDL statement.

The connection.query(sql[, values][, callback]) -> Emitter 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.

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 can 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 can be supplied as (err, res). All callback function arguments can be omitted by using ().

The connection.query() function returns an Emitter object that emits four types of events:

Event

Description

error

If an error condition is encountered, emits an Error object.

columns

When the table columns metadata is received from the result set, the columns event is emitted with a parameter as an array of metadata fields.

data

When data is received, the data event is emitted for each row of data.

end

The end event is emitted when the query ends.

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:

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

// Declare main function
function main() {
   let conn;

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

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

       add_contact(conn, contact);

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

 function add_contact(conn, data) {
    try{
       conn.query(
          "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
          [data.first_name, data.last_name, data.email],
          (err, result) => {
             if (err) throw err;
             console.log(result);
             conn.end(err => {if(err){
                console.log("SQL error in closing a connection: ", err);}
             });
        });

    } catch (err) {

       // Manage Errors
       console.log("SQL error in establishing a connection: ", err);
    }
 }

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

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

// Declare main function
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"
       });

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

       add_contact(conn, contact);

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

 function add_contact(conn, data) {
    try{
       conn.query(
          "INSERT INTO test.contacts(first_name, last_name, email) VALUES (?, ?, ?)",
          [data.first_name, data.last_name, data.email],
          (err, result) => {
             if (err) throw err;
             console.log(result);
             conn.end(err => {if(err){
                console.log("SQL error in closing a connection: ", err);}
             });
        });

    } catch (err) {

       // Manage Errors
       console.log("SQL error in establishing a connection: ", err);
    }
 }

 main();
  • MariaDB Connector/Node.js adds data to the database with INSERTINSERT statement.

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

  • If an error condition is encountered, the callback function re-throws the Error object with a throw statement.

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

  • Close the connection with conn.end() to free up resources.

The output from the script lists number of affectedRows as 1 as is expected from a DML statement that adds one row of data. An insertId further confirms that the DML statement run is an INSERT statement.

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

Confirm the data was properly inserted 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 |
+----+------------+-----------+------------------------+

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/callback");

function main() {
   let conn;

   try {
      // Establish Connection
      conn = mariadb.createConnection({
         host: "192.0.2.50",
         user: "db_user",
         password: "db_user_password",
         database: "test"
      });

      conn.query(
         "SELECT first_name, last_name, email from test.contacts",
         (err,res,meta) => {
            if (err) {
               console.error("Error querying data: ", err);
            } else {
               console.log(res);
               console.log(meta);
            }});

   } catch (err) {
      // Manage Errors
       console.error("Error connecting to the database and querying data: ", err);
   } finally {
      if (conn) conn.end(err => {
         if(err) {
            console.log("SQL error in closing connection: ", err);
         }
      })
   }
}

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

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

function main() {
   let conn;

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

      conn.query(
         "SELECT first_name, last_name, email from test.contacts",
         (err,res,meta) => {
            if (err) {
               console.error("Error querying data: ", err);
            } else {
               console.log(res);
               console.log(meta);
            }});

   } catch (err) {
      // Manage Errors
       console.error("Error connecting to the database and querying data: ", err);
   } finally {
      if (conn) conn.end(err => {
         if(err) {
            console.log("SQL error in closing connection: ", err);
         }
      })
   }
}

main();
  • A try...catch...finally statement is used for exception handling.

  • MariaDB Connector/Node.js selects data from the database with SELECTSELECT statement.

  • The callback function in the query() 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.

The output from the script lists the query result set as JSON object. The metadata is also output.

Example output:

[
   {
      first_name: 'Johnny',
      last_name: 'Smith',
      email: 'johnny.smith@example.com'
   },
   {
      first_name: 'Johnny',
      last_name: 'Smith',
      email: 'johnnysmith@example.com'
   },
   {
      first_name: 'John',
      last_name: 'Smith',
      email: 'john.smith@example.com'
   },
   {
      first_name: 'John',
      last_name: 'Smith',
      email: 'johnsmith@example.com'
   }
]
[
   ColumnDef {
      _parse: StringParser {
         dbLength: 4,
         dbOffset: 5,
         tableLength: 8,
         tableOffset: 10,
         orgTableLength: 8,
         orgTableOffset: 19,
         nameLength: 10,
         nameOffset: 28,
         orgNameLength: 10,
         orgNameOffset: 39,
         packet: [PacketNodeEncoded]
      }...
   }
]