Nestjs DrizzleNestJS Drizzle filter and column allowlists
A NestJS Drizzle filter allowlist limits the columns a client may filter, sort, select, search and write. Anything outside a set list is a 400.
A NestJS Drizzle filter allowlist is a per-entity list of the columns a client may filter on. nestjs-drizzle-crud has one for each part of a request: filtering, sorting, selecting, searching, loading relations and writing columns. You set them in forFeature, and a request that names anything outside a set list is a 400 Bad Request, both in the service and in parseQuery. An unset list allows everything.
Set these on every entity a client can reach
Without allowlists, a list endpoint that forwards the query string lets a client filter, sort and select on every column of the table, secrets included.
How do I set the allowlists?
Add the keys to the entity's config in forFeature. Each one is a plain array of strings:
DrizzleCrudModule.forFeature([
{
service: UsersService,
table: users,
config: {
filterableColumns: ["status", "role", "created_at", "team.name"],
sortableColumns: ["name", "created_at", "team.name"],
selectableColumns: ["id", "name", "email", "team.name"],
searchableColumns: ["name", "email"],
loadableRelations: ["team", "projects.tasks"],
writableColumns: ["name", "email", "role", "team_id"],
},
},
]);filterableColumnsOptionalstring[]
sortableColumnsOptionalstring[]
selectableColumnsOptionalstring[]
searchableColumnsOptionalstring[]
loadableRelationsOptionalstring[]
writableColumnsOptionalstring[]
writableRelationsOptionalstring[]
Why should I set them?
Because every column you leave open is one a client can read or probe. Three examples:
| Missing list | What a client can do |
|---|---|
selectableColumns | ?fields=password_hash returns the hashes. |
filterableColumns | ?password_hash[startsWith]=a guesses a hash one character at a time. |
writableColumns | A body with role: "admin" sets it, unless every DTO strips it. |
filterableColumns also takes full dotted paths for deeper relation filters (state.country.code) and governs countBy. For nested writes, see Relations on writes.
What does an unset or empty list mean?
An unset list allows everything, which is how 3.3 behaved. An empty list ([]) allows nothing. The one exception is writableRelations: unset, nested writes are off.
What does a rejected request return?
A 400 whose message names the list and the path. The service throws a Nest BadRequestException for the read lists and a ValidationFailedException for writableColumns:
| List | Message |
|---|---|
filterableColumns | Filtering on "password_hash" is not allowed |
sortableColumns | Sorting by "email" is not allowed |
selectableColumns | Selecting "password_hash" is not allowed |
searchableColumns | Searching "email" is not allowed |
loadableRelations | Loading the relation "sessions" is not allowed |
writableColumns | Validation failed: the column "role" is not writable |
See Error handling in NestJS with Drizzle for the full response shape.
What is not checked?
findOne(where) keys are not checked against filterableColumns, because findOne is usually a server-side lookup (findOne({ email }) during login). Its select and relations options are checked. The configured defaultSort is not checked either.
For any other trusted, server-side call, pass skipAllowlists: true:
await this.users.count(
{ password_reset_token: token },
{ skipAllowlists: true },
);Which columns can a body write by default?
Everything except the columns the package manages itself. Without writableColumns:
- the soft delete column is ignored on create and update,
- the optimistic locking version column is ignored on create and update,
- the primary key and the
createdAttimestamp are ignored on update.
Ignored means dropped silently, so a PATCH body that echoes id back still works. An update that has nothing left to set is a 400 (Validation failed: there are no columns to update).
With writableColumns set, any column outside the list is a 400, the managed columns included. List the primary key there if your create path takes client supplied keys, such as uuids or natural keys. Keys that are not columns at all are ignored. The version column is never written from a body either way.
What is the recommended setup?
Validate bodies with DTOs
Keep ValidationPipe({ whitelist: true }) and real DTOs for request bodies. The allowlists are a second line, not a replacement.
Lock down writes
Set writableColumns on every entity with a public create or update route.
Lock down reads
Set selectableColumns and filterableColumns on every entity behind a list route, and leave secrets out of both.
Guard sensitive relations
Set loadableRelations when a related table holds data some callers must not see.
Common questions
For sorting and selecting, yes: every page sorts on it anyway. For filtering and writing, only when you list it.
No. An unset list allows everything, but an empty array allows nothing, so filterableColumns: [] turns filtering off for that entity.
Pass skipAllowlists: true in the call options. Use it only for trusted calls your own code builds, never for input from a request.
Yes. parseQuery rejects anything outside a set list with the same 400, and uses searchableColumns as the default search columns.
Next steps
NestJS query string filters for Drizzle
Turn a request query string into service options, checked against these lists.
Select columns with Drizzle in NestJS
Return only the columns a client asks for, relations included.
Multi-tenancy with Drizzle in NestJS
Scope every read and write to the tenant of the request.
Error handling in NestJS with Drizzle
What each 400, 404 and 409 looks like.
Row locks with SELECT FOR UPDATE
Drizzle SELECT FOR UPDATE in NestJS. Pass lock or forNoKeyUpdate to find, findOne or findAll inside a transaction to lock rows on PostgreSQL.
Multi-tenancy with Drizzle in NestJS
Build a NestJS multi tenant API on Drizzle with one scope key. Every read and write is limited to the caller's tenant; a missing tenant is a 403.