Drizzle Adapter

The Drizzle adapter compiles the query plan into an explicit statement — aliases, joins, conditions, order, pagination — which an executor then materialises in the declared dialect.

Install

pnpm add drizzle-orm postgres
# mysql2 for MySQL, or the SQL Server driver

The executor, once

What your services inject is not Drizzle's db: it is the DrizzleDatabase that drizzleDatabase() returns.

import { Global, Module } from '@nestjs/common';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import {
  drizzleDatabase,
  type DrizzleDatabase,
} from 'nestjs-rest-query/drizzle';

export function createDatabase(url: string) {
  return drizzle({ client: postgres(url, { max: 5 }) });
}

export type AppDatabase = ReturnType<typeof createDatabase>;

export const APP_DATABASE = Symbol('APP_DATABASE');
export const DRIZZLE_EXECUTOR = Symbol('DRIZZLE_EXECUTOR');

@Global()
@Module({
  providers: [
    { provide: APP_DATABASE, useFactory: () => createDatabase(databaseUrl()) },
    {
      provide: DRIZZLE_EXECUTOR,
      inject: [APP_DATABASE],
      // No cast: the postgres-js `db` satisfies DrizzleClientLike structurally,
      // because it exposes execute().
      useFactory: (client: AppDatabase): DrizzleDatabase =>
        drizzleDatabase({ client, dialect: 'postgres' }),
    },
  ],
  exports: [APP_DATABASE, DRIZZLE_EXECUTOR],
})
export class DatabaseModule {}

The logical descriptor

The adapter does not inspect your pgTable. You declare a logical descriptor, and it is that descriptor which decides value kinds, nullability, which columns are internal, which folded column backs search/ilike and which column gives a portable total order.

import {
  createDrizzleTable,
  type DrizzleRelationMap,
  type DrizzleTable,
} from 'nestjs-rest-query/drizzle';

export const companiesTable: DrizzleTable = createDrizzleTable({
  name: 'companies',
  model: 'company',
  columns: {
    id: {
      name: 'id',
      kind: 'uuid',
      nullable: false,
      primaryKey: true,
      // Without this, every request fails: the pagination tie-break is always
      // applied over the primary key, and uuid has no portable total order.
      portableOrderField: 'idOrder',
    },
    idOrder: {
      name: 'idOrder',
      kind: 'string',
      nullable: false,
      primaryKey: false,
      internal: true,
    },
    name: {
      name: 'name',
      kind: 'string',
      nullable: false,
      primaryKey: false,
      foldedField: 'nameFolded',
    },
    nameFolded: {
      name: 'nameFolded',
      kind: 'string',
      nullable: false,
      primaryKey: false,
      internal: true,
    },
    createdAt: {
      name: 'createdAt',
      kind: 'datetime',
      nullable: false,
      primaryKey: false,
    },
  },
});

kind is the most consequential decision here. uuid is not string: it forbids gt/lt/between without a portableOrderField and refuses a value that is not a canonical UUID, instead of comparing arbitrary text.

Relations, by dotted path

export const userRelations: DrizzleRelationMap = {
  company: {
    target: companiesTable,
    cardinality: 'one',
    // companyId is nullable, so the relation is too: a LEFT JOIN with no match
    // becomes company: null, not an object full of nulls.
    nullable: true,
    sourceColumn: 'companyId',
    targetColumn: 'id',
  },
  posts: {
    target: postsTable,
    cardinality: 'many',
    nullable: true,
    sourceColumn: 'id',
    targetColumn: 'userId',
  },
};

The key is the path as seen from the root: company is one hop, company.owner would be the next. Only the dot-free keys enter the root's logical schema; the dotted ones exist so the compiler knows how to join or correlate deep paths.

sourceColumn/targetColumn are the join columns in the direction of the path, not "the real foreign key": on a many relation the root side enters with id and the target with userId, the reverse of a one relation.

Build the registry from the descriptor

import { defineQueryRules } from 'nestjs-rest-query';
import type { SchemaRegistry } from 'nestjs-rest-query';
import { buildSourceSchema } from 'nestjs-rest-query/drizzle';

export const USER_SCHEMAS: SchemaRegistry = new Map([
  ['user', buildSourceSchema(usersTable, userRelations)],
  ['company', buildSourceSchema(companiesTable, {})],
  ['post', buildSourceSchema(postsTable, {})],
]);

buildSourceSchema is the function drizzleSource uses internally to describe the source, so deriving the registry from it removes a whole class of SOURCE_CONFIGURATION_INVALID: writing defineQuerySchema by hand here would give two truths about one table, and the core compares them before executing.

The source, per request

import { drizzleSource, type DrizzleDatabase } from 'nestjs-rest-query/drizzle';

async findAll(
  query: DynamicQueryDto,
  rules: CompiledQueryRules,
): Promise<NormalizedQueryResult<object>> {
  return this.queryBuilderService.execute(
    drizzleSource({
      db: this.db,            // the DrizzleDatabase, not the raw drizzle db
      dialect: 'postgres',    // must match the executor; fails closed otherwise
      table: usersTable,
      relations: userRelations,
    }),
    query,
    rules,
  );
}

The declared dialect has to be the executor's. Diverging would not error anywhere — it would produce wrong results, because pagination and boolean coercion come from the dialect — so drizzleSource compares the two and fails closed.

drizzleSource<TRow> is generic in the row type, and TRow arrives from the executor: build it as drizzleDatabase<UserRow>({ client, dialect }) and drizzleSource({ db, ... }) infers UserRow, so the method can be annotated Promise<NormalizedQueryResult<UserRow>> with no cast. The object above is only the default you get when you do not narrow it.

What the compiler does

Relations by dotted path, an idempotent join planner, correlated EXISTS for any many hop — including chains, with the second hop as a join inside the subquery — and first-level collections hydrated by their own query. ILIKE is never emitted.

The logical key and the physical column are two different things, and both are honoured. The columns key is the logical field — what fields=, the rules and the JSON speak — and DrizzleColumn.name is the physical column the compiler puts in the SQL through sql.identifier(...). A descriptor key that differs from the physical column name is a supported mapping, not a bug.

A field that is not declared fails closed while the application boots, not in the database: buildSourceSchema runs every relation's sourceColumn and targetColumn through the same lookup, so an undeclared join column is SOURCE_CONFIGURATION_INVALID — Drizzle table users has no column declared for field companyId — at source construction.

03-app-with-drizzle uses the mapping: snake_case physical names (id_order, name_folded, created_at) behind camelCase logical keys, with a startup assertion against getTableColumns(pgTable) that each key is the pgTable property and each name is that property's physical column.

Limits

Collections nested under another relation fail closed. Projecting a to-many relation that hangs off another relation is refused with ADAPTER_CONTRACT_VIOLATION — Drizzle adapter cannot project the nested to-many relation <path> — rather than silently returning an empty collection. First-level collections are supported.

drizzle-kit push/generate cannot express the certified profile. They do not emit COLLATE "C", and code-point collation on portable text columns is part of the parity promise. Example 03 issues explicit DDL from src/database/bootstrap.ts instead; see test/profiles/ for the reference DDL per family.

Migrating a 2.x Drizzle source

v2 handed the adapter Drizzle column objects; v3 takes a logical descriptor and plain names:

columnMap is gone as well: paths resolve through the logical descriptor.

Next steps

• Usage Guide for every parameter, operator and error code.
• Adapters overview for the full divergence table.
• The full migration guide for the 2.x → 3.x path.