Reading and writing rows

Jennah provides two endpoints per dataset: data:commit writes rows in an atomic transaction, and data:query reads them through a sectioned envelope evaluated at a single snapshot.

This page covers row operations. For dataset concepts, schema definitions, and access control, see the overview.

`data:commit`

data:commit executes operations within a single atomic read-write transaction:

POST /v1/datasets/acme.prod/data:commit
{
  "operations": [
    {
      "table": "customers",
      "type": "OPERATION_TYPE_INSERT",
      "row": {
        "columns": {
          "customer_id": { "string_value": "c1" },
          "email": { "string_value": "a@example.com" }
        }
      }
    },
    {
      "table": "orders",
      "type": "OPERATION_TYPE_INSERT",
      "row": {
        "columns": {
          "order_id": { "string_value": "o1" },
          "customer_id": { "string_value": "c1" },
          "total_cents": { "int64_value": "1999" }
        }
      }
    }
  ]
}

Operations across multiple tables are committed atomically. Referencing a table outside the dataset catalog rejects the entire commit.

Values are explicitly typed rather than free-form JSON to preserve precision (such as INT64 values beyond 2⁵³ in JSON numbers) and support types without native JSON scalar equivalents (TIMESTAMP, DATE, BYTES).

Omitted columns vs. explicit nulls

Omitting a column preserves its existing value; sending {"null_value": true} explicitly sets the column to NULL. For vector column behavior, see vector columns.

Operation types

The type field specifies the operation to execute. Multiple operation types can be combined in a single commit:

`type`	Effect	Addressed by
`OPERATION_TYPE_INSERT`	Writes a new row; fails if the primary key exists	full primary key in `row`
`OPERATION_TYPE_UPSERT`	Writes a new row or merges into an existing row	full primary key in `row`
`OPERATION_TYPE_UPDATE`	Sets the columns in `row` on matching rows	`where`
`OPERATION_TYPE_DELETE`	Deletes matching rows	`where`

Upsert operations support idempotent data ingestion: re-running a commit updates existing rows instead of failing with primary key conflicts.

POST /v1/datasets/acme.prod/data:commit
{
  "operations": [
    {
      "table": "customers",
      "type": "OPERATION_TYPE_UPSERT",
      "row": {
        "columns": {
          "customer_id": { "string_value": "c1" },
          "email": { "string_value": "new@example.com" }
        }
      }
    }
  ]
}

Upsert merge behavior

Omitted columns retain their existing values rather than being cleared. For example, upserting {customer_id, email} into a row containing phone leaves phone intact. To clear a column value, set it explicitly to {"null_value": true}.

Unlike memory-plane chunk upserts (which replace all metadata), dataset row upserts merge into existing typed columns.

UPDATE and DELETE operations match rows by predicate. If no rows match, the operation is a no-op and the receipt reports 0 affected rows. To require matching rows, configure an execution precondition on the operation.

An empty where matches every row

An UPDATE or DELETE with no where is accepted and affects every row of the table. Check that a programmatically built predicate list is not empty before sending it.

Conditional commits

By default, an UPDATE that matches zero rows succeeds as a no-op while allowing subsequent operations in the same commit to proceed. For example:

commit { UPDATE accounts SET status='shipped' WHERE id=... AND version=7
         INSERT audit_log "shipped by user X" }

  update matches 0 rows (concurrent write modified version to 8)
  insert succeeds
  -> commit succeeds, receipt: {accounts: 0, audit_log: 1}

Use expect to specify affected row preconditions for the commit. If the precondition is unmet, the entire commit fails and no operations are written:

POST /v1/datasets/acme.prod/data:commit
{
  "operations": [
    {
      "table": "accounts",
      "type": "OPERATION_TYPE_UPDATE",
      "row": {
        "columns": {
          "status":  { "string_value": "shipped" },
          "version": { "int64_value": "8" }
        }
      },
      "where": [
        { "column": "account_id", "operator": "OPERATOR_EQUALS",
          "value": { "string_value": "a1" } },
        { "column": "version", "operator": "OPERATOR_EQUALS",
          "value": { "int64_value": "7" } }
      ],
      "expect": { "exactly": "1" }
    },
    {
      "table": "audit_log",
      "type": "OPERATION_TYPE_INSERT",
      "row": { "columns": {
        "audit_id": { "string_value": "log-1" },
        "note":     { "string_value": "shipped by user X" }
      } }
    }
  ]
}

To implement optimistic concurrency control: store a version column, match the expected version in where, increment the version in row, and require {"exactly": "1"} affected row. If a concurrent write modified the row, the update matches zero rows and the commit fails with FAILED_PRECONDITION without writing the audit entry.

Supported precondition rules:

`expect`	Precondition rule	Common usage
`{"exactly": "1"}`	Exactly one affected row	Single-row update or delete matching a unique primary key
`{"exactly": "0"}`	Zero affected rows	Assert absence: apply only if no rows match
`{"exactly": "N"}`	Exactly `N` affected rows (any non-negative count)	Batch update that must touch a known number of rows
`{"at_least_one": true}`	One or more affected rows	Batch or multi-row operations requiring at least one match

Use at_least_one for operations such as closing all active sessions for a user where at least one row must match. Do not use {"exactly": "1"} for predicates that match multiple rows.

Supported operation types for preconditions

Specifying expect on INSERT or UPSERT operations returns INVALID_ARGUMENT. Insert and upsert operations report no matched-row count, so there is nothing for expect to check.

Insert-if-absent does not require expect: duplicate primary keys reject the commit with ALREADY_EXISTS (HTTP 409). See Duplicate primary keys.

Duplicate primary keys

An INSERT operation with an existing primary key rejects the commit with ALREADY_EXISTS (HTTP 409), writing no changes. ALREADY_EXISTS indicates a row conflict and is not used for internal platform errors.

Error message attribution based on commit structure:

Commit structure	Reported details
Single `INSERT`	Operation index and table name (for example, `operation 0: a row with this primary key already exists in table "claims"`)
Multiple `INSERT`s in one table	Table name
Multiple `INSERT`s across multiple tables	Generic conflict message without table attribution

Error messages omit row key values; use the reported operation index to identify the conflicting row. In jnh, conflicts exit with code 7.

Atomic preconditions

Use expect parameters directly on UPDATE and DELETE operations rather than building guard tables.

A failed precondition returns FAILED_PRECONDITION with a google.rpc.PreconditionFailure detail containing the operation index, table name, and expected versus observed row counts. Inspect the error detail rather than relying on the status code alone, as other errors (such as rejected truncations) share the same code.

Precondition failures should not be retried automatically with unchanged inputs. Replaying the identical request fails again, while retrying against modified state risks lost updates. Applications should fetch current row values, re-evaluate conditions, and apply backoff under contention.

In the CLI, commits are specified via JSON files:

jnh datasets commit acme.prod --from-file commit.json
jnh datasets commit acme.prod --from-file commit.json --reject-on-truncation

Single-row deletions by primary key can also be executed directly:

jnh datasets delete-row acme.prod --table orders --key order_id=o1

All primary-key columns must be specified. The CLI validates primary key completeness locally before sending the request.

Deleting a row that does not exist succeeds and reports zero rows. Add --if-exists to require the row instead: the commit is refused with FAILED_PRECONDITION and nothing is written when no row matches.

Resending a commit safely

If a commit request times out or disconnects before a response is received, supply an idempotency_key to safely retry:

Resend handling

On ambiguous network errors or timeouts, resend the identical request with the same idempotency_key. The commit is applied at most once, and replayed requests return the original commit receipt without re-executing writes.

POST /v1/datasets/acme.prod/data:commit
{
  "idempotency_key": "charge-invoice-4021",
  "operations": [ ... ]
}

Without an idempotency key, retrying a request can cause unexpected state changes or false conflict errors:

Commit contents	Effect of keyless resend	Impact
`INSERT`	Fails with `ALREADY_EXISTS`	Indistinguishable from genuine conflict with another writer
Absolute `UPDATE`s	Applies again	Re-executes updates and generates redundant commit receipts
`expect` (CAS)	Returns `FAILED_PRECONDITION`	Fails because earlier attempt already modified target state

Idempotency key scope

The key identifies the logical unit of work (such as "charge-invoice-4021" or "apply-batch-88"):

Retries of the same operation must reuse the same idempotency_key.
Distinct logical operations must use different keys.
Sending unique keys per retry attempt treats each attempt as a separate request.

Keys are caller-defined strings up to 256 characters (such as UUIDs). Idempotency keys are scoped per dataset and are removed when the dataset is deleted.

Return value on replay

A replayed commit request returns the original commit receipt (including commit timestamp, affected row counts, and truncation reports) without executing duplicate writes.

Key reuse with modified operations

The platform computes a checksum of the operations and the reject_on_truncation setting associated with each key. If an existing key is submitted with different operations, or with reject_on_truncation flipped, the request is rejected with INVALID_ARGUMENT:

Key status	Request payload	Outcome
Existing key	Identical operations and flag	Success (returns original receipt)
Existing key	Modified operations or flag	`INVALID_ARGUMENT`

Primary key conflicts (ALREADY_EXISTS) take precedence and are reported immediately; failed commits do not persist the idempotency key, allowing failed operations to be retried.

Key retention period

Idempotency keys are retained for at least 24 hours following the initial commit.

Counters and rollups

Use set_expressions to execute atomic in-place column modifications directly within the transaction without separate read-modify-write cycles:

POST /v1/datasets/acme.prod/data:commit
{
  "idempotency_key": "invoice-4021-post",
  "operations": [
    {
      "table": "totals",
      "type": "OPERATION_TYPE_UPDATE",
      "set_expressions": {
        "total": {
          "column": "total",
          "operator": "OPERATOR_ADD",
          "operand": { "int64_value": "1200" }
        }
      },
      "where": [
        { "column": "account_id", "operator": "OPERATOR_EQUALS",
          "value": { "string_value": "a-1" } }
      ],
      "expect": { "exactly": "1" }
    }
  ]
}

This avoids read-modify-write loops and race conditions between concurrent writers. OPERATOR_ADD and OPERATOR_SUBTRACT are both supported.

Target columns must be INT64 and defined as NOT NULL. Nullable columns are rejected because arithmetic operations on NULL evaluate to NULL. FLOAT64 is not supported due to non-associative rounding and silent overflow to infinity.

Expressions do not create missing rows

An expression update against a non-existent row affects zero rows. Pair expression updates with expect: {"exactly": "1"} to ensure the target row exists:

# Initialize counter row
commit { INSERT totals {account_id: 'a-1', total: 0} }

# Increment existing counter
commit { UPDATE totals SET total = total + 1200 WHERE account_id='a-1'
         expect: exactly 1 }

Expression updates and source event rows can be committed within the same transaction:

commit {
  INSERT ledger { entry_id: <uuid>, account_id: 'a-1', amount: 1200 }
  UPDATE totals { total = total + 1200 } WHERE account_id='a-1'
  expect: exactly 1
}

Expression commits require an idempotency_key

Unlike absolute value updates, replaying an expression update modifies state again. Commits containing set_expressions without an idempotency_key are rejected with INVALID_ARGUMENT.

Reuse the same idempotency key across retries of the same logical operation. Commits containing only absolute column updates do not require an idempotency key.

Arithmetic overflow or underflow rejects the entire commit with OUT_OF_RANGE, specifying the affected table and column without applying partial writes.

High-throughput counters and sharding

A single database row is bounded by write contention (typically a few hundred writes per second).

For higher write volumes, use sharded counters: distribute writes across N rows (account_id, shard_id), increment a randomly selected shard, and aggregate the total using SUM during reads. Sharded counters cannot enforce single-row balance constraints (such as balance >= amount) across shards.

Combine conditional expressions with expect to enforce invariants (such as non-negative balances):

commit {
  UPDATE accounts SET balance = balance - 500
    WHERE account_id='a-1' AND balance >= 500
  expect: exactly 1
  ↑ 0 affected means insufficient funds, so the whole commit
    is refused rather than the debit being silently skipped
}

`data:query`

data:query uses a sectioned envelope containing relational and vector sections, evaluated at a single read snapshot so that relational predicates and vector rankings observe the same instant.

POST /v1/datasets/acme.prod/data:query
{
  "relational": {
    "table": "orders",
    "select": ["order_id", "customers.email"],
    "joins": [
      {
        "table": "customers",
        "left_column": "customer_id",
        "right_column": "customer_id",
        "type": "JOIN_TYPE_INNER"
      }
    ],
    "where": [
      {
        "column": "total_cents",
        "operator": "OPERATOR_GREATER_THAN",
        "value": { "int64_value": "1000" }
      }
    ],
    "order_by": [
      { "column": "total_cents", "descending": true }
    ],
    "limit": 50
  }
}

Predicates support the standard operator set: EQUALS, LESS_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN, and GREATER_THAN_OR_EQUAL. Multiple predicates are evaluated conjunctively (AND). Comparisons use column data types directly (INT64 compares numerically, and TIMESTAMP orders chronologically).

Columns with NULL values do not match any comparison operator, including EQUALS.

A join's type is JOIN_TYPE_INNER (also used when type is unset) or JOIN_TYPE_LEFT, which keeps root rows that have no match.

limit defaults to 100 when unset. With no select, a query returns every column of the root table except vector columns; name a vector column in select to get it back.

Single-table queries can be run directly from the CLI:

jnh datasets query acme.prod --table orders \
    --where 'total_cents>1000' --order-by total_cents:desc --limit 50

The CLI validates predicates against the dataset catalog and types parameters accordingly (for example, parsing total_cents>1000 into an int64_value). Unknown columns and unsupported operators (!=, OR) are rejected during client validation.

Complex queries (including joins, joined projections, combined relational and vector sections, or explicit staleness bounds) should be defined in a JSON file and passed via --from-file q.json.

Queries that project multiple columns with identical unqualified names (such as ["orders.total", "line_items.total"] in a join) are rejected because result rows are keyed by column name. Use separate queries or distinct projections to avoid naming collisions.

Searching vector columns

{
  "vector": {
    "table": "docs",
    "column": "embedding",
    "query_text": "a fast animal leaping",
    "where": [
      {
        "column": "lang",
        "operator": "OPERATOR_EQUALS",
        "value": { "string_value": "en" }
      }
    ],
    "limit": 10
  }
}

query_text performs server-side embedding generation and requires the target vector column to declare a source_column. For columns without a declared source column, provide the raw float vector in embedding directly (the vector dimension must match the column definition).

Predicates in where are evaluated before vector ranking (pre-filtering), returning the top nearest rows among those satisfying the filter conditions.

Single-table vector searches can be run via the CLI:

jnh datasets query acme.prod --table docs --vector-column embedding \
    --near "a fast animal leaping" --where lang=en --limit 10

--near requires the target column to have a configured source_column. If embedding values must be supplied manually, use --from-file. Flags --order-by and --all are incompatible with vector search and return an error if combined with --near.

Pagination

An empty next_page_token indicates that all matching rows have been returned. Pass next_page_token as page_token on subsequent requests to retrieve the next page. Page tokens are opaque strings; invalid or unparseable tokens are rejected with an error rather than resetting pagination to the first page.

A walk resumes in the result's own order. Following next_page_token to the end returns every matching row exactly once, in the order the query asked for:

Ordered queries. With order_by, each page continues from the last row served in that order, so order_by: total_cents desc pages through the rows by descending total. Rows that tie on the sort columns are ordered by the root table's primary key.
NULL values. NULL sorts first in ascending order and last in descending order. A walk treats NULL values like any other values, including the absent columns of rows a JOIN_TYPE_LEFT join did not match.
Joins that return several rows per root row. When a join can match more than one row (for example, orders joined to line_items on order_id), the joined table's primary key also orders the result, so a page can end partway through one order's lines and the next page continues with the rest. A join on a column that is the joined table's whole primary key, or that a UNIQUE index covers by itself, matches at most one row and adds no ordering columns.
Composite primary keys. Tables keyed by several columns page like any other.

The columns a walk resumes from are read whatever the projection, so a projection that leaves out the sort or key columns still pages through every row, and those columns are never added to the returned rows.

A page token belongs to the query that produced it. Supplying it with a different order_by or a different joins list is rejected with INVALID_ARGUMENT; change the query and start again without a token.

The CLI flag jnh datasets query --all automatically iterates across all pages, pinning all subsequent requests to the initial page's read timestamp to prevent row shifts caused by concurrent commits. --all also walks a --from-file request. If the file sets staleness.read_timestamp, the walk reads at that instant instead. Any other staleness is refused with --all, because its pages would not share one snapshot.

To fetch one page at a time, pass the token a page printed to --page-token, repeating that page's --table, --where, --order-by, --select, and --limit:

jnh datasets query acme.prod --table orders --order-by total_cents:desc \
    --limit 20 --page-token <token>

A --from-file request carries its token as relational.page_token instead. --page-token cannot be combined with --all or --near.

Staleness

Reads are strongly consistent by default. Multi-region datasets can configure bounded staleness to optimize for local replica latency:

{ "staleness": { "max_staleness": "10s" } }

staleness takes exactly one of:

Field	Reads at
`max_staleness`	any instant no older than the bound
`exact_staleness`	exactly that far in the past
`read_timestamp`	the given timestamp

Staleness bounds apply uniformly across all query sections. All three reach back at most 50 minutes: a longer staleness or an older read_timestamp is refused with INVALID_ARGUMENT.