Prisma•9mo ago

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•9mo 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 ProOP•9mo ago

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?

Gaming

Programming

Optimizing a query