Upsert

Upsert is one of only two single-shot write RPCs on dodil.tables.v1.Tables (the other is Delete). Rows are typed RowSet payloads — no SQL text, no quoting, no injection surface. This page also covers the two multi-row write surfaces: WriteStream and Commit.

See the Data hub for the full list.

Upsert vs Update: UPDATE is predicate-addressed (SET … WHERE …) and never creates rows. Upsert is row-identity-addressed and inserts when the key does not exist.

Upsert

Request

dodil data table upsert users -b kb-prod \
  -r '{"id":101,"email":"[email protected]","tier":"pro"}' \
  -r '{"id":102,"email":"[email protected]","tier":"free"}'

{ "wal_written": true, "wal_ulid": "01JX7QK9E4S2N..." }

rpc Upsert(UpsertRequest) returns (WriteAck);
 
// merge=false → full-row upsert; merge=true → partial-column merge
// (absent columns stay untouched).
message UpsertRequest {
  string db_id = 1;
  string table = 2;
  RowSet rows = 3;                     // typed or Arrow
  // The primary-key columns. EMPTY = resolve from the catalog's declared
  // PRIMARY KEY; FAILED_PRECONDITION when the table declares none.
  repeated string match_columns = 4;
  bool merge = 5;
  optional string min_ulid = 6;
}

{
  "db_id": "acme--kb-prod",
  "table": "users",
  "rows": {
    "typed": {
      "columns": [
        { "name": "id",    "type": { "code": "TYPE_CODE_INT64" } },
        { "name": "email", "type": { "code": "TYPE_CODE_STRING" } },
        { "name": "tier",  "type": { "code": "TYPE_CODE_STRING" } }
      ],
      "rows": [
        { "values": [{ "int64_value": "101" }, { "string_value": "[email protected]" }, { "string_value": "pro" }] },
        { "values": [{ "int64_value": "102" }, { "string_value": "[email protected]" }, { "string_value": "free" }] }
      ]
    }
  }
}

UPSERT INTO users (id, email, tier) VALUES
  (101, '[email protected]', 'pro'),
  (102, '[email protected]', 'free');
 
-- equivalently
INSERT INTO users (id, email, tier) VALUES (101, '[email protected]', 'pro')
ON CONFLICT DO UPDATE SET email = EXCLUDED.email, tier = EXCLUDED.tier;

Response

message WriteAck {
  string key = 1;
  // Durable in the WAL (always true on OK; failures are gRPC status).
  bool wal_written = 2;
  // The minted ordering token — the caller's next session watermark.
  string wal_ulid = 3;
}

WriteAck has no rows_written, no version, and no pending_drain. Those fields belonged to a response shape that no longer exists. What you get back is durability (wal_written) and an ordering token (wal_ulid). The number of rows you sent is the number you sent.

Key facts:

• match_columns empty resolves to the table’s declared PRIMARY KEY. A table that declares none fails FAILED_PRECONDITION — there is nothing to match on.
• merge: true is a partial-column merge: columns absent from a row stay untouched. merge: false (the default) replaces the whole row.
• Read-your-writes is a client-side convention, because the plane is stateless. Carry wal_ulid into your next read’s min_ulid; the plane then guarantees the read observes at least that write, or fails over to the S3 WAL. Setting min_ulid forces the STRONG read path — the eventual fast paths do not thread the watermark and would silently ignore it.
• Rows dropped by schema validation are dead-lettered, not silently discarded. GetDatabaseStats.rejected_rows (and TableStats.rejected_rows) is where they surface — see Schema → Database stats.

WriteStream

The bulk / CDC channel. The client streams op-tagged batches — upserts and deletes interleaved, order preserved — and receives one ack per batch carrying progress, backpressure and the session watermark. It is not called “UpsertStream” for a reason: a CDC feed has to carry tombstones in order.

rpc WriteStream(stream WriteStreamRequest) returns (stream WriteStreamAck);
 
// First message MUST be `header`; every later message is one op-tagged
// batch. Each batch is atomic (all-or-nothing) and acked individually.
message WriteStreamRequest {
  oneof msg {
    WriteStreamHeader header = 1;
    UpsertBatch upsert = 2;
    DeleteBatch delete = 3;
  }
}
 
message WriteStreamHeader {
  string db_id = 1;
  string table = 2;
  repeated string match_columns = 3;
}
 
message UpsertBatch { RowSet rows = 1; bool merge = 2; }
message DeleteBatch { RowSet keys = 1; }
 
// One ack per DATA batch, in order — the header is not acked.
message WriteStreamAck {
  uint64 batch_seq = 1;                // 1-based
  uint32 rows_written = 2;
  string max_ulid = 3;                 // the session watermark
}

Commit

The transaction primitive: an atomic multi-row commit to one (db, table). It is stateless — no server session, no BEGIN, a single-shot commit.

rpc Commit(CommitRequest) returns (CommitResponse);
 
message CommitRequest {
  string db_id = 1;
  string table = 2;                    // the SINGLE target table (v1)
  repeated Mutation mutations = 3;     // order preserved, upserts + deletes mixed
  optional string min_ulid = 4;
}
 
message Mutation {
  oneof mutation {
    UpsertMutation upsert = 1;
    DeleteMutation delete = 2;
  }
}
 
message CommitResponse {
  string commit_ulid = 1;              // one commit = one WAL blob = one S3 PUT
  uint64 rows_written = 2;
  string max_wal_ulid = 3;
}

All-or-nothing, structurally. Every mutation encodes into one WAL segment riding one S3 PUT, and object PUT is atomic — so a validation error in any mutation fails the whole commit with nothing written. Rollback is implicit; there is nothing to roll back.

v1 is single-table (the WAL blob is per-table). Naming a second table in one commit returns INVALID_ARGUMENT pointing at the cross-table follow-up.

For an interactive transaction that spans separate round trips, use the Postgres wire — the adapter buffers the block client-side and flushes it as one atomic commit. A bare BEGIN with no COMMIT in the same call is refused by the stateless plane.

See also

• Writing rows — the Delete RPC and the INSERT / UPDATE / DELETE / MERGE statements
• Query — where min_ulid is consumed
• Maintenance → Compact — drain the WAL into Delta
• CLI Guide — dodil data table upsert