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
|
1 2 3 |
server.js /routes/routes.js /config/config.js |
After creating the three files keeping in mind the hierarchy. Open the terminal in our project directory and run the following command.
|
1 |
npm init |
Once the project is initialized we are going to install some basic packages that we will use in this project.
|
1 |
npm install express morgan body-parser mysql --save |
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.
|
1 2 3 4 5 6 7 8 9 |
module.exports = { dbConfig : { host : 'localhost', user : 'root', password : '', database : 'nodemysqldemo' } }; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
let express = require('express'); let bodyParser = require('body-parser'); let mysql = require('mysql'); let morgan = require('morgan'); let app = express(); let config = require('./config/config'); let connection = mysql.createConnection(config.dbConfig); app.use(bodyParser.json()); app.use(bodyParser.urlencoded({extended :true})); app.use(morgan('dev')); connection.connect((err)=> { if(err){ console.log('error',err); }else { console.log('successfully connected to Database ',config.dbConfig.database); } }); connection.on('error', (err) => { console.log('error',err); }); require('./routes/route')(app,express,connection); app.listen(3000,() =>{ console.log('server listening on Port 3000'); }); |
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 !!
|
1 2 3 |
connection.on('error', (err) => { console.log('error',err); }); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
module.exports = function (app,express, mysqlConnection) { let router = express.Router(); router.route('/users') .get((req,res)=> { mysqlConnection.query('SELECT * FROM `users` ', function (error, results) { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }); }) .post((req,res)=> { mysqlConnection.query({ sql: 'insert into `users` (first_name,last_name,job) values (?,?,?)', values : [req.body.firstName,req.body.lastName,req.body.job], },(error,results) => { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }) }); router.route('/users/:name') .get((req,res)=> { mysqlConnection.query({ sql: 'CALL GetUserByFirstName(?)', values : [req.params.name], },(error,results) => { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }) }) .put((req,res)=> { mysqlConnection.query({ sql: 'update `users` set first_name = ? ,last_name = ?,job = ? where first_name = ? ', values : [req.body.firstName,req.body.lastName,req.body.job,req.params.name], },(error,results) => { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }) }) .delete((req,res)=> { mysqlConnection.query({ sql: 'delete from `users` where first_name = ? ', values : [req.params.name], },(error,results) => { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }) }); app.use(router); }; |
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.
|
1 2 3 4 5 6 7 8 9 |
mysqlConnection.query('SELECT * FROM `users` ', function (error, results) { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }); |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysqlConnection.query({ sql: 'insert into `users` (first_name,last_name,job) values (?,?,?)', values : [req.body.firstName,req.body.lastName,req.body.job], },(error,results) => { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysqlConnection.query({ sql: 'CALL GetUserByFirstName(?)', values : [req.params.name], },(error,results) => { if(error){ res.json({data : error}); res.end(); }else { res.json({data : results}); res.end(); } }) |
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
|
1 |
node server.js |
Now you can query your server on port 3000 by using postman or any other app.
Testing to get all the Users and creating a new User.
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.
Next time try to use mysql using sequelize. Its a ORM mapper for mysql and its works as mongoose
That’s interesting i will look into it. Thanks.
Nice blog. Thanx