Using Mysql with Node.js

Hey Guys, So i am working on a project in which i had to use mysql with node.js….. wait what ? Yes as mysql being one of the most used databases it is often used with Node.js. So i decided to create tutorial about it. In this tutorial we are going to a make a simple user CRUD.

Pre-req :  You should know basic node.js and SQL

lets get started. Below is the basic structure of the Server.
server.js is the entry point we will setup our node.js server and connect it to the database.
config.js is where the configurations of the server will lie. like database urls and authentication information.
route.js is where we define our API’s routes for our node.js

After creating the three files keeping in mind the hierarchy. Open the terminal in our project directory and run the following command.

Once the project is initialized we are going to install some basic packages that we will use in this project.

We will use express for routing. Morgan for creating server logs, body-parser for parsing the body of requests and mysql for well, using mysql.

First of all lets setup database configurations. we will put this configuration in our config.js file.

We are exporting a configuration document in which there is a sub-document “dbConfig” which contains the configuration of database.
host : represent the host where your database is on. In my case its on localhost. values could be ip address or a dns.
user : your database username which you will use for the  authentication. In my case it was root.
password : since i had no password on my localhost database i left it blank. you can put your password here.
database :  it is the name of the database you are connecting to. in my case ‘nodemysqldemo’.
port :  by default mysql port is 3306. since my db is running on the default port i omited it however if your database is running on some other port you will need to put this field.
Tip : Putting DB config and credentials in a server config file is not recommended for production. It must be placed in your path or in your .env file.

Now in your server.js file, write the following code.

From line 1 to 4 we are requiring the packages we installed.
On line 5 & 6 we created our app and imported server configurations from the configuration file.
In line 7 we are setting up a connection with mysql by supplying the dbConfig from the configurations file.

from line 9 to 11 we setup the middleware for bodyparsing and server logging.

On line 13 we are connecting mysql. In the callback function we check to if there are any errors or not. If not then we are successfully connected of mysql database !!

In the code on line 20 we are setting up error handling in-case our connection breaks for any reason it will print it on the console.  And from line 29 to onward we are requiring and setting up routes from routes.js by passing our app,express and connection variable into the function  and starting the server on port 3000.

Now we will setting our routes for APIs in route.js file.

We this file we are creating 5 routes which are

1) get -> /users    which returns all the users
2) post -> /users  which creates a user.
3) get -> /users/name    which returns user matching the name
4) put -> /users/name  which updates a user matching the name.
5) delete -> /users/name which deletes a user matching the name.

There are two ways two query mysql and i have used and will  explain both ways to query mysql.

We will use our “Connection” variable which we passed from server.js after our server was connected to mysql. First way to query mysql is by passing a simple query string as first paramenter in  connection.query() and pass second parameter as a callback functions which will have error or results of the query.

The Second way to query mysql is by passing a json object as a first parameter in connection.query(). It must have a key sql where we will write our sql query.  If you noticed there are ? in the query. These are escaping characters in which we can replace with our values. so to replaces these we pass along another key values which is a array of values that needs to be replaced in respective order i.e value on first index will replace the first question mark. In our case we are replacing the questions marks with the value that is being sent in the body. and the second parameter is the same passing a callback function with error and results of the query.

Those are the ways to query mysql using node.js.

Storage procedures

we can also use storage procedures and call them in our code.Here is an example for it.

Again i used json object method to query mysql. In my sql key i passed in my command to call the procedure GetUserByFirstName and i have used ? in the procedure as well to replace it with the name i get from req params.

Now all you need to do is run the code by running the command in your  project directory terminal

Now you can query your server on port 3000 by using postman or any other app.

 

Get -> /users

Testing to get all the Users and creating a new User.

Post -> /users

 

Well that’s about it.  you can download or clone this entire project from here. If you can have queries post a comment below.  🙂 Until next time !

Have a Good day.

 

3 thoughts on “Using Mysql with Node.js

Leave a Reply to Rohail Najam Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.