Relations

Relations between entities

Relationships are a core part of any ORM. UQL makes it easy to define and query relationships with full type safety and context awareness.

Defining Relationships

Take any class and annotate it with the decorators from uql-orm. You can use mappedBy as a string or a callback for better refactoring support.

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;

  /**
   * One-to-One: A user has one profile.
   * mappedBy can be a callback for better refactoring support.
   */
  @OneToOne({
    entity: () => Profile,
    mappedBy: (profile) => profile.user,
    cascade: true,
  })
  profile?: Profile;

  /**
   * One-to-Many: A user can have many posts.
   */
  @OneToMany({
    entity: () => Post,
    mappedBy: (post) => post.author,
  })
  posts?: Post[];
}

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

  @Field({ type: String })
  picture?: string;

  /**
   * Foreign key column. The 'references' option points at the target entity;
   * the column type is inherited from the target's primary key.
   */
  @Field({ references: () => User })
  userId?: string;

  @OneToOne({ entity: () => User })
  user?: User;
}

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

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

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

  @ManyToOne({ entity: () => User })
  author?: User;

  /**
   * Many-to-Many: A post can have many tags.
   * 'through' specifies the pivot entity.
   */
  @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;
}

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

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

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

Note

The explicit @Field({ references }) FK column above is optional. Declaring an owning relation (like @ManyToOne({ entity: () => User })) auto-creates the <relation>Id column, inheriting the target’s primary-key type. Declare it explicitly only when you want to read or query the FK by name in typed code - an explicit declaration always takes precedence.

The two go together in either direction: a @Field({ references }) column named after the key it points at also gets the many-to-one relation it describes, so PostTag above can be $populated by post and tag, and its foreign keys reach generated DDL, without declaring a @ManyToOne for each.

How a to-many says where its rows are

A to-one relation carries its own foreign key, so nothing more is needed. A to-many has no such column and needs one of three, which the compiler requires and the entity re-checks when it is first resolved:

Option	Use
mappedBy	The inverse side: names the field on the other entity that holds the foreign key or relation.
through	A junction entity with a foreign key to each side. Works for @ManyToMany and @OneToMany alike.
references	The join columns, by name, when neither convention fits.

With through, both join columns are read from the junction and named after the two entities (postId, tagId above). A junction missing one of them is also reported when the entity is resolved, rather than at the first query.

 

Querying Relations

When you query relations in UQL, scalar columns go in $select and related entities go in $populate. You can select specific fields from related entities or filter based on them.

You write

// Assuming `querier` is obtained from the pool
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' }
  }
});

Generated SQL (PostgreSQL)

-- Main query with LEFT JOIN for ManyToOne
SELECT "Post"."id", "Post"."title",
       "author"."id" "author.id", "author"."name" "author.name"
FROM "Post"
LEFT JOIN "User" "author" ON "author"."id" = "Post"."authorId"
WHERE EXISTS (
  SELECT 1 FROM "User"
  WHERE "User"."id" = "Post"."authorId" AND "User"."name" = $1
)

Generated SQL (PostgreSQL, separate query)

-- ManyToMany tags loaded via a second query
SELECT "Tag"."name", "PostTag"."postId"
FROM "Tag"
INNER JOIN "PostTag" ON "PostTag"."tagId" = "Tag"."id"
WHERE "PostTag"."postId" IN ($1, $2, ...)
  AND "Tag"."name" ILIKE $3

Check the querying relations section for more advanced examples on deep filtering and selection.