Write and read records

Choose a request by what you want to change, then read back the saved record.

Change a record without losing trackAn upsert can replace the whole row; include the fields you intend to preserve.

Identify the recordUse its table and primary key.
Choose the write operationDistinguish insert, replacement, conditional write and delete.
Apply the required conditionUse the current revision when the endpoint supports conditional writes.
Read back the resultConfirm the saved fields or the intended deletion.

Prepare your table

Register the table and its columns first. A direct point read needs a single-column primary key; use SQL to read a composite key.

Example orders table

namespace   = "shop"
table       = "orders"
primary_key = ["id"]        # single column - required for the /:pk shortcut

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "customer"
ty   = "str"

[[columns]]
name = "amount_cents"
ty   = "i64"                # money in minor units - never f64

[[columns]]
name = "status"
ty   = "str"

[[columns]]
name = "notes"
ty   = "str"

[[columns]]
name = "placed_ms"
ty   = "u64"                # epoch milliseconds

# Makes WHERE status = '...' sub-linear for SQL reads.

[[indexes]]
name    = "by_status"
columns = ["status"]

# Enforced on EVERY write path, including the batch hot path.

[[check_constraints]]
name       = "amount_positive"
expression = "amount_cents > 0"

Choose an operation

Task	Interface
Save or replace a record	`POST /rows/:schema` with a flat row object.
Read one record	`GET /rows/:schema/:pk`.
Replace only the version you read	`PUT /rows/:schema/:pk` with `If-Match`.
Delete one known record	`DELETE /rows/:schema/:pk`.
Write a batch	`POST /rows/:schema/_batch`.
Change selected fields or delete	SQL with a specific filter.
Read several records	SQL with selected columns and a limit.

These paths are relative to the tenant-scoped engine API. SDK method names vary by version; the examples below use the older contract.

Save and read one record

Send the fields directly in the JSON object, including the primary key. A successful save returns {"ok": true}. A successful read returns the row itself; a missing row returns 404.

Save a record

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Idempotency-Key: order-1001-v1" \
  -d '{
    "id":           "ord-1001",
    "customer":     "cus-77",
    "amount_cents": 4200,
    "status":       "pending",
    "notes":        "gift wrap",
    "placed_ms":    1714478049000
  }'

Example response

{ "ok": true }

Read the saved record

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/ord-1001" \
  -H "Authorization: Bearer $OC_TOKEN"

Example response

{
  "id":              "ord-1001",
  "customer":        "cus-77",
  "amount_cents":    4200,
  "status":          "pending",
  "notes":           "gift wrap",
  "placed_ms":       1714478049000,
  "_oc_row_version": 1
}

Example response

{ "error": "row \"ord-1001\" not found" }

Understand replacement

Saving an existing ID replaces the whole row. Omitted fields are removed. This is an upsert: it creates a new row or replaces the matching one.

Use SQL INSERT when a duplicate key should fail. A duplicate returns a constraint error rather than replacing the existing row. Use an explicit SQL conflict clause when you want a controlled upsert.

Use expect=insert to request insert-only behaviour on the row endpoint. A duplicate primary key returns 409 constraint_violation and preserves the existing record. Without that option, the row endpoint remains an upsert.

Change one field

Use SQL UPDATE with a WHERE clause. The row API has no partial-update operation. If you read and repost a row, remove the engine's _oc_row_version field and retain every field you intend to keep.

Update examples

# There is no PATCH. Re-POST the WHOLE row - every field you omit
# is erased. Read first, mutate, write back.
ROW=$(curl -s "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/ord-1001" \
  -H "Authorization: Bearer $OC_TOKEN")

echo "$ROW" \
  | jq 'del(._oc_row_version) | .status = "shipped"' \
  | curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders" \
      -H "Authorization: Bearer $OC_TOKEN" \
      -H "Content-Type: application/json" --data-binary @-

# Or let the engine do the read-modify-write for you, with SQL:
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{"sql":"UPDATE shop.orders SET status = $1 WHERE id = $2",
       "params":["shipped","ord-1001"]}'

Example response

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

For a read–change–save workflow, send the full replacement row with PUT /rows/:schema/:pk and If-Match set to the _oc_row_version you read. A changed version returns 409 write_conflict; a missing row returns 412 precondition_failed. If-Match: * checks only that the row exists.

This check applies to the conditional request; an unconditional POST, batch or SQL write can still overwrite the row. A deployment that cannot enforce the condition returns 503 precondition_capability rather than ignoring it. See conditional row updates for headers and responses.

Delete a record

Use DELETE /rows/:schema/:pk for one known key, SQL for a filtered set, or the transaction endpoint for a buffered delete. Deleting an absent record can succeed without changing anything. The direct endpoint reports deleted: false; SQL RETURNING can show which rows were removed.

Delete examples

# Delete by a SQL filter; direct DELETE /rows/:schema/:pk is also supported:
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 = $1","params":["ord-1001"]}'

# { "kind": "delete", "schema": "shop.orders",
#   "pk": "ord-1001", "rows_affected": 1 }

# ...or buffer the delete inside a transaction:
curl -X DELETE \
  "https://$OC_HOST/v1/tenants/$OC_TENANT/tx/$TX_ID/rows/shop.orders/ord-1001" \
  -H "Authorization: Bearer $OC_TOKEN"

Import a batch

Format	Behaviour
JSON array	One bounded batch. Validation failure rejects the whole batch. Send the array directly, not a `rows` wrapper.
Newline-delimited JSON	Commits in chunks. Earlier chunks may already be saved when a later chunk fails.

JSON batch examples

# The body is a RAW ARRAY. Not { "rows": [...] }.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/_batch" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    { "id": "ord-2001", "customer": "cus-12", "amount_cents": 1500,
      "status": "paid", "notes": "", "placed_ms": 1714478100000 },
    { "id": "ord-2002", "customer": "cus-12", "amount_cents": 8900,
      "status": "paid", "notes": "fragile", "placed_ms": 1714478160000 },
    { "id": "ord-2003", "customer": "cus-31", "amount_cents": 400,
      "status": "pending", "notes": "", "placed_ms": 1714478220000 }
  ]'

Example response

{ "inserted": 3 }

Streaming import example

# One JSON object per line. Nothing is held whole in memory, so this
# is the path for files that do not fit in a request body.
curl -X POST \
  "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.orders/_batch?chunk=2000" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/x-ndjson" \
  --data-binary @orders.ndjson

Example response

{ "inserted": 480000 }

Example response

{ "inserted": 312000, "error": "..." }

A streaming 207 is a partial result. Read its inserted count and error, reconcile the saved records and resume carefully. Do not replay the whole file automatically; partial responses are not cached against the idempotency key.

Read a set of records

Choose only the columns you need and add a LIMIT. Large results can exceed the response cap. For several key columns, put all of them in the SQL filter.

Query examples

# There is no "list rows" route. Read sets of rows with SQL.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{"sql":"SELECT id, customer, amount_cents FROM shop.orders WHERE status = $1 ORDER BY placed_ms DESC LIMIT 50",
       "params":["paid"]}'

Check validation errors

Status	Likely cause
`400`	Missing required field, wrong type or unsupported point-read key.
`404`	Missing row or table.
`409`	Constraint failed, duplicate insert-only key, or stale `If-Match` version.
`412`	Conditional update expected a row that does not exist.
`413`	Request or result exceeds the byte limit.
`429`	Write queue is full; inspect `Retry-After`.
`501`	Unsupported cross-shard foreign key.
`503`	The deployment cannot currently enforce the requested precondition.

Undeclared fields are stored rather than rejected. Use clear names and mark essential columns as required; a misspelled optional field may otherwise go unnoticed.

Keep requests bounded

Engine request	Limit
Single row	8 MiB body.
JSON batch	64 MiB body.
Streaming line	1 MiB per line; 64 MiB in-flight buffer.
Streaming chunk	1,000 rows by default; at most 10,000.

Gateway and instance limits can also apply. A successful primary write does not by itself prove a standby has received it. Where applicable, inspect X-OC-Replication for a degraded acknowledgement.

If a write response is lost, check whether the write happened before repeating it. Use idempotency only within the documented endpoint contract; it does not make every write safe to retry.

Prepare your tableCopy Anchor Link

Choose an operationCopy Anchor Link

Save and read one recordCopy Anchor Link

Understand replacementCopy Anchor Link

Change one fieldCopy Anchor Link

Delete a recordCopy Anchor Link

Import a batchCopy Anchor Link

Read a set of recordsCopy Anchor Link

Check validation errorsCopy Anchor Link

Keep requests boundedCopy Anchor Link

On this page

Prepare your table

Choose an operation

Save and read one record

Understand replacement

Change one field

Delete a record

Import a batch

Read a set of records

Check validation errors

Keep requests bounded