Introduction

Connection Pooling is the mechanism that allows us to maintain a cache of database connections in order to ensure the reusability of the database instead of creating a new database every time leading to better performance and optimization of resources. Nodejs MySQL is some of the necessary integration for any web application, the go-to combination of Nodejs-MySQL represents an easy and powerful development tool for various use cases.

MySQL is a structured query language (SQL) based free and open-source relational database management (RDBMS) system developed by Oracle which allows you to build fast, powerful, and secure data storage systems, and build and maintain customer-facing web applications.

Node.js built on Google Chrome’s JavaScript Engine (V8 Engine) is an open-source, backend JavaCript runtime environment that executes JavaScript code outside a web browser, it is used to easily build fast and scalable network applications.

In this Nodejs and MySQL tutorial, we will be learning how to connect nodejs server to MySQL database in order to optimize the performance. In order to connect Nodejs MySQL you need to have MySQL and Nodejs installed on your computer.

Connecting Nodejs API to MySQL Database

Once you have MySQL and Nodejs installed on your system you can follow these simple steps to use MySQL in Node:-

Step-1: Create a new Node.js project

First and foremost using the Node Package Manager(NPM) create a new directory and initialize a new Node project. The following command can be executed in the terminal to create a new Node.js project:-

Step-2: Install MySQL node module

After the creation of the new directory and initializing the node project, in the second step install MySQL node module using the Node Package Manager(NPM). The following command can be executed to install MySQL node module:-

Step-3: Connect with MySQL

In the last step create an app.js file and copy-paste the code as shown below. Also, change the MySQL credentials accordingly with your system.

Now run the code using the following command:-

On establishing a successful connection you will get a message “Connected to MySQL Server” in the terminal.

Executing Queries in MySQL

Now let us have a look at how we can execute different queries in Nodejs MySQL:-

Inserting Rows into a Table

In order to add new rows to the table, we can use the following code:-

Querying Data into a Table

We can use the following code to query rows in the table:-

Deleting Rows in a Table

We can use the following query to delete rows in a table:-

Calling MySQL stored procedures using node

Stored Procedures are routine or sets of SQL Statements that can be invoked with a Call statement. It may have input parameters, output parameters, and parameters that are both input parameters and output parameters.

In order to call a stores procedure using a node, first of all, make sure that you have stored procedures created in MySQL, in case if you do not have stored procedures created in MySQL then you can use the below code to do the same:-

Now after creating stored procedures in MySQL, here is the code that can be used to call the stored procedures in MySQL using Node:-

Conclusion

In the above blog on Nodejs MySQL integration, we have discussed about what are the various steps that are involved in connecting Nodejs API to MySQL, How to execute various queries in MySQL, and about how to create stored procedures in MySQL, and how to call stored procedures using node Database, we also touched a bit on what is MySQL and Node.js and how their integration is beneficial. MySQL is an open-source relational database management system and is widely used across the world and with the node it really works well.

Source: Plato Data Intelligence: PlatoData.io