Reading and writing rows

Two endpoints per dataset, mirroring memory:commit / memory:query: data:commit writes rows in one atomic transaction, and data:query reads them back through a sectioned envelope evaluated at a single snapshot.

This page is the row transport. For what a dataset is, how to declare the tables and columns these operate on, and who is allowed to reach them, see the overview.

`data:commit`

Every operation in one read-write transaction, all-or-nothing:

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" }
        }
      }
    }
  ]
}

A row in one table and a row in another land together or not at all, in one transaction spanning every table the commit names. An operation naming a table outside your dataset's catalog rejects the whole commit.

Values are explicitly typed rather than free-form JSON, because your columns are real typed columns: an INT64 routed through a JSON number silently loses precision past 2⁵³, and TIMESTAMP/DATE/BYTES have no faithful JSON scalar.

Absent and explicitly null are different

Omitting a column leaves it alone; sending {"null_value": true} writes NULL. That distinction matters most for vector columns; see vector columns on the overview.

Operation types

type chooses what an operation does. Any mix of the four can share one commit, and they all land or none do:

`type`	Effect	Addressed by
`OPERATION_TYPE_INSERT`	Writes a new row; fails if the primary key is taken	full primary key in `row`
`OPERATION_TYPE_UPSERT`	Writes the row, or merges into the one already there	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 is what you want for a load you may re-run: the same commit applied twice leaves the same rows instead of failing on the second pass.

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" }
        }
      }
    }
  ]
}

An upsert merges - it does not replace the row

Columns you leave out keep the values they had; the row is not rebuilt from what you sent. Upserting {customer_id, email} over a row that also carries phone leaves phone intact. To clear a column, name it with {"null_value": true}.

This is the opposite of a memory-plane chunk upsert, which replaces the whole metadata set. A dataset row has typed columns you declared, so merging on the key is the safe default; a chunk's metadata is one free-form map with no schema to merge against.

UPDATE and DELETE select rows by predicate, and matching nothing is not an error - the operation is a no-op and the receipt reports 0 rows for that table. If the difference matters to you, read the row count rather than treating a successful commit as proof the row was there - or make the count a precondition of the commit, which refuses it before anything is written.

Conditional commits

A commit is atomic, but by default the conditions inside it are not. An UPDATE whose predicate matches nothing is a silent no-op that still lets every other operation land, so this commit succeeds and writes an audit row describing a state change that never happened:

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

  the update matches 0 rows (someone else moved it to version 8)
  the insert lands
  → commit SUCCEEDS, receipt: {accounts: 0, audit_log: 1}

Add expect to an operation to make its own effect a precondition of the whole commit. Unmet, nothing from any table in the commit is 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" }
      } }
    }
  ]
}

That is optimistic concurrency: carry a version column, name the version you read in where, set the next one in row, and require exactly one affected row. If someone else got there first the update matches nothing, the commit is refused with FAILED_PRECONDITION, and the audit insert does not land either. Re-read the row and decide again.

Two forms, and the difference is not cosmetic:

`expect`	Precondition rule	Common usage
`{"exactly": "1"}`	exactly one affected row	a keyed write, where the predicate names one row by primary key
`{"exactly": "0"}`	no affected rows	assert-absence - apply this only if nothing matches
`{"at_least_one": true}`	one or more, any number	a predicate-selected write whose count you cannot know in advance

Use at_least_one for delete-if-exists, or for "close every open session for this user, and fail if there were none". Do not reach for exactly: 1 on a predicate that legitimately matches several rows - you will get a failure that reads like a conflict and is not one.

Valid on UPDATE and DELETE only

On INSERT and UPSERT an expect is refused as INVALID_ARGUMENT rather than ignored. Those go through mutations, whose row count is a prediction recorded when the write is buffered - the backend applies mutations at commit and reports no per-operation count, so there is nothing to check an expectation against.

Insert-if-absent needs no expect: a duplicate primary key already refuses the whole commit.

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 naming the operation's index, its table, and the expected and observed counts. Read the detail rather than the status code alone - a rejected truncation reports the same code, and a retry loop that cannot tell them apart will spin forever on the one no retry can fix.

Never retry a precondition failure blindly. The platform does not retry it, deliberately: an unchanged replay fails identically, and a replay after the contended row moved again succeeds against a state you never evaluated - a lost update. Re-read, re-decide, and back off yourself; a loop that retries immediately will spin under contention.

From the CLI a commit is file-driven, because an operation tree has no readable flag form and a partially-specified commit is worse than none:

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

The one exception is deleting a single row by primary key, which is unambiguous and bounded to one row:

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

Every primary-key column must be named. A partial key is refused by the CLI rather than sent - the API would accept it and delete every matching row in your dataset.

Resending a commit safely

A commit that times out, or whose connection drops, leaves you unable to tell whether it applied. Send an idempotency_key and you can ask:

Resend handling

On any ambiguous failure, resend the identical request with the same idempotency_key. The commit is applied at most once, and the resend returns the original commit's receipt. Do not read tables back to guess, and do not mint a new key.

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 on unique constraint	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 operations associated with each key. If an existing key is submitted with different operations, the request is rejected with INVALID_ARGUMENT:

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

Row conflicts resulting from duplicate primary keys in data payloads remain reported as row-level errors.

Key retention period

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

Failed commits do not persist idempotency keys, allowing failed operations to be retried.

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" }
    }
  ]
}

No prior read, no retry loop, and no window in which another writer can slip between your read and your write. OPERATOR_SUBTRACT is the other half - a debit is its own operator rather than an addition you have to encode as a negative.

The target must be an INT64 column declared NOT NULL. A nullable column is refused, because arithmetic over an absent value yields an absent value and the backend reports that as success - your counter would be silently emptied, and the platform will not quietly read a missing value as zero either. FLOAT64 is refused because its overflow is a silent infinity and because accumulated float addition depends on the order commits happen to interleave in.

An expression cannot create the row it increments

total = total + 1 against an account that has no row affects zero rows and does nothing. There is no value to compute from, so there is no insert-or-increment to offer.

An increment that vanishes looks exactly like one that landed, and for a total accumulated over many commits there is no later read that tells you which events were absorbed. Always pair an expression with expect.

So the pattern is: create the counter row once, then increment it forever after.

# Once per account, at signup. A duplicate key here means the
# counter already exists, which is the outcome you wanted.
commit { INSERT totals {account_id: 'a-1', total: 0} }

# Every time after that.
commit { UPDATE totals SET total = total + 1200 WHERE account_id='a-1'
         expect: exactly 1 }

The reason this matters more than the round trip it saves is that a fact and the total it moves can now land in one 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
}

The ledger row and the rollup can never disagree, and no reader can observe one without the other. Nothing stops you from inserting a ledger row without the paired update - the platform does not police that - but when you do pair them, they land in one transaction.

An expression commit must carry an idempotency_key

Every other write here is safe to resend: INSERT collides on its key, and UPSERT and UPDATE assign absolute values, so applying one twice leaves the same state as applying it once. total = total + 1200 is the first write where that stops being true, and a double application is silent - the second receipt looks exactly like the first.

A commit carrying an expression and no key is refused with INVALID_ARGUMENT before anything is written. Use one key per logical operation - the invoice, the charge, the event - not one per retry. Its retention window applies here exactly as it does everywhere else, and a resend outside that window applies again.

A commit of absolute values still needs no key. Nothing you send today changes.

If the arithmetic cannot be represented, the whole commit is refused with OUT_OF_RANGE naming the table and column, and nothing is written - not a wrapped value, not a saturated one. There is no partial state to reconcile before you retry.

One row has a write-rate ceiling - shard above it

A single Spanner row tops out at a few hundred writes per second under lock contention, and expressions make it very easy to aim every writer in your system at one counter.

Above that rate, use sharded counters: write N rows (account_id, shard), increment a random one, and SUM them on read. The trade-off is real and worth stating - a sharded counter gives up whole-counter conditionals, because no single row holds the total for a predicate to compare against. Keep a single row while you need balance >= amount to be enforceable; shard when throughput matters more.

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`

A sectioned envelope: a relational section and a vector section, evaluated at one read snapshot so a predicate and a ranking 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 use the same operator set as memory metadata filters (EQUALS, LESS_THAN, LESS_THAN_OR_EQUAL, GREATER_THAN, GREATER_THAN_OR_EQUAL), including what it leaves out: no inequality, and no disjunction between predicates (they are conjunctive). One difference: because your columns are typed, comparison is natural. 10 > 9 on an INT64, and TIMESTAMP orders chronologically. The zero-pad-your-numbers advice that applies to untyped memory metadata does not apply here.

A row whose column is NULL matches no operator, including EQUALS. There is no value to compare, so NULL excludes.

The common single-table read needs no file:

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

The CLI reads your dataset's catalog first, so total_cents>1000 binds an int64_value rather than a string - the comparison is numeric, matching the typed semantics above. A column your catalog does not hold is refused locally, listing the columns the table does have, rather than coming back as a not-found. So is an operator the platform does not implement: status!=open and a=1 OR b=2 are rejected before any request, with the same explanation the API would give.

A join, a projection over a joined table, both sections in one request, or a staleness bound has no readable flag form - write the QueryDataRequest above to a file and pass --from-file q.json. The flags tell you when a combination is not expressible rather than silently dropping part of it.

One projection is refused: two columns that would land on the same name in the response, which a join makes possible when both tables hold that column name - ["orders.total", "line_items.total"]. A result row is keyed by column name, so only one of the two could survive and nothing in the response would say which. The error names both columns and the name they collide on; ask for them in separate queries.

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 embeds server-side and requires the column to declare a source_column, because the platform will not guess which model to embed a query with for vectors it did not produce. Otherwise supply embedding directly; its width must match the column's.

Accompanying predicates are evaluated before ranking. So you get the nearest limit rows among those that match, not the predicates applied to an already-ranked set, which would return fewer results (often zero) for reasons you could not predict.

The single-table case is a flag:

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

--near needs the column to declare a source_column, for the reason above; the CLI checks that against your catalog and points you at --from-file when the column is caller-supplied only. --order-by and --all are refused with --near rather than ignored: the vector section is a top-limit ranking, so it has neither an ordering to choose nor a continuation to walk. Ask for more neighbours by raising --limit.

Pagination

next_page_token empty means exhausted, not merely "this page ended", so you can tell "that was everything" from "there may be more". Feed it back as page_token. Tokens are opaque; a token the server cannot interpret is rejected rather than silently restarting from the first page.

Your select has no effect on this. The value the platform needs to resume a walk comes from the table's schema, not from the columns you projected, so a projection naming no key column - or only part of a composite one - still enumerates to exhaustion. Nor does it reach you: the response carries exactly the columns you named. And because an empty token means exhausted, a continuation the platform cannot produce is an error, never a page served without one.

jnh datasets query --all walks that for you, pinning every page after the first to the first page's read timestamp so a commit landing mid-walk cannot shift rows across page boundaries.

Staleness

Reads default to strong and externally consistent. For a multi-region dataset you can trade that for replica-local latency:

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

The bound is applied uniformly to every section, so sections can never observe different instants. read_timestamp pins an exact instant, which is useful for keeping a paginated walk stable. It is bounded by the backend's version-retention window (about an hour).