Drizzle relations in NestJS

Declare Drizzle relations in NestJS and eager-load belongs-to, has-many, many-to-many and nested relations without N+1 queries.

Drizzle relations in NestJS are declared in the entity's forFeature config under relations, keyed by relation name, and loaded with options.relations on any read or write. Three shapes are supported, and each can nest: belongs-to (an object or null), has-many and many-to-many (arrays). No shape ever runs a query per row, so eager loading a page of 50 rows costs the same number of queries as a page of 5.

Only a top-level belongs-to is joined

A belongs-to declared on the service's own table rides along in the main query as a LEFT JOIN. Everything else (has-many, many-to-many, and every nested level whatever its shape) is loaded by one batched IN (...) query per relation. A collection is never joined, because a join would multiply parent rows and break total and pagination.

These relations are config objects that point at Drizzle tables. The package does not read Drizzle's own relations() helper, so you can keep or drop that for the Drizzle query API as you like.

How do I avoid N+1 queries?

Ask for the relation instead of looping. find, findOne, findAll and findByCursor accept options.relations, and the service batches the loads:

countries/countries.reads.ts
// 3 queries for the whole page: data, count, and all states in one IN (...)
const page = await countriesService.findAll(
  {},
  { page: 1, limit: 50 },
  { relations: ['region', 'states'] },
);

Fetching the countries and then querying states inside a loop is 1 + N queries. That is the pattern this replaces.

RelationSQLExtra queriesResult
top-level belongsTo (default)LEFT JOIN in the main query0object, or null when unmatched
hasManySELECT ... WHERE fk IN (parent keys)1 per relation, for the whole pagearray, [] when empty
manyToManySELECT ... FROM join JOIN target ... WHERE fk IN (parent keys)1 per relation, for the whole pagearray, [] when empty
nested (any shape, including belongs-to)batched IN (...) against the level above1 per relation per levelas above

Sibling relations load concurrently, so three collections are three parallel queries, not three round trips in sequence.

A level with more parent keys than one statement can bind is split, since 3.8.0: the keys go out in chunks of 64,535 on PostgreSQL and MySQL (31,766 on SQLite, 90 on Cloudflare D1), so a nested load whose level holds 70,000 keys is two queries for that relation instead of a 400. All the children of one parent come from the same chunk, so grouping and orderBy are unchanged. Override the protected relationChunkSize(db) to load in smaller pieces.

How do I declare a belongs-to relation?

Name the foreign-key column on this table with localKey. A belongs-to is a many-to-one or one-to-one link, and it is the default type.

cities/cities.module.ts
import { cities, states } from '../db/schema';

DrizzleCrudModule.forFeature([
  {
    service: CitiesService,
    table: cities,
    config: {
      relations: {
        // cities.state_id -> states.id (references defaults to 'id')
        state: { table: states, localKey: 'state_id', references: 'id' },
      },
    },
  },
])
tableRequired

Drizzle table

The target table.
typeOptional

"belongsTo" | "hasMany" | "manyToMany"

The relation shape.

Defaults to "belongsTo"

localKeyRequired

string

The column on this table that holds the foreign key. Required for belongs-to.
referencesOptional

string

The column on the target table the foreign key points at.

Defaults to "id"

relationsOptional

RelationsConfig

Relations of the target table, so a dotted path can load them. Same shape as this map.

The key of the entry (state here) is the relation name. You use it in options.relations, in filters, in select paths and as the property on the returned row.

How do I declare a has-many relation?

Set type: 'hasMany' and name the column on the child table that points back here with foreignKey:

countries/countries.module.ts
import { countries, regions, states } from '../db/schema';

DrizzleCrudModule.forFeature([
  {
    service: CountriesService,
    table: countries,
    config: {
      relations: {
        // belongs-to: countries.region_id -> regions.id
        region: { table: regions, localKey: 'region_id' },
        // has-many: states.country_id -> countries.id
        states: { table: states, type: 'hasMany', foreignKey: 'country_id' },
      },
    },
  },
])
foreignKeyRequired

string

The column on the target table that points back at this one. Required for has-many.
parentKeyOptional

string

The column on this table that foreignKey matches. Defaults to this table's primary key: the configured primaryKey at the top level, the related table's own primary key column at a nested level.
orderByOptional

SortColumn[]

How to order each collection, applied in SQL: [{ column, order }].

When the service is built, it checks that each relation has its table and the settings its shape requires (localKey, foreignKey, or the three through settings), including nested relations, and throws with the relation's path if one is missing. It does not check that the named columns exist, so a misspelled column name leaves the relation out of the result instead of failing.

How do I declare a many-to-many relation?

Name the join table and its two foreign-key columns:

countries/countries.module.ts
import { countries, countryTags, tags } from '../db/schema';

config: {
  relations: {
    // countries.id <- country_tags.country_id / country_tags.tag_id -> tags.id
    tags: {
      table: tags,
      type: 'manyToMany',
      through: countryTags,
      throughLocalKey: 'country_id',
      throughForeignKey: 'tag_id',
      orderBy: [{ column: 'name', order: 'asc' }],
    },
  },
}
throughRequired

Drizzle table

The join table. Required for many-to-many.
throughLocalKeyRequired

string

The join-table column that matches parentKey on this table.
throughForeignKeyRequired

string

The join-table column that matches references on the target table.
referencesOptional

string

The target column.

Defaults to "id"

parentKeyOptional

string

The column on this table. Defaults to its primary key, as for has-many.
countries/countries.reads.ts
await countriesService.find(1, { relations: ['tags'] });
// { id: 1, name: 'India', tags: [{ id: '...', name: 'himalayan' }, ...] }

The join table never appears in the result. Its key travels with each row long enough to group it onto the right parent, and is then removed.

Nested collections key on their own table

Before 3.3.1 a nested collection borrowed the root service's primaryKey. With a root keyed on uid, comment.author.posts looked for author.uid and came back empty. A nested level now uses the primary key column of the table it hangs off (authors.id here), so parentKey is only needed when the join column is something else.

How do I eager-load a relation of a relation?

Give the relation its own relations map, then address it with a dotted path in options.relations:

countries/countries.module.ts
config: {
  relations: {
    states: {
      table: states,
      type: 'hasMany',
      foreignKey: 'country_id',
      relations: {
        cities: { table: cities, type: 'hasMany', foreignKey: 'state_id' },
      },
    },
  },
}
countries/countries.reads.ts
// 4 queries for the page: data, count, all states, all cities
const page = await countriesService.findAll({}, { page: 1, limit: 50 }, {
  relations: ['states', 'states.cities'],
});
// page.data[0].states[0].cities -> [{ id, name, state_id }, ...]

Each level is one batched query for every row at that level, so 50 countries with 200 states and 900 cities is still 4 queries. Nesting works for every shape, including a belongs-to chain:

cities/cities.reads.ts
await citiesService.find(1, { relations: ['state.country'] });
// { id: 1, name: 'Bengaluru', state: { id: 7, name: 'Karnataka', country: { id: 3, name: 'India' } } }

Asking for state.country pulls in state on its own, so you do not have to list both. A name that is not configured is dropped, along with the rest of its path.

How do I order a collection?

Set orderBy on the relation, and the children come back sorted by SQL rather than in insertion order:

countries/countries.module.ts
states: {
  table: states,
  type: 'hasMany',
  foreignKey: 'country_id',
  orderBy: [{ column: 'name', order: 'asc' }],
}

An orderBy entry that names a column the target table does not have is warned about and skipped, the same way defaultSort behaves.

What does an eager-loaded row look like?

The relation appears as a property named after it:

countries/countries.reads.ts
await countriesService.find(1, { relations: ['region', 'states'] });
// {
//   id: 1,
//   name: 'India',
//   region_id: 3,
//   region: { id: 3, name: 'Asia', code: 'AS' },
//   states: [
//     { id: 10, name: 'Karnataka', country_id: 1 },
//     { id: 11, name: 'Kerala', country_id: 1 },
//   ],
// }

An unmatched belongs-to comes back as null, never as an object full of nulls. A parent with no children gets [], so callers can iterate without a guard.

Soft delete applies to related tables too. When soft delete is on for the service and a related table has a column under the same key, its deleted rows are hidden: a deleted parent loads as null and deleted children are left out of the collection. See Soft delete in Drizzle ORM.

Why is a collection not joined?

Because a join would break the page. One country with four states becomes four rows, count(*) returns 4 instead of 1, and limit 50 cuts a country in half. The batched IN (...) query keeps one row per parent and keeps total honest. This holds for has-many and many-to-many alike.

The count query follows the same rule. findAll joins the eager-loaded belongs-to relations and the relations your filters use into the data query, but only the filter relations into the count query, so eager loading never changes total.

Use the relation name as a filter key, with a nested object of the related table's columns:

cities/cities.reads.ts
// belongs-to at the top level: joined, then filtered
await citiesService.findAll({ state: { name: 'Karnataka' } });

// combine with normal column filters and operators
await citiesService.findAll({
  name: { ilike: 'B%' },
  state: { country_id: 3 },
});

A filter on a collection compiles to a subquery, never a join, for the same reason a collection is never joined:

countries/countries.reads.ts
// countries.id IN (SELECT country_id FROM states WHERE lower(name) = lower('Kerala'))
await countriesService.findAll({ states: { name: 'Kerala' } });

// many-to-many: a subquery through the join table
await countriesService.findAll({ tags: { name: 'himalayan' } });

That reads as "parents with at least one matching child". Collection filters accept the same operators as any other filter, and the predicate applies to the count query too.

How do I filter across more than one level?

Often you can filter on the intermediate table's columns, so you do not need a relation to the far table:

cities/cities.reads.ts
// cities -> states (direct relation) -> countries (via states.country_id)
await citiesService.findAll({ state: { country_id: 3 } });

Or filter through the relation chain itself, { state: { country: { code: 'IN' } } }. Below the first belongs-to, each level is an EXISTS subquery on an aliased table rather than a join. Use some, none and every to quantify collections. See Relation filters for the full syntax.

How do I select relation columns?

options.select takes dotted paths, so you can narrow a relation instead of loading every column:

countries/countries.reads.ts
await countriesService.findAll(
  {},
  { page: 1, limit: 20 },
  {
    relations: ['region', 'states'],
    select: ['id', 'name', 'region.code', 'states.name'],
  },
);
// { id, name, region: { code }, states: [{ name, country_id }] }

The child keeps its foreign key even when you do not list it, because that column maps each child back to its parent. The parent likewise keeps any key a nested level needs. See column selection for the full rules.

Can clients request relations over HTTP?

Yes. parseQuery turns ?with=region,states into options.relations and ?region[name]=Asia into a relation filter. Limit what a client may load with the loadableRelations allowlist; listing states.cities also allows states.

What are the limits?

  • A top-level belongs-to is always a LEFT join. To drop rows with no match, filter on the relation: { state: { id: { isNotNull: true } } }.
  • A collection is not paginated per parent. orderBy sorts the children, but every matching child is returned. Write a custom method for "the latest three per parent".
  • Join-table columns are not returned. If the join table carries data of its own (a role, a position), model it as an entity with two belongs-to relations.

Can I load relations on a write, or count them?

Yes. create, update, restore, upsert and the mass writes take options.relations and return the rows with them loaded, using the same batched loaders, and a create body can write children too. See Relations on writes. options.counts adds a _count object for collections, one grouped query per relation; see Aggregates.

Common questions

Next steps

Last updated View change history

On this page