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 function
• SelectBy function
• Insert function
• Update 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 field
• GetXXX to get XXX field

Create set of utils functions:

• GetTableName should return table name
• DbFieldMap 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