Db race condition

Is there any tool with drizzle that can help with handling race conditions?

17 Replies

micaww•16mo ago

that's incredibly vague. what problem are you trying to solve? likely outside the scope of drizzle though. depends on the db you're using and application requirements

NickolakiOP•16mo ago

I have an endpoint that create a db journey for a customer. A customer should only ever have 1 journey.

export async function POST(req: Request) {
  const body = (await req.json()) as CreateJourneyRequest;

  const existingJourney = await db.transaction(async (tx) => {
    const result = await tx.query.journeys.findFirst({
      with: {
        products: {
          with: {
            product: true,
          },
        },
      },
      where: (journeys, { eq }) =>
        eq(journeys.customer_emailAddress, body.emailAddress),
    });

    return result;
  });

  if (existingJourney)
    return NextResponse.json(existingJourney);
  }

  const journeyId = uuidv4();

  const newJourney = {
    id: journeyId,
    customer_firstName: body.firstName,
    customer_lastName: body.lastName,
    customer_emailAddress: body.emailAddress,
    completed: false,
    currentstep: "review",
  };

  await db.transaction(async (tx) => {
    console.log(Date.now(), "Inserting New Journey");
    await tx.insert(journeys).values(newJourney);
    console.log(Date.now(), "Finished Inserting New Journey");

    const productsToLink = await tx.query.products.findMany({
      where: (products) => inArray(products.name, body.initialProducts),
    });

    const journeysProductsToInsert = productsToLink.map((product) => ({
      journeyId,
      productId: product.id,
    }));

    await tx.insert(journeysToProducts).values(journeysProductsToInsert);
  });
}

export async function POST(req: Request) {
  const body = (await req.json()) as CreateJourneyRequest;

  const existingJourney = await db.transaction(async (tx) => {
    const result = await tx.query.journeys.findFirst({
      with: {
        products: {
          with: {
            product: true,
          },
        },
      },
      where: (journeys, { eq }) =>
        eq(journeys.customer_emailAddress, body.emailAddress),
    });

    return result;
  });

  if (existingJourney)
    return NextResponse.json(existingJourney);
  }

  const journeyId = uuidv4();

  const newJourney = {
    id: journeyId,
    customer_firstName: body.firstName,
    customer_lastName: body.lastName,
    customer_emailAddress: body.emailAddress,
    completed: false,
    currentstep: "review",
  };

  await db.transaction(async (tx) => {
    console.log(Date.now(), "Inserting New Journey");
    await tx.insert(journeys).values(newJourney);
    console.log(Date.now(), "Finished Inserting New Journey");

    const productsToLink = await tx.query.products.findMany({
      where: (products) => inArray(products.name, body.initialProducts),
    });

    const journeysProductsToInsert = productsToLink.map((product) => ({
      journeyId,
      productId: product.id,
    }));

    await tx.insert(journeysToProducts).values(journeysProductsToInsert);
  });
}

What I'm seeing is that If I call this endpoint twice in very fast succession. 1st request will not get an existingJourney and proceeed to insert one. 2nd request will query existingJourney but will still be undefined as the inserted one from request 1 is not complete or committed (not sure how it works). I don't know if I'm remotely using db transactions for what they are supposed to be for. Has that given you context? 🙂 @Raphaël M (@rphlmr) ⚡ Boss, can you help me with this? 😘

rphlmr ⚡•16mo ago

I would first try to wrap all in the same transaction? A transaction is to tell: do all of this or rollback if one of the request fail Is this request fired from UI? Do you have disabled submit button? One thing I don't understand: do you have a unique constraint saying that one user could only have 1 journey? because even on race conditions there should be only one winner. The slowest would return an error.

NickolakiOP•16mo ago

I do not its simply a text property in the table, so maybe that's what I should do

rphlmr ⚡•16mo ago

For cases where you can insert multiple rows (1:n like payment), you have to go on the idempotent way (client send an idempotent key with every request, preventing double fire to write twice) Can you share it? or just a quick example of your shcemas?

NickolakiOP•16mo ago

export const journeys = pgTable("journey", {
  id: uuid("id").defaultRandom().primaryKey(),
  customer_firstName: text("customer_firstName").notNull(),
  customer_lastName: text("customer_lastName").notNull(),
  customer_emailAddress: text("customer_emailAddress").notNull(),
  currentstep: text("currentstep").notNull(),
});

export const journeys = pgTable("journey", {
  id: uuid("id").defaultRandom().primaryKey(),
  customer_firstName: text("customer_firstName").notNull(),
  customer_lastName: text("customer_lastName").notNull(),
  customer_emailAddress: text("customer_emailAddress").notNull(),
  currentstep: text("currentstep").notNull(),
});

Nothing stopping a user with email "[email protected]" to have 2 journeys atm (I don't want that)

rphlmr ⚡•16mo ago

So you are okay if a user has 2 journeys?

NickolakiOP•16mo ago

Na I don't want them to only 1 So would a unique col sort this?

rphlmr ⚡•16mo ago

you can add customer_id has a reference to this table too or maybe these data are not related to a user table?

NickolakiOP•16mo ago

I currently don't have a user table 😛

rphlmr ⚡•16mo ago

ok I see, it's just a 'save a given customer payload attached to a journey'

NickolakiOP•16mo ago

Yes exactly The intresting thing about changing the col to unique. I wonder if i will still run into the issues I guess you can't, ill do some testing

rphlmr ⚡•16mo ago

With unique it should add the missing constraint I would try:

export async function POST(req: Request) {
  const body = (await req.json()) as CreateJourneyRequest;

  const result = await db.transaction(async (tx) => {
    const existingJourney = await tx.query.journeys.findFirst({
      with: {
        products: {
          with: {
            product: true,
          },
        },
      },
      where: (journeys, { eq }) =>
        eq(journeys.customer_emailAddress, body.emailAddress),
    });

    if (existingJourney) {
      await tx.rollback()
      return NextResponse.json(existingJourney);
    }

    const journeyId = uuidv4();

    const newJourney = {
      id: journeyId,
      customer_firstName: body.firstName,
      customer_lastName: body.lastName,
      customer_emailAddress: body.emailAddress,
      completed: false,
      currentstep: "review",
    };

    console.log(Date.now(), "Inserting New Journey");
    await tx.insert(journeys).values(newJourney);
    console.log(Date.now(), "Finished Inserting New Journey");

    const productsToLink = await tx.query.products.findMany({
      where: (products) => inArray(products.name, body.initialProducts),
    });

    const journeysProductsToInsert = productsToLink.map((product) => ({
      journeyId,
      productId: product.id,
    }));

    await tx.insert(journeysToProducts).values(journeysProductsToInsert);

    return NextResponse.json({ success: true, journeyId });
  });

  return result;
}

export async function POST(req: Request) {
  const body = (await req.json()) as CreateJourneyRequest;

  const result = await db.transaction(async (tx) => {
    const existingJourney = await tx.query.journeys.findFirst({
      with: {
        products: {
          with: {
            product: true,
          },
        },
      },
      where: (journeys, { eq }) =>
        eq(journeys.customer_emailAddress, body.emailAddress),
    });

    if (existingJourney) {
      await tx.rollback()
      return NextResponse.json(existingJourney);
    }

    const journeyId = uuidv4();

    const newJourney = {
      id: journeyId,
      customer_firstName: body.firstName,
      customer_lastName: body.lastName,
      customer_emailAddress: body.emailAddress,
      completed: false,
      currentstep: "review",
    };

    console.log(Date.now(), "Inserting New Journey");
    await tx.insert(journeys).values(newJourney);
    console.log(Date.now(), "Finished Inserting New Journey");

    const productsToLink = await tx.query.products.findMany({
      where: (products) => inArray(products.name, body.initialProducts),
    });

    const journeysProductsToInsert = productsToLink.map((product) => ({
      journeyId,
      productId: product.id,
    }));

    await tx.insert(journeysToProducts).values(journeysProductsToInsert);

    return NextResponse.json({ success: true, journeyId });
  });

  return result;
}

await tx.rollback() added in the if + wrapping all in the same transaction

rphlmr ⚡•16mo ago

Then, transaction behavior config exists too: https://orm.drizzle.team/docs/transactions (end of page). Not sure if it is required and what to change from the default

Transactions – DrizzleORM

Drizzle ORM | %s

NickolakiOP•16mo ago

Yeah the unique thing seems to have solved it. Will try without transaction too to see if its even needed

rphlmr ⚡•16mo ago

I would suggest to keep transaction when you insert related things, it's better to wrap that in transaction to prevent inconsistent insert

NickolakiOP•16mo ago

True, thank you bro

Gaming

Programming

Db race condition