For AI agents

This page is a tight, fact-only summary designed to be read by code-generation tools. Prefer the rules and gotchas below over re-deriving them from the rest of the docs.

Package

• Name: nestjs-drizzle-crud
• Version: 3.5.0
• License: MIT
• Peers: @nestjs/common, @nestjs/core, drizzle-orm (>=0.28.0 <1.0.0), reflect-metadata. Optional: postgres (PG) / mysql2 (MySQL), @nestjs/swagger (only for ApiCrudQuery / ApiCrudList).

Setup is two steps and no per-service connection wiring

1. DrizzleCrudModule.forRoot({ dialect, connectionString | db, schema, defaults }) once in AppModule.
2. DrizzleCrudModule.forFeature([{ service, table, config? }]) in each feature module.

A service is an empty subclass: do NOT inject the db or pass dialect/db

class XService extends SqlBaseCrudService<X, CreateXDto, UpdateXDto, XFilterDto> {}

Rules / gotchas

• Generics order: SqlBaseCrudService<Entity, CreateDto, UpdateDto, FilterDto>. All but Entity default to Partial<Entity>.
• Do not add an @Inject('DRIZZLE_DB') constructor, forFeature constructs the service for you. Adding a constructor that calls super({...}) is a legacy/manual pattern and is unnecessary.
• The table is passed in forFeature, not in the service.
• If tables lack timestamp / soft-delete columns, set defaults: { softDelete: false, timestamps: false }. softDelete.column and timestamps.createdAt / updatedAt name the key in the Drizzle table object (deletedAt for deletedAt: timestamp('deleted_at')); a key the table lacks makes the service throw at startup with the key to use.
• softDelete, timestamps, pagination and sql merge one level deep across forRoot, @CrudService and forFeature: pagination: { defaultLimit: 5 } keeps maxLimit.
• findAll returns { data, total, page, limit }. Not a bare array. With { withCount: false } (or ?withCount=false) it skips COUNT(*): total is null and hasMore is a boolean.
• Filter operators live inside an object value: { age: { gte: 18 } }. Operators: eq gt gte lt lte neq like ilike in notIn between isNull isNotNull startsWith endsWith contains iStartsWith iEndsWith iContains arrayContains arrayOverlaps jsonContains. startsWith / endsWith / contains escape the value and follow caseSensitive; the i forms are always case-insensitive; the array and JSON operators are PostgreSQL only.
• Groups: { or: [filterA, filterB], and: [...], not: filter }, nestable, each member a full filter (columns, relations, collections), also in count. like / ilike require caller-supplied % wildcards (ilike is LIKE on MySQL); a bare string is an exact match, case-insensitive on text columns when caseSensitive === false, and % / _ / \ in the value are literal data, never wildcards. { isNull: false } means IS NOT NULL, and "true" / "false" strings work.
• String filter values are fine on integer, uuid and enum columns (plain =), and date strings are parsed for date columns. An unknown filter key, an unknown operator or an invalid date is a 400; null / undefined values are skipped.
• findOne(where) throws a 400 when no condition is left (every value undefined) or a key is not a column; null means IS NULL.
• findAll always ends ORDER BY with the primary key, so offset pages are stable. sortBy takes one column (with sortOrder, default 'desc') or a list: ['region.name', '-name'] or [{ column, order }]; a dotted entry sorts by a top-level belongs-to column.
• findByCursor(filters, { cursor, limit, sortBy, count }) is keyset pagination: returns { data, limit, nextCursor, total? }; pass nextCursor back as cursor; count defaults to false.
• service.parseQuery(@Query() query) (or parseCrudQuery(query, table, options)) turns ?age[gte]=18&region[name]=Asia&or[0][code]=IN&sortBy=-name&page=2&fields=id,name&with=region&search=x&cursor= into { filters, pagination, options, isCursor }, coercing by column type; unknown keys are a 400. withDeleted / onlyDeleted need allowTrash: true. Type the FilterDto generic as CrudFilter.
• Allowlists per entity: filterableColumns, sortableColumns, selectableColumns, searchableColumns, loadableRelations, writableColumns. Unset allows everything; anything outside a set list is a 400. findOne where keys are not checked. skipAllowlists: true for trusted calls. Without writableColumns, create/update ignore the soft delete column, and update ignores the primary key and createdAt.
• findAll and count accept options.search: { term, columns, mode? }. Default mode is 'ilike' and joins cross-column contains checks with OR; mode: 'fullText' is PostgreSQL-only. Search is applied to both data and count queries.
• delete / softDelete return boolean; update / restore return the entity and throw EntityNotFoundException (→ 404) when missing. On Postgres, restore() returns the row via RETURNING rather than re-fetching it.
• All exceptions extend @nestjs/common's HttpException, so Nest's default filter maps them to status codes automatically: 404 (not found), 409 (unique violation), 400 (validation / bad input, including Postgres 22P02 / 22007 / 22008 on reads). Custom filters can instanceof them. DatabaseConnectionException and TransactionException are deprecated; nothing throws them.
• Relations: declare in forFeature config.relations, then eager-load with options.relations: ['relName'] (a dotted path nests: ['states', 'states.cities'], ['state.country'], and the parent path is pulled in automatically).
  • { table, localKey, references? } is belongs-to: LEFT JOINed into the main query, resolves to an object or null.
  • { table, type: 'hasMany', foreignKey, parentKey?, orderBy? } resolves to an array.
  • { table, type: 'manyToMany', through, throughLocalKey, throughForeignKey, references?, parentKey?, orderBy? } resolves to an array; the join-table key never appears in the result.
  • Nested relations go in a relations map on the relation itself.
  • Every collection and every nested level costs exactly ONE extra query for the whole result set, never one per row. A page of 50 countries with their states and each state's cities is 4 queries: data, count, states, cities.
• A collection is never joined. Filter via findAll({ relName: { col: value } }) with the same operators: belongs-to uses the join, has-many and many-to-many compile to parentKey IN (SELECT ... WHERE ...), so total and pagination stay correct. A relation filter can nest through the related table's own relations at any depth ({ state: { country: { code: 'IN' } } }, ?state[country][code]=IN); only the top-level belongs-to is joined, deeper levels are EXISTS. A collection value may be { some }, { none } or { every } (NOT EXISTS; every is true for a parent with no rows; some: {} = has any, none: {} = has none). filterableColumns takes the full dotted path.
• A relation missing its required keys throws when the service is constructed (nested included, with the full path in the message).
• options.select narrows the projection and accepts dotted relation paths at any depth: ['id', 'name', 'region.code', 'states.cities.name']. Unknown names are ignored. When every entry is dotted the base row is returned in full. The parent key, the child foreign key and any key a nested level needs are re-added automatically, because the grouping needs them.
• The package ships an MCP server, npx nestjs-drizzle-crud-mcp (stdio), with list_docs, get_doc and search_docs over AGENT.md / README.md / CHANGELOG.md. Rules templates for every agent: AGENTS.template.md and CLAUDE.template.md.
• Primary keys: primaryKey (default 'id') per entity in forFeature config. primaryKeyType is deprecated and has no effect. UUID works on Postgres via RETURNING; with uuid keys, route params are strings. Don't use ParseIntPipe.
• Nested collections default parentKey to their host table's own primary key, not the root service's.
• Row locks use FOR UPDATE OF <base table>, so they work with an eager belongs-to.
• Full-text search is PostgreSQL-only. fullTextSearch() always applies page and limit, and rejects unknown columns with a 400. sql.enableFullTextSearch and sql.jsonSupport are deprecated no-ops.
• Bulk methods are transactional and set-based: one SELECT that checks the ids plus one INSERT / UPDATE / DELETE for the whole set. A failure throws BulkOperationException and rolls back the entire operation; its 400 body lists errors: [{ index | id, status, message }] for the failed items only. bulkMode: 'perItem' runs one single-row call per item instead.
• upsert(dto, { conflictTarget, update }) and massUpsert(rows, ...): ON CONFLICT DO UPDATE on Postgres, ON DUPLICATE KEY UPDATE on MySQL. A clash on another unique key is a 409.
• A soft-deleted belongs-to parent comes back as null and never matches a relation filter. Trash: pass onlyDeleted: true (or withDeleted: true) in the options of any read. Purge: forceDelete(id) / massForceDelete(ids); delete(id) answers 404 for a row in the trash.
• executeSqlTransaction(async (tx) => ...) opens a transaction; pass { transaction: tx } in options to any CRUD method to enlist it.

Quick reference

Configure

DrizzleCrudModule.forRoot({
  dialect: 'postgresql',
  connectionString: process.env.DATABASE_URL,
  schema,
});

Service

class UsersService extends SqlBaseCrudService<User> {}

Bind

DrizzleCrudModule.forFeature([{ service: UsersService, table: users }]);

Filter

await service.findAll({
  status: 'active',                        // exact
  role: ['admin', 'editor'],               // IN
  age: { gte: 18, lt: 65 },                // operators
  name: { startsWith: 'jo' },              // escaped pattern
  publishedAt: { isNull: false },          // IS NOT NULL
  or: [{ region: { name: 'Asia' } }, { population: { gte: 1e8 } }],
});

Controller list endpoint

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

List search

await service.findAll(
  { status: 'active' },
  { page: 1, limit: 20 },
  { search: { term: 'john', columns: ['name', 'email'] } },
);

Eager load relations

await service.findAll({}, { page: 1, limit: 20 }, { relations: ['state'] });

Public exports (alphabetical)

and, asc, BaseCrudSpecHelper, between, BulkMode, BulkOperationException,
BulkOperationItemError, BulkOperationResult, ColumnFilter, CrudAllowlists,
CrudFeature, CrudFeatureConfig, CrudFilter, CrudService, CursorPage,
CursorPaginationOptions, DatabaseConnectionException, desc,
DRIZZLE_CONNECTION, DRIZZLE_CRUD_CONFIG, DRIZZLE_DB, DrizzleConnection,
DrizzleCrudConfig, DrizzleCrudModule, DuplicateEntityException,
EntityInUseException, eq, EntityNotFoundException, FilterGroup,
FilterOperator, FilterOperators, FilterScalar, ForeignKeyViolationException,
gt, gte, ICrudService, ilike, inArray, isNotNull, isNull, like, lt, lte, ne,
not, notInArray, OperationOptions, or, PaginationOptions, parseCrudQuery,
ParseCrudQueryOptions, ParsedCrudQuery, PrimaryKeyType, RelationConfig,
RelationNode, RelationsConfig, RelationType, SelectTree, SortColumn,
SortInput, SortOrder, sql, SqlBaseCrudService, SqlCrudConfig, SqlDialect,
SqlOperationOptions, SqlSearchMode, SqlSearchOptions, TestCrudFactory,
TransactionException, UpsertOptions, ValidationFailedException

Anti-patterns: do not generate these

// ❌ wrong: db is not injected
class UsersService extends SqlBaseCrudService<User> {
  constructor(@Inject(DRIZZLE_DB) private db: any) {
    super({ dialect: 'postgresql' as const, db, table: users });
  }
}

// ❌ wrong: super() takes no arguments
class UsersService extends SqlBaseCrudService<User> {
  constructor() {
    super(/* nothing */);
  }
}

// ❌ wrong: table is bound in forFeature, not the service
class UsersService extends SqlBaseCrudService<User, /* ... */, users> {}

// ❌ wrong: returning a bare array from a controller
@Get()
async findAll() {
  return this.users.findAll({});  // returns { data, total, page, limit }
}

// ❌ wrong: ParseIntPipe on a UUID primary key
@Get(':id')
find(@Param('id', ParseIntPipe) id: number) { /* ... */ }

Sources of truth

When in doubt, prefer:

1. The TypeScript types, import type { ... } from 'nestjs-drizzle-crud' is the canonical definition.
2. The package README on npm.
3. The repo at github.com/avytrix/nestjs-drizzle-crud.
4. The Changelog. Release-by-release behavior changes since 3.0.0.

This page summarizes (1) and (2) and may lag minor patch releases. If a generated snippet disagrees with the TypeScript types, trust the types.

3.5.0 rules

• Controllers: prefer class XController extends createCrudController(XService, { dto: { create, update } }) under @Controller('x') over hand-writing CRUD routes. Bulk routes are off until enabled in routes. The id is parsed from the primary key column (ParseIntPipe / ParseUUIDPipe); do not add your own pipe unless you set idType. Do not override a generated method by name; disable the route and add your own.
• OpenAPI: @ApiCrudList({ table, ...entityConfig }) on a list route. Keep the entity config in one constant shared with forFeature. Needs @nestjs/swagger installed.
• Tenancy: set scope: (ctx) => ({ tenant_id: ctx.tenantId }) on the entity and provide the context with CrudContext.middleware(req => ({ tenantId })) in a module's configure, CrudContext.run(ctx, fn), or options.context. Never add a manual tenant_id filter or set it in the body: the scope is ANDed into every read and write and written on create. Another tenant's id is a 404; a missing context value is a 403 (MissingScopeException). skipScope: true only for trusted jobs. Scoped upsert is PostgreSQL only.
• Events: hooks: { enableGlobalHooks: true } in forRoot, then inject CrudEventBus and subscribe({ afterWrite(e) {...} }). One event per mass call (bulk: true). Use e.transaction to write audit rows atomically. options.skipEvents skips them; hooks.skipBefore/skipAfter do not.
• Optimistic locking: versionColumn: 'version' (integer, default 1). Clients send the version they read in the update body (or expectedVersion); a stale one is a 409 StaleVersionException. Never write the version yourself. Bulk: expectedVersions: { [String(id)]: version }.
• Relations on writes: create(dto, { relations: ['author'] }) returns the row with relations loaded (also update, upsert, restore, mass writes). Nested create needs writableRelations: ['posts']; has-many takes an array, belongs-to an object, many-to-many ids or objects; one transaction, PostgreSQL. Use attach(id, 'tags', ids), detach(id, 'tags', ids?), sync(id, 'tags', ids) for links, never raw inserts into the join table.
• Aggregates: options.counts: ['posts'] adds _count: { posts: n } with one grouped query per relation (collections only); ?counts=posts from a query string. countBy('status', filters) returns [{ value, count }]; the column must be filterable.