Node Express Crud App w MySql

To create an Express CRUD REST API with a full MVC system using a MySQL database, we’ll need to adjust our approach to include the appropriate MySQL packages and configurations. Here’s a comprehensive guide to set it up, including an explanation of the plugins used and the overall app structure.

Plugins and Packages Used

Express: A web application framework for Node.js, designed for building web applications and APIs.
MySQL2: A MySQL client for Node.js with a focus on performance.
Sequelize: A promise-based Node.js ORM for MySQL, which makes it easy to manage and interact with the database.
Nodemon: A utility that monitors for any changes in your source and automatically restarts your server.
Dotenv: A module to load environment variables from a .env file into process.env.
Body-Parser: Middleware to parse incoming request bodies in a middleware before your handlers, available under the req.body property.
Cors: Middleware to enable Cross-Origin Resource Sharing.

App Structure

project
│   .env
│   .gitignore
│   package.json
│   server.js
│
└───config
│   │   db.js
│
└───controllers
│   │   userController.js
│
└───models
│   │   userModel.js
│
└───routes
│   │   userRoutes.js
│
└───middleware
    │   errorMiddleware.js

Step-by-Step Implementation

Step 1: Initialize the Project

mkdir project
cd project
npm init -y
npm install express mysql2 sequelize nodemon dotenv body-parser cors

Step 2: Setup `.env` File

Create a .env file in the root of your project:

PORT=3000
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=your_database

Step 3: Create `server.js`

Create server.js in the root directory:

const express = require('express');         // Import express
const bodyParser = require('body-parser');  // Import body-parser for parsing request bodies
const cors = require('cors');               // Import cors for handling Cross-Origin Resource Sharing
const dotenv = require('dotenv');           // Import dotenv for environment variables
const userRoutes = require('./routes/userRoutes'); // Import user routes
const errorMiddleware = require('./middleware/errorMiddleware'); // Import error middleware
const { sequelize } = require('./config/db'); // Import sequelize instance

dotenv.config();                            // Load environment variables

const app = express();                      // Initialize express app

// Middleware setup
app.use(bodyParser.json());                 // Parse JSON bodies
app.use(cors());                            // Enable CORS
app.use('/api/users', userRoutes);          // Use user routes
app.use(errorMiddleware);                   // Use error middleware

const PORT = process.env.PORT || 5000;      // Define the port

sequelize.sync().then(() => {               // Sync database
    app.listen(PORT, () => console.log(`Server running on port ${PORT}`)); // Start the server
}).catch(err => console.error('Unable to connect to the database:', err));  // Handle connection errors

Step 4: Setup Database Configuration

Create config/db.js:

const { Sequelize } = require('sequelize'); // Import Sequelize

const sequelize = new Sequelize(
    process.env.DB_NAME, 
    process.env.DB_USER, 
    process.env.DB_PASSWORD, 
    {
        host: process.env.DB_HOST,
        dialect: 'mysql',
        logging: false,
    }
);

module.exports = { sequelize };             // Export sequelize instance

Step 5: Create Sequelize Model

Create models/userModel.js:

const { DataTypes } = require('sequelize'); // Import DataTypes
const { sequelize } = require('../config/db'); // Import sequelize instance

const User = sequelize.define('User', {     // Define user model
    name: {
        type: DataTypes.STRING,
        allowNull: false,
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false,
    },
}, {
    timestamps: true,                       // Add createdAt and updatedAt fields
});

module.exports = User;                      // Export user model

Step 6: Create Controllers

Create controllers/userController.js:

const User = require('../models/userModel'); // Import user model

// Get all users
const getUsers = async (req, res) => {
    try {
        const users = await User.findAll();  // Find all users
        res.json(users);                     // Return users in JSON format
    } catch (err) {
        res.status(500).json({ message: err.message }); // Handle errors
    }
};

// Get a single user
const getUser = async (req, res) => {
    try {
        const user = await User.findByPk(req.params.id); // Find user by ID
        if (!user) return res.status(404).json({ message: 'User not found' }); // Handle user not found
        res.json(user);                     // Return user in JSON format
    } catch (err) {
        res.status(500).json({ message: err.message }); // Handle errors
    }
};

// Create a new user
const createUser = async (req, res) => {
    try {
        const newUser = await User.create(req.body); // Create new user
        res.status(201).json(newUser);      // Return new user in JSON format
    } catch (err) {
        res.status(400).json({ message: err.message }); // Handle errors
    }
};

// Update a user
const updateUser = async (req, res) => {
    try {
        const user = await User.findByPk(req.params.id); // Find user by ID
        if (!user) return res.status(404).json({ message: 'User not found' }); // Handle user not found

        await user.update(req.body);        // Update user
        res.json(user);                     // Return updated user in JSON format
    } catch (err) {
        res.status(400).json({ message: err.message }); // Handle errors
    }
};

// Delete a user
const deleteUser = async (req, res) => {
    try {
        const user = await User.findByPk(req.params.id); // Find user by ID
        if (!user) return res.status(404).json({ message: 'User not found' }); // Handle user not found

        await user.destroy();               // Delete user
        res.json({ message: 'User removed' }); // Return success message
    } catch (err) {
        res.status(500).json({ message: err.message }); // Handle errors
    }
};

module.exports = {
    getUsers,
    getUser,
    createUser,
    updateUser,
    deleteUser,
};                                           // Export all controller functions

Step 7: Create Routes

Create routes/userRoutes.js:

const express = require('express');         // Import express
const {
    getUsers,
    getUser,
    createUser,
    updateUser,
    deleteUser,
} = require('../controllers/userController'); // Import controller functions

const router = express.Router();            // Create router

router.route('/')
    .get(getUsers)                          // Route to get all users
    .post(createUser);                      // Route to create a user

router.route('/:id')
    .get(getUser)                           // Route to get a user by ID
    .put(updateUser)                        // Route to update a user by ID
    .delete(deleteUser);                    // Route to delete a user by ID

module.exports = router;                    // Export router

Step 8: Create Error Middleware

Create middleware/errorMiddleware.js:

const errorMiddleware = (err, req, res, next) => {
    const statusCode = res.statusCode !== 200 ? res.statusCode : 500; // Determine status code
    res.status(statusCode);
    res.json({
        message: err.message,
        stack: process.env.NODE_ENV === 'production' ? null : err.stack, // Show stack trace only in development
    });
};

module.exports = errorMiddleware;           // Export error middleware

Step 9: Run the Server

Add a start script in package.json:

"scripts": {
    "start": "node server.js",             // Script to start the server
    "dev": "nodemon server.js"             // Script to start the server with nodemon
}

Run the server:

npm run dev                                # Start the server in development mode

Postman Collection Setup

Create Collection: In Postman, create a new collection named “User API”.
Create Requests:
- GET /api/users:
  - Method: GET
  - URL: http://localhost:3000/api/users
- POST /api/users:
  - Method: POST
  - URL: http://localhost:3000/api/users
  - Body: raw JSON

{
    "name": "John Doe",
    "email": "john@example.com",
    "password": "password123"
}

GET /api/users/:id:
- Method: GET
- URL: http://localhost:3000/api/users/{{id}}
- (Replace {{id}} with a valid user ID)
PUT /api/users/:id:
- Method: PUT
- URL: http://localhost:3000/api/users/{{id}}
- Body: raw JSON

{
    "name": "Jane Doe",
    "email": "jane@example.com",
    "password": "newpassword123"
}

- DELETE /api/users/:id:
  - Method: DELETE
  - URL: http://localhost:3000/api/users/{{id}}
  - (Replace {{id}} with a valid user ID)

Diagram of the App Structure

project
│
├── .env               # Environment variables
├── .gitignore         # Git ignore file
├── package.json       # Project metadata and dependencies
├── server.js          # Entry point of the application
│
├── config             # Configuration files
│   └── db.js          # Database configuration
│
├── controllers        # Controller files
│   └── userController.js
│
├── models             # Sequelize models
│   └── userModel.js
│
├── routes             # Route handlers
│   └── userRoutes.js
│
└── middleware         # Custom middleware
    └── errorMiddleware.js

This guide provides a detailed setup for creating a fully functional Express CRUD REST API with a clear MVC structure and a MySQL database. You also have the setup for Postman to test your API endpoints efficiently.

React To-Do App with JSON Server

Day 14 Trading Strategies Bootcamp: Crypto Trading Tools

Day 13 Trading Strategies Bootcamp: Crypto Risk management

React To-Do App with JSON Server

Day 14 Trading Strategies Bootcamp: Crypto Trading Tools

What's Hot

Learn HTML

How to install and create react app

Beauty Salon Booking App – Node, MySql

+385 99 8302 482

Node Express Crud App w MySql

Plugins and Packages Used

App Structure

Step-by-Step Implementation

Step 1: Initialize the Project

Step 2: Setup `.env` File

Step 3: Create `server.js`

Step 4: Setup Database Configuration

Step 5: Create Sequelize Model

Step 6: Create Controllers

Step 7: Create Routes

Step 8: Create Error Middleware

Step 9: Run the Server

Postman Collection Setup

Diagram of the App Structure

Other Articles

Node Express Crud App w MongoDB

React CRUD with JSON server and local storage

Share Article

Popular posts

How to install and create react app

Beauty Salon Booking App – Node, MySql

Pages

Latest posts

Day 14 Trading Strategies Bootcamp: Crypto Trading Tools

Day 13 Trading Strategies Bootcamp: Crypto Risk management

Subscribe Now

What's Hot

+385 99 8302 482

What are you looking for?

Node Express Crud App w MySql

Plugins and Packages Used

App Structure

Step-by-Step Implementation

Step 1: Initialize the Project

Step 2: Setup .env File

Step 3: Create server.js

Step 4: Setup Database Configuration

Step 5: Create Sequelize Model

Step 6: Create Controllers

Step 7: Create Routes

Step 8: Create Error Middleware

Step 9: Run the Server

Postman Collection Setup

Diagram of the App Structure

Other Articles

Share Article

Popular posts

Pages

Latest posts

Subscribe Now

Step 2: Setup `.env` File

Step 3: Create `server.js`