SQL Query Building in Go

Raw SQL in Go through database/sql is explicit, predictable, and fast — the query you write is the query that runs. The tradeoff is verbosity: complex conditional filters and dynamic ORDER BY clauses become awkward with string concatenation or multiple prepared statement variants.

Query builders sit between raw SQL and full ORMs: they give you programmatic composition of queries while keeping the SQL explicit. This guide covers when raw SQL is the right choice, how to avoid the N+1 problem with JOINs, and when Squirrel or sqlx add real value.

For connection pooling and transaction patterns see Go database fundamentals. For ORM-based access see Go GORM ORM.

Raw SQL: The Default Choice

For straightforward queries, raw SQL with parameterized placeholders is clear and maintainable:

// ✅ Simple parameterized query — clear, safe, fast
func getUserByEmail(ctx context.Context, db *sql.DB, email string) (*User, error) {
    var u User
    err := db.QueryRowContext(ctx,
        `SELECT id, name, email, created_at FROM users WHERE email = $1`,
        email,
    ).Scan(&u.ID, &u.Name, &u.Email, &u.CreatedAt)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &u, err
}

// ✅ Multi-row query with explicit column selection
func listActiveUsers(ctx context.Context, db *sql.DB) ([]User, error) {
    rows, err := db.QueryContext(ctx,
        `SELECT id, name, email FROM users WHERE active = true ORDER BY name ASC`)
    if err != nil {
        return nil, fmt.Errorf("listActiveUsers: %w", err)
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
            return nil, err
        }
        users = append(users, u)
    }
    return users, rows.Err()
}

Always check rows.Err() after the loop — it captures errors that interrupted iteration (network failure, server restart). Forgetting it silently truncates results.

The N+1 Problem and JOIN-Based Solutions

The most common database performance problem in Go: loading a list of parent records, then querying each parent’s children separately:

// ❌ N+1: 1 query for users + 1 query per user for their posts
users, _ := db.QueryContext(ctx, `SELECT id, name FROM users`)
for _, user := range users {
    // This executes once per user row — 100 users = 101 queries
    db.QueryContext(ctx, `SELECT title FROM posts WHERE user_id = $1`, user.ID)
}

Fix: use a JOIN to load everything in one (or two) queries:

// ✅ One query with LEFT JOIN — all data in one round-trip
func getUsersWithPosts(ctx context.Context, db *sql.DB) ([]User, error) {
    rows, err := db.QueryContext(ctx, `
        SELECT
            u.id, u.name, u.email,
            p.id    AS post_id,
            p.title AS post_title
        FROM users u
        LEFT JOIN posts p ON p.user_id = u.id
        ORDER BY u.id, p.id
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    userMap := make(map[int]*User)
    var order []int  // preserve user order

    for rows.Next() {
        var uid int
        var uname, uemail string
        var postID sql.NullInt64
        var postTitle sql.NullString

        if err := rows.Scan(&uid, &uname, &uemail, &postID, &postTitle); err != nil {
            return nil, err
        }

        if _, exists := userMap[uid]; !exists {
            userMap[uid] = &User{ID: uid, Name: uname, Email: uemail}
            order = append(order, uid)
        }

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

    users := make([]User, 0, len(order))
    for _, id := range order {
        users = append(users, *userMap[id])
    }
    return users, rows.Err()
}

An alternative for large datasets: two queries — first load all users, then load all posts for those user IDs in one query using WHERE user_id = ANY($1) (PostgreSQL) or WHERE user_id IN (?) (MySQL). This is simpler than a JOIN and avoids the user row duplication for one-to-many relationships with many children.

Dynamic Queries: When Raw SQL Gets Awkward

Dynamic filter conditions — search forms, admin list pages with optional filters — are where raw SQL becomes unwieldy:

// ❌ String concatenation is fragile and SQL-injection-prone
func listUsers(name, role string, minAge int) ([]User, error) {
    query := "SELECT id, name FROM users WHERE 1=1"
    if name != "" { query += " AND name LIKE '%" + name + "%'" }  // INJECTION RISK
    if role != "" { query += " AND role = '" + role + "'"  }      // INJECTION RISK
    // ...
}

// ✅ Parameterized but still messy
func listUsers(db *sql.DB, name, role string, minAge int) ([]User, error) {
    args := []any{}
    conds := []string{"active = true"}
    if name != "" {
        args = append(args, "%"+name+"%")
        conds = append(conds, fmt.Sprintf("name ILIKE $%d", len(args)))
    }
    if role != "" {
        args = append(args, role)
        conds = append(conds, fmt.Sprintf("role = $%d", len(args)))
    }
    if minAge > 0 {
        args = append(args, minAge)
        conds = append(conds, fmt.Sprintf("age >= $%d", len(args)))
    }
    query := "SELECT id, name FROM users WHERE " + strings.Join(conds, " AND ")
    // ...
}

Squirrel: Programmatic Query Building

Squirrel constructs parameterized SQL from method calls — same safety as manual parameterization, much cleaner for dynamic conditions:

go get github.com/Masterminds/squirrel

import sq "github.com/Masterminds/squirrel"

// Squirrel uses $N for PostgreSQL, ? for MySQL — configure globally
var psql = sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

type UserFilter struct {
    Name   string
    Role   string
    MinAge int
    Limit  int
    Offset int
}

func listUsers(ctx context.Context, db *sql.DB, f UserFilter) ([]User, int, error) {
    // Base query
    base := psql.Select("id", "name", "email", "role", "age").
        From("users").
        Where(sq.Eq{"active": true})

    // Add optional conditions
    if f.Name != "" {
        base = base.Where(sq.ILike{"name": "%" + f.Name + "%"})
    }
    if f.Role != "" {
        base = base.Where(sq.Eq{"role": f.Role})
    }
    if f.MinAge > 0 {
        base = base.Where(sq.GtOrEq{"age": f.MinAge})
    }

    // Count query (same conditions, no limit/offset)
    countQuery := psql.Select("COUNT(*)").
        From("users").
        Where(sq.Eq{"active": true})
    if f.Name != "" {
        countQuery = countQuery.Where(sq.ILike{"name": "%" + f.Name + "%"})
    }
    // ... same conditions ...

    var total int
    if err := countQuery.RunWith(db).QueryRowContext(ctx).Scan(&total); err != nil {
        return nil, 0, err
    }

    // Data query with pagination
    dataQuery := base.
        OrderBy("name ASC").
        Limit(uint64(f.Limit)).
        Offset(uint64(f.Offset))

    rows, err := dataQuery.RunWith(db).QueryContext(ctx)
    if err != nil {
        return nil, 0, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email, &u.Role, &u.Age); err != nil {
            return nil, 0, err
        }
        users = append(users, u)
    }
    return users, total, rows.Err()
}

Squirrel’s conditions (sq.Eq, sq.Like, sq.Gt, sq.And, sq.Or) generate proper parameterized SQL — there’s no injection risk even with dynamic values.

sqlx: Struct Scanning Without the Boilerplate

database/sql requires you to list every column in Scan(). sqlx maps query results to struct fields by column name, using db tags:

go get github.com/jmoiron/sqlx

import "github.com/jmoiron/sqlx"

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

// StructScan replaces multiple Scan() calls
func getUserByID(ctx context.Context, db *sqlx.DB, id int) (*User, error) {
    var u User
    err := db.GetContext(ctx, &u,
        `SELECT id, name, email, created_at FROM users WHERE id = $1`, id)
    if errors.Is(err, sql.ErrNoRows) {
        return nil, ErrNotFound
    }
    return &u, err
}

// SelectContext scans all rows into a slice
func listUsers(ctx context.Context, db *sqlx.DB) ([]User, error) {
    var users []User
    err := db.SelectContext(ctx, &users,
        `SELECT id, name, email, created_at FROM users WHERE active = true ORDER BY name`)
    return users, err
}

// NamedExec: use struct fields as named parameters
func createUser(ctx context.Context, db *sqlx.DB, u *User) error {
    _, err := db.NamedExecContext(ctx,
        `INSERT INTO users (name, email) VALUES (:name, :email)`, u)
    return err
}

sqlx wraps database/sql — you can use it alongside raw database/sql calls. db.GetContext is equivalent to QueryRow + Scan but maps by column name. db.SelectContext scans all rows without the rows.Next() / rows.Scan() loop.

Choosing the Right Approach

Situation	Recommended approach
Simple CRUD, fixed columns	Raw `database/sql`
Reduce `Scan()` boilerplate	`sqlx`
Dynamic WHERE conditions, filters	Squirrel
Complex queries with many joins	Raw SQL (write it, review it)
Full ORM with relationships	GORM

The rule of thumb: raw SQL for fixed queries you can review in a PR, Squirrel for queries that vary by user input, sqlx for reducing scan boilerplate without losing SQL visibility.

Summary

Always parameterize — never concatenate user input into SQL strings
Fix N+1 with a JOIN or a batch WHERE id = ANY($1) query — not by querying in a loop
Always defer rows.Close() and check rows.Err() after the loop
Squirrel is the right tool for dynamic WHERE clauses — generates proper parameterized SQL
sqlx reduces struct-mapping boilerplate while keeping SQL explicit and reviewable
Raw SQL remains valid for complex queries that benefit from being written out explicitly

SQL Query Building in Go

Raw SQL: The Default Choice

The N+1 Problem and JOIN-Based Solutions

Dynamic Queries: When Raw SQL Gets Awkward

Squirrel: Programmatic Query Building

sqlx: Struct Scanning Without the Boilerplate

Choosing the Right Approach

Summary

Resources

Comments

Share this article

👍 Was this article helpful?