Raw SQL
Query Raw SQL with Scan
type Result struct { |
Exec
with Raw SQL
db.Exec("DROP TABLE users") |
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:
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user) |
DryRun Mode
Generate SQL
and its arguments without executing, can be used to prepare or test generated SQL, Checkout Session for details
stmt := db.Session(&gorm.Session{DryRun: true}).First(&user, 1).Statement |
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.
sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB { |
Row
& Rows
Get result as *sql.Row
// Use GORM API build SQL |
Get result as *sql.Rows
// Use GORM API build SQL |
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:
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error) |
Connection
Run mutliple SQL in same db tcp connection (not in a transaction)
db.Connection(func(tx *gorm.DB) error { |
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
var limit = 1 |
Then GORM build finally querying SQL in the Query
callbacks like:
Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR") |
Which generate SQL:
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:
db.Offset(10).Limit(5).Find(&users) |
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:
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user) |
StatementModifier
GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example
import "gorm.io/hints" |