To update records in a MySQL database using Node.js. Now, we can use the mysql package to execute SQL queries.
This Query is used to update user information, adjust inventory levels, or make any necessary changes to stored data.
Once the connection is established, we can execute SQL queries to update records.
Use the "query()" method of the connection object to execute SQL queries.
This will execute the SQL query to update records in the "employee" table where the "email" is 'alice@example.com' and the "age" is 22.
// SQL query to update records
const updateRecordQuery = 'UPDATE employee SET name = ? WHERE email = ? AND age = ?';
const values = [ 'Alice Smith', 'alice@example.com', 22];
// Execute the SQL query
dbInstance.query(updateRecordQuery, values, (err, result) => {
if (err) {
console.error('Error updating records:', err);
return;
}
console.log('Records updated:', result.affectedRows);
});
Replace the table name and column names with your specific table and column names.
Also, replace the values array with the values you want to update in the table.
Handle errors and results appropriately within the callback function.
Create the file name `server.js` and paste the below code.
import the `updateEmployee` functions from the `employeeController` file and place them inside `/update-employee` conditions respectively.
let http = require("http");
const url = require("url");
const mysql = require("mysql");
const { updateEmployee } = require("./employeeController");
let dbInstance = mysql.createConnection({
host: "localhost", // MySQL server hostname
user: "root", // MySQL username
password: "password", // MySQL password
database: "your_database_name", // MySQL database name
});
// Connect to the MySQL server
dbInstance.connect((err, result) => {
if (err) {
console.error("Error connecting to MySQL database:", err);
return;
}
console.log("Connected to MySQL database");
});
http
.createServer((req, res) => {
res.writeHead(200, { "Content-Type": "text/html" });
// Parse the requested URL
const parsedUrl = url.parse(req.url, true);
const { pathname, query } = parsedUrl;
// update if condition with if(req?.method === 'PUT' && pathname === "/update-employee")
if (pathname === "/update-employee") {
updateEmployee(req, res, dbInstance);
} else {
res.end("Server Listening...");
}
})
.listen(3002, () => {
console.log(`server listening on port 3002!`);
});Create the file name `employeeController.js` and paste the below code.
We have created functions to update record in the MySQL Table using Node.js.
async function updateEmployee(req, res, dbInstance) {
// SQL query to update records
const updateRecordQuery =
"UPDATE employee SET name = ? WHERE email = ? AND age = ?";
const values = ["Alice Smith", "Alice@gmail.com", 30];
// Checking Connection is establish
if (dbInstance) {
// Execute the SQL query
// Checking Connection is establish
dbInstance.query(updateRecordQuery, values, (err, result) => {
if (err) {
console.error("Error While updating record:", err);
res.end("Error While updating record:", JSON.stringify(err));
}
res.end(JSON.stringify(result));
});
}
}
module.exports = { updateEmployee };
To update employee data within Table, we often use the `PUT` HTTP Method to update data in DBs, but for testing if you try to hit GET Request on `http://localhost:3002/update-employee` from a web browser. it will return the updated document count from the Table if it exists.