Define a NestJS Drizzle CRUD service

A NestJS Drizzle CRUD service is an empty subclass of SqlBaseCrudService. forFeature injects the db and table, and you add custom methods.

A NestJS Drizzle CRUD service is a class that extends SqlBaseCrudService and declares four generics: the row type, the create DTO, the update DTO and the filter type. The class body is usually empty. DrizzleCrudModule.forFeature builds it with the Drizzle instance, the table, the dialect and the per-entity config, so every CRUD method works without code in the service.

No constructor, no @Injectable()

forFeature registers the service with a factory that calls new UsersService(config). A constructor with other parameters breaks that call. Leave the constructor out and reach the database through this.config.db.

How do I define a CRUD service?

Describe the table in Drizzle

db/schema.ts
import { integer, pgTable, serial, timestamp, varchar } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  created_at: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updated_at: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  deleted_at: timestamp('deleted_at', { withTimezone: true }),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 200 }).notNull(),
  author_id: integer('author_id').notNull().references(() => users.id),
});

export type User = typeof users.$inferSelect;

The keys created_at, updated_at and deleted_at match the package defaults for timestamps and soft delete, so users needs no extra config.

Extend SqlBaseCrudService

users/users.dto.ts
export interface CreateUserDto { name: string; email: string }
export interface UpdateUserDto { name?: string; email?: string }
users/users.service.ts
import { type CrudFilter, SqlBaseCrudService } from '@avytrix/nestjs-drizzle-crud';
import type { User } from '../db/schema';
import type { CreateUserDto, UpdateUserDto } from './users.dto';

export class UsersService extends SqlBaseCrudService<
  User,
  CreateUserDto,
  UpdateUserDto,
  CrudFilter
> {}

This is the same shape as CountriesService in the example app (apps/api/src/countries/countries.service.ts).

Register it with forFeature

users/users.module.ts
import { Module } from '@nestjs/common';
import { DrizzleCrudModule } from '@avytrix/nestjs-drizzle-crud';
import { users } from '../db/schema';
import { UsersController } from './users.controller';
import { UsersService } from './users.service';

@Module({
  imports: [DrizzleCrudModule.forFeature([{ service: UsersService, table: users }])],
  controllers: [UsersController],
})
export class UsersModule {}

forFeature needs DrizzleCrudModule.forRoot (or forRootAsync) in your root module. Per-entity settings go in the optional config object; forFeature lists them all.

Inject it like any provider

users/users.controller.ts
import { Controller, Get, Param, ParseIntPipe } from '@nestjs/common';
import { UsersService } from './users.service';

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

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

The provider token is the class itself, so constructor injection works as usual.

What do the four generics mean?

All four are required: SqlBaseCrudService declares no defaults for them. Pass CrudFilter as the filter type unless you want a narrower one.

TRequired

Record<string, any>

The row type the methods return, usually typeof table.$inferSelect.
CreateDtoRequired

type

The body create(), massCreate() and upsert() accept.
UpdateDtoRequired

type

The body update() and massUpdate() accept.
FilterDtoRequired

type

The type of the filters argument of findAll() and count(). CrudFilter allows every operator.

FilterDto is only a type. It does not build a query, and it does not stop a key at runtime. Use the filterableColumns allowlist for that, and see Filtering for the filter syntax.

How do I add custom methods?

Add them to the class. Inside a method you have every CRUD method on this, the resolved config on this.config, and the Drizzle instance on this.config.db.

users/users.service.ts
import { eq, isNull, sql } from 'drizzle-orm';
import { type CrudFilter, SqlBaseCrudService } from '@avytrix/nestjs-drizzle-crud';
import { posts, type User, users } from '../db/schema';
import type { CreateUserDto, UpdateUserDto } from './users.dto';

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

  newest(limit = 10) {
    return this.findAll({}, { limit, sortBy: '-created_at' });
  }

  // Raw Drizzle for a query the CRUD methods do not cover.
  withPostCount() {
    return this.config.db
      .select({
        id: users.id,
        name: users.name,
        postCount: sql<number>`count(${posts.id})`.as('post_count'),
      })
      .from(users)
      .leftJoin(posts, eq(posts.author_id, users.id))
      .where(isNull(users.deleted_at))
      .groupBy(users.id);
  }
}

A raw Drizzle query skips everything the CRUD methods add: the soft delete predicate, the tenant scope, the allowlists and the hooks. That is why withPostCount filters deleted_at itself.

Which methods can I override?

The protected hooks are there to be overridden. Each has a no-op or pass-through default, so override only the ones you need.

HookRuns
validateCreate(data), validateUpdate(id, data)Before the write, to reject bad input
beforeCreate(data), beforeUpdate(id, data)Before the write; return the data to write
mapCreateDtoToEntity(data), mapUpdateDtoToEntity(data)To turn a DTO into column values
afterCreate(entity), afterUpdate(entity)After the write
beforeDelete(id), afterDelete(id)Around a hard delete
beforeSoftDelete(id), afterSoftDelete(id)Around a soft delete
beforeRestore(id), afterRestore(entity)Around a restore

See Hooks and validation for the order they run in and how to skip them.

Why not inject the database in a constructor?

Because the module already did. This is the pattern to avoid:

users.service.ts (do not do this)
export class UsersService extends SqlBaseCrudService<User, CreateUserDto, UpdateUserDto, CrudFilter> {
  constructor(@Inject(DRIZZLE_DB) private readonly db: any) {
    super({ /* the table and config do not belong here */ });
  }
}

forFeature passes one argument, the merged config, to the constructor. A constructor that expects something else gets the config in the wrong parameter, and the service never sees its table or settings. If a custom method needs Drizzle, this.config.db is the same instance DRIZZLE_DB holds.

The same goes for @Injectable(): forFeature already registers the class as a provider, so leave the decorator off.

Do I need to open the connection myself?

No. DrizzleCrudModule owns the connection. It uses the db you pass to forRoot as is, or builds a PostgreSQL connection from connectionString and closes it when the module is destroyed. You do not need OnModuleInit to warm anything up.

When is the empty subclass not enough?

  • A query across several tables. Write a plain Nest service that injects DRIZZLE_DB and uses Drizzle directly, rather than bending a CRUD service around it.
  • Very large result sets. findAll loads a page into memory. Walk the table with cursor pagination, or inject DRIZZLE_DB and use your driver's streaming.
  • GraphQL. The service methods are plain async functions, so a resolver calls them the same way a controller does.

Common questions

Next steps

Last updated View change history

On this page