This tutorial is walk through on how to create a node.js API and connect it to a MySQL database using a : pool connection . To be able to follow along this process you should have MySQL and nodejs installed on your computer. You can use the following links if needed:
Related Articles:
- Why is Connection Pooling better than Single Connection?.
- How to install MySQL Server on Windows .
- How to install MySQL Server on mac OS.
- How to install nodeJs.
- How to create MySQL database using node.js.
- Automating Your MySQL Database with Triggers.
- How to use ON DELETE CASCADE in MySQL.
- MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
- Using MySQL Event Scheduler: A Complete Guide.
- How to interact with MySQL database using async/await promises in node.js ?
- How to use Sequelize async/await to interact with MySQL database in Node.js.
- MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
- ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js
- ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API.
- How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
- How to store Session in MySQL Database using express-mysql-session.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API
Create node.js API
Once you have node.js running in your computer, in your projects folder, create a new folder for your API: myAPI in our case. You can do that manually or by running the command line: mkdir myAPI . Then open the blank folder myAPI in your code editor (mine is Visual Studio code) then execute the following command in the terminal:
npm init
Now we are ready to set up our environment and install everything we need. First install express by running this Command:
npm install --save express
then install body parser to parse json response:
npm install --save body-parser
Inside your myAPI folder create two new files: server.js and the apiRouter.js which you will call from server.js.
server.js
//server.js
const express =require('express');
const app = express();
const bodyParser = require('body-parser');
const apiRouter = require('./apiRouter');
const PORT= 3000;
app.use(bodyParser.json());
app.use('/apiRouter',apiRouter);
app.listen(PORT, ()=>{
console.log(`server is listening on ${PORT}`);
});
module.exports = app;
apiRouter.js
//apiRouter.js
const express =require('express');
const apiRouter = express.Router();
module.exports = apiRouter;
To watch your server you need to install nodemon, so you don t need to restart you server every time you make changes in your code.
npm install --save nodemon
Add the following code line to your package.json:
"start": "nodemon server.js "
So your Package.json will look like this:
//package.js
{
"name": "myapi",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"start": "nodemon server.js "
},
"author": "",
"license": "ISC",
"dependencies": {
"express": "^4.17.1",
"nodemon": "^2.0.4"
}
}
Install MySQL module
To access MySQL database with node.js you need to install mysql module using this command line:
npm install --save mysql
Connect node.js to MySQL database
Inside myAPI folder create a new file db.js where you will setup your connection to the database and write your queries after importing mysql module by adding this line of code to db.js:
const mysql =require(‘mysql’)
In this tutorial, We are using connection pool to connect our database. If you want to now more about connection pooling and how it works you can check this article: Why is Connection Pooling better than Single Connection?.
//db.js
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 10, // the number of connections will node hold open to our database
password: “yourDBpassword”,
user: “yourusername”,
database: “yourDBname”,
host: “locahost”,
port: “yourDBport”
});
let db = {}; //create an empty object that you will use later to write and export your queries.
module.exports = db
Write MySQL queries
Let s say that we have a table in our database named “Employee” that we created using the following query:
CREATE TABLE Employee (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
position varchar(255) NOT NULL,
wage INT NOT NULL,
PRIMARY KEY (id));
To get all employees from this table you need to write a select query as method of the db object in your db.js:
// db.js
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 10,// the number of connections will node hold open to our database
password: “yourDBpassword”,
user: “yourusername”,
database: “yourDBname”,
host: “locahost”,
port: “yourDBport”
});
let db = {};
db.getAllEmployees = () =>{
return new Promise((resolve, reject)=>{
pool.query('SELECT * FROM Employee ', (error, employees)=>{
if(error){
return reject(error);
}
return resolve(employees);
});
});
};
module.exports = db;
We used promises instead of callback functions in our queries because it makes coding easier when we are using async/await to write our routes. For more details about async/await functions and promises go here: How to interact with MySQL database using async/await promises in node.js ?
Now you can do the same for INSERT, UPDATE and DELETE.
Adding Routes
Inside your apiRouter.js pull in the db object from db.js by adding:
const db = require(‘./db.js’);
Then make a call to the database using the query we wrote already in db.js, for that, we use async function so it doesn’t block the processing of the code below it.
//apiRouter.js
const express =require('express');
Const apiRouter = express.Router();
Const db = require(‘./db.js’);
apiRouter.get('/', async (req, res, next)=>{
try {
const employees = await db.getAllEmployees();
res.status(200).json({employees: employees});
} catch(e) {
console.log(e);
res.sendStatus(500);
}
});
module.exports = apiRouter;
Conclusion
Connection Pooling is the smartest way to connect a database, so a connection can be reused after releasing it. The combination node.js-MySQL represent a powerful and easy development tool .
You might also like:
Automating Your MySQL Database with Triggers.
How to use ON DELETE CASCADE in MySQL.
MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
Using MySQL Event Scheduler: A Complete Guide.
Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.
How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
How to store Session in MySQL Database using express-mysql-session.
How to interact with MySQL database using async/await promises in node.js ?
How to use Sequelize async/await to interact with MySQL database in Node.js.
MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
How to add Routes to insert data into MySQL database-related tables in Node.js API?
Example How to use initialize() Function in Node.js/Express API .
Why is Connection Pooling better than Single Connection?.
How to create MySQL database using node.js.
Nodemailer + Gmail: How to Send Emails from Node.js API using Gmail.
Node.js + Nodemailer : How to send Emails via SMTP with Nodemailer
How to get Request Origin in Express.js?