simple crud

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  age INT,
  mobile VARCHAR(15),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

# Create a new directory for your project
mkdir express-mysql-crud
cd express-mysql-crud

# Initialize a new Node.js project
npm init -y

npm install express mysql2 body-parser cors dotenv

touch index.js
mkdir controllers models routes

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_DATABASE=test

// config/db.js
const mysql = require("mysql2");
const dotenv = require("dotenv");

dotenv.config();

const connection = mysql.createConnection({
  port: 3306,
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
});

connection.connect((err) => {
  if (err) {
    console.error("Error connecting to MySQL:", err);
  } else {
    console.log("Connected to MySQL database");
  }
});

module.exports = connection;

// studentModel.js
const db = require('../config/connection');

class Student {
  createStudent(student) {
    return new Promise((resolve, reject) => {
      db.query('INSERT INTO students SET ?', [student], (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  getAllStudents() {
    return new Promise((resolve, reject) => {
      db.query('SELECT * FROM students', (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  getStudentById(id) {
    return new Promise((resolve, reject) => {
      db.query('SELECT * FROM students WHERE id = ?', [id], (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result[0]);
        }
      });
    });
  }

  updateStudent(id, student) {
    return new Promise((resolve, reject) => {
      db.query('UPDATE students SET ? WHERE id = ?', [student, id], (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  deleteStudent(id) {
    return new Promise((resolve, reject) => {
      db.query('DELETE FROM students WHERE id = ?', [id], (err, result) => {
        if (err) {
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }
}

module.exports = new Student();

// studentController.js
const Student = require('../models/studentModel');

class StudentController {
  async createStudent(req, res) {
    try {
      const { name, age, mobile } = req.body;
      const newStudent = { name, age, mobile };
      const result = await Student.createStudent(newStudent);
      res.status(201).json({ message: 'Student created successfully', id: result.insertId });
    } catch (error) {
      res.status(500).json({ error: error.message });
    }
  }

  async getAllStudents(req, res) {
    try {
      const students = await Student.getAllStudents();
      res.status(200).json(students);
    } catch (error) {
      res.status(500).json({ error: error.message });
    }
  }

  async getStudentById(req, res) {
    try {
      const { id } = req.params;
      const student = await Student.getStudentById(id);
      res.status(200).json(student);
    } catch (error) {
      res.status(500).json({ error: error.message });
    }
  }

  async updateStudent(req, res) {
    try {
      const { id } = req.params;
      const { name, age, mobile } = req.body;
      const updatedStudent = { name, age, mobile };
      await Student.updateStudent(id, updatedStudent);
      res.status(200).json({ message: 'Student updated successfully' });
    } catch (error) {
      res.status(500).json({ error: error.message });
    }
  }

  async deleteStudent(req, res) {
    try {
      const { id } = req.params;
      await Student.deleteStudent(id);
      res.status(200).json({ message: 'Student deleted successfully' });
    } catch (error) {
      res.status(500).json({ error: error.message });
    }
  }
}

module.exports = new StudentController();

// studentRoutes.js
const express = require('express');
const router = express.Router();
const StudentController = require('../controllers/studentController');

router.post('/', StudentController.createStudent);
router.get('/', StudentController.getAllStudents);
router.get('/:id', StudentController.getStudentById);
router.put('/:id', StudentController.updateStudent);
router.delete('/:id', StudentController.deleteStudent);

module.exports = router;

// src/index.js
const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");
const dotenv = require("dotenv");
const studentRoutes = require("./routes/studentRoutes");

dotenv.config();

const app = express();
const port = process.env.PORT || 3000;

// Middleware
app.use(bodyParser.json());
app.use(cors());

// Routes
app.use("/students", studentRoutes);

// Start the server
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

node index.js

{
  "name": "John Doe",
  "age": 20,
  "mobile": "123-456-7890"
}

{
  "name": "Updated Name",
  "age": 25,
  "mobile": "987-654-3210"
}