CODEWITHSUNDEEP
gogo-gorm
Alvaro Garcia
Alvaro Garcia

Reputation: 39

How can I generate SQL code from GORM struct model?

I'm using goose to manage my database migrations but I need to write SQL sentences directly in the migrations file. There is a way to generate the SQL directly from the GORM model?

Upvotes: 1

Views: 5500

Answers (3)

Sunary
Sunary

Reputation: 121

you can using this lib: https://github.com/sunary/sqlize

It's allowed you create sql from models, also support migration by differ between models and existing sql.

Upvotes: 2

Ezequiel Muns
Ezequiel Muns

Reputation: 7762

Unfortunately using the gorm.Session{DryRun: true} option doesn't make the migration SQL statement/s available to the caller as it does with normal queries.

The only way I can see right now would be to capture the SQL that is run by the migration when it's being logged by reimplementing the gorm.io/gorm/logger.Interface interface. Specifically, the Trace method.

type Interface interface {
    LogMode(LogLevel) Interface
    Info(context.Context, string, ...interface{})
    Warn(context.Context, string, ...interface{})
    Error(context.Context, string, ...interface{})
    Trace(ctx context.Context, begin time.Time, fc func() (string, int64), err error)
}

Inside Trace you can call that fc function argument to get the SQL and RowsAffected, which you can do whatever you want with.

For example:

import (
    "time"
    "context"
    "gorm.io/gorm/logger"
)

type RecorderLogger struct {
    logger.Interface
    Statements []string
}

func (r *RecorderLogger) Trace(ctx context.Context, begin time.Time, fc func() (string, int64), err error) {
    sql, _ := fc()
    r.Statements = append(r.Statements, sql)
}

Now use it as:

recorder := RecorderLogger{logger.Default.LogMode(logger.Info)}
session := db.Session(&gorm.Session{
    Logger: &recorder
})
session.AutoMigrate(&Model{}, ...)
// or 
session.Migrator().CreateTable(&Model{}, ...) // or any method therein
// now recorder.Statements contains the statements run during migration

This is very hacky, and you may run into problems because AutoMigrate modifies the current state of the database and migrates it up to what your model requires (up to a point) and for that to work your current database must reflect the current state of your production database (or whatever database your hope to migrate). So, you could build that tool that helps you get the migration script started if you're careful, but to properly gain the advantages of a migration system like goose you'll need to get your hands dirty with the SQL :)

Upvotes: 2

Chris Townsend
Chris Townsend

Reputation: 2716

I personally would use the migration functionality that is available inside Gorm, but for your case we can do the following.

Firstly there is a feature in Gorm called Dry Run and you can use this to see the SQL statements that get executed when performing queries. Unfortunately I can't see that it is possible when using migrations. So what I suggest is to use github.com/DATA-DOG/go-sqlmock

I would usually use this for testing purposes, but you could use it temporarily to get the SQL needed for your separate migrations.

package main

import (
    "database/sql"
    "time"

    "github.com/DATA-DOG/go-sqlmock"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type Model struct {
    ID          uint64 `gorm:"primaryKey"`
    Name        string `gorm:"index"`
    Description string
    CreatedAt   time.Time
    LastLogin   sql.NullTime
}

func main() {
    sqlDB, _, err := sqlmock.New()
    if err != nil {
        panic(err)
    }

    gormDB, err := gorm.Open(mysql.New(mysql.Config{
        Conn:                      sqlDB,
        SkipInitializeWithVersion: true,
    }), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    defer sqlDB.Close()

    gormDB.AutoMigrate(&Model{})
}

This will give you a result like this

all expectations were already fulfilled, call to ExecQuery 'CREATE TABLE `models` (`id` bigint unsigned AUTO_INCREMENT,`name` varchar(191),`description` longtext,`created_at` datetime(3) NULL,`last_login` datetime(3) NULL,PRIMARY KEY (`id`),INDEX idx_models_name (`name`))' with args [] was not expected
[0.003ms] [rows:0] CREATE TABLE `models` (`id` bigint unsigned AUTO_INCREMENT,`name` varchar(191),`description` longtext,`created_at` datetime(3) NULL,`last_login` datetime(3) NULL,PRIMARY KEY (`id`),INDEX idx_models_name (`name`))

which contains the SQL statement required. This feels incredibly hacky but will give you the result you need

Upvotes: 0

Related Questions