Transactions with MariaDB Connector/Node.js and Promise API

Overview

Node.js developers can connect to MariaDB database products using MariaDB Connector/Node.js to run transactions with the Promise API. Promise API is the default API.

MariaDB Connector/Node.js provides methods for managing transactions within the connection. These can be used as an alternative to the SQL START TRANSACTIONSTART TRANSACTION, COMMITCOMMIT, and ROLLBACKROLLBACK statements.

Transactions are useful for several types of data operations. For instance, in a bulk data load, if you want that either all rows of data should be added, or that no data should be added, use transactions.

Transactions with Promise API

A database transaction is a single unit of logic. A transaction can 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 Promise API provides three functions in the Connection object to manage transactions:

Function

Description

connection.beginTransaction() → Promise

Begins a new transaction. It returns a Promise that resolves if no error occurs, and rejects with an Error object if an error occurs.

connection.commit() → Promise

Commits the current transaction if one is active. It returns a Promise that resolves if no error occurs, and rejects with an Error object if an error occurs.

connection.rollback() → Promise

Rolls back the current transaction if one is active. It returns a Promise that resolves if no error occurs, and rejects with an Error object if an error occurs.

Code Example: Transactions

The following example shows how to add data in bulk within a transaction. The example uses the table created in Setup for Examples.

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

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

   try {
      conn =  await 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

      var contacts = [
            ["John", "Smith", "john.smith@example.com"],
            ["Jon", "Smith", "jon.smith@example.com"],
            ["Johnny", "Smith", "johnny.smith@example.com"]
         ];

      await addContactsInTrx(conn, contacts);

   }

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

async function addContactsInTrx(conn, data){

   try {

      // Start Transaction
      await   conn.beginTransaction();

      try {
         // Add Data in a batch
         await conn.batch(
            "INSERT INTO test.contacts(first_name, last_name, email) VALUES(?, ?, ?)",
            data
         );

         // Commit Changes
         await  conn.commit();
      } catch(err){
         console.error("Error loading data, reverting changes: ", err);
         await    conn.rollback();
      }

   } catch(err){
      console.error("Error starting a transaction: ", err);
     //...
   }

   return Promise.resolve(1);
}

main();

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

  • Transactions functions connection.beginTransaction(), connection.commit(), and connection.rollback() are used to begin, commit, and rollback a transaction respectively.

  • If no active transaction exists when the commit(), or the rollback() function is called, no command is sent to MariaDB server, and no error message is output.

  • Transaction is handled in the addContactsInTrx(conn, data) function only.

  • The calling function is only returned the value 1 with Promise.resolve(1) regardless of whether the data loading is successful or failed.

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

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