How to Work with MySQL and Express.js Using MVC Architecture

Learn how to connect MySQL with Express.js using the MVC architecture. This article covers database setup, models, controllers, and routes to help beginners build clean and structured REST APIs with confidence.

How to Download mysql community server and mysql command in cmd

How to Download mysql community server : link

MYSQL Command: link

Install Package:

npm install express mysql2 dotenv

Folder structure:

config/db.js

const mysql = require("mysql2");

const db = mysql.createPool({
  host: process.env.HOST,
  user: process.env.USER,
  password: process.env.PASSWORD,
  database: process.env.DBNAME
});

module.exports = db;

models/userModel.js

const db = require("../config/db");

exports.getAllUsers = (callback) => {
  db.query("SELECT * FROM users", callback);
};

exports.createUser = (data, callback) => {
  db.query(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    [data.name, data.email],
    callback
  );
};

controllers/userController.js

const User = require("../models/userModel");

exports.getUsers = (req, res) => {
    User.getAllUsers((err, results) => {
        if (err) return res.status(500).json({ error: err });
        res.json({
            success:true,
            results
        });
    });
};

exports.addUser = (req, res) => {
    console.log(req.body)
    User.createUser(req.body, (err, result) => {
        if (err) return res.status(500).json({ error: err });
        res.json({ message: "User added successfully",result });
    });
};

routes/userRoutes.js

const express = require("express");
const router = express.Router();
const userController = require("../controllers/userController");

router.get("/", userController.getUsers);
router.post("/", userController.addUser);

module.exports = router;

server.js

const express = require('express')
const app = express()
require('dotenv').config()
const port = process.env.PORT || 3000

app.use(express.json());
app.use(express.urlencoded());
app.get('/', (req, res) => res.send('Hello World!'))


const userRoutes = require("./routes/userRoutes");
app.use("/api/users", userRoutes);
app.listen(port, () => console.log(`Example app listening on port http://localhost:${port}`))