SQL and Query Building in Go

Introduction

Writing efficient SQL queries is crucial for database performance. This guide covers query optimization, prepared statements, and using query builders for complex queries in Go.

Core Concepts

Query Optimization

Use indexes effectively
Avoid N+1 queries
Select only needed columns
Use appropriate joins
Batch operations

Query Builders

Query builders help construct complex queries programmatically:

Squirrel
goqu
sqlc

Good: Query Optimization

Efficient Queries

package main

import (
	"database/sql"
)

// ✅ GOOD: Select only needed columns
func getUsername(db *sql.DB, id int) (string, error) {
	var name string
	err := db.QueryRow("SELECT name FROM users WHERE id = ?", id).Scan(&name)
	return name, err
}

// ❌ BAD: Select all columns
func getAllUserData(db *sql.DB, id int) (*User, error) {
	var user User
	err := db.QueryRow("SELECT * FROM users WHERE id = ?", id).
		Scan(&user.ID, &user.Name, &user.Email, &user.Age)
	return &user, err
}

// ✅ GOOD: Use indexes
func findUserByEmail(db *sql.DB, email string) (*User, error) {
	// Assumes email column is indexed
	var user User
	err := db.QueryRow("SELECT id, name, email, age FROM users WHERE email = ?", email).
		Scan(&user.ID, &user.Name, &user.Email, &user.Age)
	return &user, err
}

// ✅ GOOD: Batch operations
func insertMultipleUsers(db *sql.DB, users []User) error {
	stmt, _ := db.Prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)")
	defer stmt.Close()

	for _, user := range users {
		stmt.Exec(user.Name, user.Email, user.Age)
	}
	return nil
}

// ✅ GOOD: Use JOIN instead of multiple queries
func getUserWithPosts(db *sql.DB, userID int) (*User, error) {
	query := `
		SELECT u.id, u.name, u.email, u.age, p.id, p.title, p.content
		FROM users u
		LEFT JOIN posts p ON u.id = p.user_id
		WHERE u.id = ?
	`
	rows, _ := db.Query(query, userID)
	defer rows.Close()

	user := &User{}
	for rows.Next() {
		var postID sql.NullInt64
		var postTitle, postContent sql.NullString
		rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age,
			&postID, &postTitle, &postContent)
	}
	return user, nil
}

Avoiding N+1 Queries

package main

import (
	"database/sql"
)

// ❌ BAD: N+1 query problem
func getUsersWithPostsBad(db *sql.DB) ([]User, error) {
	rows, _ := db.Query("SELECT id, name, email, age FROM users")
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age)

		// This query runs for each user!
		postRows, _ := db.Query("SELECT id, title FROM posts WHERE user_id = ?", user.ID)
		for postRows.Next() {
			var post Post
			postRows.Scan(&post.ID, &post.Title)
			user.Posts = append(user.Posts, post)
		}
		postRows.Close()

		users = append(users, user)
	}
	return users, nil
}

// ✅ GOOD: Use JOIN to avoid N+1
func getUsersWithPostsGood(db *sql.DB) ([]User, error) {
	query := `
		SELECT u.id, u.name, u.email, u.age, p.id, p.title
		FROM users u
		LEFT JOIN posts p ON u.id = p.user_id
		ORDER BY u.id
	`
	rows, _ := db.Query(query)
	defer rows.Close()

	userMap := make(map[int]*User)
	for rows.Next() {
		var userID int
		var user User
		var postID sql.NullInt64
		var postTitle sql.NullString

		rows.Scan(&userID, &user.Name, &user.Email, &user.Age, &postID, &postTitle)

		if _, exists := userMap[userID]; !exists {
			user.ID = userID
			userMap[userID] = &user
		}

		if postID.Valid {
			post := Post{ID: int(postID.Int64), Title: postTitle.String}
			userMap[userID].Posts = append(userMap[userID].Posts, post)
		}
	}

	var users []User
	for _, user := range userMap {
		users = append(users, *user)
	}
	return users, nil
}

Good: Query Builders

Using Squirrel

package main

import (
	"database/sql"
	sq "github.com/Masterminds/squirrel"
)

// ✅ GOOD: Build queries programmatically
func findUsers(db *sql.DB, name string, minAge int) ([]User, error) {
	query := sq.Select("id", "name", "email", "age").
		From("users").
		Where(sq.Like{"name": "%" + name + "%"}).
		Where(sq.Gt{"age": minAge}).
		OrderBy("created_at DESC")

	rows, err := query.RunWith(db).Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age)
		users = append(users, user)
	}
	return users, nil
}

// ✅ GOOD: Build INSERT queries
func insertUser(db *sql.DB, user *User) error {
	query := sq.Insert("users").
		Columns("name", "email", "age").
		Values(user.Name, user.Email, user.Age)

	result, err := query.RunWith(db).Exec()
	if err != nil {
		return err
	}

	id, _ := result.LastInsertId()
	user.ID = int(id)
	return nil
}

// ✅ GOOD: Build UPDATE queries
func updateUser(db *sql.DB, user *User) error {
	query := sq.Update("users").
		Set("name", user.Name).
		Set("email", user.Email).
		Set("age", user.Age).
		Where(sq.Eq{"id": user.ID})

	_, err := query.RunWith(db).Exec()
	return err
}

// ✅ GOOD: Build DELETE queries
func deleteUser(db *sql.DB, id int) error {
	query := sq.Delete("users").Where(sq.Eq{"id": id})
	_, err := query.RunWith(db).Exec()
	return err
}

// ✅ GOOD: Complex queries with joins
func getUsersWithPostCount(db *sql.DB) ([]User, error) {
	query := sq.Select("u.id", "u.name", "COUNT(p.id) as post_count").
		From("users u").
		LeftJoin("posts p ON u.id = p.user_id").
		GroupBy("u.id", "u.name").
		Having(sq.Gt{"COUNT(p.id)": 0})

	rows, _ := query.RunWith(db).Query()
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		var postCount int
		rows.Scan(&user.ID, &user.Name, &postCount)
		users = append(users, user)
	}
	return users, nil
}

Advanced Patterns

Pagination

package main

import (
	"database/sql"
	sq "github.com/Masterminds/squirrel"
)

// ✅ GOOD: Pagination
func getPaginatedUsers(db *sql.DB, page, pageSize int) ([]User, error) {
	offset := (page - 1) * pageSize

	query := sq.Select("id", "name", "email", "age").
		From("users").
		OrderBy("created_at DESC").
		Limit(uint64(pageSize)).
		Offset(uint64(offset))

	rows, _ := query.RunWith(db).Query()
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age)
		users = append(users, user)
	}
	return users, nil
}

Aggregation

package main

import (
	"database/sql"
	sq "github.com/Masterminds/squirrel"
)

// ✅ GOOD: Aggregation queries
func getUserStats(db *sql.DB) (map[string]interface{}, error) {
	query := sq.Select(
		"COUNT(*) as total_users",
		"AVG(age) as avg_age",
		"MIN(age) as min_age",
		"MAX(age) as max_age",
	).From("users")

	var totalUsers int
	var avgAge, minAge, maxAge float64

	err := query.RunWith(db).QueryRow().
		Scan(&totalUsers, &avgAge, &minAge, &maxAge)

	if err != nil {
		return nil, err
	}

	return map[string]interface{}{
		"total_users": totalUsers,
		"avg_age":     avgAge,
		"min_age":     minAge,
		"max_age":     maxAge,
	}, nil
}

Best Practices

1. Use Prepared Statements

// ✅ GOOD: Prepared statement
stmt, _ := db.Prepare("SELECT * FROM users WHERE id = ?")
defer stmt.Close()
stmt.QueryRow(id)

// ❌ BAD: String concatenation
db.QueryRow("SELECT * FROM users WHERE id = " + strconv.Itoa(id))

2. Index Frequently Queried Columns

-- ✅ GOOD: Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- ❌ BAD: No indexes
-- Queries will be slow

3. Use Appropriate Data Types

// ✅ GOOD: Use sql.NullString for nullable columns
var email sql.NullString
rows.Scan(&email)
if email.Valid {
	fmt.Println(email.String)
}

// ❌ BAD: Assume non-null
var email string
rows.Scan(&email) // Panics if NULL

Resources

Squirrel: https://github.com/Masterminds/squirrel
goqu: https://github.com/doug-martin/goqu
SQL Performance: https://use-the-index-luke.com/

Summary

Writing efficient SQL queries is essential for database performance. Use query builders for complex queries, avoid N+1 problems with joins, and always use prepared statements. Proper indexing and query optimization can dramatically improve application performance.