Building a CRUD API using MVC Architecture in a NodeJS, Express, and MySQL Database Project

In this tutorial, we will guide you through the process of creating a CRUD API with MVC architecture using NodeJS, Express, and MySQL database. This project will allow you to perform Create, Read, Update, and Delete operations on a database using an API.

Prerequisites:

1. Basic knowledge of NodeJS, Express, and MySQL
2. NodeJS and npm installed on your system
3. MySQL server installed on your system

Step 1: Setup the project

1. Create a new directory for your project and navigate to it in the terminal
2. Run npm init -y to initialize a new NodeJS project
3. Install Express, MySQL, and body-parser npm packages by running the following command:

   npm install express mysql body-parser

Step 2: Create the database

1. Open MySQL Workbench or any other MySQL client
2. Create a new database by running the following SQL command:

   CREATE DATABASE crud_api;

Step 3: Create the model

1. Create a new directory called models in your project directory
2. Create a file called user.js inside the models directory
3. Write the following code in user.js:

   
   const mysql = require('mysql');

const connection = mysql.createConnection({
host: ‘localhost’,
user: ‘root’,
password: ‘password’,
database: ‘crud_api’
});

connection.connect();

module.exports = connection;

Step 4: Create the controller
1. Create a new directory called controllers in your project directory
2. Create a file called userController.js inside the controllers directory
3. Write the following code in userController.js:
``` javascript
const db = require('../models/user');

exports.getAllUsers = (req, res) => {
  db.query('SELECT * FROM users', (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).json({ error: 'Internal server error' });
    } else {
      res.json(results);
    }
  });
};

exports.createUser = (req, res) => {
  const { name, email } = req.body;
  db.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email], (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).json({ error: 'Internal server error' });
    } else {
      res.json({ message: 'User created successfully' });
    }
  });
};

exports.updateUser = (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  db.query('UPDATE users SET name = ?, email = ? WHERE id = ?', [name, email, id], (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).json({ error: 'Internal server error' });
    } else {
      res.json({ message: 'User updated successfully' });
    }
  });
};

exports.deleteUser = (req, res) => {
  const { id } = req.params;
  db.query('DELETE FROM users WHERE id = ?', [id], (err, results) => {
    if (err) {
      console.log(err);
      res.status(500).json({ error: 'Internal server error' });
    } else {
      res.json({ message: 'User deleted successfully' });
    }
  });
};

Step 5: Create the routes

1. Create a new directory called routes in your project directory
2. Create a file called userRoutes.js inside the routes directory
3. Write the following code in userRoutes.js:

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

router.get(‘/’, userController.getAllUsers);
router.post(‘/’, userController.createUser);
router.put(‘/:id’, userController.updateUser);
router.delete(‘/:id’, userController.deleteUser);

module.exports = router;

Step 6: Setup the server
1. Create a file called server.js in your project directory
2. Write the following code in server.js:
``` javascript
const express = require('express');
const bodyParser = require('body-parser');
const userRoutes = require('./routes/userRoutes');

const app = express();
app.use(bodyParser.json());
app.use('/users', userRoutes);

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Step 7: Start the server

1. Run the following command in the terminal to start the server:

   node server.js

Congratulations! You have successfully created a CRUD API with MVC architecture using NodeJS, Express, and MySQL database. You can now use Postman or any API testing tool to test the API endpoints for Create, Read, Update, and Delete operations on the database.