Crafting Idiomatic Go Query Builders: From GORM to Custom APIs

1. GORM, Layered Complexity and the ActiveRecord Pattern

Many Go developers rely on the GORM library for database interactions because it offers a comprehensive ORM with migrations, relations, and transactions. While GORM works well for simple CRUD use‑cases, adding conditional WHERE clauses based on URL parameters (e.g., search or after ) quickly becomes unwieldy.

<code>posts := make([]Post, 0)

search := r.URL.Query().Get("search")

db := gorm.Open("postgres", "...")

if search != "" {
    db = db.Where("title LIKE ?", "%"+search+"%")
}

db.Find(&posts)</code>

Extending the example to filter by a date after a given timestamp adds another conditional block, illustrating how the code starts to duplicate checks and grow in complexity.

<code>posts := make([]Post, 0)

search := r.URL.Query().Get("search")
after := r.URL.Query().Get("after")

db := gorm.Open("postgres", "...")

if search != "" {
    db = db.Where("title LIKE ?", "%"+search+"%")
}

if after != "" {
    db = db.Where("created_at > ?", after)
}

db.Find(&posts)</code>

2. Using the Standard database/sql Package with sqlx

The database/sql package combined with sqlx provides a lightweight way to map rows to structs, but it still requires manual string concatenation and argument handling for dynamic queries.

<code>posts := make([]Post, 0)

search := r.URL.Query().Get("search")
after := r.URL.Query().Get("after")

db := sqlx.Open("postgres", "...")

query := "SELECT * FROM posts"
args := make([]interface{}, 0)

if search != "" {
    query += " WHERE title LIKE ?"
    args = append(args, search)
}

if after != "" {
    if search != "" {
        query += " AND "
    } else {
        query += " WHERE "
    }
    query += "created_at > ?"
    args = append(args, after)
}

err := db.Select(&posts, sqlx.Rebind(query), args...)</code>

Although functional, this approach is verbose and hard to maintain as more conditions are added.

3. Leveraging squirrel for Fluent Query Construction

squirrel offers a more idiomatic, chainable API for building SQL queries. The example below shows how to assemble the same conditional logic using option structs.

<code>posts := make([]Post, 0)

search := r.URL.Query().Get("search")
after := r.URL.Query().Get("after")

eqs := make([]sq.Eq, 0)

if search != "" {
    eqs = append(eqs, sq.Like{"title", "%" + search + "%"})
}

if after != "" {
    eqs = append(eqs, sq.Gt{"created_at", after})
}

q := sq.Select("*").From("posts")
for _, eq := range eqs {
    q = q.Where(eq)
}

query, args, err := q.ToSql()
if err != nil { return }

err := db.Select(&posts, query, args...)</code>

While cleaner than raw string concatenation, the code still feels a bit repetitive.

4. Designing a Custom, Option‑Based Query Builder

The article proposes a lightweight, extensible query‑builder API that follows Go's functional‑option pattern. The core Query struct tracks the statement type, target table, selected columns, WHERE clauses, and bound arguments.

<code>type statement uint8

type Query struct {
    stmt  statement
    table []string
    cols  []string
    args  []interface{}
}

const (
    _select statement = iota
)
</code>

Option functions modify a Query instance and return the updated value, enabling composable construction of SELECT statements.

<code>type Option func(q Query) Query

func Select(opts ...Option) Query {
    q := Query{stmt: _select}
    for _, opt := range opts {
        q = opt(q)
    }
    return q
}

func Columns(cols ...string) Option { return func(q Query) Query { q.cols = cols; return q } }
func Table(table string) Option { return func(q Query) Query { q.table = []string{table}; return q } }
</code>

Additional options for WHERE clauses handle LIKE and > operators, automatically managing placeholder numbering and argument slices.

<code>func WhereLike(col string, val interface{}) Option {
    return func(q Query) Query {
        w := where{col: col, op: "LIKE", val: fmt.Sprintf("$%d", len(q.args)+1)}
        q.wheres = append(q.wheres, w)
        q.args = append(q.args, val)
        return q
    }
}

func WhereGt(col string, val interface{}) Option {
    return func(q Query) Query {
        w := where{col: col, op: ">", val: fmt.Sprintf("$%d", len(q.args)+1)}
        q.wheres = append(q.wheres, w)
        q.args = append(q.args, val)
        return q
    }
}
</code>

Higher‑level helpers such as Search and After wrap these primitives, allowing callers to express intent without worrying about empty parameters.

<code>func Search(col, val string) Option {
    return func(q Query) Query {
        if val == "" { return q }
        return WhereLike(col, "%"+val+"%")(q)
    }
}

func After(val string) Option {
    return func(q Query) Query {
        if val == "" { return q }
        return WhereGt("created_at", val)(q)
    }
}
</code>

Putting it all together yields concise, readable code for the original use‑case:

<code>posts := make([]Post, 0)

search := r.URL.Query().Get("search")
after := r.URL.Query().Get("after")

db := sqlx.Open("postgres", "...")

q := Select(
    Columns("*"),
    Table("posts"),
    Search("title", search),
    After(after),
)

err := db.Select(&posts, q.Build(), q.Args()...)
</code>

Although the Build() and Args() methods are not fully implemented in the article, the presented design demonstrates a more idiomatic way to construct complex queries in Go while keeping the API extensible.

Summary

The author concludes that building complex queries in Go benefits from a small, option‑driven builder that stays true to Go's language conventions and can be extended without the boilerplate of manual string concatenation or heavyweight ORMs.