Lesson 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.

By · 15 years in web development

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.

Quick check: 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.