Skip to main content
GORM’s SQL builder gives you full control over the SQL that reaches your database. You can execute raw queries, inspect generated SQL without running it, add optimizer hints, and use expressions in updates.

​
Raw SQL queries

Use db.Raw to write a full SQL statement and scan the results into any struct or slice. 
type Result struct {
    Name string
    Age  int
}

var result Result
db.Raw("SELECT name, age FROM users WHERE name = ?", "Alice").Scan(&result)

var results []Result
db.Raw("SELECT name, age FROM users WHERE age > ?", 18).Scan(&results)

​
Executing raw SQL

Use db.Exec for statements that do not return rows — INSERT, UPDATE, DELETE, DDL, etc. 
db.Exec("UPDATE users SET name = ? WHERE id = ?", "Bob", 1)
db.Exec("DELETE FROM orders WHERE created_at < ?", cutoff)
db.Exec("ALTER TABLE users ADD COLUMN bio TEXT")
Check RowsAffected on the returned *gorm.DB to see how many rows were changed: 
result := db.Exec("UPDATE users SET active = ? WHERE last_login < ?", false, cutoff)
fmt.Println(result.RowsAffected)

​
Named arguments

Use sql.Named or a map[string]interface{} to pass named placeholders. Any SQL containing @ is treated as a named expression. 
import "database/sql"

// Using sql.Named
db.Where(
    "name = @name OR email = @email",
    sql.Named("name", "Alice"),
    sql.Named("email", "[email protected]"),
).Find(&user)

// Using a map
db.Where(
    "name = @name AND age > @age",
    map[string]interface{}{"name": "Alice", "age": 18},
).Find(&users)

// In Raw
db.Raw(
    "SELECT * FROM users WHERE name = @name OR email = @email",
    sql.Named("name", "Alice"),
    sql.Named("email", "[email protected]"),
).Scan(&results)

​
Generating SQL without executing

db.ToSQL runs the provided function in DryRun mode and returns the final SQL string without sending anything to the database. 
sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
    return tx.Model(&User{}).Where(&User{Name: "Alice", Age: 20}).
        Limit(10).Offset(5).
        Order("name ASC").
        Find(&[]User{})
})
fmt.Println(sql)
// SELECT * FROM `users` WHERE `name` = 'Alice' AND `age` = 20
//   ORDER BY name ASC LIMIT 10 OFFSET 5
Alternatively, enable DryRun mode on a session to inspect stmt.SQL and stmt.Vars directly: 
stmt := db.Session(&gorm.Session{DryRun: true}).Find(&users).Statement
fmt.Println(stmt.SQL.String()) // the SQL template
fmt.Println(stmt.Vars)         // the bound values

​
SQL expressions in updates

Use gorm.Expr to embed raw SQL expressions inside update operations. 
// Increment a counter atomically
db.Model(&Product{}).Where("id = ?", 1).Update("stock", gorm.Expr("stock - ?", 1))

// Computed column update
db.Model(&Product{ID: 1}).Updates(map[string]interface{}{
    "price": gorm.Expr("price * ? + ?", 2, 100),
})
// UPDATE products SET price = price * 2 + 100 WHERE id = 1

​
Clause expressions

Use clause.Expr when you need a raw SQL fragment anywhere a clause.Expression is accepted. 
import "gorm.io/gorm/clause"

db.Select(clause.Expr{SQL: "COALESCE(name, ?)", Vars: []interface{}{"unknown"}}).Find(&users)

​
Locking

Add clause.Locking to any select query to control row-level locking. 
// FOR UPDATE lock
db.Clauses(clause.Locking{Strength: "UPDATE"}).Find(&users)

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

// WITH NOWAIT option (not supported by all databases)
db.Clauses(clause.Locking{Strength: "UPDATE", Options: "NOWAIT"}).Find(&users)

​
Upsert with OnConflict

clause.OnConflict controls what happens when a unique constraint violation occurs. 
// Update all columns to the new values on conflict
db.Clauses(clause.OnConflict{UpdateAll: true}).Create(&user)

// Update specific columns on conflict
db.Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "email"}},
    DoUpdates: clause.AssignmentColumns([]string{"name", "updated_at"}),
}).Create(&user)

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

​
Optimizer hints

Use the hints package (available in gorm.io/hints) to add database optimizer hints without writing raw SQL. 
import "gorm.io/hints"

// Force an index
db.Clauses(hints.UseIndex("idx_user_name")).Find(&users)

// Set a query comment
db.Clauses(hints.Comment("select", "master")).Find(&users)

Build docs developers (and LLMs) love

Get started for freeTalk to us