Gorm

https://github.com/yys190/gorm-docs
Admin
gorm docs
Tokens:10,177
Snippets:66
Trust Score:4.1
Update:1 year ago
Show doc for...
Tokens:
Raw
### Connecting to Clickhouse and Basic Operations with GORM in Go

Source: https://github.com/yys190/gorm-docs/blob/main/connecting_to_the_database.md

This Go snippet illustrates how to connect to a Clickhouse database using the `go-gorm/clickhouse` driver. It includes examples of opening a connection, performing automatic schema migration, setting Clickhouse-specific table options like `ENGINE`, and demonstrating basic create, find, and batch create operations.

```Go
import (
  "gorm.io/driver/clickhouse"
  "gorm.io/gorm"
)

func main() {
 dsn := "clickhouse://gorm:gorm@localhost:9942/gorm?dial_timeout=10s&read_timeout=20s"
  db, err := gorm.Open(clickhouse.Open(dsn), &gorm.Config{})

  // 自动迁移 (这是GORM自动创建表的一种方式--译者注)
  db.AutoMigrate(&User{})
  // 设置表选项
  db.Set("gorm:table_options", "ENGINE=Distributed(cluster, default, hits)").AutoMigrate(&User{})

  // 插入
  db.Create(&user)

  // 查询
  db.Find(&user, "id = ?", 10)

  // 批量插入
  var users = []User{user1, user2, user3}
  db.Create(&users)
  // ...
}
```

--------------------------------

### GORM StatementModifier Hint Example (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

Demonstrates using a custom StatementModifier like hints.New via the Clauses method to add database hints or comments to the generated SQL query.

```Go
import "gorm.io/hints"

db.Clauses(hints.New("hint")).Find(&User{})
```

--------------------------------

### GORM Selecting Specific Fields (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Explains how to use the Select method to specify which columns should be retrieved from the database, rather than selecting all fields by default. Shows examples using string arguments, a string slice, and a SQL expression.

```Go
db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;
```

--------------------------------

### GORM Or Conditions (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to combine multiple query conditions using the Or method. Shows examples combining plain SQL with plain SQL, struct, and map conditions.

```Go
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

// Struct
db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);
```

--------------------------------

### GORM Ordering Query Results (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to use the Order method to specify the sorting criteria for query results. Shows examples of single/multiple column ordering and using Clauses for more complex ordering like FIELD.

```Go
db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// Multiple orders
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

db.Clauses(clause.OrderBy{
  Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)
```

--------------------------------

### Configuring GORM's Underlying SQL Connection Pool in Go

Source: https://github.com/yys190/gorm-docs/blob/main/connecting_to_the_database.md

This Go snippet shows how to access the underlying `database/sql` connection pool from a GORM database instance. It provides examples of setting common connection pool parameters: maximum idle connections, maximum open connections, and the maximum lifetime of a connection.

```Go
sqlDB, err := db.DB()

// SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
sqlDB.SetMaxIdleConns(10)

// SetMaxOpenConns sets the maximum number of open connections to the database.
sqlDB.SetMaxOpenConns(100)

// SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
sqlDB.SetConnMaxLifetime(time.Hour)
```

--------------------------------

### Performing Column Operations and Getting Types with GORM Migrator (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Illustrates how to use the GORM `Migrator` to manage columns within a table, including adding, dropping, altering, checking for existence, and renaming columns. Also includes the definition of the `ColumnType` interface for inspecting column details.

```Go
type User struct {
  Name string
}

// 添加 name 字段
db.Migrator().AddColumn(&User{}, "Name")
// 删除 name 字段
db.Migrator().DropColumn(&User{}, "Name")
// 修改 name 字段
db.Migrator().AlterColumn(&User{}, "Name")
// 检查 name 字段是否存在
db.Migrator().HasColumn(&User{}, "Name")

type User struct {
  Name    string
  NewName string
}

// 字段重命名
db.Migrator().RenameColumn(&User{}, "Name", "NewName")
db.Migrator().RenameColumn(&User{}, "name", "new_name")

// 字段类型
db.Migrator().ColumnTypes(&User{}) ([]gorm.ColumnType, error)

type ColumnType interface {
    Name() string
    DatabaseTypeName() string                 // varchar
    ColumnType() (columnType string, ok bool) // varchar(64)
    PrimaryKey() (isPrimaryKey bool, ok bool)
    AutoIncrement() (isAutoIncrement bool, ok bool)
    Length() (length int64, ok bool)
    DecimalSize() (precision int64, scale int64, ok bool)
    Nullable() (nullable bool, ok bool)
    Unique() (unique bool, ok bool)
    ScanType() reflect.Type
    Comment() (value string, ok bool)
    DefaultValue() (value string, ok bool)
}
```

--------------------------------

### Apply String Conditions with Where in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Provides various examples of using the Where method with string conditions and arguments to filter records. Covers equality, inequality, IN, LIKE, AND, time comparisons, and BETWEEN clauses.

```Go
// Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

// Get all matched records
db.Where("name <> ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';
```

--------------------------------

### Using Group By and Having in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how to group results using `Group` and filter grouped results using `Having` in GORM. Includes examples of scanning into a struct and iterating over rows.

```Go
type result struct {
  Date  time.Time
  Total int
}

db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1


db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
defer rows.Close()
for rows.Next() {
  ...
}

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
defer rows.Close()
for rows.Next() {
  ...
}

type Result struct {
  Date  time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
```

--------------------------------

### Getting Current Database Name with GORM Migrator (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Shows how to access the `Migrator` interface from the GORM DB instance and call the `CurrentDatabase` method to get the name of the active database.

```Go
db.Migrator().CurrentDatabase()
```

--------------------------------

### GORM Not Conditions (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Explains how to use the Not method to negate query conditions. Shows examples using plain SQL, map (for NOT IN), struct, and slice of primary keys.

```Go
db.Not("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;

// Not In
db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

// Struct
db.Not(User{Name: "jinzhu", Age: 18}).First(&user)
// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;

// Not In slice of primary keys
db.Not([]int64{1,2,3}).First(&user)
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
```

--------------------------------

### Demonstrate SQLite Batch Insert Default Limitation

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Shows a GORM model with a default tag and an example of a batch insert that will fail in SQLite when one of the items relies on the default value.

```Go
type Pet struct {
    Name string `gorm:"default:cat"`
}
  
// 在SqlLite中，这是不允许的, 所以GORM会通过构建错误的SQL来返回错误:
// INSERT INTO `pets` (`name`) VALUES ("dog"),(DEFAULT) RETURNING `name`
db.Create(&[]Pet{{Name: "dog"}, {}})
```

--------------------------------

### Disabling Foreign Key Constraints During GORM Migration (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Shows how to configure the GORM connection to prevent the automatic creation of foreign key constraints when using `AutoMigrate`. Useful for specific database setups or migration strategies.

```Go
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
  DisableForeignKeyConstraintWhenMigrating: true,
})
```

--------------------------------

### Retrieve Single Record (First, Take, Last) in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates fetching a single record using GORM's First, Take, and Last methods. First and Last order by primary key, while Take does not specify an order. The examples also show how to check the result for the number of affected rows and potential ErrRecordNotFound errors.

```Go
// 获取第一条记录（主键升序）
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录，没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录（主键降序）
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // 返回找到的记录数
result.Error        // returns error or nil

// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)
```

--------------------------------

### Connecting to TiDB and Basic CRUD with GORM in Go

Source: https://github.com/yys190/gorm-docs/blob/main/connecting_to_the_database.md

This snippet shows how to establish a connection to a TiDB database using GORM's MySQL driver. It defines a `Product` model utilizing TiDB's `auto_random()` function for the primary key, performs automatic schema migration, creates a new product record, and then retrieves it by code.

```Go
import (
  "fmt"
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

type Product struct {
  ID    uint `gorm:"primaryKey;default:auto_random()"`
  Code  string
  Price uint
}

func main() {
  db, err := gorm.Open(mysql.Open("root:@tcp(127.0.0.1:4000)/test"), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  db.AutoMigrate(&Product{})

  insertProduct := &Product{Code: "D42", Price: 100}

  db.Create(insertProduct)
  fmt.Printf("insert ID: %d, Code: %s, Price: %d\n",
    insertProduct.ID, insertProduct.Code, insertProduct.Price)

  readProduct := &Product{}
  db.First(&readProduct, "code = ?", "D42") // find product with code D42

  fmt.Printf("read ID: %d, Code: %s, Price: %d\n",
    readProduct.ID, readProduct.Code, readProduct.Price)
}
```

--------------------------------

### AutoMigrating Models in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Demonstrates how to use `AutoMigrate` to create or update database tables based on struct definitions. Shows migrating single or multiple models and adding table options during creation.

```Go
db.AutoMigrate(&User{})

db.AutoMigrate(&User{}, &Product{}, &Order{})

// 创建表时添加后缀
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{})
```

--------------------------------

### Planning Atlas Migrations with GORM - Bash

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

This command demonstrates how to use the Atlas CLI with the GORM provider to automatically plan database schema migrations by comparing the current database state with the GORM model definitions.

```bash
atlas migrate diff --env gorm
```

--------------------------------

### Performing View Operations with GORM Migrator (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Shows how to use the GORM `Migrator` to create database views based on a GORM query, including options for replacing existing views or adding check options. Also demonstrates how to drop a view.

```Go
query := db.Model(&User{}).Where("age > ?", 20)

// Create View
db.Migrator().CreateView("users_pets", gorm.ViewOption{Query: query})
// CREATE VIEW `users_view` AS SELECT * FROM `users` WHERE age > 20

// Create or Replace View
db.Migrator().CreateView("users_pets", gorm.ViewOption{Query: query, Replace: true})
// CREATE OR REPLACE VIEW `users_pets` AS SELECT * FROM `users` WHERE age > 20

// Create View With Check Option
db.Migrator().CreateView("users_pets", gorm.ViewOption{Query: query, CheckOption: "WITH CHECK OPTION"})
// CREATE VIEW `users_pets` AS SELECT * FROM `users` WHERE age > 20 WITH CHECK OPTION

// Drop View
db.Migrator().DropView("users_pets")
// DROP VIEW IF EXISTS "users_pets"
```

--------------------------------

### GORM Offset and Limit Usage (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

Demonstrates how to use the Offset and Limit methods in GORM to paginate query results. The generated SQL varies depending on the database dialect.

```Go
db.Offset(10).Limit(5).Find(&users)
```

--------------------------------

### Performing Table Operations with GORM Migrator (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Demonstrates how to use the GORM `Migrator` interface to programmatically manage database tables, including creating tables with options, checking for their existence, dropping them, and renaming them using model structs or table names.

```Go
// 为 `User` 创建表
db.Migrator().CreateTable(&User{})

// 将 "ENGINE=InnoDB" 添加到创建 `User` 的 SQL 里去
db.Set("gorm:table_options", "ENGINE=InnoDB").Migrator().CreateTable(&User{})

// 检查 `User` 对应的表是否存在
db.Migrator().HasTable(&User{})
db.Migrator().HasTable("users")

// 如果存在表则删除（删除时会忽略、删除外键约束)
db.Migrator().DropTable(&User{})
db.Migrator().DropTable("users")

// 重命名表
db.Migrator().RenameTable(&User{}, &UserInfo{})
db.Migrator().RenameTable("users", "user_infos")
```

--------------------------------

### Applying Limit and Offset in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to use `Limit` to restrict the number of records retrieved and `Offset` to skip records in GORM queries. It also shows how to cancel these conditions using -1.

```Go
db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;

// Cancel limit condition with -1
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)

db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;

db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;

// Cancel offset condition with -1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)
```

--------------------------------

### Specifying Join Conditions in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Illustrates how to perform database joins using the `Joins` method in GORM, including left joins, joining multiple tables, and using parameters within join conditions.

```Go
type result struct {
  Name  string
  Email string
}

db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
  ...
}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// multiple joins with parameter
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_cards.user_id = users.id").Where("credit_cards.number = ?", "411111111111").Find(&user)
```

--------------------------------

### GORM Statement Build Method (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

Illustrates how GORM's Statement object uses the Build method with specified clause names to construct the final SQL query string based on the added clauses.

```Go
Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR")
```

--------------------------------

### GORM Go Single Record Retrieval with Different Targets

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Illustrates how GORM's First and Take methods behave when used with different targets, such as a struct pointer, a model specified via db.Model(), or a table specified via db.Table(). It also shows how sorting defaults to the first field if no primary key is defined.

```Go
var user User
var users []User

// works because destination struct is passed in
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

// works because model is specified using `db.Model()`
result := map[string]interface{}{}
db.Model(&User{}).First(&result)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

// doesn't work
result := map[string]interface{}{}
db.Table("users").First(&result)

// works with Take
result := map[string]interface{}{}
db.Table("users").Take(&result)

// no primary key defined, results will be ordered by first field (i.e., `Code`)
type Language struct {
  Code string
  Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
```

--------------------------------

### GORM Inline Query Conditions (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to pass query conditions directly to methods like First and Find using plain SQL, struct, or map arguments, similar to using Where.

```Go
// Get by primary key if it were a non-integer type
db.First(&user, "id = ?", "string_primary_key")
// SELECT * FROM users WHERE id = 'string_primary_key';

// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";

db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

// Struct
db.Find(&users, User{Age: 20})
// SELECT * FROM users WHERE age = 20;

// Map
db.Find(&users, map[string]interface{}{"age": 20})
// SELECT * FROM users WHERE age = 20;
```

--------------------------------

### Using Joins for Eager Loading in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how to use `Joins` and `InnerJoins` for eager loading associations in GORM, retrieving associated data in a single query.

```Go
db.Joins("Company").Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;

// inner join
db.InnerJoins("Company").Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` INNER JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id`;
```

--------------------------------

### GORM First Query Clauses (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

Demonstrates the internal GORM clauses (Select, From, Limit, OrderBy) added to a Statement when performing a First query. These clauses define the structure of the generated SQL.

```Go
var limit = 1
clause.Select{Columns: []clause.Column{{Name: "*"}}}
clause.From{Tables: []clause.Table{{Name: clause.CurrentTable}}}
clause.Limit{Limit: &limit}
clause.OrderBy{Columns: []clause.OrderByColumn{
  {
    Column: clause.Column{
      Table: clause.CurrentTable,
      Name:  clause.PrimaryKey,
    },
  },
}}
```

--------------------------------

### Eager Loading with Joins and Conditions in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to apply conditions to the joined association when performing eager loading with `Joins` in GORM.

```Go
db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;
```

--------------------------------

### GORM Migrator Interface Definition (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

Provides the Go interface definition for GORM's `Migrator`, outlining the various methods available for database schema management like creating/dropping tables, columns, constraints, and indexes.

```Go
type Migrator interface {
  // AutoMigrate
  AutoMigrate(dst ...interface{}) error

  // Database
  CurrentDatabase() string
  FullDataTypeOf(*schema.Field) clause.Expr

  // Tables
  CreateTable(dst ...interface{}) error
  DropTable(dst ...interface{}) error
  HasTable(dst interface{}) bool
  RenameTable(oldName, newName interface{}) error
  GetTables() (tableList []string, err error)

  // Columns
  AddColumn(dst interface{}, field string) error
  DropColumn(dst interface{}, field string) error
  AlterColumn(dst interface{}, field string) error
  MigrateColumn(dst interface{}, field *schema.Field, columnType ColumnType) error
  HasColumn(dst interface{}, field string) bool
  RenameColumn(dst interface{}, oldName, field string) error
  ColumnTypes(dst interface{}) ([]ColumnType, error)

  // Views
  CreateView(name string, option ViewOption) error
  DropView(name string) error

  // Constraints
  CreateConstraint(dst interface{}, name string) error
  DropConstraint(dst interface{}, name string) error
  HasConstraint(dst interface{}, name bool)

  // Indexes
  CreateIndex(dst interface{}, name string) error
  DropIndex(dst interface{}, name string) error
  HasIndex(dst interface{}, name bool)
  RenameIndex(dst interface{}, oldName, newName string) error
}
```

--------------------------------

### GORM Where Conditions with Struct, Map, and Slice (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how to use a struct, a map, or a slice of primary keys to define query conditions with GORM's Where method. Explains the resulting SQL queries.

```Go
// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// Slice of primary keys
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);
```

--------------------------------

### Scanning Query Results into Structs in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how to use the `Scan` method to map query results, including those from raw SQL, into a Go struct in GORM.

```Go
type Result struct {
  Name string
  Age  int
}

var result Result
db.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)

// Raw SQL
db.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
```

--------------------------------

### Managing GORM Indexes - Go

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

This snippet illustrates how to define indexes on model fields using GORM tags and how to use the GORM Migrator to create, drop, check for, and rename indexes by field name or the specified index name.

```go
type User struct {
  gorm.Model
  Name string `gorm:"size:255;index:idx_name,unique"`
}

// Create index for Name field
db.Migrator().CreateIndex(&User{}, "Name")
db.Migrator().CreateIndex(&User{}, "idx_name")

// Drop index for Name field
db.Migrator().DropIndex(&User{}, "Name")
db.Migrator().DropIndex(&User{}, "idx_name")

// Check Index exists
db.Migrator().HasIndex(&User{}, "Name")
db.Migrator().HasIndex(&User{}, "idx_name")

type User struct {
  gorm.Model
  Name  string `gorm:"size:255;index:idx_name,unique"`
  Name2 string `gorm:"size:255;index:idx_name_2,unique"`
}
// Rename index name
db.Migrator().RenameIndex(&User{}, "Name", "Name2")
db.Migrator().RenameIndex(&User{}, "idx_name", "idx_name_2")
```

--------------------------------

### Accessing Underlying *sql.DB in GORM - Go

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

This snippet shows how to retrieve the underlying standard library `*sql.DB` connection pool from a GORM DB instance, which can be useful when integrating with other tools or libraries that require a standard DB interface.

```go
// returns `*sql.DB`
db.DB()
```

--------------------------------

### Joining a Derived Table in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Explains how to join a subquery or derived table using the `Joins` method in GORM, useful for complex queries involving aggregated data.

```Go
type User struct {
    Id  int
    Age int
}

type Order struct {
    UserId     int
    FinishedAt *time.Time
}

query := db.Table("order").Select("MAX(order.finished_at) as latest").Joins("left join user user on order.user_id = user.id").Where("user.age > ?", 18).Group("order.user_id")
db.Model(&Order{}).Joins("join (?) q on order.finished_at = q.latest", query).Scan(&results)
// SELECT `order`.`user_id`,`order`.`finished_at` FROM `order` join (SELECT MAX(order.finished_at) as latest FROM `order` left join user user on order.user_id = user.id WHERE user.age > 18 GROUP BY `order`.`user_id`) q on order.finished_at = q.latest
```

--------------------------------

### Selecting Distinct Records in GORM (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to select distinct combinations of specified columns using the `Distinct` method in GORM.

```Go
db.Distinct("name", "age").Order("name, age desc").Find(&results)
```

--------------------------------

### Retrieve All Records with Find in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates how to fetch all records from a table into a slice using the Find method. It also shows how to access the number of records found and any potential errors from the result object.

```Go
// Get all records
result := db.Find(&users)
// SELECT * FROM users;

result.RowsAffected // returns found records count, equals `len(users)`
result.Error        // returns error
```

--------------------------------

### Managing GORM Foreign Key Constraints - Go

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

This snippet shows how to define models with one-to-many relationships and use the GORM Migrator to create, check for, and drop foreign key constraints, referencing the relationship name or the generated constraint name.

```go
type User struct {
  gorm.Model
  CreditCards []CreditCard
}

type CreditCard struct {
  gorm.Model
  Number string
  UserID uint
}

// create database foreign key for user & credit_cards
db.Migrator().CreateConstraint(&User{}, "CreditCards")
db.Migrator().CreateConstraint(&User{}, "fk_users_credit_cards")
// ALTER TABLE `credit_cards` ADD CONSTRAINT `fk_users_credit_cards` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)

// check database foreign key for user & credit_cards exists or not
db.Migrator().HasConstraint(&User{}, "CreditCards")
db.Migrator().HasConstraint(&User{}, "fk_users_credit_cards")

// drop database foreign key for user & credit_cards
db.Migrator().DropConstraint(&User{}, "CreditCards")
db.Migrator().DropConstraint(&User{}, "fk_users_credit_cards")
```

--------------------------------

### Performing Soft Delete Operation (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/delete.md

Shows how to perform a soft delete operation directly in the database when using the mixed mode with an 'is_del' flag (setting it to 1) and updating the 'deleted_at' timestamp.

```SQL
UPDATE users SET is_del = 1, deleted_at = /* current unix second */ WHERE ID = 1;
```

--------------------------------

### Managing GORM Check Constraints - Go

Source: https://github.com/yys190/gorm-docs/blob/main/migration.md

This snippet demonstrates how to define a check constraint on a model field using GORM tags and how to use the GORM Migrator to create, drop, and check for the existence of named constraints.

```go
type UserIndex struct {
  Name  string `gorm:"check:name_checker,name <> 'jinzhu'"`
}

// Create constraint
db.Migrator().CreateConstraint(&User{}, "name_checker")

// Drop constraint
db.Migrator().DropConstraint(&User{}, "name_checker")

// Check constraint exists
db.Migrator().HasConstraint(&User{}, "name_checker")
```

--------------------------------

### Querying Non-Deleted Records (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/delete.md

Shows how to query records that are *not* soft-deleted when using the mixed mode with an 'is_del' flag where 0 indicates not deleted.

```SQL
SELECT * FROM users WHERE is_del = 0;
```

--------------------------------

### Set Default Value Using BeforeCreate Hook

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Provides an alternative method to set default values using a `BeforeCreate` hook function, which can be used to work around limitations like the SQLite batch insert default issue.

```Go
func (p *Pet) BeforeCreate(tx *gorm.DB) (err error) {
    if p.Name == "" {
        p.Name = "cat"
    }
}
```

--------------------------------

### Generated MySQL Offset/Limit (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

The SQL syntax generated by GORM for MySQL when using Offset and Limit, utilizing the standard LIMIT ... OFFSET ... syntax.

```SQL
SELECT * FROM `users` LIMIT 5 OFFSET 10
```

--------------------------------

### Retrieve Record by String Primary Key in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates retrieving a single record using a string primary key by providing the condition explicitly with a WHERE clause and arguments.

```Go
db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
```

--------------------------------

### GORM Specifying Struct Fields in Where Clause (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how to pass field names (or db names) as additional arguments to Where when using a struct to specify which fields should be included in the query conditions, even if they have zero values.

```Go
db.Where(&User{Name: "jinzhu"}, "name", "Age").Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;

db.Where(&User{Name: "jinzhu"}, "Age").Find(&users)
// SELECT * FROM users WHERE age = 0;
```

--------------------------------

### Defining GORM Model with Mixed Soft Delete (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/delete.md

Defines a Go struct for a GORM model using the soft_delete plugin in 'mixed mode'. It includes a standard DeletedAt field and a custom IsDel field configured for soft deletion using a flag (0/1) or unix timestamp, referencing the DeletedAt field.

```Go
type User struct {
  ID        uint
  Name      string
  DeletedAt time.Time
  IsDel     soft_delete.DeletedAt `gorm:"softDelete:flag,DeletedAtField:DeletedAt"` // use `1` `0`
  // IsDel     soft_delete.DeletedAt `gorm:"softDelete:,DeletedAtField:DeletedAt"` // use `unix second`
  // IsDel     soft_delete.DeletedAt `gorm:"softDelete:nano,DeletedAtField:DeletedAt"` // use `unix nano second`
}
```

--------------------------------

### Generated SQL for GORM First Query (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

The resulting SQL query string generated by GORM based on the clauses added for a First operation, showing the selection, table, ordering, and limit.

```SQL
SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
```

--------------------------------

### Handle Zero Values with Pointers/Scanners in GORM

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Defines a GORM model struct using pointer types (`*int`) or `sql.NullBool` to ensure that zero values (like 0, '', false) are correctly saved to the database when a default is defined.

```Go
type User struct {
  gorm.Model
  Name string
  Age  *int           `gorm:"default:18"`
  Active sql.NullBool `gorm:"default:true"`
}
```

--------------------------------

### Define GORM Model with DB Function Default and Skip Migration Default

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Defines a GORM model struct using a database function for a default value and demonstrates how to skip setting a default during database migration for a generated column using `default:(-)`.

```Go
type User struct {
  ID        string `gorm:"default:uuid_generate_v3()"` // db func
  FirstName string
  LastName  string
  Age       uint8
  FullName  string `gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"`
}
```

--------------------------------

### Generated SQL Server Offset/Limit (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

The SQL syntax generated by GORM for SQL Server when using Offset and Limit, utilizing OFFSET ... ROW FETCH NEXT ... ROWS ONLY.

```SQL
SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY
```

--------------------------------

### GORM Map Where Condition Including Zero Values (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Explains how to use a map with GORM's Where method to explicitly include fields with zero values in the query conditions. Shows the resulting SQL query includes the zero-valued field.

```Go
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
```

--------------------------------

### GORM Insert Clause Modifier (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

Shows how to use the Clauses method with clause.Insert and the Modifier option to add database-specific keywords like IGNORE to an INSERT statement.

```Go
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user)
```

--------------------------------

### Retrieve Records by Numeric Primary Key in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how to use inline conditions with GORM's First and Find methods to retrieve records based on numeric primary keys. This can be done for a single ID or a slice of IDs.

```Go
db.First(&user, 10)
// SELECT * FROM users WHERE id = 10;

db.First(&user, "10")
// SELECT * FROM users WHERE id = 10;

db.Find(&users, []int{1,2,3})
// SELECT * FROM users WHERE id IN (1,2,3);
```

--------------------------------

### Retrieve Record with gorm.DeletedAt in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Shows how GORM automatically adds an IS NULL condition for the gorm.DeletedAt field when retrieving a record using First on a struct that includes this field, ensuring only non-soft-deleted records are fetched by default.

```Go
type User struct {
  ID           string `gorm:"primarykey;size:16"`
  Name         string `gorm:"size:24"`
  DeletedAt    gorm.DeletedAt `gorm:"index"`
}

var user = User{ID: 15}
db.First(&user)
//  SELECT * FROM `users` WHERE `users`.`id` = '15' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
```

--------------------------------

### Generated SQL with StatementModifier Hint (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

The resulting SQL query string generated by GORM when using the hints.New StatementModifier, showing the hint added as a SQL comment /*+ hint */.

```SQL
SELECT * /*+ hint */ FROM `users`
```

--------------------------------

### GORM Struct Where Condition Ignoring Zero Values (Go)

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Demonstrates that GORM's Where method, when used with a struct, ignores fields with zero values (like 0, "", false) by default. Shows the resulting SQL query excludes the zero-valued field.

```Go
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu";
```

--------------------------------

### Retrieve Record Using Struct with Primary Key in GORM Go

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Explains that when the target struct or the model specified via db.Model() already has a primary key value set, GORM automatically uses this value to build the query condition.

```Go
var user = User{ID: 10}
db.First(&user)
// SELECT * FROM users WHERE id = 10;

var result User
db.Model(User{ID: 10}).First(&result)
// SELECT * FROM users WHERE id = 10;
```

--------------------------------

### Define GORM Model with Default Tags

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Defines a GORM model struct with default values specified using the `default` tag in the `gorm` struct tag.

```Go
type User struct {
  ID   int64
  Name string `gorm:"default:galeone"`
  Age  int64  `gorm:"default:18"`
}
```

--------------------------------

### Checking Field Changes with GORM Changed Method

Source: https://github.com/yys190/gorm-docs/blob/main/update.md

Demonstrates using `tx.Statement.Changed` within a `BeforeUpdate` hook to check if one or more specified fields, or any fields, have been modified during an `Update` or `Updates` call. It shows how the method's behavior is affected by the fields included in the update and the use of `Select`.

```go
func (u *User) BeforeUpdate(tx *gorm.DB) (err error) {
  // if Role changed
    if tx.Statement.Changed("Role") {
    return errors.New("role not allowed to change")
    }

  if tx.Statement.Changed("Name", "Admin") { // if Name or Role changed
    tx.Statement.SetColumn("Age", 18)
  }

  // if any fields changed
    if tx.Statement.Changed() {
        tx.Statement.SetColumn("RefreshedAt", time.Now())
    }
    return nil
}

db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu2"})
// Changed("Name") => true
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(map[string]interface{"name": "jinzhu"})
// Changed("Name") => false, `Name` not changed
db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(map[string]interface{
  "name": "jinzhu2", "admin": false,
})
// Changed("Name") => false, `Name` not selected to update

db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu2"})
// Changed("Name") => true
db.Model(&User{ID: 1, Name: "jinzhu"}).Updates(User{Name: "jinzhu"})
// Changed("Name") => false, `Name` not changed
db.Model(&User{ID: 1, Name: "jinzhu"}).Select("Admin").Updates(User{Name: "jinzhu2"})
// Changed("Name") => false, `Name` not selected to update
```

--------------------------------

### GORM Upsert: Update All Applicable Columns

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Performs an Upsert operation using GORM's `clause.OnConflict` that updates all columns (except primary keys and those with SQL function defaults) to their new values from the inserted data on conflict.

```Go
db.Clauses(clause.OnConflict{
  UpdateAll: true,
}).Create(&users)
```

--------------------------------

### GORM Go Where Clause Interaction with Struct Primary Key

Source: https://github.com/yys190/gorm-docs/blob/main/query.md

Illustrates that when a Where clause is applied to a query targeting a struct that already has a primary key value set, GORM combines the Where condition with the primary key condition using AND. This can result in no records being found if the conditions conflict.

```Go
var user = User{ID: 10}
db.Where("id = ?", 20).First(&user)
// SELECT * FROM users WHERE id = 10 and id = 20 ORDER BY id ASC LIMIT 1
```

--------------------------------

### Generated SQL with Insert Modifier (SQL)

Source: https://github.com/yys190/gorm-docs/blob/main/sql_builder.md

The resulting SQL query string generated by GORM when using the clause.Insert with the IGNORE modifier, showing INSERT IGNORE INTO ....

```SQL
INSERT IGNORE INTO users (name,age...) VALUES ("jinzhu",18...);
```

--------------------------------

### GORM Upsert: Do Nothing on Conflict

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Performs an Upsert operation using GORM's `clause.OnConflict` where the database does nothing if a conflict occurs.

```Go
import "gorm.io/gorm/clause"

// Do nothing on conflict
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)
```

--------------------------------

### Modifying Values During GORM Update Hooks with SetColumn

Source: https://github.com/yys190/gorm-docs/blob/main/update.md

Illustrates how to use `tx.Statement.SetColumn` within a GORM hook (like `BeforeSave`) to modify the values being updated. This method is recommended for changing values during partial updates, such as hashing a password or updating a timestamp, before the database operation is executed.

```go
func (user *User) BeforeSave(tx *gorm.DB) (err error) {
  if pw, err := bcrypt.GenerateFromPassword(user.Password, 0); err == nil {
    tx.Statement.SetColumn("EncryptedPassword", pw)
  }

  if tx.Statement.Changed("Code") {
    user.Age += 20
    tx.Statement.SetColumn("Age", user.Age)
  }
}

db.Model(&user).Update("Name", "jinzhu")
```

--------------------------------

### GORM Upsert: Update Column Using SQL Expression

Source: https://github.com/yys190/gorm-docs/blob/main/create.md

Performs an Upsert operation using GORM's `clause.OnConflict` that updates a column using a SQL expression (`GREATEST`) on conflict based on the 'id' column.

```Go
db.Clauses(clause.OnConflict{
  Columns:   []clause.Column{{Name: "id"}},
  DoUpdates: clause.Assignments(map[string]interface{}{"count": gorm.Expr("GREATEST(count, VALUES(count))")}),
}).Create(&users)
```
Tokens:
Raw