Database

GOE provides seamless database integration through GORM, supporting multiple database drivers and offering features like connection pooling, migrations, and lifecycle management.

Overview

The database module provides:

GORM Integration: Full ORM capabilities with associations, hooks, and transactions
Multiple Drivers: Support for PostgreSQL, MySQL, SQLite, and SQL Server
Connection Pooling: Configurable connection pool settings
Auto-Migration: Automatic schema migration in development
Lifecycle Management: Proper connection setup and teardown

Supported Databases

PostgreSQL (postgres, pgsql, postgresql)
MySQL (mysql)
SQLite (sqlite, sqlite3)
SQL Server (sqlserver, mssql)

Configuration

Configure your database through environment variables:

bash

# PostgreSQL
DB_DRIVER=postgres
DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp
DB_USER=postgres
DB_PASSWORD=secret
DB_SSL_MODE=disable
DB_TIMEZONE=UTC

# MySQL
DB_DRIVER=mysql
DB_HOST=localhost
DB_PORT=3306
DB_NAME=myapp
DB_USER=root
DB_PASSWORD=secret
DB_CHARSET=utf8mb4
DB_PARSE_TIME=true
DB_TIMEZONE=Local

# SQLite
DB_DRIVER=sqlite
DB_PATH=./database.db

# SQL Server
DB_DRIVER=sqlserver
DB_HOST=localhost
DB_PORT=1433
DB_NAME=myapp
DB_USER=sa
DB_PASSWORD=secret

Enabling Database Module

Enable the database module in your GOE application:

package main

import "go.oease.dev/goe/v2"

func main() {
    goe.New(goe.Options{
        WithDB: true,
    })
    
    goe.Run()
}

Accessing the Database

Using Global Accessor

import "go.oease.dev/goe/v2"

func someFunction() {
    db := goe.DB()
    
    var users []User
    db.Instance().Find(&users)
}

Using Dependency Injection

import "go.oease.dev/goe/v2/contract"

type UserRepository struct {
    db contract.DB
}

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

func (r *UserRepository) FindAll() ([]User, error) {
    var users []User
    err := r.db.Instance().Find(&users).Error
    return users, err
}

Models

Define your database models using GORM conventions:

package models

import (
    "time"
    "gorm.io/gorm"
)

type User struct {
    ID        uint      `gorm:"primaryKey"`
    Name      string    `gorm:"size:100;not null"`
    Email     string    `gorm:"size:100;uniqueIndex;not null"`
    Password  string    `gorm:"size:255;not null"`
    Active    bool      `gorm:"default:true"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`
    
    // Associations
    Posts []Post `gorm:"foreignKey:UserID"`
}

type Post struct {
    ID        uint      `gorm:"primaryKey"`
    Title     string    `gorm:"size:200;not null"`
    Content   string    `gorm:"type:text"`
    UserID    uint      `gorm:"not null"`
    User      User      `gorm:"foreignKey:UserID"`
    CreatedAt time.Time
    UpdatedAt time.Time
}

CRUD Operations

Create

func (r *UserRepository) Create(user *User) error {
    return r.db.Instance().Create(user).Error
}

func (r *UserRepository) CreateInBatches(users []User) error {
    return r.db.Instance().CreateInBatches(users, 100).Error
}

Read

func (r *UserRepository) FindByID(id uint) (*User, error) {
    var user User
    err := r.db.Instance().First(&user, id).Error
    if err != nil {
        return nil, err
    }
    return &user, nil
}

func (r *UserRepository) FindByEmail(email string) (*User, error) {
    var user User
    err := r.db.Instance().Where("email = ?", email).First(&user).Error
    if err != nil {
        return nil, err
    }
    return &user, nil
}

func (r *UserRepository) FindAll() ([]User, error) {
    var users []User
    err := r.db.Instance().Find(&users).Error
    return users, err
}

func (r *UserRepository) FindActiveUsers() ([]User, error) {
    var users []User
    err := r.db.Instance().Where("active = ?", true).Find(&users).Error
    return users, err
}

Update

func (r *UserRepository) Update(user *User) error {
    return r.db.Instance().Save(user).Error
}

func (r *UserRepository) UpdateFields(id uint, updates map[string]interface{}) error {
    return r.db.Instance().Model(&User{}).Where("id = ?", id).Updates(updates).Error
}

Delete

func (r *UserRepository) Delete(id uint) error {
    return r.db.Instance().Delete(&User{}, id).Error
}

func (r *UserRepository) SoftDelete(id uint) error {
    return r.db.Instance().Delete(&User{}, id).Error // GORM soft delete
}

func (r *UserRepository) HardDelete(id uint) error {
    return r.db.Instance().Unscoped().Delete(&User{}, id).Error
}

Query Building

Advanced Queries

func (r *UserRepository) FindUsersWithPosts() ([]User, error) {
    var users []User
    err := r.db.Instance().
        Preload("Posts").
        Find(&users).Error
    return users, err
}

func (r *UserRepository) FindUsersByAgeRange(minAge, maxAge int) ([]User, error) {
    var users []User
    err := r.db.Instance().
        Where("age BETWEEN ? AND ?", minAge, maxAge).
        Order("age DESC").
        Find(&users).Error
    return users, err
}

func (r *UserRepository) FindUsersWithPagination(page, limit int) ([]User, int64, error) {
    var users []User
    var total int64
    
    offset := (page - 1) * limit
    
    err := r.db.Instance().Model(&User{}).Count(&total).Error
    if err != nil {
        return nil, 0, err
    }
    
    err = r.db.Instance().
        Offset(offset).
        Limit(limit).
        Find(&users).Error
    
    return users, total, err
}

Raw Queries

func (r *UserRepository) GetUserStats() (map[string]interface{}, error) {
    var result map[string]interface{}
    
    err := r.db.Instance().
        Raw("SELECT COUNT(*) as total_users, AVG(age) as avg_age FROM users").
        Scan(&result).Error
    
    return result, err
}

Transactions

func (r *UserRepository) CreateUserWithPost(user *User, post *Post) error {
    return r.db.Instance().Transaction(func(tx *gorm.DB) error {
        // Create user
        if err := tx.Create(user).Error; err != nil {
            return err
        }
        
        // Set user ID for post
        post.UserID = user.ID
        
        // Create post
        if err := tx.Create(post).Error; err != nil {
            return err
        }
        
        return nil
    })
}

Migrations

Auto-Migration

GOE can automatically migrate your models:

func RegisterMigrations(db contract.DB) {
    // Auto-migrate models
    db.Instance().AutoMigrate(
        &User{},
        &Post{},
        &Category{},
    )
}

func main() {
    goe.New(goe.Options{
        WithDB: true,
        Invokers: []any{RegisterMigrations},
    })
    
    goe.Run()
}

Manual Migrations

For production, use manual migrations:

func RunMigrations(db contract.DB) error {
    // Create users table
    if !db.Instance().Migrator().HasTable(&User{}) {
        if err := db.Instance().Migrator().CreateTable(&User{}); err != nil {
            return err
        }
    }
    
    // Add column
    if !db.Instance().Migrator().HasColumn(&User{}, "last_login") {
        if err := db.Instance().Migrator().AddColumn(&User{}, "last_login"); err != nil {
            return err
        }
    }
    
    return nil
}

Connection Pooling

Configure connection pool settings:

bash

# Connection pool settings
DB_MAX_OPEN_CONNS=25
DB_MAX_IDLE_CONNS=5
DB_MAX_CONN_LIFETIME=300s
DB_MAX_IDLE_TIME=60s

Database Service Pattern

Create a database service for complex operations:

type UserService struct {
    db     contract.DB
    logger contract.Logger
}

func NewUserService(db contract.DB, logger contract.Logger) *UserService {
    return &UserService{db: db, logger: logger}
}

func (s *UserService) CreateUser(name, email, password string) (*User, error) {
    s.logger.Info("Creating user", "email", email)
    
    // Hash password
    hashedPassword, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
    if err != nil {
        s.logger.Error("Failed to hash password", "error", err)
        return nil, err
    }
    
    user := &User{
        Name:     name,
        Email:    email,
        Password: string(hashedPassword),
        Active:   true,
    }
    
    if err := s.db.Instance().Create(user).Error; err != nil {
        s.logger.Error("Failed to create user", "error", err, "email", email)
        return nil, err
    }
    
    s.logger.Info("User created successfully", "user_id", user.ID, "email", email)
    return user, nil
}

func (s *UserService) AuthenticateUser(email, password string) (*User, error) {
    var user User
    
    err := s.db.Instance().Where("email = ? AND active = ?", email, true).First(&user).Error
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, ErrInvalidCredentials
        }
        return nil, err
    }
    
    if err := bcrypt.CompareHashAndPassword([]byte(user.Password), []byte(password)); err != nil {
        return nil, ErrInvalidCredentials
    }
    
    return &user, nil
}

HTTP Integration

Use database services in HTTP handlers:

type UserHandler struct {
    userService *UserService
    logger      contract.Logger
}

func NewUserHandler(userService *UserService, logger contract.Logger) *UserHandler {
    return &UserHandler{
        userService: userService,
        logger:      logger,
    }
}

func (h *UserHandler) GetUsers(c fiber.Ctx) error {
    page := c.QueryInt("page", 1)
    limit := c.QueryInt("limit", 10)
    
    users, total, err := h.userService.GetUsersWithPagination(page, limit)
    if err != nil {
        h.logger.Error("Failed to get users", "error", err)
        return c.Status(500).JSON(fiber.Map{
            "error": "Internal server error",
        })
    }
    
    return c.JSON(fiber.Map{
        "data":  users,
        "total": total,
        "page":  page,
        "limit": limit,
    })
}

func (h *UserHandler) CreateUser(c fiber.Ctx) error {
    var req CreateUserRequest
    
    if err := c.BodyParser(&req); err != nil {
        return c.Status(400).JSON(fiber.Map{
            "error": "Invalid request body",
        })
    }
    
    user, err := h.userService.CreateUser(req.Name, req.Email, req.Password)
    if err != nil {
        h.logger.Error("Failed to create user", "error", err)
        return c.Status(500).JSON(fiber.Map{
            "error": "Failed to create user",
        })
    }
    
    return c.Status(201).JSON(user)
}

Testing

Database Testing

func TestUserRepository(t *testing.T) {
    // Setup test database
    db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
    require.NoError(t, err)
    
    // Auto-migrate
    err = db.AutoMigrate(&User{})
    require.NoError(t, err)
    
    // Create repository
    mockDB := &MockDB{instance: db}
    repo := NewUserRepository(mockDB)
    
    // Test create
    user := &User{
        Name:  "John Doe",
        Email: "john@example.com",
    }
    
    err = repo.Create(user)
    assert.NoError(t, err)
    assert.NotZero(t, user.ID)
    
    // Test find
    found, err := repo.FindByEmail("john@example.com")
    assert.NoError(t, err)
    assert.Equal(t, "John Doe", found.Name)
}

Mock Database

type MockDB struct {
    instance *gorm.DB
}

func (m *MockDB) Instance() *gorm.DB {
    return m.instance
}

func (m *MockDB) IsConnected() bool {
    return true
}

Best Practices

1. Use Repository Pattern

type UserRepository interface {
    Create(user *User) error
    FindByID(id uint) (*User, error)
    FindByEmail(email string) (*User, error)
    Update(user *User) error
    Delete(id uint) error
}

type userRepository struct {
    db contract.DB
}

func NewUserRepository(db contract.DB) UserRepository {
    return &userRepository{db: db}
}

2. Handle Errors Properly

func (r *userRepository) FindByID(id uint) (*User, error) {
    var user User
    err := r.db.Instance().First(&user, id).Error
    
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            return nil, ErrUserNotFound
        }
        return nil, err
    }
    
    return &user, nil
}

func (s *UserService) CreateUserWithProfile(userData UserData, profileData ProfileData) error {
    return s.db.Instance().Transaction(func(tx *gorm.DB) error {
        user := &User{Name: userData.Name, Email: userData.Email}
        if err := tx.Create(user).Error; err != nil {
            return err
        }
        
        profile := &Profile{UserID: user.ID, Bio: profileData.Bio}
        if err := tx.Create(profile).Error; err != nil {
            return err
        }
        
        return nil
    })
}

4. Use Indexes for Performance

type User struct {
    ID    uint   `gorm:"primaryKey"`
    Email string `gorm:"size:100;uniqueIndex;not null"`
    Name  string `gorm:"size:100;index"`
}

Performance Optimization

1. Use Preloading

// Preload associations
var users []User
db.Preload("Posts").Find(&users)

// Preload with conditions
db.Preload("Posts", "published = ?", true).Find(&users)

2. Use Select

// Select specific fields
var users []User
db.Select("id", "name", "email").Find(&users)

3. Use Batching

// Batch operations
db.CreateInBatches(users, 100)

Next Steps

Caching - Add caching to database operations
Testing - Test database operations
Best Practices - Database development best practices

Database ​

Overview ​

Supported Databases ​

Configuration ​

Enabling Database Module ​

Accessing the Database ​

Using Global Accessor ​

Using Dependency Injection ​

Models ​

CRUD Operations ​

Create ​

Read ​

Update ​

Delete ​

Query Building ​

Advanced Queries ​

Raw Queries ​

Transactions ​

Migrations ​

Auto-Migration ​

Manual Migrations ​

Connection Pooling ​

Database Service Pattern ​

HTTP Integration ​

Testing ​

Database Testing ​

Mock Database ​

Best Practices ​

1. Use Repository Pattern ​

2. Handle Errors Properly ​

3. Use Transactions for Related Operations ​

4. Use Indexes for Performance ​

Performance Optimization ​

1. Use Preloading ​

2. Use Select ​

3. Use Batching ​

Next Steps ​