Json column insert into mysql db using GORM

I got this code to work, tested with Go 1.24.1 and Gorm 1.25.11 and MySQL 8.4.4 on MacOS.

package main

import (
        "gorm.io/driver/mysql"
        "gorm.io/gorm"
        "fmt"
)

type UserInfo struct {
        Name string
        Age int
}

type Profile struct {
        ID string
        User UserInfo `gorm:"type:bytes;serializer:json"`
        UserType string
}

// necessary because Gorm defaults to plural table names
func (Profile) TableName() string {
        return "profile"
}

func main() {
        // refer https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
        dsn := "root:@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"
        db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
        if err != nil {
                err.Error()
        }

        var profile Profile = Profile{
                ID: "Demo~1",
                UserType: "Permanent",
                User: UserInfo {
                        Name: "John",
                        Age: 24,
                },
        }

        if err:= db.Create(&profile).Error; err != nil {
                fmt.Errorf("Unsuccessful insert", err)
        } else {
                fmt.Printf("OK")
        }
}

I built and ran this test code, and it inserted the data into a row of the table, successfully formatting the sub-struct as JSON.

mysql> select * from profile;
+--------+-----------------------------+-----------+
| id     | user                        | user_type |
+--------+-----------------------------+-----------+
| Demo~1 | {"Age": 24, "Name": "John"} | Permanent |
+--------+-----------------------------+-----------+

The gorm serializer attribute is needed in the struct definition, to let Gorm know that you want the sub-struct to be marshalled into a string of bytes in JSON format. Otherwise you'd have to do that yourself with json.Marshal.

I didn't specify the JSON names for the struct members. I guess Gorm infers the JSON keys from their Go struct names by default.

Read more about Gorm serializers here: https://gorm.io/docs/serializer.html

You need to add the serializer tag to specify the serializer that handles how data is serialized and deserialized to and from the database, such as: serializer:json/gob/unixtime. Since your field is of type JSON, your User field should be defined as:

User *UserInfo `json:"user" gorm:"serializer:json"`

Otherwise, you need to manually call json.Marshal before inserting it into the database.

var profile Profile
Profile.ID = "Demo~1"
Profile.UserType = "Permanent"

user, _ := json.Marshal(&User {
  Name: "John",
  Age: 24,
})
Profile.User = string(user)

if err:= db.Create(&profile).Error; err != nil {
  fmt.Errorf("Unsuccessful insert")
}