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

countries.controller.ts
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.

EntryMeaning
'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 selectableColumns allowlist 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.

countries.controller.ts
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 addedWhen
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 rowAlways, because it maps the child back to its parent.
The key a nested relation hangs onA 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.

countries.controller.ts
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.

CaseWhy it helps
Wide tablesSkipping a large text or jsonb column you do not render is the cheapest win there is.
List endpointsA table view usually needs three columns out of fifteen.
Hot pathsLess 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

Next steps

Last updated View change history

On this page