Writing complex parameterized queries

A parameterized query is a query that is built dynamically based on incoming request params. Building complex database queries can be challenging but you can achieve better results by following the recommendations presented in this article.

Divide and conquer

The first and the main recommendation is to split the whole process into isolated steps:

Parsing request params

The first think you need to do is to create a data structure that will hold incoming params, for example:

1. type ArticleFilter struct {
2. CategoryID int64
3. Search       string
4. Page       int
5. }

And a factory method that will parse the params from an http.Request or JSON payload:

1. func articleFilterFromRequest(req *http.Request) (*ArticleFilter, error) {
2. query := req.URL.Query()

4. f := new(ArticleFilter)
5. f.Search = query.Get("search")

7. categoryID, err := strconv.ParseInt(query.Get("category_id"), 10, 64)
8. if err != nil {
9. return nil, err
10. }
11. f.CategoryID = categoryID

13. page, err := strconv.Atoi(query.Get("page"))
14. if err != nil {
15. return nil, err
16. }
17. f.Page = page

19. return f, nil
20. }

Params validation

The purpose of this step is to ensure you have enough data to build a query or to set default values:

1. func (f *ArticleFilter) Validate() error {
2. if f.CategoryID == 0 {
3. return errors.New("category id is required")
4. }
5. if f.Page == 0 {
6. f.Page = 1
7. } else f.Page > 1000 {
8. return errors.New("you can't paginate past page #1000")
9. }
10. return nil
11. }

Query generation

At this step you have enough data to build a query using Bun API. It is best to keep all query generation logic in a single method so it can be easily followed.

1. func articleFilterQuery(q *bun.SelectQuery, f *ArticleFilter) (*bun.SelectQuery, error) {
2. q = q.Where("category_id = ?", f.CategoryID).
3. Limit(10).
4. Offset(10 * (f.Page - 1))
5. if f.Search != "" {
6. q = q.Where("title LIKE ?", "%"+f.Search+"%")
7. }
8. return q, nil
9. }

Query execution

Lastly, you need to execute the generated query and, optionally, do some post-processing. The end result may look like this:

1. func handler(w http.ResponseWriter, req *http.Request) {
2. f, err := articleFilterFromRequest(req)
3. if err != nil {
4. panic(err)
5. }

7. if err := f.Validate(); err != nil {
8. panic(err)
9. }

11. var articles []Article

13. q, err := articleFilterQuery(db.NewSelect().Model(&articles), f)
14. if err != nil {
15. panic(err)
16. }

18. if err := q.Scan(req.Context()); err != nil {
19. panic(err)
20. }

22. if err := json.NewEncoder(w).Encode(map[string]interface{}{
23. "articles": articles,
24. }); err != nil {
25. panic(err)
26. }
27. }