Drizzle Team•12mo ago

avoiding conflicts in mySQL

Hi im trying to avoid conflicts in my app by checking the 'lastModified' column on the database against the client. But I also need to add successfully updated records to a status log table to record the history of changes. Because I'm using mySQL there's no way of returning specific rows effected. Is there a common approach for this?

export async function updateStatusByIds({
  status,
  records,
}: {
  status: Statuses;
  records: SelectRecordWithUserData[];
}) {

  // assemble sql where clause to match id and current lastModified date
  const sqlWhereChunks: SQL[] = [];

  records.forEach((r, i) => {
    sqlWhereChunks.push(
      sql`(${recordTable.id} = ${r.id} AND ${recordTable.lastModified} = ${r.lastModified})`
    );
    if (i < records.length - 1) {
      sqlWhereChunks.push(sql` OR `);
    }
  });

  const sqlWhere = sql.join(sqlWhereChunks);

  try {
    await db.transaction(async (tx) => {
      let update = await tx
        .update(recordTable)
        .set({ statusId: status })
        .where(sqlWhere);

      console.log("update:", update);

      await tx.insert(statusLog).values(
        records.map((r) => ({
          recordId: r.id,
          statusId: status,
          message: "bulk",
        }))
      );
    });
  } catch (error) {
    console.error(error);
    throw new Error(
      `Failed to update status ids: ${
        error instanceof Error ? error.message : "Unknown error"
      }`
    );
  }
}

export async function updateStatusByIds({
  status,
  records,
}: {
  status: Statuses;
  records: SelectRecordWithUserData[];
}) {

  // assemble sql where clause to match id and current lastModified date
  const sqlWhereChunks: SQL[] = [];

  records.forEach((r, i) => {
    sqlWhereChunks.push(
      sql`(${recordTable.id} = ${r.id} AND ${recordTable.lastModified} = ${r.lastModified})`
    );
    if (i < records.length - 1) {
      sqlWhereChunks.push(sql` OR `);
    }
  });

  const sqlWhere = sql.join(sqlWhereChunks);

  try {
    await db.transaction(async (tx) => {
      let update = await tx
        .update(recordTable)
        .set({ statusId: status })
        .where(sqlWhere);

      console.log("update:", update);

      await tx.insert(statusLog).values(
        records.map((r) => ({
          recordId: r.id,
          statusId: status,
          message: "bulk",
        }))
      );
    });
  } catch (error) {
    console.error(error);
    throw new Error(
      `Failed to update status ids: ${
        error instanceof Error ? error.message : "Unknown error"
      }`
    );
  }
}

12 Replies

Angelelz•12mo ago

I think this is the correct approach for this

JimOP•12mo ago

Thanks @Angelelz ! I had to write a lot more to also insert successful updates to the statusLog table. I added a new column used to write a unique transaction id to the record (idtx) which can be read to see which records were updated by the transaction

// add txid for each record in this transaction
records.forEach((r) => {
  r.idTx = createId();
});

// sql for setting idtx
const sqlSetChunks: SQL[] = [];
sqlSetChunks.push(sql`CASE`);
for (const record of records) {
  sqlSetChunks.push(sql` WHEN id = ${record.id} THEN ${record.idTx}`);
}
sqlSetChunks.push(sql`END`);
const sqlSet = sql.join(sqlSetChunks, sql.raw(" "));

// sql for match id and current lastModified date
const sqlWhereChunks: SQL[] = [];
records.forEach((r, i) => {
  sqlWhereChunks.push(
    sql`(${recordTable.id} = ${r.id} AND ${recordTable.lastModified} = ${r.lastModified})`
  );
  if (i < records.length - 1) {
    sqlWhereChunks.push(sql` OR `);
  }
});
const sqlWhere = sql.join(sqlWhereChunks, sql.raw(" "));

try {
  await db.transaction(async (tx) => {
    await tx
      .update(recordTable)
      .set({ statusId: status, idTx: sqlSet })
      .where(sqlWhere);

    // select records that were attempted to be updated
    const selectedRecordsPostUpdate = await tx
      .select()
      .from(recordTable)
      .where(
        inArray(
          recordTable.id,
          records.map((r) => r.id)
        )
      );

    // filter out records that were not updated by matching idTx with server idTx
    const updatedRecords = selectedRecordsPostUpdate.filter((r) =>
      records.find((r2) => r2.id === r.id && r2.idTx === r.idTx)
    );
    // if no records were updated, throw error
    if (updatedRecords.length === 0) {
      throw new Error("No records updated");
    }
    await tx.insert(statusLog).values(
      updatedRecords.map((r) => ({
        recordId: r.id,
        statusId: status,
        message: "bulk",
      }))
    );

    // if any records were not updated, throw error
    if (updatedRecords.length !== records.length) {
      throw new Error("Not all records updated");
    }
 });

// add txid for each record in this transaction
records.forEach((r) => {
  r.idTx = createId();
});

// sql for setting idtx
const sqlSetChunks: SQL[] = [];
sqlSetChunks.push(sql`CASE`);
for (const record of records) {
  sqlSetChunks.push(sql` WHEN id = ${record.id} THEN ${record.idTx}`);
}
sqlSetChunks.push(sql`END`);
const sqlSet = sql.join(sqlSetChunks, sql.raw(" "));

// sql for match id and current lastModified date
const sqlWhereChunks: SQL[] = [];
records.forEach((r, i) => {
  sqlWhereChunks.push(
    sql`(${recordTable.id} = ${r.id} AND ${recordTable.lastModified} = ${r.lastModified})`
  );
  if (i < records.length - 1) {
    sqlWhereChunks.push(sql` OR `);
  }
});
const sqlWhere = sql.join(sqlWhereChunks, sql.raw(" "));

try {
  await db.transaction(async (tx) => {
    await tx
      .update(recordTable)
      .set({ statusId: status, idTx: sqlSet })
      .where(sqlWhere);

    // select records that were attempted to be updated
    const selectedRecordsPostUpdate = await tx
      .select()
      .from(recordTable)
      .where(
        inArray(
          recordTable.id,
          records.map((r) => r.id)
        )
      );

    // filter out records that were not updated by matching idTx with server idTx
    const updatedRecords = selectedRecordsPostUpdate.filter((r) =>
      records.find((r2) => r2.id === r.id && r2.idTx === r.idTx)
    );
    // if no records were updated, throw error
    if (updatedRecords.length === 0) {
      throw new Error("No records updated");
    }
    await tx.insert(statusLog).values(
      updatedRecords.map((r) => ({
        recordId: r.id,
        statusId: status,
        message: "bulk",
      }))
    );

    // if any records were not updated, throw error
    if (updatedRecords.length !== records.length) {
      throw new Error("Not all records updated");
    }
 });

would be a lot simpler if mysql returned ids 😂

Angelelz•12mo ago

Doesn't mysql return the insertedId? Or you need it in the update statement? I kinda remember there was an updatedId as well Let me check Nope, you're right

JimOP•12mo ago

thanks! yeah im new to db stuff an on the drizzle docs there is no built in way to do update returns: https://orm.drizzle.team/docs/update#update-with-returning

SQL Update - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Angelelz•12mo ago

InsertId is only available on the insert Update doesn't have anything, only affectedRows which is just a number

JimOP•12mo ago

yeahhh i think i used that elsewhere in my codebase, to get the auto increment id of an insert

Angelelz•12mo ago

Drizzle doesn't have it because mysql doesn't have it Let me check something else real quick

JimOP•12mo ago

Yeah i suspected so! Also planetscale doesnt do procedures so thats another avenue blocked

Angelelz•12mo ago

Do you have an index on lastModified?

JimOP•12mo ago

no i dont think so I should add one right, since im using the column in a WHERE clause?

Angelelz•12mo ago

Not because of that, because you're already using the id in the where So not because of that, you don't need it There is a way of doing an update and getting the id back but it doesn't work for you, because of your extra lastModified in the where

console.log(
  await db
    .insert(users)
    .values({ name: "Angel", id: 1 })
    .onDuplicateKeyUpdate({ set: { name: "jjj" } }),
);

console.log(
  await db
    .insert(users)
    .values({ name: "Angel", id: 1 })
    .onDuplicateKeyUpdate({ set: { name: "jjj" } }),
);

This returns:

[
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 2,
    insertId: 1,
    info: "",
    serverStatus: 2,
    warningStatus: 0,
    changedRows: 0,
  }, undefined
]

[
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 2,
    insertId: 1,
    info: "",
    serverStatus: 2,
    warningStatus: 0,
    changedRows: 0,
  }, undefined
]

and updates the record to 'jjj' But if you try to do this, you'll update all the records with those ids regardless of the lastModified You have the only solution, I think

JimOP•12mo ago

Ahh I see, thats a handy return object yeah, i've been trying to find a solution all weekend haha good to have someone who knows what theyre doing look over it though so thanks! gpt only gets you so far 🤖 in fact gpt recommended making 'temporary tables' but i think thats overkill, plus i'm not 100% sure it solves the issue

Gaming

Programming

avoiding conflicts in mySQL