Home GitHub

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 with no function allowlist: created_at: "NOW()" stores the string 'NOW()'
Empty object insert({}) produces INSERT INTO `users` () VALUES () ``, which MySQL rejects

To store the current time, give the column DEFAULT CURRENT_TIMESTAMP or pass new Date() from your code.

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
No where() Produces an UPDATE without WHERE, which updates the whole table

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
Passing 0 n || 1 turns 0 into 1
Decrement Pass a negative number, such as increase("stock", -1)
Column name Not backtick-quoted; written as-is

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()

The match is on the whole string: "NOW() + INTERVAL 1 DAY" is not on the list and is bound as a string.

中文