Today, we are going to see how to create an API in Node.js, with MySQL database.
Please follow the below steps,
Step 1: Let's start with creating a project in Node.js, using express-generator package. Please refer to my other article for more information about express-generator: Creating Node Application Using Express Generator.
Step 2: Now, let's install MySQL package by using the command: npm install mysql.
Step 3: The next step would be to connect our application with MySQL db.

Let's create a file database.js in our project and write the following code in it:
  1. var mysql = require('mysql');
  3. module.exports = function handle_db(req, res) {
  4. var pool = mysql.createPool({
  6. connectionLimit: 100,
  7. host: 'localhost',
  8. user: 'root',
  9. password: '*******',
  10. database: 'test'
  12. });
  14. pool.getConnection(function (err, connection) {
  15. if (err) {
  16. console.error("This is error msg, when connecting to db: " + err);
  17. connection.release();
  19. res.json({ "code": 100, "status": "Error in connecting database" });
  20. return;
  21. }
  23. console.log("from db config: connected as id: " + connection.threadId);
  24. connection.on('error', function (err) {
  25. res.json({ "code": 100, "status": "Error in connection database" });
  26. return;
  28. });
  29. return connection;
  31. });
  32. return pool;
  33. }
The code creates a connection with my local database and then, provides a connection object as an output from the function getConnection. We will use this object whenever we need a db connection object.
Now, let's include this file in our main file: app.js. Use the code, as shown below:
  1. var pool = require('./database')();
Step 4: We shall now create a welcome message from our API. For this, let's create a file routes.js in our project and write the following code:
  1. module.exports = function (app, pool) {
  2. app.get('/', function (req, res) {
  3. res.send("Welcome to C-sharp corner API");
  4. });
  5. };
We have, for now, set the initial application port to 5050, by using the below code in app.js.
  1. app.listen(process.env.PORT || 5050);
  2. console.log("App listening on port 5050");
Let's run the application and view the output in Postman App. You can also see the same in a browser:
Step 5: Let's have a look on what data is there currently, in our table : users:

Step 6: Let's create a folder called api. Inside it, let's create a file user.api.js and create a get function which fetches all the data from the users table and provide to us. The code for the same is as written below:
  1. module.exports = function (app, pool) {
  3. app.get('/api/users', function (req, res) {
  4. var con = pool.getConnection(function (err, con) {
  5. con.query('SELECT * FROM users', function (err, rows) {
  7. if (!err) {
  9. console.log(rows);
  10. res.json(rows);
  12. }
  13. else {
  14. console.error("From users.api.js :" + err);
  15. res.json(err);
  16. }
  17. con.release();
  18. });
  19. });
  21. });
The code states that when a GET request is sent with the url "/api/users", then we select all the data from the users table and send it to the response object.
Please make sure that you update the code in the routes.js, as shown below, to include this new file:
  1. module.exports = function (app, pool) {
  3. require('./api/user.api')(app,pool);
  4. app.get('/', function (req, res) {
  6. res.send("Welcome to C-sharp corner Api");
  7. });
  8. };
We can test this get statement in Postman app, using the url : localhost:5050/api/users

Please see the below screenshot containing the output.



Step 7: Now, let's create a GetByID, using the following code, in user.api.js file.
  1. app.get('/api/users/:id', function (req, res) {
  2. con = pool.getConnection(function (err, con) {
  3. con.query('SELECT * FROM users where userid=?', [req.params.id], function (err, rows) {
  4. if (!err) {
  6. console.log(rows);
  7. res.json(rows);
  9. }
  10. else {
  11. console.error("From users.api.js :" + err);
  12. res.json(err);
  13. }
  14. con.release();
  15. });
  16. });
  18. });
The code simply gets user on the basis of userid. Output can be seen below.



Step 8: Now, let's create an update function by including this code in user.api.js file.
  1. app.put('/api/users/:id', function (req, res) {
  2. con = pool.getConnection(function (err, con) {
  3. con.query('UPDATE users SET UserName=? , Description=? where UserID=?', [req.body.username, req.body.desc, req.params.id], function (err, rows) {
  4. if (!err) {
  6. console.log(rows);
  7. res.json(rows);
  9. }
  10. else {
  11. console.error("From users.api.js :" + err);
  12. res.json(err);
  13. }
  14. con.release();
  16. });
  17. });
  18. });
Please note that there are i/p parameters which we can easily provide using Postman, as shown below,



Step 9: In the same way, insert and delete functions would be coded, as shown below with respective screenshots:
INSERT
  1. app.post('/api/users/', function (req, res) {
  2. con = pool.getConnection(function (err, con) {
  3. con.query('INSERT INTO users (UserID,UserName,Description) VALUES(?,?,?)', [req.body.userid,req.body.username, req.body.desc], function (err, rows) {
  4. if (!err) {
  6. console.log(rows);
  7. res.json(rows);
  9. }
  10. else {
  11. console.error("From users.api.js :" + err);
  12. res.json(err);
  13. }
  14. con.release();
  16. });
  17. });
  18. });


DELETE
  1. app.delete('/api/users/:id', function (req, res) {
  2. con = pool.getConnection(function (err, con) {
  3. con.query('DELETE FROM users WHERE UserID=?', [req.params.id], function (err, rows) {
  4. if (!err) {
  6. console.log(rows);
  7. res.json(rows);
  9. }
  10. else {
  11. console.error("From users.api.js :" + err);
  12. res.json(err);
  13. }
  14. con.release();
  16. });
  17. });
  18. });


The complete code of user.api.js is, as follows:
  1. module.exports = function (app, pool) {
  3. app.get('/api/users', function (req, res) {
  4. var con = pool.getConnection(function (err, con) {
  5. con.query('SELECT * FROM users', function (err, rows) {
  7. if (!err) {
  9. console.log(rows);
  10. res.json(rows);
  12. }
  13. else {
  14. console.error("From users.api.js :" + err);
  15. res.json(err);
  16. }
  17. con.release();
  18. });
  19. });
  21. });
  23. app.get('/api/users/:id', function (req, res) {
  24. con = pool.getConnection(function (err, con) {
  25. con.query('SELECT * FROM users where userid=?', [req.params.id], function (err, rows) {
  26. if (!err) {
  28. console.log(rows);
  29. res.json(rows);
  31. }
  32. else {
  33. console.error("From users.api.js :" + err);
  34. res.json(err);
  35. }
  36. con.release();
  37. });
  38. });
  40. });
  42. app.put('/api/users/:id', function (req, res) {
  43. con = pool.getConnection(function (err, con) {
  44. con.query('UPDATE users SET UserName=? , Description=? where UserID=?', [req.body.username, req.body.desc, req.params.id], function (err, rows) {
  45. if (!err) {
  47. console.log(rows);
  48. res.json(rows);
  50. }
  51. else {
  52. console.error("From users.api.js :" + err);
  53. res.json(err);
  54. }
  55. con.release();
  57. });
  58. });
  59. });
  61. app.post('/api/users/', function (req, res) {
  62. con = pool.getConnection(function (err, con) {
  63. con.query('INSERT INTO users (UserID,UserName,Description) VALUES(?,?,?)', [req.body.userid,req.body.username, req.body.desc], function (err, rows) {
  64. if (!err) {
  66. console.log(rows);
  67. res.json(rows);
  69. }
  70. else {
  71. console.error("From users.api.js :" + err);
  72. res.json(err);
  73. }
  74. con.release();
  76. });
  77. });
  78. });
  80. app.delete('/api/users/:id', function (req, res) {
  81. con = pool.getConnection(function (err, con) {
  82. con.query('DELETE FROM users WHERE UserID=?', [req.params.id], function (err, rows) {
  83. if (!err) {
  85. console.log(rows);
  86. res.json(rows);
  88. }
  89. else {
  90. console.error("From users.api.js :" + err);
  91. res.json(err);
  92. }
  93. con.release();
  95. });
  96. });
  97. });
  99. };
Hope this article helped in understanding how to create an API in Node.js.
I will next take up an article on how to consume this API in a Node.js Web App.