Skip to content

henriqueatila/golang-gin-best-practices / skills/golang-gin-database

golang-gin-database

Verified

PostgreSQL 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-queries.md

View on GitHub
# GORM Patterns — Scopes and Pagination

Scopes, cursor/keyset pagination. For preloading, raw SQL, batch ops, and PostgreSQL features: see [gorm-patterns-advanced.md](gorm-patterns-advanced.md).

> **Architectural recommendation:** These are mainstream Go/GORM community patterns, not part of the Gin framework API.

## Scopes

Scopes encapsulate reusable query fragments.

```go
// internal/repository/scopes.go
package repository

import "gorm.io/gorm"

// ByRole filters users by role.
func ByRole(role string) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if role == "" {
            return db
        }
        return db.Where("role = ?", role)
    }
}

// Paginate applies offset/limit pagination.
func Paginate(page, limit int) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        if page < 1 {
            page = 1
        }
        if limit < 1 || limit > 100 {
            limit = 20
        }
        return db.Offset((page - 1) * limit).Limit(limit)
    }
}

// Usage in List:
func (r *gormUserRepository) List(ctx context.Context, opts domain.ListOptions) ([]domain.User, int64, error) {
    var models []UserModel
    var total int64

    query := r.db.WithContext(ctx).Model(&UserModel{}).Scopes(ByRole(opts.Role))

    if err := query.Count(&total).Error; err != nil {
        return nil, 0, domain.ErrInternal.New(err)
    }

    if err := query.Scopes(Paginate(opts.Page, opts.Limit)).Find(&models).Error; err != nil {
        return nil, 0, domain.ErrInternal.New(err)
    }

    users := make([]domain.User, len(models))
    for i, m := range models {
        users[i] = *m.ToDomain()
    }
    return users, total, nil
}
```

## Cursor / Keyset Pagination

Offset pagination (`LIMIT x OFFSET y`) performs an O(n) skip. For large or append-heavy tables, use keyset pagination — a single index seek per page.

```go
// domain/user.go — add alongside existing ListOptions
type CursorOptions struct {
    Cursor time.Time // created_at of last item on previous page; zero = first page
    Limit  int
}

// internal/repository/user_repository_gorm.go
func (r *gormUserRepository) ListAfterCursor(ctx context.Context, opts domain.CursorOptions) ([]domain.User, error) {
    limit := opts.Limit
    if limit <= 0 || limit > 100 {
        limit = 20
    }

    var models []UserModel
    q := r.txFromCtx(ctx).WithContext(ctx).Order("created_at ASC").Limit(limit)
    if !opts.Cursor.IsZero() {
        q = q.Where("created_at > ?", opts.Cursor)
    }
    if err := q.Find(&models).Error; err != nil {
        return nil, domain.ErrInternal.New(err)
    }

    users := make([]domain.User, len(models))
    for i, m := range models {
        users[i] = *m.ToDomain()
    }
    return users, nil
}
```

**Index requirement:** `CREATE INDEX idx_users_created_at ON users(created_at ASC);` — the cursor column must be indexed for O(log n) performance.

**Returning the next cursor:** In the handler or service, take `CreatedAt` of the last returned item and encode it (ISO-8601 or Unix timestamp) as `next_cursor` in the response. The client sends it back on the next request.

| | Offset | Keyset |
|---|---|---|
| Complexity | Simple | Slightly more work |
| Performance at depth | O(offset) | O(log n) |
| Row stability | Drifts on insert/delete | Stable |
| Arbitrary page jump | Yes | No |

**Recommendation:** Use offset for small tables and admin UIs; keyset for feeds, audit logs, and any table that grows quickly.

For preloading associations, raw SQL, batch operations, and PostgreSQL-specific features (ON CONFLICT, RETURNING, arrays, JSONB): see [gorm-patterns-advanced.md](gorm-patterns-advanced.md).
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: 119kUpdated Claude CodeCodexVerifiedInstall
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: 119kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall
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: 118kUpdated Claude CodeCodexVerifiedInstall