Writing

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 backCopy Anchor Link

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

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

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

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() fills one in.

On a table with softDelete(), 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 writesCopy Anchor Link

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:

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 and PowerSync count the rows inside the statement or transaction and roll the write back. The requireMaxAffected rule in the rules plugin and the require-max-affected lint rule make the cap mandatory.

Counting writesCopy Anchor Link

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 updatesCopy Anchor Link

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:

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

UpsertsCopy Anchor Link

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

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 insertsCopy Anchor Link

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, 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 concurrencyCopy Anchor Link

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:

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.

ConflictsCopy Anchor Link

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

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" });
}