MySQL is a RDBMS software that uses SQL to perform CRUD operations on the data stored in the tables of a relational database. In this tutorial, we are learning how to use a MySQL database as a backend to a Node.js application. This chapter describes how to create a table in a MySQL database using the Node.js program.

In a relational database, a table represents an entity, characterized by one or more attributes. Each row in the table is an instance of the entity. Each entity instance is described by certain values of the table attributes. A table generally has one attribute marked as a primary key, constraining each row to have a unique value.

CREATE TABLE in MySQL

MySQL implements the CREATE TABLE statement as per the SQL standards. This statement defines the number and type of attributes of the table.

The syntax of MySQL”s CREATE TABLE query is as follows −

CREATE TABLE [IF NOT EXISTS] table_name(
   column1 datatype constraints,
   column1 datatype constraints,
) ENGINE=storage_engine;

MySQL supports variety of datatypes, such as integer types (such as INT, TINYINT etc), floating-point types (FLOAT and DOUBLE), string types (VARCHAR, TEXT etc), and DATE and TIME types.

To ensure data integrity of the relational database, a column may have constraints applied on it, such as PRIMARY KEY, NOT NULL, AUTO_INCREMENT etc.

Let us create employee Table in the mydb database. Start the MySQL server, open the MySQL command-line client, and issue the CREATE TABLE query as below −

mysql> use mydb;
Database changed
mysql> CREATE TABLE IF NOT EXISTS employee (
   -> id INT AUTO_INCREMENT PRIMARY KEY,
   -> name VARCHAR(20) NOT NULL,
   -> age INT,
   -> salary FLOAT
   -> );
Query OK, 0 rows affected (0.09 sec)

MySQL command SHOW TABLES lists all the tables in the database that is in use.

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| employee       |
+----------------+
1 row in set (0.00 sec)

CREATE TABLE in Node.js

Now we have to programmatically create a MySQL table with the help of Node.js application. The process involves following steps −

• Include the mysql module

• Call createConnection() function to obtain connection object.

• Use the query() method of the connection object, and pass the CREATE TABLE query string to it as the first argument.

To check that the table has been created, pass the SHOW TABLES query to the query() method.

Example

var mysql = require(''mysql'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});
var qry =`CREATE TABLE IF NOT EXISTS employee (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(20) NOT NULL,
   age INT,
   salary FLOAT
);`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, result) {
      if (err) throw err;
      console.log("Created table successfully");
   });
   con.query("SHOW TABLES;", function (err, result) {
      if (err) throw err;
      console.log("Showing tablesn");
      for (var i = 0; i < result.length; i++) {
         console.log(JSON.stringify(result[i]));
      }
   })
   con.end();
});

Output

Connected!
Created table successfully
Showing tables

{"Tables_in_mydb":"employee"}

You can also use the DESC command that displays the fields, type, and constraints like default, primary key, etc. of each field in our table.

con.query("DESC employee;", function (err, result) {
   if (err) throw err;
   console.log(JSON.stringify(result));
});

Output

[
{"Field":"id","Type":"int","Null":"NO","Key":"PRI","Default":null,"Extra":"auto_increment"},
{"Field":"name","Type":"varchar(20)","Null":"NO","Key":"","Default":null,"Extra":""},
{"Field":"age","Type":"int","Null":"YES","Key":"","Default":null,"Extra":""},
{"Field":"salary","Type":"float","Null":"YES","Key":"","Default":null,"Extra":""}
]