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.

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 cannot be referenced by one column: pair a column per 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:

@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:

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

-- 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 covers deep filtering and selection. On an existing database, generate:from-db writes these decorators from its foreign keys.