>>
Sequelize with PostgreSQL in Node.js: Part 3 – CRUD Operations and Services
Backend Development Database node.js

Sequelize with PostgreSQL in Node.js: Part 3 – CRUD Operations and Services

With your models and migrations in place, it’s time to implement the actual CRUD (Create, Read, Update, Delete) operations. This part focuses on building a clean architecture with controllers and services that handle database operations efficiently. We’ll also explore eager loading to optimize queries and retrieve related data.

Architecture Overview

A clean architecture separates concerns into distinct layers. Requests flow through routes to controllers, which delegate business logic to services. Services interact with models to perform database operations. This separation makes your code maintainable, testable, and scalable.

Creating Service Layer

Create src/services/userService.js to handle all user-related database operations:

const { User, Post } = require('../models');

class UserService {
  async createUser(userData) {
    try {
      const user = await User.create({
        firstName: userData.firstName,
        lastName: userData.lastName,
        email: userData.email,
        password: userData.password,
      });
      return user;
    } catch (error) {
      throw new Error(`Failed to create user: ${error.message}`);
    }
  }

  async getUserById(userId) {
    try {
      const user = await User.findByPk(userId, {
        include: {
          model: Post,
          as: 'posts',
          attributes: ['id', 'title', 'content', 'createdAt'],
        },
      });
      
      if (!user) {
        throw new Error('User not found');
      }
      return user;
    } catch (error) {
      throw new Error(`Failed to fetch user: ${error.message}`);
    }
  }

  async getAllUsers(limit = 10, offset = 0) {
    try {
      const users = await User.findAll({
        attributes: { exclude: ['password'] },
        include: {
          model: Post,
          as: 'posts',
          attributes: ['id', 'title'],
        },
        limit,
        offset,
        order: [['createdAt', 'DESC']],
      });
      return users;
    } catch (error) {
      throw new Error(`Failed to fetch users: ${error.message}`);
    }
  }

  async updateUser(userId, updateData) {
    try {
      const user = await User.findByPk(userId);
      if (!user) {
        throw new Error('User not found');
      }
      
      await user.update(updateData);
      return user;
    } catch (error) {
      throw new Error(`Failed to update user: ${error.message}`);
    }
  }

  async deleteUser(userId) {
    try {
      const user = await User.findByPk(userId);
      if (!user) {
        throw new Error('User not found');
      }
      
      await user.destroy();
      return { message: 'User deleted successfully' };
    } catch (error) {
      throw new Error(`Failed to delete user: ${error.message}`);
    }
  }
}

module.exports = new UserService();

Now create src/services/postService.js for post operations:

const { Post, User } = require('../models');

class PostService {
  async createPost(postData) {
    try {
      const post = await Post.create({
        title: postData.title,
        content: postData.content,
        userId: postData.userId,
      });
      return post;
    } catch (error) {
      throw new Error(`Failed to create post: ${error.message}`);
    }
  }

  async getPostById(postId) {
    try {
      const post = await Post.findByPk(postId, {
        include: {
          model: User,
          as: 'author',
          attributes: ['id', 'firstName', 'lastName', 'email'],
        },
      });
      
      if (!post) {
        throw new Error('Post not found');
      }
      return post;
    } catch (error) {
      throw new Error(`Failed to fetch post: ${error.message}`);
    }
  }

  async getAllPosts(limit = 10, offset = 0) {
    try {
      const posts = await Post.findAll({
        include: {
          model: User,
          as: 'author',
          attributes: ['id', 'firstName', 'lastName'],
        },
        limit,
        offset,
        order: [['createdAt', 'DESC']],
      });
      return posts;
    } catch (error) {
      throw new Error(`Failed to fetch posts: ${error.message}`);
    }
  }

  async getPostsByUserId(userId, limit = 10, offset = 0) {
    try {
      const posts = await Post.findAll({
        where: { userId },
        limit,
        offset,
        order: [['createdAt', 'DESC']],
      });
      return posts;
    } catch (error) {
      throw new Error(`Failed to fetch user posts: ${error.message}`);
    }
  }

  async updatePost(postId, updateData) {
    try {
      const post = await Post.findByPk(postId);
      if (!post) {
        throw new Error('Post not found');
      }
      
      await post.update(updateData);
      return post;
    } catch (error) {
      throw new Error(`Failed to update post: ${error.message}`);
    }
  }

  async deletePost(postId) {
    try {
      const post = await Post.findByPk(postId);
      if (!post) {
        throw new Error('Post not found');
      }
      
      await post.destroy();
      return { message: 'Post deleted successfully' };
    } catch (error) {
      throw new Error(`Failed to delete post: ${error.message}`);
    }
  }
}

module.exports = new PostService();

Creating Controller Layer

Create src/controllers/userController.js to handle HTTP requests:

const userService = require('../services/userService');

class UserController {
  async create(req, res) {
    try {
      const user = await userService.createUser(req.body);
      res.status(201).json({
        success: true,
        message: 'User created successfully',
        data: user,
      });
    } catch (error) {
      res.status(400).json({
        success: false,
        message: error.message,
      });
    }
  }

  async getById(req, res) {
    try {
      const user = await userService.getUserById(req.params.id);
      res.status(200).json({
        success: true,
        data: user,
      });
    } catch (error) {
      res.status(404).json({
        success: false,
        message: error.message,
      });
    }
  }

  async getAll(req, res) {
    try {
      const limit = req.query.limit || 10;
      const offset = req.query.offset || 0;
      
      const users = await userService.getAllUsers(limit, offset);
      res.status(200).json({
        success: true,
        data: users,
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        message: error.message,
      });
    }
  }

  async update(req, res) {
    try {
      const user = await userService.updateUser(req.params.id, req.body);
      res.status(200).json({
        success: true,
        message: 'User updated successfully',
        data: user,
      });
    } catch (error) {
      res.status(400).json({
        success: false,
        message: error.message,
      });
    }
  }

  async delete(req, res) {
    try {
      const result = await userService.deleteUser(req.params.id);
      res.status(200).json({
        success: true,
        message: result.message,
      });
    } catch (error) {
      res.status(400).json({
        success: false,
        message: error.message,
      });
    }
  }
}

module.exports = new UserController();

Create src/controllers/postController.js:

const postService = require('../services/postService');

class PostController {
  async create(req, res) {
    try {
      const post = await postService.createPost(req.body);
      res.status(201).json({
        success: true,
        message: 'Post created successfully',
        data: post,
      });
    } catch (error) {
      res.status(400).json({
        success: false,
        message: error.message,
      });
    }
  }

  async getById(req, res) {
    try {
      const post = await postService.getPostById(req.params.id);
      res.status(200).json({
        success: true,
        data: post,
      });
    } catch (error) {
      res.status(404).json({
        success: false,
        message: error.message,
      });
    }
  }

  async getAll(req, res) {
    try {
      const limit = req.query.limit || 10;
      const offset = req.query.offset || 0;
      
      const posts = await postService.getAllPosts(limit, offset);
      res.status(200).json({
        success: true,
        data: posts,
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        message: error.message,
      });
    }
  }

  async getByUserId(req, res) {
    try {
      const limit = req.query.limit || 10;
      const offset = req.query.offset || 0;
      
      const posts = await postService.getPostsByUserId(
        req.params.userId,
        limit,
        offset
      );
      res.status(200).json({
        success: true,
        data: posts,
      });
    } catch (error) {
      res.status(500).json({
        success: false,
        message: error.message,
      });
    }
  }

  async update(req, res) {
    try {
      const post = await postService.updatePost(req.params.id, req.body);
      res.status(200).json({
        success: true,
        message: 'Post updated successfully',
        data: post,
      });
    } catch (error) {
      res.status(400).json({
        success: false,
        message: error.message,
      });
    }
  }

  async delete(req, res) {
    try {
      const result = await postService.deletePost(req.params.id);
      res.status(200).json({
        success: true,
        message: result.message,
      });
    } catch (error) {
      res.status(400).json({
        success: false,
        message: error.message,
      });
    }
  }
}

module.exports = new PostController();

Creating Routes

Create src/routes/userRoutes.js:

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

const router = express.Router();

router.post('/', userController.create);
router.get('/', userController.getAll);
router.get('/:id', userController.getById);
router.put('/:id', userController.update);
router.delete('/:id', userController.delete);

module.exports = router;

Create src/routes/postRoutes.js:

const express = require('express');
const postController = require('../controllers/postController');

const router = express.Router();

router.post('/', postController.create);
router.get('/', postController.getAll);
router.get('/:id', postController.getById);
router.get('/user/:userId', postController.getByUserId);
router.put('/:id', postController.update);
router.delete('/:id', postController.delete);

module.exports = router;

Updating Main Application File

Update src/index.js to include the routes:

require('dotenv').config();
const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const sequelize = require('./config/sequelize');
const userRoutes = require('./routes/userRoutes');
const postRoutes = require('./routes/postRoutes');

const app = express();
const PORT = process.env.PORT || 3000;

app.use(helmet());
app.use(cors());
app.use(express.json());

app.get('/health', (req, res) => {
  res.json({ status: 'Server is running' });
});

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

const startServer = async () => {
  try {
    await sequelize.authenticate();
    await sequelize.sync({ alter: false });
    
    app.listen(PORT, () => {
      console.log(`Server running on port ${PORT}`);
    });
  } catch (error) {
    console.error('Failed to start server:', error);
    process.exit(1);
  }
};

startServer();

Understanding Eager Loading

Notice the ‘include’ option in our service methods. This is eager loading, which retrieves associated data in a single query instead of multiple queries. Without eager loading, fetching a user would require a separate query for each user’s posts, causing the N+1 problem.

// With eager loading (efficient)
const user = await User.findByPk(userId, {
  include: { model: Post, as: 'posts' }
});

// Without eager loading (inefficient - N+1 problem)
const user = await User.findByPk(userId);
const posts = await Post.findAll({ where: { userId } });

API Endpoints Reference

User Endpoints:

  • POST /api/users – Create new user
  • GET /api/users – Get all users with pagination
  • GET /api/users/:id – Get user by ID with posts
  • PUT /api/users/:id – Update user
  • DELETE /api/users/:id – Delete user

Post Endpoints:

  • POST /api/posts – Create new post
  • GET /api/posts – Get all posts with pagination
  • GET /api/posts/:id – Get post by ID with author
  • GET /api/posts/user/:userId – Get posts by user
  • PUT /api/posts/:id – Update post
  • DELETE /api/posts/:id – Delete post

Testing CRUD Operations

Test creating a user with curl:

curl -X POST http://localhost:3000/api/users \
  -H "Content-Type: application/json" \
  -d '{
    "firstName": "John",
    "lastName": "Doe",
    "email": "john@example.com",
    "password": "securepassword123"
  }'

Test creating a post:

curl -X POST http://localhost:3000/api/posts \
  -H "Content-Type: application/json" \
  -d '{
    "title": "My First Post",
    "content": "This is the content of my first post",
    "userId": 1
  }'

Fetch user with posts:

curl http://localhost:3000/api/users/1

Request Response Flow Visualization

sequenceDiagram
    participant Client
    participant Route
    participant Controller
    participant Service
    participant Model
    participant DB as PostgreSQL

    Client->>Route: POST /api/users
    Route->>Controller: userController.create()
    Controller->>Service: userService.createUser()
    Service->>Model: User.create()
    Model->>DB: INSERT INTO Users
    DB-->>Model: User record created
    Model-->>Service: User instance
    Service-->>Controller: User object
    Controller-->>Client: JSON response

Query Optimization Tips

  • Use eager loading with ‘include’ to avoid N+1 queries
  • Select only needed attributes to reduce data transfer
  • Use ‘where’ clause to filter data at database level
  • Implement pagination with limit and offset for large datasets
  • Use database indexes on frequently queried columns

Common CRUD Mistakes

Returning passwords: Always exclude passwords from responses. We used attributes: {exclude: [‘password’]} in getAllUsers.

Missing error handling: Always wrap database operations in try-catch blocks to handle failures gracefully.

N+1 queries: Always use eager loading when you need related data to avoid multiple queries.

What’s Next

In Part 4, we’ll explore advanced production-ready patterns including error handling middleware, input validation, transactions, connection pooling optimization, and performance monitoring. We’ll also discuss best practices for deploying your Sequelize application.

References

  • Sequelize Querying: https://sequelize.org/docs/v6/core-concepts/model-querying-basics/
  • Sequelize Eager Loading: https://sequelize.org/docs/v6/core-concepts/assocs/#eager-loading
  • REST API Best Practices: https://restfulapi.net/
  • Node.js Error Handling: https://nodejs.org/en/docs/guides/error-handling/
Share

Written by:

399 Posts

View All Posts
Follow Me :

Related Posts

You May Have Missed

Software Engineering Mental Health

The Perfectionist’s Prison: When Good Code Isn’t Good Enough

Software Engineering Mental Health

The Invisible Leash: Breaking Free from Always-On Culture

Mental Health Opinion Career

A Letter to the Hiring Manager Who Never Saw My Portfolio

Software Engineering Mental Health

Just This Once: How Crunch Culture Became Forever

How to whitelist website on AdBlocker?

How to whitelist website on AdBlocker?

  1. 1 Click on the AdBlock Plus icon on the top right corner of your browser
  2. 2 Click on "Enabled on this site" from the AdBlock Plus option
  3. 3 Refresh the page and start browsing the site