Sequelize + Express MVC Practice Environment

A professional-grade REST API built with MVC architecture, Service Layer pattern, Express.js, Sequelize ORM, and PostgreSQL.

Postman Collection

🛣️ Find complete postman collection in the postman folder. Import the Sequelize Practice.postman_collection.json into your postman for testing.

🏗️ Architecture Overview

Clean Architecture with Separation of Concerns:
📁 Controllers → Handle HTTP requests/responses
🔧 Services   → Business logic and data operations  
🗃️ Models     → Database models and associations
🛡️ Middleware → Error handling and validation
🛣️ Routes     → API endpoint definitions
⚙️ Config     → Database and application configuration

🚀 Quick Start

1. Start the server:

   npm run dev

2. Initialize database (first time):

   npm run db:init
   npm run db:seed

3. Test the API:

   npm run test:api

📁 Project Structure

src/
├── config/
│   ├── connection.js       # Database connection setup
│   └── database.js         # Database configuration
├── controllers/
│   ├── userController.js   # User HTTP handlers
│   ├── postController.js   # Post HTTP handlers
│   ├── commentController.js# Comment HTTP handlers
│   └── profileController.js# Profile HTTP handlers
├── services/
│   ├── userService.js      # User business logic
│   ├── postService.js      # Post business logic
│   ├── commentService.js   # Comment business logic
│   └── profileService.js   # Profile business logic
├── models/
│   ├── index.js           # Model loader and associations
│   ├── User.js            # User model
│   ├── Post.js            # Post model
│   ├── Comment.js         # Comment model
│   └── Profile.js         # Profile model
├── routes/
│   ├── users.js           # User API routes
│   ├── posts.js           # Post API routes
│   ├── comments.js        # Comment API routes
│   └── profiles.js        # Profile API routes
└── middleware/
    └── errorHandler.js    # Centralized error handling

📋 Available Scripts

Script	Description
npm run dev	Start server with auto-reload
npm start	Start production server
npm run console	Run Sequelize console mode
npm run test:api	Run comprehensive API tests
npm run db:init	Initialize database tables
npm run db:seed	Seed with sample data
npm run db:reset	⚠️ Reset all tables

🌐 API Endpoints

Users (/api/users)

GET    /api/users                 # List users (paginated, filtered)
GET    /api/users/:id             # Get user by ID
POST   /api/users                 # Create user
PUT    /api/users/:id             # Update user
DELETE /api/users/:id             # Delete user
GET    /api/users/:id/posts       # Get user's posts
POST   /api/users/:id/posts       # Create post for user

Posts (/api/posts)

GET    /api/posts                 # List posts (paginated, filtered)
GET    /api/posts/published       # Get published posts
GET    /api/posts/:id             # Get post by ID
GET    /api/posts/slug/:slug      # Get post by slug
POST   /api/posts                 # Create post
PUT    /api/posts/:id             # Update post
PUT    /api/posts/:id/publish     # Publish post
DELETE /api/posts/:id             # Delete post
GET    /api/posts/:id/comments    # Get post comments
POST   /api/posts/:id/comments    # Add comment to post

Comments (/api/comments)

GET    /api/comments              # List comments (paginated, filtered)
GET    /api/comments/approved     # Get approved comments
GET    /api/comments/pending      # Get pending comments
GET    /api/comments/:id          # Get comment by ID
POST   /api/comments              # Create comment
PUT    /api/comments/:id          # Update comment  
PUT    /api/comments/:id/approve  # Approve comment
DELETE /api/comments/:id          # Delete comment

Profiles (/api/profiles)

GET    /api/profiles              # List profiles
GET    /api/profiles/:id          # Get profile by ID
GET    /api/profiles/user/:userId # Get profile by user ID
POST   /api/profiles              # Create profile
PUT    /api/profiles/:id          # Update profile
PUT    /api/profiles/:id/preferences      # Update preferences
PUT    /api/profiles/:id/social-media     # Add social media
DELETE /api/profiles/:id          # Delete profile

🎯 Architecture Benefits

Controllers Layer

• ✅ Handle HTTP requests/responses
• ✅ Input validation and sanitization
• ✅ Response formatting
• ✅ Error handling delegation

Services Layer

• ✅ Business logic encapsulation
• ✅ Data transformation
• ✅ Multiple data source coordination
• ✅ Reusable across controllers

Models Layer

• ✅ Database schema definition
• ✅ Data validation rules
• ✅ Relationships and associations
• ✅ Query scopes and methods

Middleware Layer

• ✅ Centralized error handling
• ✅ Request/response processing
• ✅ Authentication (ready for JWT)
• ✅ Logging and monitoring

🔧 Query Features

Pagination

GET /api/users?page=2&limit=10

Filtering & Search

GET /api/posts?status=published&search=nodejs&tag=tutorial
GET /api/users?isActive=true&search=john
GET /api/comments?isApproved=false&postId=123

Eager Loading

GET /api/users?includeProfile=true&includePosts=true
GET /api/posts?includeAuthor=true&includeComments=true  

Scopes

GET /api/posts/published        # Using published scope
GET /api/comments/pending       # Using pending scope
GET /api/comments/approved      # Using approved scope

📝 Example Usage

Create User with Profile

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

# 2. Create profile
curl -X POST http://localhost:3000/api/profiles \
  -H "Content-Type: application/json" \
  -d '{"userId":1,"bio":"Developer","location":"NYC"}'

Create and Publish Post

# 1. Create draft post
curl -X POST http://localhost:3000/api/posts \
  -H "Content-Type: application/json" \
  -d '{"title":"My Post","content":"Content...","slug":"my-post","userId":1}'

# 2. Publish post  
curl -X PUT http://localhost:3000/api/posts/1/publish

Comment Approval Workflow

# 1. Create comment (starts as pending)
curl -X POST http://localhost:3000/api/comments \
  -H "Content-Type: application/json" \
  -d '{"content":"Great post!","userId":2,"postId":1}'

# 2. Approve comment
curl -X PUT http://localhost:3000/api/comments/1/approve

🧪 Testing

Automated API Testing

npm run test:api

Manual Testing

# Health check
curl http://localhost:3000/health

# Get users with profiles
curl "http://localhost:3000/api/users?includeProfile=true"

# Get published posts with authors
curl "http://localhost:3000/api/posts/published?includeAuthor=true"

🎓 Learning Objectives

Express.js & MVC

• ✅ MVC architecture implementation
• ✅ Service layer pattern
• ✅ Middleware development
• ✅ RESTful API design
• ✅ Error handling strategies

Sequelize ORM

• ✅ Model definitions and validations
• ✅ Associations and relationships
• ✅ Query optimization
• ✅ Scopes and instance methods
• ✅ Hooks and lifecycle events

🔧 Configuration

Environment Variables (.env)

NODE_ENV=development
PORT=3000
DB_HOST=localhost
DB_PORT=5432
DB_NAME=sequelize_db
DB_USERNAME=postgres
DB_PASSWORD=postgres

Database Connection

• Host: localhost:5432
• Database: sequelize_db
• Username: postgres
• Password: postgres

🚨 Error Handling

The API provides comprehensive error handling:

• 400 Bad Request - Validation errors, invalid input
• 404 Not Found - Resource doesn't exist
• 409 Conflict - Duplicate entries (email, slug)
• 500 Internal Server Error - Server-side errors

💡 Development Tips

1. Start with services - Write business logic first
2. Keep controllers thin - Delegate to services
3. Use transactions - For multi-model operations
4. Leverage associations - Reduce N+1 queries
5. Handle errors gracefully - Use middleware
6. Test incrementally - Use npm run test:api

Happy practicing with modern Node.js architecture!