nestjs-drizzle-crud facts for AI agents

A condensed fact sheet of nestjs-drizzle-crud for AI coding agents, with setup, generics, return shapes, filters, relations, errors and anti-patterns.

nestjs-drizzle-crud facts for AI agents fit on one page: register DrizzleCrudModule.forRoot once and forFeature per entity, declare each service as an empty subclass of SqlBaseCrudService with four generics, and call its methods. Lists return { data, total, page, limit }, errors are Nest HTTP exceptions, and every rule below is checked against the package source.

When this page and the types disagree, trust the types

The TypeScript declarations shipped in the package are the canonical definition. Generate code against import type { ... } from "@avytrix/nestjs-drizzle-crud", and use this page for the rules the types cannot express.

Package facts

FactValue
Name@avytrix/nestjs-drizzle-crud on npm (renamed from nestjs-drizzle-crud in 3.5.1), version 3.8.3, MIT
Runtime dependenciesNone
Peers@nestjs/common and @nestjs/core >=10.0.0, drizzle-orm >=0.28.0 <1.0.0, reflect-metadata ^0.1.13 || ^0.2.0
Optional peerspostgres >=3.0.0 and mysql2 >=3.0.0 (only for connectionString), @nestjs/swagger >=7.0.0 (only for ApiCrudQuery, ApiCrudList and openapi)
Node>=20.0.0
Dialects"postgresql", "mysql" and "sqlite" (SQLite, libSQL, Cloudflare D1). connectionString works on PostgreSQL and MySQL; on SQLite, build the Drizzle instance yourself and pass it as db
Binarynestjs-drizzle-crud-mcp, an MCP server over stdio with list_docs, get_doc and search_docs over AGENT.md, README.md and CHANGELOG.md
Agent rule templatesAGENTS.template.md and CLAUDE.template.md, shipped in the package

How do I set it up?

Setup is two module calls and no connection wiring in services.

  1. DrizzleCrudModule.forRoot({ dialect, connectionString | db, schema?, defaults?, sql?, hooks? }) once in AppModule (or forRootAsync({ imports?, inject?, useFactory })). It is global.
  2. DrizzleCrudModule.forFeature([{ service, table, config? }]) in each feature module. It builds each service and exports it.
users.service.ts
export class UsersService extends SqlBaseCrudService<
  User,          // typeof users.$inferSelect
  CreateUserDto,
  UpdateUserDto,
  CrudFilter
> {}

Rules for generated code

Services

  • Generics: SqlBaseCrudService<T, CreateDto, UpdateDto, FilterDto>. All four are required; none has a default. Use CrudFilter for FilterDto when filters come from a query string.
  • A service is an empty subclass. No @Injectable(), no constructor, no @Inject(DRIZZLE_DB): forFeature calls new Service(config) itself, and a decorator or extra constructor parameter breaks that.
  • The table goes in forFeature, not in the service. Per-entity config goes in forFeature config or @CrudService(config); forFeature wins.
  • For a hand-written query inside a service, use this.config.db and this.config.table. Elsewhere, inject DRIZZLE_DB.
  • Override protected hooks for custom logic: validateCreate, validateUpdate, beforeCreate, afterCreate, beforeUpdate, afterUpdate, beforeSoftDelete, afterSoftDelete, beforeRestore, afterRestore, beforeDelete, afterDelete, mapCreateDtoToEntity, mapUpdateDtoToEntity. Create runs validateCreate, then beforeCreate, then mapCreateDtoToEntity.
  • executeSqlTransaction(async (tx) => ...) is protected: call it from a service method and pass { transaction: tx } to each CRUD call that joins it.

Config

  • Defaults: softDelete: true on deleted_at, timestamps: true on created_at and updated_at, pagination: { defaultLimit: 20, maxLimit: 100 }, primaryKey: "id", sql.caseSensitive: false, sql.useReturning true on PostgreSQL and SQLite and forced off on MySQL. connection: { poolSize, idleTimeout, ssl } tunes a connection built from connectionString. forRootAsync takes useFactory, useClass or useExisting (a class with createDrizzleCrudOptions()).
  • Tables without those columns need defaults: { softDelete: false, timestamps: false }. softDelete.column, timestamps.createdAt / updatedAt and versionColumn name the key in the Drizzle table object (deletedAt for deletedAt: timestamp("deleted_at")); a key the table lacks throws 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. Arrays (allowlists) replace.
  • connectionString is PostgreSQL only and schema is optional. primaryKeyType, sql.jsonSupport and sql.enableFullTextSearch are deprecated no-ops.

Reads

  • findAll(filters?, pagination?, options?) returns { data, total, page, limit }, never a bare array. With withCount: false (or ?withCount=false) it skips COUNT(*): total is null and hasMore is a boolean.
  • findAll always ends ORDER BY with the primary key ascending. sortBy is one column (direction from sortOrder, default "asc"), a - or + prefixed column, a comma separated string, or a list: ["region.name", "-name"] or [{ column, order }]. In a list, an unprefixed entry is ascending. A dotted entry sorts by a top-level belongs-to column.
  • page below 1 becomes 1 and limit is clamped to 1 to maxLimit. A bad sortOrder or unknown sort column is a 400.
  • findByCursor(filters, { cursor, limit, sortBy, sortOrder, count }, options) is keyset pagination: { data, limit, nextCursor, total? }. Pass nextCursor back as cursor; count defaults to false. A cursor made for another sort is a 400.
  • find(id) and findOne(where) return T | null; exists(id) returns a boolean. findOne is a 400 when no condition is left (every value undefined) or a key is not a column; null means IS NULL; its keys skip the allowlists.
  • count(filters?, options?) returns a number. countBy(column, filters?, options?) returns [{ value, count }], largest first; the column must be filterable.
  • options.search: { term, columns, mode? } works on findAll and count. mode defaults to "ilike", an escaped contains match ORed across columns; "fullText" is PostgreSQL only.
  • fullTextSearch(term, columns, pagination?, options?) is PostgreSQL only, returns { data, total, page, limit }, always applies page and limit, and is a 400 for an unknown column.
  • Trash: withDeleted: true or onlyDeleted: true on any read. A soft-deleted belongs-to parent comes back as null and never matches a relation filter.

Filters

  • A key is a column, a relation, or a group (and, or, not). Keys are ANDed.
  • Column values: a scalar is an exact match, an array is IN, null and undefined are skipped, an object holds operators: { age: { gte: 18, lt: 65 } }.
  • Operators: eq neq gt gte lt lte like ilike in notIn between isNull isNotNull startsWith endsWith contains iStartsWith iEndsWith iContains arrayContains arrayOverlaps jsonContains.
  • like and ilike take your own % wildcards (ilike is LIKE on MySQL). startsWith, endsWith and contains escape %, _ and \ and follow sql.caseSensitive; the i forms are always case-insensitive. arrayContains, arrayOverlaps and jsonContains are PostgreSQL only.
  • A bare string is an exact match, case-insensitive on text columns while caseSensitive is false. { isNull: false } means IS NOT NULL, and "true" / "false" strings work.
  • Date strings are parsed for date columns; an invalid date is a 400. An unknown filter key or operator is a 400, and so is an operator on the wrong column type.
  • Groups nest and hold column, relation and collection filters: { or: [{ status: "a" }, { state: { name: "Ohio" } }], not: { tags: { name: "x" } } }. They also apply to count.

Relations

  • Declare relations in the entity config, then load with options.relations: ["state"]. A dotted path nests (["states.cities"]) and pulls in its parent.
    • Belongs-to: { table, localKey, references? }, LEFT JOINed into the main query, resolves to an object or null.
    • Has-many: { table, type: "hasMany", foreignKey, parentKey?, orderBy? }, resolves to an array.
    • Many-to-many: { table, type: "manyToMany", through, throughLocalKey, throughForeignKey, references?, parentKey?, orderBy? }, resolves to an array without the join key.
    • Nested: a relations map on the relation itself. parentKey defaults to the host table's primary key at every level.
  • Every collection and every nested level costs one query for the whole result, never one per row. Fifty countries with states and cities is four queries: data, count, states, cities.
  • A relation missing its required keys throws when the service is built, with the full path.
  • Relation filters use the same operators. A top-level belongs-to uses the join; deeper levels are EXISTS; collections are IN (SELECT ...) subqueries, so total and pagination stay right. A collection value may be { some }, { none } or { every }; every is true for a parent with no rows. filterableColumns takes the full dotted path.
  • options.select narrows columns, with dotted paths at any depth: ["id", "name", "region.code", "states.cities.name"]. The keys a nested level needs are added back automatically.
  • options.counts: ["posts"] adds _count: { posts: n }, one grouped query per relation; soft-deleted related rows are not counted.

Writes

  • create returns T. update returns T and is a 400 when nothing is left to set. softDelete and delete return a boolean. restore returns T. update, softDelete, restore and delete are a 404 for a missing row.
  • delete is a 404 for a row in the trash: purge with forceDelete(id) or massForceDelete(ids). softDelete on an entity with soft delete off is a 400.
  • Without writableColumns, create and update drop the soft delete and version columns from the body, and update also drops the primary key and createdAt. With it, any other column is a 400.
  • Writes take options.relations and options.counts and return the rows with them loaded.
  • An ISO string or a number for a date-mode timestamp / date / datetime column is turned into a Date on every write; a value that is not a date is a 400 naming the column. No DTO transform is needed for it.
  • Nested create needs writableRelations: ["posts"] and RETURNING: an array for has-many, an object for belongs-to, ids or objects for many-to-many, all in one transaction.
  • Use attach(id, "tags", ids), detach(id, "tags", ids?) and sync(id, "tags", ids) for many-to-many links, never raw inserts into the join table.
  • upsert(dto, { conflictTarget?, update? }, options?) and massUpsert(rows, ...): ON CONFLICT DO UPDATE on PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE on MySQL. A clash on another unique key is a 409.
  • Bulk methods (massCreate, massUpdate, massSoftDelete, massRestore, massDelete, massForceDelete, massUpsert) run in one transaction: one SELECT that checks the ids, plus one statement for the set. Any failure rolls back everything and throws BulkOperationException, a 400 with errors: [{ index | id, status, message }]. bulkMode: "perItem" runs the single-row method per item. A statement past the database's bound parameter limit (PostgreSQL and MySQL 65,535, SQLite 32,766, D1 100, counted on the real statement) is a 400 before it runs. On Cloudflare D1 and synchronous SQLite drivers, bulk writes, nested creates and attach / detach / sync throw TransactionException; use libSQL for them.

Errors

  • Every exception extends a Nest HttpException subclass, so no custom filter is needed: EntityNotFoundException 404, DuplicateEntityException 409, StaleVersionException 409, EntityInUseException 409, MissingScopeException 403, ForeignKeyViolationException 400, ValidationFailedException 400, BulkOperationException 400.
  • Postgres data errors (22001, 23514, 22003, 22P02, 22007, 22008, 21000), MySQL strict mode errors (1048, 1364, 1406, 1264, 1265, 1366, 1292, 3819) and SQLite NOT NULL / CHECK / STRICT type errors (3091) / SQLITE_MISMATCH become 400 on reads and writes. SQLite UNIQUE is a 409 and FOREIGN KEY a 400 or 409, as on the other dialects. TransactionException (500) means the driver cannot run a needed transaction. DatabaseConnectionException is deprecated; nothing throws it.
  • Full-text search: fullTextSearch(term, columns, pagination, { filters, relations, select, language, queryMode }), PostgreSQL only. language defaults to "english", queryMode: "websearch" uses websearch_to_tsquery. A 400 on MySQL and SQLite.

Controllers, tenancy, events, locking

  • Controllers: prefer class UsersController extends createCrudController(UsersService, { dto: { create, update } }) under @Controller("users"). Bulk routes are off until enabled in routes. The :id is parsed from the primary key column (ParseIntPipe for an integer, ParseUUIDPipe for a uuid) unless idType is set; do not add your own pipe. To change a route, turn it off in routes and add your own handler. deleteMode defaults to "hard".
  • Hand-written list routes: const q = this.usersService.parseQuery(query), then q.isCursor ? findByCursor(q.filters, q.pagination, q.options) : findAll(q.filters, q.pagination, q.options). withDeleted and onlyDeleted need allowTrash: true. parseCrudQuery(query, table, options) is the standalone form.
  • OpenAPI: @ApiCrudList({ table, ...entityConfig }) on a list route, or openapi in the controller options. Needs @nestjs/swagger.
  • Tenancy: set scope: (ctx) => ({ tenant_id: ctx.tenantId }) and provide the context with CrudContext.middleware((req) => ({ tenantId })), CrudContext.run(ctx, fn) or options.context. Never add a manual tenant filter or set the column in the body. Another tenant's id is a 404; a missing context value is a 403. skipScope: true only for trusted jobs. A scoped upsert works on PostgreSQL and SQLite and is a 400 on MySQL. A belongs-to key a write sets must point at a row of the same tenant when the related table has the scope columns; another tenant's row is a 400 ForeignKeyViolationException, the same as a missing one.
  • Events: hooks: { enableGlobalHooks: true } in forRoot, then inject CrudEventBus and subscribe({ afterWrite(e) { ... } }). One event per mass call (bulk: true). Use e.transaction for atomic audit rows. skipEvents: true skips events; hooks.skipBefore / skipAfter do not.
  • Optimistic locking: versionColumn: "version" (integer, starts at 1). Clients send the version they read in the update body or as expectedVersion; a stale one is a 409. Never write the version yourself. Mass writes take expectedVersions: { [String(id)]: version }.
  • Row locks: lock: "update" | "share" or forNoKeyUpdate: true on a read, as FOR ... OF <base table> on PostgreSQL, so they work with an eager belongs-to. On MySQL, FOR UPDATE or FOR SHARE (forNoKeyUpdate becomes FOR UPDATE). A 400 on SQLite.
  • Primary keys: the type comes from the Drizzle column. With uuid keys, route params are strings: never ParseIntPipe. On MySQL, supply uuid keys yourself on insert.

Quick reference

app.module.ts
DrizzleCrudModule.forRoot({
  dialect: "postgresql",
  connectionString: process.env.DATABASE_URL,
  schema,
});
users.module.ts
DrizzleCrudModule.forFeature([{ service: UsersService, table: users }]);
users.service.ts
await usersService.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: [{ state: { name: "Ohio" } }, { age: { gte: 65 } }],
  },
  { page: 1, limit: 20 },
  { relations: ["state"], search: { term: "john", columns: ["name", "email"] } },
);
users.controller.ts
@Get()
list(@Query() query: Record<string, unknown>) {
  const q = this.usersService.parseQuery(query);
  return q.isCursor
    ? this.usersService.findByCursor(q.filters, q.pagination, q.options)
    : this.usersService.findAll(q.filters, q.pagination, q.options);
}

Public exports

All 98, alphabetical, from the package root:

@avytrix/nestjs-drizzle-crud
and, ApiCrudList, ApiCrudQuery, ApiCrudQueryOptions, ApiCrudSource, asc,
BaseCrudSpecHelper, between, BulkMode, BulkOperationException,
BulkOperationItemError, BulkOperationResult, CollectionFilter,
CollectionQuantifier, ColumnFilter, CountByRow, createCrudController,
CrudAllowlists, CrudContext, CrudContextValue, CrudControllerInstance,
CrudControllerOptions, CrudEvent, CrudEventBus, CrudEventPhase, CrudFeature,
CrudFeatureConfig, CrudFilter, CrudPage, CrudRouteName, CrudRouteOptions,
CrudScope, CrudScopeValues, CrudService, CrudSubscriber, CrudWriteOperation,
CursorPage, CursorPaginationOptions, DatabaseConnectionException, desc,
DRIZZLE_CONNECTION, DRIZZLE_CRUD_CONFIG, DRIZZLE_DB, DrizzleConnection,
DrizzleCrudConfig, DrizzleCrudModule, DuplicateEntityException,
EntityInUseException, EntityNotFoundException, eq, FilterGroup,
FilterOperator, FilterOperators, FilterScalar, ForeignKeyViolationException,
gt, gte, ICrudService, ilike, inArray, isNotNull, isNull, like, lt, lte,
MissingScopeException, ne, not, notInArray, OperationOptions, or,
PaginationOptions, parseCrudQuery, ParseCrudQueryOptions, ParsedCrudQuery,
PrimaryKeyType, RelationConfig, RelationCounts, RelationNode, RelationsConfig,
RelationType, SelectTree, SortColumn, SortInput, SortOrder, sql,
SqlBaseCrudService, SqlCrudConfig, SqlDialect, SqlOperationOptions,
SqlSearchMode, SqlSearchOptions, StaleVersionException, TestCrudFactory,
TransactionException, UncountedCrudPage, UpsertOptions,
ValidationFailedException

What should generated code never do?

anti-patterns.ts
// Wrong: the db is not injected, and super() takes no hand-built config
class UsersService extends SqlBaseCrudService<User, CreateUserDto, UpdateUserDto, CrudFilter> {
  constructor(@Inject(DRIZZLE_DB) private db: any) {
    super({ dialect: "postgresql", db, table: users } as any);
  }
}

// Wrong: only one generic; all four are required
class UsersService extends SqlBaseCrudService<User> {}

// Wrong: treating the list envelope as an array
const rows = await this.usersService.findAll({});
rows.map((row) => row.id); // findAll returns { data, total, page, limit }

// Wrong: ParseIntPipe on a uuid primary key
@Get(":id")
find(@Param("id", ParseIntPipe) id: number) {}

// Wrong: a manual tenant filter on a scoped entity
this.usersService.findAll({ tenant_id: req.user.tenantId });

Sources of truth

  1. The TypeScript types: import type { ... } from "@avytrix/nestjs-drizzle-crud".
  2. The package README and AGENT.md on npm, also served by the MCP server.
  3. The source on GitHub.
  4. The Changelog, for behaviour changes release by release.

Common questions

Next steps

Last updated View change history

On this page