NodeJS MySQL Create Database

MySQL Database is used to store the collection of records in the form of tables consisting of rows and columns.

The Database in MySQL can be handled using SQL queries. SQL stands for Structured Query Language, which can perform many operations such as create, select, update, delete, etc on the database.

For creating a database in MySQL, the SQL query is given below

CREATE DATABASE databaseName

Where databaseName is the name of the database we want to create.

Creating a MySQL Database Using Node.js

We can directly pass the above SQL query to create a MySQL database in the query() method provided by the MySQL connection object. This method takes a SQL query and a callback as an argument.

Syntax:

con.query(query, callback)

where

  • con is the connection object,
  • query is the query to create a database

Steps to Create a MySQL Database Using Node.js

Let’s see an example to see the working of this method to create a new MySQL database 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.  

XAMPP Control Panel 2

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

Folder Structure 2

Step 3: Open the terminal and type the below command to initial NPM to install the MySQL module to connect to the MySQL server.

npm init -y
Initiate NPM 1

Step 4: Install the MySQL module using the below command.

npm i mysql
Install Sql 1

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: ""
});

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.

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 database and a callback. Inside the callback, we first check for the error and then print a success message in the console.

con.query("CREATE DATABASE newdatabase", function (err, result) {
    if (err) throw err;
    console.log("Database created");
});

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

node app.js

Complete Code:

const mysql = require('mysql');

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

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

con.query("CREATE DATABASE newdatabase", function (err, result) {
    if (err) throw err;
    console.log("Database created");
});

Output:

Database Created

Verifying the Database Creation

To verify that the database is successfully created you can use another SQL query given below.

SHOW DATABASES

This prints a list of all the databases for a specific user.

Again we have to use the query() method and pass the above query and a callback as an argument. Inside the callback, we first check for the error and then print the result in the console.

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

Output:

Validate Database

Here we can see an array containing the list of databases in which a line

RowDataPacket { Database: 'newdatabase' },

validate that the “newdatabase” is successfully created.

Summary of Creating a MySQL Database Using Node.js

MySQL database stores records in the form of one or many tables of rows and columns which makes it easy to perform various operations on them such as create, select, insert, delete, update, etc. For performing these queries we have to use SQL which stands for Structured Query Language. For creating a database the SQL query is used, this query can be directly passed in the MySQL CLI or we can execute it using the NodeJS module “mysql” which provides a method query() to run SQL queries. Hope this tutorial helps you to understand the process of creating a MySQL database using NodeJS.

Reference

https://www.npmjs.com/package/mysql

Aditya Gupta
Aditya Gupta
Articles: 113