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
| Fact | Value |
|---|---|
| Name | @avytrix/nestjs-drizzle-crud on npm (renamed from nestjs-drizzle-crud in 3.5.1), version 3.8.3, MIT |
| Runtime dependencies | None |
| 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 peers | postgres >=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 |
| Binary | nestjs-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 templates | AGENTS.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.
DrizzleCrudModule.forRoot({ dialect, connectionString | db, schema?, defaults?, sql?, hooks? })once inAppModule(orforRootAsync({ imports?, inject?, useFactory })). It is global.DrizzleCrudModule.forFeature([{ service, table, config? }])in each feature module. It builds each service and exports it.
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. UseCrudFilterforFilterDtowhen filters come from a query string. - A service is an empty subclass. No
@Injectable(), no constructor, no@Inject(DRIZZLE_DB):forFeaturecallsnew 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 inforFeatureconfigor@CrudService(config);forFeaturewins. - For a hand-written query inside a service, use
this.config.dbandthis.config.table. Elsewhere, injectDRIZZLE_DB. - Override protected hooks for custom logic:
validateCreate,validateUpdate,beforeCreate,afterCreate,beforeUpdate,afterUpdate,beforeSoftDelete,afterSoftDelete,beforeRestore,afterRestore,beforeDelete,afterDelete,mapCreateDtoToEntity,mapUpdateDtoToEntity. Create runsvalidateCreate, thenbeforeCreate, thenmapCreateDtoToEntity. 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: trueondeleted_at,timestamps: trueoncreated_atandupdated_at,pagination: { defaultLimit: 20, maxLimit: 100 },primaryKey: "id",sql.caseSensitive: false,sql.useReturningtrue on PostgreSQL and SQLite and forced off on MySQL.connection: { poolSize, idleTimeout, ssl }tunes a connection built fromconnectionString.forRootAsynctakesuseFactory,useClassoruseExisting(a class withcreateDrizzleCrudOptions()). - Tables without those columns need
defaults: { softDelete: false, timestamps: false }.softDelete.column,timestamps.createdAt/updatedAtandversionColumnname the key in the Drizzle table object (deletedAtfordeletedAt: timestamp("deleted_at")); a key the table lacks throws at startup with the key to use. softDelete,timestamps,paginationandsqlmerge one level deep acrossforRoot,@CrudServiceandforFeature:pagination: { defaultLimit: 5 }keepsmaxLimit. Arrays (allowlists) replace.connectionStringis PostgreSQL only andschemais optional.primaryKeyType,sql.jsonSupportandsql.enableFullTextSearchare deprecated no-ops.
Reads
findAll(filters?, pagination?, options?)returns{ data, total, page, limit }, never a bare array. WithwithCount: false(or?withCount=false) it skipsCOUNT(*):totalisnullandhasMoreis a boolean.findAllalways endsORDER BYwith the primary key ascending.sortByis one column (direction fromsortOrder, 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.pagebelow 1 becomes 1 andlimitis clamped to 1 tomaxLimit. A badsortOrderor unknown sort column is a 400.findByCursor(filters, { cursor, limit, sortBy, sortOrder, count }, options)is keyset pagination:{ data, limit, nextCursor, total? }. PassnextCursorback ascursor;countdefaults to false. A cursor made for another sort is a 400.find(id)andfindOne(where)returnT | null;exists(id)returns a boolean.findOneis a 400 when no condition is left (every valueundefined) or a key is not a column;nullmeansIS 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 onfindAllandcount.modedefaults 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: trueoronlyDeleted: trueon any read. A soft-deleted belongs-to parent comes back asnulland 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,nullandundefinedare 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. likeandiliketake your own%wildcards (ilikeisLIKEon MySQL).startsWith,endsWithandcontainsescape%,_and\and followsql.caseSensitive; theiforms are always case-insensitive.arrayContains,arrayOverlapsandjsonContainsare PostgreSQL only.- A bare string is an exact match, case-insensitive on text columns while
caseSensitiveis false.{ isNull: false }meansIS 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 tocount.
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 ornull. - 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
relationsmap on the relation itself.parentKeydefaults to the host table's primary key at every level.
- Belongs-to:
- 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 areIN (SELECT ...)subqueries, sototaland pagination stay right. A collection value may be{ some },{ none }or{ every };everyis true for a parent with no rows.filterableColumnstakes the full dotted path. options.selectnarrows 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
createreturnsT.updatereturnsTand is a 400 when nothing is left to set.softDeleteanddeletereturn a boolean.restorereturnsT.update,softDelete,restoreanddeleteare a 404 for a missing row.deleteis a 404 for a row in the trash: purge withforceDelete(id)ormassForceDelete(ids).softDeleteon 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 andcreatedAt. With it, any other column is a 400. - Writes take
options.relationsandoptions.countsand return the rows with them loaded. - An ISO string or a number for a date-mode
timestamp/date/datetimecolumn is turned into aDateon 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"]andRETURNING: 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?)andsync(id, "tags", ids)for many-to-many links, never raw inserts into the join table. upsert(dto, { conflictTarget?, update? }, options?)andmassUpsert(rows, ...):ON CONFLICT DO UPDATEon PostgreSQL and SQLite,ON DUPLICATE KEY UPDATEon MySQL. A clash on another unique key is a 409.- Bulk methods (
massCreate,massUpdate,massSoftDelete,massRestore,massDelete,massForceDelete,massUpsert) run in one transaction: oneSELECTthat checks the ids, plus one statement for the set. Any failure rolls back everything and throwsBulkOperationException, a 400 witherrors: [{ 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 throwTransactionException; use libSQL for them.
Errors
- Every exception extends a Nest
HttpExceptionsubclass, so no custom filter is needed:EntityNotFoundException404,DuplicateEntityException409,StaleVersionException409,EntityInUseException409,MissingScopeException403,ForeignKeyViolationException400,ValidationFailedException400,BulkOperationException400. - Postgres data errors (
22001,23514,22003,22P02,22007,22008,21000), MySQL strict mode errors (1048, 1364, 1406, 1264, 1265, 1366, 1292, 3819) and SQLiteNOT NULL/CHECK/STRICTtype errors (3091) /SQLITE_MISMATCHbecome 400 on reads and writes. SQLiteUNIQUEis a 409 andFOREIGN KEYa 400 or 409, as on the other dialects.TransactionException(500) means the driver cannot run a needed transaction.DatabaseConnectionExceptionis deprecated; nothing throws it. - Full-text search:
fullTextSearch(term, columns, pagination, { filters, relations, select, language, queryMode }), PostgreSQL only.languagedefaults to"english",queryMode: "websearch"useswebsearch_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 inroutes. The:idis parsed from the primary key column (ParseIntPipefor an integer,ParseUUIDPipefor a uuid) unlessidTypeis set; do not add your own pipe. To change a route, turn it off inroutesand add your own handler.deleteModedefaults to"hard". - Hand-written list routes:
const q = this.usersService.parseQuery(query), thenq.isCursor ? findByCursor(q.filters, q.pagination, q.options) : findAll(q.filters, q.pagination, q.options).withDeletedandonlyDeletedneedallowTrash: true.parseCrudQuery(query, table, options)is the standalone form. - OpenAPI:
@ApiCrudList({ table, ...entityConfig })on a list route, oropenapiin the controller options. Needs@nestjs/swagger. - Tenancy: set
scope: (ctx) => ({ tenant_id: ctx.tenantId })and provide the context withCrudContext.middleware((req) => ({ tenantId })),CrudContext.run(ctx, fn)oroptions.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: trueonly 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 400ForeignKeyViolationException, the same as a missing one. - Events:
hooks: { enableGlobalHooks: true }inforRoot, then injectCrudEventBusandsubscribe({ afterWrite(e) { ... } }). One event per mass call (bulk: true). Usee.transactionfor atomic audit rows.skipEvents: trueskips events;hooks.skipBefore/skipAfterdo not. - Optimistic locking:
versionColumn: "version"(integer, starts at 1). Clients send the version they read in the update body or asexpectedVersion; a stale one is a 409. Never write the version yourself. Mass writes takeexpectedVersions: { [String(id)]: version }. - Row locks:
lock: "update" | "share"orforNoKeyUpdate: trueon a read, asFOR ... OF <base table>on PostgreSQL, so they work with an eager belongs-to. On MySQL,FOR UPDATEorFOR SHARE(forNoKeyUpdatebecomesFOR 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
DrizzleCrudModule.forRoot({
dialect: "postgresql",
connectionString: process.env.DATABASE_URL,
schema,
});DrizzleCrudModule.forFeature([{ service: UsersService, table: users }]);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"] } },
);@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:
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,
ValidationFailedExceptionWhat should generated code never do?
// 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
- The TypeScript types:
import type { ... } from "@avytrix/nestjs-drizzle-crud". - The package README and
AGENT.mdon npm, also served by the MCP server. - The source on GitHub.
- The Changelog, for behaviour changes release by release.
Common questions
No. All four, T, CreateDto, UpdateDto and FilterDto, are required. SqlBaseCrudService<User> alone does not compile.
An optional one, since 3.7.0. Install it when you pass connectionString with dialect: "mysql"; the module loads it only then. If you build the Drizzle instance yourself and pass it as db, the module never touches it.
No. forFeature constructs the service with a factory and passes the merged config to the base constructor. A decorator or an extra constructor parameter breaks registration.
One for the data, one for the count, and one per collection or nested level for the whole result set. A top-level belongs-to rides in the main query as a LEFT JOIN.
Next steps
AI agents and MCP
Run the MCP server and add the rules templates to your agent.
SqlBaseCrudService API reference
Every method, its signature and what it throws.
Package exports reference
Every export, grouped and explained.
Package exports reference
Every export of nestjs-drizzle-crud, the classes, functions, decorators, DI tokens, exceptions, types and Drizzle helpers you can import from the package root.
Changelog
The nestjs-drizzle-crud changelog, every release since 3.0.0 with what was added, changed, fixed and deprecated, newest first, mirroring CHANGELOG.md.