Drizzle Adapter

Use nestjs-rest-query with Drizzle ORM - type-safe, modern, and with fine-grained control over relations.

The Drizzle adapter lets you use nestjs-rest-query with Drizzle ORM. Drizzle offers strict typing, type-safe schema generation, and a functional style that pairs well with NestJS decorators.

Install

Install Drizzle and the database driver:

pnpm add drizzle-orm postgres
# ou com mysql: drizzle-orm mysql2
# ou com sqlite: drizzle-orm better-sqlite3

Then install nestjs-rest-query:

pnpm add nestjs-rest-query

Starting with version 2.0.0, typeorm is an optional peer dependency. If you use only Drizzle, you do not need to install TypeORM.

Module setup

app.module.ts

import { Module } from '@nestjs/common';
import { DynamicQueryBuilderModule } from 'nestjs-rest-query';
import { DrizzleAdapter } from 'nestjs-rest-query/drizzle';

@Module({
  imports: [
    DynamicQueryBuilderModule.forRoot({
      adapter: new DrizzleAdapter(),
      pagination: { defaultPerPage: 20, maxPerPage: 100 },
    }),
  ],
})
export class AppModule {}

Defining `DrizzleSource`

Each Drizzle endpoint passes a DrizzleSource object to queryBuilderService.execute(). This object describes:

db - the Drizzle instance (db from your PostgreSQL, MySQL, etc. client)
table - the root table (for example, users)
primaryKey - the root table PK column (for example, users.id)
relations (optional) - relation map for JOINs
columnMap (optional) - explicit mapping for non-trivial columns

Schema example:

db/schema.ts

import {
  pgTable,
  serial,
  varchar,
  text,
  timestamp,
  integer,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  companyId: integer('company_id'),
  createdAt: timestamp('created_at').defaultNow(),
});

export const companies = pgTable('companies', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  userId: integer('user_id').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// Relations (optional, for the ORM - not used directly by the adapter)
export const usersRelations = relations(users, ({ one, many }) => ({
  company: one(companies, {
    fields: [users.companyId],
    references: [companies.id],
  }),
  posts: many(posts),
}));

Now, in your controller:

users.controller.ts

import { Controller, Get, Query } from '@nestjs/common';
import { eq } from 'drizzle-orm';
import {
  ApiDynamicQuery,
  QueryRules,
  QueryBuilderService,
  RulesConfig,
  QueryInput,
} from 'nestjs-rest-query';
import { DrizzleService } from './drizzle.service';
import { users, companies, posts } from './db/schema';

const rules: RulesConfig = {
  alias: 'user',
  filters: ['email', 'name', 'createdAt', 'company.name'],
  sorts: ['name', 'createdAt'],
  fields: ['id', 'name', 'email'],
  includes: ['company', 'posts'],
  search: ['name', 'email'],
};

@Controller('users')
export class UsersController {
  constructor(
    private readonly drizzleService: DrizzleService,
    private readonly qb: QueryBuilderService
  ) {}

  @Get()
  @ApiDynamicQuery(rules)
  list(@Query() query: QueryInput, @QueryRules() endpointRules = rules) {
    return this.qb.execute(
      {
        db: this.drizzleService.db,
        table: users,
        primaryKey: users.id,
        relations: {
          company: {
            table: companies,
            on: eq(users.companyId, companies.id),
            cardinality: 'one',
          },
          posts: {
            table: posts,
            on: eq(posts.userId, users.id),
            cardinality: 'many',
            primaryKey: posts.id,
          },
        },
      },
      query,
      endpointRules
    );
  }
}

The relations map is required for dotted paths

Unlike TypeORM, which auto-discovers relations via @OneToOne and @OneToMany, Drizzle will not discover relations automatically. If your query string includes a dotted path (for example, ?filter[company.name][eq]=Acme), you must register the relation in the relations map.

If you try to use an unregistered path:

GET /users?filter[company.name][eq]=Acme

And relations does not contain company, the adapter will throw:

DrizzleAdapter: no relation registered for "company". Add it to DrizzleSource.relations.

The relations map is keyed by joinPath (for example, "company", "company.owner"):

relations: {
  company: {
    table: companies,
    on: eq(users.companyId, companies.id),
    cardinality: 'one',
  },
  'company.owner': {
    table: ownerUsers, // the User table again, for owner
    on: eq(companies.ownerId, ownerUsers.id),
    cardinality: 'one',
  },
}

Field paths in query strings

Field paths in the query string must correspond to:

Direct columns on the root table - for example, name → users.name
Columns on registered relations - for example, company.name → companies.name (if relations.company is registered)
Mapped via columnMap - for example, if columnMap['company.name'] = companies.name, the company.name path is resolved

If a column is not found:

DrizzleAdapter: column "name" not found on relation "company".
Map it explicitly via DrizzleSource.columnMap["company.name"].

Use columnMap for non-trivial columns or when the schema structure makes automatic discovery difficult:

columnMap: {
  'company.name': companies.name,
  'company.owner.email': ownerUsers.email,
}

`cardinality: 'many'` requires `primaryKey`

When you declare a 1:N relation (cardinality: 'many'), you MUST provide the related table PK column. This is required for the adapter to deduplicate rows during aggregation.

relations: {
  posts: {
    table: posts,
    on: eq(posts.userId, users.id),
    cardinality: 'many',
    primaryKey: posts.id, // required for 'many'
  },
}

If you forget:

DrizzleAdapter: relation "posts" has cardinality 'many' but no primaryKey. 'many' relations require relations["posts"].primaryKey for deduplication. If this is a 1:1 relation, change cardinality to 'one' (or omit it).

This is checked at compile time (the TypeScript discriminated union prevents invalid syntax) and at runtime (when createQueryBuilder is called).

ORDER BY through a 'many' relation is NOT supported

If you try to sort by a column from a 1:N relation:

GET /users?sort=-posts.createdAt

The adapter will throw:

DrizzleAdapter: ORDER BY a column from 'many' relation "posts" is not supported. Sort by root or 'one' relation columns. To order presented relation arrays, use the customize hook to add per-relation ORDER BY in your application layer.

Why? When you JOIN 1:N, each user appears multiple times in the result set. Doing ORDER BY while collapsing those rows with DISTINCT makes the sort meaning undefined - which createdAt are you sorting, the first post or the last one?

Workaround: use the customize hook to apply relation ordering after the adapter pipeline, in the application layer:

return this.qb.execute(drizzleSource, query, rules, (qb) => {
  // qb is the Drizzle query builder - you can run custom operations
  // After the adapter runs, you can transform the data in the application
});

Or, in the controller layer, after receiving data, sort the relation arrays:

const result = await this.qb.execute(...);
result.data = result.data.map(row => ({
  ...row,
  posts: row.posts.sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime()),
}));
return result;

Result shape - flat, TypeORM-compatible

DrizzleAdapter returns rows in the same flat shape as TypeOrmAdapter. Root columns stay at the top level; relations are keys at the same level (object for 'one', array for 'many').

GET /users?filter[email][ilike]=%@acme.com&includes=company,posts&page=1&perPage=2

{
  "data": [
    {
      "id": 1,
      "name": "Ana",
      "email": "ana@acme.com",
      "createdAt": "2024-01-15T10:00:00Z",
      "company": { "id": 101, "name": "Acme" },
      "posts": [
        {
          "id": 201,
          "title": "Hello",
          "userId": 1,
          "createdAt": "2024-01-20T14:00:00Z"
        },
        {
          "id": 202,
          "title": "World",
          "userId": 1,
          "createdAt": "2024-01-21T09:00:00Z"
        }
      ]
    },
    {
      "id": 2,
      "name": "Bia",
      "email": "bia@acme.com",
      "createdAt": "2024-02-01T08:00:00Z",
      "company": { "id": 101, "name": "Acme" },
      "posts": []
    }
  ],
  "page": 1,
  "perPage": 2,
  "total": 17,
  "lastPage": 9
}

Notes:

Root columns go directly to the top of the object. There is no wrapper key (root/user/etc.).
1:N relations (like posts) appear as arrays, deduplicated by posts.id.
1:1 relations (like company) appear as scalar objects (or null if the LEFT JOIN finds no match).
There is no deep nesting (no company.owner inside company). If you need that, wait for the future DrizzleRelationalAdapter based on db.query.<table>.findMany({ with }).
rules.alias is still useful for logging and error messages, but it does not affect the response shape.
Warning: if a root-table column has the same name as a relation key, the relation overwrites the column in the object. Avoid that in the schema design.

Two-phase pagination (transparent)

When a 'many' relation is present, the adapter enables two-phase pagination:

Phase 1: SELECT DISTINCT root.id WHERE ... ORDER BY ... LIMIT/OFFSET
- Finds matching user IDs, respecting page/limit
Phase 2: SELECT root., relations. WHERE root.id IN (...) ORDER BY ...
- Fetches all data and relations for those users
Phase 3: Client-side aggregation - groups by root.id, arrays for 'many', scalars for 'one'

Result:

data.length <= perPage always (never inflated by 1:N JOINs)
total = distinct user count (not result rows)
lastPage is correct

Trade-off: 3 queries per request instead of 1. In-memory aggregation cost ≈ perPage × Σ(cardinality of each 'many' relation).

Recommendation: for endpoints expecting many children per parent, keep perPage modest (≤50). Or consider a separate endpoint to enumerate children.

Customize hook for advanced cases

Sometimes you want to apply Drizzle operations that the adapter does not expose:

return this.qb.execute(drizzleSource, query, rules, (qb) => {
  // qb is the accumulated Drizzle query builder
  // You can call custom methods here
});

Example: sort relation arrays after aggregation (as mentioned in the ORDER BY section):

const result = await this.qb.execute(drizzleSource, query, rules);
result.data = result.data.map((row) => ({
  ...row,
  posts: [...(row.posts || [])].sort(
    (a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime()
  ),
}));
return result;

Next steps

Migrating from a previous version? See the 1.x → 2.x migration guide for details on optional peer dependencies, response shape changes, and adapter error messages.
For a complete guide to parameters, operators, and the whitelist, see Usage Guide.
If you want to implement your own adapter, see Writing your own.

Drizzle Adapter

On this page