Relations

Foreign keys

A foreign key is declared on the column with @ColumnOption.References(column), completed by @ReferenceOption:

import {Column, ColumnOption, Join, QueryRow, ReferenceOption, Table} from '@fca.gg/orm';
import {User} from './User';

@Table('posts')
export class Post extends QueryRow {
  @Column.Increment()
  @ColumnOption.Primary()
  private id!: number;

  @Column.Integer()
  @ColumnOption.NotNullable()
  @ColumnOption.Unsigned()
  @ColumnOption.References('id')
  @ReferenceOption.InTable('users')
  @ReferenceOption.OnDelete('CASCADE')
  private user_id!: number;

  @Join(User)
  private user!: User;

  @Column.String(255)
  @ColumnOption.NotNullable()
  private title!: string;

  public getId(): number {
    return this.id;
  }

  public getUser(): User {
    return this.user;
  }
}

Reference options:

• InTable(table): the referenced table;
• OnDelete(action) and OnUpdate(action): 'CASCADE', 'SET NULL', 'RESTRICT'…;
• WithKeyName(name): the constraint name;
• Deferrable(type): when the constraint is checked (PostgreSQL).

Joins

@Join(Model) on a property loads the related row on every read. By default, the join:

• is an INNER join;
• uses the <property>_id column (here user_id);
• compares it to the id column of the joined table.

const post = await Post.findOneById(1);
post?.getUser().getName(); // the user is already loaded

To change these settings, pass a column and options:

@Join(User, 'author_id', {type: 'LEFT', alias: 'author', references: 'id'})
private author!: User;

• type: 'INNER', 'LEFT', 'RIGHT', 'LEFT OUTER' or 'RIGHT OUTER';
• alias: the alias of the joined table, useful to join the same table twice;
• operator: the comparison operator (= by default);
• references: the column of the joined table (id by default).

Joins chain: if User declares joins of its own, they are loaded too.

Reading the key column (post.user_id) returns the ID of the joined row, read from the loaded object.