PostgreSQL Question posted in
The official documentation can be found here.

TypeORM One-To-One Relationship Showing as Many-To-One/One-To-Many in PostgreSQL ERD

I’m using TypeORM with NestJS to establish a one-to-one relationship between two tables, but when I view the ERD in pgAdmin, it’s showing up as a many-to-one/one-to-many relationship instead. I have included my code snippets and an image of the ERD for reference.

@Entity({ name: 'users' })
@ObjectType()
export class User {
    @PrimaryGeneratedColumn()
    @Field((type) => Int)
    id: number

    @Column()
    @Field()
    username: string

    @Column({ nullable: true })
    @Field({ nullable: true })
    displayName?: string

    @OneToOne(() => UserSetting)
    @JoinColumn()
    @Field({ nullable: true })
    settings?: UserSetting
}

@Entity({ name: 'user_settings' })
@ObjectType()
export class UserSetting {
    @PrimaryColumn()
    @Field((type) => Int)
    userId: number

    @Column({ default: false })
    @Field({ defaultValue: false })
    receiveNotifications: boolean

    @Column({ default: false })
    @Field({ defaultValue: false })
    receiveEmails: boolean
}

enter image description here

In pgAdmin, you can check relationship, this should appear as one-to-one. Has anyone else encountered this? Could there be something I’m missing in the configuration?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The issue we encountered appear due to the pgAdmin bug as per the below references.

    The relationship we defined in code first approach is correct it is showing wrong relationship due to pgAdmin bugs. I have used DbBeaver to generate ER diagram and it shows correct relationship over there.


  2. 0

    Why PostgreSQL Shows it as Many-to-One/One-to-Many:

    I Think it’s because UserSetting didn’t have userId set as a primary key, PostgreSQL treated it as a One-to-Many relationship, where multiple settings could be linked to a single user.

    By making userId the primary key in UserSetting, you ensure that each user can only have one setting, thus enforcing the one-to-one relationship.

    Here’s how you can modify your entities:

    @Entity({ name: 'users' })
@ObjectType()
export class User {
    @PrimaryGeneratedColumn()
    @Field((type) => Int)
    id: number;

    @Column()
    @Field()
    username: string;

    @Column({ nullable: true })
    @Field({ nullable: true })
    displayName?: string;

    @OneToOne(() => UserSetting, (setting) => setting.user, { cascade: true })
    @JoinColumn({ name: 'userSettingId' })
    @Field({ nullable: true })
    settings?: UserSetting;
}

@Entity({ name: 'user_settings' })
@ObjectType()
export class UserSetting {
    @PrimaryColumn()
    @Field((type) => Int)
    userId: number;

    @OneToOne(() => User, (user) => user.settings)
    @JoinColumn({ name: 'userId' })
    user: User;

    @Column({ default: false })
    @Field({ defaultValue: false })
    receiveNotifications: boolean;

    @Column({ default: false })
    @Field({ defaultValue: false })
    receiveEmails: boolean;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search