DDL with MariaDB Connector/Node.js (Callback API)

Overview

Node.js developers can use MariaDB Connector/Node.js to perform basic DDL (Data Definition Language) operations with MariaDB database products using the Callback API.

DDL (Data Definition Language) refers to all SQL-schema statements in the SQL standard (ISO/IEC 9075-2:2016).

Some examples of DDL include ALTER TABLEALTER TABLE, CREATE TABLECREATE TABLE, DROP TABLEDROP TABLE, CREATE DATABASECREATE DATABASE, and TRUNCATE TABLETRUNCATE TABLE.

DDL Operations with Callback API

With Callback API, DDL operations can be performed by invoking the following method:

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 may be supplied as (err, res). All callback function arguments may 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: CREATE TABLE

The following example shows how to create the data definition for the example table contacts in example setup. If you already created the table, the example program should still work because it uses the IF NOT EXISTS clause for CREATE TABLECREATE TABLE

The example could be used as an alternative to running a CREATE TABLECREATE TABLE DDL statement in MariaDB Client:

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

// 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 to call create_table(conn)
      create_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // ...
   }
}

//Create Table
function create_table(conn) {

   conn.query("CREATE TABLE IF NOT EXISTS 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);
         conn.end(error => {if(error){
               console.log("SQL error in closing a connection: ", error);
            }});
   });
}
main();

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

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

// 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 to call create_table(conn)
      create_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // ...
   }
}

//Create Table
function create_table(conn) {

   conn.query("CREATE TABLE IF NOT EXISTS 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);
         conn.end(error => {if(error){
               console.log("SQL error in closing a connection: ", error);
            }});
   });
}
main();

  • 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 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 to free up resources with conn.end()

The output from the script lists the number of affectedRows as 0 as is expected from a DDL statement. Metadata is null

OkPacket { affectedRows: 0, insertId: 0, warningStatus: 0 }
null

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

SHOW TABLES IN test;

+----------------+
| Tables_in_test |
+----------------+
| contacts       |
+----------------+

DESCRIBE test.contacts;

+------------+-------------+------+-----+--------------------------+
| Field      | Type        | Null | Key | Default  | Extra         |
+------------+-------------+------+-----+----------+---------------+
| id         | int(11)     | NO   | PRI | NULL     | auto_increment|
| first_Name | varchar(25) | YES  |     | NULL     |               |
| last_Name  | varchar(25) | YES  |     | NULL     |               |
| email      | varchar(25) | YES  |     | NULL     |               |
+------------+-------------+------+-----+----------+---------------+

Code Example: ALTER TABLE

The following example shows how to modify the data definition for the example table contacts in example setup to add a column.

The example could be used as an alternative to running a ALTER TABLEALTER TABLE DDL statement in MariaDB Client:

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

// 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 to call alter_table(conn)
      alter_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // ...
   }
}

// Alter Table
function alter_table(conn) {
   conn.query("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)",
      if (err) throw err;
      console.log(res);
      console.log(meta);
      conn.end(error => {if(error){
         console.log("SQL error in closing a connection: ", error);
      }});
   );
}

main();

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

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

// 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 to call alter_table(conn)
      alter_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // ...
   }
}

// Alter Table
function alter_table(conn) {
   conn.query("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)",
      if (err) throw err;
      console.log(res);
      console.log(meta);
      conn.end(error => {if(error){
         console.log("SQL error in closing a connection: ", error);
      }});
   );
}

main();

  • 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 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 to free up resources with conn.end()

The output from the script lists number of affectedRows as 0 as is expected from a DDL statement. Metadata is null

OkPacket { affectedRows: 0, insertId: 0, warningStatus: 0 }
null

Confirm if the database table was altered by using MariaDB Client to execute a DESCRIBEDESCRIBE statement:

DESCRIBE test.contacts;

Example output:

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(25)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | YES  |     | NULL    |                |
| email         | varchar(100) | YES  |     | NULL    |                |
| contact_since | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Code Example: TRUNCATE TABLE

The following example shows how to truncate a table, for the example table contacts in example setup to remove all data from the table.

The example could be used as an alternative to running a TRUNCATETRUNCATE DDL statement in MariaDB Client:

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

// 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 to call truncate_table(conn)
      truncate_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      if (conn) conn.end(err => {
         if(err) {
            console.log("SQL error in closing connection: ", err);
         }
      })
   }
}

// Truncate Table
function truncate_table(conn) {
   conn.query("TRUNCATE TABLE test.contacts",
      if (err) throw err;
      console.log(res);
      console.log(meta);
   );
}

main();

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

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

// 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 to call truncate_table(conn)
      truncate_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      if (conn) conn.end(err => {
         if(err) {
            console.log("SQL error in closing connection: ", err);
         }
      })
   }
}

// Truncate Table
function truncate_table(conn) {
   conn.query("TRUNCATE TABLE test.contacts",
      if (err) throw err;
      console.log(res);
      console.log(meta);
   );
}

main();

Confirm the table was properly truncated by using MariaDB Client to execute a SELECTSELECT statement. The statement should return an empty set:

SELECT * from test.contacts;
Empty set (0.000 sec)

The TRUNCATETRUNCATE statement deletes all data in addition to resetting the AUTO_INCREMENT column counter to 0. The next contact you add is assigned id as 1.