CODEWITHSUNDEEP
node.jsnestjstypeorm
Felipe
Felipe

Reputation: 7381

TypeORM - How to insert into table with foreign key without fetching relation first

I am creating a small app using NestJS and TypeORM and I am having trouble inserting into a table that has a composite foreign key.

Whenever the insert is executed, all columns get filled, except for the foreign keys, which stay as null.

Here are my entities:

Employee

import { Column, Entity, PrimaryColumn, PrimaryGeneratedColumn } from 'typeorm';

@Entity({ name: 'employees' })
export class Employee {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @PrimaryColumn()
  version: number;

  @Column({ name: 'employee_name', type: 'varchar' })
  employeeName: string;

  @Column({ name: 'employee_salary', type: 'numeric' })
  employeeSalary: string;
}

Employee Payroll

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';
import { Employee } from '../../employee/entities/employee.entity';

@Entity({ name: 'employee_payrolls' })
export class EmployeePayroll {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ name: 'employee_payroll_name', nullable: true })
  employeePayrollName: string;

  @ManyToOne(() => Employee)
  @JoinColumn([
    { name: 'employee_id', referencedColumnName: 'id' },
    { name: 'employee_version', referencedColumnName: 'version' },
  ])
  employee: Employee;
}

Employee Payroll Service

And here is the code that is doing the insert:

export class EmployeePayrollDTO {
  employeePayrollName: string;
  employeeId: string;
  employeeVersion: number;
}

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { EmployeePayrollDTO } from 'src/employee-payroll/dto/employee-payroll.dto';
import { EmployeePayroll } from 'src/employee-payroll/entities/employee-payroll.entity';
import { Repository } from 'typeorm';

@Injectable()
export class EmployeePayrollService {
  constructor(
    @InjectRepository(EmployeePayroll)
    private readonly employeeRepository: Repository<EmployeePayroll>,
  ) {}

  async addEmployeePayroll(employeePayroll: EmployeePayrollDTO) {
    return await this.employeeRepository
      .createQueryBuilder()
      .insert()
      .into(EmployeePayroll)
      .values(employeePayroll)
      .execute();
  }
}

The Problem

The problem is that while the insert is successful, the values of the columns: employee_id and employee_version are null:

id employee_payroll_name employee_id employee_version
53de51fd-6c9e-4b96-8906-edd1f6eea26c Payroll 1 null null
64b8a147-acee-4f43-9ea1-b64c2c036369 Payroll 2 null null

Can you help me?

Upvotes: 1

Views: 8574

Answers (1)

Felipe
Felipe

Reputation: 7381

As per @Michael Levi's comment, the problem was that I wasn't setting the employee object correctly.

Here is what worked for me:

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { EmployeePayrollDTO } from 'src/employee-payroll/dto/employee-payroll.dto';
import { EmployeePayroll } from 'src/employee-payroll/entities/employee-payroll.entity';
import { Repository } from 'typeorm';

@Injectable()
export class EmployeePayrollService {
  constructor(
    @InjectRepository(EmployeePayroll)
    private readonly employeeRepository: Repository<EmployeePayroll>,
  ) {}

  async addEmployeePayroll(employeePayroll: EmployeePayrollDTO) {
    return await this.employeeRepository
      .createQueryBuilder()
      .insert()
      .into(EmployeePayroll)
      .values({
        employeePayrollName: employeePayroll.employeePayrollName,
        employee: {
          id: employeePayroll.employeeId,
          version: employeePayroll.employeeVersion,
        },
      })
      .execute();
  }
}

Please note that this only inserts records in the EmployeePayroll table, and if I try to use a new id or version inside the employee object, I get a foreign key violation error.

Upvotes: 2

Related Questions