[Feature request] Batch queries #504
Comments
|
Had the same issue so I took inspiration from this article on drizzle Here is my implementation, it is not pretty but works let orderCase = 'CASE\n';
let stepIdCase = 'CASE\n';
const ids: string[] = [];
const orderParams: Record<string, any> = {};
const stepParams: Record<string, any> = {};
//input is an array of values sent from my frontend app
input.forEach((match, index) => {
const idParam = `id${index}`;
const orderParam = `new_order_value${index}`;
const stepParam = `new_step_value${index}`;
orderParams[idParam] = match.id;
orderParams[orderParam] = match.newOrder;
stepParams[idParam] = match.id;
stepParams[stepParam] = match.newStepId;
orderCase += ` WHEN id = $${idParam} THEN $${orderParam}\n`;
stepIdCase += ` WHEN id = $${idParam} THEN $${stepParam}\n`;
ids.push(match.id);
});
orderCase += ' ELSE "order"\nEND';
stepIdCase += ' ELSE "step_id"\nEND';
await db.talentMatch.whereIn('id', ids).update({
order: () => sql({ raw: orderCase, values: orderParams }),
stepId: () => sql({ raw: stepIdCase, values: stepParams }),
});This generates: UPDATE "talent_matches" SET "order" = CASE
WHEN id = $1 THEN $2
ELSE "order"
END, "step_id" = CASE
WHEN id = $3 THEN $4
ELSE "step_id"
END, "updated_at" = now() WHERE "talent_matches"."id" IN ($5) |
|
That's a good feature, I'll work on it later. You probably don't want 9 updates to succeed and 1 of them to fail. So the new Once I was doing a batch insert and encountered that partial success case, had to wrap it in a transaction and expected it to become slower. But, in contrary, I learned that transaction actually makes batch inserts/updates faster, because db can do its persisting job just once for all records rather than many times. So it's even faster in a transaction. I expect the way suggested by Drizzle to be much less efficient, it will be interesting to compare. |
Uh oh!
There was an error while loading. Please reload this page.
There are many times in my code where I need to update multiple records at once. Since we can't update multiple records when they have different values I need to use many queries and await them all at once using
Promise.all.It would really be nice if we could have a similar
db.$batchfunction that combines all queries into a single multi-statement query. This will result in a single round trip.Like
Promise.all, the return type should be a tuple with results in the same order as the passed queries.The text was updated successfully, but these errors were encountered: