TypeORM QueryBuilder Insert Into Table With Foreign Key Constraint

Question

I have two tables - organizations and departments. The departments table has a foreign key constraint with organizations on the organizationId column. I obviously have lots of other foreign key relationships for these two tables. This is just on use case.

Doing a select statement on both tables works fine:

import "reflect-metadata";
import {createConnection} from "typeorm";
import {getConnection} from "typeorm";
import {Departments} from "./entity/Departments"

createConnection().then(async connection => {

    //get total estimated hours by month
    const departments = await getConnection()
        .createQueryBuilder( "Departments", "d")
        .innerJoinAndSelect("organizations", "o", "d.organizationId = o.organizationId")
        .select("organizationName, departmentName ")
        .where("d.organizationId = :organizationId", {organizationId: 2})
        .getRawMany();
    //  .getSql();

    console.log("Departments: ", departments);
}).catch(error => console.log(error));

When I try to do an insert into Departments using QueryBuilder, organizationId isn't recognized as a valid column:

import "reflect-metadata";
import {createConnection} from "typeorm";
import {getConnection} from "typeorm";
import {Departments} from "./entity/Departments"

createConnection().then(async connection => {

    //get total estimated hours by month
    await getConnection()
        .createQueryBuilder()
        .insert()
        .into(Departments)
        .values([
            { departmentName: "Test Product", organizationId: 2}
            ])
        .execute();
    //  .getSql();

}).catch(error => console.log(error));

Here's the entity code for both organizations and departments:

Organizations.ts -

import {
  Column,
  Entity,
  Index,
  OneToMany,
  PrimaryGeneratedColumn,
} from "typeorm";
import { Departments } from "./Departments";
import { Groups } from "./Groups";
import { IntegrationDetails } from "./IntegrationDetails";
import { Jobcodes } from "./Jobcodes";
import { JobMaster } from "./JobMaster";
import { Locations } from "./Locations";
import { Phases } from "./Phases";
import { RoadmapActualCostDetails } from "./RoadmapActualCostDetails";
import { RoadmapEstimatedCostDetails } from "./RoadmapEstimatedCostDetails";
import { RoadmapEstimates } from "./RoadmapEstimates";
import { RoadmapTemplates } from "./RoadmapTemplates";
import { Strategies } from "./Strategies";
import { Teammates } from "./Teammates";

@Index("organizations_organizationId_index", ["organizationId"], {})
@Index("organizations_pk", ["organizationId"], { unique: true })
@Entity("organizations", { schema: "dbo" })
export class Organizations {
  @PrimaryGeneratedColumn({ type: "int", name: "organizationId" })
  organizationId: number;

  @Column("varchar", { name: "organizationName", length: 100 })
  organizationName: string;

  @Column("varchar", { name: "orgShortName", length: 50 })
  orgShortName: string;

  @OneToMany(() => Departments, (departments) => departments.organization)
  departments: Departments[];

  @OneToMany(() => Groups, (groups) => groups.organization)
  groups: Groups[];

  @OneToMany(
    () => IntegrationDetails,
    (integrationDetails) => integrationDetails.organization
  )
  integrationDetails: IntegrationDetails[];

  @OneToMany(() => Jobcodes, (jobcodes) => jobcodes.organization)
  jobcodes: Jobcodes[];

  @OneToMany(() => JobMaster, (jobMaster) => jobMaster.organization)
  jobMasters: JobMaster[];

  @OneToMany(() => Locations, (locations) => locations.organization)
  locations: Locations[];

  @OneToMany(() => Phases, (phases) => phases.organization)
  phases: Phases[];

  @OneToMany(
    () => RoadmapActualCostDetails,
    (roadmapActualCostDetails) => roadmapActualCostDetails.organization
  )
  roadmapActualCostDetails: RoadmapActualCostDetails[];

  @OneToMany(
    () => RoadmapEstimatedCostDetails,
    (roadmapEstimatedCostDetails) => roadmapEstimatedCostDetails.organization
  )
  roadmapEstimatedCostDetails: RoadmapEstimatedCostDetails[];

  @OneToMany(
    () => RoadmapEstimates,
    (roadmapEstimates) => roadmapEstimates.organization
  )
  roadmapEstimates: RoadmapEstimates[];

  @OneToMany(
    () => RoadmapTemplates,
    (roadmapTemplates) => roadmapTemplates.organization
  )
  roadmapTemplates: RoadmapTemplates[];

  @OneToMany(() => Strategies, (strategies) => strategies.organization)
  strategies: Strategies[];

  @OneToMany(() => Teammates, (teammates) => teammates.organization)
  teammates: Teammates[];
}

Departments.ts

export class Departments {
  @PrimaryGeneratedColumn({ type: "int", name: "departmentId" })
  departmentId: number;

  @Column("nvarchar", { name: "departmentName", length: 100 })
  departmentName: string;

  @Column("int", { name: "ownerId", nullable: true })
  ownerId: number | null;

  @ManyToOne(() => Organizations, (organizations) => organizations.departments)
  @JoinColumn([
    { name: "organizationId", referencedColumnName: "organizationId" },
  ])
  organization: Organizations;

  @ManyToOne(() => Departments, (departments) => departments.departments)
  @JoinColumn([{ name: "parentId", referencedColumnName: "departmentId" }])
  parent: Departments;

  @OneToMany(() => Departments, (departments) => departments.parent)
  departments: Departments[];

  @OneToMany(() => Products, (products) => products.department)
  products: Products[];

  @ManyToMany(() => Programs, (programs) => programs.departments)
  @JoinTable({
    name: "programs_departments",
    joinColumns: [
      { name: "departmentId", referencedColumnName: "departmentId" },
    ],
    inverseJoinColumns: [
      { name: "programId", referencedColumnName: "programId" },
    ],
    schema: "dbo",
  })
  programs: Programs[];

  @OneToMany(() => Roadmaps, (roadmaps) => roadmaps.department)
  roadmaps: Roadmaps[];
}

Answer 1

Never mind - I figured it out. Even though the ManyToOne relationships are correctly defined in the entity files, you still have to add a @Column reference.

@Column("int", { name: "organizationId" }) organizationId: number;

Is related to this - Typeorm insert with relationId