# Writing

> create, update, upsert and delete, with typed conflicts and optimistic concurrency.

Source: https://bettersupabase.com/docs/repository/writing

| Method                               | Returns                                         |
| ------------------------------------ | ----------------------------------------------- |
| `create(data, args?)`                | The row                                         |
| `createMany(data[], args?)`          | The rows                                        |
| `update(key, data, args?)`           | The row, or `not_found`                         |
| `updateMany({ where, data })`        | `{ count }`, or the rows with `returning: true` |
| `upsert(data, { onConflict })`       | The row                                         |
| `upsertMany(data[], { onConflict })` | The rows                                        |
| `delete(key)`                        | Nothing, or `not_found`                         |
| `deleteMany({ where })`              | `{ count }`, or the rows with `returning: true` |

Inserts are typed by the generated `Insert` shape: required columns are
required, columns with defaults are optional, and generated identity
columns are rejected.

## Choosing what comes back [#choosing-what-comes-back]

Writes return the full row by default. Narrow it with `select`, or skip it
with `returning: false`:

```ts
await db.customers.create(data, { select: ["id"] });
await db.auditLog.create(entry, { returning: false });
```

> **RLS and returning**
>
> Returning rows needs a `SELECT` policy that matches the new row. If users may
> insert rows they cannot read, use `returning: false`. Otherwise the insert
> fails with `forbidden` after it passed your `INSERT` policy.

`updateMany` and `deleteMany` return `{ count }`. Pass `returning: true` to get
the written rows instead, narrowed with `select` and `include` as in reads:

```ts
const sent = await db.invoices
  .updateMany({
    where: { status: "draft", dueAt: { lte: today } },
    data: { status: "sent" },
    returning: true,
    select: ["id", "number"],
  })
  .orThrow();
```

`updateMany` and `deleteMany` need a `where` that filters rows: an empty or
missing `where`, or one whose conditions are all `undefined`, returns
`invalid_request` instead of writing every row. Pass `allowAll: true` to
`updateMany` when you mean the whole table; for `deleteMany`, filter on a
column that every row matches. An `update` or `updateMany` whose `data` sets
no columns also returns `invalid_request`, unless a plugin such as
[`timestamps()`](/docs/plugins/timestamps) fills one in.

On a table with [`softDelete()`](/docs/plugins/soft-delete), `deleteMany`
with `returning: true` keeps `RETURNING` on the update it becomes, so it needs
a `SELECT` policy that still shows the soft-deleted row.

## Limiting bulk writes [#limiting-bulk-writes]

`maxAffected` caps how many rows `updateMany` and `deleteMany` may change.
When the `where` matches more, the write fails with `max_affected` (400) and
changes nothing:

```ts
const result = await db.sessions.deleteMany({
  where: { userId },
  maxAffected: 50,
});
if (result.error?.kind === "max_affected") {
  // the filter matched more rows than expected
}
```

Over PostgREST this sends `Prefer: handling=strict, max-affected=50`, which
needs PostgREST 13 or later. On an older server, set
`defineSupabase(schema, { postgrestVersion: "12.2" })` and a call with
`maxAffected` fails with `invalid_request` before any request. The
[Postgres executor](/docs/auth/postgres) and
[PowerSync](/docs/repository/powersync) count the rows inside the statement or
transaction and roll the write back. The `requireMaxAffected` rule in the
[rules plugin](/docs/plugins/rules) and the `require-max-affected`
[lint rule](/docs/plugins/lint) make the cap mandatory.

## Counting writes [#counting-writes]

`updateMany` and `deleteMany` count the written rows exactly. Pass
`count: "planned"` or `count: "estimated"` to use the Postgres planner's
estimate over PostgREST instead; `createMany` and `upsertMany` with
`returning: false` take the same option. The SQL executors always return the
statement's own row count.

## Conditional updates [#conditional-updates]

`update` matches the row by its key. Add `where` for anything else the row
must meet, such as its tenant or a status it may only leave once. A row with
this key that doesn't match comes back as `not_found`, in one request:

```ts
const result = await db.invitations.update(
  id,
  { acceptedAt: now },
  { where: { organizationId, status: { in: ["pending", "resent"] } } },
);
```

## Upserts [#upserts]

`onConflict` takes a unique constraint name from the generated `UniqueKeys`,
`'primaryKey'`, or a column list:

```ts
await db.customers.upsert(data, {
  onConflict: "customers_organization_id_kvk_key",
});
await db.tags.upsertMany(rows, {
  onConflict: ["organizationId", "name"],
  ignoreDuplicates: true,
});
```

`upsertMany` sends the rows sorted by the conflict columns, with nulls last.
Two requests that upsert overlapping rows then lock them in the same order and
wait for each other instead of deadlocking. The returned rows follow that
order, not the order you passed.

## Bulk inserts [#bulk-inserts]

`createMany` and `upsertMany` send one request over PostgREST however many
rows you pass. Rows can leave out different columns: a missing column gets
its default. Pass `defaultToNull: true` to write `null` for missing columns
instead, as supabase-js does by default. On the [Postgres executor](/docs/auth/postgres), one statement
takes at most 65,535 bind parameters (one per column value), so a larger
insert runs as several statements in one transaction: either every row is
written or none is. Clients without `transaction` (a custom `SqlClient`) run
the statements one after the other, and an error leaves the earlier ones
written.

## Optimistic concurrency [#optimistic-concurrency]

Pass the values you read in `expect`. If the row changed in the meantime, the
update fails with `stale` (412) instead of overwriting it. `expect` takes the
same operators as `where`; plain values mean equality:

```ts
const result = await db.customers.update(
  id,
  { name },
  { expect: { updatedAt } },
);
if (result.error?.kind === "stale") {
  // reload and ask the user
}
```

Telling `stale` from `not_found` takes a second request that checks whether
the row exists. Use `where` for conditions that are not about a concurrent
change, such as a tenant guard, so a miss is `not_found` without the probe.

## Conflicts [#conflicts]

Unique violations come back as `conflict` with the constraint and columns, so
forms can point at the right field:

```ts
const result = await db.tags.create({ organizationId, name });
if (
  result.error?.kind === "conflict" &&
  result.error.columns?.includes("name")
) {
  form.setError("name", { message: "Tag already exists" });
}
```