Stop concatenating strings and start building queries the right way

If you've been building Go applications that interact with PostgreSQL, you've probably written code like this:

query := "SELECT * FROM users WHERE name = '" + userName + "' AND age > " + strconv.Itoa(minAge)
rows, err := db.Query(query)

While this works, it's a ticking time bomb. String concatenation for SQL queries opens the door to SQL injection attacks, makes code harder to maintain, and becomes unwieldy as queries grow complex. There's a better way.

The Problem with String Concatenation

Let's look at why building SQL queries through string concatenation is problematic:

1. SQL Injection Vulnerabilities

Consider this innocent-looking code:

username := r.FormValue("username") // User input: "'; DROP TABLE users; --"
query := "SELECT * FROM users WHERE username = '" + username + "'"

Congratulations, you just gave an attacker the keys to your database. The malicious input transforms your query into:

SELECT * FROM users WHERE username = ''; DROP TABLE users; --'

2. Type Safety Issues

String concatenation bypasses Go's type system:

// This compiles but will fail at runtime
age := "not_a_number"
query := "SELECT * FROM users WHERE age > " + age

3. Maintenance Nightmare

Complex queries become unreadable:

query := "SELECT u.name, p.title FROM users u " +
         "JOIN posts p ON p.user_id = u.id " +
         "WHERE u.active = " + strconv.FormatBool(active) +
         " AND p.created_at > '" + startDate + "'" +
         " ORDER BY p.created_at DESC"

The Solution: A Type-Safe Query Builder

Let's build a query builder that solves these problems elegantly. Our goals are:

• Security: Automatic parameter binding to prevent SQL injection
• Readability: Fluent interface for clear, chainable methods
• Type Safety: Compile-time error detection
• Maintainability: Reusable, modular query construction

Here's our implementation:

package main

import (
	"database/sql"
	"fmt"
	"strings"

	_ "github.com/lib/pq"
)

// QueryBuilder represents a SQL query builder
type QueryBuilder struct {
	queryType   string
	table       string
	columns     []string
	values      []interface{}
	placeholders []string
	conditions  []string
	joins       []string
	orderBy     []string
	groupBy     []string
	having      []string
	limit       *int
	offset      *int
	args        []interface{}
	argCounter  int
}

// NewQueryBuilder creates a new query builder instance
func NewQueryBuilder() *QueryBuilder {
	return &QueryBuilder{
		columns:      make([]string, 0),
		values:       make([]interface{}, 0),
		placeholders: make([]string, 0),
		conditions:   make([]string, 0),
		joins:        make([]string, 0),
		orderBy:      make([]string, 0),
		groupBy:      make([]string, 0),
		having:       make([]string, 0),
		args:         make([]interface{}, 0),
		argCounter:   0,
	}
}

Building SELECT Queries

The fluent interface makes query construction intuitive:

// Select starts a SELECT query
func (qb *QueryBuilder) Select(columns ...string) *QueryBuilder {
	qb.queryType = "SELECT"
	if len(columns) == 0 {
		qb.columns = append(qb.columns, "*")
	} else {
		qb.columns = append(qb.columns, columns...)
	}
	return qb
}

// From specifies the table to select from
func (qb *QueryBuilder) From(table string) *QueryBuilder {
	qb.table = table
	return qb
}

Safe WHERE Conditions

Instead of string concatenation, we use parameterized queries:

// WhereEq adds a WHERE column = value condition
func (qb *QueryBuilder) WhereEq(column string, value interface{}) *QueryBuilder {
	qb.argCounter++
	qb.conditions = append(qb.conditions, fmt.Sprintf("%s = $%d", column, qb.argCounter))
	qb.args = append(qb.args, value)
	return qb
}

// WhereIn adds a WHERE column IN (...) condition
func (qb *QueryBuilder) WhereIn(column string, values ...interface{}) *QueryBuilder {
	if len(values) == 0 {
		return qb
	}
	
	placeholders := make([]string, len(values))
	for i := range values {
		qb.argCounter++
		placeholders[i] = fmt.Sprintf("$%d", qb.argCounter)
		qb.args = append(qb.args, values[i])
	}
	
	condition := fmt.Sprintf("%s IN (%s)", column, strings.Join(placeholders, ", "))
	qb.conditions = append(qb.conditions, condition)
	return qb
}

// Additional WHERE methods
func (qb *QueryBuilder) WhereGt(column string, value interface{}) *QueryBuilder {
	qb.argCounter++
	qb.conditions = append(qb.conditions, fmt.Sprintf("%s > $%d", column, qb.argCounter))
	qb.args = append(qb.args, value)
	return qb
}

func (qb *QueryBuilder) WhereLike(column string, pattern string) *QueryBuilder {
	qb.argCounter++
	qb.conditions = append(qb.conditions, fmt.Sprintf("%s LIKE $%d", column, qb.argCounter))
	qb.args = append(qb.args, pattern)
	return qb
}

JOIN Support

Complex queries often require JOINs:

// Join adds an INNER JOIN
func (qb *QueryBuilder) Join(table, condition string) *QueryBuilder {
	qb.joins = append(qb.joins, fmt.Sprintf("INNER JOIN %s ON %s", table, condition))
	return qb
}

// LeftJoin adds a LEFT JOIN
func (qb *QueryBuilder) LeftJoin(table, condition string) *QueryBuilder {
	qb.joins = append(qb.joins, fmt.Sprintf("LEFT JOIN %s ON %s", table, condition))
	return qb
}

INSERT, UPDATE, and DELETE

CRUD operations are just as important:

// Insert starts an INSERT query
func (qb *QueryBuilder) Insert(table string) *QueryBuilder {
	qb.queryType = "INSERT"
	qb.table = table
	return qb
}

// Values adds values for INSERT
func (qb *QueryBuilder) Values(data map[string]interface{}) *QueryBuilder {
	for column, value := range data {
		qb.columns = append(qb.columns, column)
		qb.argCounter++
		qb.placeholders = append(qb.placeholders, fmt.Sprintf("$%d", qb.argCounter))
		qb.args = append(qb.args, value)
	}
	return qb
}

// Update starts an UPDATE query
func (qb *QueryBuilder) Update(table string) *QueryBuilder {
	qb.queryType = "UPDATE"
	qb.table = table
	return qb
}

// Set adds a SET clause for UPDATE
func (qb *QueryBuilder) Set(column string, value interface{}) *QueryBuilder {
	qb.argCounter++
	qb.columns = append(qb.columns, fmt.Sprintf("%s = $%d", column, qb.argCounter))
	qb.args = append(qb.args, value)
	return qb
}

Query Execution

The builder pattern culminates in query execution:

// Build constructs the final SQL query and returns it with arguments
func (qb *QueryBuilder) Build() (string, []interface{}) {
	var query strings.Builder

	switch qb.queryType {
	case "SELECT":
		query.WriteString("SELECT ")
		query.WriteString(strings.Join(qb.columns, ", "))
		query.WriteString(" FROM ")
		query.WriteString(qb.table)

		// JOINs
		if len(qb.joins) > 0 {
			query.WriteString(" ")
			query.WriteString(strings.Join(qb.joins, " "))
		}

		// WHERE
		if len(qb.conditions) > 0 {
			query.WriteString(" WHERE ")
			query.WriteString(strings.Join(qb.conditions, " AND "))
		}

		// ORDER BY, GROUP BY, etc.
		// ... (additional clauses)
		
	case "INSERT":
		query.WriteString("INSERT INTO ")
		query.WriteString(qb.table)
		query.WriteString(" (")
		query.WriteString(strings.Join(qb.columns, ", "))
		query.WriteString(") VALUES (")
		query.WriteString(strings.Join(qb.placeholders, ", "))
		query.WriteString(")")
		
	// ... (other query types)
	}

	return query.String(), qb.args
}

// Execute executes the query and returns the result
func (qb *QueryBuilder) Execute(db *sql.DB) (*sql.Rows, error) {
	query, args := qb.Build()
	return db.Query(query, args...)
}

Real-World Usage Examples

Now let's see how this query builder transforms your code:

Simple User Lookup

Before (vulnerable):

username := "john_doe"
query := "SELECT id, name, email FROM users WHERE username = '" + username + "'"
rows, err := db.Query(query)

After (secure):

rows, err := NewQueryBuilder().
	Select("id", "name", "email").
	From("users").
	WhereEq("username", username).
	Execute(db)

Complex Analytics Query

Before (unmaintainable):

query := "SELECT u.name, COUNT(p.id) as post_count, AVG(p.rating) as avg_rating " +
         "FROM users u " +
         "LEFT JOIN posts p ON p.user_id = u.id " +
         "WHERE u.active = " + strconv.FormatBool(true) +
         " AND u.created_at > '" + startDate + "'" +
         " GROUP BY u.id, u.name " +
         "HAVING COUNT(p.id) > " + strconv.Itoa(minPosts) +
         " ORDER BY avg_rating DESC " +
         "LIMIT " + strconv.Itoa(limit)

After (readable and safe):

rows, err := NewQueryBuilder().
	Select("u.name", "COUNT(p.id) as post_count", "AVG(p.rating) as avg_rating").
	From("users u").
	LeftJoin("posts p", "p.user_id = u.id").
	WhereEq("u.active", true).
	WhereGt("u.created_at", startDate).
	GroupBy("u.id", "u.name").
	Having("COUNT(p.id) > ?", minPosts).
	OrderBy("avg_rating", "DESC").
	Limit(limit).
	Execute(db)

Batch Operations

Insert multiple records safely:

// Insert a new user
result, err := NewQueryBuilder().
	Insert("users").
	Values(map[string]interface{}{
		"name":       "Alice Johnson",
		"email":      "alice@example.com",
		"active":     true,
		"created_at": time.Now(),
	}).
	ExecuteNonQuery(db)

// Update user preferences
_, err = NewQueryBuilder().
	Update("users").
	Set("email_notifications", false).
	Set("updated_at", time.Now()).
	WhereEq("id", userID).
	ExecuteNonQuery(db)

Advanced Features

Query Reusability

Clone and modify existing queries:

// Base query for active users
baseQuery := NewQueryBuilder().
	Select("id", "name", "email").
	From("users").
	WhereEq("active", true)

// Specific queries built from base
adminUsers := baseQuery.Clone().WhereEq("role", "admin")
recentUsers := baseQuery.Clone().WhereGt("created_at", lastWeek)

Conditional Query Building

Build queries dynamically based on conditions:

func BuildUserQuery(filters UserFilters) *QueryBuilder {
	qb := NewQueryBuilder().
		Select("id", "name", "email").
		From("users")

	if filters.Active != nil {
		qb.WhereEq("active", *filters.Active)
	}
	
	if filters.Role != "" {
		qb.WhereEq("role", filters.Role)
	}
	
	if len(filters.IDs) > 0 {
		qb.WhereIn("id", filters.IDs...)
	}
	
	if filters.CreatedAfter != nil {
		qb.WhereGt("created_at", *filters.CreatedAfter)
	}

	return qb
}

Performance Considerations

Our query builder is designed for both safety and performance:

1. Prepared Statement Ready: All queries use parameter placeholders, perfect for prepared statements
2. Minimal Allocations: Efficient string building reduces garbage collection pressure
3. Query Plan Caching: PostgreSQL can cache execution plans for parameterized queries

// Prepare once, execute many times
query, args := NewQueryBuilder().
	Select("name", "email").
	From("users").
	WhereEq("department", "?").
	Build()

stmt, err := db.Prepare(query)
defer stmt.Close()

// Execute with different departments
for _, dept := range departments {
	rows, err := stmt.Query(dept)
	// Process results...
}

Testing Made Easy

The query builder makes unit testing straightforward:

func TestUserQuery(t *testing.T) {
	qb := NewQueryBuilder().
		Select("id", "name").
		From("users").
		WhereEq("active", true).
		WhereGt("age", 18)

	query, args := qb.Build()

	expectedQuery := "SELECT id, name FROM users WHERE active = $1 AND age > $2"
	expectedArgs := []interface{}{true, 18}

	assert.Equal(t, expectedQuery, query)
	assert.Equal(t, expectedArgs, args)
}

Benefits Summary

Adopting this query builder approach provides numerous advantages:

Security

• Automatic SQL injection prevention through parameterized queries
• No more worrying about escaping user input

Readability

• Fluent interface makes complex queries easy to understand
• Self-documenting code that clearly expresses intent

Maintainability

• Modular query construction
• Easy to modify and extend
• Consistent patterns across your application

Performance

• Optimized for PostgreSQL parameter binding
• Prepared statement friendly
• Efficient memory usage

Testability

• Easy to unit test query construction
• Mockable interface for integration tests

Extending the Builder

The query builder is designed to be extensible. You can easily add PostgreSQL-specific features:

// Add UPSERT support
func (qb *QueryBuilder) OnConflict(column string, action string) *QueryBuilder {
	// Implementation for ON CONFLICT clause
}

// Add window functions
func (qb *QueryBuilder) WindowFunction(function, partition, orderBy string) *QueryBuilder {
	// Implementation for window functions
}

// Add CTE support
func (qb *QueryBuilder) With(name, query string) *QueryBuilder {
	// Implementation for Common Table Expressions
}

Getting Started

To use this query builder in your project:

1. Add PostgreSQL driver:

go get github.com/lib/pq

2. Copy the query builder code into your project

3. Start building safer queries:

db, err := sql.Open("postgres", connectionString)
if err != nil {
    log.Fatal(err)
}

// Your first safe query
users, err := NewQueryBuilder().
    Select("id", "name", "email").
    From("users").
    WhereEq("active", true).
    OrderBy("name").
    Limit(10).
    Execute(db)

Conclusion

String concatenation for SQL queries is a practice that belongs in the past. By building a type-safe, fluent query builder, we've created a tool that:

• Eliminates SQL injection vulnerabilities
• Makes complex queries readable and maintainable
• Provides compile-time safety
• Improves testing capabilities
• Enhances overall code quality

The investment in building (or adopting) a query builder pays dividends in security, maintainability, and developer productivity. Your future self—and your security team—will thank you.

Ready to make the switch? Start by replacing your most critical queries with the query builder approach, and gradually migrate your entire codebase. The safety and clarity improvements will be immediately apparent.