Postgres TypeORM - FK to PK that is also FK to another table - constraint error

user_18588668 0
Viewed 277

In my NestJS application with postgres db I have these 3 entities: User, Writer, Book and a class named UserBaseEntity.

export class UserBaseEntity {

    @PrimaryColumn()
    id: string;

    @OneToOne(() => UserEntity, { primary: true, cascade: true })
    @JoinColumn({ name: 'id'})
    user: UserEntity

@Entity()
export class WriterEntity extends UserBaseEntity implements Writer {

    @Column()
    name: string;

    @OneToMany(() => BookEntity)
    books: BookEntity[];


@Entity()
export class BookEntity extends BaseEntity {

  @Column()
  name: string;

  @ManyToOne(() => WriterEntity, writer => writer.books)
  @JoinColumn({referencedColumnName: 'id', name: 'writerId'})
  writer?: WriterEntity;

  @Column()
  writeId: string;

Writer extends UserBaseEntity class which makes his Primary key(id) be be a foreign key to UserEntity table(id).

When I tried to make One To Many relation between Writer and Book I am getting this error

QueryFailedError: foreign key constraint "FK_XXX" cannot be implemented

or

writerId violates not-null constraint

So, there is a problem I guess with Postgres when there is this kind of situation: book.writerId(FK) -> writer.id(FK) -> user.id

Any idea that maybe can help?

A solution I have found is to move the oneToMany decorator from writer entity to user entity. I hope to find a more elegant and logic suitable way.

Thanks

typeorm nestjs typescript postgresql database
1 Answers

My educated guess: inheritance is the the problem here

What I think happened:

The class UserBaseEntity is not abstract so it is probably being used more than once. Why is this problematic? Well, the definition itself states that the user field is defined with relation to id field. When directly used - an inner relation is created (so far so good). But, when used by WriterEntity the table is created with primary column id (so far so good) and user field which makes a relation on this table to its id column (here things are getting sideways). So, we define an invalid relation (between user field and id field for WriterEntity table) and also try to create another relation from BookEntity to this column as well (this relation has a different type). That breaks consistency and normalization rules so it will fail or produce with wrong DB schema.

My 2 cents:

When using ORMs solutions, inheritance should be limited to primary keys and common fields. Any other definition in a parent class may result in duplicates (at "best") or invalid relations which will result with errors. In person I think that the problem inheritance solves is very different from what class represents in ORMs (e.g. defining a schema) and that's why as a rule of thumb - I'm not using inheritance when using ORMs (at all). I believe that creating a parent class and allowing inheritance results in a space that encourage developers to make mistakes as a function of time and prefer to avoid this scenario (you may consider this as an act of enforcement without OOP)

How should you fix this: Just remove the user field from UserBaseEntity or stop using inheritance

user_40621970
Related