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

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
})

Model lifecycle hooks (GORM)​

GORM invokes methods on your model type around create, update, and delete. Use them for defaults, validation that depends on the DB session, cache invalidation, and lightweight invariants—keep heavy business rules in services so they stay easy to test.

When you embed model.Base, GORM already maintains ID, ResourceID, timestamps, and soft delete; hooks should not fight that (for example, prefer setting fields the service layer owns rather than duplicating timestamp logic unless you have a specific reason).

BeforeCreate​

func (p *Product) BeforeCreate(tx *gorm.DB) error {
	if p.SKU == "" {
		return fmt.Errorf("sku is required")
	}
	return nil
}

AfterCreate / AfterUpdate / AfterDelete​

Typical uses: notify downstream systems, refresh caches, enqueue work.

func (p *Product) AfterUpdate(tx *gorm.DB) error {
	cache.Delete("product:" + p.ResourceID)
	return nil
}

BeforeUpdate / BeforeDelete​

Use for guarding deletes or logging field-level changes (tx.Statement.Changed("Price")). Return an error to abort the operation.

More background: Hooks & Events (service-level hooks) and the GORM docs for the full callback list.

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​