GORM #

# GORM core
go get gorm.io/gorm

# Driver — choose based on your database
go get gorm.io/driver/mysql      # MySQL
go get gorm.io/driver/postgres   # PostgreSQL
go get gorm.io/driver/sqlite     # SQLite
go get gorm.io/driver/sqlserver  # SQL Server

import (
    "gorm.io/driver/mysql"
    "gorm.io/driver/postgres"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

// MySQL
dsn := "root:password@tcp(localhost:3306)/onlinestore?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

// PostgreSQL
dsn = "host=localhost user=postgres password=password dbname=onlinestore port=5432 sslmode=disable"
db, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})

// SQLite — ideal for development and testing
db, err = gorm.Open(sqlite.Open("onlinestore.db"), &gorm.Config{})

// Logger configuration
db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info), // show all SQL
})

// Access the *sql.DB behind GORM to set up the pool
sqlDB, err := db.DB()
sqlDB.SetMaxOpenConns(25)
sqlDB.SetMaxIdleConns(25)
sqlDB.SetConnMaxLifetime(5 * time.Minute)

import "gorm.io/gorm"

// gorm.Model includes ID, CreatedAt, UpdatedAt, DeletedAt (soft delete)
type Product struct {
    gorm.Model                    // embed: ID uint, CreatedAt, UpdatedAt, DeletedAt

    Name        string           `gorm:"size:200;not null;uniqueIndex"`
    Description string           `gorm:"type:text"`
    Price       float64          `gorm:"not null;default:0"`
    Stock       int              `gorm:"not null;default:0"`
    CategoryID  uint             `gorm:"not null;index"`
    IsActive    bool             `gorm:"default:true"`

    // Associations
    Category    Category         `gorm:"foreignKey:CategoryID"`
    Tags        []Tag            `gorm:"many2many:product_tags;"`
    Images      []ProductImage   `gorm:"foreignKey:ProductID"`
}

type Category struct {
    gorm.Model
    Name     string    `gorm:"size:100;not null;uniqueIndex"`
    Slug     string    `gorm:"size:100;not null;uniqueIndex"`
    Products []Product `gorm:"foreignKey:CategoryID"`
}

type Tag struct {
    gorm.Model
    Name     string    `gorm:"size:50;not null;uniqueIndex"`
    Products []Product `gorm:"many2many:product_tags;"`
}

type ProductImage struct {
    gorm.Model
    ProductID uint   `gorm:"not null;index"`
    URL       string `gorm:"size:500;not null"`
    IsPrimary bool   `gorm:"default:false"`
}

// A model without gorm.Model — full control over the fields
type Order struct {
    ID         uint      `gorm:"primaryKey;autoIncrement"`
    CreatedAt  time.Time
    UpdatedAt  time.Time
    CustomerID uint      `gorm:"not null;index"`
    Total      float64   `gorm:"not null;default:0"`
    Status     string    `gorm:"size:50;default:'pending'"`
    Note       string    `gorm:"type:text"`

    Customer   Customer  `gorm:"foreignKey:CustomerID"`
    Items      []OrderItem
}

type OrderItem struct {
    ID        uint    `gorm:"primaryKey;autoIncrement"`
    OrderID   uint    `gorm:"not null;index"`
    ProductID uint    `gorm:"not null"`
    Qty       int     `gorm:"not null;default:1"`
    Price     float64 `gorm:"not null"`
    Product   Product `gorm:"foreignKey:ProductID"`
}

type Customer struct {
    gorm.Model
    Name   string  `gorm:"size:100;not null"`
    Email  string  `gorm:"size:100;not null;uniqueIndex"`
    Phone  string  `gorm:"size:20"`
    Orders []Order `gorm:"foreignKey:CustomerID"`
}

// AutoMigrate creates tables, columns, and indexes that don't exist yet
// It does NOT drop existing columns (safe for production)
err := db.AutoMigrate(
    &Category{},
    &Tag{},
    &Product{},
    &ProductImage{},
    &Customer{},
    &Order{},
    &OrderItem{},
)
if err != nil {
    log.Fatal("AutoMigrate:", err)
}

// Create a single record
product := Product{
    Name:       "Pro Laptop 14",
    Price:      15_000_000,
    Stock:      10,
    CategoryID: 1,
}
result := db.Create(&product)
if result.Error != nil {
    log.Fatal("Failed to create:", result.Error)
}
fmt.Println("New ID:", product.ID)  // GORM auto-sets the ID after Create

// Create with selected fields only
db.Select("Name", "Price").Create(&product)

// Create many records at once
products := []Product{
    {Name: "Wireless Mouse", Price: 350_000, Stock: 50, CategoryID: 1},
    {Name: "Mech Keyboard", Price: 1_500_000, Stock: 25, CategoryID: 1},
}
db.Create(&products)
// Each product's ID is filled after Create

// Upsert — create or update on conflict
db.Save(&product)  // inserts if ID=0, updates if ID > 0

// Create or update based on a specific field
db.Where(Product{Name: "Pro Laptop 14"}).
    Attrs(Product{Stock: 10}).    // only set if it's a new record
    FirstOrCreate(&product)

// Get a single record by primary key
var p Product
db.First(&p, 1)                           // WHERE id = 1 ORDER BY id
db.First(&p, "id = ?", 1)                 // equivalent
db.Take(&p, 1)                            // without ORDER BY — faster

// With error handling
result := db.First(&p, 999)
if errors.Is(result.Error, gorm.ErrRecordNotFound) {
    fmt.Println("Product not found")
}

// Find — many records
var products []Product
db.Find(&products)                        // all products
db.Find(&products, "category_id = ?", 1)  // with a condition

// Where — various ways
db.Where("name = ?", "Pro Laptop 14").First(&p)
db.Where("price BETWEEN ? AND ?", 100_000, 5_000_000).Find(&products)
db.Where("name LIKE ?", "%laptop%").Find(&products)
db.Where("stock > ? AND is_active = ?", 0, true).Find(&products)

// Where with a struct — only non-zero fields are used
db.Where(&Product{CategoryID: 1, IsActive: true}).Find(&products)

// Where with a map — more explicit, including zero values
db.Where(map[string]interface{}{
    "category_id": 1,
    "is_active":   true,
    "stock":       0,  // can use 0 as a value
}).Find(&products)

// Select specific fields
db.Select("id", "name", "price").Find(&products)

// Order, Limit, Offset
db.Order("price DESC").Limit(10).Offset(20).Find(&products)

// Count
var count int64
db.Model(&Product{}).Where("category_id = ?", 1).Count(&count)

// Pluck — get one column as a slice
var names []string
db.Model(&Product{}).Pluck("name", &names)

// Scan into a custom struct (for JOIN/aggregate results)
type ProductSummary struct {
    CategoryName string
    Count        int
    AvgPrice     float64
}
var summary []ProductSummary
db.Model(&Product{}).
    Select("categories.name AS category_name, COUNT(*) AS count, AVG(price) AS avg_price").
    Joins("JOIN categories ON categories.id = products.category_id").
    Group("categories.name").
    Scan(&summary)

// Update all changed fields (Save)
p.Price = 14_500_000
p.Stock = 8
db.Save(&p)  // UPDATE all non-zero columns

// Update specific fields only
db.Model(&p).Update("price", 14_500_000)
db.Model(&p).Updates(Product{Price: 14_500_000, Stock: 8})
db.Model(&p).Updates(map[string]interface{}{
    "price": 14_500_000,
    "stock": 0,  // can update to zero with a map
})

// Update without fetching first (more efficient)
db.Model(&Product{}).Where("category_id = ?", 1).
    Update("is_active", false)

// Update with a SQL expression
db.Model(&Product{}).Where("id = ?", 1).
    UpdateColumn("stock", gorm.Expr("stock - ?", 5))

// Soft delete — sets DeletedAt, the data isn't actually removed
db.Delete(&p)             // SET deleted_at = NOW()
db.Delete(&Product{}, 1)  // with an ID

// Hard delete — permanent removal
db.Unscoped().Delete(&p)

// Delete with a condition
db.Where("stock = 0").Delete(&Product{})

// Query soft-deleted data
var products []Product
db.Unscoped().Where("deleted_at IS NOT NULL").Find(&products)

// Load one association
var product Product
db.Preload("Category").First(&product, 1)
// SELECT * FROM products WHERE id=1;
// SELECT * FROM categories WHERE id=product.CategoryID;

// Load many associations at once
db.Preload("Category").Preload("Tags").Preload("Images").First(&product, 1)

// Nested preload
db.Preload("Orders.Items.Product").First(&customer, 1)

// Preload with a condition
db.Preload("Images", "is_primary = ?", true).Find(&products)

// Preload everything (careful — N+1 queries!)
db.Preload(clause.Associations).First(&product, 1)

// Create a parent and its children at once
product := Product{
    Name:  "Gaming Laptop",
    Price: 20_000_000,
    Tags: []Tag{
        {Name: "gaming"},
        {Name: "laptop"},
    },
    Images: []ProductImage{
        {URL: "https://img.example.com/laptop.jpg", IsPrimary: true},
    },
}
db.Create(&product)  // GORM auto-creates all associations

// Add an association to an existing record
var tags []Tag
db.Find(&tags, []uint{1, 2, 3})
db.Model(&product).Association("Tags").Append(&tags)

// Remove an association (many2many — only deletes the join table row)
db.Model(&product).Association("Tags").Delete(&tags)

// Replace all associations
db.Model(&product).Association("Tags").Replace(&newTags)

type Product struct {
    gorm.Model
    Name  string
    Price float64
    Slug  string
}

// BeforeCreate — runs before INSERT
func (p *Product) BeforeCreate(tx *gorm.DB) error {
    // Validation
    if p.Price < 0 {
        return errors.New("price cannot be negative")
    }
    // Auto-generate a slug from the name
    p.Slug = slug.Make(p.Name)
    return nil
}

// AfterCreate — runs after a successful INSERT
func (p *Product) AfterCreate(tx *gorm.DB) error {
    // Send a notification, update a cache, etc.
    log.Printf("New product created: %s (ID: %d)", p.Name, p.ID)
    return nil
}

// BeforeUpdate — validation before UPDATE
func (p *Product) BeforeUpdate(tx *gorm.DB) error {
    if p.Price < 0 {
        return errors.New("price cannot be negative")
    }
    return nil
}

// BeforeDelete — runs before DELETE
func (p *Product) BeforeDelete(tx *gorm.DB) error {
    // Check whether any active orders use this product
    var count int64
    tx.Model(&OrderItem{}).
        Joins("JOIN orders ON orders.id = order_items.order_id").
        Where("order_items.product_id = ? AND orders.status != 'completed'", p.ID).
        Count(&count)
    if count > 0 {
        return fmt.Errorf("cannot delete — there are %d active orders", count)
    }
    return nil
}

// Automatic transaction with a closure
err := db.Transaction(func(tx *gorm.DB) error {
    // Use tx (not db) inside the transaction
    if err := tx.Create(&order).Error; err != nil {
        return err  // auto rollback if an error is returned
    }

    for _, item := range order.Items {
        // Reduce the stock
        result := tx.Model(&Product{}).
            Where("id = ? AND stock >= ?", item.ProductID, item.Qty).
            UpdateColumn("stock", gorm.Expr("stock - ?", item.Qty))

        if result.Error != nil {
            return result.Error
        }
        if result.RowsAffected == 0 {
            return fmt.Errorf("insufficient stock for product %d", item.ProductID)
        }
    }

    return nil  // commit if there are no errors
})

// Manual transaction
tx := db.Begin()
defer func() {
    if r := recover(); r != nil {
        tx.Rollback()
    }
}()

if err := tx.Create(&order).Error; err != nil {
    tx.Rollback()
    return err
}
tx.Commit()

// Scope definition — a function that modifies a query
func Active(db *gorm.DB) *gorm.DB {
    return db.Where("is_active = ?", true)
}

func InStock(db *gorm.DB) *gorm.DB {
    return db.Where("stock > 0")
}

func ByCategory(categoryID uint) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("category_id = ?", categoryID)
    }
}

func Paginate(page, perPage int) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        offset := (page - 1) * perPage
        return db.Offset(offset).Limit(perPage)
    }
}

func PriceRange(min, max float64) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("price BETWEEN ? AND ?", min, max)
    }
}

// Usage — chainable
var products []Product
db.Scopes(Active, InStock, ByCategory(1), Paginate(1, 10)).
    Order("price ASC").
    Find(&products)

// With a price range
db.Scopes(Active, PriceRange(100_000, 5_000_000)).
    Find(&products)

// Raw query into a struct
type RevenueReport struct {
    Month    string
    Category string
    Revenue  float64
    Orders   int
}

var report []RevenueReport
db.Raw(`
    SELECT
        DATE_FORMAT(o.created_at, '%Y-%m') AS month,
        c.name AS category,
        SUM(oi.price * oi.qty) AS revenue,
        COUNT(DISTINCT o.id) AS orders
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON p.id = oi.product_id
    JOIN categories c ON c.id = p.category_id
    WHERE o.status = 'completed'
      AND o.created_at >= ?
    GROUP BY month, c.name
    ORDER BY month DESC, revenue DESC
`, time.Now().AddDate(0, -6, 0)).Scan(&report)

// Exec for DDL / DML that doesn't return rows
db.Exec("UPDATE products SET stock = 0 WHERE deleted_at IS NOT NULL")
db.Exec("CREATE INDEX IF NOT EXISTS idx_price ON products(price)")

package main

import (
    "errors"
    "fmt"
    "log"
    "time"

    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
    "gorm.io/gorm/logger"
)

// ── Models ────────────────────────────────────────────────────

type Category struct {
    gorm.Model
    Name     string    `gorm:"size:100;not null;uniqueIndex"`
    Products []Product `gorm:"foreignKey:CategoryID"`
}

type Product struct {
    gorm.Model
    Name       string   `gorm:"size:200;not null"`
    Price      float64  `gorm:"not null;default:0"`
    Stock      int      `gorm:"not null;default:0"`
    CategoryID uint     `gorm:"not null;index"`
    IsActive   bool     `gorm:"default:true"`
    Category   Category `gorm:"foreignKey:CategoryID"`
}

func (p *Product) BeforeCreate(tx *gorm.DB) error {
    if p.Price < 0 {
        return errors.New("price cannot be negative")
    }
    return nil
}

type Customer struct {
    gorm.Model
    Name   string  `gorm:"size:100;not null"`
    Email  string  `gorm:"size:100;not null;uniqueIndex"`
    Orders []Order `gorm:"foreignKey:CustomerID"`
}

type Order struct {
    gorm.Model
    CustomerID uint        `gorm:"not null;index"`
    Total      float64     `gorm:"not null;default:0"`
    Status     string      `gorm:"size:50;default:'pending'"`
    Customer   Customer    `gorm:"foreignKey:CustomerID"`
    Items      []OrderItem `gorm:"foreignKey:OrderID"`
}

type OrderItem struct {
    gorm.Model
    OrderID   uint    `gorm:"not null;index"`
    ProductID uint    `gorm:"not null"`
    Qty       int     `gorm:"not null;default:1"`
    Price     float64 `gorm:"not null"`
    Product   Product `gorm:"foreignKey:ProductID"`
}

// ── Scopes ────────────────────────────────────────────────────

func Active(db *gorm.DB) *gorm.DB  { return db.Where("is_active = ?", true) }
func InStock(db *gorm.DB) *gorm.DB { return db.Where("stock > 0") }
func Paginate(page, perPage int) func(*gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Offset((page - 1) * perPage).Limit(perPage)
    }
}

// ── Service ───────────────────────────────────────────────────

func placeOrder(db *gorm.DB, customerID uint, items []OrderItem) (*Order, error) {
    var order Order

    err := db.Transaction(func(tx *gorm.DB) error {
        // Calculate the total and validate stock
        total := 0.0
        for i := range items {
            var p Product
            if err := tx.First(&p, items[i].ProductID).Error; err != nil {
                return fmt.Errorf("product %d not found", items[i].ProductID)
            }
            if p.Stock < items[i].Qty {
                return fmt.Errorf("insufficient stock for %s (available: %d)", p.Name, p.Stock)
            }
            items[i].Price = p.Price
            total += p.Price * float64(items[i].Qty)

            // Reduce the stock
            if err := tx.Model(&p).UpdateColumn("stock",
                gorm.Expr("stock - ?", items[i].Qty)).Error; err != nil {
                return err
            }
        }

        // Create the order
        order = Order{
            CustomerID: customerID,
            Total:      total,
            Status:     "pending",
            Items:      items,
        }
        return tx.Create(&order).Error
    })

    return &order, err
}

// ── Main ──────────────────────────────────────────────────────

func main() {
    db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Warn),
    })
    if err != nil {
        log.Fatal(err)
    }

    // Migrate
    db.AutoMigrate(&Category{}, &Product{}, &Customer{}, &Order{}, &OrderItem{})

    // Seed categories
    categories := []Category{
        {Name: "Electronics"},
        {Name: "Fashion"},
        {Name: "Books"},
    }
    db.Create(&categories)

    // Seed products
    products := []Product{
        {Name: "Pro Laptop 14", Price: 15_000_000, Stock: 10, CategoryID: categories[0].ID},
        {Name: "Wireless Mouse", Price: 350_000, Stock: 50, CategoryID: categories[0].ID},
        {Name: "Mech Keyboard", Price: 1_500_000, Stock: 25, CategoryID: categories[0].ID},
        {Name: "Plain T-Shirt", Price: 85_000, Stock: 100, CategoryID: categories[1].ID},
        {Name: "Go Programming Book", Price: 180_000, Stock: 30, CategoryID: categories[2].ID},
    }
    db.Create(&products)

    // Seed a customer
    customer := Customer{Name: "Budi Santoso", Email: "[email protected]"}
    db.Create(&customer)

    fmt.Println("=== Data Seeded ===")

    // Query with scopes and preload
    fmt.Println("\n=== Active In-Stock Products (Electronics) ===")
    var electronics []Product
    db.Scopes(Active, InStock).
        Where("category_id = ?", categories[0].ID).
        Preload("Category").
        Order("price ASC").
        Find(&electronics)
    for _, p := range electronics {
        fmt.Printf("  %-20s %-12s Rp%.0f (stock: %d)\n",
            p.Name, p.Category.Name, p.Price, p.Stock)
    }

    // Place an order
    fmt.Println("\n=== Place Order ===")
    order, err := placeOrder(db, customer.ID, []OrderItem{
        {ProductID: products[0].ID, Qty: 1},
        {ProductID: products[1].ID, Qty: 2},
    })
    if err != nil {
        fmt.Println("  Order failed:", err)
    } else {
        fmt.Printf("  Order #%d successful! Total: Rp%.0f\n", order.ID, order.Total)
    }

    // Load the order with all associations
    fmt.Println("\n=== Order Details ===")
    var loadedOrder Order
    db.Preload(clause.Associations).
        Preload("Items.Product").
        First(&loadedOrder, order.ID)

    fmt.Printf("  Order #%d — %s (Total: Rp%.0f)\n",
        loadedOrder.ID, loadedOrder.Customer.Name, loadedOrder.Total)
    for _, item := range loadedOrder.Items {
        fmt.Printf("    - %-20s x%d @ Rp%.0f = Rp%.0f\n",
            item.Product.Name, item.Qty,
            item.Price, item.Price*float64(item.Qty))
    }

    // Check the stock after the order
    fmt.Println("\n=== Stock After Order ===")
    db.Where("id IN ?", []uint{products[0].ID, products[1].ID}).Find(&products[:2])
    for _, p := range products[:2] {
        db.First(&p, p.ID)
        fmt.Printf("  %-20s stock: %d\n", p.Name, p.Stock)
    }

    // Soft delete
    fmt.Println("\n=== Soft Delete ===")
    db.Delete(&products[4])  // delete "Go Programming Book"
    var count int64
    db.Model(&Product{}).Count(&count)
    fmt.Printf("  Active products: %d (1 has been soft-deleted)\n", count)

    // Query including soft-deleted records
    db.Unscoped().Model(&Product{}).Count(&count)
    fmt.Printf("  Total including deleted: %d\n", count)

    // Statistics with raw SQL
    fmt.Println("\n=== Statistics per Category ===")
    type CatStats struct {
        CategoryName string
        ProductCount int64
        TotalStock   int64
        AvgPrice     float64
    }
    var stats []CatStats
    db.Model(&Product{}).
        Select("categories.name AS category_name, COUNT(*) AS product_count, SUM(products.stock) AS total_stock, AVG(products.price) AS avg_price").
        Joins("JOIN categories ON categories.id = products.category_id").
        Group("categories.name").
        Scan(&stats)

    for _, s := range stats {
        fmt.Printf("  %-12s: %d products, %d stock, avg Rp%.0f\n",
            s.CategoryName, s.ProductCount, s.TotalStock, s.AvgPrice)
    }
}