Remove rows deliberately

Check the rows selected by a filter before deleting them. The examples show a direct delete and a delete buffered inside a transaction.

Before you run it: SQL DELETE without WHERE empties the table. Use a selective filter and verify the rows before sending the write.

Load the introductory orders sample dataset first. It contains o_001, o_002 and o_003 with customer_id, amount_cents and status. Use a disposable copy because this recipe changes those rows.

Preview and commit a single-order deletionUse the same session ID for the transaction and confirm the target before deleting.

Preview order o_003Run a matching SELECT in the test table.
Begin the transactionKeep the session ID for all following statements.
Delete the intended rowBuffer the primary-key deletion in that transaction.
Commit and checkConfirm the row is absent after the commit.

Follow the example

Run SELECT with the intended filter first.
Choose the direct or transaction example.
Keep the same session ID through COMMIT.
Check the write count and read back the keys.

Supported operations

DELETE over /sql executes. On its own it removes the matched rows in a single durable frame and answers with rows_affected. A WHERE <pk> = <literal> takes a typed single-row fast path; any other supported predicate - and a composite primary key - lowers to a scan-based delete. Inside BEGIN; ... COMMIT; the same statement buffers instead, reporting rows_buffered until commit.

Delete inside a transaction

Inside an open transaction, a primary-key DELETE buffers a row-removal op and the COMMIT applies it durably in one frame. Carry the same session id across all three calls so they share one transaction buffer.

# Supported SQL route: wrap the DELETE in an explicit transaction.
# The same session id must accompany BEGIN, the DELETE, and COMMIT so
# the buffered row-removal is applied atomically on COMMIT.
SID="sess-$(date +%s)"

# 1. open the transaction
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "BEGIN" }'

# 2. buffer the delete (matched by primary key)
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "DELETE FROM shop.orders WHERE id = '\''o_003'\''" }'

# 3. commit - the buffered row-removal lands durably here
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "COMMIT" }'

Check the transaction result

// the DELETE step, inside the open transaction:
{
  "kind":          "delete",
  "schema":        "shop.orders",
  "pk":            "o_003",
  "rows_buffered": 1
}

// the COMMIT step - the buffered op is now durable:
{
  "kind":          "tx",
  "op":            "commit",
  "ops_committed": 1
}

rows_buffered is 1 when the row existed (and 0 when it didn't). The removal is not visible to other readers until COMMIT returns ops_committed: 1.

Delete through the row API

When you already hold the primary key and don't need SQL, delete the row through the typed row endpoints. The row delete path carries the same idempotency-key plumbing as the rest of the typed /rows surface.

Delete without a transaction

With no BEGIN around it, the same statement autocommits: the rows are removed in one frame before the response returns, and rows_affected counts them. Foreign keys are enforced on this path too. Note that /sql is the one surface that accepts a bare DELETE FROM t with no WHERE and empties the table - the typed row endpoints refuse that shape.

# This statement commits without an explicit transaction.
# Check rows_affected, then read back the key to confirm the result.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "DELETE FROM shop.orders WHERE id = '\''o_003'\''"
  }'

Check the direct-delete result

{
  "kind":   "delete",
  "schema": "shop.orders",
  "rows_affected": 1
}

Add RETURNING order_id, total_cents (or RETURNING *) and the response also carries a rows array holding the deleted rows' values, projected to that column list. A no-match delete answers rows_affected: 0 with an empty rows array rather than an error.

Expected result

The transaction example deletes o_003. If you then run the direct-delete example without restoring that row, expect rows_affected: 0, because it is already gone. Restore the sample only in your test database if you want to compare both deletion paths from the same starting state.

Continue

Continue to update one row by primary key.

Remove rows deliberately