Nodejs MySQL Integration: 4 Easy Steps

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications, among others.

MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This Server is availed as a separate program and handles all the database instructions, commands, and statements.

Node.js is a backend, open-source JavaScript runtime environment that can carry out JavaScript code outside a web browser and runs on the V8 Engine.

This blog talks in detail about the different steps you can implement to seamlessly execute Npm MySQL Integration. It also gives a brief introduction to Npm MySQL2 before diving into the different aspects of Nodejs MySQL Integration, like insertion, querying, creation, modification, to name a few.

What is MySQL?

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a variety of use cases like web applications. 

Since it comes standard in the LAMP Stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, PHP; MySQL is the go-to choice for scalable web applications. Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL. 

Key Features of MySQL

• Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
• Long History: MySQL has been around for over 20 years since its first release in 1995.
• Frequent Updates: MySQL is getting more robust with frequent updates alongside new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
• MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
• A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
• Open-Source: MySQL is also a Free and Open-Source Relational Database Management System (RDBMS).
• Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, Zendesk to name a few.

What is Node.Js?

Node.js was developed as an asynchronous event-driven JavaScript runtime that can be leveraged to develop scalable applications. It is primarily used by the developers to develop command-line tools, in addition to server-side scripting for the creation of the dynamic web content before it actually gets opened on the user’s display.

Powered by Google Chrome’s V8 engine, the utility of Node.js exists in the fact that it can process the JavaScript code outside a web browser.

Keeping it in view, Node.js can be considered as a “JavaScript everywhere” regime i.e., integrate Application Development (web) around a solitary Programming Language, as opposed to different programming media being leveraged for the respective stakeholders i.e., Server-side and Client-side codes. Specifically, Node.js is an event-driven architecture focused on enriching and optimizing scalability and throughput within web applications. 

As far as the naming is concerned, .js is referred to as a standard file extension name in JavaScript but Node.js doesn’t refer to a particular file rather it is a generic product name. 

How to Connect to MySQL Database using Node.Js?

Here are the steps involved in establishing MySQL Nodejs Integration:

• Nodejs MySQL Integration: Install MySQL Node.js Driver
• Nodejs MySQL Integration: Configure MySQL Database Server Connection
• Nodejs MySQL Integration: Close Database Connection
• Nodejs MySQL Integration: Pool Connections

Nodejs MySQL Integration: Install MySQL Node.js Driver

• Step 1: First, you need to create a folder for storing the Nodejs app, for instance, node-mysql, and leverage the npm init command to develop the package.json file. Next, you need to install node.js for the MySQL package with the help of the following command:

npm install mysql

• Step 2: In this step, you need to create the connect.js script within the node-mysql folder to store the code that links to the MySQL Database Server.
  
  For this example, you will be leveraging the ‘todoapp’ database for demonstration purposes, so, you should create the database in your MySQL Database Server by running the following CREATE DATABASE command: 

CREATE DATABASE todoapp;

• Step 3: Once you’ve created the database, you can go about connecting it from the Nodejs application.

Nodejs MySQL Integration: Configure MySQL Database Server Connection

• Step 1: To begin the configuration of the MySQL Database Server Connection, you first need to import the module mysql via the command mentioned below:

let mysql = require('mysql');

• Step 2: Next, you need to create a connection to the MySQL database by referring to the createConnection() method and supplying the requisite information on MySQL Server such as user, host, database, and password according to the following command:

let connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'todoapp'
});

• Step 3: Now that you’ve created the connection to the database ‘todoapp’ within the local database server, you can call the connect() method on the connection object to link to the MySQL Database Server:

connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  console.log('Connected to the MySQL server.');
});

• Step 4: The connect() method expressed in the previous step accepts a callback function that contains the err argument which provides detailed errors in the event of one. You can test the connect.js program with the help of the following code snippet:

> node connect.js
Connected to the MySQL server

• Step 5: After executing the aforementioned command, if you receive the message “connected to the MySQL Server”, it means that you have successfully connected to the MySQL Database Server from within the Nodejs application.
  
  Now, if the todoapps database ceased to exist within your database server and you still attempt to link to it, here’s the error message you will receive:

> node connect.js
error: ER_BAD_DB_ERROR: Unknown database 'todoapps'

• Step 6: A point to note here is that every method you choose to call on the connection object is queued and fulfilled in sequence.

Nodejs MySQL Integration: Close Database Connection

• Step 1: Now that you’ve configured the database connection, you need to close it as well. For this, you need to call the end() method present on the connection object. The end() method makes sure that the remainder of the queries is always carried out prior to the closing of the database connection.
  
  Here’s the code snippet for the same:

connection.end(function(err) {
  if (err) {
    return console.log('error:' + err.message);
  }
  console.log('Close the database connection.');
});

• Step 2: However, if you choose to force the closing of the connection instantly, you can opt for the destroy() method. This method ensures that no more events or callbacks would be triggered for the connection.
  
  Here’s the code snippet depicting how you can leverage the destroy() method:

connection.destroy();

• Step 3: As stated previously, the destroy() method clearly doesn’t have a callback argument as opposed to the end() method.

Nodejs MySQL Integration: Pool Connections

• Step 1: To pool connections for the Nodejs MySQL Integration, you can leverage the in-house MySQL driver for the Nodejs module since it comes with a built-in connection pooling feature. Say, if you wish to generate a connection pool with 7 connections then you can use the following code snippet:

var pool = mysql.createPool({
    connectionLimit: 7,
    host: 'localhost',
    user: 'root',
    password: '', 
    database: 'todoapp'
});

• Step 2: Now, to establish a connection from the connection pool, you can leverage the getConnection() method as follows:

pool.getConnection(function(err, connection) {
  // execute query
  // ...
});

• Step 3: Now, to revert a connection back to the pool once you’re done with it you can invoke the connection.release() method. Following this, the connection can be availed in the pool by someone else.
  
  Here’s the code snippet for the same:

pool.getConnection(function(err, connection) {
  // execute query
  // ...
  connnection.release();
});

• Step 4: If you want to close a connection and eliminate it from the pool, you can use the connection.destroy() method. A new connection would be generated within the pool if the need arises for it.
  
  A key point to note here is that the connection pool creates these connections lazily. For instance, if you configure the connection pool with 7 connections but you use only 3 connections in parallel, then the pool only generates 3 connections for you. If you want to close all connections within the pool, you can use the end() method of the pool object as mentioned in the command below:

pool.end(function(err) {
  if (err) {
    return console.log(err.message);
  }
  // close all connections
});

How to Create MySQL Tables using Node.js?

• Step 1: First, you need to connect to the MySQL Database Server. In this instance (query.js) you can connect to the todoapp database and carry out the CREATE TABLE statement as follows:

let mysql = require('mysql');
let connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'todoapp'
});

// connect to the MySQL server
connection.connect(function(err) {
  if (err) {
    return console.error('error: ' + err.message);
  }

  let createTodos = `create table if not exists todos(
                          id int primary key auto_increment,
                          title varchar(255)not null,
                          completed tinyint(1) not null default 0
                      )`;

  connection.query(createTodos, function(err, results, fields) {
    if (err) {
      console.log(err.message);
    }
  });

  connection.end(function(err) {
    if (err) {
      return console.log(err.message);
    }
  });
});

• Step 2: The query() method accepts a callback and an SQL statement. The callback function will take 3 arguments: error, results, and fields.
  
  Here’s the code snippet for the same:

> node query.js

• Step 3: Now that your query has been executed without any errors, you need to check if the todos table was created within your designated database:

>mysql -u root -p todoapp;
Enter password: *********

mysql> show tables;
+-------------------+
| Tables_in_todoapp |
+-------------------+
| todos             |
+-------------------+
1 row in set (0.08 sec)

• Step 4: As is evident from the previous query, you can see that the todos table was successfully created within your todoapp database.

How to Insert Data in MySQL Tables with Node.js?

Here are the different aspects of Nodejs MySQL Table Insertion:

• Nodejs MySQL Table Insertion: Inserting One Data Row in MySQL Table
• Nodejs MySQL Table Insertion: Inserting Multiple Rows in MySQL Table

Before jumping into Nodejs MySQL Table insertion, you first need to connect to the MySQL Database. For this, you can reuse the config.js module that contains the MySQL Database information. This is what the config.js script looks like:

let config = {
  host    : 'localhost',
  user    : 'root',
  password: '',
  database: 'todoapp'
};

module.exports = config;

Nodejs MySQL Table Insertion: Inserting One Data Row in MySQL Table

• Step 1: You can use the following code snippet to insert a solitary data row within your MySQL Table (todos):

let mysql  = require('mysql');
let config = require('./config.js');
let connection = mysql.createConnection(config);

// insert statment
let sql = `INSERT INTO todos(title,completed)
           VALUES('Learn how to insert a new row',true)`;

// execute the insert statment
connection.query(sql);

connection.end();

• Step 2: You can carry out the insert.js program by following the command mentioned below:

> node insert.js

• Step 3: Next, you need to check the data from the todos table with the following code snippet:

mysql> select * from todos;
+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         1 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)

This shows that the row was successfully added to your table.

Nodejs MySQL Table Insertion: Inserting Multiple Rows in MySQL Table

• Step 1: You can use the code snippet mentioned below to insert multiple rows within your todos table:

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

// insert statment
let stmt = `INSERT INTO todos(title,completed)  VALUES ?  `;
let todos = [
  ['Insert multiple rows at a time', false],
  ['It should work perfectly', true]
];

// execute the insert statment
connection.query(stmt, [todos], (err, results, fields) => {
  if (err) {
    return console.error(err.message);
  }
  // get inserted rows
  console.log('Row inserted:' + results.affectedRows);
});

// close the database connection
connection.end();

• Step 2: A point to note here is that you have only used one question mark (?) within the INSERT statement. Apart from this, the multiple row data is an array of arrays.
  
  You can also obtain the number of rows you have inserted by leveraging the ‘affectedRows’ property within the results object.
  
  Here’s the code snippet for the same:

>node insert3.js
Row inserted:2

This shows that 2 rows were successfully inserted within your MySQL table.

How to Query MySQL Databases using Node.js?

Here are the different methods you can use for Nodejs MySQL Querying:

• Nodejs MySQL Querying: Simple SQL Query
• Nodejs MySQL Querying: Avoiding SQL Injection

Nodejs MySQL Querying: Simple SQL Query

• Step 1: In this code snippet, you will be selecting all the data from the todos table within the todoapp database:

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

let sql = `SELECT * FROM todos`;
connection.query(sql, (error, results, fields) => {
  if (error) {
    return console.error(error.message);
  }
  console.log(results);
});

connection.end(); 

• Step 2: Following the execution of the aforementioned statement, you can run the following command:

>node select.js
[ RowDataPacket { id: 1, title: 'Learn how to insert a new row', completed: 1 },
  RowDataPacket { id: 2, title: 'Insert a new row with placeholders', completed:0 },
  RowDataPacket { id: 3, title: 'Insert multiple rows at a time', completed: 0 },
  RowDataPacket { id: 4, title: 'It should work perfectly', completed: 1 } ]

This statement will return 4 rows, as was expected from it.

• Step 3: You can even pass the data to the query by executing the following program that selects only the completed todo:

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

let sql = `SELECT * FROM todos WHERE completed=?`;
connection.query(sql, [true], (error, results, fields) => {
  if (error) {
    return console.error(error.message);
  }
  console.log(results);
});

connection.end();

• Step 4: In this example, you have used the question mark as the placeholder value of the field that has been completed. When you called the query() method, you passed an array as the second argument. The placeholder would then be replaced by the values of the array in sequence as follows:

>node select2.js
[ RowDataPacket { id: 1, title: 'Learn how to insert a new row', completed: 1 },
  RowDataPacket { id: 4, title: 'It should work perfectly', completed: 1 } ]

This query returns 2 rows along with the completed column and the value 1. This value reflects true within Node.js.

Nodejs MySQL Querying: Avoiding SQL Injection

• Step 1: Say, if you wish to query a todo on the basis of its id that springs from the argument within the command line, you can execute the following code snippet:

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

let id = process.argv[2]; // pass argument to query
let sql = `SELECT * FROM todos WHERE id=` + id ;

connection.query(sql, (error, results, fields) => {
  if (error) {
    return console.error(error.message);
  }
  console.log(results);
});

connection.end();

• Step 2: Now, say if you want to select the todo with the id 1, you can use the following command:

 >node select.js 1
    [ RowDataPacket { id: 1, title: 'Learn how to insert a new row', completed: 1 } ]

• Step 3: The statement executes correctly. Although, there is still a lingering issue that a suspicious user might try to exploit the program by supplying the SQL Code within the argument.
  
  To avoid this SQL Injection, you can either leverage the placeholder (?) as shown in the example prior to this step or you can leverage the escape() method of the connection or mysql object as follows:

let sql = `SELECT * FROM todos WHERE id = ` + mysql.escape(id);

How to Modify MySQL Tables with Node.js?

• Step 1: You can use the following code snippet to update the status of a todo on the basis of a specified id:

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

// update statment
let sql = `UPDATE todos
           SET completed = ?
           WHERE id = ?`;

let data = [false, 1];

// execute the UPDATE statement
connection.query(sql, data, (error, results, fields) => {
  if (error){
    return console.error(error.message);
  }
  console.log('Rows affected:', results.affectedRows);
});

connection.end();

• Step 2: In this example, you have leveraged placeholders within the UPDATE statement. Now, when you execute the UPDATE statement by invoking the query() method on the connection object, you also pass the data to the UPDATE statement in the form of an array.
  
  The placeholders will be replaced by the values in the data array in the specified order. In this instance, completed will be set to false and id would be set to 1. The results argument of the callback function consists of the ‘affectedRows’ property that depicts the number of rows updated by the UPDATE statement.

• Step 3: However, before you carry out the program, you need to check the row with id 1 within the todos table as follows:

mysql> SELECT * FROM todos WHERE id = 1;
+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         1 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)

• Step 4: Now, on running the snippet in the first step you get the following result:

>node update.js
Rows affected: 1

• Step 5: This program depicts a message that lets you know that the number of affected rows is 1. You can check it within the database as well with the following code snippet:

mysql> SELECT * FROM todos WHERE id = 1;
+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         0 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)

You can see clearly that the value within the completed column has been swapped to 0, which stands for false inside Node.js.

How to Delete Data from MySQL Tables using Node.js?

• Step 1: In this delete.js program, you will be deleting a row in the todos table on the basis of the row’s id:

let mysql  = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

// DELETE statment
let sql = `DELETE FROM todos WHERE id = ?`;

// delete a row with id 1
connection.query(sql, 1, (error, results, fields) => {
  if (error)
    return console.error(error.message);

  console.log('Deleted Row(s):', results.affectedRows);
});

connection.end();

• Step 2: For this example, you leveraged a placeholder within the DELETE statement. When you invoke the query() method on the connection object to carry out the statement, you sent the data to the DELETE statement as the second argument. The placeholder would be swapped by the input value so id will get the value 1 on the execution of the query as follows:

DELETE FROM todos WHERE id = 1

• Step 3: A point to note here is that if you possess multiple placeholders, you would have to pass an array to the query to send the data to the SQL statement. Now, to obtain the number of rows deleted, you can obtain the affectedRows property of the results argument. However, before executing the program, check the row with id 1 within the todos table:

>node delete.js
Deleted Row(s): 1

• Step 4: In the aforementioned command, the number of deleted rows is 1. You can even verify it within the database by using the SELECT statement as shown below:

mysql> SELECT * FROM todos WHERE id = 1;
Empty set (0.00 sec)

This depicts that the row with the id 1 was eliminated from the todos table.

Bonus: Calling/Running MySQL Stored Procedures using Node.js

• Step 1: For this process, you will first be creating a stored procedure filterTodo to query rows from the todos table on the basis of the value obtained from the completed field as follows:

DELIMITER $$

CREATE PROCEDURE `filterTodo`(IN done BOOLEAN)
BEGIN
    SELECT * FROM todos WHERE completed = done;
END$$

DELIMITER ;

• Step 2: The stored procedure created above will return the rows within the todos table on the basis of the done argument. So, if the done argument is true, the statement will return all the completed todos. If the done argument is false, this statement will return all the unfinished todos.
• Step 3: It is common knowledge that you can call a stored procedure within MySQL by leveraging the CALL statement. For instance, if you wish to call the ‘filterTodo’ stored procedure, you can carry out the following code snippet:

CALL filterTodo(true);

• Step 4: The aforementioned statement will return the following result set:

+----+--------------------------+-----------+
| id | title                    | completed |
+----+--------------------------+-----------+
|  4 | It should work perfectly |         1 |
+----+--------------------------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

• Step 5: The storedproc.js program mentioned below calls the ‘filterTodo’ stored procedure and gives you back the result set:

let mysql = require('mysql');
let config = require('./config.js');

let connection = mysql.createConnection(config);

let sql = `CALL filterTodo(?)`;

connection.query(sql, true, (error, results, fields) => {
  if (error) {
    return console.error(error.message);
  }
  console.log(results[0]);
});

connection.end();

• Step 6: The program uses the config.js module as mentioned in the steps given above. In the CALL statement, you leverage a placeholder to move data to the stored procedure.
  
  When you invoked the query() method on the connection object, you move the value of the done argument as the second argument of the query() method. Now, if you run the storedproc.js program, here’s what the result would look like:

> node storedproc.js
[ RowDataPacket { id: 4, title: 'It should work perfectly', completed: 1 } ]

As expected, the program depicted a solitary row.

Conclusion

This blog discusses the steps involved in MySQL JS Integration after a brief introduction to the salient benefits and features of MySQL and Nodejs. It also covers topics like deletion, creation, querying, running MySQL Tables with the help of Nodejs MySQL Connection.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.