Upsert

Last updated


This page explains how upsert() builds INSERT ... ON DUPLICATE KEY UPDATE and the three forms of its second argument.

Basic Use


upsert(data, updateData?) inserts data; when a PRIMARY KEY or UNIQUE index collides, it updates instead (src/MySQLPool.ts:316-360).
ItemBehavior
PoolWrite pool
ReturnsinsertId; null when it is 0
Values in dataAll bound through placeholders with no function allowlist

Three Forms of updateData


FormExampleGenerated UPDATE clause
Omittedupsert({ email: "e", name: "n" })`email` = VALUES(`email`), `name` = VALUES(`name`)
Objectupsert({ email: "e" }, { updated_at: "NOW()", hits: 3 })`updated_at` = NOW(), `hits` = ?
Stringupsert({ email: "e" }, "hits = hits + 1")hits = hits + 1

  1. // email is unique: insert when missing, otherwise bump the count and timestamp
  2. await MySQLPool
  3. .table("subscribers")
  4. .upsert(
  5. { email: "john@example.com", hits: 1 },
  6. "hits = hits + 1, updated_at = NOW()"
  7. );


中文