Insert and Update
Last updated
This page covers
insert(), update(), and increase(), and which methods honor the MySQL function allowlist.insert()
- const id = await MySQLPool
- .table("users")
- .insert({ name: "John Doe", email: "john@example.com" });
- // INSERT INTO `users` (`name`, `email`) VALUES (?, ?)
| Item | Behavior |
|---|---|
| Pool | Write pool |
| Returns | insertId; null when it is 0 (for example, no AUTO_INCREMENT column) |
| Values | All bound through placeholders |
update()
- const result = await MySQLPool
- .table("users")
- .where("id", 1)
- .update({ name: "Jane Doe", updated_at: "NOW()" });
- // UPDATE `users` SET `name` = ?, `updated_at` = NOW() WHERE `id` = ?
- console.log(result.affectedRows);
| Item | Behavior |
|---|---|
| Pool | Write pool |
| Returns | mysql2 ResultSetHeader (affectedRows, changedRows, and so on) |
| Allowlisted functions | A value equal (case-insensitive) to an allowlist entry is written into SQL as-is |
increase()
increase(column, n) adds column = column + n to the SET clause of update():- await MySQLPool
- .table("users")
- .where("id", 1)
- .increase("login_count")
- .increase("score", 5)
- .update({ last_login: "NOW()" });
- // UPDATE `users` SET login_count = login_count + 1, score = score + 5, `last_login` = NOW() WHERE `id` = ?
| Rule | Detail |
|---|---|
| Default step | 1; 0 is also treated as 1 because the step is number || 1 |
| Column name | Written as-is without backticks |
| Decrement | Pass a negative number, such as increase("stock", -1) |
MySQL Function Allowlist
In
update() and upsert(), a value equal to any of the following is written into SQL as-is (src/MySQLPool.ts:31-36):NOW(), CURRENT_TIMESTAMP, UUID(), RAND(), CURDATE(), CURTIME(), UNIX_TIMESTAMP(), UTC_TIMESTAMP(), SYSDATE(), LOCALTIME(), LOCALTIMESTAMP(), PI(), DATABASE(), USER(), VERSION()