DEV Community

Abhay Singh Kathayat
Abhay Singh Kathayat

Posted on

2

Building an Advanced CRUD API with Sequelize, PostgreSQL, and JWT Authentication in Node.js

To create an advanced CRUD API using Sequelize with Node.js and PostgreSQL (or any other SQL database), we'll follow a similar structure as before, but we'll replace MongoDB with PostgreSQL (or another SQL database) and Sequelize ORM for database interactions.

Here’s a detailed guide to building an advanced CRUD API using Sequelize and PostgreSQL with JWT authentication, input validation, and error handling.


1. Setup Your Node.js Project

If you haven't set up a Node.js project already, create it and initialize:

mkdir advanced-crud-api-sequelize
cd advanced-crud-api-sequelize
npm init -y
Enter fullscreen mode Exit fullscreen mode

2. Install Dependencies

Install the required packages:

npm install express sequelize pg pg-hstore bcryptjs jsonwebtoken dotenv body-parser joi
npm install --save-dev nodemon
Enter fullscreen mode Exit fullscreen mode
  • express: Web framework.
  • sequelize: ORM for interacting with SQL databases.
  • pg & pg-hstore: Packages for PostgreSQL support in Sequelize.
  • jsonwebtoken: For JWT authentication.
  • bcryptjs: For password hashing.
  • dotenv: For environment variables.
  • joi: For input validation.

3. Setup Sequelize and Database

3.1 Database Configuration

Create a config/config.js file to configure Sequelize:

module.exports = {
  development: {
    username: "postgres",
    password: "your_password",
    database: "advanced_crud",
    host: "127.0.0.1",
    dialect: "postgres",
  },
  production: {
    use_env_variable: "DATABASE_URL",
    dialect: "postgres",
  },
};
Enter fullscreen mode Exit fullscreen mode

Note: Replace your_password with your actual PostgreSQL password.

3.2 Initialize Sequelize

To initialize Sequelize in the project, run the following:

npx sequelize-cli init
Enter fullscreen mode Exit fullscreen mode

This will create several folders like models, migrations, and seeders.


4. Define the User Model

Inside the models folder, create a user.js model file:

const { Model, DataTypes } = require('sequelize');
const bcrypt = require('bcryptjs');

module.exports = (sequelize) => {
  class User extends Model {
    static associate(models) {
      // associations can be defined here
    }

    static async hashPassword(password) {
      const salt = await bcrypt.genSalt(10);
      return await bcrypt.hash(password, salt);
    }

    static async comparePassword(password, hashedPassword) {
      return await bcrypt.compare(password, hashedPassword);
    }
  }

  User.init(
    {
      name: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
        validate: {
          isEmail: true,
        },
      },
      password: {
        type: DataTypes.STRING,
        allowNull: false,
        validate: {
          len: [6, 100], // password should be at least 6 characters
        },
      },
    },
    {
      sequelize,
      modelName: 'User',
    }
  );

  return User;
};
Enter fullscreen mode Exit fullscreen mode

This file defines the User model with name, email, and password fields. The hashPassword and comparePassword methods are used for hashing and checking passwords.


5. User Controller

Create a controllers folder and inside it, create userController.js to handle CRUD operations:

const { User } = require('../models');
const jwt = require('jsonwebtoken');
const Joi = require('joi');
const dotenv = require('dotenv');

dotenv.config();

// Validation schema using Joi
const userValidationSchema = Joi.object({
  name: Joi.string().min(3).required(),
  email: Joi.string().email().required(),
  password: Joi.string().min(6).required(),
});

// Register user
exports.registerUser = async (req, res) => {
  try {
    const { name, email, password } = req.body;

    // Validate input data
    const { error } = userValidationSchema.validate(req.body);
    if (error) return res.status(400).json({ error: error.details[0].message });

    // Check if the user already exists
    const userExists = await User.findOne({ where: { email } });
    if (userExists) {
      return res.status(400).json({ message: 'User already exists' });
    }

    const hashedPassword = await User.hashPassword(password);

    const newUser = await User.create({
      name,
      email,
      password: hashedPassword,
    });

    const token = jwt.sign({ id: newUser.id }, process.env.JWT_SECRET, { expiresIn: '30d' });

    res.status(201).json({ user: newUser, token });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: 'Server error' });
  }
};

// Login user
exports.loginUser = async (req, res) => {
  try {
    const { email, password } = req.body;

    // Validate input data
    const { error } = userValidationSchema.validate(req.body);
    if (error) return res.status(400).json({ error: error.details[0].message });

    const user = await User.findOne({ where: { email } });
    if (!user) return res.status(400).json({ message: 'Invalid email or password' });

    const isMatch = await User.comparePassword(password, user.password);
    if (!isMatch) return res.status(400).json({ message: 'Invalid email or password' });

    const token = jwt.sign({ id: user.id }, process.env.JWT_SECRET, { expiresIn: '30d' });

    res.json({ user, token });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: 'Server error' });
  }
};

// Get user details
exports.getUserDetails = async (req, res) => {
  try {
    const user = await User.findByPk(req.user.id);
    if (!user) return res.status(404).json({ message: 'User not found' });

    res.json(user);
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: 'Server error' });
  }
};

// Update user
exports.updateUser = async (req, res) => {
  try {
    const { name, email } = req.body;

    const user = await User.findByPk(req.user.id);
    if (!user) return res.status(404).json({ message: 'User not found' });

    user.name = name || user.name;
    user.email = email || user.email;

    await user.save();

    res.json(user);
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: 'Server error' });
  }
};

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

    await user.destroy();
    res.status(204).send();
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: 'Server error' });
  }
};
Enter fullscreen mode Exit fullscreen mode

This file includes methods to:

  • Register a new user with password hashing.
  • Login a user by checking the hashed password.
  • Retrieve, update, and delete user information.

6. Middleware for JWT Authentication

Create a middleware middleware/auth.js to protect routes that require authentication:

const jwt = require('jsonwebtoken');
const dotenv = require('dotenv');

dotenv.config();

const protect = (req, res, next) => {
  const token = req.header('Authorization')?.replace('Bearer ', '');

  if (!token) {
    return res.status(401).json({ message: 'No token, authorization denied' });
  }

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    req.user = decoded; // Attach user info to the request
    next();
  } catch (err) {
    res.status(401).json({ message: 'Token is not valid' });
  }
};

module.exports = protect;
Enter fullscreen mode Exit fullscreen mode

7. Define Routes

Create a routes/userRoutes.js file for the routes:

const express = require('express');
const { registerUser, loginUser, getUserDetails, updateUser, deleteUser } = require('../controllers/userController');
const protect = require('../middleware/auth');

const router = express.Router();

router.post('/register', registerUser);
router.post('/login', loginUser);
router.get('/me', protect, getUserDetails);
router.put('/me', protect, updateUser);
router.delete('/me', protect, deleteUser);

module.exports = router;
Enter fullscreen mode Exit fullscreen mode

8. Set Up the Main Server

In index.js, set up the main server file:

const express = require('express');
const dotenv = require('dotenv');
const { sequelize } = require('./models');
const userRoutes = require('./routes/userRoutes');
const bodyParser = require('body-parser');

dotenv.config();

const app = express();
app.use(bodyParser.json());

// Sync Sequelize models with the database
sequelize.sync().then(() => {
  console.log('Database connected');
});

// Use user routes
app.use('/api/users', userRoutes

);

const PORT = process.env.PORT || 5000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});
Enter fullscreen mode Exit fullscreen mode

9. Running the Application

To run the application, start the server:

npm run dev
Enter fullscreen mode Exit fullscreen mode

Conclusion

This is a comprehensive setup for building an advanced CRUD API using Sequelize and PostgreSQL with:

  • JWT Authentication for securing routes.
  • Sequelize ORM for interacting with SQL databases.
  • Input validation using Joi.
  • Password hashing using bcryptjs.

This setup provides a solid foundation for building more complex APIs with user management and security.

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay