search

georgysavva / scany

Library for scanning data from a database into Go structs and more
MIT License
1.31k stars 68 forks source link

`sqlscan.ScanOne` seems to be mapping columns incorrectly #117

Closed jsumners closed 1 year ago

jsumners commented 1 year ago

Note: it's quite likely I'm doing something wrong and would welcome a pointer as to what that is.

I have created a reproduction for my issue at https://github.com/jsumners/go-experiments/tree/439bbc922c1552d2b1863eecbf0099e6a2ba4c20/scanny-test

The error I get is:

&fmt.wrapError{msg:"scanning: doing scan: scanFn: scany: scan row into struct fields: sql: Scan error on column index 4, name \"created_at\": input is not a date-time string: Atlanta", err:(*fmt.wrapError)(0x14000393500)}

This is due to scany trying to use the city column value from the database as the value for the DetailedAthlete.CreatedAt field.

go.mod ```go module scannytest go 1.21.1 require ( github.com/davecgh/go-spew v1.1.1 // indirect github.com/dustin/go-humanize v1.0.1 // indirect github.com/georgysavva/scany/v2 v2.0.0 // indirect github.com/google/uuid v1.3.0 // indirect github.com/jsumners/go-reggie v1.0.0-rc.1 // indirect github.com/jsumners/go-rfc3339 v1.0.0 // indirect github.com/kballard/go-shellquote v0.0.0-20180428030007-95032a82bc51 // indirect github.com/mattn/go-isatty v0.0.16 // indirect github.com/pmezard/go-difflib v1.0.0 // indirect github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect github.com/samber/mo v1.8.0 // indirect github.com/spf13/cast v1.5.0 // indirect github.com/stretchr/objx v0.5.0 // indirect github.com/stretchr/testify v1.8.4 // indirect golang.org/x/mod v0.3.0 // indirect golang.org/x/sys v0.0.0-20220811171246-fbc7d0a398ab // indirect golang.org/x/tools v0.0.0-20201124115921-2c860bdd6e78 // indirect golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1 // indirect gopkg.in/yaml.v3 v3.0.1 // indirect lukechampine.com/uint128 v1.2.0 // indirect modernc.org/cc/v3 v3.40.0 // indirect modernc.org/ccgo/v3 v3.16.13 // indirect modernc.org/libc v1.24.1 // indirect modernc.org/mathutil v1.5.0 // indirect modernc.org/memory v1.6.0 // indirect modernc.org/opt v0.1.3 // indirect modernc.org/sqlite v1.26.0 // indirect modernc.org/strutil v1.1.3 // indirect modernc.org/token v1.0.1 // indirect ) ```
main.go ```go package scannytest import ( "database/sql" "github.com/georgysavva/scany/v2/sqlscan" _ "modernc.org/sqlite" ) func InitDb() (*sql.DB, error) { db, err := sql.Open("sqlite", "file::memory:") if err != nil { return nil, err } db.Exec(schemaSql) return db, nil } func AddAthlete(db *sql.DB, athlete *DetailedAthlete) error { _, err := db.Exec( insertAthleteSql, sql.Named("athleteId", athlete.Id), sql.Named("bio", athlete.Bio), sql.Named("city", athlete.City), sql.Named("country", athlete.Country), sql.Named("createdAt", athlete.CreatedAt), sql.Named("firstName", athlete.FirstName), sql.Named("followerCount", athlete.FollowerCount), sql.Named("friendCount", athlete.FriendCount), sql.Named("ftp", athlete.FunctionalThresholdPower), sql.Named("lastName", athlete.LastName), sql.Named("measurementPreference", athlete.MeasurementPreference), sql.Named("premium", athlete.Premium), sql.Named("profile", athlete.Profile), sql.Named("profileMedium", athlete.ProfileMedium), sql.Named("sex", athlete.Sex), sql.Named("state", athlete.State), sql.Named("summit", athlete.Summit), sql.Named("updatedAt", athlete.UpdatedAt), sql.Named("weight", athlete.Weight), ) return err } func GetAthlete(db *sql.DB, athleteId AthleteIdentifier) (*DetailedAthlete, error) { rows, err := db.Query(selectAthleteSql, sql.Named("athleteId", athleteId)) if err != nil { return nil, err } var result DetailedAthlete err = sqlscan.ScanOne(&result, rows) if err != nil { return nil, err } return &result, nil } ```
main_test.go ```go package scannytest import ( "testing" "github.com/jsumners/go-rfc3339" "github.com/stretchr/testify/assert" "github.com/stretchr/testify/require" ) func Test_GetAthlete(t *testing.T) { db, err := InitDb() require.Nil(t, err) defer db.Close() dt, _ := rfc3339.NewDateTimeFromString("2023-10-08T09:00:00.000-04:00") expected := DetailedAthlete{ SummaryAthlete: SummaryAthlete{ MetaAthlete: MetaAthlete{Id: 123456}, Bio: "A test athlete.", City: "Atlanta", Country: "GA", CreatedAt: dt, FirstName: "John", LastName: "Doe", Premium: true, Profile: "https://example.com/profile.jpg", ProfileMedium: "https://example.com/profile_medium.jpg", ResourceState: 3, Sex: "M", State: "GA", Summit: true, UpdatedAt: dt, }, Bikes: nil, Clubs: nil, FollowerCount: 10, FriendCount: 10, FunctionalThresholdPower: 180, MeasurementPreference: "imperial", Shoes: nil, Weight: 165.0, } err = AddAthlete(db, &expected) require.Nil(t, err) found, err := GetAthlete(db, 123456) require.Nil(t, err) assert.Equal(t, expected, found) } ```
models.go ```go package scannytest import ( "github.com/jsumners/go-rfc3339" ) type AthleteIdentifier int64 type MetaAthlete struct { Id AthleteIdentifier `json:"id" db:"athlete_id"` } type SummaryAthlete struct { MetaAthlete Bio string `json:"bio"` City string `json:"city"` Country string `json:"country"` CreatedAt rfc3339.DateTime `json:"created_at"` FirstName string `json:"firstname"` LastName string `json:"lastname"` Premium bool `json:"premium"` Profile string `json:"profile"` ProfileMedium string `json:"profile_medium"` ResourceState int `json:"resource_state"` Sex string `json:"sex"` State string `json:"state"` Summit bool `json:"summit"` UpdatedAt rfc3339.DateTime `json:"updated_at"` } type DetailedAthlete struct { SummaryAthlete Bikes []any `json:"bikes"` Clubs []any `json:"clubs"` FollowerCount int `json:"follower_count"` FriendCount int `json:"friend_count"` FunctionalThresholdPower int `json:"ftp"` MeasurementPreference string `json:"measurement_preference"` Shoes []any `json:"shoes"` Weight float64 `json:"weight"` } ```
db_sql.go ```go package scannytest import ( _ "embed" ) //go:embed schema.sql var schemaSql string const insertAthleteSql = ` insert into athletes ( athlete_id, country, created_at, follower_count, functional_threshold_power, profile_medium, bio, city, first_name, friend_count, last_name, measurement_preference, premium, profile, sex, state, summit, updated_at, weight ) values ( @athleteId, @bio, @city, @country, @createdAt, @firstName, @followerCount, @friendCount, @ftp, @lastName, @measurementPreference, @premium, @profile, @profileMedium, @sex, @state, @summit, @updatedAt, @weight ) on conflict do update set bio = @bio, city = @city, country = @country, created_at = @createdAt, first_name = @firstName, follower_count = @followerCount, friend_count = @friendCount, functional_threshold_power = @ftp, last_name = @lastName, measurement_preference = @measurementPreference, premium = @premium, profile = @profile, profile_medium = @profileMedium, sex = @sex, state = @state, summit = @summit, updated_at = @updatedAt, weight = @weight ` const selectAthleteSql = ` select athlete_id, bio, city, country, created_at, first_name, follower_count, friend_count, functional_threshold_power, last_name, measurement_preference, premium, profile, profile_medium, sex, state, summit, updated_at, weight from athletes where athlete_id = @athleteId ` ```
schema.sql ```sql create table athletes ( id integer primary key, athlete_id integer, bio text, city text, country text, created_at text, first_name text, follower_count integer, friend_count integer, functional_threshold_power integer, last_name text, measurement_preference text, premium integer, profile text, profile_medium text, sex text, state text, summit integer, updated_at text, weight real ); create unique index athletes_athid on athletes (athlete_id); create index athletes_created on athletes (created_at); create index athletes_updated on athletes (updated_at); ```
jsumners commented 1 year ago

The insert statement was ordered incorrectly.