Relationships

Define and query relationships between your models

Relationship Types

Stabilize supports four relationship types:

RelationType.OneToOne - Each record in A relates to exactly one in B
RelationType.ManyToOne - Many records in A relate to one in B
RelationType.OneToMany - One record in A relates to many in B
RelationType.ManyToMany - Many records in A relate to many in B (uses join table)

One-to-Many

A user can have many posts:

models/User.ts

import { defineModel, DataTypes, RelationType } from "stabilize-orm";

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 100 },
    email: { type: DataTypes.STRING, length: 255, required: true, unique: true },
  },
  relations: [
    {
      type: RelationType.OneToMany,
      target: () => Post,
      property: "posts",
      // The key lives on the target table, so a OneToMany names it with
      // inverseKey. foreignKey is accepted here too, as a synonym.
      inverseKey: "authorId",
    },
  ],
});

export const Post = defineModel({
  tableName: "posts",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    title: { type: DataTypes.STRING, length: 200, required: true },
    body: { type: DataTypes.TEXT },
    authorId: { type: DataTypes.STRING, required: true },
  },
  relations: [
    {
      type: RelationType.ManyToOne,
      target: () => User,
      property: "author",
      foreignKey: "authorId",
    },
  ],
});

One-to-One

A user has one profile:

models/Profile.ts

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    email: { type: DataTypes.STRING, length: 255, required: true, unique: true },
  },
  relations: [
    {
      type: RelationType.OneToOne,
      target: () => Profile,
      property: "profile",
      foreignKey: "userId",
    },
  ],
});

export const Profile = defineModel({
  tableName: "profiles",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    userId: { type: DataTypes.STRING, required: true, unique: true },
    bio: { type: DataTypes.TEXT },
  },
});

Many-to-Many

Users can have many roles, roles can belong to many users:

models/Role.ts

export const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 100 },
  },
  relations: [
    {
      type: RelationType.ManyToMany,
      target: () => Role,
      property: "roles",
      joinTable: "user_roles",
      foreignKey: "userId",
      inverseKey: "roleId",
    },
  ],
});

export const Role = defineModel({
  tableName: "roles",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    name: { type: DataTypes.STRING, length: 50, required: true },
  },
  relations: [
    {
      type: RelationType.ManyToMany,
      target: () => User,
      property: "users",
      joinTable: "user_roles",
      foreignKey: "roleId",
      inverseKey: "userId",
    },
  ],
});

Eager Loading

Pass relations to load related rows alongside the parent. Each relation is fetched with one batched query, so a to-many relation is never truncated by a LIMIT and never multiplies the parent rows. A to-many relation comes back as an array (empty when nothing is linked), a to-one relation as the row or null.

examples/eager-loading.ts

const userRepo = orm.getRepository(User);
const postRepo = orm.getRepository(Post);

// Nested paths use dot notation
const user = await userRepo.findOne(1, {
  relations: ["posts", "profile"],
});
user.posts;  // Post[] — empty array if the user has none
user.profile; // Profile | null

// The same option is accepted by create, bulkCreate, findMany,
// findBy, findOneBy and findAndCount
const posts = await postRepo.findBy(
  { authorId: 1 },
  { relations: ["author"] },
);

On the query builder, use withRelations(), which composes with where, limit and paginate:

examples/with-relations.ts

const users = await userRepo
  .find()
  .where("isActive = ?", true)
  .withRelations("posts", "posts.comments")
  .limit(10)
  .execute(orm.client);

Related rows are read through the target model, so its soft-delete filter applies: a deleted child is not returned as part of its parent.

Managing Many-to-Many Links

attach, detach and sync write the join table directly, so a relation can be edited without loading and re-saving either side.

examples/link-management.ts

const userRepo = orm.getRepository(User);

// Links roles 1 and 2; already-linked pairs are left alone
await userRepo.attach(userId, "roles", [1, 2]);

// Unlinks role 2, or every role when no ids are given
await userRepo.detach(userId, "roles", [2]);
await userRepo.detach(userId, "roles");

// Makes the link set exactly [3, 4] and reports what changed
const { attached, detached } = await userRepo.sync(userId, "roles", [3, 4]);

// Link management needs a ManyToMany relation; anything else throws
await userRepo.attach(userId, "profile", [1]); // StabilizeError

Querying with JOINs

Use the query builder to load related data via SQL JOINs:

examples/query-relationships.ts

const postRepo = orm.getRepository(Post);

// Find posts with author name
const postsWithAuthor = await postRepo
  .find()
  .join("users", "posts.authorId = users.id")
  .select("posts.*", "users.name as authorName")
  .execute(orm.client);

// Many-to-many: users with roles
const userRepo = orm.getRepository(User);
const usersWithRoles = await userRepo
  .find()
  .join("user_roles", "users.id = user_roles.userId")
  .join("roles", "user_roles.roleId = roles.id")
  .select("users.*", "roles.name as roleName")
  .execute(orm.client);

// Count posts per user
const userPostCounts = await userRepo
  .find()
  .leftJoin("posts", "users.id = posts.authorId")
  .select("users.name", "COUNT(posts.id) as postCount")
  .groupBy("users.id", "users.name")
  .execute(orm.client);