We use cookies to enhance your experience on the site

Database relations - the bonds of the legion

You already have a table of legionaries and a table of legions. Each holds up well on its own - but how do you know who serves under whom? You could write the legion's name into a text column next to every legionary. That works until the first typo: "Legio X Equestris" and "Legio X Equestriss" are two different legions as far as the database is concerned, and you have just split a cohort in two.

The Romans knew a better way. A legionary does not carry the legion's name - he carries its number. One legion, one number, everyone pointing at the same entry in the register. That is a relation: a link based on an identifier rather than on retyped text.

In this lesson you will meet three kinds of such bonds and learn to read them from the code's side.

One to many - a legion and its legionaries

Let's start with the most common arrangement. A legion has many legionaries; a legionary belongs to one legion. This relation has two sides and both must be described - each in its own entity:

1@Entity()
2export class Legion {
3  @OneToMany(() => Legionary, (legionary) => legionary.legion)
4  legionaries: Legionary[];
5}
6
7@Entity()
8export class Legionary {
9  @ManyToOne(() => Legion, (legion) => legion.legionaries)
10  legion: Legion;
11}

The key is the rule that tells you where to put what: a decorator describes the side it stands on. A legion is the "one", and it has "many" legionaries - so

@OneToMany

sits in the

Legion

entity and its field is an array. A legionary is one of the "many" and belongs to "one" legion - so

@ManyToOne

sits in the

Legionary

entity and its field is singular. Whenever you hesitate, read the sentence from the side of the entity you are writing: "a legionary belongs to one legion" - and there is your

@ManyToOne

Let's look at the decorator's two arguments, because both are required and both confuse. The first,

() => Legionary

, points at the entity on the other side. Why a function rather than the class name itself? Because both entities refer to each other, and in such a loop one of the classes would not yet be defined at the moment of reading. The function defers that lookup until both exist.

The second argument,

(legionary) => legionary.legion

, is the inverse side: it points at the field in that entity which describes the same relation. Thanks to it, TypeORM knows that

Legion.legionaries

and

Legionary.legion

are two ends of one bridge, not two independent relations.

And what happens in the database? The identifier column is created only on the

@ManyToOne

side - a

legionId

appears in the legionaries table. The legions table does not change at all. That is the "number carried with you": every legionary points at his legion, not the other way round.

Many to many - legionaries and skills

The second arrangement: a legionary has mastered many skills, and each skill has been mastered by many legionaries. Here neither side can store the identifier - a single column will not hold a list.

1@Entity()
2export class Legionary {
3  @ManyToMany(() => Skill)
4  @JoinTable()
5  skills: Skill[];
6}
7
8@Entity()
9export class Skill {
10  @ManyToMany(() => Legionary, (legionary) => legionary.skills)
11  legionaries: Legionary[];
12}

The solution is a separate intermediate table in which one row is one pair: this legionary knows this skill. The

@JoinTable()

decorator creates it for you - and this is the detail most people trip on: @JoinTable()
goes on one side only, the one we treat as the relation's owner. Putting it on both sides ends in two intermediate tables describing the same thing. Putting it on neither ends in an error at application startup.

Note that

@ManyToMany

on the

Legionary

side does without the second argument. The inverse side is optional: you give it when you also want to reach the links from the other end - here, to read from a skill who has mastered it.

One to one - a legionary and his map

The third arrangement is the simplest: a legionary has one personal tribute map, and the map belongs to one legionary.

1@Entity()
2export class Legionary {
3  @OneToOne(() => TributeMap)
4  @JoinColumn()
5  map: TributeMap;
6}

Here you decide yourself which table gets the identifier column -

@JoinColumn()

marks it. Placed in the

Legionary

entity, it makes the legionaries table receive a

mapId

column. Do not confuse it with

@JoinTable()

from the previous section:

@JoinColumn

marks a column in an existing table,

@JoinTable

creates a whole new intermediate table.

Fetching relations

A relation described in an entity does not mean the data arrives by itself. By default

find()

brings the record alone and the relation field stays empty - deliberately, because otherwise fetching one legionary would drag half the database along. You pull the links in on purpose:

1const legionaries = await this.legionaryRepo.find({
2  relations: ['legion', 'skills'],
3});

The

relations

key lists what should travel with the record. This is explicit loading and the default way of working: in every query you decide separately what you need.

There is an automatic variant too. Adding

eager: true

to a relation makes it arrive always, unasked:

1@ManyToOne(() => Legion, (legion) => legion.legionaries, { eager: true })
2legion: Legion;

Convenient - and dangerous for exactly that reason.

eager

applies to every query for legionaries, including those where the legion is entirely unnecessary, and quietly weighs down queries across the whole application. Note also what

eager

does not change: it is still one query with a JOIN, merely performed always instead of on demand. That is why I recommend sticking with

relations

and reaching for

eager

only exceptionally, @name - when the relation truly is needed every single time.

Summary

The legion holds together, and you can describe its bonds:

a relation links tables through an identifier, not through retyped text,
a decorator describes the side it stands on:
```
@OneToMany
```
on the "one" side (array field),
```
@ManyToOne
```
on the "many" side (singular field),
the first argument is a
```
() => Entity
```
function - it defers reading the class, because entities point at each other,
the second argument marks the inverse side, the other end of the same bridge,
the identifier column is created on the
```
@ManyToOne
```
side,
```
@ManyToMany
```
needs an intermediate table:
```
@JoinTable()
```
on one side only,
```
@OneToOne
```
with
```
@JoinColumn()
```
decides which table gets the column -
```
@JoinColumn
```
marks a column,
```
@JoinTable
```
creates a new table,
you fetch relations explicitly with
```
relations: ['...']
```
;
```
eager: true
```
does it always, so use it sparingly.

In the next lesson you will meet repositories - the archivists who reach for this data on behalf of services. For now remember: a relation is a number carried with you - one entry in the register that everyone points at, instead of a name retyped next to every legionary.

Go to CodeWorlds→