TypeORM create a record with the ForeignKey

Question

I have two entities questionnaires and sections. A questionnaire has multiple sections so the relation is OneToMany.

When I try to create a new record for the questionnaire - it works just fine. I get an id and then I create a section record and reference the id there.

Response, when I try to create a section, is this

{
  "name": "Section 1",
  "questionnaire_id": 1,
  "description": "Section 1 description",
  "id": 1
}

As you see it returns questionnaire_id back but the problem is that that record in DB is questionnaire_id=null. If I change it manually and assign an id value to it then it works and I get my JSON document with sections in it.

If I fetch all records from the DB then I get this

[
  {
    "id": 1,
    "name": "Section 1",
    "description": "Section 1 description"
  }
]

questionnaire_id is not present because it's null. 😕.

Do you know what I might be doing wrong? I have a feeling it has something to do with Entity relation but not sure what exactly.

@Entity({ name: 'questionnaires' })
export class Questionnaire {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 128 })
  name: string;

  @Column({ type: 'enum', enum: STATUS, default: STATUS.ACTIVE })
  status: STATUS;

  @OneToMany(() => Section, (section) => section.questionnaire)
  @JoinColumn({ name: 'questionnaire_id' })
  sections: Section[];
}

@Entity({ name: 'sections' })
export class Section {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 128 })
  name: string;

  @Column({ type: 'text', default: '' })
  description: string;

  @ManyToOne(() => Questionnaire, (questionnaire) => questionnaire.sections)
  questionnaire: Questionnaire;
}

The goal is to get all the questionnaires with their' sections.

The way I'm trying to achieve is this

@Injectable()
export class QuestionnaireService {
  constructor(@InjectRepository(Questionnaire) private readonly questionnaire: Repository<Questionnaire>) {}

  create(createQuestionnaireDto: CreateQuestionnaireDto) {
    return this.questionnaire.save(createQuestionnaireDto);
  }

  findAll() {
    return this.questionnaire.find({
      relations: ['sections'],
    });
  }
}

@Injectable()
export class SectionService {
  constructor(@InjectRepository(Section) private readonly sectionRepository: Repository<Section>) {}

  create(createSectionDto: CreateSectionDto) {
    return this.sectionRepository.save(createSectionDto);
  }

  findAll() {
    return this.sectionRepository.find();
  }
}

export class CreateSectionDto {
  @ApiProperty({
    description: 'Section name',
    example: 'Section 1',
  })
  @IsString()
  @IsNotEmpty()
  @MaxLength(128)
  name: string;

  @ApiProperty({
    description: 'Relation to the questionnaire',
    example: 1,
  })
  @IsNumber()
  questionnaire_id: number;

  @ApiProperty({
    description: 'Section description',
    example: 'Section 1 description',
    default: '',
  })
  @IsString()
  @IsOptional()
  @MaxLength(512)
  description?: string;
}

p.s.

I checked this one TypeORM insert row with foreign key

but did not work for me.

EDIT:

This is what query is executed when running insert command

query: START TRANSACTION
query: INSERT INTO "sections"("created_at", "updated_at", "name", "description", "questionnaire_id") VALUES (DEFAULT, DEFAULT, $1, $2, DEFAULT) RETURNING "created_at", "updated_at", "id", "description" -- PARAMETERS: ["Section 1","Section 1 description"]
query: COMMIT

I have "created_at", "updated_at" fields too but I did not include in the examples above.

As it's visible it includes questionnaire_id but for some reason the value is set to DEFAULT. Not passing the one I'm sending via http request.

SOLUTION

The problem was my approach of creating records. Since I've set up a DB relation and the Section's table is dependent on the Questionnaire's table - TypeORM does not allow me to create sections records with my FK value. The library handles it automatically if I created Questionnaire first and assigned Sections object to it.

With the configration above the line below works just fine

  async create(createQuestionnaireDto: CreateQuestionnaireDto) {
    const questionnaire = this.questionnaire.create(createQuestionnaireDto);
    const sections = new Section();
    sections.name = 'Default Section';
    sections.summary = 'Default Section Summary';
    sections.questionnaire = questionnaire;

    await this.questionnaire.save(questionnaire);
    await this.section.save(sections);

    return questionnaire;
  }

And the query it is running is this

query: START TRANSACTION
query: INSERT INTO "sections"("created_at", "updated_at", "name", "summary", "questionnaire_id") VALUES (DEFAULT, DEFAULT, $1, $2, $3) RETURNING "created_at", "updated_at", "id", "summary" -- PARAMETERS: ["Default Section","Default Section Summary",1]
query: COMMIT

As you can see there is no DEFAULT for questionnaire_id.

If I wanted my initial approach to make work I should not be using TypeORM relation decorators, which is a pretty bad idea.

Answer 1

As I have not used TypeORM very often so I am not totally sure about the answer but you can check this out.

So you probably doing wrong in defining your Entity relation. You are using @JoinColumn in Questionnaire Entity.

It should be defined in Sections Entity like below:

@Entity({ name: 'sections' })
export class Section {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 128 })
  name: string;

  @Column({ type: 'text', default: '' })
  description: string;

  @ManyToOne(() => Questionnaire, (questionnaire) => questionnaire.sections)
  @JoinColumn({ name: 'questionnaire_id' })
  questionnaire: Questionnaire;
}

The answer is based on this document https://typeorm.io/relations#joincolumn-options. Hope it solves your issue.

EDIT : (not related might be deprecated but mentioned in official docs) Are you sure loading relations this way works ?

findAll() {
    return this.questionnaire.find({
      relations: ['sections'],
    });
  }

As I can see in documents HERE, they are doing something else.

findAll() {
    return this.questionnaire.find({
      relations: {
              sections: true,
         },
    });
  }

EDIT 2:

So if you now add a Column into your section entity like this it will work.

@Entity({ name: 'sections' })
export class Section {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 128 })
  name: string;

  @Column({ type: 'text', default: '' })
  description: string;

  @Column({ type: 'number' })
  questionnaire_id: number;

  @ManyToOne(() => Questionnaire, (questionnaire) => questionnaire.sections)
  @JoinColumn({ name: 'questionnaire_id' })
  questionnaire: Questionnaire;
}

Let me know if this works.