Skip to main content

Database & Models

Developer Intermediate

Learn how to define data models and perform database operations using GORM.

Base Model​

All models must embed the Base struct:

import "github.com/sven-victor/ez-console/pkg/model"

type Product struct {
    model.Base
    Name        string  `gorm:"size:128;not null" json:"name"`
    Description string  `gorm:"type:text" json:"description"`
    Price       float64 `gorm:"type:decimal(10,2)" json:"price"`
    Stock       int     `gorm:"default:0" json:"stock"`
}

The Base struct provides:

  • ID - Internal auto-incrementing primary key
  • ResourceID - Public UUID identifier
  • CreatedAt - Creation timestamp
  • UpdatedAt - Last update timestamp
  • DeletedAt - Soft delete timestamp

Model Definition​

Basic Model​

type Category struct {
    model.Base
    Name string `gorm:"size:64;uniqueIndex" json:"name"`
}

Model with Relationships​

type Product struct {
    model.Base
    Name        string    `gorm:"size:128" json:"name"`
    Price       float64   `gorm:"type:decimal(10,2)" json:"price"`
    CategoryID  uint      `json:"-"`
    Category    *Category `gorm:"foreignKey:CategoryID" json:"category,omitempty"`
}

Many-to-Many Relationship​

type User struct {
    model.Base
    Username string  `json:"username"`
    Roles    []Role  `gorm:"many2many:user_roles" json:"roles"`
}

type Role struct {
    model.Base
    Name  string `json:"name"`
    Users []User `gorm:"many2many:user_roles" json:"-"`
}

GORM Tags​

Common tags:

  • gorm:"size:128" - Column size
  • gorm:"not null" - NOT NULL constraint
  • gorm:"uniqueIndex" - Unique index
  • gorm:"default:0" - Default value
  • gorm:"type:text" - Column type
  • gorm:"foreignKey:CategoryID" - Foreign key
  • json:"name" - JSON field name
  • json:"-" - Exclude from JSON

Database Operations​

Create​

product := &Product{
    Name:  "Laptop",
    Price: 999.99,
    Stock: 10,
}
db.Create(product)

Read​

// Find by ResourceID
var product Product
db.Where("resource_id = ?", resourceID).First(&product)

// Find all
var products []Product
db.Find(&products)

// With conditions
db.Where("price > ?", 100).Find(&products)

// With preload
db.Preload("Category").Find(&products)

Update​

// Update single field
db.Model(&product).Update("stock", 20)

// Update multiple fields
db.Model(&product).Updates(map[string]interface{}{
    "name":  "New Name",
    "price": 199.99,
})

// Save (updates all fields)
db.Save(&product)

Delete​

// Soft delete
db.Delete(&product)

// Permanent delete
db.Unscoped().Delete(&product)

Migrations​

Migrations are automatic in EZ-Console:

// Models are automatically migrated on server start
// Add your models in init() function

func init() {
    // Register custom models for migration
    db.AutoMigrate(&Product{}, &Category{})
}

Querying​

Basic Queries​

// Find by ID
db.First(&product, 1)

// Find by ResourceID
db.Where("resource_id = ?", resourceID).First(&product)

// Find with condition
db.Where("price > ?", 100).Find(&products)

// Find with multiple conditions
db.Where("price > ? AND stock > ?", 100, 0).Find(&products)

Joins​

db.Joins("Category").
   Where("categories.name = ?", "Electronics").
   Find(&products)

Pagination​

func (s *ProductService) List(ctx context.Context, page, pageSize int) ([]*Product, int64, error) {
    var products []*Product
    var total int64
    
    query := s.db.Model(&Product{})
    
    // Count total
    query.Count(&total)
    
    // Paginate
    offset := (page - 1) * pageSize
    err := query.Offset(offset).Limit(pageSize).Find(&products).Error
    
    return products, total, err
}

Transactions​

err := db.Transaction(func(tx *gorm.DB) error {
    // Create order
    if err := tx.Create(&order).Error; err != nil {
        return err
    }
    
    // Update stock
    if err := tx.Model(&Product{}).
        Where("id = ?", productID).
        Update("stock", gorm.Expr("stock - ?", quantity)).Error; err != nil {
        return err
    }
    
    return nil
})

Database Configuration​

SQLite (Default)​

database:
  driver: sqlite
  path: ./app.db

MySQL​

database:
  driver: mysql
  host: localhost
  port: 3306
  username: root
  password: password
  dbname: myapp

PostgreSQL​

database:
  driver: postgres
  host: localhost
  port: 5432
  username: postgres
  password: password
  dbname: myapp
  sslmode: disable

Best Practices​

DO ✅​

  1. Always embed model.Base
  2. Use proper GORM tags
  3. Define indexes for frequently queried fields
  4. Use transactions for multi-step operations
  5. Use ResourceID for external APIs
  6. Preload relationships to avoid N+1 queries

DON'T ❌​

  1. Don't expose internal ID in APIs
  2. Don't forget to handle errors
  3. Don't use SELECT * unnecessarily
  4. Don't perform operations in loops (use batch operations)
  5. Don't ignore database constraints

Next Steps​