DDL with MariaDB Connector/Node.js and Promise API

This page is part of MariaDB's Documentation.

The parent of this page is: MariaDB Connector/Node.js with Promise API

Topics on this page:

Overview
DDL Operations with Promise API
Code Example: CREATE TABLE
Code Example: ALTER TABLE
Code Example: TRUNCATE TABLE

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 TABLE ALTER TABLE, CREATE TABLE CREATE TABLE, DROP TABLE DROP TABLE, CREATE DATABASE CREATE DATABASE, and TRUNCATE TABLE TRUNCATE 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 TABLE CREATE TABLE

The example could be used as an alternative to running a CREATE TABLE CREATE 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 TABLES SHOW TABLES statement:

SHOW TABLES IN test;

Example output:

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

Further, confirm the table definition with the DESCRIBE DESCRIBE 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 TABLE ALTER 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 DESCRIBE DESCRIBE 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 TRUNCATE TRUNCATE 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 TRUNCATE TRUNCATE 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 SELECT SELECT statement. The statement should return an empty set:

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