golang-gin-database

Integrate PostgreSQL databases with Go Gin APIs using GORM or sqlx. Covers repository pattern, connection retry with backoff, cursor/keyset pagination, context-based transactions, TLS/sslmode, migrations, and dependency injection. Use when adding database support, creating models, writing queries, implementing repositories, setting up migrations, or wiring database layers into a Gin project. Also activate when the user mentions GORM, sqlx, database connection, SQL queries, repository pattern, or database migrations in a Go/Gin context.

Safety Notice

This listing is imported from skills.sh public index metadata. Review upstream SKILL.md and repository scripts before running.

Copy this and send it to your AI assistant to learn

Install skill "golang-gin-database" with this command: npx skills add henriqueatila/golang-gin-best-practices/henriqueatila-golang-gin-best-practices-golang-gin-database

golang-gin-database — Database Integration

Integrate PostgreSQL with Gin APIs using the repository pattern. Keeps database logic out of handlers and services, and supports swapping GORM ↔ sqlx without touching business logic.

When to Use

  • Adding a PostgreSQL database to a Gin project
  • Implementing the repository pattern (interface + concrete implementation)
  • Writing GORM or sqlx queries
  • Setting up database connection pooling
  • Running migrations (golang-migrate)
  • Wiring repositories → services → handlers in main.go
  • Writing context-propagating transactions

Repository Interface Pattern

Define the interface in the domain layer; implement it in the repository layer. This inverts the dependency: services depend on an abstraction, not a concrete database library.

Architecture note: In clean architecture, domain entities should not carry json or binding tags. Use separate request/response DTOs in the delivery layer. See golang-gin-clean-arch Golden Rule 4.

// internal/domain/user.go
package domain

import (
    "context"
    "time"
)

type User struct {
    ID           string
    Name         string
    Email        string
    Role         string
    PasswordHash string    // set by service layer before persisting; never serialized to API responses
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

type ListOptions struct {
    Page  int
    Limit int
    Role  string
}

// UserRepository defines the data access contract.
// Implementations live in internal/repository — GORM or sqlx, interchangeable.
type UserRepository interface {
    Create(ctx context.Context, user *User) error
    GetByID(ctx context.Context, id string) (*User, error)
    GetByEmail(ctx context.Context, email string) (*User, error)
    List(ctx context.Context, opts ListOptions) ([]User, int64, error)
    Update(ctx context.Context, user *User) error
    Delete(ctx context.Context, id string) error
}

Why define it at the consumer? The domain package does not import gorm.io/gorm or jmoiron/sqlx. Only the repository package does. This is the Dependency Inversion Principle applied to data access.

Database Connection Setup

// internal/repository/db.go
package repository

import (
    "fmt"
    "log/slog"
    "time"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

type Config struct {
    DSN             string
    MaxOpenConns    int
    MaxIdleConns    int
    ConnMaxLifetime time.Duration
}

// NewGORMDB opens a PostgreSQL connection with connection pool settings.
// Architectural recommendation — not part of the Gin API.
func NewGORMDB(cfg Config, appLogger *slog.Logger) (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(cfg.DSN), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Warn),
    })
    if err != nil {
        return nil, fmt.Errorf("gorm.Open: %w", err)
    }

    sqlDB, err := db.DB()
    if err != nil {
        return nil, fmt.Errorf("db.DB: %w", err)
    }

    sqlDB.SetMaxOpenConns(cfg.MaxOpenConns)       // e.g. 25
    sqlDB.SetMaxIdleConns(cfg.MaxIdleConns)       // e.g. 5
    sqlDB.SetConnMaxLifetime(cfg.ConnMaxLifetime)  // e.g. 5*time.Minute

    if err := sqlDB.Ping(); err != nil {
        return nil, fmt.Errorf("db.Ping: %w", err)
    }

    appLogger.Info("database connected")
    return db, nil
}

// ConnectWithRetry retries the connection with exponential backoff.
// Use during startup when the database container may not be ready yet.
func ConnectWithRetry(dsn string, maxRetries int) (*gorm.DB, error) {
    var db *gorm.DB
    var err error
    for i := 0; i < maxRetries; i++ {
        db, err = gorm.Open(postgres.Open(dsn), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Warn),
        })
        if err == nil {
            sqlDB, err := db.DB()
            if err != nil {
                return nil, fmt.Errorf("failed to get underlying sql.DB: %w", err)
            }
            sqlDB.SetMaxOpenConns(25)
            sqlDB.SetMaxIdleConns(10)
            sqlDB.SetConnMaxLifetime(5 * time.Minute)
            sqlDB.SetConnMaxIdleTime(1 * time.Minute)
            if err := sqlDB.Ping(); err != nil {
                return nil, fmt.Errorf("database ping failed: %w", err)
            }
            return db, nil
        }
        backoff := time.Duration(1<<uint(i)) * time.Second
        slog.Warn("database connection failed, retrying", "attempt", i+1, "backoff", backoff, "error", err)
        time.Sleep(backoff)
    }
    return nil, fmt.Errorf("failed to connect after %d retries: %w", maxRetries, err)
}

TLS / production DSN: Always use sslmode=verify-full in production to prevent MITM attacks.

// Development (local Docker)
dsn := "host=localhost user=app password=secret dbname=myapp sslmode=disable"

// Production: verify the server certificate
dsn := "host=db.example.com user=app password=*** dbname=myapp sslmode=verify-full sslrootcert=/etc/ssl/certs/rds-ca.pem"

For sqlx, see references/sqlx-patterns.md.

GORM Repository Implementation (Brief)

// internal/repository/user_repository_gorm.go
package repository

import (
    "context"
    "errors"

    "gorm.io/gorm"
    "myapp/internal/domain"
)

type gormUserRepository struct {
    db *gorm.DB
}

func NewUserRepository(db *gorm.DB) domain.UserRepository {
    return &gormUserRepository{db: db}
}

func (r *gormUserRepository) GetByID(ctx context.Context, id string) (*domain.User, error) {
    var m UserModel
    if err := r.txFromCtx(ctx).WithContext(ctx).First(&m, "id = ?", id).Error; err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, domain.ErrNotFound.New(err)
        }
        return nil, domain.ErrInternal.New(err)
    }
    return m.ToDomain(), nil
}

For full GORM patterns (models, soft deletes, preloading, transactions, hooks): see references/gorm-patterns.md.

sqlx Repository Implementation (Brief)

// internal/repository/user_repository_sqlx.go
package repository

import (
    "context"
    "database/sql"
    "errors"

    "github.com/jmoiron/sqlx"
    "myapp/internal/domain"
)

type sqlxUserRepository struct {
    db *sqlx.DB
}

func NewSqlxUserRepository(db *sqlx.DB) domain.UserRepository {
    return &sqlxUserRepository{db: db}
}

func (r *sqlxUserRepository) GetByID(ctx context.Context, id string) (*domain.User, error) {
    var u userRow
    err := r.db.GetContext(ctx, &u, `SELECT id, name, email, role, created_at, updated_at FROM users WHERE id = $1`, id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, domain.ErrNotFound.New(err)
        }
        return nil, domain.ErrInternal.New(err)
    }
    return u.toDomain(), nil
}

For full sqlx patterns (struct scanning, NamedExec, IN clauses, transactions): see references/sqlx-patterns.md.

Transaction Pattern (Context-Based)

Pass *gorm.DB via context so repositories transparently participate in a transaction. The service orchestrates; repositories just call txFromCtx.

// internal/repository/tx.go (GORM approach)
package repository

import (
    "context"

    "gorm.io/gorm"
)

type txKey struct{}

// WithTx stores a transaction in ctx so repositories can use it.
func WithTx(ctx context.Context, tx *gorm.DB) context.Context {
    return context.WithValue(ctx, txKey{}, tx)
}

// txFromCtx returns the transaction from ctx, or the default db.
// Every repository method should call this instead of r.db directly.
func (r *gormUserRepository) txFromCtx(ctx context.Context) *gorm.DB {
    if tx, ok := ctx.Value(txKey{}).(*gorm.DB); ok {
        return tx
    }
    return r.db
}

// Create uses txFromCtx — works both standalone and inside a transaction.
func (r *gormUserRepository) Create(ctx context.Context, user *domain.User) error {
    m := fromDomain(user)
    if err := r.txFromCtx(ctx).WithContext(ctx).Create(m).Error; err != nil {
        return mapGORMError(err)
    }
    user.ID = m.ID
    user.CreatedAt = m.CreatedAt
    user.UpdatedAt = m.UpdatedAt
    return nil
}

// Service-layer usage — the service orchestrates the transaction:
// internal/service/user_service.go
func (s *userService) RegisterWithProfile(ctx context.Context, req domain.CreateUserRequest) error {
    return s.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        txCtx := repository.WithTx(ctx, tx)
        if err := s.userRepo.Create(txCtx, &user); err != nil {
            return err // tx rolled back automatically
        }
        return s.profileRepo.Create(txCtx, &profile)
    })
}

Cursor / Keyset Pagination

Offset pagination (LIMIT x OFFSET y) degrades at large offsets because PostgreSQL must skip rows. Keyset pagination is O(log n) via an index seek — preferred for large or fast-growing tables.

See Cursor Pagination in GORM Patterns for implementation.

Dependency Injection in main.go

Wire repositories → services → handlers. Nothing creates its own dependencies.

// cmd/api/main.go
package main

import (
    "log/slog"
    "os"
    "time"

    "myapp/internal/handler"
    "myapp/internal/repository"
    "myapp/internal/service"
)

func main() {
    logger := slog.New(slog.NewJSONHandler(os.Stdout, nil))

    dbCfg := repository.Config{
        DSN:             os.Getenv("DATABASE_URL"),
        MaxOpenConns:    25,
        MaxIdleConns:    5,
        ConnMaxLifetime: 5 * time.Minute,
    }

    db, err := repository.NewGORMDB(dbCfg, logger)
    if err != nil {
        logger.Error("failed to connect to database", "error", err)
        os.Exit(1)
    }

    // Wire the dependency graph: repo → service → handler
    userRepo    := repository.NewUserRepository(db)
    userService := service.NewUserService(userRepo, logger)
    userHandler := handler.NewUserHandler(userService, logger)

    // ... router setup, see gin-api skill
    _ = userHandler
}

Critical: Read DATABASE_URL from environment, never hardcode credentials. See the golang-gin-deploy skill for Docker/Kubernetes secrets.

Reference Files

Load these for deeper detail:

  • references/gorm-patterns.md — GORM model definition, CRUD, soft deletes, scopes, preloading, raw SQL, batch ops, hooks, connection pooling, PostgreSQL-specific features, complete repository implementation
  • references/sqlx-patterns.md — Connection setup, struct scanning, Get/Select/NamedExec, safe dynamic queries, sqlx.In, null handling, query builder, complete repository implementation
  • references/migrations.md — golang-migrate CLI and library usage, file naming, zero-downtime migrations, startup vs CI/CD strategy, seeding, rollback

Cross-Skill References

  • For dependency injection wiring and main.go patterns: see the golang-gin-api skill
  • For testing repositories with a real database: see the golang-gin-testing skill (integration tests)
  • For running migrations in Docker containers: see the golang-gin-deploy skill
  • For user authentication using the UserRepository: see the golang-gin-auth skill
  • golang-gin-clean-arch → Architecture: repository pattern, domain error wrapping, transaction patterns

Official Docs

If this skill doesn't cover your use case, consult the GORM documentation, sqlx GoDoc, or Gin GoDoc.

Source Transparency

This detail page is rendered from real SKILL.md content. Trust labels are metadata-based hints, not a safety guarantee.

Open in GitHubOpen in ClawHub

Related Skills

Related by shared tags or category signals.

General

golang-gin-api

No summary provided by upstream source.

Repository SourceNeeds Review
21-henriqueatila
General

golang-gin-psql-dba

No summary provided by upstream source.

Repository SourceNeeds Review
8-henriqueatila
General

golang-gin-swagger

No summary provided by upstream source.

Repository SourceNeeds Review
7-henriqueatila
General

golang-gin-testing

No summary provided by upstream source.

Repository SourceNeeds Review
7-henriqueatila