Dynamic Select Queries

[BigBoulard]

Hi mates,

I wonder how I can handle dynamic fields in a select query.

For example, in a REST API, a request like this /GET /users?fields=id,first_name,last_name,phone,country would trigger a query like this select id, first_name, last_name, phone, country from user; and then return the appropriate data structure to create the JSON.

Similarly, a request like this /GET /users?fields=id,phone would trigger a query like this select id, phone from user; etc.

I can build the query like below but I don't know how to manage the Scan part cause usually the fields are known beforehand.

package main

import (
	"errors"
	"strings"
)

const (
	ErrInvalidField = "invalid_field"
)

func buildSelectQuery(fields []string) (string, error) {
	validFields := []string{"id", "first_name", "last_name", "phone", "country"}
	var validColumns []string

	for _, field := range fields {
		if contains(validFields, field) {
			validColumns = append(validColumns, field)
		} else {
			return "", errors.New(ErrInvalidField)
		}
	}

	if len(validColumns) == 0 {
		return "SELECT * FROM user;", nil
	}

	return "SELECT " + strings.Join(validColumns, ", ") + " FROM user;", nil
}

func contains(slice []string, item string) bool {
	for _, s := range slice {
		if s == item {
			return true
		}
	}
	return false
}

func main() {
	requiredFields := []string{"first_name", "last_name"}
	query, err := buildSelectQuery(requiredFields)
	if err != nil {
		println(err.Error())
	}
	println(query)
}

For selecting a single row, I used to do something like below but obviously this doesn't work as we don't know the requested fields beforehand...

import(
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgconn"
)

const(
   selectQ = "select id, first_name, last_name, phone, country from user"
)

type User struct{
    Id string `sql:"id"`
    FirstName string `sql:"first_name"`
    LastName string `sql:"last_name"`
    Phone string `sql:"phone"`
   // NOTE: I use pgtype.Text in case the field is NULL and needs to be marshaled
   // please advise if there's a better option
    Country pgtype.Text `sql:"country"` 
}

func (r *repository) GetUser(id string) (*User, error){
        user := &User{}
	if err := pgclient.GetSession().QueryRow(
		ctx,
		selectQ,
		id,
	).Scan(
		&user.Id,
		&user.FirstName,
		&user.LastName,
		&user.Phone,
		&user.Country,
	); err != nil {
           return nil, err
        }

   return user, nil
}

[jackc]

You probably want to look at the Rows.FieldDescriptions() and Rows.Values().

But if your ultimate goal is SQL you might want to look into building the JSON directly in PostgreSQL with functions like row_to_json and json_build_object. See https://www.postgresql.org/docs/current/functions-json.html.

[yyovkov]

Well, looks like FiledDescriptions is available only for Rows, when using .Query(), but when using .QueryRow can't get the names.