#Relations

Declare how entities relate to one another once, then read the graph with include. The whole tree arrives in one statement.

1import { z } from "alepha";2import { $entity, $relations, $repositories, $repository, db } from "alepha/orm";

Relations are the layer above joins. A join is a SQL join you write per query; a relation is a name you declare once and reuse. Relations also do what a join cannot: one-to-many without multiplying rows, many-to-many without exposing the junction, and arbitrary nesting.

#Declaring the graph

Relations live in their own statement, not on $entity:

 1const teams = $entity({ 2  name: "teams", 3  schema: z.object({ 4    id: db.primaryKey(), 5    name: z.text(), 6  }), 7}); 8  9const players = $entity({10  name: "players",11  schema: z.object({12    id: db.primaryKey(),13    teamId: db.ref(z.integer(), () => teams.cols.id),14    name: z.text(),15    mentorId: db.ref(z.integer().optional(), () => players.cols.id),16  }),17});18 19const tournaments = $entity({20  name: "tournaments",21  schema: z.object({22    id: db.primaryKey(),23    title: z.text(),24  }),25});26 27/** Junction: which team entered which tournament. */28const entries = $entity({29  name: "entries",30  schema: z.object({31    id: db.primaryKey(),32    teamId: db.ref(z.integer(), () => teams.cols.id),33    tournamentId: db.ref(z.integer(), () => tournaments.cols.id),34  }),35  indexes: [{ columns: ["teamId", "tournamentId"], unique: true }],36});37 38export const schema = { teams, players, tournaments, entries };39 40export const relations = $relations(schema, (r) => ({41  teams: {42    players: r.many.players({ from: r.teams.id, to: r.players.teamId }),43  },44  players: {45    team: r.one.teams({ from: r.players.teamId, to: r.teams.id }),46    mentor: r.one.players({ from: r.players.mentorId, to: r.players.id }),47  },48}));

Both sides of every join are typed. Swap r.teams.id for r.teams.name in the players relation and it stops compiling, because a text column cannot pair with players.teamId.

#Why relations are a separate statement

They cannot be inferred from db.ref, and they cannot be nested inside $entity. players.mentorId is the reason: a self-reference makes the entity's own type depend on itself, and TypeScript gives up with TS7022. The () => any inside db.ref is what keeps that from happening, which also means the reference carries no type information for anything else to read.

Declaring relations separately sidesteps it entirely — by the time $relations runs, every entity is already a complete type.

#The graph can be partial

A $relations schema does not have to cover the application. Five entities out of twenty-three is a normal starting point: a relational repository and a plain one address the same tables and coexist, so one controller migrates while everything else stays as it was.

#Getting a repository

Two shapes, same object underneath.

1class TeamService {2  /** One binding for every entity in the schema. */3  db = $repositories(relations);4 5  /** ...or just the one this class needs. */6  teams = $repository(relations, "teams");7}

$repository(entity) — the plain form — is unchanged. The two-argument form takes the relations and the key of the entity within its schema.

#Reading

#include

1const team = await db.teams.findOne({2  where: { name: { eq: "Rovers" } },3  include: { players: true },4});5 6team?.players[0]?.name; // fully inferred

A many relation yields an array; a one yields T | undefined. A relation you did not include is absent from the type, so reading it is a compile error rather than undefined at runtime.

An empty to-many is [], never undefined — which is the case a LEFT JOIN gets wrong.

#Nesting

1const team = await db.teams.findOne({2  where: { id: { eq: 1 } },3  include: { players: { include: { mentor: true } } },4});5 6team?.players[0]?.mentor?.name;

Depth costs columns, not round trips: each level becomes a subquery inside the same statement.

#Many-to-many

Both sides hop through the junction with .through(), and each names its own column on it:

 1const relations = $relations(schema, (r) => ({ 2  teams: { 3    tournaments: r.many.tournaments({ 4      from: r.teams.id.through(r.entries.teamId), 5      to: r.tournaments.id.through(r.entries.tournamentId), 6    }), 7  }, 8  tournaments: { 9    teams: r.many.teams({10      from: r.tournaments.id.through(r.entries.teamId),11      to: r.teams.id.through(r.entries.teamId),12    }),13  },14}));

The junction never appears in the result — a row reached through it is a plain target row, with no link columns bolted on. Hopping only one side throws at declaration time, because there would be nothing to match on the other.

A many-to-many de-duplicates only if the junction does. include returns what the join returns. If a team could hold two entries rows for the same tournament, that tournament comes back twice and nothing in the response type would catch it. The unique index on (teamId, tournamentId) above is what makes the relation safe — worth a test of its own next to the endpoints that rely on it.

#Shaping a relation

A relation is a query, so it takes the same vocabulary as the root:

 1const team = await db.teams.findOne({ 2  where: { id: { eq: 1 } }, 3  include: { 4    players: { 5      where: { name: { like: "A%" } }, 6      orderBy: { column: "name", direction: "asc" }, 7      limit: 5, 8      select: ["id", "name"], 9    },10  },11});

limit caps rows per parent, not across the result — which is the other thing a plain join cannot do without truncating children instead of parents.

#Projection

select narrows the row and its type:

1const teams = await db.teams.findMany({2  select: ["name"],3  include: { players: true },4});5 6teams[0]?.name;7// teams[0].id is a compile error — it was projected away

Projecting the parent does not break its relations: the join key is still read internally and does not resurface in the result.

#Filtering by a relation

A where key that names a declared relation takes a nested where describing the related rows, and compiles to EXISTS:

1// Teams that have a player called Ana.2const found = await db.teams.findMany({3  where: { players: { name: { eq: "Ana" } } },4});

Because it is an EXISTS, the root rows are not multiplied and nothing needs de-duplicating afterwards. Column filters and relation filters combine, and relation filters nest:

 1await db.players.findMany({ 2  where: { 3    name: { like: "A%" }, 4    team: { name: { eq: "Rovers" } }, 5  }, 6}); 7  8// Two levels deep. 9await db.players.findMany({10  where: { team: { players: { name: { eq: "Ana" } } } },11});

{} is meaningful — the join condition alone is a presence check:

1// Teams with at least one player.2await db.teams.findMany({ where: { players: {} } });

Every operator works at any depth: inArray, like, between, all of them.

#Soft delete and tenancy

Both apply automatically, at every level of the tree, including a relation included with a bare true. The predicate is the one the repository itself would use — so an entity marked db.organization({ strict: true }) still refuses a read with no resolved tenant, rather than returning every tenant's rows.

A relation filter inherits the same rule: a soft-deleted player does not make its team match.

#force

Some views want the history a soft delete hides — a crash inbox still shows reports from a source that has since been revoked. force is the same flag the plain repository takes:

1await db.teams.findMany({2  include: { players: { force: true } },3});

It applies to the level that asked for it, so a forced parent does not quietly un-hide everything hanging off it.

#Writing

create understands nested data and runs the whole graph in one transaction:

1const team = await db.teams.create({2  data: {3    name: "Rovers",4    players: {5      create: [{ name: "Ana" }, { name: "Bo" }],6    },7  },8  include: { players: true },9});

Ordering is forced by where each foreign key lives: a to-one related row is created first, because this row's key points at it; a to-many child is created after, because its key points back. A failure part-way through leaves no half-built graph behind.

update and upsert take where / data as an options object and accept include on the result:

1await db.teams.update({2  where: { id: { eq: 1 } },3  data: { name: "Rovers FC" },4  include: { players: true },5});

Everything else — createMany, save, aggregate, raw query — is reached through .base, which is the fully typed plain repository:

1await db.teams.base.aggregate({ select: { id: { count: true } } });

#What it costs

One statement, whatever the shape. Each included relation becomes a subquery, using the strategy the dialect is best at:

toSQL() returns the statement without running it, which is how you get it in front of EXPLAIN:

1const { sql, params } = db.teams.toSQL({2  where: { id: { eq: 1 } },3  include: { players: true },4});

It needs a query with relations — without them the read goes through the plain repository and there is no gap to inspect.

Every table a read touched is announced on repository:read:before, root first, so a cache keyed per table sees the whole tree rather than only its root.

#Limitations

• A relation filter cannot go inside and / or / not. Those compile to one SQL expression, and an EXISTS cannot be folded into it. Lift the relation filter to the top level of the where; anything else is refused with an explanatory error rather than quietly matching nothing.
• count cannot carry a relation filter. There is no count on the relational engine, so the predicate cannot reach a COUNT(*). It is refused rather than returning a number that ignored the filter. The same applies to paginate(..., { count: true }).
• Only foreign keys are relations. A uuid[] column of ids, or a polymorphic id column told apart by a discriminator, is not a foreign key — those lookups stay explicit.
• No aggregates through relations. Use .base.aggregate() or raw SQL.
• paginate pages the root only. Relations are resolved in full for the rows on that page, which is the point: page size bounds the work.