henriqueatila/golang-gin-best-practices / skills/golang-gin-database
golang-gin-database
VerifiedPostgreSQL integration for Go Gin with GORM/sqlx. Use when adding a database, writing queries, creating repositories, running migrations, or wiring DB layers in a Gin project.
references/defensive-query-patterns.md
View on GitHub# Defensive Query Patterns — Error Handling, LIKE Escape, Table Aliases
See also: `sqlx-patterns-queries.md`, `setup-repositories.md`
## Rule #1: NEVER Ignore Database Errors
Every `db.Get()`, `db.Select()`, `db.Exec()`, and `db.QueryRow().Scan()` call can fail. Ignoring the error returns zero-values, corrupts responses, and masks outages.
```go
// ❌ DANGEROUS — silently returns zeros if DB is down
r.db.Get(&stats.Total, "SELECT COUNT(*) FROM users WHERE active = true")
r.db.QueryRow("SELECT COUNT(*) ...").Scan(&stats.Active, &stats.Blocked)
// ✅ SAFE — propagates error to handler, which returns 500
if err := r.db.Get(&stats.Total, "SELECT COUNT(*) FROM users WHERE active = true"); err != nil {
return nil, fmt.Errorf("active users count: %w", err)
}
if err := r.db.QueryRow("SELECT COUNT(*) ...").Scan(&stats.Active, &stats.Blocked); err != nil {
return nil, fmt.Errorf("user counts: %w", err)
}
```
**Rule:** If a function returns `error`, you MUST check it. The only exception is fire-and-forget audit logging — and even then, log the error:
```go
// Audit: fire-and-forget but still log failures
if _, err := r.db.Exec("INSERT INTO audit_logs ...", ...); err != nil {
slog.Error("audit log failed", "action", entry.Action, "error", err)
}
```
## Rule #2: Escape LIKE Metacharacters in User Search Input
The `%` and `_` characters are LIKE wildcards. If user input contains them, the query matches unintended patterns. This can cause performance degradation (full table scans) or information leaks.
```go
// ❌ DANGEROUS — user input "100%" becomes "ILIKE '%100%%'" (matches everything with "100")
// But user input "%" becomes "ILIKE '%%%'" (matches EVERYTHING)
fb.add("data::text ILIKE $%d", "%"+params.Search+"%")
// ✅ SAFE — escape metacharacters before wrapping with wildcards
func escapeLike(s string) string {
s = strings.ReplaceAll(s, `\`, `\\`)
s = strings.ReplaceAll(s, `%`, `\%`)
s = strings.ReplaceAll(s, `_`, `\_`)
return s
}
fb.add("data::text ILIKE $%d", "%"+escapeLike(params.Search)+"%")
```
**Rule:** ALWAYS escape `\`, `%`, and `_` in user input before using in LIKE/ILIKE clauses. This applies to both raw SQL and query builders.
## Rule #3: Always Use Table Alias in JOINed Queries
When a query has JOINs, unqualified column names can be ambiguous (both tables have `id`, `created_at`, etc.). Always use the table alias prefix.
```go
// ❌ AMBIGUOUS — "id" exists in both cobrancas and users tables
dataQuery := fmt.Sprintf(`SELECT c.* FROM cobrancas c JOIN users u ON c.user_id = u.id %s %s`,
baseWhere, paginate(params, "", "ORDER BY id DESC"))
// ^^ ^^ ambiguous!
// ✅ EXPLICIT — table alias used everywhere
dataQuery := fmt.Sprintf(`SELECT c.* FROM cobrancas c JOIN users u ON c.user_id = u.id %s %s`,
baseWhere, paginate(params, "c", "ORDER BY c.id DESC"))
// ^^^ ^^^^ clear!
```
**Rule:** In any query with JOINs:
- Pass the main table alias to helper functions (`paginate`, `orderBy`, etc.)
- Use the alias in ORDER BY, WHERE, and SELECT clauses
- Be consistent — if ListByEmpresa uses alias `"v"`, ListByHolding must too
## Rule #4: Validate Sort Fields Against a Whitelist
Dynamic ORDER BY from user input is an SQL injection vector. Always validate against a whitelist of allowed fields.
```go
// ✅ SAFE — only whitelisted fields allowed in ORDER BY
var sortableFields = map[string]bool{
"id": true, "created_at": true, "updated_at": true,
"status": true, "name": true, "email": true,
}
func orderByClause(sort, tableAlias, defaultOrder string) string {
if sort == "" {
return defaultOrder
}
field := strings.TrimPrefix(sort, "-")
if !sortableFields[field] {
return defaultOrder // reject unknown fields
}
dir := "ASC"
if strings.HasPrefix(sort, "-") {
dir = "DESC"
}
prefix := ""
if tableAlias != "" {
prefix = tableAlias + "."
}
return fmt.Sprintf("ORDER BY %s%s %s", prefix, field, dir)
}
```
## Summary Checklist
Before marking a repository method "done," verify:
- [ ] Every `db.Get`, `db.Select`, `db.Exec`, `db.QueryRow().Scan()` error is checked
- [ ] LIKE/ILIKE user input has `%`, `_`, `\` escaped
- [ ] JOINed queries use explicit table aliases in ORDER BY, WHERE, paginate
- [ ] Dynamic ORDER BY validated against a field whitelist
- [ ] Parameterized queries used everywhere (no `fmt.Sprintf` with user input in SQL)
- [ ] Fire-and-forget operations (audit, analytics) log errors via `slog`
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |