SqlBaseCrudService API reference
Every public method on SqlBaseCrudService in nestjs-drizzle-crud, with its signature, return type, the errors it throws and the options it takes.
SqlBaseCrudService is the abstract class every nestjs-drizzle-crud service extends, and its public methods are the whole CRUD API: reads, writes, bulk writes, search and three helpers for controllers. Each method takes an optional SqlOperationOptions object last, returns a promise of plain rows, and throws an HttpException subclass, so Nest answers with the right 4xx status.
All four generics are required
The class is SqlBaseCrudService<T, CreateDto, UpdateDto, FilterDto> and none of the four has a
default. Pass all of them, and use CrudFilter for FilterDto when the filters come from a query
string.
What does the class signature look like?
The class is generic over the row type and the three input shapes, and its constructor takes the merged entity config.
abstract class SqlBaseCrudService<
T extends Record<string, any>,
CreateDto,
UpdateDto,
FilterDto,
> implements ICrudService<T, CreateDto, UpdateDto, FilterDto> {
constructor(protected readonly config: SqlCrudConfig);
}| Generic | Used by |
|---|---|
T | The row type, usually typeof users.$inferSelect. Every method returns it. |
CreateDto | create, massCreate, upsert and massUpsert. |
UpdateDto | update and massUpdate. |
FilterDto | findAll, findByCursor, count and countBy. Use CrudFilter when filters come from parseQuery. |
You never call the constructor yourself. DrizzleCrudModule.forFeature builds the service and hands it the config, so a service is normally an empty subclass:
import { type CrudFilter, SqlBaseCrudService } from "@avytrix/nestjs-drizzle-crud";
import type { users } from "../db/schema";
import type { CreateUserDto, UpdateUserDto } from "./dto";
export type User = typeof users.$inferSelect;
export class UsersService extends SqlBaseCrudService<
User,
CreateUserDto,
UpdateUserDto,
CrudFilter
> {}Read
| Method | Returns | Notes |
|---|---|---|
find(id, options?) | Promise<T | null> | By primary key. null when the row is missing, soft-deleted or outside the scope. |
findOne(where, options?) | Promise<T | null> | where is a Partial<T>: a value is an exact, case-sensitive match, null is IS NULL, and an operator object works as in findAll. undefined values are skipped. A key that is not a column, or no condition left at all, is a 400 rather than an arbitrary row. |
findAll(filters?, pagination?, options?) | Promise<CrudPage<T>> | { data, total, page, limit }. With a literal withCount: false it returns UncountedCrudPage<T>: total is null and hasMore is set (skip the count). sortOrder defaults to "desc", and ORDER BY always ends with the primary key. An unknown filter key is a 400. |
findByCursor(filters?, pagination?, options?) | Promise<CursorPage<T>> | Keyset pagination: { data, limit, nextCursor, total? }. total only with count: true. See Cursor pagination. |
exists(id, options?) | Promise<boolean> | Runs find with only the primary key selected. |
count(filters?, options?) | Promise<number> | Rows matching the filters and options.search. No pagination. |
countBy(column, filters?, options?) | Promise<CountByRow<V>[]> | [{ value, count }] per value of a column or a belongs-to relation.column, largest group first. One GROUP BY. The column must be filterable. See Aggregates. |
Every read takes withDeleted or onlyDeleted to include or only return soft-deleted rows (Soft delete), and counts for _count on each row. On an entity with a scope, a read only sees the caller's rows.
const = .(1, { : ['state'] });
const = .({ : 'ada@example.com' });const page = await usersService.findAll(
{ status: "active" }, // filters
{ page: 1, limit: 20, sortBy: "-name" }, // pagination
{ relations: ["state"], search: { term: "john", columns: ["name"] } },
);
const total = await usersService.count(
{ status: "active" },
{ search: { term: "john", columns: ["name", "email"] } },
);Write
| Method | Returns | Notes |
|---|---|---|
create(data, options?) | Promise<T> | Runs validateCreate, beforeCreate, mapCreateDtoToEntity, the insert, then afterCreate. Sets the timestamps, the scope and the version. |
update(id, data, options?) | Promise<T> | 404 when the row is missing. 400 (ValidationFailedException) when data has no column to set. Runs validateUpdate, beforeUpdate, mapUpdateDtoToEntity, the update, then afterUpdate. |
softDelete(id, options?) | Promise<boolean> | Sets the soft delete column to now. 404 when the row is missing. 400 when soft delete is off for the entity. |
restore(id, options?) | Promise<T> | Clears the soft delete column. 404 when the row is missing. |
delete(id, options?) | Promise<boolean> | Hard delete. 404 for a missing row or a row in the trash; 409 (EntityInUseException) when other rows still reference it. |
forceDelete(id, options?) | Promise<boolean> | Hard delete whether or not the row is soft-deleted. Runs the delete hooks. |
upsert(data, upsert?, options?) | Promise<T> | ON CONFLICT DO UPDATE on Postgres, ON DUPLICATE KEY UPDATE on MySQL. upsert is UpsertOptions: { conflictTarget?, update? }. See Upsert. |
attach(id, relation, relatedIds, options?) | Promise<T> | Links many-to-many rows through the join table and returns the row with the relation loaded. See Relations on writes. |
detach(id, relation, relatedIds?, options?) | Promise<T> | Unlinks the ids, or every link when relatedIds is left out. |
sync(id, relation, relatedIds, options?) | Promise<T> | Makes the links exactly relatedIds. |
Writes take options.relations and options.counts and return the rows with them loaded. With a versionColumn, update, softDelete, restore and delete take expectedVersion (or the version in an update body) and answer a stale one with a 409. With global hooks on, every write raises one before and one after event.
create, update and upsert apply writableColumns. Without that list, both drop the soft delete and version columns from the body, and update also drops the primary key and the createdAt column.
Bulk
Every bulk method runs in one transaction (or joins options.transaction) and rolls back on any failure.
| Method | Returns |
|---|---|
massCreate(data[], options?) | Promise<T[]> |
massUpdate(ids[], data, options?) | Promise<T[]> |
massSoftDelete(ids[], options?) | Promise<boolean> |
massRestore(ids[], options?) | Promise<T[]> |
massDelete(ids[], options?) | Promise<boolean> |
massForceDelete(ids[], options?) | Promise<boolean> |
massUpsert(data[], upsert?, options?) | Promise<T[]> |
Each one is a single statement for the whole set, plus one SELECT that checks the ids exist for the methods that take ids. bulkMode: "perItem" runs the single-row method per item instead, each in its own savepoint. A failure throws BulkOperationException, a 400 whose body is { statusCode, error, message, errors: [{ index | id, status, message }] }. See Bulk operations.
Search
| Method | Returns | Notes |
|---|---|---|
fullTextSearch(term, columns, pagination?, options?) | Promise<{ data: T[]; total: number; page: number; limit: number }> | PostgreSQL only. to_tsvector('english', ...) @@ plainto_tsquery('english', ...) by default, ordered by ts_rank, then the primary key. options is FullTextSearchOptions: filters, relations, select and counts work as on findAll, language picks the configuration and queryMode: "websearch" uses websearch_to_tsquery. Page and limit always apply. An empty column list or an unknown column is a 400. Respects soft delete and scope. |
For a search inside a normal list, pass options.search to findAll or count instead. See Full-text search.
Controller helpers
| Method | Returns | Notes |
|---|---|---|
parseQuery(query, options?) | ParsedCrudQuery | Turns an HTTP query object into { filters, pagination, sort, options, isCursor, ... } with this entity's relations, primary key and allowlists. See Parse query strings. |
parseId(raw) | Promise<unknown> | Parses a route id by the primary key column: ParseIntPipe for an integer, ParseUUIDPipe for a uuid, BigInt for a bigint, the string otherwise. Bad input is a 400. |
idKind() | "int" | "bigint" | "uuid" | "string" | What the primary key column holds. |
@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);
}Protected members for subclasses
These are not callable from outside the service. Override the hooks in your subclass, and call the helpers from your own methods.
| Member | Signature | Default |
|---|---|---|
validateCreate | (data: CreateDto) => Promise<void> | Does nothing. Throw ValidationFailedException to reject. |
validateUpdate | (id, data: UpdateDto) => Promise<void> | Does nothing. |
beforeCreate | (data: CreateDto) => Promise<CreateDto> | Returns data. |
afterCreate | (entity: T) => Promise<void> | Does nothing. |
beforeUpdate | (id, data: UpdateDto) => Promise<UpdateDto> | Returns data. |
afterUpdate | (entity: T) => Promise<void> | Does nothing. |
beforeSoftDelete, afterSoftDelete | (id) => Promise<void> | Do nothing. |
beforeRestore | (id) => Promise<void> | Does nothing. |
afterRestore | (entity: T) => Promise<void> | Does nothing. |
beforeDelete, afterDelete | (id) => Promise<void> | Do nothing. |
mapCreateDtoToEntity | (data: CreateDto) => Record<string, any> | A shallow copy of data. |
mapUpdateDtoToEntity | (data: UpdateDto) => Record<string, any> | A shallow copy of data. |
executeSqlTransaction | <R>(operation: (tx) => Promise<R>, existingTransaction?) => Promise<R> | Runs operation in existingTransaction when given, otherwise in a new db.transaction. |
bindLimit | (db) => { limit: number; name: string } | The most bound parameters one statement may carry, and the database's name for the 400: PostgreSQL and MySQL 65,535, SQLite 32,766, Cloudflare D1 100. Since 3.7.2. |
relationChunkSize | (db) => number | How many parent keys one batched relation or count query binds: the bind limit less headroom, so 64,535 on PostgreSQL and MySQL, 31,766 on SQLite, 90 on D1. A level with more keys is loaded in several queries. Since 3.8.0. |
getEntityName | () => string | The Drizzle table name, used in error messages. |
config | SqlCrudConfig | The merged entity config, including db and table for hand-written queries. |
hooks: { skipBefore: true } or { skipAfter: true } in the options skips the before or after hooks for one call. See Hooks and validation.
const = .();The transaction commits when the callback resolves and rolls back when it throws. See Transactions.
SqlOperationOptions
The last argument of every method. Every key is optional.
transactionOptionalany
relationsOptionalstring[]
selectOptionalstring[]
search.termOptionalstring
search.columnsOptionalstring[]
search.modeOptional"ilike" | "fullText"
Defaults to "ilike"
search.languageOptionalstring
Defaults to "english"
search.queryModeOptional"plain" | "websearch"
Defaults to "plain"
hooks.skipBeforeOptionalboolean
Defaults to false
hooks.skipAfterOptionalboolean
Defaults to false
lockOptional"update" | "share" | "none"
forNoKeyUpdateOptionalboolean
withDeletedOptionalboolean
onlyDeletedOptionalboolean
skipAllowlistsOptionalboolean
bulkModeOptional"set" | "perItem"
Defaults to "set"
countsOptionalRelationCounts
contextOptionalRecord<string, unknown>
skipScopeOptionalboolean
skipEventsOptionalboolean
expectedVersionOptionalnumber
expectedVersionsOptionalRecord<string, number>
relations joins a top-level belongs-to into the main query and loads has-many, many-to-many and every nested level with one batched query each for the whole result, never one per row. See Relations and Column selection. The locks are described on Row-level locks.
Common questions
No. All four generics, T, CreateDto, UpdateDto and FilterDto, are required. A subclass that passes only the row type does not compile.
update, softDelete, restore and delete throw it (404) when the row is missing, and the mass versions report it per id inside a BulkOperationException. Reads never throw it: find and findOne return null and exists returns false.
It returns { data, total: null, page, limit, hasMore }. The service fetches one row past the page to set hasMore and skips the COUNT(*) query.
No, it is protected. Call it from a method on your service subclass, and pass { transaction: tx } to every CRUD call that should join the transaction.
Next steps
Config types
SqlCrudConfig, DrizzleCrudConfig and every type the methods take.
Exports
Everything you can import from the package root.
Errors
The HTTP status of every exception, per method.
Drizzle vs TypeORM vs Prisma for NestJS
Drizzle vs TypeORM vs Prisma for NestJS CRUD. How each defines the schema, queries, migrates and plugs into Nest, and which CRUD layers fit each.
Config types reference
The nestjs-drizzle-crud config types, DrizzleCrudConfig for forRoot and SqlCrudConfig per entity, with every option, default and type the methods take.