Optimizing a query

I currently have the following code to update the pricing of the items in the database:
const prismapromises = Object.keys(items).map((item) =>
prisma.minion.update({
where: { id: item },
data: { cost: items[items] }
})
);

await prisma.$transaction(prismapromises);
const prismapromises = Object.keys(items).map((item) =>
prisma.minion.update({
where: { id: item },
data: { cost: items[items] }
})
);

await prisma.$transaction(prismapromises);
This is a cron job that runs every day and I have 700 items. So every day, 700 queries are being executed. This is not great, especially since I'm using Accelerate as this costs 21000 queries a month (700 * 30). I don't know how to optimize this, since updateMany doesn't work with multiple different id's. Any help would be great!
2 Replies
Nurul
Nurul4mo ago
Hey 👋 We have a related GitHub Feature request here: https://github.com/prisma/prisma/issues/8238 One another approach would be to use raw sql queries. Have you seen this related solution posted by a user? https://github.com/prisma/prisma/discussions/19765#discussioncomment-8244861
GitHub
Update multiple rows at one time · Issue #8238 · prisma/prisma
Problem There seems no way to update many rows by one call. Suggested solution Implement a method that to update multiple rows by one call. For example) You wanna make a query like below. UPDATE TO...
GitHub
Update multiple rows without looping through · prisma prisma · Disc...
This question has been asked before, see here: #12389 But I think it's worth bringing it up again. EDIT: In a hindsight, I should probably have just continued in the previous discussion. I as a...
Woman wearing Vision Pro
Thank you! I used the SQL approach and it works perfect! I hope Prisma will support this natively Coming back to this. I use Supabase as my database which uses PostgreSQL. I also have a local Supabase docker instance for development. Locally, my Prisma raw sql query runs fine, but in production (Vercel) I'm getting the following error message:
{\"name\":\"PrismaClientKnownRequestError\",\"code\":\"P2010\",\"clientVersion\":\"5.12.1\",\"meta\":{\"code\":\"42804\",\"message\":\"ERROR: column \\\"craftCost\\\" is of type double precision but expression is of type text\\nHINT: You will need to rewrite or cast the expression.\"}}"
{\"name\":\"PrismaClientKnownRequestError\",\"code\":\"P2010\",\"clientVersion\":\"5.12.1\",\"meta\":{\"code\":\"42804\",\"message\":\"ERROR: column \\\"craftCost\\\" is of type double precision but expression is of type text\\nHINT: You will need to rewrite or cast the expression.\"}}"
+server.ts:
const bulkUpdates: BulkUpdateEntries = Object.keys(items).map((item) => {
return {
id: item,
craftCost: items[item]
};
});

await prisma.$transaction([bulkUpdate("Item", bulkUpdates)]);
const bulkUpdates: BulkUpdateEntries = Object.keys(items).map((item) => {
return {
id: item,
craftCost: items[item]
};
});

await prisma.$transaction([bulkUpdate("Item", bulkUpdates)]);
bulkUpdates.ts:
import { type PrismaPromise } from "@prisma/client";

export type BulkUpdateEntry = {
id: number | string;
[key: string]: number | string | boolean | Date;
};
export type BulkUpdateEntries = BulkUpdateEntry[];

export function bulkUpdate(tableName: string, entries: BulkUpdateEntries): PrismaPromise<number> {
if (entries.length === 0) return prisma.$executeRawUnsafe(`SELECT 1;`);

const fields = Object.keys(entries[0]!).filter((key) => key !== "id");
const setSql = fields.map((field) => `"${field}" = data."${field}"`).join(", ");

const valuesSql = entries
.map((entry) => {
const values = fields.map((field) => {
const value = entry[field];
if (typeof value === "string") {
// Handle strings and escape single quotes
return `'${value.replace(/'/g, "''")}'`;
} else if (value instanceof Date) {
// Convert Date to ISO 8601 string format
return `'${value.toISOString()}'`;
}
// Numbers and booleans are used as-is
return value;
});

return `('${entry.id}', ${values.join(", ")})`;
})
.join(", ");

const sql = `
UPDATE "${tableName}"
SET ${setSql}
FROM (VALUES ${valuesSql}) AS data(id, ${fields.map((field) => `"${field}"`).join(", ")})
WHERE "${tableName}".id::text = data.id;
`;

return prisma.$executeRawUnsafe(sql);
}
import { type PrismaPromise } from "@prisma/client";

export type BulkUpdateEntry = {
id: number | string;
[key: string]: number | string | boolean | Date;
};
export type BulkUpdateEntries = BulkUpdateEntry[];

export function bulkUpdate(tableName: string, entries: BulkUpdateEntries): PrismaPromise<number> {
if (entries.length === 0) return prisma.$executeRawUnsafe(`SELECT 1;`);

const fields = Object.keys(entries[0]!).filter((key) => key !== "id");
const setSql = fields.map((field) => `"${field}" = data."${field}"`).join(", ");

const valuesSql = entries
.map((entry) => {
const values = fields.map((field) => {
const value = entry[field];
if (typeof value === "string") {
// Handle strings and escape single quotes
return `'${value.replace(/'/g, "''")}'`;
} else if (value instanceof Date) {
// Convert Date to ISO 8601 string format
return `'${value.toISOString()}'`;
}
// Numbers and booleans are used as-is
return value;
});

return `('${entry.id}', ${values.join(", ")})`;
})
.join(", ");

const sql = `
UPDATE "${tableName}"
SET ${setSql}
FROM (VALUES ${valuesSql}) AS data(id, ${fields.map((field) => `"${field}"`).join(", ")})
WHERE "${tableName}".id::text = data.id;
`;

return prisma.$executeRawUnsafe(sql);
}
What am I doing wrong? Update: I tried to run the generated SQL directly in Supabase's SQL Editor, no errors! What is going wrong that I get the PrismaClientKnownRequestError only in production?