Gorm: Plan for migration generation tool

[raphaelvigee]

Describe the feature

Is there any plan for creating a tool to generate migration files from DB (just like django does) to be integrated with goose or similar?

Motivation

My issue is that I dont wanna have to manually write all those migrations queries, but rather have them generated like django migrations work (create a diff between model/db, dump up and down queries to files and then run those migrations during deploy).

---

See below comments for further details

I was able to hacky do it by listening on the automigrate queries and making them no-op and redirecting them to a file, but thats far from being ideal.

For reference:

    stmts := make([]string, 0)
    if err := db.Callback().Raw().Replace("gorm:raw", func(db *gorm.DB) {
        stmts = append(stmts, db.Statement.SQL.String())
    }); err != nil {
        panic(err)
    }

    if err := db.AutoMigrate(models...); err != nil {
        panic(err)
    }

question

[jinzhu]

https://v2.gorm.io/docs/migration.html

https://github.com/go-gormigrate/gormigrate

[raphaelvigee]

Thank you for the v2 docs link, but I dont think your answer covers the queries generation, there is no way to generate the Up/Down migrations without copy/paste the automigrate code and add the appropriate couple (for each create, add a delete etc)

Could you suggest a way to hook into the automigrate code ?

[jinzhu]

Hello @raphaelvigee

You can create a *gorm.DB with debug mode enabled and configured its logger to print executed SQL into a file.

But I would suggest you use migration tool like https://github.com/go-gormigrate/gormigrate

[raphaelvigee]

Hi @jinzhu

I do plan on using https://github.com/golang-migrate/migrate.
My issue is that I dont wanna have to manually write all those migrations queries, but rather have them generated like django migrations work (create a diff between model/db, dump up and down queries to files and then run those migrations during deploy).

You can create a *gorm.DB with debug mode enabled and configured its logger to print executed SQL into a file.

This would only generate up queries, and makes it difficult to generate the down ones automatically.

The solution I explored (with partial success) is creating a custom Dialector, overriding the Migrator function to return a custom Migrator:

type Dialector struct {
    gorm.Dialector
    UpOut   io.ReadWriter
    DownOut io.ReadWriter
}

func (d Dialector) Migrator(db *gorm.DB) gorm.Migrator {
    upDb := db.Session(&gorm.Session{})
    if err := upDb.Callback().Raw().Replace("gorm:raw", func(db *gorm.DB) {
        d.UpOut.Write([]byte(db.Statement.SQL.String()))
        d.UpOut.Write([]byte(";\n"))
    }); err != nil {
        panic(err)
    }

    downDb := db.Session(&gorm.Session{})
    if err := downDb.Callback().Raw().Replace("gorm:raw", func(db *gorm.DB) {
        d.DownOut.Write([]byte(db.Statement.SQL.String()))
        d.DownOut.Write([]byte(";\n"))
    }); err != nil {
        panic(err)
    }

    return GenMigrator{
        Migrator: d.Dialector.Migrator(db).(mysql.Migrator),
        Up: HookedMigrator{
            Migrator: d.Dialector.Migrator(upDb),
            Out:      d.UpOut,
        },
        Down: HookedMigrator{
            Migrator: d.Dialector.Migrator(downDb),
            Out:      d.DownOut,
        },
    }
}

type GenMigrator struct {
    mysql.Migrator
    Up   HookedMigrator
    Down HookedMigrator
}

type HookedMigrator struct {
    gorm.Migrator
    Out io.ReadWriter
}

func (m GenMigrator) CreateTable(dst ...interface{}) error {
    if err := m.Up.CreateTable(dst...); err != nil {
        return err
    }
    if err := m.Down.DropTable(dst...); err != nil {
        return err
    }

    return nil
}

... [omitted for brevity]

I'm sure this could be turned into a first class feature with your expertise

[raphaelvigee]

Update, I got it to work by changing the logic that collects the statements, it seems that the two Callback().Replace() override each other, even though they are in different sessions (need to deep clone the config callbacks when creating a new session ?)

Here is the result:

====================== UP:
CREATE TABLE `images` (`id` varchar(191),`created_at` datetime(3) NULL,`updated_at` datetime(3) NULL,`deleted_at` datetime(3) NULL,`filename` longtext,`source_id` longtext,PRIMARY KEY (`id`),INDEX idx_images_deleted_at (`deleted_at`),CONSTRAINT `fk_sources_images` FOREIGN KEY (`source_id`) REFERENCES `sources`(`id`));
CREATE TABLE `sources` (`id` varchar(191),`created_at` datetime(3) NULL,`updated_at` datetime(3) NULL,`deleted_at` datetime(3) NULL,`name` longtext,`url` longtext,PRIMARY KEY (`id`),INDEX idx_sources_deleted_at (`deleted_at`));
CREATE TABLE `layers` (`id` varchar(191),`created_at` datetime(3) NULL,`updated_at` datetime(3) NULL,`deleted_at` datetime(3) NULL,`source_id` longtext,`config` longtext,`popup` longtext,PRIMARY KEY (`id`),INDEX idx_layers_deleted_at (`deleted_at`),CONSTRAINT `fk_sources_layers` FOREIGN KEY (`source_id`) REFERENCES `sources`(`id`));

====================== DOWN:
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `layers` CASCADE;
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `sources` CASCADE;
SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;
DROP TABLE IF EXISTS `images` CASCADE;
SET FOREIGN_KEY_CHECKS = 0;

I believe this would be worth reopening

[jinzhu]

You can use http://v2.gorm.io/docs/logger.html to collect SQL

Callbacks are registered into the global *gorm.DB, not the session-level, if you require *gorm.DB with different callbacks, you need to initialize another *gorm.DB, just added it to http://v2.gorm.io/docs/write_plugins.html ;)

[raphaelvigee]

You can use http://v2.gorm.io/docs/logger.html to collect SQL

Yeah my first go at this was to collect SQL through the logger, but the drawback is that it makes it hard to auto generate the down migration.

Would you be interested in a PR for auto generation of up/down migration with the above method ?