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). The table needs a matching unique index, otherwise every call inserts.
| Item | Behavior |
|---|---|
| Pool | Write pool |
| Returns | insertId; null when it is 0 |
Values in data |
All bound through placeholders with no function allowlist |
Three Forms of updateData
| Form | Example | Generated UPDATE clause |
|---|---|---|
| Omitted | upsert({ email: "e", name: "n" }) |
`email` = VALUES(`email`), `name` = VALUES(`name`) |
| Object | upsert({ email: "e" }, { updated_at: "NOW()", hits: 3 }) |
`updated_at` = NOW(), `hits` = ? |
| String | upsert({ email: "e" }, "hits = hits + 1") |
hits = hits + 1 |
// email is unique: insert when missing, otherwise bump the count and timestamp
await MySQLPool
.table("subscribers")
.upsert(
{ email: "john@example.com", hits: 1 },
"hits = hits + 1, updated_at = NOW()"
);
| Form | Caveat |
|---|---|
| Omitted | Reads the new value through VALUES(), which MySQL deprecated in 8.0.20 (it still runs and emits a warning) |
| Object | Allowlisted values are written as-is and the rest are bound; column names without . are backtick-quoted |
| String | Appended after ON DUPLICATE KEY UPDATE with no escaping, so pass only fixed SQL from your code |