go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite    # SQLite
go get -u gorm.io/driver/postgres  # PostgreSQL
go get -u gorm.io/driver/mysql     # MySQL
go get -u gorm.io/driver/sqlserver # SQL Server

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

// SQLite
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})

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

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

// With Logger
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info), // log all SQL
})

import "gorm.io/gorm"

// Basic model — gorm.Model adds ID, CreatedAt, UpdatedAt, DeletedAt
type User struct {
    gorm.Model
    Name  string
    Email string `gorm:"uniqueIndex"`
    Age   int
}

// Custom primary key
type Product struct {
    ID    uint   `gorm:"primaryKey"`
    Code  string `gorm:"uniqueIndex;size:100"`
    Price float64
}

// All common struct tags
type Example struct {
    ID        uint           `gorm:"primaryKey;autoIncrement"`
    Name      string         `gorm:"column:full_name;size:255;not null"`
    Email     string         `gorm:"uniqueIndex"`
    Age       int            `gorm:"default:18"`
    Bio       string         `gorm:"type:text"`
    Active    bool           `gorm:"default:true"`
    Score     float64        `gorm:"precision:2"`
    Ignored   string         `gorm:"-"`           // skip this field
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`       // soft delete
}

db.AutoMigrate(&User{})                          // migrate single model
db.AutoMigrate(&User{}, &Product{}, &Order{})    // migrate multiple models

user := User{Name: "Alice", Email: "alice@example.com", Age: 30}

result := db.Create(&user)                        // insert and populate ID
result.Error                                      // check for errors
result.RowsAffected                               // number of rows inserted

// Create with selected fields only
db.Select("Name", "Email").Create(&user)

// Batch insert
users := []User{
    {Name: "Alice"},
    {Name: "Bob"},
    {Name: "Charlie"},
}
db.Create(&users)

// Create with map
db.Model(&User{}).Create(map[string]interface{}{
    "Name": "Dave", "Email": "dave@example.com",
})

var user User
var users []User

db.First(&user)                                   // ORDER BY id LIMIT 1
db.Last(&user)                                    // ORDER BY id DESC LIMIT 1
db.Take(&user)                                    // no order, LIMIT 1

db.First(&user, 10)                               // find by primary key = 10
db.First(&user, "email = ?", "alice@example.com") // inline condition

db.Find(&users)                                   // get all records
db.Find(&users, []int{1, 2, 3})                   // find by slice of primary keys

// Where
db.Where("name = ?", "Alice").First(&user)
db.Where("age > ? AND active = ?", 18, true).Find(&users)
db.Where("name IN ?", []string{"Alice", "Bob"}).Find(&users)
db.Where("name LIKE ?", "%ali%").Find(&users)
db.Where("created_at BETWEEN ? AND ?", startDate, endDate).Find(&users)

// Where with struct (only non-zero fields are used)
db.Where(&User{Name: "Alice", Age: 30}).Find(&users)

// Where with map (zero values ARE included)
db.Where(map[string]interface{}{"name": "Alice", "age": 0}).Find(&users)

// Not / Or
db.Not("name = ?", "Alice").Find(&users)
db.Where("name = ?", "Alice").Or("name = ?", "Bob").Find(&users)

// Select specific columns
db.Select("name", "email").Find(&users)

// Order, Limit, Offset
db.Order("age desc").Find(&users)
db.Limit(10).Offset(20).Find(&users)                     // page 3 of 10

// Distinct
db.Distinct("name", "age").Find(&users)

// Count
var count int64
db.Model(&User{}).Where("active = ?", true).Count(&count)

// Pluck (single column into slice)
var names []string
db.Model(&User{}).Pluck("name", &names)

// Save — updates all fields (including zero values)
db.Save(&user)

// Update single column
db.Model(&user).Update("name", "NewName")

// Update multiple columns with struct (skips zero values)
db.Model(&user).Updates(User{Name: "New", Age: 25})

// Update multiple columns with map (includes zero values)
db.Model(&user).Updates(map[string]interface{}{
    "name": "New", "age": 0, "active": false,
})

// Update with condition (no model)
db.Model(&User{}).Where("active = ?", false).Update("name", "Inactive")

// Update selected columns only
db.Model(&user).Select("name", "age").Updates(User{Name: "Alice", Age: 0})

// Omit specific columns
db.Model(&user).Omit("age").Updates(User{Name: "Alice", Age: 99}) // age not updated

// Increment / Decrement (atomic)
db.Model(&user).Update("score", gorm.Expr("score + ?", 10))

// Soft delete (sets DeletedAt — requires gorm.Model or gorm.DeletedAt field)
db.Delete(&user)
db.Delete(&user, 10)                              // by primary key
db.Where("age < ?", 18).Delete(&User{})           // conditional delete

// Find soft-deleted records
db.Unscoped().Where("name = ?", "Alice").Find(&users)

// Permanently delete (hard delete, bypasses soft delete)
db.Unscoped().Delete(&user)

// Batch delete
db.Where("active = ?", false).Delete(&User{})

// One-to-Many
type User struct {
    gorm.Model
    Name        string
    CreditCards []CreditCard // has many
}
type CreditCard struct {
    gorm.Model
    Number string
    UserID uint   // foreign key (convention: OwnerType + ID)
}

// Belongs-To
type Order struct {
    gorm.Model
    UserID uint
    User   User  // belongs to
}

// Many-to-Many
type User struct {
    gorm.Model
    Languages []Language `gorm:"many2many:user_languages;"` // join table
}
type Language struct {
    gorm.Model
    Name string
}

// Has-One
type User struct {
    gorm.Model
    Profile Profile
}
type Profile struct {
    gorm.Model
    Bio    string
    UserID uint
}

// Preload single association
db.Preload("CreditCards").Find(&users)

// Preload nested associations
db.Preload("Orders.Products").Find(&users)

// Preload with conditions
db.Preload("CreditCards", "number = ?", "1234").Find(&users)

// Preload all associations
db.Preload(clause.Associations).Find(&users)

// Joins (SQL JOIN instead of separate query)
db.Joins("Profile").Find(&users)
db.Joins("JOIN orders ON orders.user_id = users.id").Find(&users)

// Append to has-many
db.Model(&user).Association("CreditCards").Append(&CreditCard{Number: "9999"})

// Replace all associations
db.Model(&user).Association("Languages").Replace([]Language{langEN, langZH})

// Delete specific association record
db.Model(&user).Association("CreditCards").Delete(&card)

// Clear all associations (without deleting records)
db.Model(&user).Association("Languages").Clear()

// Count associations
db.Model(&user).Association("CreditCards").Count()

// Raw query into struct/slice
var users []User
db.Raw("SELECT * FROM users WHERE age > ?", 18).Scan(&users)

// Raw into map
var result map[string]interface{}
db.Raw("SELECT name, age FROM users WHERE id = ?", 1).Scan(&result)

// Exec (INSERT / UPDATE / DELETE without returning rows)
db.Exec("UPDATE users SET active = ? WHERE id = ?", true, 1)

// Named args
db.Where("name = @name AND age = @age", sql.Named("name", "Alice"), sql.Named("age", 30)).Find(&users)
db.Raw("SELECT * FROM users WHERE name = @name", map[string]interface{}{"name": "Alice"}).Scan(&users)

// Auto transaction (rollback on error)
err := db.Transaction(func(tx *gorm.DB) error {
    if err := tx.Create(&User{Name: "Alice"}).Error; err != nil {
        return err // triggers rollback
    }
    if err := tx.Create(&Order{UserID: 1}).Error; err != nil {
        return err
    }
    return nil // commit
})

// Manual transaction
tx := db.Begin()
if tx.Error != nil { /* handle */ }

tx.Create(&User{Name: "Alice"})
tx.Create(&Order{UserID: 1})

if someCondition {
    tx.Rollback()
} else {
    tx.Commit()
}

// SavePoint
tx := db.Begin()
tx.Create(&user1)
tx.SavePoint("sp1")
tx.Create(&user2)
tx.RollbackTo("sp1")     // rolls back user2 only
tx.Commit()

// Define reusable scopes
func ActiveUsers(db *gorm.DB) *gorm.DB {
    return db.Where("active = ?", true)
}

func OlderThan(age int) func(db *gorm.DB) *gorm.DB {
    return func(db *gorm.DB) *gorm.DB {
        return db.Where("age > ?", age)
    }
}

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

// Use scopes
db.Scopes(ActiveUsers, OlderThan(18), Paginate(2, 10)).Find(&users)

// Available hooks (Before/After for Create, Save, Update, Delete, Find)
func (u *User) BeforeCreate(tx *gorm.DB) error {
    u.Name = strings.TrimSpace(u.Name)
    return nil
}

func (u *User) AfterCreate(tx *gorm.DB) error {
    log.Printf("User %d created", u.ID)
    return nil
}

func (u *User) BeforeDelete(tx *gorm.DB) error {
    if u.Admin {
        return errors.New("cannot delete admin users")
    }
    return nil
}

// Hooks run inside the same transaction automatically
// Return error to abort and rollback

type User struct {
    gorm.Model
    Name    string `gorm:"index"`                          // simple index
    Email   string `gorm:"uniqueIndex"`                    // unique index
    City    string `gorm:"index:idx_city_age"`             // composite index (part 1)
    Age     int    `gorm:"index:idx_city_age"`             // composite index (part 2)
    Code    string `gorm:"index:,sort:desc,type:btree"`    // index options
    Ref     string `gorm:"check:ref_check,ref <> 'bad'"`  // check constraint
}

// Create index manually
db.Exec("CREATE INDEX idx_name ON users(name)")

type Result struct {
    Date  string
    Total int
}

// Group by with aggregation
db.Model(&Order{}).
    Select("date(created_at) AS date, SUM(amount) AS total").
    Group("date(created_at)").
    Having("SUM(amount) > ?", 100).
    Scan(&results)

// Multiple aggregations
db.Model(&User{}).
    Select("age, count(*) as count, avg(score) as avg_score").
    Group("age").
    Scan(&results)

sqlDB, err := db.DB()   // get underlying *sql.DB

sqlDB.SetMaxIdleConns(10)             // max idle connections
sqlDB.SetMaxOpenConns(100)            // max open connections
sqlDB.SetConnMaxLifetime(time.Hour)   // max connection lifetime
sqlDB.SetConnMaxIdleTime(10 * time.Minute)

sqlDB.Stats()   // pool statistics
sqlDB.Ping()    // check connectivity
sqlDB.Close()   // close pool

// Reusable session (does not share state)
tx := db.Session(&gorm.Session{})
tx.Where("name = ?", "Alice").Find(&users)

// Dry run (build SQL without executing)
stmt := db.Session(&gorm.Session{DryRun: true}).Find(&users)
stmt.Statement.SQL.String()   // print the SQL

// WithContext (cancellation / tracing)
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
db.WithContext(ctx).Find(&users)

// PrepareStmt (cache prepared statements)
db.Session(&gorm.Session{PrepareStmt: true}).Find(&users)

// Always check .Error
result := db.First(&user, 10)
if result.Error != nil {
    if errors.Is(result.Error, gorm.ErrRecordNotFound) {
        // no record found
    }
    log.Fatal(result.Error)
}

// Common GORM errors
gorm.ErrRecordNotFound       // First/Last/Take found no row
gorm.ErrInvalidTransaction   // invalid transaction state
gorm.ErrNotImplemented       // feature not implemented by driver
gorm.ErrMissingWhereClause   // update/delete without WHERE (safety guard)
gorm.ErrUnsupportedRelation  // unsupported association type
gorm.ErrDryRunModeUnsupported

db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{
    NamingStrategy: schema.NamingStrategy{
        TablePrefix:   "app_",        // prefix all table names
        SingularTable: true,          // use singular table names (user vs users)
    },
    DisableForeignKeyConstraintWhenMigrating: true,
    SkipDefaultTransaction: true,     // improve perf for single writes
    QueryFields: true,                // SELECT all named fields instead of SELECT *
    CreateBatchSize: 1000,            // default batch size for bulk inserts
    Logger: logger.Default.LogMode(logger.Silent), // suppress logs
})

import "database/sql/driver"

// Custom JSON type
type JSON json.RawMessage

func (j *JSON) Scan(value interface{}) error { /* parse bytes into j */ }
func (j JSON) Value() (driver.Value, error)  { return string(j), nil }

// Custom Encrypted type
type Encrypted string

func (e *Encrypted) Scan(value interface{}) error {
    // decrypt value from DB
    *e = Encrypted(decrypt(value.(string)))
    return nil
}
func (e Encrypted) Value() (driver.Value, error) {
    return encrypt(string(e)), nil
}

// Use in model
type User struct {
    gorm.Model
    Settings JSON
    Token    Encrypted
}

import "gorm.io/gorm/clause"

// Locking
db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)    // SELECT ... FOR UPDATE
db.Clauses(clause.Locking{Strength: "SHARE"}).Find(&users)     // SELECT ... FOR SHARE

// ON CONFLICT (Upsert)
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)   // ignore duplicates

db.Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "email"}},
    DoUpdates: clause.AssignmentColumns([]string{"name", "age"}),
}).Create(&user)

// Returning (PostgreSQL)
db.Clauses(clause.Returning{}).Create(&user)    // populate all fields after insert
db.Clauses(clause.Returning{Columns: []clause.Column{{Name: "id"}}}).Create(&user)

// Print SQL without executing
stmt := db.Session(&gorm.Session{DryRun: true}).
    Where("age > ?", 18).Find(&User{})
fmt.Println(stmt.Statement.SQL.String())
fmt.Println(stmt.Statement.Vars)

// Debug mode (print all SQL for this query)
db.Debug().Where("name = ?", "Alice").Find(&users)

// Custom logger
newLogger := logger.New(
    log.New(os.Stdout, "\r\n", log.LstdFlags),
    logger.Config{
        SlowThreshold:             200 * time.Millisecond, // warn on slow queries
        LogLevel:                  logger.Warn,
        IgnoreRecordNotFoundError: true,
        Colorful:                  true,
    },
)
db, _ = gorm.Open(sqlite.Open("test.db"), &gorm.Config{Logger: newLogger})

type Dog struct {
    gorm.Model
    Name string
    Toys []Toy `gorm:"polymorphic:Owner;"`
}

type Cat struct {
    gorm.Model
    Name string
    Toys []Toy `gorm:"polymorphic:Owner;"`
}

type Toy struct {
    gorm.Model
    Name      string
    OwnerID   uint
    OwnerType string   // stores "dogs" or "cats"
}

// GORM auto-sets OwnerType on create/query
db.Create(&Dog{Name: "Rex", Toys: []Toy{{Name: "Ball"}}})
db.Model(&dog).Association("Toys").Find(&toys)

// Custom table name via method
func (User) TableName() string { return "app_users" }

// Query a different table at runtime
db.Table("archived_users").Find(&users)
db.Table("users").Select("name, email").Scan(&results)

// Use a subquery as table
subQuery := db.Select("AVG(age) as avg_age").Table("users")
db.Select("*").Table("(?) AS u", subQuery).Where("u.avg_age > ?", 25).Scan(&results)

import "gorm.io/plugin/soft_delete"

type User struct {
    gorm.Model
    Name      string
    DeletedAt soft_delete.DeletedAt                              // Unix timestamp
    // OR
    IsDel     soft_delete.DeletedAt `gorm:"softDelete:flag"`    // 0/1 flag
}

// Query includes deleted with Unscoped
db.Unscoped().Find(&users)   // includes soft-deleted rows

// First or create
db.FirstOrCreate(&user, User{Email: "alice@example.com"})

// First or init (does not save)
db.FirstOrInit(&user, User{Email: "alice@example.com"})

// Assign extra attrs when found
db.Where(User{Email: "alice@example.com"}).Assign(User{Name: "Alice"}).FirstOrCreate(&user)

// Find and update in one call
db.Model(&user).Where("id = ?", 1).Update("name", "Alice")

// Rows (iterate large result sets without loading all into memory)
rows, _ := db.Model(&User{}).Where("active = ?", true).Rows()
defer rows.Close()
for rows.Next() {
    var u User
    db.ScanRows(rows, &u)
    // process u
}

// Check if record exists
var exists bool
db.Model(&User{}).Select("count(*) > 0").Where("email = ?", "alice@example.com").Find(&exists)