Column selection

Pass options.select with the column names you want and the query stops being SELECT *. It works on find, findOne and findAll, and a dotted entry narrows an eager-loaded relation.

await countries.findAll({}, { page: 1, limit: 20 }, { select: ['id', 'name'] });
// SELECT "id", "name" FROM "countries" ...
// -> { data: [{ id: 1, name: 'India' }, ...], total, page, limit }

Omit select and every column comes back, which is the default.

Which columns can I ask for?

A dotted entry only takes effect when the relation is also listed in options.relations. Names that do not match a real column are ignored rather than rejected, so a typo returns fewer columns instead of an error.

Narrowing a relation

await countries.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 }],
// }

Two keys are added back automatically, because the loader cannot group children without them:

• the parent key on the base row (the configured primaryKey, or parentKey when set),
• the foreign key on each child row.

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.

Listing only relation columns keeps the parent whole

If every entry is dotted, the base row is not narrowed at all:

await countries.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 this worth doing?

• Wide tables. Skipping a large text or jsonb column you do not render is the cheapest win available.
• List endpoints. A table view usually needs three columns out of fifteen.
• Hot paths. Less data over the wire, less to hydrate, and a covering index can serve the whole query.

Keep it out of write paths: create, update and restore return the full row through RETURNING, and select does not apply to them.

Related

• exists(id) already uses this internally, selecting only the primary key.
• Relations covers eager loading and the batched has-many query.
• Pagination and sorting covers the rest of the read options.