I’m developing a project and wanted to use sqlc. I really like this tool but I can’t find examples where it is possible to make something more advanced than select/insert/update/delete queries.

I want to filter query (with WHERE clause), pass pagination parameters and do PATCH queries.
This how I make filtering and patching with sqlx:

1. Filtering

    ...
    var args []string
    var vals []interface{}
    count := 1

    if filter.Name != nil {
        args = append(args, fmt.Sprintf("name=$%d", count))
        vals = append(vals, &filter.Name)
        count++
    }
    if filter.OrgType != nil {
        args = append(args, fmt.Sprintf("org_type=$%d", count))
        vals = append(vals, &filter.OrgType)
        count++
    }
    if filter.DeliveryArea != nil {
        args = append(args, fmt.Sprintf("delivery_area=$%d", count))
        vals = append(vals, &filter.DeliveryArea)
        count++
    }
    if filter.Status != nil {
        args = append(args, fmt.Sprintf("status=$%d", count))
        vals = append(vals, &filter.Status)
        count++
    }

    query := "select * from contractors"
    if count != 1 {
        query = fmt.Sprintf("%s where %s", query, strings.Join(args, " and "))
    }
    // query might be 
    // select * from contractors -- no filters
    // select * from contractors where name=$1
    // select * from contractors where name=$1 and org_type=$2
    // select * from contractors where name=$1 and org_type=$2 and delivery_area=$3
    // select * from contractors where name=$1 and org_type=$2 and delivery_area=$3 and status=$4
    ...
    // adding limit and offset to query

2. Patching

    ...
    var args []string
    var vals []interface{}
    count := 1
    if payload.Name != nil {
        args = append(args, fmt.Sprintf("name=$%d", count))
        vals = append(vals, payload.Name)
        count++
    }
    if payload.ContractNum != nil {
        args = append(args, fmt.Sprintf("contract_num=$%d", count))
        vals = append(vals, payload.ContractNum)
        count++
    }
    if payload.DeliveryArea != nil {
        args = append(args, fmt.Sprintf("delivery_area=$%d", count))
        vals = append(vals, payload.DeliveryArea)
        count++
    }

    if count == 1 {
        log.Ctx(ctx).Error().Msg("nothing to update")
        return fmt.Errorf("nothing to update")
    }

    query := fmt.Sprintf("update contractors set %s where id=$%d", strings.Join(args, ", "), id)
    // same with filtering query might be (if nothing to update - throws error)
    // update contractors set name=$1 where id=$2
    // update contractors set name=$1, contract_num=$2 where id=$3
    // update contractors set name=$1, contract_num=$2, delivery_area=$3 where id=$4

    ...

As you can see this style is quite tedious to write, test and if several entities require filtering and patching the same way it’ll be just copy-paste mess with little changes that I might miss. I would like to hide this under sqlc code generation.