Skip to main content

Week2 : DB connection & CRUD

Database connection

  1. InstallLet's start by creating an index.js then initializing your JavaScript project by running npm init in your terminal after that let's download the libraries that we will be using today which are express and mysql library to your project, by enter the command linerunning npm install express mysql2.

Screenshot 2024-04-23 132412.png

        Then write the following code

const express = require("express");
const app = express();
const port = 5000;


app.listen(port, () => {
  console.log(`Example app listening on port ${port}`);
});
  1. Import the mysql library to your project.
const mysql = require("mysql2");
  1. Add the following code
  • This is the database configuration of your database.
const connection = mysql.createConnection({
  host: "tutorialdb.pspgun.com",
  port: "13308",
  user: "",
  password: "",
  database: "csc-105",
});
  1. Connect to the database
  • This line of code is using the connection variable from above to connect to the database using .connect() method.
connection.connect((err) => {
  if (err) {
    console.log(err);
  } else {
    console.log("Database is connected");
  }
});

The code

const express = require("express");
const app = express();
const mysql = require("mysql2");
const port = 3000;5000;

const connection = mysql.createConnection({
  host: "server2.mixkoap.com",
  port: "6105",
  user: "user",
  password: "password",
  database: "csc105-workshop",
});

// Connect to database
connection.connect((err) => {
  if (err) {
    console.log(err);
  } else {
    console.log("Database is connected");
  }
});

// GET `/` (http://127.0.0.1:5000/ or http://localhost:5000/)
app.get("/", (req, res) => {
  return res.status(200).json({
    success: true,
    data: "Welcome to the example API",
    error: null,
  });
});

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`);
});
  1. Restart the applcationapplication
  • If your database connection is invalid, it will show you an error. βŒπŸ‘‡πŸ»

  • If your database connection is valid, your application will run correctly. βœ…πŸ‘‡πŸ»

Screenshot 2024-04-23 134128.png

Let's write the endpoint that query todo-list data from the database and response to the user.
  • This the schema of sample database πŸ‘‡πŸ»

Example of GETRead method

  1. Let's create a first endpontendpoint query all todo-listbalance from the database.database using the code below.
  • In the path /todo/allbalance, it will query from allbalance dataof fromthe itemsusers table.
  • We will use the .query() method to insert your SQL query.
  • The second argument to the .query() method is a callback function (err, rows), which will be called when the MySQL send the response. The callback function takes two arguments, err and rows, which represent the error of the database and rows objects for response data from MySQL database.
  • If we found an error, we will response the error message to the client.
  • else, we will return row which is data from the itemsusers table from the database. πŸ‘‡πŸ»
app.get("/todo/all"balance", async (req, res) => {
	connection.query("SELECT// *Assign FROMthe items",params (err,as rows)a variable
	const userId => {req.query.userId;

	// Regex to check the userId is a number only or not
	const checkUserId = new RegExp(/^\d+$/).test(userId); // Boolean

	// Check if cannot find the datatodo_id inis thenot databaseexist thenor is not a number, return thejson with an error
	if (err)!userId || !checkUserId) {
		return res.json({
			success: false,
			data: null,
			error: err.message,"user_id is invalid",
		});
	}
	else// Query the data from the database
	try {
		// Get user balance data
		const userBalanceQuery = await connection
			.promise()
			.query(`SELECT balance FROM users WHERE id = ${userId}`);
		const userBalance = userBalanceQuery[0][0];
		// Check if user data is empty
		if (!userBalance) {
			return res.status(404).json({
				success: false,
				data: null,
				error: "User not found",
			});
		}
		// Return data to the client if success
		return res.json({
			success: true,
			data: rows,userBalance,
			error: null,
		});
	} catch (error) {
		console.error("Error:", error);
		return res.status(500).json({
			success: false,
			data: null,
			error: "Internal server error",
		});
	}
});
  1. Let's run your application again, and gouse topostman thewith path localhost:3000/todo/all5000/balance?userId=1
  • You will see the dataresult thatlike response to the client.this.

Screenshot 2024-04-23 171658.png

Another example of Read

  1. Let'Now let's installtry ato programcreate thatanother youendpoint, canwhich testis your/users/all APIfrom easier!the code below.

Postman is an API platform for building and using APIs. Postman simplifies each step of the API lifecycle and streamlines collaboration so you can create better APIsβ€”faster.

  • Open the Postman
  • Click +
  • Click Add a request to add a request.

  • (1) Name your request
  • (2) Enter your request path (localhost == 127.0.0.1)
  • (3) Click Send

  • After you clicked send, it will show the response data from yourthe backenddatabase application.try πŸ‘‡πŸ»
  • {
//

Get
    user
  • Youdata canconst useuserDataQuery Postman= await connection .promise() .query(`SELECT * FROM users`); const userData = userDataQuery[0]; // Check if user data is empty if (!userData) { return res.status(404).json({ success: false, data: null, error: "There is no user data in the database", }); } // Return data to testthe yourclient APIif fromsuccess yourreturn backendres.json({ applicationsuccess: here.true, thatdata: easieruserData, thanerror: testingnull, on}); your} website.catch 😊
  • (error) { console.error("Error:", error); return res.status(500).json({ success: false, data: null, error: "Internal server error", }); } });

      Let's back to our backend application.

      Example of queryCreate parameter URL

      1. Create a endpoint that querywill a todo-list by id using parameters from URL.create.

      • This is how query parameter works in URL πŸ‘†πŸ»
      • In this code, we have ...
      const todoId = req.query.todo_id;
      
      • that be a query parameter for this endpoint.
      app.get("/todo", (req, res) => {
        // Assign the params as a variable
        // https://medium.com/@joseph.pyram/9-parts-of-a-url-that-you-should-know-89fea8e11713
        const todoId = req.query.todo_id;
      
        // Regex to check the todo_is is a number only or not
        const checkTodoId = new RegExp(/^\d+$/).test(todoId); // Boolean
      
        // Check if the todo_id is not exist or is not a number, return json with an error
        if (!todoId || !checkTodoId) {
          res.json({
            success: false,
            data: null,
            error: "todo_id is invalid",
          });
        }
      
        connection.query(`SELECT * FROM items WHERE id = ${todoId}`, (err, rows) => {
          // Check if cannot find the data in the database then return the error
          if (err) {
            res.json({
              success: false,
              data: null,
              error: err.message,
            });
          } else {
            // Return data to the client if success
            if (rows[0]) {
              res.json({
                success: true,
                data: rows[0],
                error: null,
              });
            } else {
              res.json({
                success: true,
                data: null,
                error: null,
              });
            }
          }
        });
      });
      
      • Let's try sending a request by creating a new request at Postman

      • Create a request.
      • http://127.0.0.1:3000/todo?todo_id=1

      • Click Send
      • The response from your backend application πŸ‘‡πŸ»

      Example of the POST method

      1. Create an endpoint that create a user
      • Before writing a request, we need to install the library body-parser first.

      • npm i body-parser

      • Because Express JS cannot read the JSON body request directly, So we need to install body-parser first.

      const bodyParser = require("body-parser"); // import the body-parser
      
      // parse various different custom JSON types as JSON
      app.use(bodyParser.json({ type: "application/json" }));
      

      Code ...

      const express = require("express");
      const app = express();
      const mysql = require("mysql2");
      const bodyParser = require("body-parser"); πŸ‘ˆπŸ»
      const port = 3000;
      
      const connection = mysql.createConnection({
        host: "server2.mixkoap.com",
        port: "6103",
        user: "root",
        password: "root_apisitmaneerat",
        database: "csc105-workshop",
      });
      
      // Connect to database
      connection.connect();
      
      console.log("Database is connected");
      
      // parse various different custom JSON types as JSON
      app.use(bodyParser.json({ type: "application/json" })); πŸ‘ˆπŸ»
      
      app.get("/", (req, res) => {
        res.send("Hello World!");
      });
      .
      .
      .
      .
      .
      .
      
      • Create an endpoint
      app.post("/todo/create", (req, res) => {
        // The JSON body
        const payload = req.body;
      
        connection.query(
          `INSERT INTO users (username) VALUES (?)`, [payload.userName], (err, rows) => {
            // Check if cannot find the data in the database then return the error
            if (err) {
              res.json({
                success: false,
                data: null,
                error: err.message,
              });
            } else {
              // Return data to the client if success
              console.log(rows);
              if (rows) {
                res.json({
                  success: true,
                  data: {
                    message: "create success",
                  },
                });
              }
            }
          }
        );
      });
      
      • The request body mostly send in JSON format πŸ‘†πŸ»
      • Create a request in Postman and change the HTTP method to POST

      ****Remark - HTTP Methods πŸ‘‡πŸ»

      • Add the JSON body πŸ‘‡πŸ»

      http://127.0.0.1:3000/todo/create

      {
          "userName" : "Apisit Maneerat"
      }
      

      • Click Send

      • The response from your backend application. πŸ‘†πŸ»

      • In the Database πŸ‘‡πŸ»

      • For more detail

      Example of the PATCH method

      1. Create an endpoint that edit the data in the Database
      • For example we are going to change the name and detail from id = 1

      • Create the endpoint
      • For this endpoint we use .patch() because we are going to edit some fields that you specific in the Database.
      app.patch("/todo/edit", (req, res) => {
        // The JSON body
        const payload = req.body;
      
        console.log(payload);
      
        connection.query(
          "UPDATE items SET name = ?, detail = ? WHERE id = ?", [payload.name, payload.detail, payload.id],
          (err, rows) => {
            // Check if cannot find the data in the database then return the error
            if (err) {
              res.json({
                success: false,
                data: null,
                error: err.message,
              });
            } else {
              // Return data to the client if success
              if (rows) {
                res.json({
                  success: true,
                  data: {
                    message: "update successfully",
                  },
                });
              }
            }
          }
        );
      });
      
      • Create a request in Postman

      • Click Send

      Example of the DELETE method

      1. Create an endpoint that delete the data from the links table in the database.
      • For this endpoint, we will use .delete() because we are going to delete data from the database.
      • We use query string to get the data from the client
      • Create an endpoint πŸ‘‡πŸ»
      app.delete("/todo/delete", (req, res) => {
        // Assign the params as a variable
        const id = req.query.id;
        const todoId = req.query.todo_id;
      
        connection.query(
          `DELETE FROM links where id = ? AND todo_id = ?`, [id, todoId],
          (err, rows) => {
            // Check if cannot find the data in the database then return the error
            if (err) {
              res.json({
                success: false,
                data: null,
                error: err.message,
              });
            } else {
              if (rows) {
                res.json({
                  success: true,
                  data: {
                    message: "delete successfully",
                  },
                });
              }
            }
          }
        );
      });
      
      • Create a request in Postman

      • Click Send

      --- End of the example ----

      ** This is the finalized code for this example πŸ‘‡πŸ»

      const express = require("express");
      const app = express();
      const mysql = require("mysql2");
      const bodyParser = require("body-parser");
      const port = 3000;
      
      const connection = mysql.createConnection({
        host: "server2.mixkoap.com",
        port: "6103",
        user: "root",
        password: "root_apisitmaneerat",
        database: "csc105-workshop",
      });
      
      // Connect to database
      connection.connect();
      
      console.log("Database is connected");
      
      // parse various different custom JSON types as JSON
      app.use(bodyParser.json({ type: "application/json" }));
      
      app.get("/", (req, res) => {
        res.send("Hello World!");
      });
      
      app.get("/hello", (req, res) => {
        res.json({
          name: "Apisit Maneerat",
          nickName: "Mixko",
          university: "KMUTT",
        });
      });
      
      app.get("/todo/all", (req, res) => {
        connection.query("SELECT * FROM items", (err, rows) => {
          // Check if cannot find the data in the database then return the error
          if (err) {
            res.json({
              success: false,
              data: null,
              error: err.message,
            });
          } else {
            // Return data to the client if success
            res.json({
              success: true,
              data: rows,
              error: null,
            });
          }
        });
      });
      
      app.get("/todo", (req, res) => {
        // Assign the params as a variable
        // https://medium.com/@joseph.pyram/9-parts-of-a-url-that-you-should-know-89fea8e11713
        const todoId = req.query.todo_id;
      
        // Regex to check the todo_is is a number only or not
        const checkTodoId = new RegExp(/^\d+$/).test(todoId); // Boolean
      
        // Check if the todo_id is not exist or is not a number, return json with an error
        if (!todoId || !checkTodoId) {
          res.json({
            success: false,
            data: null,
            error: "todo_id is invalid",
          });
        }
      
        connection.query(`SELECT * FROM items WHERE id = ${todoId}`, (err, rows) => {
          // Check if cannot find the data in the database then return the error
          if (err) {
            res.json({
              success: false,
              data: null,
              error: `Data not found ${err.message}`,
            });
          } else {
            // Return data to the client if success
            if (rows[0]) {
              res.json({
                success: true,
                data: rows[0],
                error: null,
              });
            } else {
              res.json({
                success: true,
                data: null,
                error: null,
              });
            }
          }
        });
      });
      
      app.post("/todo/create", (req, res) => {
        // The JSON body
        const payload = req.body;
      
        connection.query(
          `INSERT INTO users (username) VALUES (?)`,
          [payload.userName],
          (err, rows) => {
            // Check if cannot find the data in the database then return the error
            if (err) {
              res.json({
                success: false,
                data: null,
                error: err.message,
              });
            } else {
              // Return data to the client if success
              if (rows) {
                res.json({
                  success: true,
                  data: {
                    message: "create successfully",
                  },
                });
              }
            }
          }
        );
      });
      
      app.patch("/todo/edit", (req, res) => {
        // The JSON body
        const payload = req.body;
      
        console.log(payload);
      
        connection.query(
          "UPDATE items SET name = ?, detail = ? WHERE id = ?",
          [payload.name, payload.detail, payload.id],
          (err, rows) => {
            // Check if cannot find the data in the database then return the error
            if (err) {
              res.json({
                success: false,
                data: null,
                error: err.message,
              });
            } else {
              // Return data to the client if success
              if (rows) {
                res.json({
                  success: true,
                  data: {
                    message: "update successfully",
                  },
                });
              }
            }
          }
        );
      });
      
      app.delete("/todo/delete", (req, res) => {
        // Assign the params as a variable
        const id = req.query.id;
        const todoId = req.query.todo_id;
      
        connection.query(
          `DELETE FROM links where id = ? AND todo_id = ?`,
          [id, todoId],
          (err, rows) => {
            // Check if cannot find the data in the database then return the error
            if (err) {
              return res.json({
                success: false,
                data: null,
                error: err.message,
              });
            } else {
              if (rows) {
                res.json({
                  success: true,
                  data: {
                    message: "delete successfully",
                  },
                });
              }
            }
          }
        );
      });
      
      app.listen(port, () => {
        console.log(`Example app listening on port ${port}`);
      });
      

      More examples for those who want to create more than one query in one endpoint

      app.get("/todo/from", async (req, res) => {
        const userId = req.query.userId;
      
        // User data
        const userData = await new Promise((resolve, reject) => {
          connection.query(
            `SELECT * FROM users WHERE id = ?`,
            [userId],
            (err, rows) => {
              if (err) {
                res.json({
                  suceess: false,
                  error: err.message,
                });
              } else {
                resolve(rows[0]);
              }
            }
          );
        });
      
        // User's todos
        const userTodos = await new Promise((resolve, reject) => {
          connection.query(
            `SELECT * FROM items WHERE owner_id = ?`,
            [userId],
            (err, rows) => {
              if (err) {
                res.json({
                  suceess: false,
                  error: err.message,
                });
              } else {
                resolve(rows);
              }
            }
          );
        });
      
        res.json({
          success: true,
          data: {
            user_data: userData,
            todos: userTodos,
          },
          error: null,
        });
      });