Building a Full-Stack CRUD Application with Express.js, MySQL, and Sequelize

Building a Full-Stack CRUD Application with Express.js, MySQL, and Sequelize

Introduction to MySQL and Sequelize ORM: A Complete Guide for Node.js Developers

In today's data-driven world, building robust database applications is essential for developers. MySQL remains one of the most popular relational database systems, and Sequelize provides an elegant Node.js ORM to interact with it. In this comprehensive tutorial, we'll build a complete CRUD (Create, Read, Update, Delete) application using Express.js, MySQL, and Sequelize from scratch.

Prerequisites

• Node.js and npm installed on your system
• MySQL installed and running
• Basic understanding of JavaScript and Express.js
• A cup of coffee (coding is always better with caffeine)

Let's dive into building our application!

Setting Up MySQL for Our Project

Before we start coding, you'll need MySQL installed and running on your system.

Step 1: Install MySQL

If you haven't installed MySQL yet, download it from the official website and follow the installation instructions for your operating system.

Step 2: Create a Database

Open your MySQL terminal or MySQL Workbench and run:

CREATE DATABASE crud_db;

That's it! Sequelize will handle creating our tables automatically.

Building Our Application Step by Step

Step 1: Initialize Your Node.js Project

Create a new directory and initialize a Node.js project:

mkdir sequelize-crud
cd sequelize-crud
npm init -y

Step 2: Install Required Dependencies

npm install express mysql2 sequelize dotenv
npm install --save-dev nodemon

Step 3: Create Environment Variables

Create a .env file to store your database credentials:

DB_NAME=crud_db
DB_USER=root
DB_PASSWORD=1234Asdf?
DB_HOST=localhost
DB_DIALECT=mysql
PORT=3500

Step 4: Set Up Sequelize

Create a directory for configuration files:

mkdir config

Now let's create the Sequelize configuration file. This file will handle connecting to the MySQL database and setting up the Sequelize instance.

Create config/database.js:

const { Sequelize } = require('sequelize');
require('dotenv').config();

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

const connectDB = async () => {
  try {
    await sequelize.authenticate();
    console.log('Database connection has been established successfully.');
    await sequelize.sync({ alter: true });
    console.log('All models were synchronized successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
    process.exit(1);
  }
};

module.exports = { sequelize, connectDB };

Step 5: Define Models

Create a directory for models:

mkdir models

Create models/User.js:

const { DataTypes } = require('sequelize');
const { sequelize } = require('../config/database');

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true,
    },
  },
  age: {
    type: DataTypes.INTEGER,
    allowNull: true,
  }
}, {
  tableName: 'users',
  timestamps: true,
});

module.exports = User;

Step 6: Create Controllers

Create a directory for controllers:

mkdir controllers

Create controllers/userController.js:

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

// Create a new user
exports.createUser = async (req, res) => {
  try {
    const { name, email, age } = req.body;

    const user = await User.create({
      name,
      email,
      age
    });

    res.status(201).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(400).json({
      success: false,
      error: error.message
    });
  }
};

// Get all users
exports.getAllUsers = async (req, res) => {
  try {
    const users = await User.findAll();

    res.status(200).json({
      success: true,
      count: users.length,
      data: users
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Get a single user by ID
exports.getUserById = async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'User not found'
      });
    }

    res.status(200).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Update a user
exports.updateUser = async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'User not found'
      });
    }

    const { name, email, age } = req.body;

    await user.update({
      name: name || user.name,
      email: email || user.email,
      age: age || user.age
    });

    res.status(200).json({
      success: true,
      data: user
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

// Delete a user
exports.deleteUser = async (req, res) => {
  try {
    const user = await User.findByPk(req.params.id);

    if (!user) {
      return res.status(404).json({
        success: false,
        error: 'User not found'
      });
    }

    await user.destroy();

    res.status(200).json({
      success: true,
      data: {}
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
};

Step 7: Set Up Routes

Create a directory for routes:

mkdir routes

Create routes/userRoutes.js:

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

// Create a new user
router.post('/', userController.createUser);

// Get all users
router.get('/', userController.getAllUsers);

// Get a single user
router.get('/:id', userController.getUserById);

// Update a user
router.put('/:id', userController.updateUser);

// Delete a user
router.delete('/:id', userController.deleteUser);

module.exports = router;

Step 8: Create the Express Server

Create server.js:

const express = require('express');
const { connectDB } = require('./config/database');
const userRoutes = require('./routes/userRoutes');
require('dotenv').config();

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

// Connect to database
connectDB();

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// Routes
app.use('/api/users', userRoutes);

// Home route
app.get('/', (req, res) => {
  res.send('Welcome to User CRUD API with Express, MySQL, and Sequelize');
});

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

Step 9: Run the Application

Start the application using nodemon:

npx nodemon server.js

Your CRUD application is now up and running! You can use tools like Postman to test the API endpoints.

Folder Structure

Here's the final folder structure of our project:

sequelize-crud/
├── config/
│   └── database.js
├── controllers/
│   └── userController.js
├── models/
│   └── User.js
├── routes/
│   └── userRoutes.js
├── .env
├── package.json
├── server.js

Conclusion

In this tutorial, we built a full-stack CRUD application using Express.js, MySQL, and Sequelize. We covered setting up the project, defining models, creating controllers, and setting up routes. With this foundation, you can expand the application further by adding authentication, validation, and more features.
Don't forget to drop a like ❤️😉❤️
Happy coding!