CRUD operations with Drizzle in NestJS

For Drizzle ORM NestJS CRUD, every service that extends SqlBaseCrudService inherits the same methods: find, findOne, findAll, findByCursor, count and exists to read; create, update and upsert to write; softDelete, restore, delete and forceDelete to remove; and mass* versions that write a list in one transaction. Each method is typed against your row and DTO types and throws NestJS HTTP exceptions, so a controller can return its result directly.

Which methods does a service have?Copy Anchor Link

These are the public methods of SqlBaseCrudService<T, CreateDto, UpdateDto, FilterDto>. T is the row type, and every options argument is the SqlOperationOptions object listed in the options table.

ReadsCopy Anchor Link

WritesCopy Anchor Link

Mass writesCopy Anchor Link

Every mass write runs in one transaction and writes all rows or none.

Duplicate ids in a mass update, delete or restore are written once. Pass bulkMode: "perItem" to run each item through the single-row method in its own savepoint instead. Bulk operations covers both modes.

HelpersCopy Anchor Link

executeSqlTransaction is protected: call it from a method on your service, not from a controller. See Transactions.

How do I use them in a service and a controller?Copy Anchor Link

Call the inherited methods directly, and add your own methods to the service when a route needs more than one call. This service adds two methods on top of the inherited ones:

import { type CrudFilter, SqlBaseCrudService } from "@avytrix/nestjs-drizzle-crud";
import type { users } from "../db/schema";
import type { CreateUserDto, UpdateUserDto } from "./users.dto";

export type User = typeof users.$inferSelect;

export class UsersService extends SqlBaseCrudService<
  User,
  CreateUserDto,
  UpdateUserDto,
  CrudFilter
> {
  findByEmail(email: string) {
    return this.findOne({ email });
  }

  countCreatedSince(since: Date) {
    return this.count({ created_at: { gte: since } });
  }
}

import {
  Body,
  Controller,
  Delete,
  Get,
  HttpCode,
  NotFoundException,
  Param,
  ParseIntPipe,
  Patch,
  Post,
  Query,
} from "@nestjs/common";
import { CreateUserDto, UpdateUserDto } from "./users.dto";
import { UsersService } from "./users.service";

@Controller("users")
export class UsersController {
  constructor(private readonly users: UsersService) {}

  @Get()
  list(@Query() query: Record<string, unknown>) {
    const q = this.users.parseQuery(query);
    return this.users.findAll(q.filters, q.pagination, q.options);
  }

  @Get(":id")
  async get(@Param("id", ParseIntPipe) id: number) {
    const user = await this.users.find(id, { relations: ["posts"] });
    if (!user) throw new NotFoundException(`User ${id} not found`);
    return user;
  }

  @Post()
  create(@Body() dto: CreateUserDto) {
    return this.users.create(dto);
  }

  @Post("bulk")
  createMany(@Body() dtos: CreateUserDto[]) {
    return this.users.massCreate(dtos);
  }

  @Patch(":id")
  update(@Param("id", ParseIntPipe) id: number, @Body() dto: UpdateUserDto) {
    return this.users.update(id, dto);
  }

  @Delete(":id")
  remove(@Param("id", ParseIntPipe) id: number) {
    return this.users.softDelete(id);
  }

  @Post(":id/restore")
  @HttpCode(200)
  restore(@Param("id", ParseIntPipe) id: number) {
    return this.users.restore(id);
  }
}

relations: ["posts"] only works once the entity declares a posts relation; see Relations. Drop it if yours has none.

What does findAll return?Copy Anchor Link

findAll returns one page and the total count in a { data, total, page, limit } envelope. It runs the data query and the COUNT(*) query together, so a list costs two queries, plus one batched query per eager loaded relation that is not a joined belongs-to.

const page = await users.findAll(
  { name: { iContains: "ali" } },
  { page: 2, limit: 10, sortBy: ["-created_at", "name"] },
);
// { data: User[], total: 37, page: 2, limit: 10 }

The pagination argument takes these keys:

With no sortBy, rows follow the entity's defaultSort, and the primary key is always added last so pages never overlap. Note the sortOrder default: sortBy: "name" on its own sorts descending. Pagination and sorting covers every rule, and Filtering covers the filters object.

Which exceptions does each method throw?Copy Anchor Link

Each exception extends a NestJS HTTP exception, so the default exception filter returns the right status without any setup. They come from src/exceptions/crud.exceptions.ts.

softDelete, restore, massSoftDelete and massRestore on an entity with soft delete off, and fullTextSearch on MySQL (or search.mode: "fullText" there), are a 400 BadRequestException. Errors shows the response bodies and how to write a custom filter.

How do delete, softDelete and forceDelete differ?Copy Anchor Link

softDelete hides a row by setting its deleted_at column, delete removes a live row, and forceDelete removes a row whether it is soft deleted or not.

Reads skip soft deleted rows by default. Pass withDeleted: true to include them, or onlyDeleted: true for a trash view. Soft delete covers the column setting and the trash routes.

Which options does every method take?Copy Anchor Link

The last argument of every method is SqlOperationOptions. A method ignores the keys that do not apply to it.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link