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.

nestjs-drizzle-crud is configured through two types: DrizzleCrudConfig, the project config you pass to forRoot or forRootAsync, and SqlCrudConfig, the merged per-entity config each service runs on. The module builds the second from the first, then layers @CrudService metadata and the forFeature config on top, so an entity only names the settings it changes.

Column settings take the table key

softDelete.column, timestamps.createdAt, timestamps.updatedAt, versionColumn and the keys of a fixed scope name the key in your Drizzle table object (deletedAt for deletedAt: timestamp("deleted_at")), not the SQL name. A key the table lacks throws when the service is built, with the key to use.

DrizzleCrudConfig

The input to DrizzleCrudModule.forRoot(config), and what the forRootAsync factory returns. It holds the connection and the project defaults.

dialectRequired

"postgresql" | "mysql" | "sqlite"

The SQL dialect. sqlite covers SQLite, libSQL and Cloudflare D1.
dbOptional

any

A Drizzle instance you built. Required for sqlite. The module uses it as is and never closes it.
connectionStringOptional

string

PostgreSQL (postgres) and MySQL (mysql2). The module opens the connection and closes it on shutdown.
connectionOptional

DrizzleConnectionOptions

poolSize, idleTimeout (seconds) and ssl for the connection built from connectionString. Ignored with db.
schemaOptional

Record<string, unknown>

Optional. Passed to drizzle() when the module builds the connection from connectionString.
defaults.softDeleteOptional

boolean

Soft delete on the deleted_at column for every entity.

Defaults to true

defaults.timestampsOptional

boolean

Set created_at and updated_at on every entity.

Defaults to true

defaults.pagination.defaultLimitOptional

number

Page size when the caller passes no limit.

Defaults to 20

defaults.pagination.maxLimitOptional

number

The largest limit a caller gets. Larger values are clamped.

Defaults to 100

defaults.sortOrderOptional

"asc" | "desc"

Opt-in. Entities without a defaultSort sort by their createdAt column in this direction. Unset, there is no fallback sort.
sql.caseSensitiveOptional

boolean

Exact string matches and startsWith, endsWith, contains follow this.

Defaults to false

sql.useReturningOptional

boolean

Read written rows back with RETURNING. Forced off on MySQL.

Defaults to dialect !== "mysql"

sql.jsonSupportOptional

boolean

Deprecated. Has no effect.
sql.enableFullTextSearchOptional

boolean

Deprecated. Has no effect: full-text search works on PostgreSQL either way.
hooks.enableGlobalHooksOptional

boolean

Every service raises a before and an after event per write on the CrudEventBus.

Defaults to false

hooks.subscribersOptional

CrudSubscriber[]

Subscribed when the bus is created. Without enableGlobalHooks they never fire, and the module logs a warning.

Provide one of db or connectionString; with neither, startup fails. A connectionString with dialect: "sqlite" also fails, because only PostgreSQL and MySQL connections are built for you.

drizzle-crud-config.interface.ts
interface DrizzleConnectionOptions {
  poolSize?: number;    // postgres-js max, mysql2 connectionLimit
  idleTimeout?: number; // seconds: postgres-js idle_timeout, mysql2 idleTimeout x 1000 (and maxIdle)
  ssl?: boolean | "require" | "allow" | "prefer" | "verify-full" | Record<string, unknown>;
}

interface DrizzleCrudOptionsFactory {
  createDrizzleCrudOptions(): DrizzleCrudConfig | Promise<DrizzleCrudConfig>;
}

interface DrizzleCrudAsyncOptions {
  imports?: any[];
  inject?: any[];
  useFactory?: (...args: any[]) => DrizzleCrudConfig | Promise<DrizzleCrudConfig>;
  useClass?: Type<DrizzleCrudOptionsFactory>;
  useExisting?: Type<DrizzleCrudOptionsFactory>;
}
``` See [NestJS Drizzle module forRoot options](/docs/for-root) and [Drizzle forRootAsync with ConfigService](/docs/for-root-async).

`sql.jsonSupport`, `sql.enableFullTextSearch` and `primaryKeyType` are deprecated and have no effect. They still compile so existing configs keep working, and they will be removed in the next major version. `hooks.enableGlobalHooks` was deprecated as a no-op in 3.3.1 and works again since 3.5.0: it turns on [write events](/docs/events).

## How is `SqlCrudConfig` built?

Each entity's config is the project defaults with three layers on top, each overriding the one before:

| Layer | Source |
| --- | --- |
| 1 | `forRoot` / `forRootAsync` defaults, after the module fills in its own |
| 2 | `@CrudService(config)` decorator metadata on the service class |
| 3 | The `config` object in `forFeature` |
| 4 | `db` and `table`, set by `forFeature` |

`softDelete`, `timestamps`, `pagination` and `sql` merge one level deep at every layer, so `pagination: { defaultLimit: 5 }` keeps the project `maxLimit`. Every other key, the allowlist arrays included, replaces the value from the layer before. `timestamps: undefined` turns timestamps off for that entity.

## `SqlCrudConfig`

The resolved per-entity config. It extends `CrudAllowlists`. In `forFeature` you pass the optional `CrudFeatureConfig` form of it, and the module fills in `dialect`, `db` and `table`.

<PropsTable
  props={{
    dialect: { type: '"postgresql" | "mysql" | "sqlite"', required: true, description: "Set by the module from forRoot." },
    db: { type: "any", required: true, description: "The Drizzle instance. Set by the module." },
    table: { type: "any", required: true, description: "The Drizzle table. Set by forFeature." },
    primaryKey: { type: "string", default: '"id"', description: "The table key of the primary key column. Its type comes from the column." },
    primaryKeyType: { type: "PrimaryKeyType", description: "Deprecated. Has no effect." },
    "softDelete.enabled": { type: "boolean", default: "true", description: "Reads hide rows whose soft delete column is set, and softDelete sets it." },
    "softDelete.column": { type: "string", default: '"deleted_at"', description: "The table key of the soft delete column." },
    "timestamps.createdAt": { type: "string", default: '"created_at"', description: "Set on create." },
    "timestamps.updatedAt": { type: "string", default: '"updated_at"', description: "Set on create and on every update." },
    "pagination.defaultLimit": { type: "number", default: "20", description: "Page size when the caller passes no limit." },
    "pagination.maxLimit": { type: "number", default: "100", description: "Upper bound for limit." },
    defaultSort: { type: "SortColumn[]", description: "ORDER BY when the caller passes no sortBy, applied in order. An unknown column is skipped with a warning." },
    "sql.caseSensitive": { type: "boolean", default: "false", description: "As in DrizzleCrudConfig." },
    "sql.useReturning": { type: "boolean", default: "true on postgresql and sqlite", description: "Forced off on MySQL when the service is built." },
    relations: { type: "RelationsConfig", description: "Relations by name: belongs-to, has-many, many-to-many, nested. See RelationConfig below." },
    filterableColumns: { type: "string[]", description: "Columns and \"relation.column\" paths a filter may use. Unset allows all." },
    sortableColumns: { type: "string[]", description: "Columns and \"belongsTo.column\" paths a caller may sort by." },
    selectableColumns: { type: "string[]", description: "Columns and dotted paths select may name." },
    searchableColumns: { type: "string[]", description: "Columns search and fullTextSearch may use." },
    loadableRelations: { type: "string[]", description: "Relation paths relations and counts may load. \"states.cities\" also allows \"states\"." },
    writableColumns: { type: "string[]", description: "Columns create, update and upsert may write. Anything else is a 400." },
    writableRelations: { type: "string[]", description: "Relations a create body may nest, and attach, detach and sync may change. Unset, nested create is off." },
    scope: { type: "CrudScope", description: "column: value pairs ANDed into every read and write and set on create. A function reads the call's context." },
    versionColumn: { type: "string", description: "An integer column for optimistic locking. Starts at 1, plus one on every write." },
    events: { type: "CrudEventBus", description: "Set by the module when hooks.enableGlobalHooks is on. Pass your own when you build a service by hand." },
  }}
/>

An allowlist that is unset allows everything; a request that names something outside a set list is a 400. See [Allowlists](/docs/allowlists), [Multi-tenancy](/docs/multi-tenancy), [Optimistic locking](/docs/optimistic-locking) and [Global hooks and write events](/docs/events).

## `CrudFeature` and `CrudFeatureConfig`

`forFeature` takes an array of `CrudFeature`, one per entity.

```ts title="drizzle-crud-config.interface.ts"
interface CrudFeature {
  service: new (...args: any[]) => any; // your SqlBaseCrudService subclass
  table: any;                           // the Drizzle table
  config?: CrudFeatureConfig;           // per-entity overrides
}

type CrudFeatureConfig = Partial<
  Omit<SqlCrudConfig, "softDelete" | "timestamps" | "pagination" | "sql">
> & {
  softDelete?: Partial<NonNullable<SqlCrudConfig["softDelete"]>>;
  timestamps?: Partial<NonNullable<SqlCrudConfig["timestamps"]>>;
  pagination?: Partial<NonNullable<SqlCrudConfig["pagination"]>>;
  sql?: Partial<NonNullable<SqlCrudConfig["sql"]>>;
};

Every key of CrudFeatureConfig is optional. Before 3.3.1, config was typed Record<string, any>, so a typo or a half-filled pagination compiled. See forFeature.

users.module.ts
DrizzleCrudModule.forFeature([
  {
    service: UsersService,
    table: users,
    config: {
      softDelete: { column: "deletedAt" },
      pagination: { defaultLimit: 50 },
      filterableColumns: ["name", "status", "state.name"],
    },
  },
]);

RelationConfig

One entry of relations. RelationsConfig is Record<string, RelationConfig> and RelationType is "belongsTo" | "hasMany" | "manyToMany".

tableRequired

any

The related Drizzle table.
typeOptional

RelationType

belongsTo resolves to an object or null; hasMany and manyToMany to an array.

Defaults to "belongsTo"

localKeyOptional

string

belongsTo, required: the foreign key column on this table.
referencesOptional

string

belongsTo and manyToMany: the referenced column on the related table.

Defaults to "id"

foreignKeyOptional

string

hasMany, required: the column on the related table that points back here.
parentKeyOptional

string

hasMany and manyToMany: the column on this table being pointed at. Defaults to the host table's primary key.
throughOptional

any

manyToMany, required: the join table.
throughLocalKeyOptional

string

manyToMany, required: the join column matching parentKey.
throughForeignKeyOptional

string

manyToMany, required: the join column matching references.
orderByOptional

SortColumn[]

hasMany and manyToMany: how to order the collection, in SQL.
relationsOptional

RelationsConfig

Relations of the related table, loadable as "relName.childName".

A relation missing its required keys throws when the service is built, nested relations included, with the full path in the message. See Relations.

RelationNode and SelectTree

The resolved eager-loading plan and the parsed options.select. Both are exported for subclasses that override the loader; normal use never touches them.

sql-crud-config.interface.ts
interface RelationNode {
  name: string;
  config: RelationConfig;
  children: RelationNode[];
  joined?: boolean; // folded into the main query as a LEFT JOIN
}

interface SelectTree {
  base: string[];
  relations: Record<string, SelectTree>;
}

SqlOperationOptions and SqlSearchOptions

SqlOperationOptions is the last argument of every method; its keys are listed on the API reference. Its search key is a SqlSearchOptions:

sql-crud-config.interface.ts
type SqlSearchMode = "ilike" | "fullText";
type FullTextQueryMode = "plain" | "websearch";

interface FullTextOptions {
  language?: string;             // default "english"
  queryMode?: FullTextQueryMode; // default "plain"
}

interface SqlSearchOptions extends FullTextOptions {
  term: string;
  columns: string[];
  mode?: SqlSearchMode; // default "ilike"
}

// The options of fullTextSearch()
interface FullTextSearchOptions<FilterDto = any>
  extends SqlOperationOptions, FullTextOptions {
  filters?: FilterDto;
}

ilike matches each column with an escaped contains pattern and joins them with OR (plain LIKE on MySQL and SQLite). fullText is PostgreSQL only and compiles to to_tsvector(...) @@ plainto_tsquery(...), or websearch_to_tsquery(...) with queryMode: "websearch". language must be a plain identifier, optionally schema qualified; anything else is a 400.

PaginationOptions

The second argument of findAll and fullTextSearch.

pageOptional

number

1-based. Values below 1 are clamped to 1.

Defaults to 1

limitOptional

number

Clamped to 1 to pagination.maxLimit.

Defaults to pagination.defaultLimit

sortByOptional

SortInput

One column, a list, or a comma separated string. A - prefix sorts descending.
sortOrderOptional

"asc" | "desc"

Direction for a single unprefixed sortBy column.

Defaults to "asc"

withCountOptional

boolean

false skips the COUNT(*): total is null and hasMore is set.

Defaults to true

Filter, sort, cursor and upsert types

Added in 3.4.0. See Filtering, Pagination and sorting, Cursor pagination and Upsert.

query.interface.ts
type FilterScalar = string | number | boolean | bigint | Date;
type ColumnFilter = FilterScalar | FilterScalar[] | FilterOperators | null | undefined;

interface FilterOperators {
  eq?: unknown; neq?: unknown; gt?: unknown; gte?: unknown; lt?: unknown; lte?: unknown;
  like?: string; ilike?: string;
  in?: unknown[]; notIn?: unknown[]; between?: [unknown, unknown];
  isNull?: boolean | "true" | "false"; isNotNull?: boolean | "true" | "false";
  startsWith?: string; endsWith?: string; contains?: string;
  iStartsWith?: string; iEndsWith?: string; iContains?: string;
  arrayContains?: unknown[]; arrayOverlaps?: unknown[]; jsonContains?: unknown;
}
type FilterOperator = keyof FilterOperators;

interface FilterGroup { and?: CrudFilter[]; or?: CrudFilter[]; not?: CrudFilter }
type CrudFilter = FilterGroup & {
  [key: string]: ColumnFilter | CrudFilter | CrudFilter[] | CollectionFilter;
};

type SortOrder = "asc" | "desc";
interface SortColumn { column: string; order?: SortOrder } // order defaults to "asc"
type SortInput = string | Array<string | SortColumn>;

interface CursorPaginationOptions {
  cursor?: string | null;
  after?: string | null; // alias of cursor
  limit?: number;
  sortBy?: SortInput;
  sortOrder?: SortOrder;
  count?: boolean;       // default false
}
interface CursorPage<T> { data: T[]; limit: number; nextCursor: string | null; total?: number }

interface UpsertOptions { conflictTarget?: string | string[]; update?: string[] }
type BulkMode = "set" | "perItem";

CrudAllowlists is the seven allowlist keys of SqlCrudConfig, from filterableColumns to writableColumns. ParseCrudQueryOptions extends it with relations, primaryKey, searchColumns, allowTrash and ignore; it and ParsedCrudQuery are described on Parse query strings into filters.

Page, count, scope, event and controller types

Added in 3.5.0. See Relation filters, Aggregates, Multi-tenancy, Global hooks and write events, CRUD controller factory and OpenAPI docs.

query.interface.ts
interface CollectionFilter { some?: CrudFilter; none?: CrudFilter; every?: CrudFilter }
type CollectionQuantifier = keyof CollectionFilter;

interface CrudPage<T, Total extends number | null = number> {
  data: T[]; total: Total; page: number; limit: number; hasMore?: boolean;
}
interface UncountedCrudPage<T> extends CrudPage<T, null> { hasMore: boolean }

type RelationCounts = string[] | Record<string, true | CrudFilter>;
interface CountByRow<V = unknown> { value: V; count: number }

type CrudContextValue = Record<string, unknown>;
type CrudScopeValues = Record<string, unknown>;
type CrudScope = CrudScopeValues | ((context: CrudContextValue) => CrudScopeValues | null);

type CrudWriteOperation = "create" | "update" | "upsert" | "softDelete" | "restore"
  | "delete" | "attach" | "detach" | "sync";
type CrudEventPhase = "before" | "after";
interface CrudEvent<T = any> {
  entity: string; operation: CrudWriteOperation; phase: CrudEventPhase; bulk: boolean;
  ids: unknown[]; input?: unknown; rows: T[]; transaction?: unknown; context?: CrudContextValue;
}
interface CrudSubscriber<T = any> {
  entities?: string[];
  beforeWrite?(e: CrudEvent<T>): void | Promise<void>;
  afterWrite?(e: CrudEvent<T>): void | Promise<void>;
  // plus before and after for Create, Update, Upsert, SoftDelete, Restore,
  // Delete, Attach, Detach and Sync: beforeCreate, afterCreate, ...
}

type CrudRouteName = "list" | "trash" | "get" | "create" | "update" | "delete"
  | "softDelete" | "restore" | "bulkCreate" | "bulkUpdate" | "bulkDelete" | "bulkRestore";
interface CrudRouteOptions { enabled?: boolean; decorators?: MethodDecorator[] }
interface CrudControllerOptions {
  path?: string;
  idType?: "int" | "uuid" | "string";
  dto?: { create?: Type<unknown>; update?: Type<unknown> };
  query?: ParseCrudQueryOptions;
  routes?: Partial<Record<CrudRouteName, boolean | CrudRouteOptions>>;
  decorators?: MethodDecorator[];
  classDecorators?: ClassDecorator[];
  deleteMode?: "hard" | "soft"; // default "hard"
  openapi?: true | ApiCrudSource;
}
interface CrudControllerInstance<S> { readonly service: S }

type ApiCrudSource =
  | (ParseCrudQueryOptions & { table: any })
  | (abstract new (...args: any[]) => unknown);
interface ApiCrudQueryOptions { cursor?: boolean; trash?: boolean }

Service interface types

ICrudService<T, CreateDto, UpdateDto, FilterDto> is the interface SqlBaseCrudService implements: find, findOne, findAll, create, update, softDelete, restore, delete, the five mass methods, exists and count. It types options as OperationOptions, the older subset of SqlOperationOptions (transaction, relations, select, search, hooks).

crud-service.interface.ts
interface BulkOperationResult<T> {
  success: boolean;
  data?: T[];
  errors?: Array<{ id?: any; index?: number; error: Error }>;
}

type SqlDialect = "postgresql" | "mysql" | "sqlite";
type PrimaryKeyType = "serial" | "bigserial" | "int" | "bigint" | "uuid"; // deprecated setting

Common questions

Next steps

Last updated View change history

On this page