mnmlmnl

I really enjoy writing SQL builders. By that I mean that instead of writing a SQL query, I write a little struct along with a builder pattern that allows me to build up the query. This gives me a proper API documenting the query's intent, as well as a way to do schema migrations in the future while keeping the power of raw SQL.

This is three times more so these days, where most of this code can be TAB-completed with GitHub copilot. I wrote the following example in about 2 minutes.

type UserQuery struct {
	filterEnabled bool
	filterValue   string
	getFullInfo   bool
}

func NewUserQuery() *UserQuery {
	return &UserQuery{
		filterEnabled: false,
		filterValue:   "",
		getFullInfo:   false,
	}
}

func (uq *UserQuery) Filter(filterValue string) *UserQuery {
	uq.filterEnabled = true
	uq.filterValue = filterValue
	return uq
}

func (uq *UserQuery) FullInfo() *UserQuery {
	uq.getFullInfo = true
	return uq
}

func (uq *UserQuery) Build() (string, []interface{}) {

	binds := make([]interface{}, 0)
	selects := []string{"id", "name", "email"}
	wheres := []string{"1=1"}

	if uq.filterEnabled {
		wheres = append(wheres, "name LIKE ?")
		binds = append(binds, uq.filterValue)
	}
	if uq.getFullInfo {
		selects = append(selects, "phone", "address")
	}

	return fmt.Sprintf(
		"SELECT %s FROM users WHERE %s",
		strings.Join(selects, ", "),
		strings.Join(wheres, " AND ")), binds
}

func main() {
	q := NewUserQuery().Filter("Manuel%").FullInfo()
	query, binds := q.Build()
	db.Query(query, binds...)
}

One thing I love doing with this pattern is keeping performance metrics and logs of executed queries, which is easily done and configurable because I can add all kinds of things to the UserQuery class itself.