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/gorm-patterns-advanced.md
View on GitHub# GORM Patterns — Preloading, Raw SQL, Batches, and PostgreSQL Features
Preloading associations, raw SQL, batch operations, and PostgreSQL-specific GORM features.
> **Architectural recommendation:** These are mainstream Go/GORM community patterns, not part of the Gin framework API.
## Preloading Associations
```go
// ProfileModel demonstrates a 1:1 association
type ProfileModel struct {
ID string `gorm:"type:uuid;primaryKey;default:gen_random_uuid()"`
UserID string `gorm:"type:uuid;not null;uniqueIndex"`
Bio string `gorm:"type:text"`
}
type UserWithProfile struct {
UserModel
Profile ProfileModel `gorm:"foreignKey:UserID"`
}
// Preload loads Profile in a single additional query (N+1 safe)
func (r *gormUserRepository) GetWithProfile(ctx context.Context, id string) (*UserWithProfile, error) {
var u UserWithProfile
if err := r.db.WithContext(ctx).
Preload("Profile").
First(&u.UserModel, "id = ?", id).Error; err != nil {
if errors.Is(err, gorm.ErrRecordNotFound) {
return nil, domain.ErrNotFound.New(err)
}
return nil, domain.ErrInternal.New(err)
}
return &u, nil
}
// Preload with condition
r.db.Preload("Orders", "status = ?", "active").Find(&users)
```
**Warning:** `Preload` issues one extra query per association, not one per row (GORM batches it). Avoid preloading large datasets — use `Joins` for filtering.
## Raw SQL
Use raw SQL for complex queries that GORM can't express cleanly.
```go
type UserStats struct {
Role string `gorm:"column:role"`
Count int64 `gorm:"column:count"`
}
func (r *gormUserRepository) StatsByRole(ctx context.Context) ([]UserStats, error) {
var stats []UserStats
if err := r.db.WithContext(ctx).Raw(`
SELECT role, COUNT(*) AS count FROM users
WHERE deleted_at IS NULL GROUP BY role ORDER BY count DESC
`).Scan(&stats).Error; err != nil {
return nil, domain.ErrInternal.New(err)
}
return stats, nil
}
// Exec for mutations
func (r *gormUserRepository) BanUser(ctx context.Context, id string) error {
result := r.db.WithContext(ctx).Exec(
`UPDATE users SET role = 'banned', updated_at = NOW() WHERE id = ?`, id,
)
if result.Error != nil {
return domain.ErrInternal.New(result.Error)
}
if result.RowsAffected == 0 {
return domain.ErrNotFound.New(fmt.Errorf("user %s not found", id))
}
return nil
}
```
## Batch Operations
```go
// CreateInBatches — inserts in chunks to avoid parameter limit
func (r *gormUserRepository) BulkCreate(ctx context.Context, users []domain.User) error {
models := make([]UserModel, len(users))
for i, u := range users {
models[i] = *fromDomain(&u)
}
if err := r.db.WithContext(ctx).CreateInBatches(models, 100).Error; err != nil {
return domain.ErrInternal.New(err)
}
return nil
}
// FindInBatches — process large result sets without loading all rows into memory
func (r *gormUserRepository) ExportAll(ctx context.Context, process func([]domain.User) error) error {
var models []UserModel
return r.db.WithContext(ctx).Model(&UserModel{}).
FindInBatches(&models, 200, func(tx *gorm.DB, batch int) error {
users := make([]domain.User, len(models))
for i, m := range models {
users[i] = *m.ToDomain()
}
return process(users)
}).Error
}
```
## PostgreSQL-Specific Features
```go
import (
"gorm.io/gorm/clause"
"github.com/lib/pq" // pq.StringArray for array columns
"gorm.io/datatypes" // datatypes.JSON for JSONB columns
)
// ON CONFLICT DO NOTHING — idempotent insert
r.db.WithContext(ctx).Clauses(clause.OnConflict{DoNothing: true}).Create(&m)
// ON CONFLICT DO UPDATE (upsert)
r.db.WithContext(ctx).Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "email"}},
DoUpdates: clause.AssignmentColumns([]string{"name", "updated_at"}),
}).Create(&m)
// RETURNING — get generated values after insert
r.db.WithContext(ctx).
Clauses(clause.Returning{Columns: []clause.Column{{Name: "id"}, {Name: "created_at"}}}).
Create(&m)
// Array and JSONB column types
type UserModel struct {
Tags pq.StringArray `gorm:"type:text[]"`
Metadata datatypes.JSON `gorm:"type:jsonb"`
}
```
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 |