Skip to main content

Nodejs and MySQL Tutorial With Example

Today We are going to learn MySQL database connectivity with nodejs . MySQL is very popular and open source database people use so if you also going to use mysql in your project then this tutorial will help you.  You know database is required to save user data and mysql is good choice in this. In this tutorial i will cover calling simple query , stored procedure using nodejs application on mysql and how to call db query in high load website so lets start.

Prerequisites

  • Nodejs , npm and mysql datbase installed on your machine.

here is my folder structure

and my package.json file.

Now create a directory and run command, if you already running a project you can ignore this step.

this will prompt you too create package.json file, fill information as per your project. Now we are going to install project dependency

this will install express nodejs framework. This will help us for creating server and routing.

Creating MySQL table

Login to your mysql database and create table for demo purpose, I am using phpmyadmin for that.

after creating table I am going to insert some dummy data to this table so later i can fetch with nodejs

so here is my table structure in mysql database

mysql-table-structure

Installing Node mysql module [Driver]

Now I am going to use node-mysql module to connect nodejs with mysql database, this module is very popular and well documented

this will install mysql driver to our project and here I am going to use it in my index.js file.

If you see my index.js file what i am doing here , i created express app and created mysql connection with help of mysql.createConnection , you have to pass server name, username , password and database.

after that in root request, i am calling connection.connect() , this will establish a connection to mysql database and using connection.query to execute query to fetch the student where id =1. If you run this app  using node index.js then you will see one student record on your browser by hitting http://localhost:4000/

nodejs-mysql-query-result

Till here, you are able to execute sql query with parameter but Its not good practice in production everyone use stored procedure to call database so for that I am going to create one stored procedure and execute in mysql and later call with nodejs.

and here i added a new route in my index.js file to call stored procedure for demo purpose.

add this route to your index.js file and run node index.js again and visit http://localhost:4000/sp , you will see all student record.

nodejs-mysql-stored-procedure-result

till here you are able to call mysql database with query as well as with stored procedure. If you look to above code, for each request i create a connection using connection.connect() method and close after ending the call with help of connection.end();

Connection Pooling in MySQL

You know creating connection is very time taken process and for each request if you do then it will take more time creating connection rather doing actual work.
For high traffic website always use connection pooling, what it will do, it will cache the connection and reuse for another request so time will be saved to create a new connection.

So i am going to use connection object with connection pooling in mysql

You see, now i am using mysql.createPool method to create connection pool , this method also need servername, database, username and password.

While executing database query first i am requesting pool to give me a connection and passing a callback. whenever i get a connection callback will get executed and in this callback i am calling my actual stored procedure as earlier. after finish my db call the first thing i do is releasing the connection with connection.release(); method.

Always try to use connection pool whenever do database call. If you want to read more about node mysql module then you can refer their well maintained documentation.

Leave a Reply

Your email address will not be published. Required fields are marked *