NodeJS MySQL Create Table

Creating a table is extremely important for storing data in multiple rows and columns in a MySQL database. MySQL stores data in the form of a table which can be created using SQL queries, SQL stands for Structured Query Language and can be used to perform different operations on MySQL database.

The below query is used to create a table in MySQL.

CREATE TABLE tableName ( column1 datatype, column2 datatype, …., columnN datatype );

where,

tableName is the name of the table you want to create,
column1, column2, …, columnN is the name of the different field in the table,
datatype specified the type of value a column holds such as INT, VARCHAR, etc.

Creating a Table in MySQL Using NodeJS

MySQL tables can be directly created in NodeJS using the MySQL connection object. During the connection of NodeJS and MySQL, this connection object is created. This connection object has a method query() to run SQL queries in NodeJS.

Inside this method, we can pass a query of creating a table and a callback function as an argument in order to create the table. Inside the callback, check for the error which can occur when the table we want to create already exists in the database.

con.query("CREATE TABLE tableName ( column1 datatype, column2 datatype, …., columnN datatype)", function (err) {
    if (err) throw err;
    // callback body
});

where the con is the connection object.

Example of Creating a Table in MySQL with NodeJS

Let’s see an example to create a new MySQL table using NodeJS.

Step 1: Run the MySQL server in the background.

For this, you can use XAMPP which is extremely fast, easy to install, and can run a MySQL server in one click.

Step 2: Create a folder with a file “app.js” where we write our code and open this folder in the code editor.

Step3: Open the terminal and type the below command to initial NPM.

npm init -y

Step 4: Type the below command to install the MySQL module.

npm i mysql

Step 5: Open the “app.js” file and import the MySQL module.

const mysql = require('mysql');

Step 6: Connect to the MySQL server using the createConnection() and connect() methods.

const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "newdatabase"
});

con.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log("Connected to MySQL!");
});

We have a separate tutorial on creating MySQL connections using NodeJS if you want to read it.

We specified the database as “newdatabase” which we have created in another tutorial NodeJS MySQL Create Database.

Step 7: Use the connection object and a dot notation right to it and use the query() method.

con.query();

Step 8: Inside the query method pass the query to create a table and a callback. Inside the callback, we first check for the error and then print a success message in the console.

con.query("CREATE TABLE newtable (id INT, name VARCHAR(255),email VARCHAR(255))", function (err) {
    if (err) throw err;
    console.log("Table created");
});

Step 9: Type the below command to run the application.

node app.js

Compete Code:

const mysql = require('mysql');

const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "newdatabase"
});

con.connect(function (err) {
    if (err) {
        throw err;
    }
    console.log("Connected to MySQL!");
});


con.query("CREATE TABLE newtable (id INT, name VARCHAR(255),email VARCHAR(255))", function (err) {
    if (err) throw err;
    console.log("Table created");
});

Output:

Verifying the Table Creation in MySQL with NodeJS

To verify that the table is successfully created you can use another SQL query, this will return an array of all the tables for a specific database.

con.query("SHOW TABLES", function (err, result) {
    if (err) throw err;
    console.log(result);
});

Output:

Summary

A table is useful in order to store the data in a database in the form of rows and columns which makes it easy to query data. We can create a table using a SQL query of creating the table and pass it inside the query() method which can run a SQL query directly in NodeJS. Hope this tutorial helps you to understand the process of creating a MySQL table using NodeJS.