Includes

const customer = await db.customers
  .findById(id, {
    select: ["id", "name"],
    include: {
      organization: { select: ["name"] },
      primaryContact: true,
      notes: {
        select: ["id", "body"],
        where: { kind: "call" },
        orderBy: { createdAt: "desc" },
        limit: 5,
      },
      customerTags: {
        select: ["tagId"],
        include: { tag: { select: ["name", "color"] } },
      },
    },
  })
  .orThrow();

The result type follows the relation:

• to-one, not nullable: organization: { name: string }
• to-one, nullable: primaryContact: Contact | null
• to-many: notes: { id: number; body: string }[]

Includes take select, include, where, orderBy and limit. where on a to-many include filters the related rows, not the parents. To only return parents that have a match, add required: true or filter the parent with some.

Includes use the foreign key name as the embed hint, so tables with several relations to the same target never hit PostgREST's ambiguity error.

Row level securityCopy Anchor Link

A not null foreign key types the include as non-null, but a SELECT policy on the related table can still hide the row, and PostgREST then returns null. Add required: true to drop those parents instead; the include is then typed non-null for nullable relations too:

await db.notes.findMany({
  include: { customer: { select: ["name"], required: true } },
});

To make the types say so everywhere, set relations: { nullableUnderRls: true } in the config: gen then types every to-one include of a table with row level security as | null, unless the include says required: true.

Counting related rowsCopy Anchor Link

_count counts to-many relations in the same request, without loading them. A where counts only the matching rows:

const customers = await db.customers
  .findMany({
    select: ["id", "name"],
    include: {
      _count: { notes: true, locations: { where: { isPrimary: true } } },
    },
    limit: 20,
  })
  .orThrow();
// { id: string; name: string; _count: { notes: number; locations: number } }[]

To-one relations can't be counted; that's a type error, and an invalid_request error at runtime. _sum, _avg, _min and _max work the same way; see Aggregates.