DML with MariaDB Connector/Node.js and Callback API

This page is part of MariaDB's Documentation.

The parent of this page is: MariaDB Connector/Node.js with Callback API

Topics on this page:

Overview
DML Operations with Callback API
Code Example: INSERT, UPDATE, DELETE
Code Example: SELECT

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, DELETE DELETE, INSERT INSERT, REPLACE REPLACE, SELECT SELECT, and UPDATE UPDATE.

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

INSERT INSERT, UPDATE UPDATE, and DELETE DELETE are DML (Data Manipulation Language) operations that modify the data in a table.

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

To update or delete data, replace the INSERT statement in the code example with an UPDATE UPDATE, or DELETE DELETE 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 INSERT INSERT 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 SELECT SELECT statement:

SELECT * FROM test.contacts;

Example output:

+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+

Code Example: SELECT

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

The following code demonstrates how to execute SELECT SELECT 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 SELECT SELECT 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]
      }...
   }
]