JSON fields and MySQL

[Rudis1261]

I have a table to which I need to add external provider IDs. I was hoping to set a field to JSON and defining it as the struct I will be passing it. And I thought rel would do the marshaling for me when storing it.

But when I do a FindAll it tries to load this information as associated records, which fail to load. I was hoping it would marshal/unmarshal for me. Because preloading this data normally is a tad painful. Since it's 1 -> many.

It this a possible use-case or should I just split them into separate tables and have references back and forth?

For context:

	type Provider struct {
		ID       int    `json:"id"`
		Name     string `json:"name"`
		SeriesID int    `json:"series_id"`
	}

	type Series struct {
		ID        int        `json:"id"`
		SeriesID  int        `json:"series_id"`
		Providers []Provider `json:"providers"`
	}

        schema.AddColumn("series", "providers", rel.JSON, rel.Default(""))
        
        // Migrate series to series providers
	schema.Do(func(ctx context.Context, repo rel.Repository) error {
		var series []Series

		if err := repo.FindAll(ctx, &series); err != nil {
			return err
		}

		for _, serie := range series {
			serie.Providers = []Provider{
				{ID: serie.SeriesID, Name: "tvdb"},
			}

			log.Println(serie)
			
			// This raises error
			if err := repo.Update(ctx, &serie); err != nil {
				return err
			}
		}

		return nil
	})
	
	schema.DropColumn("series", "series_id")

=> Done: migrate 202410282200 alter table series, alter table series, alter table series, run go code in 50.892709ms
        Error:  migrate   sql: Scan error on column index 13, name "providers": unsupported Scan, storing driver.Value type []uint8 into type *[]migrations.Provider
panic: sql: Scan error on column index 13, name "providers": unsupported Scan, storing driver.Value type []uint8 into type *[]migrations.Provider

goroutine 1 [running]:
github.com/go-rel/migration.check(...)
        /Users/rudistrydom/go/pkg/mod/github.com/go-rel/[email protected]/migration.go:176
github.com/go-rel/migration.(*Migration).Migrate(0x14000032040, {0x1005b75d0, 0x1007b5620})
        /Users/rudistrydom/go/pkg/mod/github.com/go-rel/[email protected]/migration.go:127 +0x218
main.main()
        /var/folders/l1/hcfpr_dx78qg78s_h25wl16r0000gp/T/rel-136900769.go:82 +0x234

[Rudis1261]

I ended up just adding a column per each table that will have a vendor id. I think this is just the simplest approach which do not overcomplicate queries later on.