Step 0: Create table
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
);
BashStep 1: Initialize your project
# Create a new directory for your project
mkdir express-mysql-crud
cd express-mysql-crud
# Initialize a new Node.js project
npm init -y
BashStep 2: Install dependencies
npm install express mysql2 body-parser cors dotenv
BashStep 3: Create project structure
touch index.js
mkdir controllers models routes
BashStep 4: Set up your .env
file
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_DATABASE=test
BashStep 5: Set up your database connection (db.js)
// 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;
BashStep 6: Create your model (studentModel.js)
// 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();
BashStep 7: Create your controller (studentController.js)
// 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();
BashStep 8: Create your routes (studentRoutes.js)
// 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;
BashStep 9: Set up your main server file (index.js)
// 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}`);
});
Bashnode index.js
BashRoutes
- GET: http://localhost:3000/students
- Method: GET
- URL: http://localhost:3000/students
- Headers: None
- Body: None
- Expected Response: List of all students
- POST: http://localhost:3000/students (with JSON body)
- Method: POST
- URL: http://localhost:3000/students
- Headers:
- Content-Type: application/json
- Body (raw JSON):
{
"name": "John Doe",
"age": 20,
"mobile": "123-456-7890"
}
BashGET: http://localhost:3000/students/1
- Method: GET
- URL: http://localhost:3000/students/1
- Headers: None
- Body: None
- Expected Response: Information about the student with ID 1
PUT: http://localhost:3000/students/1 (with JSON body)
- Method: PUT
- URL: http://localhost:3000/students/1
- Headers:
- Content-Type: application/json
- Body (raw JSON):
{
"name": "Updated Name",
"age": 25,
"mobile": "987-654-3210"
}
BashDELETE: http://localhost:3000/students/1
- Method: DELETE
- URL: http://localhost:3000/students/1
- Headers: None
- Body: None
- Expected Response: Message indicating successful deletion