NestJS CRUD with Database

Real-world analogy

What is it?

A complete CRUD API combines controllers (HTTP handling), services (business logic), DTOs (input validation), and database operations (Prisma/TypeORM) into a RESTful interface. Each endpoint maps to a database operation: POST→create, GET→read, PATCH→update, DELETE→delete.

Real-world relevance

Every SaaS product is built on CRUD. Shopify's product API, Notion's page API, GitHub's repository API — they all follow the same CRUD pattern with pagination, filtering, and validation. This is the bread and butter of backend development.

Key points

• What is CRUD? — Create, Read, Update, Delete — the four fundamental operations for any data-driven app. Every app you use does CRUD: Instagram creates posts, reads feeds, updates profiles, deletes comments. Mastering CRUD means you can build almost any app.
• Project Structure — Each feature gets its own folder: users/users.module.ts, users/users.controller.ts, users/users.service.ts, users/dto/create-user.dto.ts, users/dto/update-user.dto.ts. `nest g resource users` generates all of this in one command!
• Create — POST /items — Accept a validated DTO, pass to service, insert into database, return the created item with 201 status. Always validate input with DTOs before creating. Return the full created object so the client has the ID and timestamps.
• Read — GET /items & GET /items/:id — findAll() returns a paginated list with metadata (total, page, pages). findOne() returns a single item or throws NotFoundException. Add query params for filtering (search, status), sorting (sortBy, order), and pagination (page, limit).
• Update — PATCH /items/:id — Use PATCH (partial update) over PUT (full replace). Accept UpdateDto (PartialType of CreateDto) so clients only send changed fields. Verify the item exists first — update a non-existent item should throw 404, not create a new one.
• Delete — DELETE /items/:id — Soft delete vs hard delete: soft delete sets a deletedAt timestamp (recoverable), hard delete removes the row permanently. Return 204 No Content on success. Always verify the item exists before deleting.
• Pagination Pattern — Return `{ data: items[], meta: { total, page, limit, pages } }`. Calculate skip = (page - 1) * limit. Use default values for page (1) and limit (10). Set a max limit (100) to prevent clients from requesting millions of rows.
• Putting It All Together — Module imports PrismaModule, registers controller + service. Controller handles HTTP with decorators. Service contains all database logic. DTOs validate input. This pattern repeats for every feature in your app — learn it once, use it forever.

Code example

// products.dto.ts — Input validation
import {
  IsString, IsNumber, IsOptional,
  MinLength, Min, MaxLength, IsEnum,
} from 'class-validator';
import { PartialType } from '@nestjs/mapped-types';

enum ProductStatus { ACTIVE = 'active', DRAFT = 'draft' }

export class CreateProductDto {
  @IsString()
  @MinLength(3)
  name: string;

  @IsNumber()
  @Min(0)
  price: number;

  @IsOptional()
  @IsString()
  @MaxLength(500)
  description?: string;

  @IsOptional()
  @IsEnum(ProductStatus)
  status?: ProductStatus;
}

export class UpdateProductDto extends PartialType(CreateProductDto) {}

// products.service.ts — Business logic + DB
import { Injectable, NotFoundException } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';

@Injectable()
export class ProductsService {
  constructor(private prisma: PrismaService) {}

  // CREATE
  async create(dto: CreateProductDto) {
    return this.prisma.product.create({ data: dto });
  }

  // READ (all, paginated)
  async findAll(params: {
    page: number; limit: number; search?: string;
  }) {
    const { page, limit, search } = params;
    const skip = (page - 1) * limit;
    const where = search
      ? { name: { contains: search, mode: 'insensitive' as const } }
      : {};

    const [data, total] = await Promise.all([
      this.prisma.product.findMany({
        where, skip, take: limit,
        orderBy: { createdAt: 'desc' },
      }),
      this.prisma.product.count({ where }),
    ]);

    return {
      data,
      meta: {
        total,
        page,
        limit,
        pages: Math.ceil(total / limit),
      },
    };
  }

  // READ (one)
  async findOne(id: number) {
    const product = await this.prisma.product.findUnique({
      where: { id },
    });
    if (!product) {
      throw new NotFoundException(`Product #${id} not found`);
    }
    return product;
  }

  // UPDATE
  async update(id: number, dto: UpdateProductDto) {
    await this.findOne(id); // Throws 404 if not found
    return this.prisma.product.update({
      where: { id }, data: dto,
    });
  }

  // DELETE
  async remove(id: number) {
    await this.findOne(id); // Throws 404 if not found
    return this.prisma.product.delete({ where: { id } });
  }
}

// products.controller.ts — HTTP layer
import {
  Controller, Get, Post, Patch, Delete,
  Param, Query, Body, HttpCode,
  ParseIntPipe, DefaultValuePipe,
} from '@nestjs/common';

@Controller('products')
export class ProductsController {
  constructor(private productsService: ProductsService) {}

  @Post()
  create(@Body() dto: CreateProductDto) {
    return this.productsService.create(dto);
  }

  @Get()
  findAll(
    @Query('page', new DefaultValuePipe(1), ParseIntPipe) page: number,
    @Query('limit', new DefaultValuePipe(10), ParseIntPipe) limit: number,
    @Query('search') search?: string,
  ) {
    return this.productsService.findAll({ page, limit, search });
  }

  @Get(':id')
  findOne(@Param('id', ParseIntPipe) id: number) {
    return this.productsService.findOne(id);
  }

  @Patch(':id')
  update(
    @Param('id', ParseIntPipe) id: number,
    @Body() dto: UpdateProductDto,
  ) {
    return this.productsService.update(id, dto);
  }

  @Delete(':id')
  @HttpCode(204)
  remove(@Param('id', ParseIntPipe) id: number) {
    return this.productsService.remove(id);
  }
}

Line-by-line walkthrough

1. 1. products.dto.ts — Input validation
2. 2. Import validation decorators
3. 3.
4. 4.
5. 5.
6. 6. Import PartialType utility
7. 7.
8. 8. Define allowed product statuses
9. 9.
10. 10. DTO for creating a product
11. 11. Name must be a string
12. 12. At least 3 characters
13. 13. Property declaration
14. 14.
15. 15. Price must be a number
16. 16. Cannot be negative
17. 17. Property declaration
18. 18.
19. 19. Description is optional
20. 20. Must be a string if provided
21. 21. Maximum 500 characters
22. 22. Optional property declaration
23. 23.
24. 24. Status is optional
25. 25. Must be a valid enum value
26. 26. Optional property declaration
27. 27. Close DTO class
28. 28.
29. 29. UpdateDto — all fields become optional
30. 30.
31. 31. products.service.ts — Business logic + DB
32. 32. Import Injectable and error class
33. 33. Import Prisma for database access
34. 34.
35. 35. Mark as injectable for DI
36. 36. Export the service class
37. 37. Inject PrismaService via constructor
38. 38.
39. 39. CREATE operation
40. 40. Insert DTO data into database
41. 41. Close method
42. 42.
43. 43. READ all with pagination
44. 44. Accept page, limit, and optional search
45. 45.
46. 46. Destructure parameters
47. 47. Calculate records to skip
48. 48. Build where clause for search
49. 49. Case-insensitive name search
50. 50. Empty where if no search
51. 51.
52. 52. Run both queries in parallel
53. 53. Fetch paginated products
54. 54. Apply where filter, skip, and limit
55. 55. Sort by newest first
56. 56. Close findMany
57. 57. Count total matching products
58. 58. Close Promise.all
59. 59.
60. 60. Return structured response
61. 61. The product records
62. 62. Pagination metadata
63. 63. Total matching records
64. 64. Current page
65. 65. Items per page
66. 66. Total number of pages
67. 67. Close meta
68. 68. Close return
69. 69. Close method
70. 70.
71. 71. READ one by ID
72. 72. Query database for single product
73. 73. Filter by ID
74. 74. Close query
75. 75. If not found...
76. 76. Throw 404 error
77. 77. Close if
78. 78. Return the product
79. 79. Close method
80. 80.
81. 81. UPDATE operation
82. 82. Check if product exists (throws 404)
83. 83. Update in database
84. 84. Filter by ID and set new data
85. 85. Close update
86. 86. Close method
87. 87.
88. 88. DELETE operation
89. 89. Check if product exists (throws 404)
90. 90. Delete from database
91. 91. Close method
92. 92. Close service class
93. 93.
94. 94. products.controller.ts — HTTP layer
95. 95. Import NestJS decorators
96. 96.
97. 97.
98. 98.
99. 99.
100. 100. Controller with /products base route
101. 101. Export the controller class
102. 102. Inject the service
103. 103.
104. 104. POST /products — create
105. 105. Validate body against CreateProductDto
106. 106. Delegate to service
107. 107. Close method
108. 108.
109. 109. GET /products — list all
110. 110. Method signature
111. 111. Page query param with default 1
112. 112. Limit query param with default 10
113. 113. Optional search query param
114. 114. Close parameter list
115. 115. Delegate to service with params
116. 116. Close method
117. 117.
118. 118. GET /products/:id — find one
119. 119. Extract and validate ID as integer
120. 120. Delegate to service
121. 121. Close method
122. 122.
123. 123. PATCH /products/:id — update
124. 124. Method signature
125. 125. Extract and validate ID
126. 126. Validate body against UpdateProductDto
127. 127. Close parameter list
128. 128. Delegate to service
129. 129. Close method
130. 130.
131. 131. DELETE /products/:id — remove
132. 132. Return 204 No Content
133. 133. Extract and validate ID
134. 134. Delegate to service
135. 135. Close method
136. 136. Close controller class

Spot the bug

@Controller('products')
export class ProductsController {
  constructor(private productsService: ProductsService) {}

  @Patch(':id')
  async update(@Param('id') id: string, @Body() dto: UpdateProductDto) {
    return this.productsService.update(id, dto);
  }

  @Delete(':id')
  async remove(@Param('id') id: string) {
    this.productsService.remove(id);
  }
}

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• NestJS CRUD Generator (NestJS Official)
• Prisma CRUD Operations (Prisma)
• REST API Design Best Practices (RESTful API)