Drizzle ORM

import { Injectable } from '@nestjs/common';
import { Filterable, FilterFor } from '@dudousxd/nestjs-filter';
import { DrizzleFilter } from '@dudousxd/nestjs-filter-drizzle';
import { eq, gte } from 'drizzle-orm';
import { posts, users } from './schema.js';

@Injectable()
@Filterable({ entity: users }) // the table object is the entity
export class UserFilter extends DrizzleFilter<typeof users> {
  static readonly sort = ['name', 'age', 'posts.$count'];
  static readonly search = ['name', 'email'];
  static readonly includes = ['posts'];

  @FilterFor('minAge')
  applyMinAge(value: number) {
    this.$query.where(gte(users.age, value));
  }

  @FilterFor('name')
  applyName(value: string) {
    this.whereILike('name', value);
  }

  @FilterFor('hasDrafts')
  applyHasDrafts() {
    this.$query.whereHas('posts', (p) => eq((p as typeof posts).status, 'draft'));
  }
}

pnpm add @dudousxd/nestjs-filter @dudousxd/nestjs-filter-drizzle drizzle-orm

// src/schema.ts
import { relations } from 'drizzle-orm';
import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull(),
  age: integer('age').notNull(),
  managerId: integer('manager_id'),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  status: text('status').notNull(),
  authorId: integer('author_id').notNull(),
});

export const usersRelations = relations(users, ({ one, many }) => ({
  manager: one(users, { fields: [users.managerId], references: [users.id], relationName: 'manager' }),
  reports: many(users, { relationName: 'manager' }),
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

export const schema = { users, posts, usersRelations, postsRelations };

// src/database.module.ts
import { Global, Module } from '@nestjs/common';
import { drizzle } from 'drizzle-orm/node-postgres';
import { schema } from './schema.js';

export const DRIZZLE = Symbol('DRIZZLE');

@Global()
@Module({
  providers: [{ provide: DRIZZLE, useFactory: () => drizzle(process.env.DATABASE_URL!, { schema }) }],
  exports: [DRIZZLE],
})
export class DatabaseModule {}

// src/app.module.ts
import { Module } from '@nestjs/common';
import { FilterModule } from '@dudousxd/nestjs-filter';
import { drizzleAdapter } from '@dudousxd/nestjs-filter-drizzle';
import { DRIZZLE, DatabaseModule } from './database.module.js';
import { UserFilter } from './user.filter.js';
import { UsersController } from './users.controller.js';

@Module({
  imports: [
    DatabaseModule,
    FilterModule.forRoot({ inputNormalizer: 'camelCase', adapter: drizzleAdapter({ connection: DRIZZLE }) }),
    FilterModule.forFeature([UserFilter]),
  ],
  controllers: [UsersController],
})
export class AppModule {}

// src/users.controller.ts
import { Controller, Get } from '@nestjs/common';
import { ApplyFilter } from '@dudousxd/nestjs-filter';
import type { DrizzleQuery } from '@dudousxd/nestjs-filter-drizzle';
import type { users } from './schema.js';
import { UserFilter } from './user.filter.js';

@Controller('users')
export class UsersController {
  @Get()
  list(@ApplyFilter(UserFilter) q: DrizzleQuery<typeof users>) {
    return q.execute();
  }

  @Get('page')
  page(@ApplyFilter(UserFilter) q: DrizzleQuery<typeof users>) {
    return q.executeAndCount(); // { rows, total } — total ignores LIMIT/OFFSET
  }
}

import { DrizzleAdapter } from '@dudousxd/nestjs-filter-drizzle';
import { drizzle } from 'drizzle-orm/node-postgres';

const q = new DrizzleAdapter(drizzle.mock()).query(users);
await runner.apply(UserFilter, { name: 'al', minAge: 18 }, q);

expect(q.toSQL()).toEqual({
  sql: 'select "id", "name", "age" from "users" where ("users"."name" ilike $1 and "users"."age" >= $2)',
  params: ['%al%', 18],
});