MariaDB Connector/Node.js (Callback API) Examples

Overview

Example programs provided here illustrate how to use MariaDB Connector/Node.js to connect to MariaDB database products and retrieve and manipulate data.

Short Example

The following example uses the Callback API to select data from the table defined in example setup. Complete information on using Connector/Node.js with the Callback API is available.

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

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

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

Example output:

[
  {
    first_name: 'John',
    last_name: 'Smith',
    email: 'john.smith@example.com'
  }
]

Long Example

The following example program connects using query pipelining, and demonstrates some CRUD (CREATE, READ, UPDATE, DELETE) operations within a transaction, with the example setup.

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

function main() {
   let conn;

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

      //Declare a JSON object for data to add
      var addContact = {
         first_name: "John",
         last_name: "Smith",
         email: "johnsmith@example.com",
      };

      //Declare a JSON object for data to update
      const updateContact = {
         first_name: "John",
         email: "john.smith@example.com",
      };

      //Declare a JSON object for data to delete
      const deleteContact = {
         id: 3
      };

      crudInPipeline(conn, addContact, updateContact, deleteContact);

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

//Function to perform CRUD
function crudInPipeline(conn, data, update, del) {
   try {

      // Start Transaction
      conn.beginTransaction(error => {
         if (error) {
            console.log("SQL error in starting a transaction: ", error);
         }
      });

      try {

         //Create Table
         conn.query("CREATE TABLE test.contacts (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(25),last_name VARCHAR(25),email VARCHAR(100)) ENGINE=InnoDB", (err, res, meta) => {
            if (err) throw err;
            console.log(res);
            console.log(meta);
         });

         // Add Contact
         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);
            });
         });

         // Update contact
         conn.query(
            "UPDATE test.contacts SET email = ? WHERE first_name = ?",
            [update.email, update.first_name], (err, res, meta) => {
               if (err) {
                  console.error("Error updating data, reverting changes: ", err);
               } else {
                  console.log(res);
                  console.log(meta);
               }
            });

         //Delete contact
         conn.query(
            "DELETE FROM test.contacts WHERE id = ?",
            [del.id], (err, res, meta) => {
               if (err) {
                  console.error("Error deleting data, reverting changes: ", err);
               } else {
                  console.log(res);
                  console.log(meta);
               }
            });

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

      } catch (err) {
         console.error("Error adding user, reverting changes: ", err);
         conn.rollback(error => {
            if (error) {
               console.log("SQL error in rolling back a transaction: ", error);
            }
         });

      }

   } catch (err) {
      console.error("Error in query pipeline app: ", 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 (with query pipelining set)
      conn = mariadb.createConnection({
         host: "example.skysql.net",
         port: 5009,
         ssl: { ca: serverCert },
         user: "db_user",
         password: "db_user_password",
         database: "test",
         pipelining: true,
      });

      //Declare a JSON object for data to add
      var addContact = {
         first_name: "John",
         last_name: "Smith",
         email: "johnsmith@example.com",
      };

      //Declare a JSON object for data to update
      const updateContact = {
         first_name: "John",
         email: "john.smith@example.com",
      };

      //Declare a JSON object for data to delete
      const deleteContact = {
         id: 3
      };

      crudInPipeline(conn, addContact, updateContact, deleteContact);

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

//Function to perform CRUD
function crudInPipeline(conn, data, update, del) {
   try {

      // Start Transaction
      conn.beginTransaction(error => {
         if (error) {
            console.log("SQL error in starting a transaction: ", error);
         }
      });

      try {

         //Create Table
         conn.query("CREATE TABLE test.contacts (id INT PRIMARY KEY AUTO_INCREMENT,first_name VARCHAR(25),last_name VARCHAR(25),email VARCHAR(100)) ENGINE=InnoDB", (err, res, meta) => {
            if (err) throw err;
            console.log(res);
            console.log(meta);
         });

         // Add Contact
         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);
            });
         });

         // Update contact
         conn.query(
            "UPDATE test.contacts SET email = ? WHERE first_name = ?",
            [update.email, update.first_name], (err, res, meta) => {
               if (err) {
                  console.error("Error updating data, reverting changes: ", err);
               } else {
                  console.log(res);
                  console.log(meta);
               }
            });

         //Delete contact
         conn.query(
            "DELETE FROM test.contacts WHERE id = ?",
            [del.id], (err, res, meta) => {
               if (err) {
                  console.error("Error deleting data, reverting changes: ", err);
               } else {
                  console.log(res);
                  console.log(meta);
               }
            });

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

      } catch (err) {
         console.error("Error adding user, reverting changes: ", err);
         conn.rollback(error => {
            if (error) {
               console.log("SQL error in rolling back a transaction: ", error);
            }
         });

      }

   } catch (err) {
      console.error("Error in query pipeline app: ", err);
   }
}

main();

After running the application script, confirm the table has been created:

SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| contacts       |
+----------------+

Confirm the data was added or updated:

SELECT * from test.contacts;
+----+------------+-----------+------------------------+
| id | first_name | last_name | email                  |
+----+------------+-----------+------------------------+
|  1 | John       | Smith     | john.smith@example.com |
+----+------------+-----------+------------------------+