Exercise: SQL Query Builder

Difficulty - Intermediate

Learning Objectives

• Implement the builder pattern for fluent APIs
• Generate SQL queries programmatically
• Handle query composition and validation
• Implement method chaining for ergonomics
• Build type-safe query construction
• Support parameterized queries for SQL injection prevention

Problem Statement

Create a fluent SQL query builder that generates SELECT queries programmatically. Building SQL strings manually is error-prone and vulnerable to SQL injection. A query builder provides a type-safe, composable API for constructing queries. This pattern is used in ORMs, database abstraction layers, and data access frameworks. Your implementation should support SELECT statements with WHERE clauses, JOINs, ORDER BY, LIMIT, and parameterized values.

Requirements

1. Basic SELECT Queries

Implement a QueryBuilder type that:

• Builds SELECT queries with column selection
• Supports FROM clause with table name
• Returns the final SQL string via Build() method
• Uses method chaining
• Handles SELECT * when no columns specified
• Properly quotes identifiers if needed

Example Usage:

1query := Select("id", "name", "email").
2    From("users").
3    Build()
4// SELECT id, name, email FROM users

2. WHERE Clause

Add filtering with WHERE conditions:

• Support single WHERE condition
• Support multiple conditions with AND
• Use placeholders for parameterized queries
• Return both SQL and parameter values
• Support various operators
• Properly escape and parameterize values

Example Usage:

1qb := Select("*").
2    From("products").
3    Where("price > ?", 100).
4    Where("category = ?", "electronics")
5
6sql, args := qb.Build()
7// SQL: SELECT * FROM products WHERE price > ? AND category = ?
8// Args: [100, "electronics"]

3. ORDER BY Clause

Support result ordering:

• Order by single column
• Order by multiple columns
• Support ASC and DESC directions
• Default to ASC if not specified
• Validate column names
• Chain with other clauses

Example Usage:

1qb := Select("name", "age").
2    From("users").
3    OrderBy("age", "DESC").
4    OrderBy("name", "ASC")
5// SELECT name, age FROM users ORDER BY age DESC, name ASC

4. LIMIT and OFFSET

Implement pagination support:

• LIMIT clause for max results
• OFFSET clause for skipping results
• Combine for pagination
• Validate positive integers
• Handle databases with different syntax
• Support common pagination patterns

Example Usage:

1// Page 3, 10 items per page
2qb := Select("*").
3    From("posts").
4    OrderBy("created_at", "DESC").
5    Limit(10).
6    Offset(20)
7// SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20

5. JOIN Operations

Support table joins:

• INNER JOIN with ON condition
• LEFT JOIN and RIGHT JOIN
• Multiple joins in single query
• Alias support for tables
• Join condition with parameterized values
• Validate join syntax

Example Usage:

1qb := Select("u.name", "o.total").
2    From("users AS u").
3    Join("INNER", "orders AS o", "u.id = o.user_id").
4    Where("o.status = ?", "completed")
5// SELECT u.name, o.total FROM users AS u
6// INNER JOIN orders AS o ON u.id = o.user_id
7// WHERE o.status = ?

6. Advanced Features

Enhance with additional capabilities:

• GROUP BY clause for aggregations
• HAVING clause for group filtering
• DISTINCT keyword for unique results
• Aggregate functions
• Subquery support
• Raw SQL fragments for complex cases

Example Usage:

1qb := Select("category", "COUNT(*) as count").
2    From("products").
3    GroupBy("category").
4    Having("COUNT(*) > ?", 5).
5    OrderBy("count", "DESC")
6// SELECT category, COUNT(*) as count FROM products
7// GROUP BY category
8// HAVING COUNT(*) > ?
9// ORDER BY count DESC

Type Signatures

 1package querybuilder
 2
 3import "strings"
 4
 5// QueryBuilder builds SQL SELECT queries
 6type QueryBuilder struct {
 7    columns  []string
 8    table    string
 9    where    []whereClause
10    joins    []joinClause
11    orderBy  []orderClause
12    groupBy  []string
13    having   []whereClause
14    limit    int
15    offset   int
16    distinct bool
17}
18
19type whereClause struct {
20    condition string
21    args      []interface{}
22}
23
24type joinClause struct {
25    joinType  string  // INNER, LEFT, RIGHT
26    table     string
27    condition string
28}
29
30type orderClause struct {
31    column    string
32    direction string  // ASC or DESC
33}
34
35// Constructor
36func Select(columns ...string) *QueryBuilder
37func SelectDistinct(columns ...string) *QueryBuilder
38
39// Core building methods
40func From(table string) *QueryBuilder
41func Where(condition string, args ...interface{}) *QueryBuilder
42func OrderBy(column, direction string) *QueryBuilder
43func Limit(n int) *QueryBuilder
44func Offset(n int) *QueryBuilder
45
46// JOIN operations
47func Join(joinType, table, condition string) *QueryBuilder
48func InnerJoin(table, condition string) *QueryBuilder
49func LeftJoin(table, condition string) *QueryBuilder
50func RightJoin(table, condition string) *QueryBuilder
51
52// Grouping and aggregation
53func GroupBy(columns ...string) *QueryBuilder
54func Having(condition string, args ...interface{}) *QueryBuilder
55
56// Build final query
57func Build()
58func BuildQuery() string  // SQL only, no args
59func ToSQL() string        // Alias for BuildQuery

Example: E-commerce Query Builder

Here's a realistic example building queries for an e-commerce system:

 1package main
 2
 3import (
 4    "database/sql"
 5    "fmt"
 6    "log"
 7    _ "github.com/lib/pq"
 8    "your-module/querybuilder"
 9)
10
11func main() {
12    db, err := sql.Open("postgres", "connection-string")
13    if err != nil {
14        log.Fatal(err)
15    }
16    defer db.Close()
17
18    // Query 1: Find active products in category
19    sql1, args1 := querybuilder.Select("id", "name", "price").
20        From("products").
21        Where("category = ?", "electronics").
22        Where("active = ?", true).
23        OrderBy("price", "ASC").
24        Limit(10).
25        Build()
26
27    rows1, err := db.Query(sql1, args1...)
28    if err != nil {
29        log.Fatal(err)
30    }
31    defer rows1.Close()
32
33    // Query 2: User order summary with JOIN
34    sql2, args2 := querybuilder.
35        Select("u.name", "u.email", "COUNT(o.id) as order_count", "SUM(o.total) as total_spent").
36        From("users AS u").
37        LeftJoin("orders AS o", "u.id = o.user_id").
38        Where("o.created_at > ?", "2024-01-01").
39        GroupBy("u.id", "u.name", "u.email").
40        Having("COUNT(o.id) > ?", 5).
41        OrderBy("total_spent", "DESC").
42        Limit(20).
43        Build()
44
45    rows2, err := db.Query(sql2, args2...)
46    if err != nil {
47        log.Fatal(err)
48    }
49    defer rows2.Close()
50
51    // Query 3: Product search with multiple filters
52    filters := map[string]interface{}{
53        "min_price": 100,
54        "max_price": 500,
55        "category":  "laptops",
56    }
57
58    qb := querybuilder.Select("*").From("products")
59
60    if min, ok := filters["min_price"]; ok {
61        qb.Where("price >= ?", min)
62    }
63    if max, ok := filters["max_price"]; ok {
64        qb.Where("price <= ?", max)
65    }
66    if cat, ok := filters["category"]; ok {
67        qb.Where("category = ?", cat)
68    }
69
70    sql3, args3 := qb.OrderBy("rating", "DESC").
71        Limit(50).
72        Build()
73
74    rows3, err := db.Query(sql3, args3...)
75    if err != nil {
76        log.Fatal(err)
77    }
78    defer rows3.Close()
79
80    fmt.Println("Queries executed successfully")
81}

Test Cases

Your implementation should pass these scenarios:

  1// Test basic SELECT
  2func TestBasicSelect() {
  3    qb := Select("id", "name").From("users")
  4    sql, _ := qb.Build()
  5    // sql should be: "SELECT id, name FROM users"
  6}
  7
  8// Test SELECT *
  9func TestSelectAll() {
 10    qb := Select().From("products")
 11    sql, _ := qb.Build()
 12    // sql should be: "SELECT * FROM products"
 13}
 14
 15// Test WHERE clause
 16func TestWhere() {
 17    qb := Select("*").From("users").Where("age > ?", 18)
 18    sql, args := qb.Build()
 19    // sql should contain: "WHERE age > ?"
 20    // args[0] should be: 18
 21}
 22
 23// Test multiple WHERE
 24func TestMultipleWhere() {
 25    qb := Select("*").
 26        From("products").
 27        Where("price > ?", 100).
 28        Where("category = ?", "electronics")
 29
 30    sql, args := qb.Build()
 31    // sql should contain: "WHERE price > ? AND category = ?"
 32    // len(args) should be: 2
 33    // args[0] should be: 100
 34    // args[1] should be: "electronics"
 35}
 36
 37// Test ORDER BY
 38func TestOrderBy() {
 39    qb := Select("*").
 40        From("users").
 41        OrderBy("created_at", "DESC")
 42
 43    sql, _ := qb.Build()
 44    // sql should contain: "ORDER BY created_at DESC"
 45}
 46
 47// Test multiple ORDER BY
 48func TestMultipleOrderBy() {
 49    qb := Select("*").
 50        From("users").
 51        OrderBy("age", "DESC").
 52        OrderBy("name", "ASC")
 53
 54    sql, _ := qb.Build()
 55    // sql should contain: "ORDER BY age DESC, name ASC"
 56}
 57
 58// Test LIMIT
 59func TestLimit() {
 60    qb := Select("*").From("posts").Limit(10)
 61    sql, _ := qb.Build()
 62    // sql should contain: "LIMIT 10"
 63}
 64
 65// Test OFFSET
 66func TestOffset() {
 67    qb := Select("*").From("posts").Limit(10).Offset(20)
 68    sql, _ := qb.Build()
 69    // sql should contain: "LIMIT 10 OFFSET 20"
 70}
 71
 72// Test INNER JOIN
 73func TestInnerJoin() {
 74    qb := Select("u.name", "o.total").
 75        From("users AS u").
 76        InnerJoin("orders AS o", "u.id = o.user_id")
 77
 78    sql, _ := qb.Build()
 79    // sql should contain: "FROM users AS u"
 80    // sql should contain: "INNER JOIN orders AS o ON u.id = o.user_id"
 81}
 82
 83// Test LEFT JOIN
 84func TestLeftJoin() {
 85    qb := Select("*").
 86        From("users").
 87        LeftJoin("profiles", "users.id = profiles.user_id")
 88
 89    sql, _ := qb.Build()
 90    // sql should contain: "LEFT JOIN profiles ON users.id = profiles.user_id"
 91}
 92
 93// Test GROUP BY
 94func TestGroupBy() {
 95    qb := Select("category", "COUNT(*) as count").
 96        From("products").
 97        GroupBy("category")
 98
 99    sql, _ := qb.Build()
100    // sql should contain: "GROUP BY category"
101}
102
103// Test HAVING
104func TestHaving() {
105    qb := Select("category", "COUNT(*) as count").
106        From("products").
107        GroupBy("category").
108        Having("COUNT(*) > ?", 5)
109
110    sql, args := qb.Build()
111    // sql should contain: "HAVING COUNT(*) > ?"
112    // args should contain: 5
113}
114
115// Test DISTINCT
116func TestDistinct() {
117    qb := SelectDistinct("category").From("products")
118    sql, _ := qb.Build()
119    // sql should contain: "SELECT DISTINCT category"
120}
121
122// Test complex query
123func TestComplexQuery() {
124    qb := Select("u.name", "COUNT(o.id) as orders").
125        From("users AS u").
126        LeftJoin("orders AS o", "u.id = o.user_id").
127        Where("u.active = ?", true).
128        Where("o.created_at > ?", "2024-01-01").
129        GroupBy("u.id", "u.name").
130        Having("COUNT(o.id) > ?", 3).
131        OrderBy("orders", "DESC").
132        Limit(10)
133
134    sql, args := qb.Build()
135
136    // Verify all clauses present
137    // sql should contain: "SELECT u.name, COUNT(o.id) as orders"
138    // sql should contain: "FROM users AS u"
139    // sql should contain: "LEFT JOIN orders AS o ON u.id = o.user_id"
140    // sql should contain: "WHERE u.active = ? AND o.created_at > ?"
141    // sql should contain: "GROUP BY u.id, u.name"
142    // sql should contain: "HAVING COUNT(o.id) > ?"
143    // sql should contain: "ORDER BY orders DESC"
144    // sql should contain: "LIMIT 10"
145    // len(args) should be: 3
146}
147
148// Test method chaining
149func TestMethodChaining() {
150    qb := Select("*")
151
152    // Should return same instance for chaining
153    result := qb.From("users").Where("age > ?", 18)
154
155    // result should be same instance as qb
156}

Common Pitfalls

⚠️ Watch out for these common mistakes:

1. SQL injection: Never concatenate user input directly - always use placeholders
2. Incorrect WHERE AND logic: Multiple WHERE calls should combine with AND, not replace each other
3. Missing spaces in SQL: Forgetting spaces between clauses causes syntax errors
4. ORDER BY direction validation: Not validating ASC/DESC allows invalid SQL
5. Placeholder counting: Mismatched number of ? placeholders and argument values causes errors
6. Mutating vs returning: Builder methods must return *QueryBuilder for chaining
7. Nil pointer dereference: Not initializing slices in constructor causes panics

Hints

1func From(table string) *QueryBuilder {
2    qb.table = table
3    return qb  // Return self for chaining
4}

 1type whereClause struct {
 2    condition string
 3    args      []interface{}
 4}
 5
 6// In Build():
 7if len(qb.where) > 0 {
 8    conditions := make([]string, len(qb.where))
 9    for i, w := range qb.where {
10        conditions[i] = w.condition
11    }
12    sql += " WHERE " + strings.Join(conditions, " AND ")
13}

 1var args []interface{}
 2
 3for _, w := range qb.where {
 4    args = append(args, w.args...)
 5}
 6
 7for _, h := range qb.having {
 8    args = append(args, h.args...)
 9}
10
11return sql, args

 1parts := []string{"SELECT"}
 2// 1. SELECT columns
 3// 2. FROM table
 4// 3. JOIN clauses
 5// 4. WHERE conditions
 6// 5. GROUP BY columns
 7// 6. HAVING conditions
 8// 7. ORDER BY columns
 9// 8. LIMIT
10// 9. OFFSET
11
12return strings.Join(parts, " ")

Solution

  1package querybuilder
  2
  3import (
  4    "fmt"
  5    "strings"
  6)
  7
  8type QueryBuilder struct {
  9    columns  []string
 10    table    string
 11    where    []whereClause
 12    joins    []joinClause
 13    orderBy  []orderClause
 14    groupBy  []string
 15    having   []whereClause
 16    limit    int
 17    offset   int
 18    distinct bool
 19}
 20
 21type whereClause struct {
 22    condition string
 23    args      []interface{}
 24}
 25
 26type joinClause struct {
 27    joinType  string
 28    table     string
 29    condition string
 30}
 31
 32type orderClause struct {
 33    column    string
 34    direction string
 35}
 36
 37func Select(columns ...string) *QueryBuilder {
 38    return &QueryBuilder{
 39        columns: columns,
 40        where:   make([]whereClause, 0),
 41        joins:   make([]joinClause, 0),
 42        orderBy: make([]orderClause, 0),
 43        groupBy: make([]string, 0),
 44        having:  make([]whereClause, 0),
 45    }
 46}
 47
 48func SelectDistinct(columns ...string) *QueryBuilder {
 49    qb := Select(columns...)
 50    qb.distinct = true
 51    return qb
 52}
 53
 54func From(table string) *QueryBuilder {
 55    qb.table = table
 56    return qb
 57}
 58
 59func Where(condition string, args ...interface{}) *QueryBuilder {
 60    qb.where = append(qb.where, whereClause{
 61        condition: condition,
 62        args:      args,
 63    })
 64    return qb
 65}
 66
 67func OrderBy(column, direction string) *QueryBuilder {
 68    if direction == "" {
 69        direction = "ASC"
 70    }
 71    qb.orderBy = append(qb.orderBy, orderClause{
 72        column:    column,
 73        direction: strings.ToUpper(direction),
 74    })
 75    return qb
 76}
 77
 78func Limit(n int) *QueryBuilder {
 79    qb.limit = n
 80    return qb
 81}
 82
 83func Offset(n int) *QueryBuilder {
 84    qb.offset = n
 85    return qb
 86}
 87
 88func Join(joinType, table, condition string) *QueryBuilder {
 89    qb.joins = append(qb.joins, joinClause{
 90        joinType:  strings.ToUpper(joinType),
 91        table:     table,
 92        condition: condition,
 93    })
 94    return qb
 95}
 96
 97func InnerJoin(table, condition string) *QueryBuilder {
 98    return qb.Join("INNER", table, condition)
 99}
100
101func LeftJoin(table, condition string) *QueryBuilder {
102    return qb.Join("LEFT", table, condition)
103}
104
105func RightJoin(table, condition string) *QueryBuilder {
106    return qb.Join("RIGHT", table, condition)
107}
108
109func GroupBy(columns ...string) *QueryBuilder {
110    qb.groupBy = append(qb.groupBy, columns...)
111    return qb
112}
113
114func Having(condition string, args ...interface{}) *QueryBuilder {
115    qb.having = append(qb.having, whereClause{
116        condition: condition,
117        args:      args,
118    })
119    return qb
120}
121
122func Build() {
123    var parts []string
124    var args []interface{}
125
126    // SELECT clause
127    selectClause := "SELECT"
128    if qb.distinct {
129        selectClause += " DISTINCT"
130    }
131
132    if len(qb.columns) == 0 {
133        selectClause += " *"
134    } else {
135        selectClause += " " + strings.Join(qb.columns, ", ")
136    }
137    parts = append(parts, selectClause)
138
139    // FROM clause
140    if qb.table != "" {
141        parts = append(parts, "FROM "+qb.table)
142    }
143
144    // JOIN clauses
145    for _, join := range qb.joins {
146        parts = append(parts, fmt.Sprintf("%s JOIN %s ON %s",
147            join.joinType, join.table, join.condition))
148    }
149
150    // WHERE clause
151    if len(qb.where) > 0 {
152        conditions := make([]string, len(qb.where))
153        for i, w := range qb.where {
154            conditions[i] = w.condition
155            args = append(args, w.args...)
156        }
157        parts = append(parts, "WHERE "+strings.Join(conditions, " AND "))
158    }
159
160    // GROUP BY clause
161    if len(qb.groupBy) > 0 {
162        parts = append(parts, "GROUP BY "+strings.Join(qb.groupBy, ", "))
163    }
164
165    // HAVING clause
166    if len(qb.having) > 0 {
167        conditions := make([]string, len(qb.having))
168        for i, h := range qb.having {
169            conditions[i] = h.condition
170            args = append(args, h.args...)
171        }
172        parts = append(parts, "HAVING "+strings.Join(conditions, " AND "))
173    }
174
175    // ORDER BY clause
176    if len(qb.orderBy) > 0 {
177        orderParts := make([]string, len(qb.orderBy))
178        for i, o := range qb.orderBy {
179            orderParts[i] = o.column + " " + o.direction
180        }
181        parts = append(parts, "ORDER BY "+strings.Join(orderParts, ", "))
182    }
183
184    // LIMIT clause
185    if qb.limit > 0 {
186        parts = append(parts, fmt.Sprintf("LIMIT %d", qb.limit))
187    }
188
189    // OFFSET clause
190    if qb.offset > 0 {
191        parts = append(parts, fmt.Sprintf("OFFSET %d", qb.offset))
192    }
193
194    sql := strings.Join(parts, " ")
195    return sql, args
196}
197
198func BuildQuery() string {
199    sql, _ := qb.Build()
200    return sql
201}
202
203func ToSQL() string {
204    return qb.BuildQuery()
205}

Key Takeaways

• Builder pattern enables fluent, composable APIs
• Parameterized queries prevent SQL injection
• Method chaining improves code readability
• Type safety catches errors at compile time
• Query builders abstract database-specific syntax
• Proper SQL clause ordering is critical for valid queries