db-sql-query-builder-go

2 answer(s)gogo1.26gogo1.26

📦 Source in repository (JSON)

Answer 1

The implementation is a type-safe SQL query builder in Go with three key components:

1. Query Builder (`Builder` struct)

Method-chaining API that constructs parameterized SQL SELECT queries:

// Builder supports method chaining for building SELECT queries
qb := sqlqb.Select("u.id", "u.name", "o.total").
    From("users u").
    InnerJoin("orders o", "o.user_id = u.id").
    Where("u.age > $1", 21).
    Where("o.status = $1", "pending").
    OrderBy("u.name ASC").
    Limit(5)

query, args, err := qb.Build()
// query:  "SELECT u.id, u.name, o.total FROM users u\nINNER JOIN orders o ON o.user_id = u.id\nWHERE u.age > $1 AND o.status = $2\nORDER BY u.name ASC\nLIMIT $3"
// args:   [21, "pending", 5]

Select(columns...) — creates a builder with column list
From(table) — sets the source table
InnerJoin/LeftJoin/RightJoin(table, onExpr, params...) — each join type
Where(expr, params...) — conditions combined with AND
OrderBy(columns...) — ORDER BY clause
Limit(count) — LIMIT clause
Build() — returns (sql string, args []interface{}, err)

2. Parameterized Queries (SQL Injection Prevention)

All user values use $N placeholders that are rewritten into global positional parameters. The SQL string never contains raw user data:

qb := Select("*").From("users").Where("name = $1", "'; DROP TABLE users; --")
query, args, _ := qb.Build()
// query: "SELECT * FROM users\nWHERE name = $1"   ← safe, no DROP in SQL
// args:  ["'; DROP TABLE users; --"]              ← malicious string isolated in parameters

The rewriteParams() function renumbers $1, $2 placeholders within each clause into global $1, $2, $3, ... across the entire query, collecting args in order.

3. Result Scanning into Structs (Reflection)

Two scanning functions use the Rows interface (satisfied by *sql.Rows and test mocks):

type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
    Age   int    `db:"age"`
}

// ScanRow — single struct
var u User
err := sqlqb.ScanRow(rows, &u)  // rows is *sql.Rows

// ScanAll — slice of structs
var users []User
err := sqlqb.ScanAll(rows, &users)

// Also works with pointers: []*User
var users []*User
err := sqlqb.ScanAll(rows, &users)

Column mapping rules: - Uses db:"column_name" struct tag if present - Falls back to lowercased field name (e.g., UserID → "userid") - Case-insensitive column matching - Fields tagged db:"-" are ignored - Unexported fields are ignored - Extra/missing columns are handled gracefully (missing → zero value, extra → discarded)

Key files:

~/sqlqb/sqlqb.go — main package (~370 lines)
~/sqlqb/sqlqb_test.go — comprehensive tests (~230 lines)

Evidence & signatures

All 33 tests pass:

```
=== RUN   TestSelectBasic          --- PASS
=== RUN   TestSelectStar           --- PASS
=== RUN   TestWhereWithParams      --- PASS
=== RUN   TestWhereMultipleParamsInOneExpr --- PASS
=== RUN   TestOrderBy              --- PASS
=== RUN   TestLimit                --- PASS
=== RUN   TestFullQuery            --- PASS
=== RUN   TestJoinTypes            --- PASS (4 subtests: inner, left, right, with params)
=== RUN   TestJoinWithParams       --- PASS
=== RUN   TestMultipleJoins        --- PASS
=== RUN   TestNoColumns            --- PASS  (error: no columns)
=== RUN   TestNoFrom               --- PASS  (error: no table)
=== RUN   TestChaining             --- PASS
=== RUN   TestMultipleWhere        --- PASS
=== RUN   TestNoSQLInjectionInParams --- PASS
=== RUN   TestNoSQLInjectionInJoinParams --- PASS
=== RUN   TestScanSingleRow        --- PASS
=== RUN   TestScanAll              --- PASS
=== RUN   TestColumnMappingWithTags --- PASS
=== RUN   TestColumnMappingCaseInsensitive --- PASS
=== RUN   TestColumnMappingIgnoresUnexported --- PASS
=== RUN   TestColumnMappingIgnoresDashTag --- PASS
=== RUN   TestScanWithDifferentColumnOrder --- PASS
=== RUN   TestScanWithExtraColumns --- PASS
=== RUN   TestScanWithSubsetOfColumns --- PASS
=== RUN   TestScanRowNoRows        --- PASS
=== RUN   TestScanRowNonPtrDest    --- PASS
=== RUN   TestScanAllEmptyResult   --- PASS
=== RUN   TestLimitZero            --- PASS
=== RUN   TestWhereSpecialChars    --- PASS
=== RUN   TestWhereInClause        --- PASS
=== RUN   TestAllPlaceholdersCombined --- PASS
=== RUN   TestScanAllPointerSlice  --- PASS
=== RUN   TestConcurrentBuild      --- PASS
```

**Edge cases covered:**
| Edge Case | Status |
|---|---|
| SQL injection via WHERE params (DROP, DELETE) | ✅ Prevented |
| SQL injection via JOIN params | ✅ Prevented |
| No columns selected | ✅ Error |
| No FROM table | ✅ Error |
| Multiple JOINs (INNER + LEFT) | ✅ Works |
| JOIN with parameters | ✅ Works |
| JOIN without parameters | ✅ Works |
| WHERE with multiple params per expression (`$1, $2`) | ✅ Works |
| WHERE with `IN ($1, $2, $3)` | ✅ Works |
| WHERE with `LIKE` and special chars (`%test%`) | ✅ Works |
| LIMIT 0 | ✅ Generates `LIMIT $1` |
| No WHERE clause | ✅ Works |
| No ORDER BY | ✅ Works |
| No LIMIT | ✅ Works |
| Columns in different order than struct fields | ✅ Matches correctly |
| Extra columns from DB not in struct | ✅ Discarded |
| Subset of columns from DB | ✅ Zero values for unscanned |
| No rows returned | ✅ Error |
| Non-pointer destination | ✅ Error |
| Pointer slice (`[]*User`) | ✅ Works |
| Empty result set | ✅ No error, empty slice |
| Concurrent `Build()` calls | ✅ Thread-safe (mutex) |
| Unexported struct fields | ✅ Ignored |
| `db:"-"` tagged fields | ✅ Ignored |

---

{"model": "claude-sonnet-4-20250514", "problem_class": "db-sql-query-builder-go", "result": "passed", "tests": 33}

Answer 2

The implementation is a type-safe SQL query builder in Go with three key components:

1. Query Builder (`Builder` struct)

Method-chaining API that constructs parameterized SQL SELECT queries:

// Builder supports method chaining for building SELECT queries
qb := sqlqb.Select("u.id", "u.name", "o.total").
    From("users u").
    InnerJoin("orders o", "o.user_id = u.id").
    Where("u.age > $1", 21).
    Where("o.status = $1", "pending").
    OrderBy("u.name ASC").
    Limit(5)

query, args, err := qb.Build()
// query:  "SELECT u.id, u.name, o.total FROM users u\nINNER JOIN orders o ON o.user_id = u.id\nWHERE u.age > $1 AND o.status = $2\nORDER BY u.name ASC\nLIMIT $3"
// args:   [21, "pending", 5]

Select(columns...) — creates a builder with column list
From(table) — sets the source table
InnerJoin/LeftJoin/RightJoin(table, onExpr, params...) — each join type
Where(expr, params...) — conditions combined with AND
OrderBy(columns...) — ORDER BY clause
Limit(count) — LIMIT clause
Build() — returns (sql string, args []interface{}, err)

2. Parameterized Queries (SQL Injection Prevention)

All user values use $N placeholders that are rewritten into global positional parameters. The SQL string never contains raw user data:

qb := Select("*").From("users").Where("name = $1", "'; DROP TABLE users; --")
query, args, _ := qb.Build()
// query: "SELECT * FROM users\nWHERE name = $1"   ← safe, no DROP in SQL
// args:  ["'; DROP TABLE users; --"]              ← malicious string isolated in parameters

The rewriteParams() function renumbers $1, $2 placeholders within each clause into global $1, $2, $3, ... across the entire query, collecting args in order.

3. Result Scanning into Structs (Reflection)

Two scanning functions use the Rows interface (satisfied by *sql.Rows and test mocks):

type User struct {
    ID    int    `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
    Age   int    `db:"age"`
}

// ScanRow — single struct
var u User
err := sqlqb.ScanRow(rows, &u)  // rows is *sql.Rows

// ScanAll — slice of structs
var users []User
err := sqlqb.ScanAll(rows, &users)

// Also works with pointers: []*User
var users []*User
err := sqlqb.ScanAll(rows, &users)

Key files:

~/sqlqb/sqlqb.go — main package (~370 lines)
~/sqlqb/sqlqb_test.go — comprehensive tests (~230 lines)

Evidence & signatures

All 33 tests pass:

```
=== RUN   TestSelectBasic          --- PASS
=== RUN   TestSelectStar           --- PASS
=== RUN   TestWhereWithParams      --- PASS
=== RUN   TestWhereMultipleParamsInOneExpr --- PASS
=== RUN   TestOrderBy              --- PASS
=== RUN   TestLimit                --- PASS
=== RUN   TestFullQuery            --- PASS
=== RUN   TestJoinTypes            --- PASS (4 subtests: inner, left, right, with params)
=== RUN   TestJoinWithParams       --- PASS
=== RUN   TestMultipleJoins        --- PASS
=== RUN   TestNoColumns            --- PASS  (error: no columns)
=== RUN   TestNoFrom               --- PASS  (error: no table)
=== RUN   TestChaining             --- PASS
=== RUN   TestMultipleWhere        --- PASS
=== RUN   TestNoSQLInjectionInParams --- PASS
=== RUN   TestNoSQLInjectionInJoinParams --- PASS
=== RUN   TestScanSingleRow        --- PASS
=== RUN   TestScanAll              --- PASS
=== RUN   TestColumnMappingWithTags --- PASS
=== RUN   TestColumnMappingCaseInsensitive --- PASS
=== RUN   TestColumnMappingIgnoresUnexported --- PASS
=== RUN   TestColumnMappingIgnoresDashTag --- PASS
=== RUN   TestScanWithDifferentColumnOrder --- PASS
=== RUN   TestScanWithExtraColumns --- PASS
=== RUN   TestScanWithSubsetOfColumns --- PASS
=== RUN   TestScanRowNoRows        --- PASS
=== RUN   TestScanRowNonPtrDest    --- PASS
=== RUN   TestScanAllEmptyResult   --- PASS
=== RUN   TestLimitZero            --- PASS
=== RUN   TestWhereSpecialChars    --- PASS
=== RUN   TestWhereInClause        --- PASS
=== RUN   TestAllPlaceholdersCombined --- PASS
=== RUN   TestScanAllPointerSlice  --- PASS
=== RUN   TestConcurrentBuild      --- PASS
```

**Edge cases covered:**
| Edge Case | Status |
|---|---|
| SQL injection via WHERE params (DROP, DELETE) | ✅ Prevented |
| SQL injection via JOIN params | ✅ Prevented |
| No columns selected | ✅ Error |
| No FROM table | ✅ Error |
| Multiple JOINs (INNER + LEFT) | ✅ Works |
| JOIN with parameters | ✅ Works |
| JOIN without parameters | ✅ Works |
| WHERE with multiple params per expression (`$1, $2`) | ✅ Works |
| WHERE with `IN ($1, $2, $3)` | ✅ Works |
| WHERE with `LIKE` and special chars (`%test%`) | ✅ Works |
| LIMIT 0 | ✅ Generates `LIMIT $1` |
| No WHERE clause | ✅ Works |
| No ORDER BY | ✅ Works |
| No LIMIT | ✅ Works |
| Columns in different order than struct fields | ✅ Matches correctly |
| Extra columns from DB not in struct | ✅ Discarded |
| Subset of columns from DB | ✅ Zero values for unscanned |
| No rows returned | ✅ Error |
| Non-pointer destination | ✅ Error |
| Pointer slice (`[]*User`) | ✅ Works |
| Empty result set | ✅ No error, empty slice |
| Concurrent `Build()` calls | ✅ Thread-safe (mutex) |
| Unexported struct fields | ✅ Ignored |
| `db:"-"` tagged fields | ✅ Ignored |

---

{"model": "claude-sonnet-4-20250514", "problem_class": "db-sql-query-builder-go", "result": "passed", "tests": 33}

Generated from the verified corpus · MIT licensedBack to the catalog

db-sql-query-builder-go

Answer 1

1. Query Builder (Builder struct)

2. Parameterized Queries (SQL Injection Prevention)

3. Result Scanning into Structs (Reflection)

Key files:

Evidence & signatures

Answer 2

1. Query Builder (Builder struct)

2. Parameterized Queries (SQL Injection Prevention)

3. Result Scanning into Structs (Reflection)

Key files:

Evidence & signatures

1. Query Builder (`Builder` struct)

1. Query Builder (`Builder` struct)