database

database Package

PostgreSQL connection management with sqlx, connection pooling, and query helpers.

Features

• Connection Pooling: Automatic connection management
• Health Checks: Monitor database connectivity
• Query Helpers: Simplified CRUD operations
• Transaction Support: Safe transaction handling with rollback
• Logging Integration: Automatic query logging with duration
• Context Support: Timeout and cancellation for all operations
• Error Wrapping: Consistent error handling with errors package

Installation

import "github.com/LaRestoOU/laresto-go-common/pkg/database"

Quick Start

Connect to Database

cfg := database.Config{
    Host:     "localhost",
    Port:     5432,
    Database: "customer_db",
    User:     "laresto_app",
    Password: "laresto_app_dev_password",
    SSLMode:  "disable",
}

log := logger.New(logger.Config{...})
db, err := database.Connect(cfg, log)
if err != nil {
    log.Fatal("Failed to connect to database", err)
}
defer db.Close()

Query Single Row

type User struct {
    ID    int64  `db:"id"`
    Email string `db:"email"`
    Name  string `db:"name"`
}

var user User
err := db.GetContext(ctx, &user, 
    "SELECT * FROM users WHERE email = $1", 
    "user@example.com",
)

Query Multiple Rows

var users []User
err := db.SelectContext(ctx, &users, 
    "SELECT * FROM users WHERE created_at > $1 ORDER BY created_at DESC",
    time.Now().AddDate(0, -1, 0),
)

Execute Commands

err := db.ExecContext(ctx,
    "INSERT INTO users (email, name) VALUES ($1, $2)",
    "user@example.com", "John Doe",
)

err = db.ExecContext(ctx,
    "UPDATE users SET name = $1 WHERE id = $2",
    "Jane Doe", userID,
)

err = db.ExecContext(ctx,
    "DELETE FROM users WHERE id = $1",
    userID,
)

Configuration

type Config struct {
    // Connection details
    Host     string // Database host
    Port     int    // Database port
    Database string // Database name
    User     string // Database user
    Password string // Database password
    SSLMode  string // SSL mode (disable, require, verify-ca, verify-full)
    
    // Connection pool settings
    MaxOpenConns    int           // Max open connections (default: 25)
    MaxIdleConns    int           // Max idle connections (default: 5)
    ConnMaxLifetime time.Duration // Max connection lifetime (default: 5m)
    ConnMaxIdleTime time.Duration // Max connection idle time (default: 10m)
}

Connection Pool Sizing

Guidelines:

• MaxOpenConns: Total connections = Services × MaxOpenConns
• MaxIdleConns: Keep 20-30% of MaxOpenConns idle
• ConnMaxLifetime: Rotate connections to prevent stale connections

Example for 5 services:

cfg := database.Config{
    MaxOpenConns:    20,  // 5 services × 20 = 100 total
    MaxIdleConns:    5,   // 25% idle
    ConnMaxLifetime: 5 * time.Minute,
}

Transactions

Basic Transaction

err := db.Transaction(ctx, func(tx *sqlx.Tx) error {
    // Execute queries within transaction
    _, err := tx.Exec(
        "INSERT INTO orders (user_id, total) VALUES ($1, $2)",
        userID, total,
    )
    if err != nil {
        return err // Automatic rollback
    }
    
    _, err = tx.Exec(
        "UPDATE inventory SET quantity = quantity - $1 WHERE product_id = $2",
        quantity, productID,
    )
    if err != nil {
        return err // Automatic rollback
    }
    
    return nil // Automatic commit
})

Transaction with Multiple Operations

err := db.Transaction(ctx, func(tx *sqlx.Tx) error {
    // Insert order
    var orderID int64
    err := tx.QueryRow(
        "INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id",
        userID, total,
    ).Scan(&orderID)
    if err != nil {
        return errors.WrapDB(err)
    }
    
    // Insert order items
    for _, item := range items {
        _, err := tx.Exec(
            "INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ($1, $2, $3, $4)",
            orderID, item.ProductID, item.Quantity, item.Price,
        )
        if err != nil {
            return errors.WrapDB(err)
        }
    }
    
    return nil
})

Named Queries

Use named parameters for complex updates:

type UpdateUser struct {
    ID    int64  `db:"id"`
    Name  string `db:"name"`
    Email string `db:"email"`
}

update := UpdateUser{
    ID:    123,
    Name:  "Jane Doe",
    Email: "jane@example.com",
}

err := db.NamedExecContext(ctx,
    "UPDATE users SET name = :name, email = :email WHERE id = :id",
    update,
)

Health Checks

// Check database connectivity
func (s *Service) HealthCheck() error {
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    defer cancel()
    
    return s.db.HealthCheck(ctx)
}

Database Statistics

Monitor connection pool usage:

stats := db.Stats()
// Returns:
// {
//     "max_open_connections": 25,
//     "open_connections": 10,
//     "in_use": 3,
//     "idle": 7,
//     "wait_count": 0,
//     "wait_duration_ms": 0
// }

Usage Patterns

Repository Pattern

type UserRepository struct {
    db *database.DB
}

func NewUserRepository(db *database.DB) *UserRepository {
    return &UserRepository{db: db}
}

func (r *UserRepository) FindByEmail(ctx context.Context, email string) (*User, error) {
    var user User
    err := r.db.GetContext(ctx, &user,
        "SELECT * FROM users WHERE email = $1",
        email,
    )
    if err != nil {
        return nil, err
    }
    return &user, nil
}

func (r *UserRepository) Create(ctx context.Context, user *User) error {
    return r.db.ExecContext(ctx,
        "INSERT INTO users (email, name, password_hash) VALUES ($1, $2, $3)",
        user.Email, user.Name, user.PasswordHash,
    )
}

func (r *UserRepository) Update(ctx context.Context, user *User) error {
    return r.db.NamedExecContext(ctx,
        "UPDATE users SET name = :name, email = :email WHERE id = :id",
        user,
    )
}

func (r *UserRepository) Delete(ctx context.Context, id int64) error {
    return r.db.ExecContext(ctx,
        "DELETE FROM users WHERE id = $1",
        id,
    )
}

Service Layer

type AuthService struct {
    db   *database.DB
    repo *UserRepository
}

func (s *AuthService) Register(ctx context.Context, req RegisterRequest) (*User, error) {
    // Hash password
    hash, err := bcrypt.GenerateFromPassword([]byte(req.Password), bcrypt.DefaultCost)
    if err != nil {
        return nil, err
    }
    
    // Create user in transaction
    var user User
    err = s.db.Transaction(ctx, func(tx *sqlx.Tx) error {
        // Insert user
        err := tx.QueryRow(
            "INSERT INTO users (email, name, password_hash) VALUES ($1, $2, $3) RETURNING id, email, name, created_at",
            req.Email, req.Name, hash,
        ).Scan(&user.ID, &user.Email, &user.Name, &user.CreatedAt)
        
        return err
    })
    
    if err != nil {
        return nil, err
    }
    
    return &user, nil
}

Best Practices

DO ✅

// Always use context
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
db.GetContext(ctx, &user, query, args...)

// Use parameterized queries (prevent SQL injection)
db.ExecContext(ctx, "SELECT * FROM users WHERE email = $1", email)

// Use transactions for multiple related operations
db.Transaction(ctx, func(tx *sqlx.Tx) error {
    // Multiple operations
    return nil
})

// Close database connection on shutdown
defer db.Close()

// Check errors
if err := db.ExecContext(ctx, query, args...); err != nil {
    return err
}

DON'T ❌

// Don't use string concatenation (SQL injection!)
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// Don't ignore context
db.Get(&user, query) // Missing context!

// Don't forget to close connections
db, _ := database.Connect(cfg, log) // No defer db.Close()!

// Don't create connections in loops
for _, item := range items {
    db, _ := database.Connect(cfg, log) // Wrong!
}

// Don't ignore errors
db.ExecContext(ctx, query, args...) // Error ignored!

Testing

Unit Tests (Mock Database)

// Use interface for mocking
type DB interface {
    GetContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error
}

// Mock implementation
type MockDB struct{}

func (m *MockDB) GetContext(ctx context.Context, dest interface{}, query string, args ...interface{}) error {
    // Return test data
    return nil
}

Integration Tests (Real Database)

func TestUserRepository_Integration(t *testing.T) {
    if testing.Short() {
        t.Skip("Skipping integration test")
    }
    
    // Connect to test database
    cfg := database.Config{
        Host:     "localhost",
        Database: "test_db",
        // ...
    }
    
    db, err := database.Connect(cfg, logger.NewDefault())
    require.NoError(t, err)
    defer db.Close()
    
    // Run migrations
    // Create test data
    // Test operations
    // Cleanup
}

Performance Tips

1. Use connection pooling: Don't create new connections per request
2. Set appropriate timeouts: Use context with timeout
3. Monitor pool stats: Watch for connection exhaustion
4. Use prepared statements: For repeated queries (via sqlx)
5. Batch operations: Use transactions for multiple inserts

Security

Prevent SQL Injection

// ✅ SAFE: Parameterized query
db.ExecContext(ctx, "SELECT * FROM users WHERE email = $1", email)

// ❌ UNSAFE: String concatenation
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

Connection Security

// Production: Use SSL
cfg := database.Config{
    SSLMode: "require", // or "verify-full"
}

// Development: Disable SSL
cfg := database.Config{
    SSLMode: "disable",
}

iOS Developer Notes

Database package is similar to:

• Core Data stack management
• NSPersistentContainer setup
• Managed object context operations

Comparison:

// iOS Core Data
let fetchRequest: NSFetchRequest<User> = User.fetchRequest()
fetchRequest.predicate = NSPredicate(format: "email == %@", email)
let users = try context.fetch(fetchRequest)

// Go database
var user User
db.GetContext(ctx, &user, "SELECT * FROM users WHERE email = $1", email)

Key concepts:

• Connection pool = NSPersistentContainer with multiple contexts
• Transactions = Core Data's performAndWait
• Context = Like NSManagedObjectContext but for cancellation/timeout
• sqlx tags = Like Core Data's @NSManaged properties

License

MIT License - see LICENSE file for details