# Relations and nested writes [#relations-and-nested-writes]

A Prisma relation (`User.posts`, `Post.author`, `Post.category`) is a field of its Resource. You read it by naming it in `select` or `include`, and write it with directives such as `$create` and `$connect`. The examples use `User` (has many `Post`), `Post` (belongs to a `User`, optionally to a `Category`) and `Category`.

## Reading relations [#reading-relations]

With neither `select` nor `include`, a call returns every readable scalar field and no relations.

### include [#include]

`include` adds relations to the default scalars:

```ts
await avClient.User.find.first({ where: { id: 3 }, include: ["posts"] });
// {
//   avatar: null, createdAt: 2026-10-06T22:58:25.885Z, email: 'alan@example.com', id: 3, name: null,
//   passwordHash: '', role: 'USER', settings: null,
//   posts: [ { authorId: 3, bigViews: 0n, body: null, categoryId: 1, id: 6, price: AvDecimal {},
//             publishedAt: null, title: 'Enigma', views: 100 } ]
// }
```

### select [#select]

`select` names exactly what you want, scalars and relations mixed. A relation named as a string uses the target's default scalars; to customize it, use the object form:

```ts
await avClient.Post.find.first({
  where: { id: 1 },
  select: ["id", "title", { category: { select: ["name"] } }],
});
// { id: 1, title: 'Hello', category: { name: 'News' } }

await avClient.User.find.many({
  select: [
    "id",
    "name",
    { posts: { select: ["id", "title"], where: { views: { gt: 5 } }, orderBy: { views: "desc" }, limit: 2 } },
  ],
});
// [ { id: 1, name: 'Ada Lovelace', posts: [ { id: 1, title: 'Hello' } ] },
//   { id: 2, name: 'Grace Hopper', posts: [ { id: 3, title: 'Compilers' }, { id: 4, title: 'Navy stories' } ] },
//   { id: 3, name: null, posts: [ { id: 6, title: 'Enigma' } ] } ]
```

A to-many relation accepts its own `where`, `orderBy`, `limit`, `offset` and `cursor`, applied per parent row. A to-one relation that is empty reads as `null`:

```ts
await avClient.Post.find.unique({ where: { id: 4 }, select: ["title", { category: { select: ["name"] } }] });
// { title: 'Navy stories', category: null }
```

`select` and `include` cannot be used together. A customized relation must be an object: `{ posts: ["id"] }` is refused with `V1008` ("Customized relation projection must be an object.").

### Going deeper [#going-deeper]

Relations nest, up to `maxNestingDepth` (12 by default):

```ts
await avClient.Category.find.many({
  select: ["name", { posts: { select: ["title", { author: { select: ["email"] } }] } }],
});
// [ { name: 'News',      posts: [ { title: 'Hello', author: { email: 'ada@example.com' } },
//                                 { title: 'Enigma', author: { email: 'alan@example.com' } } ] },
//   { name: 'Tutorials', posts: [ { title: 'Notes on engines', author: { email: 'ada@example.com' } },
//                                 { title: 'Compilers', author: { email: 'grace@example.com' } } ] } ]
```

To count related rows instead of loading them, use `$count` ([Counting](/framework/docs/counting)). To filter or order parents by their relations, see [Filtering, sorting and paging](/framework/docs/filtering-sorting-paging#filtering-through-relations).

[Exposure](/framework/docs/exposure) can hide a relation, forbid including it, or narrow its pagination: [Exposing and hiding fields](/framework/docs/exposing-and-hiding-fields).

## Writing relations [#writing-relations]

Inside `data` of a `create` or `update`, a relation field takes directives. You may also set the foreign key directly (`authorId: 1`), which is simpler when you have it. Two limits apply:

* Not together with the same relation: setting `authorId` and `author: { $connect: … }` in one record names the reference twice and is refused before the database is asked: `422` `A2004` with a `V1012` issue at the relation, `Set "authorId" or "author", not both.` It holds for required and optional relations, for `create`, `update` and `upsert`, for nested writes at any depth and for each step of a transaction. Either one alone is fine.
* Not beside another relation's write (Prisma): a record that writes one relation through its field and another relation directly, such as `authorId` beside `category: { $connect: … }`, is refused with `V1012`: `Use relation fields only, or reference fields only, in one record: "authorId" is a reference field and "category" is a relation.` Write all the record's relations as relations, or all through their fields. A field beside a relation that holds no reference on that side (`mentorId` beside `posts: { $create: … }` on an author) is accepted.
* Not back along the relation it is written through: in a `Post` created in `User.posts`, `author` and `authorId` are what the parent supplies. Writing either is refused before the database: `422` `A2004`, `V1012` at that field (`Relation "author" is the relation this Post is written through, from its other end ("User.posts"): the write it sits in supplies it.`). The same holds in `$connectOrCreate`, `$update` and `$upsert`, at any depth, and it is a type error in typed calls. A self-relation's other edge stays writable.

### Required relations and fields [#required-relations-and-fields]

A create must write every field the database requires and every required relation. The contract says which, per Resource, as `createRequirements` ([Resources and operations](/framework/docs/resources-and-operations#what-a-create-requires)), and the types follow it: in the generated client, `framework.application`, `framework.client`, the transaction builders and a stage's `ctx`, a create that leaves one out does not compile.

A required relation is written one way or the other: the relation (`author: { $connect: … }` or `$create`), or the field that holds its reference (`authorId`). Neither is a type error, and so is both:

```ts
await avClient.Post.create.one({ data: { title: "Hi", authorId: 1 } });                     // ok
await avClient.Post.create.one({ data: { title: "Hi", author: { $connect: { id: 1 } } } }); // ok
// @ts-expect-error: write `author` or `authorId`
await avClient.Post.create.one({ data: { title: "Hi" } });
// @ts-expect-error: `author` and `authorId` both
await avClient.Post.create.one({ data: { title: "Hi", authorId: 1, author: { $connect: { id: 1 } } } });
```

When the reference has several fields (a composite key), the field way means all of them together. A create nested through a relation gets that relation from its parent, so a `Post` in `User.posts: { $create: … }` needs its `title` and nothing for its author. A `$ref` counts as a value. `create.many` and `create.count` records write no relation, so each one needs `authorId` (all the fields of a composite reference), and a record without it is refused with `V1000` at `data.<n>.authorId`.

What a layer hides is not asked of its callers: if the client layer hides `authorId`, a client create needs `author`; if it hides `author`, it needs `authorId`. Hiding both (or exempting their `create`) while the layer can still create posts is refused at startup (`COMPILER_HIDDEN_REQUIRED_FIELD`, see [Exposure](/framework/docs/exposure#creates-need-what-they-require)). So is hiding `authorId` while the layer keeps `create.many` or `create.count`, even with `author` exposed: those records have no other way to name the author. Exempt the two variants in that layer, or keep `authorId`.

A field a [`compute.create`](/framework/docs/computed-fields) stage fills is not required in the generated client, `framework.application`, `framework.client` and the transaction builders, whether the stage is written inline in `createFramework`, in a `defineResource` block, or in an overlay (where it counts for that layer only). A stage's `ctx` is the exception: its calls are typed from the adapter's model, with no layer or compute applied, so `ctx.Post.create.one` still asks for `slug` even where a `compute.create` fills it. Pass any value there (the compute overwrites it), or call through the façade. A request that gets past the types is refused the same way at runtime: `A2004` with `V1000` at the missing field (`Relation "author" is required to create Post: write it, or "authorId".`), or `V1012` for both.

| Directive          | Meaning                                                                                                                                                           | On create | On update                |
| ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------- | ------------------------ |
| `$create`          | Create related records and link them.                                                                                                                             | yes       | yes                      |
| `$connect`         | Link existing records, by identifier.                                                                                                                             | yes       | yes                      |
| `$connectOrCreate` | Link if found, otherwise create.                                                                                                                                  | yes       | yes                      |
| `$update`          | Update the related record(s); on a to-many, every record matching a `where` (required); its `data` takes what `update.many`'s does: no relation writes on Prisma. |           | yes                      |
| `$upsert`          | Update the match, otherwise create.                                                                                                                               |           | yes                      |
| `$delete`          | Delete the related record(s); on a to-many, those matching a `where` (required).                                                                                  |           | optional to-one, to-many |
| `$disconnect`      | Unlink without deleting: `true` on an optional to-one, identifiers on a to-many.                                                                                  |           | optional to-one, to-many |
| `$set`             | Make the relation exactly this set.                                                                                                                               |           | to-many                  |

Where an adapter's relation takes plain fields only in a nested write's `where`, as a to-many relation does with the Prisma 7 adapter, the `where` of `$update` and `$delete` is typed `AvScalarWhereInput`: each scalar field's filter and `AND`/`OR`/`NOT`, with no relation filter at any depth. A relation that advertises no directive for a write takes `AvNoAdvertisedMember`, which accepts `{}` and refuses every key. Both are exported from `@aventara/core`.

The directives a relation accepts are in the contract (`capabilities.create` / `update`), and exposure can narrow them. Directives are only valid in writes: in a `where` they answer `V1006`.

### Create and link [#create-and-link]

```ts
// a Post linked to existing records
await avClient.Post.create.one({
  data: { title: "Connected", author: { $connect: { id: 1 } }, category: { $connect: { id: 2 } } },
  select: ["id", "title", "authorId", "categoryId"],
});
// { id: 7, title: 'Connected', authorId: 1, categoryId: 2 }

// a User and their Posts in one call
await avClient.User.create.one({
  data: { email: "linus@example.com", name: "Linus", posts: { $create: [{ title: "Kernel" }, { title: "Git" }] } },
  select: ["id", "email", { posts: { select: ["id", "title"], orderBy: { id: "asc" } } }],
});
// { id: 4, email: 'linus@example.com', posts: [ { id: 8, title: 'Kernel' }, { id: 9, title: 'Git' } ] }

// link a category, creating it when it does not exist yet
await avClient.Post.create.one({
  data: {
    title: "CoC",
    author: { $connect: { email: "ada@example.com" } },
    category: { $connectOrCreate: { where: { name: "Opinion" }, create: { name: "Opinion" } } },
  },
  select: ["id", "categoryId"],
});
// { id: 10, categoryId: 3 }
```

`$connect` takes an identifier: the primary key or any unique column (`{ email: "..." }`).

### Update, unlink, replace [#update-unlink-replace]

```ts
// add a related record while updating the parent
await avClient.User.update.unique({
  where: { id: 1 },
  data: { posts: { $create: { title: "Nested" } } },
  select: ["id", { posts: { select: ["title"], orderBy: { id: "desc" }, limit: 1 } }],
});
// { id: 1, posts: [ { title: 'Nested' } ] }

// change related records matching a where
await avClient.User.update.unique({
  where: { id: 1 },
  data: { posts: { $update: { where: { title: "Nested" }, data: { views: { $increment: 5 } } } } },
  select: ["id", { posts: { select: ["title", "views"], where: { title: "Nested" } } }],
});
// { id: 1, posts: [ { title: 'Nested', views: 5 } ] }

// unlink an optional to-one
await avClient.Post.update.unique({ where: { id: 1 }, data: { category: { $disconnect: true } }, select: ["id", "categoryId"] });
// { id: 1, categoryId: null }

// replace a to-many with exactly this set; $disconnect unlinks given members only
await avClient.Category.update.unique({ where: { id: 1 }, data: { posts: { $set: [{ id: 1 }, { id: 3 }] } }, select: ["id", { posts: { select: ["id"], orderBy: { id: "asc" } } }] });
// { id: 1, posts: [ { id: 1 }, { id: 3 } ] }
await avClient.Category.update.unique({ where: { id: 1 }, data: { posts: { $disconnect: [{ id: 3 }] } }, select: ["id", { posts: { select: ["id"] } }] });
// { id: 1, posts: [ { id: 1 } ] }

// delete related records matching a where
await avClient.User.update.unique({ where: { id: 1 }, data: { posts: { $delete: { where: { title: "Nested" } } } }, select: ["id"] });
```

A to-many `$update` or `$delete` without a `where` is refused: `V1012` ("To-many $update requires where.").

### Upsert [#upsert]

`upsert.unique` creates the record when the identifier does not match, and updates it when it does:

```ts
await avClient.User.upsert.unique({
  where: { email: "new@example.com" },
  create: { email: "new@example.com", name: "New" },
  update: { name: "Renamed" },
  select: ["id", "name"],
});
// first call:  { id: 4, name: 'New' }          (HTTP 200, A1008)
// second call: { id: 4, name: 'Renamed' }
```

### What fails, and how [#what-fails-and-how]

| Mistake                                                                                                                                | Result                                                                                                                             |
| -------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| `$connect` to a record that does not exist                                                                                             | `AvNotFoundError`, `404` `A2003` ("The operation's arguments referenced a record that does not exist."), in a transaction step too |
| A nested write the target Resource's own guard refuses (a client `$create` of a `Category` when `Category`'s create guard says no)     | `AvAuthError`, `A4001` (or the code the guard threw)                                                                               |
| A foreign key that does not exist (`authorId: 999`)                                                                                    | `AvConflictError`, `A2008`                                                                                                         |
| `$update` / `$delete` on a to-many without `where`                                                                                     | `AvValidationError`, `A2004` / `V1012`                                                                                             |
| A reference field and its own relation in one record (`authorId` with `author: { $connect: … }`)                                       | `AvValidationError`, `A2004` / `V1012`: `Set "authorId" or "author", not both.`                                                    |
| One relation written through its reference field and another written directly, on Prisma (`authorId` with `category: { $connect: … }`) | `AvValidationError`, `A2004` / `V1012`: `Use relation fields only, or reference fields only, in one record: …`                     |
| A directive the contract does not offer on that relation                                                                               | `AvValidationError`, `A2004` / `V1006`                                                                                             |
| A relation write in the `data` of a to-many `$update`                                                                                  | `AvValidationError`, `A2004` / `V1006` (the data is a bulk write)                                                                  |
| A relation filter in the `where` of a to-many nested `$update` or `$delete` (at any depth, under `AND`, `OR` or `NOT` too)             | `AvValidationError`, `A2004` / `V1006` (below)                                                                                     |

On a required relation, a directive that would leave a child with no parent (for example `$set` that drops a `Post` from its `User`) is refused by the database and answers `AvConflictError`, `409` `A2008` ("The operation conflicts with the current state of the resource."). Nothing is written. This applies to `User.update.unique` with `posts: { $disconnect: [{ id: 1 }] }` or `posts: { $set: [] }` where `Post.authorId` is required.

### Nested writes run the target's guards [#nested-writes-run-the-targets-guards]

A nested write is checked by the Resource it reaches, before the single database call. `Post.create.one({ data: { category: { $create: { name: "x" } } } })` runs `Category`'s own Resource and `create` guards, and its field pipes, validators, write guards and compute, on the nested data. A `$update` or `$upsert` runs the target's `update` guards, a `$delete` its `delete` guards, and a `$connect`, `$disconnect` or `$set` the target's `update` guards when the target's row is what changes. A guard there sees `ctx.$operation.nested === true`. The whole table is in [Pipelines](/framework/docs/pipelines#nested-writes-run-the-targets-stages).

### The `where` of a nested `$update` or `$delete` [#the-where-of-a-nested-update-or-delete]

Through a to-many relation, a nested `$update` or `$delete` filters plain fields only on Prisma 7. A relation filter in its `where` is refused before the database is asked, with `A2004` and `V1006`, and it is a type error in the typed calls:

```ts
await avClient.User.update.unique({
  where: { id: 1 },
  data: { posts: { $update: { where: { category: { name: "News" } }, data: { views: 1 } } } },
});
// AvValidationError A2004 (422): { path: ["arguments","data","posts","$update","where","category"], code: "V1006",
//   message: 'Relation filter "category" is not available here: a nested $update or $delete filter on this relation takes scalar fields only.' }
```

The refusal covers any depth and the `AND`, `OR` and `NOT` wrappers: `{ AND: [{ NOT: { comments: { some: { text: "x" } } } }] }` is refused at `["arguments","data","posts","$update","where","AND",0,"NOT","comments"]`. When the directive holds an array, the path names the entry: the same filter in the second `$update` entry is refused at `["arguments","data","posts","$update",1,"where","category"]`. Every to-many directive's array (`$create`, `$connect`, `$connectOrCreate`, `$update`, `$delete`, `$upsert`, `$set`, `$disconnect`) is addressed this way. Filter the nested rows by their own fields (`{ title: "Nested" }`, `{ id: 3 }`), or find the ids first and name them. A to-one relation's nested `$update` keeps relation filters. The limit belongs to the adapter and is stated in the contract, so a client generated before it was declared must be regenerated.

## Scalar directives [#scalar-directives]

On a number field, an object with one `$`-key acts on the current value instead of assigning:

```ts
await avClient.Post.update.unique({ where: { id: 1 }, data: { views: { $increment: 5 } }, select: ["id", "views"] });
// { id: 1, views: 15 }
await avClient.Post.update.many({ where: { views: { gt: 50 } }, data: { views: { $multiply: 2 } }, select: ["id", "views"] });
// [ { id: 6, views: 200 } ]
```

`$increment`, `$decrement`, `$multiply` and `$divide` apply to numeric fields (`Int`, `BigInt`, `Float`, `Decimal`). A required to-one relation such as `Post.author` has no `$disconnect` or `$delete`.

One object holds one directive: `{ $increment: 1, $decrement: 2 }` is a type error and is refused with `422` `A2004` / `V1001` at the field.

A `$divide` by zero is the database's to answer. PostgreSQL refuses the division, and SQLite computes `NULL`, which a required column refuses. Either way the call answers `422` `A2004` / `V1004` at `["arguments"]` and nothing is written. On SQLite a nullable column stores the `NULL`.

## See also [#see-also]

* [Counting](/framework/docs/counting)
* [Filtering, sorting and paging](/framework/docs/filtering-sorting-paging)
* [Transactions with $ref](/framework/docs/transactions-with-ref): when a later step needs an id from an earlier one.
* [Querying](/framework/docs/querying)