CRUD Interface - Raw SQL & SQL Builder - 《GORM v1.23.5 Documentation》

Raw SQL
Named Argument
DryRun Mode
ToSQL
Row & Rows
Scan *sql.Rows into struct
Connection
Advanced

Raw SQL

Query Raw SQL with Scan

1. type Result struct {
2. ID   int
3. Name string
4. Age  int
5. }

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

10. db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)

12. var age int
13. db.Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Scan(&age)

15. var users []User
16. db.Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Scan(&users)

Exec with Raw SQL

1. db.Exec("DROP TABLE users")
2. db.Exec("UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})

4. // Exec with SQL Expression
5. db.Exec("UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")

NOTE GORM allows cache prepared statement to increase performance, checkout Performance for details

Named Argument

GORM supports named arguments with sql.NamedArg, map[string]interface{}{} or struct, for example:

1. db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
2. // SELECT * FROM `users` WHERE name1 = "jinzhu" OR name2 = "jinzhu"

4. db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3)
5. // SELECT * FROM `users` WHERE name1 = "jinzhu2" OR name2 = "jinzhu2" ORDER BY `users`.`id` LIMIT 1

7. // Named Argument with Raw SQL
8. db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
9. sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user)
10. // SELECT * FROM users WHERE name1 = "jinzhu1" OR name2 = "jinzhu2" OR name3 = "jinzhu1"

12. db.Exec("UPDATE users SET name1 = @name, name2 = @name2, name3 = @name",
13. sql.Named("name", "jinzhunew"), sql.Named("name2", "jinzhunew2"))
14. // UPDATE users SET name1 = "jinzhunew", name2 = "jinzhunew2", name3 = "jinzhunew"

16. db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2",
17. map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(&user)
18. // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"

20. type NamedArgument struct {
21. Name string
22. Name2 string
23. }

25. db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2",
26. NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(&user)
27. // SELECT * FROM users WHERE (name1 = "jinzhu" AND name3 = "jinzhu") AND name2 = "jinzhu2"

DryRun Mode

Generate SQL and its arguments without executing, can be used to prepare or test generated SQL, Checkout Session for details

1. stmt := db.Session(&Session{DryRun: true}).First(&user, 1).Statement
2. stmt.SQL.String() //=> SELECT * FROM `users` WHERE `id` = $1 ORDER BY `id`
3. stmt.Vars         //=> []interface{}{1}

ToSQL

Returns generated SQL without executing.

GORM uses the database/sql’s argument placeholders to construct the SQL statement, which will automatically escape arguments to avoid SQL injection, but the generated SQL don’t provide the safety guarantees, please only use it for debugging.

1. sql := DB.ToSQL(func(tx *gorm.DB) *gorm.DB {
2. return tx.Model(&User{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&[]User{})
3. })
4. sql //=> SELECT * FROM "users" WHERE id = 100 AND "users"."deleted_at" IS NULL ORDER BY age desc LIMIT 10

`Row` & `Rows`

Get result as *sql.Row

1. // Use GORM API build SQL
2. row := db.Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row()
3. row.Scan(&name, &age)

5. // Use Raw SQL
6. row := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row()
7. row.Scan(&name, &age, &email)

Get result as *sql.Rows

1. // Use GORM API build SQL
2. rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows()
3. defer rows.Close()
4. for rows.Next() {
5. rows.Scan(&name, &age, &email)

7. // do something
8. }

10. // Raw SQL
11. rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows()
12. defer rows.Close()
13. for rows.Next() {
14. rows.Scan(&name, &age, &email)

16. // do something
17. }

Checkout FindInBatches for how to query and process records in batch Checkout Group Conditions for how to build complicated SQL Query

Scan `*sql.Rows` into struct

Use ScanRows to scan a row into a struct, for example:

1. rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error)
2. defer rows.Close()

4. var user User
5. for rows.Next() {
6. // ScanRows scan a row into user
7. db.ScanRows(rows, &user)

9. // do something
10. }

Connection

Run mutliple SQL in same db tcp connection (not in a transaction)

1. db.Connection(func(tx *gorm.DB) error {
2. tx.Exec("SET my.role = ?", "admin")

4. tx.First(&User{})
5. })

Advanced

Clauses

GORM uses SQL builder generates SQL internally, for each operation, GORM creates a *gorm.Statement object, all GORM APIs add/change Clause for the Statement, at last, GORM generated SQL based on those clauses

For example, when querying with First, it adds the following clauses to the Statement

1. clause.Select{Columns: "*"}
2. clause.From{Tables: clause.CurrentTable}
3. clause.Limit{Limit: 1}
4. clause.OrderByColumn{
5. Column: clause.Column{Table: clause.CurrentTable, Name: clause.PrimaryKey},
6. }

Then GORM build finally querying SQL in the Query callbacks like:

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

Which generate SQL:

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

You can define your own Clause and use it with GORM, it needs to implements Interface

Check out examples for reference

Clause Builder

For different databases, Clauses may generate different SQL, for example:

1. db.Offset(10).Limit(5).Find(&users)
2. // Generated for SQL Server
3. // SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY
4. // Generated for MySQL
5. // SELECT * FROM `users` LIMIT 5 OFFSET 10

Which is supported because GORM allows database driver register Clause Builder to replace the default one, take the Limit as example

Clause Options

GORM defined Many Clauses, and some clauses provide advanced options can be used for your application

Although most of them are rarely used, if you find GORM public API can’t match your requirements, may be good to check them out, for example:

1. db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user)
2. // INSERT IGNORE INTO users (name,age...) VALUES ("jinzhu",18...);

StatementModifier

GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example

1. import "gorm.io/hints"

3. db.Clauses(hints.New("hint")).Find(&User{})
4. // SELECT * /*+ hint */ FROM `users`

Raw SQL & SQL Builder