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.

sql-base-crud.service.ts
abstract class SqlBaseCrudService<
  T extends Record<string, any>,
  CreateDto,
  UpdateDto,
  FilterDto,
> implements ICrudService<T, CreateDto, UpdateDto, FilterDto> {
  constructor(protected readonly config: SqlCrudConfig);
}
GenericUsed by
TThe row type, usually typeof users.$inferSelect. Every method returns it.
CreateDtocreate, massCreate, upsert and massUpsert.
UpdateDtoupdate and massUpdate.
FilterDtofindAll, 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:

users.service.ts
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

MethodReturnsNotes
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.

users.service.ts
const  = .(1, { : ['state'] });
const  = .({ : 'ada@example.com' });
users.service.ts
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

MethodReturnsNotes
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.

MethodReturns
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.

MethodReturnsNotes
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

MethodReturnsNotes
parseQuery(query, options?)ParsedCrudQueryTurns 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.
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);
}

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.

MemberSignatureDefault
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) => numberHow 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() => stringThe Drizzle table name, used in error messages.
configSqlCrudConfigThe 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.

users.service.ts
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.

transactionOptional

any

A Drizzle transaction handle. The call runs inside it.
relationsOptional

string[]

Configured relations to eager-load. A dotted path nests: "state.country".
selectOptional

string[]

Columns to return. A dotted entry narrows a relation at any depth: "region.code".
search.termOptional

string

The search term for findAll and count.
search.columnsOptional

string[]

Table columns to search.
search.modeOptional

"ilike" | "fullText"

ilike ORs a contains match per column. fullText is PostgreSQL only.

Defaults to "ilike"

search.languageOptional

string

fullText only: the text search configuration, a plain identifier.

Defaults to "english"

search.queryModeOptional

"plain" | "websearch"

fullText only: plainto_tsquery or websearch_to_tsquery.

Defaults to "plain"

hooks.skipBeforeOptional

boolean

Skip the before hooks for this call.

Defaults to false

hooks.skipAfterOptional

boolean

Skip the after hooks for this call.

Defaults to false

lockOptional

"update" | "share" | "none"

Row lock on a read: FOR UPDATE OF or FOR SHARE OF the base table on PostgreSQL, FOR UPDATE or FOR SHARE on MySQL. A 400 on SQLite.
forNoKeyUpdateOptional

boolean

Lock with FOR NO KEY UPDATE on PostgreSQL, FOR UPDATE on MySQL. A 400 on SQLite.
withDeletedOptional

boolean

Also return soft-deleted rows.
onlyDeletedOptional

boolean

Return only soft-deleted rows (the trash). A 400 when soft delete is off.
skipAllowlistsOptional

boolean

Skip the entity's allowlists for a trusted, server-side call.
bulkModeOptional

"set" | "perItem"

Mass writes: one statement for the batch, or the single-row method per item.

Defaults to "set"

countsOptional

RelationCounts

Relation counts under _count: ["posts"] or { posts: { status: "draft" } }.
contextOptional

Record<string, unknown>

The context a function scope reads. Defaults to CrudContext.current().
skipScopeOptional

boolean

Run this trusted call without the entity's scope.
skipEventsOptional

boolean

Do not raise module-level events for this call.
expectedVersionOptional

number

Optimistic locking: the version the caller last read.
expectedVersionsOptional

Record<string, number>

Optimistic locking for mass writes, keyed by the id as a string.

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

Next steps

Last updated View change history

On this page