Database Connection

Dokumentasi koneksi database PostgreSQL dengan GORM.

Overview

Package infra/database menangani koneksi ke PostgreSQL dengan support untuk:

• Master-Replica configuration
• Connection pooling
• Query logging

Global Variables

var (
    DB  *gorm.DB
    err error
)

Functions

DbConnection

Membuat koneksi database dengan master dan replica.

func DbConnection(masterDSN, replicaDSN string) error

Parameters

Parameter	Type	Deskripsi
masterDSN	string	Data Source Name untuk master database
replicaDSN	string	Data Source Name untuk replica database

DSN Format

host=localhost user=postgres password=secret dbname=sisappra port=5432 sslmode=disable

GetDB

Mendapatkan instance database connection.

func GetDB() *gorm.DB

Implementation

func DbConnection(masterDSN, replicaDSN string) error {
    logMode := viper.GetBool("DB_LOG_MODE")
    debug := viper.GetBool("DEBUG")

    loglevel := logger.Silent
    if logMode {
        loglevel = logger.Info
    }

    // Connect to master
    db, err = gorm.Open(postgres.Open(masterDSN), &gorm.Config{
        Logger: logger.Default.LogMode(loglevel),
    })
    
    // Setup replica (only in production)
    if !debug {
        db.Use(dbresolver.Register(dbresolver.Config{
            Replicas: []gorm.Dialector{
                postgres.Open(replicaDSN),
            },
            Policy: dbresolver.RandomPolicy{},
        }))
    }
    
    if err != nil {
        log.Fatalf("Db connection error")
        return err
    }
    
    DB = db
    return nil
}

Master-Replica Architecture

┌─────────────────────────────────────────────────────────────┐
│                        APPLICATION                           │
│                                                              │
│                    ┌─────────────────┐                       │
│                    │      GORM       │                       │
│                    │   DB Resolver   │                       │
│                    └────────┬────────┘                       │
│                             │                                │
│              ┌──────────────┼──────────────┐                │
│              │              │              │                 │
│              ▼              │              ▼                 │
│    ┌─────────────────┐     │     ┌─────────────────┐       │
│    │     MASTER      │     │     │    REPLICA      │       │
│    │  (Read/Write)   │     │     │   (Read Only)   │       │
│    └─────────────────┘     │     └─────────────────┘       │
│                            │                                │
└────────────────────────────┼────────────────────────────────┘
                             │
                   ┌─────────┴─────────┐
                   │   Automatic       │
                   │   Routing:        │
                   │   - Write → Master│
                   │   - Read → Replica│
                   └───────────────────┘

Configuration

Environment Variables

Variable	Type	Default	Deskripsi
DB_LOG_MODE	bool	false	Enable SQL query logging
DEBUG	bool	false	Debug mode (skip replica)

Database DSN Variables

Master:

• MASTER_DB_HOST
• MASTER_DB_PORT
• MASTER_DB_NAME
• MASTER_DB_USER
• MASTER_DB_PASSWORD
• MASTER_SSL_MODE

Replica:

• REPLICA_DB_HOST
• REPLICA_DB_PORT
• REPLICA_DB_NAME
• REPLICA_DB_USER
• REPLICA_DB_PASSWORD
• REPLICA_SSL_MODE

Usage

Initialize Connection

// In main.go
func main() {
    config.SetupConfig()
    
    masterDSN, replicaDSN := config.DbConfiguration()
    
    if err := database.DbConnection(masterDSN, replicaDSN); err != nil {
        log.Fatal("Failed to connect to database")
    }
    
    // ...
}

Use in Repository

package repository

import "api-gateway/infra/database"

func GetUsers() ([]models.User, error) {
    var users []models.User
    err := database.DB.Find(&users).Error
    return users, err
}

Logging

Enable Query Logging

Set di .env:

DB_LOG_MODE=true

Log Output

2026/01/22 10:00:00 [info] [rows:1] SELECT * FROM "users" WHERE "id" = '123'

Connection Pooling

GORM secara otomatis mengelola connection pool. Untuk konfigurasi custom:

sqlDB, _ := db.DB()

// SetMaxIdleConns sets the maximum number of connections in the idle connection pool
sqlDB.SetMaxIdleConns(10)

// SetMaxOpenConns sets the maximum number of open connections to the database
sqlDB.SetMaxOpenConns(100)

// SetConnMaxLifetime sets the maximum amount of time a connection may be reused
sqlDB.SetConnMaxLifetime(time.Hour)

Error Handling

if err := database.DbConnection(masterDSN, replicaDSN); err != nil {
    switch {
    case strings.Contains(err.Error(), "connection refused"):
        log.Fatal("Database server is not running")
    case strings.Contains(err.Error(), "authentication failed"):
        log.Fatal("Invalid database credentials")
    case strings.Contains(err.Error(), "does not exist"):
        log.Fatal("Database does not exist")
    default:
        log.Fatalf("Database connection error: %v", err)
    }
}

Best Practices

1. Use Connection Pooling - Jangan buat koneksi baru setiap request
2. Enable Logging di Development - Untuk debugging query
3. Disable Logging di Production - Untuk performance
4. Monitor Connection Pool - Pantau metrics koneksi
5. Graceful Shutdown - Tutup koneksi saat aplikasi shutdown

   defer func() {
       sqlDB, _ := database.DB.DB()
       sqlDB.Close()
   }()