Relationships

Connections between tables

In this section, we will explore how to create different types of relationships between tables in my ORM system using the OneToOne, OneToMany, ManyToMany, and ForeignKey decorators.

One-to-One

A "one-to-one" relationship means that one record in one table corresponds to exactly one record in another table. For example, each user has one associated profile.

import {
    Boolean,
    Column,
    OneToOne,
    PrimaryGeneratedColumn,
    String,
    Table
} from "@myroslavshymon/orm";
import {Sections} from "./sections";

@Table({name: 'users'})
export class Users {
    @PrimaryGeneratedColumn({type: 'BIGINT'})
    user_id: number;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {unique: true, nullable: false}})
    username: string;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {nullable: false}})
    email: string;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {nullable: false}})
    password: string;

    @Column({options: {dataType: 'BOOLEAN', defaultValue: false}})
    is_active: boolean;

    @Column({options: {dataType: "BIGINT"}})
    section_id: number;

    @OneToOne({ table: 'sections', foreignKey: 'section_id', referenceColumn: 'section_id' })
    section: Sections;
}

One-to-Many

A "one-to-many" relationship means that one record in one table can correspond to many records in another table. For example, one user may have many tasks.

import {
    Boolean,
    Column,
    OneToMany,
    PrimaryGeneratedColumn,
    String,
    Table
} from "@myroslavshymon/orm";
import {Tasks} from "./tasks";

@Table({name: 'users'})
export class Users {
    @PrimaryGeneratedColumn({type: 'BIGINT'})
    user_id: number;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {unique: true, nullable: false}})
    username: string;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {nullable: false}})
    email: string;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {nullable: false}})
    password: string;

    @Column({options: {dataType: 'BOOLEAN', defaultValue: false}})
    is_active: boolean;

    @Column({options: {dataType: "BIGINT"}})
    task_id: number
    @OneToMany({ foreignKey: 'task_id', referenceColumn: 'task_id' })
    tasks: Tasks[];
}

Many-to-Many

A "many-to-many" relationship means that many records in one table can correspond to many records in another table. To implement such a relationship, a join table is typically required.

Table Sections:

import {
    Column, 
    ManyToMany, 
    PrimaryGeneratedColumn,
    String,
    Table
} from "@myroslavshymon/orm";
import {Sections} from "./sections";

@Table({name: 'users'})
export class Users {
    @PrimaryGeneratedColumn({type: 'BIGINT'})
    user_id: number;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {unique: true, nullable: false}})
    username: string;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {nullable: false}})
    email: string;

    @String({type: "VARCHAR", length: 255})
    @Column({options: {nullable: false}})
    password: string;

    @Column({options: {dataType: 'BOOLEAN', defaultValue: false}})
    is_active: boolean;

    @ManyToMany({foreignKey: 'section_id', referencedColumn: 'section_id'})
    sections: Sections[]
}

Table Users:

import {Column, ManyToMany, PrimaryGeneratedColumn, Table} from "@myroslavshymon/orm";
import {User} from "../other/user.entity";

@Table({ name: 'sections' })
export class Sections {
    @PrimaryGeneratedColumn({ type: 'BIGINT' })
    section_id: number;

    @Column({ options: { dataType: "BIGINT" } })
    user_id: number;

    @ManyToMany({foreignKey: 'user_id', referencedColumn: 'user_id'})
    users: User[]
}

ForeignKey

The ForeignKey decorator is used to define a foreign key. For example, each task is associated with a user through the foreign key user_id.

import {
    Boolean,
    Column,
    ComputedColumn,
    ForeignKey,
    Numeric,
    PrimaryGeneratedColumn,
    String,
    Table
} from "@myroslavshymon/orm";

@Table({ name: 'tasks' })
export class Tasks {
    @PrimaryGeneratedColumn({ type: 'BIGINT' })
    task_id: number;

    @String({ type: "VARCHAR", length: 255 })
    @Column({ options: { nullable: false } })
    title: string;

    @String({ type: "TEXT" })
    @Column({ options: { nullable: true } })
    description: string;

    @Boolean()
    @Column({ options: { nullable: false, defaultValue: false } })
    is_completed: boolean;

    @Column({ options: { dataType: 'DATE', nullable: true } })
    due_date: Date;

    @ComputedColumn({ dataType: 'VARCHAR', calculate: "title || ' - ' || CASE WHEN is_completed THEN 'Completed' ELSE 'Pending' END" })
    status: string;

    @Column({ options: { dataType: 'TIMESTAMP', defaultValue: 'CURRENT_TIMESTAMP' } })
    created_at: Date;

    @Column({ options: { dataType: 'TIMESTAMP', defaultValue: 'CURRENT_TIMESTAMP' } })
    updated_at: Date;

    @Numeric({ type: 'NUMERIC', precision: 10, scale: 2 })
    @Column({ options: { check: 'price >= 0', nameOfCheckConstraint: 'check_price' } })
    price: number;

    @ForeignKey({ table: 'users', key: 'user_id' })
    @Column({ options: { dataType: "BIGINT" } })
    user_id: number;
}

These examples demonstrate how to use different types of relationships in my ORM system to model relationships between tables in a database.