# Insert and Update

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

## `insert()`

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

```typescript
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()`:

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

## Related Pages

- [Upsert](/upsert)
- [Raw SQL](/raw-sql)