Building SQL Queries in Go

Building SQL queries safely and efficiently is crucial for database applications. This guide covers best practices and patterns for query building in Go.

Basic Query Building

Static Queries

package main

import (
    "database/sql"
    "time"
)

type User struct {
    ID        int64
    Name      string
    Email     string
    CreatedAt time.Time
}

func GetUserByID(db *sql.DB, id int64) (*User, error) {
    query := `
        SELECT id, name, email, created_at
        FROM users
        WHERE id = $1
    `
    
    var user User
    err := db.QueryRow(query, id).Scan(
        &user.ID,
        &user.Name,
        &user.Email,
        &user.CreatedAt,
    )
    if err != nil {
        return nil, err
    }
    
    return &user, nil
}

func CreateUser(db *sql.DB, user *User) error {
    query := `
        INSERT INTO users (name, email, created_at)
        VALUES ($1, $2, $3)
        RETURNING id
    `
    
    return db.QueryRow(
        query,
        user.Name,
        user.Email,
        time.Now(),
    ).Scan(&user.ID)
}

Dynamic Queries

type UserFilter struct {
    Name      *string
    Email     *string
    CreatedAt *time.Time
    SortBy    string
    SortDesc  bool
    Limit     int
    Offset    int
}

func BuildUserQuery(filter UserFilter) (string, []interface{}) {
    query := "SELECT id, name, email, created_at FROM users WHERE 1=1"
    var args []interface{}
    argPos := 1
    
    if filter.Name != nil {
        query += fmt.Sprintf(" AND name LIKE $%d", argPos)
        args = append(args, "%"+*filter.Name+"%")
        argPos++
    }
    
    if filter.Email != nil {
        query += fmt.Sprintf(" AND email = $%d", argPos)
        args = append(args, *filter.Email)
        argPos++
    }
    
    if filter.CreatedAt != nil {
        query += fmt.Sprintf(" AND created_at >= $%d", argPos)
        args = append(args, *filter.CreatedAt)
        argPos++
    }
    
    if filter.SortBy != "" {
        query += " ORDER BY " + filter.SortBy
        if filter.SortDesc {
            query += " DESC"
        }
    }
    
    if filter.Limit > 0 {
        query += fmt.Sprintf(" LIMIT $%d", argPos)
        args = append(args, filter.Limit)
        argPos++
    }
    
    if filter.Offset > 0 {
        query += fmt.Sprintf(" OFFSET $%d", argPos)
        args = append(args, filter.Offset)
    }
    
    return query, args
}

Query Building Patterns

1. Query Builder

type QueryBuilder struct {
    sql  strings.Builder
    args []interface{}
    pos  int
}

func NewQueryBuilder() *QueryBuilder {
    return &QueryBuilder{pos: 1}
}

func (qb *QueryBuilder) WriteString(s string) {
    qb.sql.WriteString(s)
}

func (qb *QueryBuilder) AddArg(arg interface{}) string {
    qb.args = append(qb.args, arg)
    placeholder := fmt.Sprintf("$%d", qb.pos)
    qb.pos++
    return placeholder
}

func (qb *QueryBuilder) Where(condition string, args ...interface{}) {
    if qb.pos == 1 {
        qb.WriteString(" WHERE ")
    } else {
        qb.WriteString(" AND ")
    }
    
    for _, arg := range args {
        condition = strings.Replace(
            condition,
            "?",
            qb.AddArg(arg),
            1,
        )
    }
    
    qb.WriteString(condition)
}

func (qb *QueryBuilder) Build() (string, []interface{}) {
    return qb.sql.String(), qb.args
}

// Usage
func GetUsersByFilter(db *sql.DB, filter UserFilter) ([]User, error) {
    qb := NewQueryBuilder()
    qb.WriteString("SELECT id, name, email, created_at FROM users")
    
    if filter.Name != nil {
        qb.Where("name LIKE ?", "%"+*filter.Name+"%")
    }
    
    if filter.Email != nil {
        qb.Where("email = ?", *filter.Email)
    }
    
    query, args := qb.Build()
    rows, err := db.Query(query, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt); err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    
    return users, rows.Err()
}

2. SQL Builder

type SQLBuilder struct {
    table     string
    columns   []string
    where     []string
    whereArgs []interface{}
    orderBy   string
    limit     *int
    offset    *int
}

func NewSQLBuilder(table string) *SQLBuilder {
    return &SQLBuilder{table: table}
}

func (b *SQLBuilder) Select(columns ...string) *SQLBuilder {
    b.columns = columns
    return b
}

func (b *SQLBuilder) Where(condition string, args ...interface{}) *SQLBuilder {
    b.where = append(b.where, condition)
    b.whereArgs = append(b.whereArgs, args...)
    return b
}

func (b *SQLBuilder) OrderBy(orderBy string) *SQLBuilder {
    b.orderBy = orderBy
    return b
}

func (b *SQLBuilder) Limit(limit int) *SQLBuilder {
    b.limit = &limit
    return b
}

func (b *SQLBuilder) Offset(offset int) *SQLBuilder {
    b.offset = &offset
    return b
}

func (b *SQLBuilder) BuildSelect() (string, []interface{}) {
    var query strings.Builder
    
    query.WriteString("SELECT ")
    if len(b.columns) > 0 {
        query.WriteString(strings.Join(b.columns, ", "))
    } else {
        query.WriteString("*")
    }
    
    query.WriteString(" FROM ")
    query.WriteString(b.table)
    
    if len(b.where) > 0 {
        query.WriteString(" WHERE ")
        query.WriteString(strings.Join(b.where, " AND "))
    }
    
    if b.orderBy != "" {
        query.WriteString(" ORDER BY ")
        query.WriteString(b.orderBy)
    }
    
    if b.limit != nil {
        query.WriteString(fmt.Sprintf(" LIMIT %d", *b.limit))
    }
    
    if b.offset != nil {
        query.WriteString(fmt.Sprintf(" OFFSET %d", *b.offset))
    }
    
    return query.String(), b.whereArgs
}

Best Practices

1. Prepared Statements

type UserStore struct {
    db *sql.DB
    
    insertUser  *sql.Stmt
    selectUser  *sql.Stmt
    updateUser  *sql.Stmt
    deleteUser  *sql.Stmt
}

func NewUserStore(db *sql.DB) (*UserStore, error) {
    store := &UserStore{db: db}
    
    // Prepare statements
    var err error
    store.insertUser, err = db.Prepare(`
        INSERT INTO users (name, email, created_at)
        VALUES ($1, $2, $3)
        RETURNING id
    `)
    if err != nil {
        return nil, err
    }
    
    store.selectUser, err = db.Prepare(`
        SELECT id, name, email, created_at
        FROM users
        WHERE id = $1
    `)
    if err != nil {
        return nil, err
    }
    
    store.updateUser, err = db.Prepare(`
        UPDATE users
        SET name = $1, email = $2
        WHERE id = $3
    `)
    if err != nil {
        return nil, err
    }
    
    store.deleteUser, err = db.Prepare(`
        DELETE FROM users
        WHERE id = $1
    `)
    if err != nil {
        return nil, err
    }
    
    return store, nil
}

func (s *UserStore) Close() error {
    var errs []error
    
    if err := s.insertUser.Close(); err != nil {
        errs = append(errs, err)
    }
    if err := s.selectUser.Close(); err != nil {
        errs = append(errs, err)
    }
    if err := s.updateUser.Close(); err != nil {
        errs = append(errs, err)
    }
    if err := s.deleteUser.Close(); err != nil {
        errs = append(errs, err)
    }
    
    if len(errs) > 0 {
        return fmt.Errorf("errors closing statements: %v", errs)
    }
    return nil
}

2. Query Validation

type QueryValidator struct {
    allowedColumns map[string]bool
    allowedTables  map[string]bool
}

func NewQueryValidator() *QueryValidator {
    return &QueryValidator{
        allowedColumns: map[string]bool{
            "id":         true,
            "name":      true,
            "email":     true,
            "created_at": true,
        },
        allowedTables: map[string]bool{
            "users":    true,
            "profiles": true,
        },
    }
}

func (v *QueryValidator) ValidateColumn(column string) error {
    if !v.allowedColumns[column] {
        return fmt.Errorf("invalid column: %s", column)
    }
    return nil
}

func (v *QueryValidator) ValidateTable(table string) error {
    if !v.allowedTables[table] {
        return fmt.Errorf("invalid table: %s", table)
    }
    return nil
}

func (v *QueryValidator) ValidateOrderBy(orderBy string) error {
    parts := strings.Split(orderBy, " ")
    if len(parts) > 2 {
        return fmt.Errorf("invalid order by clause: %s", orderBy)
    }
    
    if err := v.ValidateColumn(parts[0]); err != nil {
        return err
    }
    
    if len(parts) == 2 {
        direction := strings.ToUpper(parts[1])
        if direction != "ASC" && direction != "DESC" {
            return fmt.Errorf("invalid sort direction: %s", direction)
        }
    }
    
    return nil
}

3. Query Logging

type QueryLogger struct {
    db     *sql.DB
    logger *log.Logger
}

func NewQueryLogger(db *sql.DB, logger *log.Logger) *QueryLogger {
    return &QueryLogger{
        db:     db,
        logger: logger,
    }
}

func (ql *QueryLogger) logQuery(query string, args []interface{}, duration time.Duration, err error) {
    ql.logger.Printf(
        "Query: %s\nArgs: %v\nDuration: %s\nError: %v\n",
        query,
        args,
        duration,
        err,
    )
}

func (ql *QueryLogger) Query(query string, args ...interface{}) (*sql.Rows, error) {
    start := time.Now()
    rows, err := ql.db.Query(query, args...)
    ql.logQuery(query, args, time.Since(start), err)
    return rows, err
}

func (ql *QueryLogger) QueryRow(query string, args ...interface{}) *sql.Row {
    start := time.Now()
    row := ql.db.QueryRow(query, args...)
    ql.logQuery(query, args, time.Since(start), nil)
    return row
}

Common Patterns

1. Bulk Operations

type BulkInserter struct {
    db        *sql.DB
    tableName string
    columns   []string
    batchSize int
    values    []interface{}
    valueLen  int
}

func NewBulkInserter(db *sql.DB, tableName string, columns []string, batchSize int) *BulkInserter {
    return &BulkInserter{
        db:        db,
        tableName: tableName,
        columns:   columns,
        batchSize: batchSize,
        valueLen:  len(columns),
    }
}

func (bi *BulkInserter) Add(values ...interface{}) error {
    if len(values) != bi.valueLen {
        return fmt.Errorf(
            "invalid number of values: got %d, want %d",
            len(values),
            bi.valueLen,
        )
    }
    
    bi.values = append(bi.values, values...)
    
    if len(bi.values)/bi.valueLen >= bi.batchSize {
        return bi.Flush()
    }
    
    return nil
}

func (bi *BulkInserter) Flush() error {
    if len(bi.values) == 0 {
        return nil
    }
    
    valueCount := len(bi.values) / bi.valueLen
    placeholders := make([]string, valueCount)
    
    for i := 0; i < valueCount; i++ {
        placeholder := make([]string, bi.valueLen)
        for j := 0; j < bi.valueLen; j++ {
            placeholder[j] = fmt.Sprintf("$%d", i*bi.valueLen+j+1)
        }
        placeholders[i] = "(" + strings.Join(placeholder, ", ") + ")"
    }
    
    query := fmt.Sprintf(
        "INSERT INTO %s (%s) VALUES %s",
        bi.tableName,
        strings.Join(bi.columns, ", "),
        strings.Join(placeholders, ", "),
    )
    
    _, err := bi.db.Exec(query, bi.values...)
    if err != nil {
        return err
    }
    
    bi.values = bi.values[:0]
    return nil
}

2. Query Caching

type QueryCache struct {
    cache  map[string]*sql.Stmt
    db     *sql.DB
    mu     sync.RWMutex
}

func NewQueryCache(db *sql.DB) *QueryCache {
    return &QueryCache{
        cache: make(map[string]*sql.Stmt),
        db:    db,
    }
}

func (qc *QueryCache) GetStmt(query string) (*sql.Stmt, error) {
    qc.mu.RLock()
    stmt, ok := qc.cache[query]
    qc.mu.RUnlock()
    
    if ok {
        return stmt, nil
    }
    
    qc.mu.Lock()
    defer qc.mu.Unlock()
    
    // Double-check after acquiring write lock
    if stmt, ok := qc.cache[query]; ok {
        return stmt, nil
    }
    
    stmt, err := qc.db.Prepare(query)
    if err != nil {
        return nil, err
    }
    
    qc.cache[query] = stmt
    return stmt, nil
}

func (qc *QueryCache) Close() error {
    qc.mu.Lock()
    defer qc.mu.Unlock()
    
    var errs []error
    for _, stmt := range qc.cache {
        if err := stmt.Close(); err != nil {
            errs = append(errs, err)
        }
    }
    
    if len(errs) > 0 {
        return fmt.Errorf("errors closing statements: %v", errs)
    }
    return nil
}

Next Steps

• Learn about Transactions
• Explore Connection Pooling
• Study NoSQL