spanner: add better support for reading a row that may not exist

[husam-e]

Is your feature request related to a problem? Please describe.
ReadRow documentation states that it returns a NotFound error if the row is not found.
However, we've discovered that there are other scenarios where NotFound can be returned that aren't documented, such as when a column is not found. This was difficult to find given we had added special logic for handling NotFound to return nil, nil instead. This thus makes handling the typical code path of doing a "FetchOne", then checking if it exists, extra hard (as not only is it returning an error instead of just nil, but the error is ambiguous).

Describe the solution you'd like
Ideally, ReadRow would not fail simply on not finding the entity. It would fail when there's a real error (not finding the row is arguably nominal and typically handled by returning null or equivalent). A variant like ReadRowOrNil would be ideal.

Otherwise, provide some robust way to determine when the row is not found vs when there's an actual error.
Another thing to consider is whether a schema issue like a missing column should be treated as NotFound, as that is not intuitive to me, since my assumption is that the "NotFound" pertains to the Row, not some underlying schema detail.

Describe alternatives you've considered
Matching on the string message e.g. "row not found", which is not ideal and preferable to avoid.

Additional context
NA

[rahul2393]

Hello @husam-e
Current behaviour of ReadRow is correct and it returns error in case the row does not exist as per the documentation.

If you want to ignore the error in case row does not exist use Read or ReadWithOptions example https://github.com/GoogleCloudPlatform/golang-samples/blob/main/spanner/spanner_snippets/spanner/spanner_read_data.go#L40-L52

Please let me know if you want to discuss more.

ReadRow as of now returns errors with correct descriptions, any server side errors are surfaced "as-is",
example:

• Table Not Found(Server returned error)
  spanner: code = "NotFound", desc = "Table not found: {INVALID_TABLE}"

• Column Not found(Server returned error)
  spanner: code = "NotFound", desc = "Column not found in table {CORRECT_TABLE}: {INVALID_TABLE}"

It will return CUSTOM error only in 1 scenario where row does not exist with proper message like
spanner: code = "NotFound", desc = "row not found(Table: {TEST_TABLE}, PrimaryKey: ("{KEY}"))"

[husam-e]

Hi @rahul2393 , thanks for getting back to me.

Using those lower level APIs with an iterator is a lot less ergonomic and more error prone for a simple maybe-read-one-row use case, as it has more boilerplate code.

I'm hoping for a higher level API providing a straightforward way to reliably query for one row and know when it doesn't exist. It's a very common use case, so I think all users will benefit.

What are your thoughts on adding such an API to the sdk? If I find time I can perhaps create a PR to demonstrate what I'm thinking.

[rahul2393]

We do also support GORM which exposes higher level APIs example https://gorm.io/docs/query.html#Retrieving-a-single-object
Reference: https://github.com/googleapis/go-gorm-spanner/tags.

But please help with creating a PR to get more understanding.
Thanks

[husam-e]

@rahul2393 I created a draft PR (#10358) for illustration purposes of 2 options, lmk wyt