Transactions with MariaDB Connector/Node.js and Callback API

Overview

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

Transactions with Callback API

A database transaction is a single unit of logic. A transaction may consist of one or more database operations. Transactions are useful and sometimes essential in several types of data operations.

For example, many applications require that a set of SQL statements either complete, or fail, as a single unit. The common characteristics of transactions are atomicity, consistency, isolation, and durability, what is termed as ACID (atomic, consistent, isolated, durable) transactions.

MariaDB transactions are ACID compliant.

The Callback API provides three functions in the Connection object to manage transactions:

Function

Description

connection.beginTransaction([callback])

Begins a new transaction. The callback function is defined with a single argument for the Error object if an error condition occurs in starting the transaction.

connection.commit([callback])

Commits the current transaction if one is active. The callback function is defined with a single argument for the Error object if an error condition occurs in committing the transaction.

connection.rollback([callback])

Rolls back the current transaction if one is active. The callback function is defined with a single argument for the Error object if an error condition occurs in committing the transaction.

Code Example: Transactions

The following example shows how to add data within a transaction to the example table created in example setup.

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

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

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

      // Use Connection to call function that loads data within an explicit transaction

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

      addContactsInTrx(conn, contact);

   }

   // Manage Errors
   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 addContactsInTrx(conn, data){
   // Start Transaction
   conn.beginTransaction(error => {

      if (error){
         console.log("SQL error in starting a transaction: ", error);
      } else {

         // Add Data
         conn.query(
            "INSERT INTO test.contacts(first_name, last_name, email) VALUES(?, ?, ?)",
            [data.first_name,data.last_name,data.email],
            (err,res,meta) => {
               if (err) {
                  console.error("Error loading data, reverting changes: ", err);
                  console.error("Error loading data, reverting changes: ", err);
                  conn.rollback(error => {
                     if (error) {
                        console.log("SQL error in rolling back a transaction: ", error);
                     }
                  });
                } else {
                   console.log(res);
                   console.log(meta);

                   // Commit Changes
                   conn.commit(err => {
                      if (err) {
                         console.log("SQL error in committing a transaction: ", err);
                      }
                   });
                }
            }
         );
      }
   });
}

main();

  • MariaDB Connector/Node.js uses the beginTransaction() function to begin a transaction.

  • In the body of the transaction, it checks and reports if an error occurred.

  • If no error occurred, MariaDB Connector/Node.js adds data to the database using the with INSERTINSERT statement.

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

  • If the query does not encounter an error, it reports the results and commits the transaction.

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

  • MariaDB Connector/Node.js, closes the connection with conn.end() in the finally block to free up resources.

The output from the script lists the number of affectedRows as 1 as is expected from a DML statement that adds one row of data. Metadata is undefined.

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

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