Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to do cascading inserts with tables having auto increment id columns in TypeORM

I am trying to implement cascading inserts using typeorm. The child table entries include a foreign key reference to the ID field on the parent table.

It seems typeorm is not capturing the auto-increment id from the parent row and supplying it to the child inserts.

Here is the parent Entity:

import ...
@Entity("parent")
export class Parent {
    @PrimaryGeneratedColumn()
    id: number;

    @OneToOne(type => Account, accountId => accountId.provider)
    @JoinColumn({name: "account_id"})
    accountId: Account;

    @Column("varchar", {
        name: "name",
        nullable: false,
        length: 255,
    })
    name: string;

    @OneToMany(type => Child, Children => Children.parentId, {
        cascadeInsert: true,
        cascadeUpdate: true
    })
    Children: Child[];

}

and the child entity:

import ...

@Entity("child")
export class Child{
    @PrimaryGeneratedColumn()
    id: number;

    @ManyToOne(type => Parent, parentId => parentId.children)
    @JoinColumn({name: "parent_id"})
    parentId: Parent;

    @Column("varchar", {
        name: "number",
        nullable: true,
        length: 45,
    })
    number: string;

}

the console log shows that the sql being generated does not include the foreign key column, producing the obvious error ER_NO_DEFAULT_FOR_FIELD: Field 'parent_id' doesn't have a default value

info: executing query:  START TRANSACTION
info: executing query:  INSERT INTO parent(name, account_id) VALUES (?,?) -- PARAMETERS: ["Name","id"]
info: executing query:  INSERT INTO child(number) VALUES (?) -- PARAMETERS: ["12345678"]
info: query failed:  INSERT INTO child(number) VALUES (?) -- PARAMETERS: ["12345678"]
info: error:  { Error: ER_NO_DEFAULT_FOR_FIELD: Field 'parent_id' doesn't have a default value

Is there some way that typeorm can be instructed to capture the LAST_INSERT_ID() or otherwise populate the foregin key id field on the child row?

like image 548
Milton Avatar asked Jan 25 '18 23:01

Milton


Video Answer


1 Answers

If the id is not auto generated, but assigned before saving the entity, it works.

Use a uuid as primary key.

import ...
@Entity("parent")
export class Parent {

    @PrimaryColumn()
    id: string;

    @OneToOne(type => Account, accountId => accountId.provider)
    @JoinColumn({name: "account_id"})
    accountId: Account;

    @Column("varchar", {
        name: "name",
        nullable: false,
        length: 255,
    })
    name: string;

    @OneToMany(type => Child, Children => Children.parentId, {
        cascadeInsert: true,
        cascadeUpdate: true
   })
   Children: Child[];
}

and when you create the entity.

const parent: Parent = new Parent();
parent.id = "896b677f-fb14-11e0-b14d-d11ca798dbac"; // your uuid goes here

Unfortunately you can not use @PrimaryGeneratedColumn("uuid") either.

There are a lot of libraries to generate a uuid in JS.

Example with uuid-js - https://github.com/pnegri/uuid-js

const parent: Parent = new Parent();
parent.id = UUID.create(4).toString();

I think this is more like a workaround, rather than a solution to the problem. But I could not find a way to tell TypeORM to consider generated ids when cascading children.

like image 90
BilledTrain380 Avatar answered Oct 15 '22 10:10

BilledTrain380