> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Relations

> Define one-to-one, one-to-many and many-to-many relations between UQL entities.

Source: https://uql-orm.dev/entities/relations

A relation links two entities so you can load, filter and write related rows. Members are named with callbacks such as `(post) => post.author`, so a typo is a compile error and a rename follows.

```ts
import { v7 as uuidv7 } from 'uuid';
import {
  Entity,
  Id,
  Field,
  OneToOne,
  OneToMany,
  ManyToOne,
  ManyToMany,
} from 'uql-orm';

@Entity()
export class User {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ type: String })
  name?: string | null;

  @OneToOne({
    entity: () => Profile,
    mappedBy: (profile) => profile.user,
    cascade: true,
  })
  profile?: Profile;

  @OneToMany({ entity: () => Post, mappedBy: (post) => post.author })
  posts?: Post[];
}

@Entity()
export class Profile {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ references: () => User })
  userId?: string | null;

  @OneToOne({ entity: () => User, references: (profile) => profile.userId })
  user?: User;
}

@Entity()
export class Post {
  @Id({ type: Number })
  id?: number;

  @Field({ type: String })
  title?: string | null;

  @Field({ references: () => User })
  authorId?: string | null;

  @ManyToOne({ entity: () => User, references: (post) => post.authorId })
  author?: User;

  @ManyToMany({ entity: () => Tag, through: () => PostTag, cascade: true })
  tags?: Tag[];
}

@Entity()
export class Tag {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ type: String })
  name?: string | null;
}

@Entity()
export class PostTag {
  @Id({ type: 'uuid', onInsert: uuidv7 })
  id?: string;

  @Field({ references: () => Post })
  postId?: number | null;

  @Field({ references: () => Tag })
  tagId?: string | null;
}
```

## Foreign keys

The side of a to-one relation that holds the foreign key declares the column with `@Field({ references })` and names it in the relation’s `references`. The column takes its type from the key it points at, and stays a normal field you can select, filter and write. A column with no relation over it is still a foreign key in the DDL, with nothing to `$populate`.

A to-many has no column of its own, so it says where its rows are in one of three ways:

| Option | Use it for |
| - | - |
| `mappedBy` | The inverse side: the member on the other entity holding the foreign key or relation. |
| `through` | A junction entity with a foreign key to each side, for `@ManyToMany` or `@OneToMany`. |
| `references` | Explicit join columns: `(order, customer) => [{ local: order.customerCode, foreign: customer.code }]`. Not with `through`. |

With `through`, uql joins on the junction’s one column referencing each side (`postId` and `tagId` above), whatever its name. A junction with none or two for a side fails when the entity loads, as does a join column referencing a different entity than the relation’s.

A [composite key](https://uql-orm.dev/entities/basic.md#composite-primary-keys) cannot be referenced by one column: [pair a column per key](https://uql-orm.dev/entities/basic.md#pointing-at-a-composite-key) in the relation’s `references` instead.

## Writing related rows: `cascade`

`cascade: true` makes inserts, updates and deletes reach the relation’s rows; `'persist'` or `'delete'` enables one half. Such a write runs in one transaction, whether or not you opened one.

| The relation | An insert or update | A delete |
| - | - | - |
| holds the key: `@ManyToOne`, `@OneToOne` with `references` | writes the target first. `{ id }` links an existing target without writing it; `null` clears the key. | `'persist'` only, since other rows may share the target |
| is held by its rows: `@OneToMany`, `@OneToOne` with `mappedBy` | writes the rows, pointed at the parent. An update replaces them: listed rows are saved, the parent’s other rows deleted. `null` or `[]` deletes all. | deletes the rows first |
| goes through a junction: `@ManyToMany`, `@OneToMany` with `through` | writes the targets, then the links. An update keeps or adds the listed links and removes the rest; targets stay. | deletes the links first |

A soft delete cascades as a soft delete: rows that can be stamped are, and the rest, junction links included, are kept so a restore brings everything back.

## Database actions: `onDelete` and `onUpdate`

To have the database act instead of uql, set the foreign key’s action on the side holding the key. A single `DELETE` then removes the children:

```ts
@ManyToOne({ entity: () => User, references: (post) => post.authorId, onDelete: 'CASCADE' })
author?: User;
```

Each takes `'CASCADE'`, `'SET NULL'`, `'SET DEFAULT'`, `'RESTRICT'` or `'NO ACTION'`. A column with no relation takes `@Field({ references, onDelete })`; a relation’s action wins over its column’s. The default is `'NO ACTION'`, or `defaultForeignKeyAction` from your migration config.

## Querying relations

Columns go in `$select`, related entities in `$populate`, each with its own selection and filter:

```ts title="You write"
const posts = await pool.findMany(Post, {
  $select: { id: true, title: true },
  $populate: {
    author: { $select: { id: true, name: true } },
    tags: {
      $select: { name: true },
      $where: { name: { $istartsWith: 'typescript' } },
    },
  },
  $where: { author: { name: 'Roger' } },
});
```

PostgreSQL:

```sql
-- One statement: the author is joined, the tags are a correlated subquery, the author filter an EXISTS.
SELECT "Post"."id", "Post"."title",
       "author"."id" "author.id", "author"."name" "author.name",
       (SELECT COALESCE(JSON_AGG("_uql_row"), '[]'::json)
          FROM (SELECT "tags"."name" FROM "Tag" "tags"
                WHERE "tags"."name" ILIKE $1 AND "tags"."id" IN (
                  SELECT "PostTag"."tagId" FROM "PostTag"
                  WHERE "PostTag"."postId" = "Post"."id")) "tags"
          CROSS JOIN LATERAL (SELECT "tags"."name") "_uql_row") "tags"
FROM "Post"
LEFT JOIN "User" "author" ON "author"."id" = "Post"."authorId"
WHERE EXISTS (
  SELECT 1 FROM "User" "author_2"
  WHERE "author_2"."id" = "Post"."authorId" AND "author_2"."name" = $2
)
-- values: ['typescript%', 'Roger']
```

[Querying relations](https://uql-orm.dev/querying/relations.md) covers deep filtering and selection. On an existing database, `generate:from-db` [writes these decorators from its foreign keys](https://uql-orm.dev/migrations.md#relations-when-scaffolding).