Relations

Declare relations in the entity's forFeature config under relations, keyed by relation name, then eager-load them with options.relations. Three kinds are supported, and any of them can nest:

• belongs-to (many-to-one / one-to-one): a foreign key on this table points at another table. Loaded with a LEFT JOIN in the same query, resolves to an object or null.
• has-many (one-to-many): a foreign key on the other table points back here. Loaded with one extra batched query per relation, resolves to an array.
• many-to-many: a join table holds a foreign key to each side. Loaded with one extra batched query per relation, resolves to an array.

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.

How do I avoid N+1 queries?

Ask for the relation instead of looping. find, findOne and findAll accept options.relations, and the service does the batching for you:

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

The alternative, fetching countries and then querying states inside a loop, is 1 + N queries. That is the pattern this replaces.

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

Declaring a belongs-to relation

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' },
      },
    },
  },
])

Declaring a has-many relation

Set type: 'hasMany' and name the column on the child table that points back at this one:

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' },
      },
    },
  },
])

A relation missing its key column throws when the service is constructed, so a typo shows up at startup instead of as an empty array at runtime.

Declaring a many-to-many relation

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

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' }],
    },
  },
}

await countries.find(1, { relations: ['tags'] });
// { id: 1, name: 'India', tags: [{ id: '...', name: 'himalayan' }, ...] }

The join table never appears in the result. Its key is used to group each row onto the right parent and then removed.

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:

config: {
  relations: {
    states: {
      table: states,
      type: 'hasMany',
      foreignKey: 'country_id',
      relations: {
        cities: { table: cities, type: 'hasMany', foreignKey: 'state_id' },
      },
    },
  },
}

// 4 queries for the page: data, count, all states, all cities
const page = await countries.findAll({}, { page: 1, limit: 50 }, {
  relations: ['states', 'states.cities'],
});
// 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:

await cities.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.

Ordering a collection

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

states: {
  table: states,
  type: 'hasMany',
  foreignKey: 'country_id',
  orderBy: [{ column: 'name', order: 'asc' }],
}

An orderBy entry naming a column that does not exist is warned about and skipped, the same way defaultSort behaves.

Eager loading

await countries.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 relation comes back as null, never as an object full of nulls. A parent with no children comes back as [], so callers can iterate without a guard.

Soft delete is respected on both sides: when it is enabled and the child table has the configured column, deleted children are excluded from the collection.

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 silently cuts a country in half. The batched IN (...) query keeps one row per parent and keeps total honest. This applies to has-many and many-to-many alike.

The same reasoning applies to filters, so filtering by a child column compiles to a subquery:

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

That is "parents with at least one matching child". Many-to-many filters work the same way, through the join table. Both support the same operators as any other filter, and the predicate is applied to the count query too.

Filtering by related columns

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

// belongs-to: joined, then filtered
await cities.findAll({ state: { name: 'Karnataka' } });

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

// has-many: subquery, no join
await countries.findAll({ states: { name: { ilike: 'K%' } } });

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

Multi-level filtering

Multi-level filtering works through the intermediate table's columns (e.g. filter cities by state.country_id), so you usually do not need a direct relation to the far table.

// cities → states (direct relation) → countries (via state.country_id)
await cities.findAll({
  state: { country_id: 3 },
});

You can also declare a direct relation to the far table if the queries are frequent. Each declared relation is joined (belongs-to) or batched (has-many) independently when it is referenced in a filter or in options.relations.

Selecting relation columns

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

await countries.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 is what maps each child back to its parent. See column selection for the full rules.

Limitations

• Filters are one level deep. findAll({ states: { name: 'X' } }) works; findAll({ states: { cities: { name: 'X' } } }) does not. Filter on the intermediate table's own columns instead.
• Belongs-to joins are always LEFT. Filter by id: { isNotNull: true } if you need to drop the unmatched rows.
• A collection is not paginated per parent. orderBy sorts the children, but every matching child is returned. Use a custom method when you need "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.

Next

• → Column selection: dotted paths and partial rows
• → Primary keys: including uuid, which affects relation FKs
• → Filtering: the full filter spec including operator reference