DEV Community

Cover image for Building a Full-Stack CRUD Application with Express.js, MySQL, and Sequelize
FredAbod
FredAbod

Posted on

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

#webdev #programming #mysql #tutorial

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!

Dive In

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 2: Install Required Dependencies 

npm install express mysql2 sequelize dotenv npm install --save-dev nodemon
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Step 4: Set Up Sequelize

Create a directory for configuration files:

mkdir config
Enter fullscreen mode Exit fullscreen mode

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 };
Enter fullscreen mode Exit fullscreen mode

Step 5: Define Models

Create a directory for models:

mkdir models
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Step 6: Create Controllers

Create a directory for controllers:

mkdir controllers
Enter fullscreen mode Exit fullscreen mode

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 }); } };
Enter fullscreen mode Exit fullscreen mode

Step 7: Set Up Routes

Create a directory for routes:

mkdir routes
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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}`); });
Enter fullscreen mode Exit fullscreen mode

Step 9: Run the Application

Start the application using nodemon:

npx nodemon server.js
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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!

Happy Coding

Top comments (0)