googleapis / googleapis/google-cloud-go
spanner: allow to bind spanner.Statement to a placeholder
- Dominant language
- Go
- Stars
- 4.5k
- Forks
- 1.6k
- Avg merge
- 1d 13h
- Merged PRs (30d)
- 109
Description
**Is your feature request related to a problem? Please describe.**
Application modularization encourages applications to keep data access logic for tables isolated from each other. This is normally good for maintainability, but has the negative consequence of encouraging to fetch resultsets just to be then sent as arguments to other queries, forcing needless roundtrips that lead to high latencies and resource consumption.
It would instead be ideal if users had ways to transparently compose complex queries out of building blocks managed by each data access module.
**Describe the solution you'd like**
A potential solution could be to allow `spanner.Statement`s parameters to be `spanner.Statement`s, e.g.
```go
spanner.Statement{
SQL: "DELETE FROM table1 WHERE id IN @ids",
Params: map[string]any{
"ids": spanner.Statement{
SQL: "SELECT id FROM table2 WHERE parent_id IN @ids",
Params: map[string]any{
"ids": []int{ /* ... */ },
},
},
},
}
```
that would thus execute as
```go
spanner.Statement{
SQL: `DELETE FROM table1 WHERE id IN (
SELECT id FROM table2 WHERE parent_id IN @ids
)`,
Params: map[string]any{
"ids": []int{ /* ... */ },
},
}
```
This will allow the data access logic for a table to return a resultset as a query, instead of as an actual material resultset, thereby allowing modularization to be maintained without forcing the needless materialization of datasets. (in the case above, the statement bound to placeholder `ids` would be returned by the data access module for `table2`, while the outer statement would be returned by the access module for table `table1`)
The only tricky bit, AFAICT, would be handling the case of duplicated placeholder names in the bound statements. In addition to the case above there could also be something like:
```go
spanner.Statement{
SQL: "DELETE FROM table1 WHERE foo IN @foos AND id IN @ids",
Params: map[string]any{
"ids": []string{ /* ... */ },
"foos": spanner.Statement{
SQL: "SELECT id FROM table2 WHERE parent_id IN @ids",
Params: map[string]any{
"ids": []int{ /* ... */ },
},
},
},
}
```
that would need to be executed as something like
```go
spanner.Statement{
SQL: `DELETE FROM table1 WHERE foo in (
SELECT id FROM table2 WHERE parent_id IN @ids$1
) AND id IN @ids`,
Params: map[string]any{
"ids": []string{ /* ... */ },
"ids$1": []int{ /* ... */ }, // automatically renamed to handle the conflict
},
}
```
In a sense, this would allow to cleanly compose complex queries out of simpler ones, similar to CTEs.
The above could AFAICT be implemented directly in the clients, without real support needed on the spanner side in the form of new APIs or capabilities. The only potential effect on spanner AFAICT may be that of stressing the query planner, as this may lead applications to send in more complex queries.
An alternative, that would cover both this requirement as well as cover many more out of scope in this request, would be to support some form of shareable cursor (or temporary table), and allow to pass a handle to that cursor/table in a placeholder. This approach would require significant new functionality on the spanner side.
Contributor guide
Assessment
This issue has not been assessed yet.