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).| 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()"
- );