Insert Record In Mysql

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

This operation is essential for adding new data, creating relationships between data.

The SQL query for inserting a record into a table is typically in the form of INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...). This query specifies the table name, columns to insert data into, and corresponding values.

Create a MySQL connection:

First, create a MySQL connection to your MySQL server.

Example:

const mysql = require('mysql');

const dbInstance = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'your_database_name'
});

dbInstance.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
    return;
  }
  console.log('Connected to MySQL');
});

Replace the connection options host, user, password, database with the appropriate values for your MySQL database server.

Insert records:

Once the connection is established, we can execute SQL queries to insert records.

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

This will execute the SQL query to insert a record into the "employee" table.

Example:

// SQL query to insert a record
const insertRecordQuery = 'INSERT INTO employee (name, email, department, age) VALUES (?,?,?,?)';
const values = ["Alice", "HR", "[email protected]", 22];

// Execute the SQL query
dbInstance.query(insertRecordQuery, values, (err, result) => {
  if (err) {
    console.error('Error inserting record:', err);
    return;
  }
  console.log('Record inserted successfully');
});

Replace the table name and column names with your specific table and column names. Also, replace the values in the array with the values you want to insert into the table.

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 { addEmployee } = 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 } = parsedUrl;

    // update if condition with 
    // if(req?.method === 'POST' && pathname === "/add-employee")
    if (pathname === "/add-employee") {
      addEmployee(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.

Example:

// SQL query to insert a record
const insertRecordQuery =
  "INSERT INTO employee (name, email, department, age) VALUES (?,?,?,?)";

async function addEmployee(req, res, dbInstance) {
  // Checking is establish

  if (dbInstance) {

    const name = req.body.name;
    const department = req.body.department;
    const email = req.body.email;
    const age = req.body.age;

    // Execute the SQL query
    // update below code: [name, department, email, age]
    const values = ["Alice", "HR", "[email protected]", 22];

    dbInstance.query(insertRecordQuery, values, (err, result) => {
      if (err) {
        console.error("Error inserting record:", err);
        res.end("Error inserting record:", JSON.stringify(err));
      }
      res.end("Employee Add Successfully.");
    });
  }
}

module.exports = { addEmployee };

<-- Drop Table

Select Record -->