proposal: database/sql: add methods to scan an entire row into one value

[jba]

Edited: struct field names are matched to columns case-sensitively.
Edited: untouched storage is ignored rather than zeroed.

---

I propose adding the method

ScanRow(dest any) error

to the Row and Rows types. ScanRow attempts to populate dest with all the columns of the current row. dest can be a pointer to an array, slice, map or struct.

Motivation

ScanRow makes it more convenient to populate a row from a struct. Evidence that this is a desirable feature comes from the github.com/jmoiron/sqlx package, which has 9,800 importers, about 1,000 forks and about 14,000 GitHub stars. (To be fair, sqlx provides several other enhancements to database/sql as well.) The ScanRow method brings database/sql closer to parity with encoding/json, whose ability to unmarshal into structs and other data types is widely used.

Another motivation comes from the likely addition of iterators to the language. Without something like ScanRow, an iterator over a DB query would have to return a Row or Rows, since the Scan method is variadic. An iterator like that still improves on using a Rows directly because it makes error-handling more explicit and always calls Close. But we could do better with ScanRow, because the iterator could deliver a single value holding the entire row:

type Product struct {
    Name string
    Quantity int
 }

func processProducts() {
    for p, err := range sql.Query[Product](ctx, db, "SELECT * FROM products") {
       if err != nil {...}
       // use p
    }
}

This proposal doesn't include that iterator; I show it merely for motivation.

Details

ScanRow acts as if each part of its argument were passed to Scan.

If the value is an array pointer, successive array elements are scanned from the corresponding columns. Excess columns are dropped. Excess array elements are left untouched.

If the value is a slice pointer, the slice is resized to the number of columns and slice elements are scanned from the corresponding columns.

If the value is a map pointer, the underlying key type must be string. For each column, a map entry is created whose key is the column name and whose value is scanned from the column. Unnamed columns are assigned unique keys of the form _%d for integers 0, 1, .... Other entries in the map are left untouched.

If the value is a struct pointer, its exported visible fields are matched by name to the column names and the field values are scanned from the corresponding columns. The name matching is done as follows:

1. If the field has a struct tag with key "sql", its value is the column name.
2. Otherwise, the column name is matched to the field name case-sensitively.

Unassigned columns are dropped. Unassigned fields are left untouched.

[jba]

Maybe we should be more strict about struct name matching to prevent misspelling errors. Unfortunately that interferes with the ability to use the same struct for different partial views of its table.

[doggedOwl]

Otherwise, the column name is matched to the field name case-insensitively.

Maybe a snake_case match would be better as a default.
When field names are composed of more words most db conventions use snake_case for column names.
FieldName->field_name

Also most projects i have seen in use already use 'db' Key in struct tags instead of 'sql'. If this could use the same there would be less work to convert existing projects.

Edit: meant snake_case (camelCase columns would already match with the case-insentive match)

[Sinea]

@doggedOwl db is kind of generic.sql is more clear. And unpacking columns to fields by a naming convention doesn't seem like the "go" way of doing things.

[PaluMacil]

Case insensitivity might be problematic. If you have a database collation set to case sensitive, I believe SQL Server and MySQL will consider column names with two casings to be different columns. This is not all databases, and not all usages of the databases it affects. Also, you can already alias each field in your select statement, or you can use tags as an exact match. To be specific. I don't think trying to be clever about case insensitivity would be a good idea for databases that act different from each other.

[PaluMacil]

@doggedOwl db is kind of generic.sql is more clear. And unpacking columns to fields by a naming convention doesn't seem like the "go" way of doing things.

Another benefit of "sql" as the tag is matching the package it comes from. That seems both concise and intuitive.

[Merovius]

Personally, I'd far prefer scanning into a struct to scan the columns into fields in order - or at least having the option to. I don't particularly like the mechanism of using struct tags to control unmarshaling anyways and generally prefer my exported types to be tag-free. This would also allow scanning unnamed columns into structs.

Even if the iterator is not part of the proposal, given that it's part of the rationale, I'd also mention that I find the semantics of returning an error as an iteration value a bit confusing. It is not enforced that iteration stops if an error is returned. Arguably, that's a good thing - it might be, that a specific row can't be scanned into a value, but for some use cases, it might still make sense to iterate over the rest of the query. On the other hand, the ambiguity will potentially have people write incorrect code, if they assume one semantic or the other.

[benhoyt]

I'd far prefer scanning into a struct to scan the columns into fields in order

@Merovius Isn't that rather error-prone? If you change the order of fields in the select, but forget in the struct (which might be defined a ways away), it'll break.

I don't particularly like the mechanism of using struct tags to control unmarshaling anyways and generally prefer my exported types to be tag-free. This would also allow scanning unnamed columns into structs.

Out of interest, how do you unmarshal JSON with this approach? Or do you unmarshal into a non-exported struct with tags, and then copy to an exported struct without tags?

In terms of the proposal itself, I really like it -- I've wanted this for many years (and have used sqlx too). Code like rows.Scan(&item.ID, &item.Name, &item.TimeCreated, ...) gets tedious really fast (and is error-prone for the same ordering reason as above).

@jba For structs, will embedded fields be supported, like they are in sqlx? What will ScanRow do in the face of ambiguous column names, for queries like SELECT 1 AS a, 2 AS a or SELECT a.id, a.name, b.id, b.name FROM foos AS a JOIN foos AS b ON a.parent = b.id?

[Merovius]

@benhoyt I don't know if it's error-prone. I don't necessarily "buy" theoretical arguments about error-proneness. But AIUI, sqlx works that way already, so maybe the maintainers or users of that package can provide some data.

As for how I unmarshal JSON without tags: I declare an UnmarshalJSON method that unmarshals into an unexported struct type and then either converts it to the tagless one or copies over the fields I'm interested in. That's a bit of added boilerplate, but mostly because the encoding/json package relies on struct tags to begin with - I'd prefer an API that allows you to pass a function doing what the struct tags currently do. How that might look is off-topic here, IMO.

[derekperkins]

If we're going to add ScanRow(dest any) error, I would extend the proposal to also add ScanRows(dest any) error. If we can scan one row, it's not much more effort to scan all of them, appending to the provided slice, which would reduce a lot of boilerplate. The corollary in sqlx is Select, where ScanRow maps to Get, and just like encoding/json can Unmarshal a single object or an array of objects, this follows the same pattern.

[jba]

@Merovius Unless they are doing fine-grained memory optimization, people think of structs as unordered sets of fields. As does the language: re-ordering fields is never a breaking change.

As for iterators, there doesn't seem to be any appetite for error handling over on #61405, so I think having error as the second return type is the best we're going to get.

[jba]

@benhoyt Embedded fields will be supported. The implementation will use reflect.VisibleFields.

Personally, I think ScanRow should fail if there are ambiguous column names, but that's open for discussion.