Improving sql queries and validation #
PGX v5 add generic support #
This allow us to connect structs and sql queries #
Step by step solution:
- Using generics pass type to the database layer
- Bacause of generics connection to database should be pass as function argument
- Amount of fields in structure should match amount of fields in select, so we use reflect to get structure fields
Need to note that our existing approach with interfaces does not work very well with generics. So we will have to review usage of database interfaces in the future
adapters/postgres.go:
func RetriveProfileByIDAny[T any](ctx context.Context, pg PostgresRepository, profileID int, result interface{}) error {
// using reflect to get list of fields
vals := reflect.ValueOf(result).Elem()
valsLen := vals.NumField()
fields := make([]string, valsLen)
for i := 0; i < valsLen; i++ {
jsonTag := vals.Type().Field(i).Tag.Get("json")
if jsonTag != "" {
fields[i] = jsonTag
}
}
// constructing sql
sql, args, err := sq.Select(strings.Join(fields, ",")).From("investment_profiles").
Where(sq.And{sq.Eq{"id": profileID}}).PlaceholderFormat(sq.Dollar).ToSql()
if err != nil {
resErr := errors.Wrapf(ErrSQLRequest, "%s: %s, %v", err.Error(), sql, args)
pg.log.Error().Err(resErr).Msg("RetriveProfileByIDAny error")
return resErr
}
// getting result to any structure
pg.log.Trace().Msgf("query: %s, %v", cleanSQL(sql), args)
rows, _ := pg.client.Query(context.Background(), sql, args...)
result, err = pgx.CollectOneRow(rows, pgx.RowToAddrOfStructByName[T])
if err != nil {
resErr := errors.Wrapf(err, "for id %d", profileID)
pg.log.Error().Err(resErr).Msg("RetriveProfileByIDAny error")
return resErr
}
return nil
}
app/app.go:
err := adapters.RetriveProfileByIDAny[validators.ProfileForEscrowAccount](
ctx,
app.repo.(adapters.PostgresRepository), // <--- problem here
data.ProfileID,
profile,
)
Future improvements #
Create auto-generated models lawyer #
We would like to improve our solution with: Creating an sql lawyer with build in functions:
SelectByID
functionSelectBy
functionInsert
functionUpdate
function
Creating an models lawyer with helpers to get,save: So it will be possible to do:
profile, err := UserProfile(ctx, app.repo, ProfileID).GetByID()
profile.SetKYCStatus(dto.KYCPending)
profile.save()
Move validation inside models lawyer:
- So we don’t have to do additional validation check in the app
Create set of functions to have more flexability:
SetXXX
to get XXX fieldGetXXX
to get XXX field
Create set of utils functions:
GetTableName
should return table nameDbFieldMap
should return map between table names and fields to eliminate reflect Inspired by LORE
And this call can be auto generated using database table schema