OriginChain docs
query shapes · row crud

Row CRUD.

The row endpoints are the direct door to the store: address a row by its primary key and write or read it, with no query to plan. Reach for them when your application already knows the key — fetching a session, saving a record, loading an order by id — and when you are loading data in bulk, where the batch route is by a wide margin the fastest path into the database.

Use SQL instead when the question is "which rows", when you need a projection or an aggregate, or when you want a partial update. The two surfaces write to the same store and enforce the same constraints — but they differ on one thing that matters, covered in section 4.

1

Before you start.

Every example on this page uses the shop.orders table from the quickstart. Two things in this manifest matter for the row endpoints specifically:

  • A single-column primary key. The /rows/:schema/:pk point read addresses one path segment. A table with a composite primary key is readable only through SQL.
  • Declared columns. They drive type validation and the canonical on-disk form of each value.
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"

The shell examples assume $OC_HOST, $OC_TENANT and $OC_TOKEN are set — see Authentication. The SDK examples assume a client named db, built as in the quickstart.

2

The whole surface.

Three routes. That is the complete list — there is no PUT, no PATCH, no DELETE, and no list route on the row surface.

Method Path Body Response
POST /v1/tenants/:t/rows/:schema One row object, flat. 200 { "ok": true }
GET /v1/tenants/:t/rows/:schema/:pk — 200 the row object, bare · 404 if absent
POST /v1/tenants/:t/rows/:schema/_batch A raw array of rows, or newline-delimited JSON. 200 { "inserted": n } · 207 on a partial stream

Deleting a row and reading a set of rows both go through SQL; deleting is also available inside a transaction. SDK coverage is uneven and worth checking before you plan around it:

Operation cURL Python TypeScript Go
Write one row yes db.rows.put(schema, row) not wrapped not wrapped
Read one row yes db.rows.get(schema, pk) not wrapped not wrapped
Batch write yes db.rows.put_batch(schema, rows) not wrapped not wrapped
Delete a row via SQL via db.sql.execute via db.sql via db.SQL
Read many rows via SQL db.sql.query db.sql db.SQL

Only the Python client wraps the row endpoints today. The TypeScript and Go examples on this page therefore call the HTTP API directly — which is exactly what those SDKs will do for you when the helpers ship.

3

Write and read one row.

Write

POST /rows/:schema takes the row as a flat JSON object — fields at the top level, not nested under a row key. The primary key travels in the body like any other column. A success is a terse { "ok": true }: the endpoint does not echo the row back.

Send an Idempotency-Key header on writes you might retry. A repeat of the same key replays the original response instead of writing again — which matters because a client that times out and retries has no other way to tell a lost request from a lost response.

POST /rows/:schema 
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
  }'

# 200
# { "ok": true }

Read

GET /rows/:schema/:pk returns the row object bare — no envelope, no rows array. This is a hash lookup on the key, not a scan, so it does not get slower as the table grows.

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

# 200 - the row object, bare (no envelope):
# {
#   "id":              "ord-1001",
#   "customer":        "cus-77",
#   "amount_cents":    4200,
#   "status":          "pending",
#   "notes":           "gift wrap",
#   "placed_ms":       1714478049000,
#   "_oc_row_version": 1
# }

# 404
# { "error": "row \"ord-1001\" not found" }
the _oc_row_version field

Every row comes back with an engine-maintained _oc_row_version counter — 1 on first write, incremented on every subsequent one. It is useful for spotting that a row changed. It is not a concurrency control: no write path checks it, and there is no If-Match or expected-version parameter on any HTTP write. Do not write it back — strip it before re-posting a row you read.

4

Upsert semantics — read this one.

the row endpoint is an upsert, not an insert

POST /rows/:schema with a primary key that already exists replaces the existing row and returns 200. It is last-write-wins by design. There is no duplicate-key error on this path. If you need "fail if it already exists", use SQL INSERT — below.

This is deliberate. The underlying store is key-addressed, so writing a key overwrites it, and the row endpoint keeps that contract untouched because it is also the high-throughput ingest path. What the engine does do on an overwrite is retire the old row's secondary-index and relation entries in the same atomic write, so an overwrite leaves nothing stale behind.

SQL INSERT is strict. A duplicate primary key over /sql returns 409 constraint_violation and writes nothing — the existing row is preserved. It catches duplicates against committed state and duplicates within the same multi-row VALUES list. Use INSERT … ON CONFLICT when you want an explicit, controlled upsert.

?expect=insert does not do what its name suggests

The ?expect=insert query parameter (and the Python client's expect_insert=True) is a performance hint, not an assertion. It tells the engine to skip reading the prior row, which saves a lookup per row on bulk ingest. It does not make a duplicate key fail — the write still overwrites — and because the prior row was never read, its old secondary-index entries are not retired, leaving stale index entries behind. Use it only when you know the keys are new.

5

Updating a row.

every row write is a full replace

There is no merge or partial-update route. Whatever object you post becomes the row — any column you leave out is erased, not preserved. This is the single most common way to lose data through this API.

Two ways to change one field. Prefer the SQL form: it does the read-modify-write inside the engine, so there is no window between your read and your write, and no chance of dropping a column you forgot to carry over.

changing one field 
# 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"]}'
# { "kind": "update", "schema": "shop.orders", "rows_affected": 1 }

Two rules on the SQL form: a WHERE clause is mandatory — a bare UPDATE is refused as a safety check — and you cannot SET a primary-key column. To change a key, write the new row and delete the old one.

6

Deleting a row.

There is no DELETE /rows/:schema/:pk route. Deleting goes through SQL, or through the transaction row surface where the verb does exist.

The delete is real, not a tombstone: the row body, every secondary-index entry and every relation entry derived from it are removed in one atomic write, and a subsequent point read returns 404.

deleting by primary key 
# There is NO DELETE /rows/:schema/:pk route. Use SQL:
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 delete inside a transaction, where the verb IS available:
curl -X DELETE \
  "https://$OC_HOST/v1/tenants/$OC_TENANT/tx/$TX_ID/rows/shop.orders/ord-1001" \
  -H "Authorization: Bearer $OC_TOKEN"
deleting a row that is not there

It is a silent success, not a 404. Nothing is written and the call returns normally. If you need to know whether a row existed, read it first, or use DELETE … RETURNING over SQL and check whether any rows came back.

7

Batch and streaming writes.

POST /rows/:schema/_batch is the bulk path, and the throughput difference is not marginal — the batch route sustains roughly an order of magnitude more rows per second than issuing the same rows one request at a time, because the whole array is prepared once and committed in a single durable write.

The body is a raw array of row objects. Wrapping it as { "rows": [...] } is the most common mistake here and returns a 400 about expecting a sequence.

POST /rows/:schema/_batch — JSON array 
# 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 }
  ]'

# 200 - all three landed in ONE log frame, or none did.
# { "inserted": 3 }
the JSON array form is all-or-nothing

One array is one log frame. If any row fails validation or a constraint, the request fails and none of the rows are written. That is a real guarantee you can lean on for a bounded batch — and it is exactly why this form is capped at 64 MiB rather than being unbounded.

Streaming ingest for anything larger

Send Content-Type: application/x-ndjson and the same route switches to a streaming loader: one JSON object per line, flushed in chunks, with no cap on the total body. This is the path for a multi-gigabyte load.

POST /rows/:schema/_batch — newline-delimited JSON 
# 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

# 200  - everything landed:
# { "inserted": 480000 }

# 207  - it failed partway. The count is REAL: those rows are durable.
# { "inserted": 312000, "error": "..." }
streaming ingest is NOT all-or-nothing

Chunks commit as they go. A failure partway returns 207 Multi-Status with an inserted count and an error — and that count is real: those rows are already durable. Treat 207 as a partial success and resume from the reported offset. Note also that a 207 is not cached against your idempotency key, so a blind retry re-sends everything.

Tune the flush size with ?chunk=N. The default is 1,000 rows per flush and values above 10,000 are clamped down silently rather than rejected. Larger chunks trade memory for fewer flushes.

8

Reading many rows.

There is no "list rows" or "scan table" route on the row surface, and no cursor. To read a set of rows, use SQL — which every SDK wraps, and which lets you project only the columns you need.

reading a set of rows 
# 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"]}'
always put a LIMIT on it

A projection over a large table is assembled in memory before it is sent, and past the per-query cap the request fails with 413 result_too_large rather than streaming. Keep a LIMIT on anything that reads a big table, and page with OFFSET — or aggregate server-side, which does stream. See the SQL limits for the details.

9

What gets validated.

Every write path — single row, batch array, streaming ingest, SQL, and transactions — goes through the same validation. What it checks:

  • Required columns are present. A missing column declared required = true is a 400. An explicit null on a required column is the same error.
  • Types match, they are not coerced. A string where an i64 is declared is rejected, not parsed.
  • Some values are canonicalised. A decimal sent as a JSON number is stored as its exact decimal string; a date or timestamp sent as a text literal is stored as its integer form. Both spellings address the same row, including in the primary key.
  • CHECK constraints and unique indexes are enforced on every write, including the bulk path. A violation is a 409.
  • Foreign keys are enforced before the write is queued. An orphan reference is a 409.
undeclared columns are accepted, not rejected

Validation walks the columns your manifest declares — it never walks the keys of your request object. A field that is not in the schema is stored verbatim and returned on read. There is no strict-schema mode. That makes ad-hoc extra fields easy, and it makes a typo like amount_cent silently create a junk field while the real column stays unset — which then trips its own required-column or CHECK error, if you declared one. Declare required = true on the columns you cannot do without.

HTTP When Body
400 A required column is missing, a value has the wrong JSON type, or the primary key in the path will not coerce to the column's type. { "error": "…" }
400 A point read against a table whose primary key spans more than one column. composite primary keys are not addressable by path…
404 The row does not exist, or the table is not registered. { "error": "row \"…\" not found" }
409 A foreign key, CHECK or UNIQUE-index constraint failed. { "error": "constraint_violation", "detail": "…" }
413 A single-row body over 8 MiB, a batch body over 64 MiB, or a newline-delimited line over 1 MiB. { "error": "body too large or unreadable: …" }
429 The write queue is saturated. Carries Retry-After: 1. { "error": "write queue full (n commits pending); retry" }
501 On a sharded instance, a foreign key pointing at a table owned by another shard. { "error": "…" }
10

Limits & gotchas.

Limit Value Notes
Single-row body 8 MiB The general request limit. A row larger than this must go through the batch route.
Batch body (JSON array) 64 MiB Eight times the single-row limit, because the whole array is buffered to commit atomically.
Rows per batch no cap There is no row-count limit — the byte size is the real constraint.
Streaming body unlimited Per line, 1 MiB. In-flight buffer, 64 MiB — lower ?chunk=N if you hit it.
Streaming flush size 1,000 / 10,000 Default and maximum ?chunk=N. A larger value is clamped, not refused.
Write queue depth 4,096 Pending commits. Over it, writes shed with 429 and Retry-After: 1 — back off rather than hammering.
no optimistic concurrency on this surface

No If-Match, no expected-version parameter, no compare-and-set. Two clients doing read-modify-write on the same row through the row endpoint will both succeed and one update is lost, silently. If that matters, use a transaction, whose commit revalidates what you read.

composite primary keys have no point-read route

GET /rows/:schema/:pk addresses one path segment. Against a table whose primary key spans two or more columns it returns 400, naming the number of key columns. Read those tables with SQL: WHERE k1 = … AND k2 = …. Writing is unaffected — the key columns travel in the body like any other.

a degraded write is still a successful write

Replication to a standby is asynchronous: the 200 means the write is flushed to durable storage on this instance, not that the standby holds it. Where a standby-acknowledgement wait is configured and the standby does not answer in time, the write still returns 200 and the response carries X-OC-Replication: degraded. If your application cares about that distinction, check the header; a bare status code will not tell you. Row writes are the only endpoints that wait at all — SQL, Cypher and transaction commits never do.

a single write is not slower than a batched one, per request

Single-row writes go through the same group-commit path as batches — concurrent writes are coalesced into one flush rather than each paying for their own. So a busy single-row workload scales fine. The batch route wins on bulk loading, where you have the rows in hand and can skip the per-request overhead entirely.

Related.

  • SQL — reading sets of rows, partial updates, deletes, and strict inserts.
  • Transactions — making several row writes land together, with conflict detection.
  • Schema reference — every field in the manifest, including column types and constraints.
  • Error reference — the shared error envelope every endpoint returns.