DDL with MariaDB Connector/Node.js and Promise 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 Promise API.

DDL (Data Definition Language) refers to all SQL-schema statements in the SQL standard (ISO/IEC 9075-2:2016). Connector/Python is compliant with the Python Database API Specification v2.0 (PEP 249) .

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

DDL Operations with Promise API

With Promise API, DDL operations can be performed by invoking the following methods:

Method

Description

connection.query(sql[, values])

Execute any DDL statement. Returns a Promise object.

The connection.query(sql[, values]) -> Promise 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.

The connection.query() function returns a Promise object that resolves with a value if the DDL operation is successful. The Promise object rejects with an Error object if an error condition occurs.

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:

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

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

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

      // Use Connection to call create_table(conn)
      await create_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Create Table
async function create_table(conn) {
   return 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");
}

main();
// Required Modules
const mariadb = require("mariadb");

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

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

   // Create a Connection
   try {
      conn = await 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)
      await create_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Create Table
async function create_table(conn) {
   return 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");
}

main();
  • Add an async function create_table(conn) for creating the test.contacts table.

  • The async keyword declares the function to be an asynchronous function.

  • The create_table(conn) function should return a Promise object because it is an async function.

  • Send a DDL statement to the MariaDB server using the connection.query() function.

  • In the main() function, call the asynchronous function create_table(conn) with an argument for the connection object. Use the await keyword with the function call as it is an asynchronous function call.

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

Confirm if the database table was created by using MariaDB Client to execute a SHOW TABLESSHOW TABLES statement:

SHOW TABLES IN test;

Example output:

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

Further, confirm the table definition with the DESCRIBEDESCRIBE statement:

DESCRIBE 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 execute an ALTER TABLEALTER TABLE statement to add a new column to the example table created in Setup for Examples.

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

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

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

      // Use Connection to call alter_table(conn)
      await alter_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Alter Table
async function alter_table(conn) {
   return conn.query("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)");
}

main();
// Required Modules
const mariadb = require("mariadb");

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

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

   // Create a connection
   try {
      conn = await 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)
      await alter_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Alter Table
async function alter_table(conn) {
   return conn.query("ALTER TABLE test.contacts ADD COLUMN IF NOT EXISTS (contact_since INT)");
}

main();

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

Example output:

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(100) | YES  |     | NULL    |                |
| contact_since | int(11)      | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Code Example: TRUNCATE TABLE

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. The following example shows how to execute a TRUNCATETRUNCATE statement to truncate the example table created in Setup for Examples:

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

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

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

      // Use Connection to call truncate_table(conn)
      await truncate_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Truncate Table
async function truncate_table(conn) {
   return conn.query("TRUNCATE TABLE test.contacts");
}

main();
// Required Modules
const mariadb = require("mariadb");

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

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

   // Create a Connection
   try {
      conn = await 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)
      await truncate_table(conn);
   } catch (err) {
      // Manage Errors
      console.log(err);
   } finally {
      // Close Connection
      if (conn) conn.close();
   }
}

//Truncate Table
async function truncate_table(conn) {
   return conn.query("TRUNCATE TABLE test.contacts");
}

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)