warning

Please note that the principle of SQL capture in the framework is that any SQL statement template generated by SQL operations, along with SQL execution parameters, is intercepted by the framework before being submitted to the underlying database engine and automatically formatted into a human-readable string by the framework component for reference and debugging purposes only and is not the complete SQL statement submitted to the underlying database engine. The captured SQL statements are identical to the SQL statements output when the ORM component is in debugging mode, as they are generated by the same component.

CatchSQL

We can use the gdb.CatchSQL method to capture the list of SQL executed within a specified range. The method is defined as follows:

// CatchSQL catches and returns all sql statements that are EXECUTED in given closure function.
// Be caution that, all the following sql statements should use the context object passing by function `f`.
func CatchSQL(ctx context.Context, f func(ctx context.Context) error) (sqlArray []string, err error)

As you can see, this method uses a closure function to execute SQL statements. All SQL operations executed within the closure function will be recorded and returned as a []string type. Note that the SQL operations executed within the closure should pass the ctx context object, otherwise the statements corresponding to the SQL operations cannot be recorded. Here is an example:

user.sql

CREATE TABLE `user` (
    `id`          int(10) unsigned NOT NULL AUTO_INCREMENT,
    `passport`    varchar(45) NULL,
    `password`    char(32) NULL,
    `nickname`    varchar(45) NULL,
    `create_time` timestamp(6) NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

main.go

package main

import (
    "context"

    _ "github.com/gogf/gf/contrib/drivers/mysql/v2"
    "github.com/gogf/gf/v2/database/gdb"
    "github.com/gogf/gf/v2/frame/g"
    "github.com/gogf/gf/v2/os/gctx"
    "github.com/gogf/gf/v2/os/gtime"
)

type User struct {
    Id         int
    Passport   string
    Password   string
    Nickname   string
    CreateTime *gtime.Time
}

func initUser(ctx context.Context) error {
    _, err := g.Model("user").Ctx(ctx).Data(User{
        Id:       1,
        Passport: "john",
        Password: "12345678",
        Nickname: "John",
    }).Insert()
    return err
}

func main() {
    var ctx = gctx.New()
    sqlArray, err := gdb.CatchSQL(ctx, func(ctx context.Context) error {
        return initUser(ctx)
    })
    if err != nil {
        panic(err)
    }
    g.Dump(sqlArray)
}

After execution, the terminal outputs:

[
    "SHOW FULL COLUMNS FROM `user`",
    "INSERT INTO `user`(`id`,`passport`,`password`,`nickname`,`created_at`,`updated_at`) VALUES(1,'john','12345678','John','2023-12-19 21:43:57','2023-12-19 21:43:57') ",
]

ToSQL

We can use gdb.ToSQL to convert a given SQL operation into an SQL statement without actually executing it. The method is defined as follows:

// ToSQL formats and returns the last one of sql statements in given closure function
// WITHOUT TRULY EXECUTING IT.
// Be caution that, all the following sql statements should use the context object passing by function `f`.
func ToSQL(ctx context.Context, f func(ctx context.Context) error) (sql string, err error)

As you can see, this method estimates SQL statements through a closure function. All SQL operations within the closure function will be estimated, but only the last SQL statement will be returned as a string type. Note that the SQL operations within the closure should pass the ctx context object, otherwise the statements corresponding to the SQL operations cannot be recorded. Here is an example:

user.sql

CREATE TABLE `user` (
    `id`          int(10) unsigned NOT NULL AUTO_INCREMENT,
    `passport`    varchar(45) NULL,
    `password`    char(32) NULL,
    `nickname`    varchar(45) NULL,
    `create_time` timestamp(6) NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

main.go

package main

import (
    "context"

    _ "github.com/gogf/gf/contrib/drivers/mysql/v2"
    "github.com/gogf/gf/v2/database/gdb"
    "github.com/gogf/gf/v2/frame/g"
    "github.com/gogf/gf/v2/os/gctx"
    "github.com/gogf/gf/v2/os/gtime"
)

type User struct {
    Id         int
    Passport   string
    Password   string
    Nickname   string
    CreateTime *gtime.Time
}

func initUser(ctx context.Context) error {
    _, err := g.Model("user").Ctx(ctx).Data(User{
        Id:       1,
        Passport: "john",
        Password: "12345678",
        Nickname: "John",
    }).Insert()
    return err
}

func main() {
    var ctx = gctx.New()
    sql, err := gdb.ToSQL(ctx, func(ctx context.Context) error {
        return initUser(ctx)
    })
    if err != nil {
        panic(err)
    }
    g.Dump(sql)
}

After execution, the terminal outputs:

"INSERT INTO `user`(`id`,`passport`,`password`,`nickname`,`created_at`,`updated_at`) VALUES(1,'john','12345678','John','2023-12-19 21:49:21','2023-12-19 21:49:21') "