Your Question

How to get SQL statement before run

I tried to use 

db.Statement.SQL.String()

and 

db.Session(&gorm.Session{QueryFields: true}).Statement.SQL.String()

to get the sql before execute it, but it seem like always return empty.

Is there any way I can get the sql statement before I execute it?

The document you expected this should be explained

https://gorm.io/docs/advanced_query.html

Expected answer

How to get sql statement from sql builder before execute the query

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: insanePi

Not sure if this is good way of doing this, but you can use callbacks.BuildQuerySQL(db) to build query and then use db.Statement.SQL.String(). Please note that this doesn't include other callbacks like BeforeQuery, AfterQuery etc.

Comment From: hengway

Thank you for the suggestion, but it seem like not working in my side Below is my sample code:

query := db.Table("table_name1").
        Select("column1",
               "column2",
               "column3").
        Joins("INNER JOIN table_name2 table_name1.column1 = table_name2 .column1").
        Where(filter)

callbacks.BuildQuerySQL(query)
fmt.Print("query.Statement.SQL--->" + query.Statement.SQL.String())

Comment From: insanePi

Sorry, my bad. I had wrong understanding of the code. This can achieved by setting DryRun option to true in db.Config. But this changes configuration of db instance variable. You may have to reset it to false after execution depending on your use case.

IMO, providing DryRun option at Statement level will be more useful than in Config.

Comment From: a631807682

https://gorm.io/docs/sql_builder.html#ToSQL

Comment From: hengway

@insanePi can help suggest how can we set DryRun on statement level? Because I tried below code

db.DryRun = true
db.Find(&model)
db.Statement.SQL.String()
db.DryRun = false

db.Find(&model).Error

the sql statement is returned, but the final execution show dry run mode unsupported error

Comment From: insanePi

@insanePi can help suggest how can we set DryRun on statement level? Because I tried below code

``` db.DryRun = true db.Find(&model) db.Statement.SQL.String() db.DryRun = false

db.Find(&model).Error ```

the sql statement is returned, but the final execution show dry run mode unsupported error

It is not possible to do it as of now. I was just suggesting it could be done as a new feature. But in a way, it is already supported by https://gorm.io/docs/sql_builder.html#ToSQL.

Comment From: risnandym

you must return dry run to false like this:

db.DryRun = false
db.Config.DryRun = false

Comment From: lukx33

GORM v1.25.12

db.DryRun = true
query := db.Create(obj)

fmt.Println(query.Dialector.Explain(query.Statement.SQL.String(), query.Statement.Vars...))

db.DryRun = false
err := db.Create(obj).Error
...

It works, but it doesn't look very elegant. Especially when the query is more complicated, e.g. query := db.Model(&table{}).Where("ID = ?", o.ID).Select("Name").Limit(1).Offset(2)

I would like something like this:

query := db.Model(&table{}).Where("ID = ?", o.ID).Select("Name").Limit(1).Offset(2)`
fmt.Println(query.ToSQL())
err := query.Execute()

This is a better way:

sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
    return tx.Create(obj)
})
fmt.Println(sql)

But not ideally. When you build a query in another function, and here you just want to execute it and get the SQL statement