uptrace
feat: add support for scanning slices of sql.Scanner structs
I often find myself needing to select a single column of IDs. However in my project all IDs are of type ULID which bun interprets as a model struct rather than a simple value. Looking at the _newModel function, I can see that time.Time is the only exception to this rule.
I'd like to propose that any slice of structs that implement sql.Scanner should be treated as a slice of values, not models. This already works for single values and slices of built-in types.
In other words, I'd like the ability to do this:
var ids []ulid.ULID
err := s.db.NewSelect().Model((*model.User)(nil)).Column("id").Scan(ctx, &ids)
This PR adds support for this simple scenario by checking for sql.Scanner in addition to time.Time. It also adds a unit test in the internal/dbtest package to validate.
After reflection, the kind of ULID should be Array([16]byte) rather than Struct.
The example I constructed manually works.
ids := make([]ulid.ULID, 0)
err := db.NewSelect().Model((*User)(nil)).Column("ulid").Scan(ctx, &ids)
if err != nil {
panic(err)
}
fmt.Println(ids)
You're right, it does work when using ulid.ULID, however in my particular case I'm using a custom struct with ULID embedded.
type UID struct{ ulid.ULID }
ids := make([]UID, 0)
if err := s.db.NewSelect().Model((*model.User)(nil)).Column("id").Scan(ctx, &ids); err != nil {
panic(err)
}
fmt.Println(ids) // succeeds but all ids are zero
My UID type implicitly implements sql.Scanner but isn't directly convertible from [16]byte.
@NathanBaulch I’d like to first clarify the specific situation — would you mind trying to reproduce the issue using the latest version?
In my local environment, using a type alias works, but when using embed, it throws an error because ulid.ULID is actually a []byte.
type ID ulid.ULID
func (id *ID) Scan(src any) error {
uid := (*ulid.ULID)(id)
return uid.Scan(src)
}
var _ sql.Scanner = (*ID)(nil)
[bun] 17:00:10.341 SELECT 11µs SELECT "user"."ulid" FROM "users" AS "user"
[[1 105 209 223 144 144 0 0 0 0 0 0 0 0 0 0] [1 105 209 223 144 144 0 0 0 0 0 0 0 0 0 0]]
type ID struct {
ulid.ULID
}
var _ sql.Scanner = (*ID)(nil)
[bun] 16:57:31.790 SELECT 9µs SELECT "user"."ulid" FROM "users" AS "user" *fmt.wrapError: sql: Scan error on column index 0, name "ulid": bun: ID does not have column "ulid"
panic: sql: Scan error on column index 0, name "ulid": bun: ID does not have column "ulid"
type ID struct {
ULID ulid.ULID `bun:"ulid"`
}
func (id *ID) Scan(src any) error {
return id.ULID.Scan(src)
}
var _ sql.Scanner = (*ID)(nil)
[bun] 16:57:09.700 SELECT 6µs SELECT "user"."ulid" FROM "users" AS "user"
[{01D78XZ44G0000000000000000} {01D78XZ44G0000000000000000}]
I don't get any errors, only empty (zero) values. I've tested against v1.2.15. I can't easily test against the latest master because of the breaking schema.Formatter rename.
Here's a fresh example with no references to my model:
type UID struct{ ulid.ULID }
ids := make([]UID, 0)
err := db.NewSelect().ColumnExpr(`'\x0198a84c62c66fa11f7d332e78de72dc'::bytea`).Scan(ctx, &ids)
fmt.Println(err) // nil
fmt.Println(ids) // [00000000000000000000000000]
I should point out that scanning into a single value works:
type UID struct{ ulid.ULID }
id := UID{}
err := db.NewSelect().ColumnExpr(`'\x0198a84c62c66fa11f7d332e78de72dc'::bytea`).Scan(ctx, &id)
fmt.Println(err) // nil
fmt.Println(id) // 01K2M4RRP6DYGHYZ9K5SWDWWPW
The goal of this PR is to get slices of my ID struct working just like single values already do.
I switched to v1.2.15 and added the relevant code based on example/basic.
The result is still that IDAlias and IDField work, but IDEmbed throws an error bun: IDEmbed does not have column "ulid"
That’s consistent with my direct understanding of the related code. Would you mind running it locally to see if any zero values occur?
example/basic/main.go
package main
import (
"context"
"database/sql"
"fmt"
"github.com/oklog/ulid/v2"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect/sqlitedialect"
"github.com/uptrace/bun/driver/sqliteshim"
"github.com/uptrace/bun/extra/bundebug"
)
type IDAlias ulid.ULID
func (id *IDAlias) Scan(src any) error {
uid := (*ulid.ULID)(id)
return uid.Scan(src)
}
type IDEmbed struct {
ulid.ULID
}
type IDField struct {
ULID ulid.ULID `bun:"ulid"`
}
func (id *IDField) Scan(src any) error {
return id.ULID.Scan(src)
}
var _ sql.Scanner = (*IDAlias)(nil)
var _ sql.Scanner = (*IDEmbed)(nil)
var _ sql.Scanner = (*IDField)(nil)
func main() {
ctx := context.Background()
sqlite, err := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
if err != nil {
panic(err)
}
sqlite.SetMaxOpenConns(1)
db := bun.NewDB(sqlite, sqlitedialect.New())
db.AddQueryHook(bundebug.NewQueryHook(
bundebug.WithVerbose(true),
bundebug.FromEnv("BUNDEBUG"),
))
if err := resetSchema(ctx, db); err != nil {
panic(err)
}
// Select all users.
users := make([]User, 0)
if err := db.NewSelect().Model(&users).OrderExpr("id ASC").Scan(ctx); err != nil {
panic(err)
}
fmt.Printf("all users: %v\n\n", users)
// Select one user by primary key.
user1 := new(User)
if err := db.NewSelect().Model(user1).Where("id = ?", 1).Scan(ctx); err != nil {
panic(err)
}
fmt.Printf("user1: %v\n\n", user1)
// Select a story and the associated author in a single query.
story := new(Story)
if err := db.NewSelect().
Model(story).
Relation("Author").
Limit(1).
Scan(ctx); err != nil {
panic(err)
}
fmt.Printf("story and the author: %v\n\n", story)
// Select a user into a map.
var m map[string]interface{}
if err := db.NewSelect().
Model((*User)(nil)).
Limit(1).
Scan(ctx, &m); err != nil {
panic(err)
}
fmt.Printf("user map: %v\n\n", m)
// Select all users scanning each column into a separate slice.
var ids []int64
var names []string
if err := db.NewSelect().
ColumnExpr("id, name").
Model((*User)(nil)).
OrderExpr("id ASC").
Scan(ctx, &ids, &names); err != nil {
panic(err)
}
fmt.Printf("users columns: %v %v\n\n", ids, names)
{
ids := make([]IDAlias, 0)
err := db.NewSelect().Model((*User)(nil)).Column("ulid").Scan(ctx, &ids)
if err != nil {
panic(err)
}
fmt.Println("scan into alias", ids)
}
{
ids := make([]IDField, 0)
err := db.NewSelect().Model((*User)(nil)).Column("ulid").Scan(ctx, &ids)
if err != nil {
panic(err)
}
fmt.Println("scan into struct field", ids)
}
{
ids := make([]IDEmbed, 0)
err := db.NewSelect().Model((*User)(nil)).Column("ulid").Scan(ctx, &ids)
if err != nil {
panic(err)
}
fmt.Println("scan into embed", ids)
}
}
type User struct {
ID int64 `bun:",pk,autoincrement"`
ULID ulid.ULID `bun:",default:'01D78XZ44G0000000000000000'"`
Name string
Emails []string
}
func (u User) String() string {
return fmt.Sprintf("User<%d %s %v>", u.ID, u.Name, u.Emails)
}
type Story struct {
ID int64 `bun:",pk,autoincrement"`
Title string
AuthorID int64
Author *User `bun:"rel:belongs-to,join:author_id=id"`
}
func resetSchema(ctx context.Context, db *bun.DB) error {
if err := db.ResetModel(ctx, (*User)(nil), (*Story)(nil)); err != nil {
return err
}
users := []User{
{
Name: "admin",
Emails: []string{"admin1@admin", "admin2@admin"},
},
{
Name: "root",
Emails: []string{"root1@root", "root2@root"},
},
}
if _, err := db.NewInsert().Model(&users).Exec(ctx); err != nil {
return err
}
stories := []Story{
{
Title: "Cool story",
AuthorID: users[0].ID,
},
}
if _, err := db.NewInsert().Model(&stories).Exec(ctx); err != nil {
return err
}
return nil
}
git diff in go.mod
diff --git a/example/basic/go.mod b/example/basic/go.mod
index a243b82..c94afaa 100644
--- a/example/basic/go.mod
+++ b/example/basic/go.mod
@@ -13,6 +13,7 @@ replace github.com/uptrace/bun/dialect/sqlitedialect => ../../dialect/sqlitedial
replace github.com/uptrace/bun/driver/sqliteshim => ../../driver/sqliteshim
require (
+ github.com/oklog/ulid/v2 v2.1.1
github.com/uptrace/bun v1.2.15
github.com/uptrace/bun/dialect/sqlitedialect v1.2.15
github.com/uptrace/bun/driver/sqliteshim v1.2.15
diff --git a/example/basic/go.sum b/example/basic/go.sum
index 8e1e114..48da257 100644
--- a/example/basic/go.sum
+++ b/example/basic/go.sum
@@ -18,6 +18,9 @@ github.com/mattn/go-sqlite3 v1.14.28 h1:ThEiQrnbtumT+QMknw63Befp/ce/nUPgBPMlRFEu
github.com/mattn/go-sqlite3 v1.14.28/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y=
github.com/ncruces/go-strftime v0.1.9 h1:bY0MQC28UADQmHmaF5dgpLmImcShSi2kHU9XLdhx/f4=
github.com/ncruces/go-strftime v0.1.9/go.mod h1:Fwc5htZGVVkseilnfgOVb9mKy6w1naJmn9CehxcKcls=
+github.com/oklog/ulid/v2 v2.1.1 h1:suPZ4ARWLOJLegGFiZZ1dFAkqzhMjL3J1TzI+5wHz8s=
+github.com/oklog/ulid/v2 v2.1.1/go.mod h1:rcEKHmBBKfef9DhnvX7y1HZBYxjXb0cP5ExxNsTT1QQ=
+github.com/pborman/getopt v0.0.0-20170112200414-7148bc3a4c30/go.mod h1:85jBQOZwpVEaDAr341tbn15RS4fCAsIst0qp7i8ex1o=
github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
github.com/puzpuzpuz/xsync/v3 v3.5.1 h1:GJYJZwO6IdxN/IKbneznS6yPkVC+c3zyY/j19c++5Fg=
Yes, I see exactly the same behavior. There is obviously some difference between SQLite (your example program) and PostgreSQL (what I'm using).
But either way, my patch in this PR fixes the issue for both databases, allowing arrays of sql.Scanner structs to be handled correctly.
When I switched to PostgreSQL, the error I encountered was still that the embed field was not recognized, rather than it being zero value.
dsn := "postgres://j2gg0s:@localhost:5432/postgres?sslmode=disable"
sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))
db := bun.NewDB(sqldb, pgdialect.New())
db.AddQueryHook(bundebug.NewQueryHook(
bundebug.WithVerbose(true),
bundebug.FromEnv("BUNDEBUG"),
))
type User struct {
ID int64 `bun:",pk,autoincrement"`
ULID ulid.ULID `bun:"type:varchar,default:'01K47E3YF67THMBD2WHFV3PB5J'"`
Name string
Emails []string
}
Sorry for the many rounds of communication. I’m a bit unfamiliar with Bun’s code recently, so I’d prefer to reproduce the issue before making any judgment.
OK, I've created a minimal example that builds upon your example here: https://gist.github.com/NathanBaulch/d7e88b6b8586a59d628bbdfb87dc65f5
It uses your three types IDAlias, IDEmbed and IDField against a Pg database.
Test case 3 (IDEmbed slice) fails in the current master branch with the "does not have column" error you've probably been seeing, and succeeds in the scanner-slice branch that this PR is based on.
I believe the fact that test cases 1 (IDAlias slice), 4 (IDEmbed single) and 5 (IDAlias slice) all succeed in master is a strong indication that test case 3 should be supported for consistency.
This pull request has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this pr will be closed. Please feel free to give a status update now, ping for review, when it's ready. Thank you for your contributions!
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
This pull request has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this pr will be closed. Please feel free to give a status update now, ping for review, when it's ready. Thank you for your contributions!
This pull request has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this pr will be closed. Please feel free to give a status update now, ping for review, when it's ready. Thank you for your contributions!