• Node.js Video Tutorials

Node.js - MySQL Create Database

One or more databases (or schemas) can be created on a MySQL server. While we can create databases with MySQL query CREATE DATABASE, as well as with Workbench, we are interested in creating a database with a Node.js program.

Databases are where we store our data in the form of tables. We use the MySQL query CREATE DATABASE to create a database.

Syntax

Following is the syntax of the MySQL Creating Database query −

CREATE DATABASE IF NOT EXISTS database_name;

Example

Create a database of your choice, in here we are creating one for a pet store and hence calling it mydb and, running the command “CREATE DATABASE” on MySQL Command Line Client.

mysql> CREATE DATABASE IF NOT EXISTS mydb;
Query OK, 1 row affected (0.02 sec)

To verify database creation, we can run the "SHOW DATABASES" query −

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

con.query() method

The connection object returned by mysql.createConnection() method has access to a query() method. A string representation of any valid MySQL query is passed to it as an argument. The second argument to query() method is a callback function. The query() method is used as follows −

con.query(sql, function (err, result) {. . . . . . });

We just need to change the "sql" argument in the query() function with the desired query in the string format.

Since the query to create a database in MySQL is "CREATE DATABASE $database_name$", then, the syntax to create a database using the Node.js con.query() function becomes −

con.query("CREATE DATABASE $database_name$;", function (err, result) {
   //Your code
});

Example

Following the Java Script example is trying to create a MySQL database named mydb. To do so −

  • First of all, you need to replace the "sql" argument in the query function with "CREATE DATABASE mydb;" and run the file using node.

  • Now, to check if you've created the database, we can use the MySQL query "SHOW DATABASES;". For this, you need to execute the con.query() function again this time you need to pass the "SHOW DATABASES;" query as the first parameter.

  • The SHOW DATABASES query returns an array of RowDataPacket objects. You need to stringify each object in the array to get the database name.

  • Run a loop to look at all the databases with their names and find the database you've just created.

  • We have added the IF NOT EXISTS clause while creating the database as we don't want any error to occur during the creation of the database in case it has already been created.

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword"
});
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query("CREATE DATABASE IF NOT EXISTS mydb;", function (err, result) {
      if (err) throw err;
         console.log("Created database successfully");
   });
   con.query("SHOW DATABASES;", function (err, result) {
      if (err) throw err;
         console.log("Showing databases\n");
      for (var i = 0; i < result.length; i++) {
         console.log(JSON.stringify(result[i]));
      }
   })
   con.end();
});

Output

Connected!
Created database successfully
Showing databases

{"Database":"information_schema"}
{"Database":"mydb"}
{"Database":"mysql"}
{"Database":"performance_schema"}
{"Database":"sakila"}
{"Database":"sys"}
{"Database":"world"}
Advertisements