or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

associations.md clause.md database-operations.md hooks.md index.md logger.md migrations.md query-building.md schema.md transactions.md

tile.json

Query Building

GORM provides a chainable API for building complex SQL queries. Most query methods return *DB, allowing you to chain multiple methods together.

Chainable Methods

Chainable methods build up the query but don't execute it. Append a finisher method to execute the query.

Specifying Model

Specify the table/model for the query.

func (db *DB) Model(value interface{}) *DB

Usage:

// Use model type to determine table
db.Model(&User{}).Where("age > ?", 18).Find(&users)

// Use model value
user := User{ID: 10}
db.Model(&user).Update("Age", 26)

Table Name

Explicitly specify the table name.

func (db *DB) Table(name string, args ...interface{}) *DB

Usage:

// Simple table name
db.Table("users").Where("age > ?", 18).Find(&users)

// Dynamic table name with arguments
db.Table("users_?", "2023").Find(&users)

// Use for raw queries
var result []map[string]interface{}
db.Table("users").Select("name, age").Where("age > ?", 18).Find(&result)

WHERE Conditions

Add WHERE conditions to filter query results.

func (db *DB) Where(query interface{}, args ...interface{}) *DB

Usage:

// String conditions with placeholders
db.Where("name = ?", "Alice").Find(&users)
db.Where("name = ? AND age >= ?", "Alice", 18).Find(&users)

// Struct conditions (only non-zero fields)
db.Where(&User{Name: "Alice", Age: 18}).Find(&users)

// Map conditions
db.Where(map[string]interface{}{
    "name": "Alice",
    "age":  0,  // Will be included in query
}).Find(&users)

// Slice of primary keys
db.Where([]int64{1, 2, 3}).Find(&users)

// Multiple WHERE calls are combined with AND
db.Where("name = ?", "Alice").Where("age > ?", 18).Find(&users)

Inline Conditions

Specify conditions directly in query methods.

// In First, Take, Last, Find
db.First(&user, "name = ?", "Alice")
db.Find(&users, "age > ?", 18)

// Primary key
db.First(&user, 10)  // WHERE id = 10
db.Find(&users, []int{1, 2, 3})  // WHERE id IN (1, 2, 3)

NOT Conditions

Add NOT conditions.

func (db *DB) Not(query interface{}, args ...interface{}) *DB

Usage:

db.Not("name = ?", "Alice").Find(&users)
db.Not(User{Name: "Alice"}).Find(&users)
db.Not(map[string]interface{}{"name": []string{"Alice", "Bob"}}).Find(&users)

// NOT IN
db.Not("name IN ?", []string{"Alice", "Bob"}).Find(&users)

OR Conditions

Add OR conditions.

func (db *DB) Or(query interface{}, args ...interface{}) *DB

Usage:

db.Where("name = ?", "Alice").Or("name = ?", "Bob").Find(&users)
db.Where("name = ?", "Alice").Or(User{Name: "Bob"}).Find(&users)

// Complex OR groups
db.Where("age > ?", 18).Or(
    db.Where("country = ?", "US").Where("state = ?", "CA"),
).Find(&users)

Selecting Fields

Specify which fields to retrieve.

func (db *DB) Select(query interface{}, args ...interface{}) *DB

Usage:

// Select specific fields
db.Select("name", "age").Find(&users)
db.Select("name, age").Find(&users)

// Select with expressions
db.Select("name, age, age * 2 as double_age").Find(&users)

// Select with aggregate functions
db.Model(&User{}).Select("COUNT(*)").Row().Scan(&count)
db.Model(&User{}).Select("COALESCE(age, 0)").Find(&users)

Omitting Fields

Omit specific fields from the query.

func (db *DB) Omit(columns ...string) *DB

Usage:

// Omit password field
db.Omit("password").Find(&users)

// Omit multiple fields
db.Omit("password", "secret_token").Find(&users)

// Useful in updates
db.Model(&user).Omit("UpdatedAt").Updates(map[string]interface{}{
    "Name": "Alice",
})

Ordering

Specify the order of query results.

func (db *DB) Order(value interface{}) *DB

Usage:

// Simple ordering
db.Order("age DESC").Find(&users)
db.Order("age").Find(&users)  // ASC by default

// Multiple order columns
db.Order("age DESC, name").Find(&users)

// Order by expression
db.Order("FIELD(id, 3, 1, 2)").Find(&users)

// Multiple Order calls
db.Order("age DESC").Order("name ASC").Find(&users)

Limiting Results

Limit the number of records returned.

func (db *DB) Limit(limit int) *DB

Usage:

// Get first 10 records
db.Limit(10).Find(&users)

// Cancel limit with -1
db.Limit(10).Find(&users1)
db.Limit(-1).Find(&users2)  // Get all records

Offset

Skip a specified number of records.

func (db *DB) Offset(offset int) *DB

Usage:

// Skip first 10 records
db.Offset(10).Find(&users)

// Pagination
page := 3
pageSize := 20
db.Offset((page - 1) * pageSize).Limit(pageSize).Find(&users)

// Cancel offset with -1
db.Offset(10).Find(&users1)
db.Offset(-1).Find(&users2)

DISTINCT

Select distinct values.

func (db *DB) Distinct(args ...interface{}) *DB

Usage:

// Distinct all columns
db.Distinct("name").Find(&users)

// Count distinct
var count int64
db.Model(&User{}).Distinct("name").Count(&count)

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

GROUP BY

Group query results.

func (db *DB) Group(name string) *DB

Usage:

type Result struct {
    Name  string
    Total int
}

var results []Result
db.Model(&User{}).Select("name, sum(age) as total").Group("name").Find(&results)

// Group by multiple columns
db.Model(&User{}).Select("name, age, count(*) as total").Group("name").Group("age").Find(&results)

HAVING

Filter grouped results.

func (db *DB) Having(query interface{}, args ...interface{}) *DB

Usage:

type Result struct {
    Name  string
    Total int
}

var results []Result
db.Model(&User{}).Select("name, sum(age) as total").
    Group("name").
    Having("sum(age) > ?", 100).
    Find(&results)

// Multiple HAVING conditions
db.Model(&User{}).
    Select("name, count(*) as count, avg(age) as avg_age").
    Group("name").
    Having("count(*) > ?", 5).
    Having("avg(age) > ?", 18).
    Find(&results)

Joins

Add JOIN clauses to the query.

// Add JOIN clause
func (db *DB) Joins(query string, args ...interface{}) *DB

// Add INNER JOIN clause
func (db *DB) InnerJoins(query string, args ...interface{}) *DB

Usage:

type Result struct {
    UserName  string
    OrderID   int
}

var results []Result

// Simple join
db.Model(&User{}).Select("users.name, orders.id").
    Joins("left join orders on orders.user_id = users.id").
    Find(&results)

// Join with conditions
db.Model(&User{}).
    Joins("JOIN orders ON orders.user_id = users.id AND orders.status = ?", "completed").
    Find(&users)

// Multiple joins
db.Model(&User{}).
    Joins("JOIN orders ON orders.user_id = users.id").
    Joins("JOIN items ON items.order_id = orders.id").
    Find(&users)

// Preload with join (to avoid N+1)
db.Joins("Company").Find(&users)  // Preload Company with INNER JOIN

Preloading (Eager Loading)

Preload associations to avoid N+1 queries.

func (db *DB) Preload(query string, args ...interface{}) *DB

Usage:

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

// Preload with conditions
db.Preload("Orders", "status = ?", "completed").Find(&users)

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

// Multiple preloads
db.Preload("Orders").Preload("Profile").Find(&users)

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

// Custom preload query
db.Preload("Orders", func(db *gorm.DB) *gorm.DB {
    return db.Order("orders.created_at DESC").Limit(10)
}).Find(&users)

Scopes

Apply reusable query logic.

func (db *DB) Scopes(funcs ...func(*DB) *DB) *DB

Usage:

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

func RecentUsers(db *gorm.DB) *gorm.DB {
    return db.Where("created_at > ?", time.Now().AddDate(0, 0, -7))
}

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

// Apply scopes
db.Scopes(ActiveUsers, RecentUsers).Find(&users)

// Apply parameterized scope
db.Scopes(Paginate(2, 20)).Find(&users)

// Chain with other methods
db.Scopes(ActiveUsers).Where("age > ?", 18).Find(&users)

Raw SQL

Execute raw SQL queries.

func (db *DB) Raw(sql string, values ...interface{}) *DB

Usage:

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

// Raw query into custom type
type Result struct {
    Name  string
    Total int
}
var results []Result
db.Raw("SELECT name, COUNT(*) as total FROM users GROUP BY name").Scan(&results)

// Raw query with named parameters
db.Raw("SELECT * FROM users WHERE name = @name AND age > @age",
    sql.Named("name", "Alice"),
    sql.Named("age", 18)).Scan(&users)

SQL Expressions

Use SQL expressions in queries.

import "gorm.io/gorm/clause"

// In WHERE
db.Where("age > ?", 18).Or(
    clause.Expr("name LIKE ?", "%test%"),
).Find(&users)

// In SELECT
db.Select(clause.Expr("COALESCE(name, ?)", "unknown")).Find(&users)

// In UPDATE
db.Model(&User{}).
    Where("id = ?", 1).
    Update("balance", clause.Expr("balance + ?", 100))

Clauses

Add custom SQL clauses.

import "gorm.io/gorm/clause"

func (db *DB) Clauses(conds ...clause.Expression) *DB

Usage:

import "gorm.io/gorm/clause"

// Add ON CONFLICT clause for upsert
db.Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "id"}},
    DoUpdates: clause.AssignmentColumns([]string{"name", "age"}),
}).Create(&users)

// Add locking clause
db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)

// Add specific hint
db.Clauses(clause.Expr{SQL: "USE INDEX (idx_name)"}).Find(&users)

Scanning Results

Scan query results into variables.

func (db *DB) Scan(dest interface{}) *DB

Usage:

// Scan into struct
type Result struct {
    Name string
    Age  int
}
var result Result
db.Table("users").Select("name, age").Where("id = ?", 1).Scan(&result)

// Scan into slice of structs
var results []Result
db.Table("users").Select("name, age").Scan(&results)

// Scan into map
var result map[string]interface{}
db.Table("users").Select("name, age").Where("id = ?", 1).Scan(&result)

// Scan single row
var name string
var age int
db.Table("users").Where("id = ?", 1).Row().Scan(&name, &age)

Query to SQL (Dry Run)

Convert query to SQL string without executing.

func (db *DB) ToSQL(queryFn func(tx *DB) *DB) string

Usage:

sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
    return tx.Model(&User{}).Where("age > ?", 18).
        Order("age DESC").
        Limit(10).
        Find(&[]User{})
})
fmt.Println(sql)  // Prints the generated SQL

Row and Rows

Get raw sql.Row or sql.Rows from query.

// Get sql.Row from query
func (db *DB) Row() *sql.Row

// Get sql.Rows from query
func (db *DB) Rows() (*sql.Rows, error)

// Scan sql.Rows into struct
func (db *DB) ScanRows(rows *sql.Rows, dest interface{}) error

Usage:

// Single row
var name string
var age int
row := db.Table("users").Select("name, age").Where("id = ?", 1).Row()
row.Scan(&name, &age)

// Multiple rows
rows, err := db.Model(&User{}).Where("age > ?", 18).Rows()
defer rows.Close()

for rows.Next() {
    var user User
    db.ScanRows(rows, &user)
    // Process user
}

FindInBatches

Process records in batches with a callback.

func (db *DB) FindInBatches(dest interface{}, batchSize int, fc func(tx *DB, batch int) error) *DB

Usage:

var users []User
result := db.Where("age > ?", 18).FindInBatches(&users, 100, func(tx *gorm.DB, batch int) error {
    // Process users in this batch
    for _, user := range users {
        // Process user
    }

    // Return error to stop batching
    return nil
})

// Check for errors
if result.Error != nil {
    // Handle error
}

// Get total rows processed
fmt.Println(result.RowsAffected)

SubQueries

Use subqueries in your queries.

// Subquery in WHERE
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)

// Subquery in FROM
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).
    Where("age > ?", 18).
    Find(&users)

// Subquery in SELECT
db.Model(&User{}).
    Select("users.*, (?) as order_count",
        db.Model(&Order{}).Select("count(*)").Where("orders.user_id = users.id"),
    ).
    Find(&users)

Named Arguments

Use named arguments in queries.

import "database/sql"

// Named arguments in raw query
db.Raw("SELECT * FROM users WHERE name = @name AND age > @age",
    sql.Named("name", "Alice"),
    sql.Named("age", 18),
).Scan(&users)

// Named arguments in where
db.Where("name = @name AND age > @age",
    sql.Named("name", "Alice"),
    sql.Named("age", 18),
).Find(&users)

Connection

Use a specific connection from the pool for the query.

func (db *DB) Connection(fc func(tx *DB) error) error

Usage:

err := db.Connection(func(tx *gorm.DB) error {
    // All operations in this function use the same connection
    tx.First(&user)
    tx.Create(&order)
    return nil
})

Version

Tile

Files

query-building.md.css-3qkkll{font-size:var(--chakra-font-sizes-sm);font-weight:var(--chakra-font-weights-normal);color:var(--chakra-colors-gray-300);}docs/

Query Building

Chainable Methods

Specifying Model

Table Name

WHERE Conditions

Inline Conditions

NOT Conditions

OR Conditions

Selecting Fields

Omitting Fields

Ordering

Limiting Results

Offset

DISTINCT

GROUP BY

HAVING

Joins

Preloading (Eager Loading)

Scopes

Raw SQL

SQL Expressions

Clauses

Scanning Results

Query to SQL (Dry Run)

Row and Rows

FindInBatches

SubQueries

Named Arguments

Connection

query-building.mddocs/