Drizzle Team•5mo ago

Is there a way to set foreign key on a bulk insert?

Apologies if this is a stupid question but I need to know if I'm missing something obvious. I have two tables where the second one has a foreign key relationship to the other, standard master/detail setup. After I insert the master records, call the entity Pool I return the new id and then I loop through an array of detail records and set that foreign key pool_id to the newly acquired id and then do a batch insert of those detail records PoolTeam like so

async createPoolTeamPlayers(input: CreatePoolTeamPlayer[]): Promise<void> {
    await db.insert(pool_team_player).values(input)
}

async createPoolTeamPlayers(input: CreatePoolTeamPlayer[]): Promise<void> {
    await db.insert(pool_team_player).values(input)
}

Is there any way I can specify the master record id in this insert statement without having to previous loop through the array setting the pool_id property? It it possible to do this master/detail insert in a single call with Drizzle? It works the way I have it but just seems wrong to do it this way.

1 Reply

piechart•5mo ago

Each drizzle call represents one SQL query and you need one insert per table, so I don't think you can do this in a single call. The way you're doing it seems optimal to me, and looping through the array to set the master id can be done quite concisely in js

Gaming

Programming

Is there a way to set foreign key on a bulk insert?