Select Record In Mysql

To select records from a MySQL database using Node.js. Now, we can use the mysql package to execute SQL queries.

The SQL query for selecting records typically follows the format of SELECT columns FROM table WHERE conditions. This query specifies the columns to retrieve and optional data filtering conditions.

Select records:

Once the connection is established. we can execute SQL queries to select records from Tables.

Use the "query()" method of the connection object to execute SQL queries.

This will execute the SQL query to select all records from the "employee" table.

Example:

// SQL query to select records
const selectRecordsQuery = 'SELECT * FROM employee';

// Execute the SQL query
dbInstance.query(selectRecordsQuery, (err, rows) => {
  if (err) {
    console.error('Error selecting records:', err);
    return;
  }
  console.log('Selected records:', rows);
});

Replace the table name with your specific table name.

Handle errors and results appropriately within the callback function.

Full Code:

Create the file name `server.js` and paste the below code.

Example:

let http = require("http");
const url = require("url");
const mysql = require("mysql");

const { fetchEmployees, fetchEmployee } = 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 === 'GET' && pathname === "/get-employee")

    if (pathname === "/get-employee") {
        fetchEmployee(req, res, dbInstance, query);
    } else if (pathname === "/get-employees") {
        fetchEmployees(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 fetch single record and multiple records from the MySQL database using Node.js.

Example:

// SQL query to select record
const SelectAllRecord = "SELECT * FROM employee";
const SelectSpecificRecord = (email) =>
  `SELECT * FROM employee WHERE email='${email}'`;

async function fetchEmployee(req, res, dbInstance, query) {
  // Checking Connection is establish
  if (dbInstance) {
    // Execute the SQL query
    // Checking Connection is establish
    dbInstance.query(
      SelectSpecificRecord(query.email),
      (err, result) => {
        if (err) {
          console.error("Error While Fetching record:", err);
          res.end("Error While Fetching record:", JSON.stringify(err));
        }
        res.end(JSON.stringify(result));
      }
    );
  }
}

async function fetchEmployees(req, res, dbInstance) {
  // Checking Connection is establish
  if (dbInstance) {
    // Execute the SQL query
    dbInstance.query(SelectAllRecord, (err, result) => {
      if (err) {
        console.error("Error While Fetching record:", err);
        res.end("Error While Fetching record:", JSON.stringify(err));
      }
      res.end(JSON.stringify(result));
    });
  }
}

module.exports = { fetchEmployee, fetchEmployees };

Here, we have created functions `fetchEmployee` and `fetchEmployees` to fetch specific and multiple records from DB.

<-- Insert Record

Update Record -->