🚀 Node.js Backend API

Enterprise-Grade Node.js Backend API with Advanced Features

Node.js Backend is a comprehensive, production-ready RESTful API built with Express.js, featuring JWT authentication with Instagram-style multi-token pattern, role-based access control, real-time activity logging, advanced security middleware, and comprehensive monitoring capabilities.

Repository: https://github.com/fullstack-open-source/nodejs-backend-with-sql

🚀 Features

🎯 Core Capabilities

RESTful API: Comprehensive REST API with Express.js framework
JWT Authentication: Instagram-style multi-token authentication system (Access, Session, Refresh tokens)
Role-Based Access Control: Flexible permission system with groups and permissions
Activity Logging: Comprehensive audit trail with detailed metadata
File Upload: Google Cloud Storage integration for media files
Email & SMS: Twilio and Nodemailer integration for notifications and OTP
Error Tracking: Sentry integration for production monitoring and error tracking
API Documentation: Auto-generated Swagger/OpenAPI documentation
Multilingual Support: Multi-language API responses with automatic translation
Rate Limiting: Request throttling and rate limiting for traffic management
Worker Pool: CPU-intensive task processing with worker threads
Docker Ready: Production-ready containerization with multi-stage builds
PM2 Support: Process management with cluster mode for high availability

🔧 Technical Features

Express.js 4.18+: Modern web framework for building APIs
PostgreSQL 16+: Robust relational database with advanced features
Prisma ORM: Type-safe database access with connection pooling
Redis: Caching and session management for improved performance
JWT: Secure token-based authentication and authorization
Winston: Professional logging system with file rotation
Helmet: Security headers for protection against common vulnerabilities
Rate Limiting: Request throttling to prevent abuse
Compression: Response compression for optimized bandwidth usage
CORS: Cross-origin resource sharing with whitelist validation
Swagger: Interactive API documentation with Swagger UI
Nginx Reverse Proxy: Production-ready reverse proxy configuration
Multilingual: Multi-language support for API responses (English, Arabic, and more)

🔐 Authentication Features

Multi-Token System: Access token (1 hour), Session token (7 days), Refresh token (30 days)
Token Blacklisting: Redis-based token invalidation for secure logout
OTP Verification: Email, SMS, and WhatsApp OTP support
Password Management: Set, change, and reset password functionality
Email/Phone Verification: Two-step verification for contact changes
Session Management: Stateless session management with unique session IDs
Token Rotation: Automatic token rotation on refresh for enhanced security

🗄️ Database Support & Flexibility

This project uses Prisma ORM, which provides exceptional database flexibility and support for multiple database systems. While the project is configured for PostgreSQL by default, Prisma allows you to easily switch between different databases without changing your application code.

Currently Configured Database

PostgreSQL 16+: The default database for this project, providing robust relational database features, JSON support, and advanced querying capabilities.

Prisma Supported Databases

Prisma ORM supports 10+ database systems, giving you the flexibility to choose the best database for your needs:

Self-hosted Databases:

✅ PostgreSQL (9.6 - 17) - Currently configured
✅ MySQL (5.6, 5.7, 8.0, 8.4)
✅ MariaDB (10.0+)
✅ SQLite (All versions)
✅ Microsoft SQL Server (2017, 2019, 2022)
✅ MongoDB (4.2+)
✅ CockroachDB (21.2.4+)

Managed/Cloud Databases:

✅ AWS Aurora (All versions)
✅ AWS Aurora Serverless (All versions)
✅ Azure SQL (All versions)
✅ CockroachDB-as-a-Service (All versions)
✅ MongoDB Atlas (All versions)
✅ Neon Serverless Postgres (All versions)
✅ PlanetScale (All versions)
✅ Cloudflare D1 (Preview)
✅ Aiven (MySQL & Postgres)

Advantages of Using Prisma

Database Agnostic: Switch between databases by simply changing the provider in schema.prisma
Type Safety: Auto-generated TypeScript types based on your database schema
Migration Management: Built-in migration system for schema versioning
Connection Pooling: Automatic connection pool management for optimal performance
Query Optimization: Intelligent query builder that generates optimized SQL
Developer Experience: Intuitive API with excellent IDE autocomplete support
Multi-Database Support: Use different databases for different environments (e.g., SQLite for development, PostgreSQL for production)

Switching Databases

To switch to a different database, simply update the datasource provider in api/prisma/schema.prisma:

// For PostgreSQL (current)
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// For MySQL
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

// For SQLite
datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

// For MongoDB
datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

Then run:

# ------------------------------
# Prisma Client & Schema
# ------------------------------

# Regenerate Prisma Client after schema changes
npm run prisma:generate

# Pull the current database schema into Prisma (update schema.prisma)
npm run prisma:pull

# Push the Prisma schema to the database (without migration)
npm run prisma:push

# Open Prisma Studio (GUI to view/edit database)
npm run prisma:studio

# Format Prisma schema file
npm run prisma:format

# Validate Prisma schema for errors
npm run prisma:validate

# ------------------------------
# Database seeding & syncing
# ------------------------------

# Pull data (custom seed script)
npm run db:pull

# Push data (custom seed script)
npm run db:push

# Sync data (custom seed script)
npm run db:sync

# Seed the database with initial data
npm run db:seed

# ------------------------------
# Migrations
# ------------------------------

# Create and apply migrations (development)
npm run migrate:dev

# Apply migrations in production
npm run migrate:deploy

# Drop database and reapply all migrations (destructive)
npm run migrate:reset

Note: Some schema features may vary between databases. Refer to Prisma Database Documentation for database-specific considerations.

🏗️ Architecture

System Architecture

The system follows a layered architecture with clear separation of concerns:

┌───────────────────────────────────────────────────────────────┐
│                        External Proxy                         │
│                    (api.example.com)                          │
│                    SSL/TLS Termination                        │
└────────────────────────────┬──────────────────────────────────┘
                              │
                              ▼
┌────────────────────────────────────────────────────────────────┐
│                    Internal Nginx Proxy                        │
│                    (Port 9080:80)                              │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  Security Layer:                                         │  │
│  │  - Rate Limiting (200 req/min per IP)                    │  │
│  │  - Request Size Validation (15MB max)                    │  │
│  │  - Attack Pattern Detection                              │  │
│  │  - Content Security Policy                               │  │
│  │  - DDoS Protection                                       │  │
│  └──────────────────────────────────────────────────────────┘  │
└────────────────────────────┬───────────────────────────────────┘
                             │
                             ▼
┌────────────────────────────────────────────────────────────────┐
│                    Express.js Application                      │
│                    (Port 3000)                                 │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  Middleware Stack:                                       │  │
│  │  1. Sentry Request Handler                               │  │
│  │  2. Helmet (Security Headers)                            │  │
│  │  3. CORS (Cross-Origin)                                  │  │
│  │  4. Body Parser (JSON/URL-encoded)                       │  │
│  │  5. Compression                                          │  │
│  │  6. Morgan (Request Logging)                             │  │
│  │  7. Request Logger (Custom)                              │  │
│  │  8. Rate Limiter                                         │  │
│  │  9. Security Middleware (Custom)                         │  │
│  └──────────────────────────────────────────────────────────┘  │
│  ┌──────────────────────────────────────────────────────────┐  │
│  │  Application Modules:                                    │  │
│  │  - Authentication (JWT, OTP, Login)                      │  │
│  │  - Profile Management                                    │  │
│  │  - Permissions (Groups, Permissions)                     │  │
│  │  - Dashboard (Analytics)                                 │  │
│  │  - Activity Logging                                      │  │
│  │  - File Upload (GCS)                                     │  │
│  │  - Health Monitoring                                     │  │
│  └──────────────────────────────────────────────────────────┘  │
└────────────────────────────┬──────────────────────────────-────┘
                             │
                ┌────────────┴─────────────┐
                ▼                          ▼
┌──────────────────────────┐   ┌──────────────────────────┐
│   PostgreSQL Database    │   │   Redis Cache            │
│   (Prisma ORM)           │   │   (Sessions, Cache)      │
│   - User Data            │   │   - Session Storage      │
│   - Permissions          │   │   - OTP Cache            │
│   - Activity Logs        │   │   - Token Blacklist      │
│   - Groups               │   │   - Rate Limiting        │
└──────────────────────────┘   └──────────────────────────┘
                │
                ▼
┌──────────────────────────┐
│   Google Cloud Storage   │
│   (Media & Static Files) │
│   - User Uploads         │
│   - Generated Content    │
│   - Static Assets        │
└──────────────────────────┘

Authentication Architecture

The authentication system implements a stateless, multi-token architecture similar to Instagram:

Key Features:

Multi-Token System: Access token (1 hour), Session token (7 days), Refresh token (30 days)
Token Blacklisting: Redis-based cache for token invalidation
Session Management: Unique session_id links all tokens together
Token Rotation: Refresh tokens rotate on each refresh for security
Origin Validation: Domain-specific token validation

Complete Documentation: See Authentication Router

Request Flow

1. Client Request
   │
   ├─► External Proxy (api.example.com)
   │
   ├─► Internal Nginx (Port 9080)
   │   ├─► Security Checks (Rate Limiting, Attack Detection)
   │   ├─► Request Size Validation
   │   ├─► Content Security Policy Headers
   │   └─► Proxy to Express (http://api:3000)
   │
   ├─► Express Middleware Stack
   │   ├─► Sentry Request Handler
   │   │   └─► Request Context Capture
   │   │
   │   ├─► Helmet
   │   │   └─► Security Headers
   │   │
   │   ├─► CORS
   │   │   └─► Origin Validation
   │   │
   │   ├─► Body Parser
   │   │   ├─► JSON Parsing (15MB limit)
   │   │   └─► URL-encoded Parsing
   │   │
   │   ├─► Compression
   │   │   └─► Response Compression
   │   │
   │   ├─► Morgan + Request Logger
   │   │   └─► Request Logging
   │   │
   │   ├─► Rate Limiter
   │   │   └─► Request Throttling (200/min)
   │   │
   │   └─► Security Middleware
   │       ├─► Input Sanitization
   │       ├─► SQL Injection Detection
   │       ├─► XSS Detection
   │       └─► Command Injection Detection
   │
   ├─► Route Handler
   │   ├─► JWT Authentication (if required)
   │   ├─► Permission Check (if required)
   │   ├─► Request Validation
   │   ├─► Business Logic
   │   ├─► Database Operations (Prisma)
   │   └─► Response Formatting
   │
   ├─► Activity Logging
   │   └─► Log to Database
   │
   └─► Response
       ├─► Error Handling (if any)
       ├─► Sentry Error Capture (if error)
       ├─► Security Headers
       └─► Client Response

Middleware Stack

The middleware stack processes requests in a specific order to ensure security, performance, and reliability:

Sentry Request Handler - Captures request context for error tracking and performance monitoring
Helmet - Sets security headers including X-Content-Type-Options, X-Frame-Options, and X-XSS-Protection
CORS - Handles cross-origin requests with whitelist validation for allowed origins
Body Parser - Parses JSON and URL-encoded request bodies with 15MB size limit
Compression - Compresses responses using gzip algorithm for bandwidth optimization
Morgan + Request Logger - Logs HTTP requests with detailed metadata including IP, method, path, and response time
Rate Limiter - Throttles requests to 200 requests per minute per IP address
Security Middleware - Sanitizes input data and detects attack patterns including SQL injection, XSS, and command injection
Route Handlers - Application-specific logic execution
Error Handler - Catches and formats errors with appropriate status codes
Sentry Error Handler - Captures errors for monitoring and alerting

Database Architecture

┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL Database                      │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  User Model                                          │   │
│  │  - user_id (UUID, Primary Key)                       │   │
│  │  - email, phone_number, user_name                    │   │
│  │  - password, auth_type                               │   │
│  │  - is_email_verified, is_phone_verified              │   │
│  │  - status, is_active, is_verified                    │   │
│  │  - profile_picture_url, bio                          │   │
│  │  - Relationships: UserGroup[], ActivityLog[]         │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  Permission Model                                    │   │
│  │  - permission_id (UUID, Primary Key)                 │   │
│  │  - name, codename (unique)                           │   │
│  │  - description, category                             │   │
│  │  - Relationships: GroupPermission[]                  │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  Group Model                                         │   │
│  │  - group_id (UUID, Primary Key)                      │   │
│  │  - name, codename (unique)                           │   │
│  │  - description, is_system, is_active                 │   │
│  │  - Relationships: GroupPermission[], UserGroup[]     │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  GroupPermission Model (Many-to-Many)                │   │
│  │  - id (UUID, Primary Key)                            │   │
│  │  - group_id (FK → Group)                             │   │
│  │  - permission_id (FK → Permission)                   │   │
│  │  - Unique constraint: (group_id, permission_id)      │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  UserGroup Model (Many-to-Many)                      │   │
│  │  - id (UUID, Primary Key)                            │   │
│  │  - user_id (FK → User)                               │   │
│  │  - group_id (FK → Group)                             │   │
│  │  - assigned_at, assigned_by_user_id                  │   │
│  │  - Unique constraint: (user_id, group_id)            │   │
│  └──────────────────────────────────────────────────────┘   │
│                                                             │
│  ┌──────────────────────────────────────────────────────┐   │
│  │  ActivityLog Model                                   │   │
│  │  - log_id (UUID, Primary Key)                        │   │
│  │  - user_id (FK → User, nullable)                     │   │
│  │  - level, message, action, module                    │   │
│  │  - ip_address, user_agent, device, browser, os       │   │
│  │  - endpoint, method, status_code                     │   │
│  │  - request_id, session_id                            │   │
│  │  - metadata, error_details (JSONB)                   │   │
│  │  - duration_ms, created_at                           │   │
│  └──────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘

Module Structure

NodeJs Backend API
├── 📁 api/                              # Main application directory
│   ├── 📄 server.js                     # Express server entry point
│   ├── 📄 Dockerfile                    # Multi-stage Docker build
│   ├── 📄 ecosystem.config.js           # PM2 configuration
│   ├── 📄 package.json                  # Dependencies and scripts
│   ├── 📄 start.sh                      # Container startup script
│   │
│   ├── 📁 router/                       # API route handlers
│   │   ├── 🔐 authenticate/              # Authentication routes
│   │   │   ├── authenticate.js          # Login, OTP, token management
│   │   │   ├── profile.js               # User profile management
│   │   │   ├── models.js                # Request validation schemas
│   │   │   ├── query.js                 # Database queries
│   │   │   └── utils.js                 # Helper functions
│   │   │
│   │   ├── 📊 dashboard/                # Dashboard routes
│   │   │   └── api.js                   # Analytics and statistics
│   │   │
│   │   ├── 🔑 permissions/              # Permission management
│   │   │   └── api.js                   # Groups, permissions, users
│   │   │
│   │   ├── 📝 activity/                 # Activity logging
│   │   │   └── api.js                   # Activity log endpoints
│   │   │
│   │   ├── 📤 upload/                   # File upload
│   │   │   └── api.js                   # Media upload endpoints
│   │   │
│   │   └── ❤️ health/                   # Health monitoring
│   │       ├── api.js                   # Health check endpoints
│   │       └── test-sentry.js           # Sentry test endpoint
│   │
│   ├── 📁 src/                          # Source code modules
│   │   ├── 🔐 authenticate/             # Authentication logic
│   │   │   ├── authenticate.js          # JWT validation
│   │   │   ├── checkpoint.js            # User authentication
│   │   │   ├── models.js                # User models
│   │   │   └── otp_cache.js             # OTP management
│   │   │
│   │   ├── 📊 activity/                 # Activity logging
│   │   │   └── activityLog.js          # Activity log service
│   │   │
│   │   ├── 💾 cache/                    # Caching layer
│   │   │   └── cache.js                # Redis cache utilities
│   │   │
│   │   ├── ⚙️ config/                   # Configuration
│   │   │   └── swagger.js               # Swagger/OpenAPI config
│   │   │
│   │   ├── 🗄️ db/                       # Database layer
│   │   │   ├── prisma.js                # Prisma client setup
│   │   │   ├── index.js                 # Database utilities
│   │   │   └── postgres/                # PostgreSQL utilities
│   │   │       ├── postgres.js          # Connection pool
│   │   │       └── initTriggers.js      # Database triggers
│   │   │
│   │   ├── 📧 email/                    # Email service
│   │   │   ├── email.js                 # Email sending
│   │   │   └── template.js              # Email templates
│   │   │
│   │   ├── 📋 enum/                     # Enumerations
│   │   │   └── enum.js                  # Application enums
│   │   │
│   │   ├── 📝 logger/                   # Logging system
│   │   │   └── logger.js                # Winston logger
│   │   │
│   │   ├── 🛡️ middleware/               # Express middleware
│   │   │   ├── errorHandler.js          # Global error handler
│   │   │   ├── permissionMiddleware.js   # Permission checking
│   │   │   ├── requestLogger.js         # Request logging
│   │   │   ├── requestQueue.js          # Request queuing
│   │   │   └── securityMiddleware.js    # Security checks
│   │   │
│   │   ├── 🔑 permissions/               # Permission system
│   │   │   └── permissions.js            # Permission utilities
│   │   │
│   │   ├── 📤 response/                  # Response handlers
│   │   │   ├── success.js               # Success responses
│   │   │   ├── error.js                 # Error responses
│   │   │   └── map.js                   # Response mapping
│   │   │
│   │   ├── 🐛 sentry/                    # Error tracking
│   │   │   └── sentry.js                # Sentry configuration
│   │   │
│   │   ├── 📱 sms/                       # SMS service
│   │   │   └── sms.js                   # Twilio integration
│   │   │
│   │   ├── 💾 storage/                   # File storage
│   │   │   └── storage.js               # Google Cloud Storage
│   │   │
│   │   ├── 🛠️ utils/                     # Utilities
│   │   │   ├── debug.js                 # Debug utilities
│   │   │   └── workerUtils.js           # Worker pool utilities
│   │   │
│   │   ├── 🌐 multilingual/              # Multilingual support
│   │   │   └── multilingual.js          # Language utilities
│   │   │
│   │   └── 👷 workers/                   # Worker threads
│   │       ├── workerPool.js            # Worker pool manager
│   │       └── cpuTaskWorker.js         # CPU task worker
│   │
│   ├── 📁 prisma/                        # Prisma ORM
│   │   ├── schema.prisma                # Database schema
│   │   ├── seed.js                      # Database seeding
│   │   └── seed-defaults.js             # Default seed data
│   │
│   ├── 📁 credentials/                   # Service credentials
│   │   └── google-backend-master.json   # GCS credentials
│   │
│   ├── 📁 logs/                          # Application logs
│   │   ├── errors-server.log            # Error logs
│   │   └── success-server.log           # Success logs
│   │
│   └── 📁 scripts/                       # Utility scripts
│       ├── kill-port.sh                 # Port cleanup
│       └── db-sync.sh                   # Database sync
│
├── 📁 nginx/                             # Nginx configuration
│   ├── nginx.conf                        # Main nginx config
│   ├── proxy.conf                        # Proxy settings
│   ├── security.conf                    # Security headers
│   └── conf.d/                          # Additional configs
│       ├── default.conf                 # Default server config
│       └── security.conf                # Security rules
│
├── 📄 docker-compose.yaml                # Docker Compose config
├── 📄 reload.sh                          # Deployment script
└── 📄 README.md                          # This file

📚 API Documentation

Authentication & User Management

Complete Documentation: Authentication Router

Endpoints:

POST /{MODE}/auth/login-with-password - Login with email/phone and password
POST /{MODE}/auth/login-with-otp - Login with OTP
POST /{MODE}/auth/send-one-time-password - Send OTP via email/SMS/WhatsApp
POST /{MODE}/auth/verify-one-time-password - Verify OTP
POST /{MODE}/auth/verify - Signup/Register with OTP
POST /{MODE}/auth/set-password - Set password for authenticated user
POST /{MODE}/auth/change-password - Change user password
POST /{MODE}/auth/forget-password - Reset password with OTP
POST /{MODE}/auth/refresh-token - Refresh access tokens
POST /{MODE}/auth/logout - Logout and revoke tokens
POST /{MODE}/auth/token-info - Get token information
POST /{MODE}/auth/check-user-availability - Check email/phone availability
POST /{MODE}/auth/verify-email-and-phone - Verify email/phone with OTP

Profile Management: Profile Router

Endpoints:

GET /{MODE}/settings/profile - Get user profile
GET /{MODE}/settings/profile/{user_id} - Get profile by ID
POST /{MODE}/settings/profile-picture - Update profile picture
PUT /{MODE}/settings/profile - Update user profile
POST /{MODE}/settings/change-email - Change email with OTP verification
POST /{MODE}/settings/change-phone - Change phone with OTP verification
POST /{MODE}/settings/profile-accessibility - Update profile accessibility
POST /{MODE}/settings/profile-language - Update profile language
POST /{MODE}/settings/update-theme - Update theme preference
POST /{MODE}/settings/update-timezone - Update timezone
GET /{MODE}/settings - Get user settings
POST /{MODE}/settings/deactivate-account - Deactivate account
POST /{MODE}/settings/delete-account - Delete account

Permissions & Groups

Complete Documentation: Permissions Router

Endpoints:

GET /{MODE}/permissions - Get all permissions
GET /{MODE}/permissions/{permission_id} - Get permission by ID
POST /{MODE}/permissions - Create new permission
PUT /{MODE}/permissions/{permission_id} - Update permission
DELETE /{MODE}/permissions/{permission_id} - Delete permission
GET /{MODE}/groups - Get all groups
GET /{MODE}/groups/{group_id} - Get group by ID
POST /{MODE}/groups - Create new group
PUT /{MODE}/groups/{group_id} - Update group
DELETE /{MODE}/groups/{group_id} - Delete group
POST /{MODE}/groups/{group_id}/permissions - Assign permissions to group
GET /{MODE}/users/{user_id}/groups - Get user groups
GET /{MODE}/users/{user_id}/permissions - Get user permissions
POST /{MODE}/users/{user_id}/groups - Assign groups to user
GET /{MODE}/users/me/groups - Get current user groups
GET /{MODE}/users/me/permissions - Get current user permissions

Dashboard & Analytics

Complete Documentation: Dashboard Router

Endpoints:

GET /{MODE}/dashboard/overview - Get dashboard overview statistics
GET /{MODE}/dashboard/users-by-status - Get users grouped by status
GET /{MODE}/dashboard/users-by-type - Get users grouped by type
GET /{MODE}/dashboard/users-by-auth-type - Get users grouped by auth type
GET /{MODE}/dashboard/users-by-country - Get users grouped by country
GET /{MODE}/dashboard/users-by-language - Get users grouped by language
GET /{MODE}/dashboard/user-growth - Get user growth statistics
GET /{MODE}/dashboard/role-statistics - Get role/group statistics
GET /{MODE}/dashboard/recent-sign-ins - Get recent sign-in activity
GET /{MODE}/dashboard/all-statistics - Get all statistics

File Upload

Complete Documentation: Upload Router

Endpoints:

POST /{MODE}/upload/media - Upload media file (direct upload or URL)
DELETE /{MODE}/upload/media/{file_id} - Delete uploaded media

Features:

Direct file upload with multipart/form-data
URL-based upload (downloads and stores file from URL)
Google Cloud Storage integration
Automatic file validation and processing
Support for images, videos, and documents

Activity Logging

Complete Documentation: Activity Router

Endpoints:

GET /{MODE}/activity - Get activity logs with filtering
GET /{MODE}/activity/{log_id} - Get specific activity log
POST /{MODE}/activity - Create activity log entry

Features:

Comprehensive audit trail
User action tracking
Request/response logging
Error logging with details
Metadata storage (JSONB)

Health Monitoring

Complete Documentation: Health Router

Endpoints:

GET /health - Basic health check (no prefix)
GET /{MODE}/health - Detailed health check
GET /{MODE}/health/system - System health with metrics
GET /{MODE}/health/database - Database connection health
GET /{MODE}/health/storage - Storage (GCS) health check
GET /{MODE}/health/test-sentry - Test Sentry integration

📦 Installation & Setup

Prerequisites

System Requirements:

OS: Linux (Ubuntu 20.04+), macOS, or Windows with WSL2
Node.js: 18.0 or higher
npm: 9.0 or higher
PostgreSQL: 12+ (for production)
Redis: 6+ (optional, for caching)
Docker: 20.10+ (for containerized deployment)
Docker Compose: 2.0+ (for multi-container orchestration)
Google Cloud Storage: Account and bucket (for media files)

Development Tools:

Git 2.30+
Code editor (VS Code recommended)
Postman or similar API testing tool

Quick Start

Step 1: Clone Repository

git clone https://github.com/fullstack-open-source/nodejs-backend-with-sql.git
cd nodejs-backend-with-sql

Step 2: Setup Environment

# Copy example environment file
cp example.env .env

# Edit .env file with your configuration
nano .env

Step 2.5: Setup Google Cloud Storage Credentials (Optional)

If you plan to use file upload features, you need to configure Google Cloud Storage credentials:

# Navigate to credentials directory
cd api/credentials

# Copy the template credential file
cp "google-backend-master copy.json" google-backend-master.json

# Edit the credential file with your Google Cloud Service Account credentials
nano google-backend-master.json

How to get Google Cloud Service Account credentials:

Go to Google Cloud Console
Select or create a project
Navigate to IAM & Admin → Service Accounts
Create a new service account or select an existing one
Click on the service account → Keys tab
Click Add Key → Create new key → Choose JSON format
Download the JSON file
Copy the contents of the downloaded JSON file into api/credentials/google-backend-master.json

Important Notes:

The template file google-backend-master copy.json is provided as a reference
Your actual credentials file google-backend-master.json is ignored by git (for security)
Make sure your service account has the necessary permissions for Google Cloud Storage
Update GOOGLE_STORAGE_BUCKET_NAME in your .env file with your bucket name

Step 3: Start Services with Docker Compose

# Create Docker network (if not exists)
docker network create nodejs_backend_with_postgresql_network

# Start all services
docker compose up -d

# Check service status
docker compose ps

# View logs
docker compose logs -f api

Step 4: Setup Database Schema

# Generate Prisma Client
docker compose exec api npm run prisma:generate

# Push schema to database
docker compose exec api npm run db:push

# Seed database (optional)
docker compose exec api npm run db:seed

# Apply migrations
docker compose exec api npm run migrate:dev    

# Inspect DB with Prisma Studio
docker compose exec api npm run prisma:studio

Step 5: Access Services

API Base: http://localhost:9080
API Docs: http://localhost:9080/api-docs
Health Check: http://localhost:9080/health (no prefix) or http://localhost:9080/{MODE}/health
API Routes: http://localhost:9080/{MODE}/* (e.g., /prod/v1/authenticate, /dev/v1/token)
pgAdmin: http://localhost:5050 (if enabled)

Note: Replace {MODE} with your configured MODE value (e.g., prod/v1 or dev/v1)

API Route Prefix (MODE)

All API routes are prefixed with the MODE environment variable. This allows you to version your API and separate environments.

Configuration in .env:

MODE=prod/v1    # For production API version 1
# or
MODE=dev/v1     # For development API version 1

API Route Examples:

If MODE=prod/v1, all routes will be prefixed with /prod/v1/:

Health Check: http://localhost:9080/prod/v1/health
Authentication: http://localhost:9080/prod/v1/authenticate
Login: http://localhost:9080/prod/v1/token
Profile: http://localhost:9080/prod/v1/settings
Dashboard: http://localhost:9080/prod/v1/dashboard
Permissions: http://localhost:9080/prod/v1/permissions
Activity: http://localhost:9080/prod/v1/activity

If MODE=dev/v1, all routes will be prefixed with /dev/v1/:

Health Check: http://localhost:9080/dev/v1/health
Authentication: http://localhost:9080/dev/v1/authenticate
Login: http://localhost:9080/dev/v1/token
Profile: http://localhost:9080/dev/v1/settings
Dashboard: http://localhost:9080/dev/v1/dashboard
Permissions: http://localhost:9080/dev/v1/permissions
Activity: http://localhost:9080/dev/v1/activity

Note: The /health endpoint is available without the MODE prefix for health checks:

Direct Health Check: http://localhost:9080/health (no prefix)

Development Setup

Step 1: System Preparation

# Update system packages
sudo apt update && sudo apt upgrade -y

# Install Node.js 18+
curl -fsSL https://deb.nodesource.com/setup_18.x | sudo -E bash -
sudo apt install -y nodejs

# Install Docker (optional, for containerized development)
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh
sudo apt install docker-compose-plugin -y

Step 2: Project Setup

# Clone repository
git clone https://github.com/fullstack-open-source/nodejs-backend-with-sql.git
cd nodejs-backend-with-sql/api

# Install dependencies
npm install

Step 3: Environment Configuration

# Copy example environment file
cp ../example.env ../.env

# Edit .env file with your settings
nano ../.env

Step 3.5: Setup Google Cloud Storage Credentials (Optional)

If you plan to use file upload features, configure Google Cloud Storage credentials:

# Navigate to credentials directory
cd credentials

# Copy the template credential file
cp "google-backend-master copy.json" google-backend-master.json

# Edit the credential file with your Google Cloud Service Account credentials
nano google-backend-master.json

How to get Google Cloud Service Account credentials:

Go to Google Cloud Console
Select or create a project
Navigate to IAM & Admin → Service Accounts
Create a new service account or select an existing one
Click on the service account → Keys tab
Click Add Key → Create new key → Choose JSON format
Download the JSON file
Copy the contents of the downloaded JSON file into api/credentials/google-backend-master.json

Important Notes:

The template file google-backend-master copy.json is provided as a reference
Your actual credentials file google-backend-master.json is ignored by git (for security)
Make sure your service account has the necessary permissions for Google Cloud Storage
Update GOOGLE_STORAGE_BUCKET_NAME in your .env file with your bucket name

Step 4: Database Setup (Local PostgreSQL)

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib -y

# Create database and user
sudo -u postgres psql << EOF
CREATE DATABASE nodejs_backend;
CREATE USER nodejs_user WITH PASSWORD 'your_password';
GRANT ALL PRIVILEGES ON DATABASE nodejs_backend TO nodejs_user;
\q
EOF

# Update .env with local database connection
# DATABASE_URL=postgresql://nodejs_user:your_password@localhost:5432/nodejs_backend

Step 5: Prisma Setup

# ------------------------------
# Prisma Client & Schema
# ------------------------------

# Regenerate Prisma Client after schema changes
npm run prisma:generate

# Pull the current database schema into Prisma (update schema.prisma)
npm run prisma:pull

# Push the Prisma schema to the database (without migration)
npm run prisma:push

# Open Prisma Studio (GUI to view/edit database)
npm run prisma:studio

# Format Prisma schema file
npm run prisma:format

# Validate Prisma schema for errors
npm run prisma:validate

# ------------------------------
# Database seeding & syncing
# ------------------------------

# Pull data (custom seed script)
npm run db:pull

# Push data (custom seed script)
npm run db:push

# Sync data (custom seed script)
npm run db:sync

# Seed the database with initial data
npm run db:seed

# ------------------------------
# Migrations
# ------------------------------

# Create and apply migrations (development)
npm run migrate:dev

# Apply migrations in production
npm run migrate:deploy

# Drop database and reapply all migrations (destructive)
npm run migrate:reset

Step 6: Seed Database (Optional)

# Seed with default data
npm run db:seed

Step 7: Start Development Server

# Development mode with auto-reload
npm run dev

# Or start directly
npm start

Step 8: Access Application

API Base: http://localhost:3000
API Docs: http://localhost:3000/api-docs
Health Check: http://localhost:3000/health (no prefix) or http://localhost:3000/{MODE}/health
API Routes: http://localhost:3000/{MODE}/* (e.g., /prod/v1/authenticate, /dev/v1/token)
Prisma Studio: http://localhost:5555 (if running)

Note: Replace {MODE} with your configured MODE value (e.g., prod/v1 or dev/v1)

Production Setup

Step 1: Server Preparation

# Update system
sudo apt update && sudo apt upgrade -y

# Install Node.js 18+
curl -fsSL https://deb.nodesource.com/setup_18.x | sudo -E bash -
sudo apt install -y nodejs

# Install PM2
npm install -g pm2

# Install Docker
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh
sudo apt install docker-compose-plugin -y

Step 2: Clone and Setup Project

# Clone repository
git clone https://github.com/fullstack-open-source/nodejs-backend-with-sql.git
cd nodejs-backend-with-sql

# Create production .env file
cp example.env .env

# Edit .env file with production settings
nano .env

Important: Update .env file with production values:

Set NODE_ENV=production
Set API_MODE=production
Set DEBUG_MODE=false
Configure production database credentials
Set strong JWT secret (generate with: openssl rand -base64 32)
Configure production Redis, Sentry, and other services
Set GOOGLE_STORAGE_BUCKET_NAME with your production bucket name

Step 2.5: Setup Google Cloud Storage Credentials (Required for File Upload)

For production file uploads, configure Google Cloud Storage credentials:

# Navigate to credentials directory
cd api/credentials

# Copy the template credential file
cp "google-backend-master copy.json" google-backend-master.json

# Edit the credential file with your production Google Cloud Service Account credentials
nano google-backend-master.json

How to get Google Cloud Service Account credentials:

Go to Google Cloud Console
Select or create a project
Navigate to IAM & Admin → Service Accounts
Create a new service account or select an existing one
Click on the service account → Keys tab
Click Add Key → Create new key → Choose JSON format
Download the JSON file
Copy the contents of the downloaded JSON file into api/credentials/google-backend-master.json

Production Security Notes:

The template file google-backend-master copy.json is provided as a reference
Your actual credentials file google-backend-master.json is ignored by git (for security)
Use a dedicated service account with minimal required permissions
Enable Cloud Storage API in your Google Cloud project
Ensure your service account has Storage Object Admin or Storage Admin role
Never commit your actual credentials file to version control

Step 3: Deploy with Docker (Recommended)

# Build and start services
docker compose build
docker compose up -d

# Check status
docker compose ps

# View logs
docker compose logs -f api

# Run migrations
docker compose exec api npm run db:push

Step 4: Or Deploy with PM2

cd api

# Generate Prisma Client
npm run prisma:generate

# Start with PM2
npm run pm2:start

# Check status
pm2 status

# View logs
pm2 logs

# Monitor
pm2 monit

Step 5: Configure External Proxy (Nginx)

# Install Nginx
sudo apt install nginx -y

# Create Nginx config
sudo nano /etc/nginx/sites-available/api.example.com

Add configuration:

server {
    listen 80;
    server_name api.example.com;

    location / {
        proxy_pass http://localhost:9080;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

# Enable site
sudo ln -s /etc/nginx/sites-available/api.example.com /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl reload nginx

Docker Setup

Step 1: Prerequisites

# Check Docker version
docker --version
docker compose version

# Install Docker if needed
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh get-docker.sh
sudo apt install docker-compose-plugin -y

Step 2: Docker Compose Services

The docker-compose.yaml includes:

api: Node.js application (Express server)
db: PostgreSQL database
redis: Redis cache and session store
nginx: Internal reverse proxy
pgadmin: PostgreSQL admin interface

Step 3: Build and Start Services

# Create Docker network
docker network create nodejs_backend_with_postgresql_network

# Build images
docker compose build

# Start all services
docker compose up -d

# View all logs
docker compose logs -f

# View specific service logs
docker compose logs -f api
docker compose logs -f nginx

Step 4: Service Management

# Stop services
docker compose stop

# Start services
docker compose start

# Restart services
docker compose restart

# Restart specific service
docker compose restart api

# Stop and remove containers
docker compose down

# Stop, remove containers, and volumes
docker compose down -v

Step 5: Execute Commands in Containers

# Run Prisma migrations
docker compose exec api npm run db:push

# Generate Prisma Client
docker compose exec api npm run prisma:generate

# Access container shell
docker compose exec api sh

# Run database seed
docker compose exec api npm run db:seed

# Access PostgreSQL
docker compose exec db psql -U postgres -d postgres

Step 6: Health Checks

# Check service health
docker compose ps

# Test API health endpoint
curl http://localhost:8900/health

# Test Nginx health endpoint
curl http://localhost:9080/health

# Check database connection
docker compose exec api npm run prisma:studio

Step 7: Network Configuration

# Create network (if not exists)
docker network create nodejs_backend_with_postgresql_network

# Verify network
docker network ls

# Inspect network
docker network inspect nodejs_backend_with_postgresql_network

🔄 Complete Project Workflow

Service Connection Architecture

┌─────────────────────────────────────────────────────────────────┐
│                    Docker Network                               │
│              (nodejs_backend_with_postgresql_network)           │
│                                                                 │
│  ┌──────────────┐                                               │
│  │   Client     │                                               │
│  │  (Browser)   │                                               │
│  └──────┬───────┘                                               │
│         │                                                       │
│         │ HTTP/HTTPS (Port 9080)                                │
│         ▼                                                       │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  Nginx Service                                           │   │
│  │  Container: nodejs-backend-nginx                         │   │
│  │  Port: 9080:80 (host:container)                          │   │
│  │  Config: ./nginx/nginx.conf                              │   │
│  └──────┬───────────────────────────────────────────────────┘   │
│         │                                                       │
│         │ Proxy to http://api:3000                              │
│         ▼                                                       │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  API Service (Node.js/Express)                           │   │
│  │  Container: nodejs-backend                               │   │
│  │  Port: 3000 (internal only)                              │   │
│  │  ┌────────────────────────────────────────────────────┐  │   │
│  │  │  Prisma Client                                     │  │   │
│  │  │  - Reads schema.prisma                             │  │   │
│  │  │  - Generates type-safe queries                     │  │   │
│  │  └──────┬─────────────────────────────────────────────┘  │   │
│  └─────────┼────────────────────────────────────────────────┘   │
│            │                                                    │
│            │ PostgreSQL Connection                              │
│            │ (postgresql://postgres:postgres@                   │
│            │  nodejs-backend-db:5432/postgres)                  │
│            ▼                                                    │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  PostgreSQL Service                                      │   │
│  │  Container: nodejs-backend-db                            │   │
│  │  Port: 5432 (internal only)                              │   │
│  │  Volume: postgres_data (persistent)                      │   │
│  │  ┌────────────────────────────────────────────────────┐  │   │
│  │  │  Database: postgres                                │  │   │
│  │  │  - User table                                      │  │   │
│  │  │  - Permission table                                │  │   │
│  │  │  - Group table                                     │  │   │
│  │  │  - ActivityLog table                               │  │   │
│  │  └────────────────────────────────────────────────────┘  │   │
│  └──────────────────────────────────────────────────────────┘   │
│            ▲                                                    │
│            │                                                    │
│            │ Redis Connection                                   │
│            │ (redis://nodejs-backend-redis:6379)                │
│            │                                                    │
│  ┌─────────┴──────────────────────────────────────────────┐     │
│  │  Redis Service                                         │     │
│  │  Container: nodejs-backend-redis                       │     │
│  │  Port: 6379 (internal only)                            │     │
│  │  ┌──────────────────────────────────────────────────┐  │     │
│  │  │  Cache Storage                                   │  │     │
│  │  │  - OTP cache                                     │  │     │
│  │  │  - Session storage                               │  │     │
│  │  │  - Token blacklist                               │  │     │
│  │  │  - Rate limiting data                            │  │     │
│  │  └──────────────────────────────────────────────────┘  │     │
│  └────────────────────────────────────────────────────────┘     │
│                                                                 │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  pgAdmin Service (Optional)                              │   │
│  │  Container: nodejs-backend-pgadmin                       │   │
│  │  Port: 5050:80 (host:container)                          │   │
│  │  ┌────────────────────────────────────────────────────┐  │   │
│  │  │  Web Interface                                     │  │   │
│  │  │  - Database management                             │  │   │
│  │  │  - Query execution                                 │  │   │
│  │  │  - Schema visualization                            │  │   │
│  │  └──────┬─────────────────────────────────────────────┘  │   │
│  │         │                                                │   │
│  │         │ PostgreSQL Connection                          │   │
│  │         │ (nodejs-backend-db:5432)                       │   │
│  │         ▼                                                │   │
│  │  ┌──────────────────────────────────────────────────┐    │   │
│  │  │  Connects to PostgreSQL Service                  │    │   │
│  │  └──────────────────────────────────────────────────┘    │   │
│  └──────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

Service Startup Order

1. Docker Network
   └─► nodejs_backend_with_postgresql_network

2. Database Services (Start First)
   ├─► PostgreSQL (nodejs-backend-db:5432)
   └─► Redis (nodejs-backend-redis:6379)

3. API Service (Waits for Database Health Checks)
   ├─► Connects to PostgreSQL via Prisma
   └─► Connects to Redis for Cache

4. Nginx Service (Waits for API Health Check)
   └─► Proxies to API (http://api:3000)

5. pgAdmin Service (Optional, Waits for Database)
   └─► Connects to PostgreSQL

Connection Flow

Client Request
    │
    ▼
Port 9080 (Host) ──► Nginx ──► Port 3000 ──► API Service
                                         │
                                         ├─► PostgreSQL (Port 5432)
                                         └─► Redis (Port 6379)

🔐 Authentication System

Multi-Token Architecture

The authentication system uses a three-token approach similar to Instagram:

Access Token (1 hour)
- Lightweight token for API authentication
- Minimal payload for fast validation
- Includes JTI (JWT ID) for efficient blacklisting
Session Token (7 days) - Recommended
- Contains full user profile and permissions
- Fastest validation (no database lookup needed)
- Preferred for frontend API calls
Refresh Token (30 days)
- Used to obtain new tokens when they expire
- Cannot be used for API authentication
- Rotates on each refresh for security

Complete Documentation: Authentication Router

Token Usage

Recommended Approach (Session Token):

// Store tokens after login
{
    "access_token": "...",
    "session_token": "...",  // RECOMMENDED for API calls
    "refresh_token": "...",
    "session_id": "..."
}

// Use session_token for API calls (fastest validation)
headers = {
    "X-Session-Token": session_token  // Preferred
    // OR
    "Authorization": `Bearer ${session_token}`  // Also works
}

📄 License & Open Source

This project is open source and free to use for all purposes with no restrictions.

🎯 Open Source License

This project is released under the MIT License, which means:

✅ Free to use for any purpose (commercial or personal)
✅ No restrictions on usage, modification, or distribution
✅ No warranty provided
✅ Attribution is appreciated but not required

📄 Full License Text: See LICENSE file for complete license terms and conditions.

🙏 Technologies & Acknowledgments

This project is built with amazing open-source technologies. Special thanks to:

Core Framework & Runtime

Node.js - JavaScript runtime built on Chrome's V8 engine
Express.js - Fast, unopinionated web framework for Node.js

Database & ORM

PostgreSQL - Advanced open-source relational database
Prisma - Next-generation ORM for Node.js and TypeScript
Redis - In-memory data structure store

Security & Authentication

JWT (jsonwebtoken) - JSON Web Token implementation
bcryptjs - Password hashing library
Helmet - Security middleware for Express

Monitoring & Logging

Winston - Logging library for Node.js
Sentry - Error tracking and performance monitoring
Morgan - HTTP request logger middleware

API Documentation

Swagger - API documentation and testing tools
swagger-jsdoc - Swagger specification generator
swagger-ui-express - Swagger UI for Express

Utilities & Middleware

CORS - Cross-Origin Resource Sharing middleware
compression - Response compression middleware
express-rate-limit - Rate limiting middleware
express-validator - Request validation middleware
Joi - Schema validation library

Communication Services

Nodemailer - Email sending library
Twilio - Cloud communications platform

Storage

Google Cloud Storage - Object storage service
Multer - File upload middleware

Process Management

PM2 - Production process manager for Node.js

Containerization

Docker - Containerization platform
Docker Compose - Multi-container Docker application tool

Web Server

Nginx - High-performance web server and reverse proxy

🌟 Contributing

Contributions are welcome! This is an open-source project, and we encourage:

🐛 Bug reports
💡 Feature requests
📝 Documentation improvements
🔧 Code contributions
⭐ Starring the repository

📞 Support

For issues, questions, or contributions, please visit:

Repository: https://github.com/fullstack-open-source/nodejs-backend-with-sql
Issues: Open an issue on GitHub

Made with ❤️ using open-source technologies

This project is free to use, modify, and distribute for any purpose without restrictions.

📜 License

This project is licensed under the MIT License - see the LICENSE file for details.

Uh oh!

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

🚀 Node.js Backend API

📋 Table of Contents

🚀 Features

🎯 Core Capabilities

🔧 Technical Features

🔐 Authentication Features

🗄️ Database Support & Flexibility

Currently Configured Database

Prisma Supported Databases

Advantages of Using Prisma

Switching Databases

🏗️ Architecture

System Architecture

Authentication Architecture

Request Flow

Middleware Stack

Database Architecture

Module Structure

📚 API Documentation

Authentication & User Management

Permissions & Groups

Dashboard & Analytics

File Upload

Activity Logging

Health Monitoring

📦 Installation & Setup

Prerequisites

Quick Start

Step 1: Clone Repository

Step 2: Setup Environment

Step 2.5: Setup Google Cloud Storage Credentials (Optional)

Step 3: Start Services with Docker Compose

Step 4: Setup Database Schema

Step 5: Access Services

API Route Prefix (MODE)

Development Setup

Step 1: System Preparation

Step 2: Project Setup

Step 3: Environment Configuration

Step 3.5: Setup Google Cloud Storage Credentials (Optional)

Step 4: Database Setup (Local PostgreSQL)

Step 5: Prisma Setup

Step 6: Seed Database (Optional)

Step 7: Start Development Server

Step 8: Access Application

Production Setup

Step 1: Server Preparation

Step 2: Clone and Setup Project

Step 2.5: Setup Google Cloud Storage Credentials (Required for File Upload)

Step 3: Deploy with Docker (Recommended)

Step 4: Or Deploy with PM2

Step 5: Configure External Proxy (Nginx)

Docker Setup

Step 1: Prerequisites

Step 2: Docker Compose Services

Step 3: Build and Start Services

Step 4: Service Management

Step 5: Execute Commands in Containers

Step 6: Health Checks

Step 7: Network Configuration

🔄 Complete Project Workflow

Service Connection Architecture

Service Startup Order

Connection Flow

🔐 Authentication System

Multi-Token Architecture

Token Usage

📄 License & Open Source

🎯 Open Source License

🙏 Technologies & Acknowledgments

Core Framework & Runtime

Database & ORM

Security & Authentication