CrazyCroatKid Posts - Answer Overflow

CrazyCroatKid

Explore posts from servers

•Created by CrazyCroatKid on 8/6/2024 in #help

Right way to make UpdatedAt column in Drizzle?

I'm trying to copy the way prisma was able to create a column that would update to the newest time when a row is changed. Is this a good implementation below or have people had issues with this that have tried to implement it?

export const users = pgTable("users", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey()
    .notNull(),
  kindeUserId: text("kinde_user_id").unique(),
  email: text("email").unique(),
  firstName: text("first_name"),
  lastName: text("last_name"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at") // <-- HERE
    .notNull()
    .default(sql`(CURRENT_TIMESTAMP)`)
    .$onUpdate(() => sql`(CURRENT_TIMESTAMP)`),
});

export const users = pgTable("users", {
  id: text("id")
    .$defaultFn(() => createId())
    .primaryKey()
    .notNull(),
  kindeUserId: text("kinde_user_id").unique(),
  email: text("email").unique(),
  firstName: text("first_name"),
  lastName: text("last_name"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at") // <-- HERE
    .notNull()
    .default(sql`(CURRENT_TIMESTAMP)`)
    .$onUpdate(() => sql`(CURRENT_TIMESTAMP)`),
});

A reference example the way Prisma had it

model User {
  id        Int      @id @default(autoincrement())
  name      String?
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt // <-- HERE
}

model User {
  id        Int      @id @default(autoincrement())
  name      String?
  email     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt // <-- HERE
}

3 replies

DTDrizzle Team

•Created by CrazyCroatKid on 5/6/2024 in #help

How to retry seriazible transactions Drizzle Orm

Hello everyone, I'm currently working with Drizzle ORM in a Node.js application and facing challenges handling high concurrency when multiple instances of my program are interacting with the same PostgreSQL database. Specifically, I have multiple processes running that update a scraped status in a URL table. When one process is updating a row, I want the other processes to skip this row and proceed to the next available one instead of trying to update the same row. However, I'm encountering frequent serialization errors like "could not serialize access due to concurrent update," which cause my programs to crash completely. Here's a simplified version of how my transaction logic is set up:

await db.transaction(
  async (tx) => {
    const urlResult = await tx.query.url.findFirst({
      where: { scraped: false },
    });

    if (!urlResult) {
      console.log("No unscraped URL found, retrying...");
      await new Promise(resolve => setTimeout(resolve, 5000));
      return;
    }

    try {
      // Marking as currently being scraped
      await tx.update(url)
        .set({ scraped: null })
        .where({ gmPartNo: urlResult.gmPartNo });

      // Process scraping here
      await processScraping(urlResult);

      // Mark as scraped. Inside of processscraping actually, but showing here
      // for clarity
      await tx.update(url)
        .set({ scraped: true })
        .where({ gmPartNo: urlResult.gmPartNo });
    } catch (error) {
      console.error("Error processing URL:", error);
      // Attempt to revert or handle error
      await tx.update(url)
        .set({ scraped: false })
        .where({ gmPartNo: urlResult.gmPartNo });
    }
  },
  {
    isolationLevel: "serializable",
    accessMode: "read write",
    deferrable: true,
  }
);

await db.transaction(
  async (tx) => {
    const urlResult = await tx.query.url.findFirst({
      where: { scraped: false },
    });

    if (!urlResult) {
      console.log("No unscraped URL found, retrying...");
      await new Promise(resolve => setTimeout(resolve, 5000));
      return;
    }

    try {
      // Marking as currently being scraped
      await tx.update(url)
        .set({ scraped: null })
        .where({ gmPartNo: urlResult.gmPartNo });

      // Process scraping here
      await processScraping(urlResult);

      // Mark as scraped. Inside of processscraping actually, but showing here
      // for clarity
      await tx.update(url)
        .set({ scraped: true })
        .where({ gmPartNo: urlResult.gmPartNo });
    } catch (error) {
      console.error("Error processing URL:", error);
      // Attempt to revert or handle error
      await tx.update(url)
        .set({ scraped: false })
        .where({ gmPartNo: urlResult.gmPartNo });
    }
  },
  {
    isolationLevel: "serializable",
    accessMode: "read write",
    deferrable: true,
  }
);

Is there a best way to handle retrying when one process is encountering "could not serialize access due to concurrent update"? Thanks!

2 replies

Gaming

Programming