Insert and Update

Last updated


This page covers insert(), update(), and increase(), and which methods honor the MySQL function allowlist.

insert()


  1. const id = await MySQLPool
  2. .table("users")
  3. .insert({ name: "John Doe", email: "john@example.com" });
  4. // INSERT INTO `users` (`name`, `email`) VALUES (?, ?)

ItemBehavior
PoolWrite pool
ReturnsinsertId; null when it is 0 (for example, no AUTO_INCREMENT column)
ValuesAll bound through placeholders

update()


  1. const result = await MySQLPool
  2. .table("users")
  3. .where("id", 1)
  4. .update({ name: "Jane Doe", updated_at: "NOW()" });
  5. // UPDATE `users` SET `name` = ?, `updated_at` = NOW() WHERE `id` = ?
  6. console.log(result.affectedRows);

ItemBehavior
PoolWrite pool
Returnsmysql2 ResultSetHeader (affectedRows, changedRows, and so on)
Allowlisted functionsA 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():
  1. await MySQLPool
  2. .table("users")
  3. .where("id", 1)
  4. .increase("login_count")
  5. .increase("score", 5)
  6. .update({ last_login: "NOW()" });
  7. // UPDATE `users` SET login_count = login_count + 1, score = score + 5, `last_login` = NOW() WHERE `id` = ?

RuleDetail
Default step1; 0 is also treated as 1 because the step is number || 1
Column nameWritten as-is without backticks
DecrementPass 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()

中文