पाठ 12 / 35

Updating, Deleting and Upserting

यह पाठ अभी हिंदी में अनुवादित नहीं है, इसलिए अंग्रेज़ी में दिखाया जा रहा है।

Update and delete single rows and sets of rows, read the affected count, and use upsert for insert-or-update logic.

लेखक: · 15 साल से वेब डेवलपर

One row or many rows

Updates and deletes come in two shapes. Targeted operations identify one row: Prisma update({ where: { id }, data }) and delete({ where: { id } }) return the changed record and throw if the row does not exist. Bulk operations affect every row matching a condition: Prisma updateMany and deleteMany return { count }; TypeORM update(criteria, partial) and delete(criteria) return a result with an affected property; Sequelize Model.update(values, { where }) resolves to an array whose first element is the number of affected rows, and Model.destroy({ where }) resolves to a number. Bulk methods run one SQL statement, so they are fast, but they usually skip per-instance logic such as TypeORM entity listeners or Sequelize instance hooks. An upsert inserts a row if a unique key is new and updates it otherwise, which in SQL (INSERT ... ON CONFLICT) avoids a read-then-write race. Prisma has upsert({ where, create, update }), but it only uses a native database upsert when the query is simple enough (for example one unique field in where and no nested writes); otherwise it reads first, so two concurrent calls can still hit a unique-constraint error. TypeORM and Sequelize both offer an upsert method too.

Update, bulk update, upsert

increment in Prisma generates SET views = views + 1 in SQL, which is safe under concurrency, unlike reading the value and writing it back.

// targeted update: throws if id 5 does not exist
await prisma.post.update({ where: { id: 5 }, data: { views: { increment: 1 } } });

// bulk: archive old drafts, returns { count }
const { count } = await prisma.post.updateMany({
  where: { published: false, createdAt: { lt: cutoff } },
  data: { archived: true },
});

// insert or update by a unique field
await prisma.user.upsert({
  where: { email: 'asha@example.com' },
  update: { name: 'Asha K' },
  create: { email: 'asha@example.com', name: 'Asha K' },
});

The same ideas in Sequelize and TypeORM

Always pass where to bulk operations. Sequelize refuses destroy() without one unless you pass truncate: true or where: {}.

// Sequelize
const [affected] = await Post.update({ archived: true }, { where: { published: false } });
const removed = await Post.destroy({ where: { archived: true } });

// TypeORM
const res = await postRepo.update({ published: false }, { archived: true });
console.log(res.affected);

Read-modify-write is a race

Fetching a counter, adding one in JavaScript and saving it back loses updates when two requests overlap. Use atomic SQL instead: Prisma { increment: 1 }, TypeORM repo.increment(criteria, 'views', 1), Sequelize instance.increment('views') or Model.increment.

त्वरित जाँच: Two requests read views = 10 at the same time, both add 1 in JavaScript and save. What is the stored value afterwards?

  • 11, because one update overwrites the other
  • 12, because databases serialise all writes
  • An error, because the ORM detects the conflict
  • 10, because neither save succeeds
Answer

11, because one update overwrites the other — This is a lost update. Both write 11. An atomic increment (SET views = views + 1) or optimistic locking prevents it.