In this workshop, we will be understand how to implement and combine data persistence and a simple web service to develop a simple application backend service.
After cloning/downlaoding the files, run npm install to install all dependencies needed to run the scripts. Alse update your user and password for your local MySQL login.
Please also install Postman for API Endpoint testing
we will be designing our web service based on the Model-View-Controller (MVC) Design with the above API endpoints.
MVC is a simple architecture where all components are seperated into 3 classes:
- The Model is the file that contains code to interact with the database.
- The View is the components that will display the model to the user.
- The Components that will handle any interaction with the user.
We will first use MySQL to create the database Schema and tables.
-
Locate the Create Schema icon on the top-left corner of MySQL workbench.
-
Once you'e clicked on the Create Schema icon, Enter userDatabase as the Schema name and click apply.
-
Right-click userDatabase schema on the left-side pane and select Create Table.
-
Enter the Table Name, Column Names and Column Datatypes and click Apply on the bottom-right corner.
-
Input the first 2 rows of values (excluding the last column created_at)
-
We will first create a folder called myFirstWS.
-
Open the folder you just created in VScode; create another 2 folders: controller and model
3. Right-click on the explorer pane empty space or press Ctrl + ` to open up the integrated terminal.
4. Run the following code in the integrated terminal to setup and install the necessary packages for your project.
npm init
npm install mysql2 body-parser express
We will make use of the createConnection() method from MySQL library to create a connection to the database.
To connect to the database, we will have to specify the host IP(localhost), Database User account name(root), Database User account password(root) and the Database(userDatabase) schema we are connecting to.
// Create the following in the databaseConfig.js file
const mysql = require("mysql") // Loads the MySQL library
const dbConnect = {
getConnection: () => {
let conn = mysql.createConnection({
host: 'localhost',
user: 'root', // Username of your mysql workbench
password: 'root',// password of your mysql workbench
database: 'userDatabase' // Database(Schema) name
})
return conn
}
}
module.exports = dbConnectAs the database connection and its settings will be used frequently by different files and modules, we will define the code in the model folder.
| URL | HTTP Method | POST Body | Result |
|---|---|---|---|
| /user | GET | empty | Retrieve ALL user data |
| /user/{id} | GET | empty | Retrieve user data with userID |
| /user | POST | JSON object | Insert new user record |
| /user/{id} | PUT | JSON object | Update existing user record |
| /user/{id} | DELETE | empty | Delete user with ID |
We will create a server.js file for the server to listen at port 8081
const app = require("./controller/app.js")
let port = 8081
const server = app.listen(port, () => {
console.log("Server is running at Port: " + port)
})An error might occur as the app.js file has not been created. (Don't worry and read on :D)
We will now proceed to design our database call to access the data in the database.
We will first be creating an asynchronous function called getUser() that will return a callback function once the data is returned from the database.
-
Create a file inside the model folder called
user.js -
Add the following code into user.js
const db = require("./databaseConfig.js") let User = { getUser : (userID, callback) => { let conn = db.getConnection() conn.connect((err) => { if (err){ console.log("Database Error!") return callback(err, null) } else{ console.log("Database Connected!") let QUERY = `SELECT * FROM Users WHERE userID = ?` conn.query(QUERY, [userID], (err, result)=>{ conn.end() if (err){ console.log("Query Error") return callback(err, null) } else{ console.log("Query Success!") return callback(null, result) } }) } }) } } module.exports = User
-
Defining the routes in the controller layer
At the controller layer, we will create a new router called
app.jsto define the application routing.const express = require("express") const User = require("../model/user.js") const app = express() // API Endpoints app.get('/api/user/:userID', (req, res) => { let userID = req.params.userID User.getUser(userID, (err, result) => { if (!err){ res.status(200).send(result) } else{ res.status(500).send("Internal Server Error") } }) }) module.exports = app
-
Test out our server
We will now test out the server. Open up the integrated terminal in your VScode and key in the following
node server.jsOpen your Browser and input
localhost:8081/api/user/1into the address bar and you should see the JSON object appear.
We will now be creating a new function to GET all users from the database.
// Insert the code after yout getUser function in user.js
const db = require("./databaseConfig.js")
let User = {
getUser : (userID, callback) => {
/* previous endpoint code */
},
getAllUser : (callback) => {
let conn = db.getConnection()
conn.connect((err) => {
if (err){
console.log("Database Error!")
return callback(err, null)
}
else{
console.log("Database Connected!")
let QUERY = `SELECT * FROM Users`
conn.query(QUERY, (err, result)=>{
conn.end()
if (err){
console.log("Query Error")
return callback(err, null)
}
else{
console.log("Query Success!")
return callback(null, result)
}
})
}
})
}
}
module.exports = UserWe created a new function called getAllUser() after the previous function for our controller to query the database.
Next, we will have to add another API endpoint inside app.js to receive and route the request.
// Endpoint to get ALL users
app.get('/api/users', (req, res) => {
User.getAllUser((err, result) => {
if (!err){
res.status(200).send(result)
}
else{
res.status(500).send("Internal Server Error")
}
})
})
To handle the HTTP POST Request, we need to use a middleware to extract the entire body portion of the incoming request stream.
-
Add Express bodyparser.
app.use(express.json()) // used to parse JSON objects app.use(express.urlencoded()) // used to parse URL-encoded bodies
The 2 lines above are the built-in bodyParser for parsing JSON objects and url-encoded contents of the incoming request. Insert it right before the first API endpoint as follows:
const express = require("express") const User = require("../model/user.js") const app = express() app.use(express.json()) app.use(express.urlencoded()) // API Endpoints app.get('/api/user/:userID', (req, res) => { /* endpoint codes */ }) app.get('/api/user/:userID', (req, res) => { /* endpoint codes */ }) module.exports = app
-
Create a new function to INSERT a new user into the database
addUser : (username, email, course, age, password, callback) => { let conn = db.getConnection() conn.connect((err) => { if (err){ return callback(err, null) } else{ console.log('Database Connected!') let QUERY = `INSERT INTO Users(username, email, course, age, password) VALUES (?, ?, ?, ?, ?)` conn.query(QUERY, [username, email, course, age, password], (err, result) => { conn.end() if (err){ console.log('Query Error!') return callback(err, null) } else{ console.log('Query Success!') return callback(null, result.affectedRows) } }) } }) }
Insert the code above right after the previous
getAllUser()function for our controller to query the database.We will now add the POST endpoint inside
app.jsto receive and route the request to insert the new user into the database.app.post('/api/user', (req, res) => { let username = req.body.username let email = req.body.email let course = req.body.course let age = req.body.age let password = req.body.password User.addUser(username, email, course, age, password, (err, result) => { if (!err){ console.log(result) res.status(200).send(result + ' records inserted!') } else{ res.status(err.statusCode).send("Server Error!") } }) })
This is where things get a little spicy; we are going to create a new function to UPDATE the email and password of an existing record in the database.
updateUser : (userid, email, password, callback) => {
let conn = db.getConnection()
conn.connect((err) => {
if (err){
return callback(err, null)
}
else{
console.log('Database Connected!')
let QUERY = `UPDATE Users SET email=?, password=? WHERE userID=?`
conn.query(QUERY, [email, password, userid], (err, result) => {
conn.end()
if (err){
console.log(err)
return callback(err, null)
}
else{
console.log('No. of records successfully updated: ' + result.affectedRows)
return callback(null, result.affectedRows)
}
})
}
})
}Input the following code after the previous API endpoint
// Endpoint for updating existing user in database
app.put('/api/user/:userid', (req, res) => {
let userID = req.params.userid
let email = req.body.email
let password = req.body.password
User.updateUser(userID, email, password, (err, result) => {
if (!err) {
res.status(200).send(result + ' record(s) updated!')
}
else {
res.status(err.statusCode).send('Server Error!')
}
})
})
Using postman, change the request to a PUT request and input the new email and password to be changed inside the body of the request.
The above image shows the updated information being accurately reflected using the GET user endpoint.
Lastly, we have the DELETE requests to remove those unwanted user informations 😏
deleteUser : (userID, callback) => {
let conn = db.getConnection()
conn.connect((err) => {
if (err){
console.log('Connection error!')
return callback(err, null)
}
else{
console.log('Database Connected!')
let QUERY = `DELETE FROM Users WHERE userID = ? `
conn.query(QUERY, [userID], (err, result) => {
conn.end()
if (err){
console.log('Query Error')
return callback(err, null)
}
else{
console.log('Query Success!')
console.log('Successfully deleted ' + result.affectedRows + ' record(s)')
return callback(null, result.affectedRows)
}
})
}
})
}Input the following into app.js after the previous endpoint.
app.delete('/api/user/:userID', (req, res) => {
let userID = req.params.userID
User.deleteUser(userID, (err, result) => {
if (!err) {
res.status(200).send('Successfully deleted ' + result + ' record(s)')
}
else{
res.status(500).send("Server Error")
}
})
})
This guide has covered the 4 basic CRUD functions for setting up a web service to query our database with the example source codes.
Content/repository - Kah Shin
Powerpoint Slides - Faythlii
© angkahshin 2021