Database Documentation

This documentation explains the features and usage of Database Module: Located at src/common/database

Overview

This documentation explains the database architecture and features in Complete NestJS Boilerplate:

Related Documents

• Installation Documentation - For complete project setup and dependencies
• Environment Documentation - For database connection and environment variables
• Configuration Documentation - For understanding the config module structure

Table of Contents

• Overview
• Related Documents
• Prerequisites
• Migration
• Generate Database Client
• Seeding
  • Database Seeds
  • Template Seeds
  • AWS S3 Configuration Seed
• Initial Seeded Data
  • API Keys
  • Roles
  • Users
  • Feature Flags
  • Term Policies
• Composite Types
  • GeoLocation
  • UserAgent
  • UserTermPolicy
  • UserPhoto
  • RoleAbility
  • TermPolicyContent
• Docker
• Database Tools
  • Prisma ORM
  • Why Prisma for Repository Design Pattern?
  • Change DB with Minimal Effort

Prerequisites

💡 Tip: Use Docker setup from the installation guide for automatic MongoDB replica set configuration.

MongoDB 8.0.x running as a replica set (required for transactions)

Migration

Prisma does not support migrations for MongoDB. Instead, use prisma db push to sync your Prisma schema with the MongoDB database.

In Complete NestJS Boilerplate, you can use the pnpm db:migrate script to quickly sync your schema to MongoDB.

For details, see the official Prisma documentation: Prisma for MongoDB 

Generate Database Client

Prisma uses a generated client to provide type-safe database access and query building. You must generate the Prisma Client every time you change your Prisma schema (prisma/schema.prisma).

When to Generate Prisma client?

• After any change to your Prisma schema (e.g., adding, removing, or updating models/fields).
• After pulling schema changes from version control.

How to Generate Prisma Client:

pnpm db:generate

This command will read your Prisma schema and generate the client code in generated/prisma-client. The generated client is required for your application to interact with the database using Prisma.

Seeding

Seeding in Complete NestJS Boilerplate is handled using Commander.js . All seed commands are implemented in src/migration/seeds/*.

Database Seeds

Complete NestJS Boilerplate provides ready-to-use seed scripts to help you quickly initialize or remove data for development and testing. Database seeding is used to populate the database with initial or test data, making development and testing easier.

Seed Data Location:

• All seed data is stored in src/migration/data/*.

How to Run All Seeds:

• pnpm migration:seed — runs all seed commands to populate initial data.
• pnpm migration:remove — removes all seeded data from the database.
• pnpm migration:fresh — force-resets the database schema (prisma db push --force-reset) then immediately re-seeds all data. Useful during development when you need a clean slate.

How to Seed/Remove a Specific Module: Run the command:

• Seed: pnpm migration {module} --type seed
• Remove: pnpm migration {module} --type remove

Available Types:

• seed (add data)
• remove (delete data)

Available Modules:

• apiKey: Inserts default and system API keys for authentication and service access.
• country: Inserts country data (name, codes, phone code, continent, timezone).
• featureFlag: Inserts feature flags to enable/disable features (e.g., login methods, sign up, change password).
• role: Inserts user roles (superadmin, admin, user) with abilities and permissions.
• termPolicy: Inserts term policy documents (cookies, marketing, privacy, terms of service) with version and content.
• user: Inserts initial user accounts (Super Admin, Admin, User) with country, role, and credentials.

Template Seeds

Template seeding uses the same script and commands as Database Seeds, but is specifically for template files like email and term policies.

Available Types:

• seed (add template data)
• remove (delete template data)

Email Templates

Every time you run the email template seed, the templates will be inserted into AWS SES automatically.

How to Run Email Template Seeds:

• Seed: pnpm migration template-email-notification --type seed
• Remove: pnpm migration template-email-notification --type remove

Term Policy Templates

Every time you run the term policy template seed, the policy documents will be linked to the database records automatically.

How to Run Term Policy Template Seeds:

• Seed: pnpm migration template-termPolicy --type seed
• Remove: pnpm migration template-termPolicy --type remove (no-op — term policy removal is intentionally skipped)

AWS S3 Configuration Seed

The migration script is a special seed command that configures AWS S3 bucket policies and settings for both public and private buckets. Unlike other seed commands, this migration doesn’t populate database data but instead configures your AWS infrastructure.

What It Does:

This script automatically configures essential S3 bucket settings in the correct order:

1. Block Public Access Configuration - Controls public access restrictions
2. Disable ACL Configuration - Enforces bucket owner ownership controls
3. Bucket Policy - Sets read/write permissions based on bucket accessibility
4. CORS Configuration - Configures Cross-Origin Resource Sharing rules
5. Lifecycle Configuration - Automatically deletes incomplete multipart uploads

Why Sequential Configuration Matters:

The configuration must be applied in a specific order because AWS S3 policies have dependencies. For example, you must configure public access blocks before setting bucket policies.

How to Run:

# Configure both public and private buckets
pnpm migration aws-s3-config --type seed

Important Notes:

• This migration runs configurations for both public and private buckets simultaneously
• The --type remove option is intentionally skipped (no removal operation)
• Requires valid AWS credentials and appropriate IAM permissions
• Bucket names and ARNs must be properly configured in your environment variables

Configuration Applied:

For Public Buckets:

• Public read access (s3:GetObject) for all objects
• Full IAM user access for management operations
• CORS rules allowing GET/HEAD from any origin
• CORS rules allowing PUT/POST/DELETE from whitelisted origins

For Private Buckets:

• Blocks all public access
• CORS rules only allow whitelisted origins for all methods
• Full IAM user access required for all operations

Initial Seeded Data

When you run pnpm migration:seed, the following initial data will be created in your database. This data is essential for testing and development purposes.

API Keys

⚠️ These are development keys. Always regenerate API keys for production environments.

Two API keys are created for authentication and service access:

API Key Prefix Convention:

All generated API keys automatically include an environment prefix to help identify which environment they belong to. The format is:

{environment}_{random_string}

Examples:

• local_abc123xyz - API key for local/development environment
• development_def456uvw - API key for development environment
• staging_ghi789rst - API key for staging environment
• production_jkl012mno - API key for production environment

This prefix is automatically added based on the APP_ENV environment variable when creating new API keys, ensuring easy identification and preventing accidental cross-environment usage.

Roles

Three user roles are created with different permission levels:

Admin Role Abilities: The admin role has full CRUD permissions (create, read, update, delete) on all policy subjects defined in the system.

Users

⚠️ These are test accounts with default passwords. Change or remove these accounts in production environments.

The seeded users differ per environment. This is controlled by migrationUserData in src/migration/data/migration.user.data.ts:

User accounts:

Feature Flags

Five feature flags are created to control authentication and user features:

All features are enabled by default with 100% rollout for development convenience.

Term Policies

Four term policy documents are created:

The actual content for these policies is stored as file references in src/migration/data/term-policy/*. The files are not automatically linked to the database records. You must run the term policy migration script to link the files and update the content keys in the database.

For more details on how seeding works, see: Template Seeds

Composite Types

Prisma composite types are embedded sub-documents in MongoDB (not separate collections). They are defined with the type keyword in prisma/schema.prisma and stored inline within the parent document rather than in separate collections.

GeoLocation

Represents the geographic location derived from a client’s IP address using geoip-lite.

type GeoLocation {
  latitude  Float
  longitude Float
  country   String
  region    String
  city      String
}

Used in:

• Session.geoLocation — location at login time
• ActivityLog.geoLocation — location when the action was performed

Resolved automatically via the @RequestGeoLocation() parameter decorator. See Security and Middleware Documentation for details.

UserAgent

Represents parsed user-agent information from the client’s User-Agent HTTP header using ua-parser-js. UserAgent is the top-level type that embeds four sub-types.

type UserAgent {
  ua      String?
  browser UserAgentBrowser?
  cpu     UserAgentCpu?
  device  UserAgentDevice?
  engine  UserAgentEngine?
  os      UserAgentOs?
}
 
type UserAgentBrowser {
  name    String?
  version String?
  major   String?
  type    String?
}
 
type UserAgentCpu {
  architecture String?
}
 
type UserAgentDevice {
  type   String?
  vendor String?
  model  String?
}
 
type UserAgentEngine {
  name    String?
  version String?
}
 
type UserAgentOs {
  name    String?
  version String?
}

UserAgent fields:

Used in:

• Session.userAgent — client info at login time
• ActivityLog.userAgent — client info when the action was performed

Resolved automatically via the @RequestUserAgent() parameter decorator. See Security and Middleware Documentation for details.

UserTermPolicy

Represents the user’s acceptance flags for each term policy type. Stored inline on the User document.

type UserTermPolicy {
  termsOfService Boolean
  privacy        Boolean
  marketing      Boolean
  cookies        Boolean
}

Used in:

• User.termPolicy

UserPhoto

Represents the user’s profile photo stored in AWS S3.

type UserPhoto {
  bucket       String
  key          String
  cdnUrl       String?
  completedUrl String
  mime         String
  extension    String
  access       String
}

Used in:

• User.photo

RoleAbility

Represents a single CASL ability entry embedded in a Role. Each entry defines which actions are allowed on a given policy subject.

type RoleAbility {
  action  String[]
  subject String
}

Used in:

• Role.abilities

See Authorization Documentation for how abilities are evaluated at runtime.

TermPolicyContent

Represents a localized content file for a term policy document, stored in AWS S3.

type TermPolicyContent {
  language     String
  bucket       String
  key          String
  cdnUrl       String?
  completedUrl String
  mime         String
  extension    String
  access       String
  size         Int
}

Used in:

• TermPolicy.contents

Docker

Running database commands inside Docker containers from your host machine:

Generate Prisma Client inside container:

docker-compose exec apis pnpm db:generate

Run database migration inside container:

docker-compose exec apis pnpm db:migrate

Run all seeds inside container:

docker-compose exec apis pnpm migration:seed

Remove all seeded data inside container:

docker-compose exec apis pnpm migration:remove

These commands execute directly in the running Docker container without needing to enter the container shell. Ensure Docker Compose is running with docker-compose up -d before executing these commands.

Database Tools

Prisma ORM

Complete NestJS Boilerplate uses [Prisma][ref-prisma] v6.19.x as the primary database toolkit. Prisma is not just an ORM - it’s a complete database toolkit that provides the foundation for implementing clean architecture patterns.

Why Prisma for Repository Design Pattern?

Prisma perfectly enables Repository Design Pattern implementation:

• Type-Safe Repository Layer: Auto-generated TypeScript types ensure compile-time validation throughout repositories
• Clean Architecture: PrismaClient provides foundation for clean separation between database and business logic
• Easy Implementation: Consistent query API and transaction support simplify repository development
• Database Agnostic: Switch between MongoDB, PostgreSQL without changing repository code

Change DB with Minimal Effort

Prisma, combined with the Repository Pattern, allows you to switch databases with minimal effort and maximum codebase stability. The data access layer is fully abstracted, so your service and business logic remain unchanged regardless of the underlying database engine.

Supported Databases

Other supported databases: MySQL, SQLite, SQL Server, CockroachDB

Quick Migration: MongoDB → PostgreSQL

1. Update Prisma Schema (prisma/schema.prisma):

// Change provider
datasource db {
  provider = "postgresql"  // was: "mongodb"
  url      = env("DATABASE_URL")
}
 
// Update ID fields in all models
model User {
  id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid  // was: @default(auto()) @map("_id") @db.ObjectId
  // Replace @db.ObjectId with @db.Uuid from all foreign keys
}

2. Update Environment (.env):

# From:
DATABASE_URL=mongodb://localhost:27017/CompleteNestJs?replicaSet=rs0
 
# To:
DATABASE_URL=postgresql://user:password@localhost:5432/CompleteNestJs

3. Generate Migration & Client:

pnpm prisma migrate dev --name init  # PostgreSQL
pnpm db:generate                      # Regenerate client

4. Update DatabaseService Code:

• DatabaseService (src/common/database/services/database.service.ts) - May require updates for connection management, health checks, and database-specific features
• DatabaseUtil (src/common/database/utils/database.util.ts) - Replace MongoDB ObjectId helpers with UUID validators

5. Re-seed Database:

pnpm migration:seed

Learn More

• Prisma: Switching Databases 
• Prisma MongoDB Documentation 
• PostgreSQL Documentation