Select columns with Drizzle in NestJS
Select columns in NestJS with Drizzle: pass options.select to return only the columns you need, and use dotted paths to narrow eager-loaded relations.
To select columns with Drizzle in NestJS, pass options.select with the column names you want, and the query stops being SELECT *. It works on find, findOne, findAll and findByCursor. A dotted entry such as 'region.code' narrows an eager-loaded relation, at any depth. Leave select out and every column comes back, which is the default.
Names are table keys
Each entry is the key in your Drizzle table object, not the SQL column name. The examples use a countries table with a belongs-to region and a has-many states, served by a CountriesService.
How do I return only some columns?
List them in select, the third argument of findAll (the second of find and findOne).
await this.countriesService.findAll({}, { page: 1, limit: 20 }, { select: ['id', 'name'] });
// SELECT "id", "name" FROM "countries" ...
// { data: [{ id: 1, name: 'India' }, ...], total, page, limit }
await this.countriesService.find(1, { select: ['id', 'name'] });
// { id: 1, name: 'India' }Over HTTP, parseQuery reads ?fields=id,name into options.select.
Which columns can I ask for?
Any column of the table, or a dotted path into a relation you also load.
| Entry | Meaning |
|---|---|
'name' | A column on this entity's table. |
'region.code' | The code column of the eager-loaded region relation. |
'states.name' | The name column of every child in the has-many states relation. |
'states.cities.name' | The name column one nesting level down. Paths can be any depth. |
A dotted entry only takes effect when the relation is also listed in options.relations. Without it, nothing is loaded and the entry does nothing.
What happens with a name that is not a column?
The service skips it rather than failing, so a typo returns fewer columns instead of an error. Two things make it a 400 instead:
- a
selectableColumnsallowlist that does not list the name, parseQuery, which checks every?fields=entry against the table and its relations.
How do I narrow a relation?
Load the relation with relations and name its columns with dotted entries in select.
await this.countriesService.findAll(
{},
{ page: 1, limit: 20 },
{
relations: ['region', 'states'],
select: ['id', 'name', 'region.code', 'states.name'],
},
);
// {
// id: 1,
// name: 'India',
// region: { code: 'AS' },
// states: [{ name: 'Karnataka', country_id: 1 }],
// }Why do some columns come back that I did not ask for?
The loader needs some keys to attach children to their parents, so it adds them back:
| Key added | When |
|---|---|
The parent key on the base row (the configured primaryKey, or the relation's parentKey when set) | A has-many or many-to-many relation is loaded, or counted with counts. |
| The foreign key on each has-many child row | Always, because it maps the child back to its parent. |
| The key a nested relation hangs on | A relation of a relation is loaded. |
So select: ['name', 'states.name'] still returns id on the country and country_id on each state. The same applies at every nesting level: select: ['name', 'states.name', 'states.cities.name'] keeps states.id too, because the cities load needs it. Everything else you did not ask for is left out of the SQL.
A top-level belongs-to such as region rides along in the main query as a LEFT JOIN, so its key appears only in the ON clause and nothing is added to the base row.
What if I only list relation columns?
Then the base row is not narrowed at all.
await this.countriesService.findAll({}, undefined, {
relations: ['region'],
select: ['region.name'],
});
// full country row + { region: { name } }That way select: ['region.name'] reads as "narrow the region", not "drop every country column".
When is column selection worth it?
When the rows are wide or the endpoint is hot.
| Case | Why it helps |
|---|---|
| Wide tables | Skipping a large text or jsonb column you do not render is the cheapest win there is. |
| List endpoints | A table view usually needs three columns out of fifteen. |
| Hot paths | Less data over the wire and less to hydrate, and a covering index can serve the whole query. |
exists(id) already uses this internally: it selects only the primary key.
Writes return the full row
select does not apply to create, update or restore. They return the full row through RETURNING, and relations loaded on a write come back whole.
Common questions
Yes. findByCursor takes the same options.select as findAll, and the cursor is still built from the sort columns even when you leave them out of select.
The loader groups has-many children by their foreign key, so it always selects it. Remove it in your controller or DTO if the client should not see it.
Not in the service: it is skipped. It is a 400 when a selectableColumns allowlist is set, or when the name comes through parseQuery as ?fields=.
Yes. Use a dotted path of any depth, such as states.cities.name, and load the same path with relations.
Next steps
Relations
Eager loading, nested paths and the batched has-many query.
Pagination and sorting with Drizzle
The rest of the read options for findAll.
NestJS Drizzle filter and column allowlists
Limit which columns a caller may select with selectableColumns.
Cursor pagination with Drizzle
Drizzle cursor pagination with findByCursor: pass each nextCursor back for the next page. With an index on the sort columns, page 500 is as fast as page 1.
Full-text search in PostgreSQL with Drizzle
Drizzle full text search on Postgres in NestJS: fullTextSearch() ranks with ts_rank, takes filters and relations, a language and websearch queries.