Example of JSON column

[elrayle]

I am trying to include a JSON column in my table. The JSON is provided by the caller and I have no idea in advance what the data is going to be. I think I am close, but can't seem to get it to work.

What I have so far...

Skeema definition

CREATE TABLE `user_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `provided_data` JSON NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SQL Boiler Generated struct (for reference)

type UserData struct {
    ID           uint64     `boil:"id" json:"id" toml:"id" yaml:"id"`
    Name         string     `boil:"name" json:"name" toml:"name" yaml:"name"`
    ProvidedData types.JSON `boil:"provided_data" json:"provided_data" toml:"provided_data" yaml:"provided_data"`

    R *userDataR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L userDataL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

My struct for holding the data

package data
type UserData struct {
    ID           uint64
    Name         string
    ProvidedData []byte
}

Function to convert from my struct to SQL Boiler struct

func toDBUserData(ud *data.UserData) *models.UserData {
    var providedData types.JSON
    _ = providedData.UnmarshalJSON(ud.ProvidedData)
    fmt.Println("providedData: ", providedData, " -- ", reflect.TypeOf(providedData)) // PRINTS: providedData:  {"some": "data"}  --  types.JSON

    return &models.UserData{
        ID:           ud.ID,
        Name:         ud.Name,
        ProvidedData: providedData,
    }
}

Function to insert

func (uds *UserDataStore) CreateUserData(ctx context.Context, ud *userData) (uint64, error) {
    ...
    dbUserData := toDBUserData(ud)
    if err := dbUserData.Insert(ctx, uds.db, boil.Infer()); err != nil {
        fmt.Println("err: ", err) // PRINTS: err:  models: unable to insert into user_data: Error 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
    }
    ...
}

Related suggestion for this error

I found this on stackoverflow which seems to apply (https://stackoverflow.com/questions/38078119/mysql-5-7-12-import-cannot-create-a-json-value-from-a-string-with-character-set).

INSERT INTO json_table (json_column) VALUES (CONVERT(X'7B22666F6F223A2022626172227D' using utf8mb4));

I changed the INSERT statement in the SQLBoiler generated table...

FROM:

cache.query = fmt.Sprintf("INSERT INTO `user_data` (`%s`) %%sVALUES (%s)%%s", strings.Join(wl, "`,`"), strmangle.Placeholders(dialect.UseIndexPlaceholders, len(wl), 1, 1))

which generates

INSERT INTO `user_data` (`name`,`provided_data`) %sVALUES (?,?)%s"

TO:

cache.query = "INSERT INTO `user_data` (`name`,`provided_data`) %sVALUES (?,CONVERT(? using utf8mb4))%s"

The insert succeeds with this hardcoded INSERT statement.

Is there another way to accomplish this? I, of course, do not want to commit edits to the generated code.

[stephenafamo]

IIRC, types.JSON is based on json.RawMessage, which is based on []byte. So you should simply be able to do

func toDBUserData(ud *data.UserData) *models.UserData {
    return &models.UserData{
        ID:           ud.ID,
        Name:         ud.Name,
        ProvidedData: types.JSON(ud.ProvidedData),
    }
}

However, instead of passing byte slices around, you can use the system I outlined in this other discussion.

[elrayle]

@stephenafamo Thanks for the quick response! I saw the discussion you link to before I posted. It didn't seem to apply to what I am seeing, but maybe I'm not following all the info provided in that post.

The critical part I'm seeing is the failure to insert and update getting error models: unable to insert into user_data: Error 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'..

Everything works if I make 2 changes to the generated model.

For insert...

// COMMENTED OUT generated code
// cache.query = fmt.Sprintf("INSERT INTO `user_data` (`%s`) %%sVALUES (%s)%%s", strings.Join(wl, "`,`"), strmangle.Placeholders(dialect.UseIndexPlaceholders, len(wl), 1, 1))
// TODO: Hardcoded query -- NEED OTHER SOLUTION -- SHOULD NOT UPDATE GENERATED CODE
cache.query = "INSERT INTO `user_data` (`name`,`provided_data`) %sVALUES (?,CONVERT(? using utf8mb4))%s"

For update...

// COMMENTED OUT generated code
// cache.query = fmt.Sprintf("UPDATE `user_data` SET %s WHERE %s",
//     strmangle.SetParamNames("`", "`", 0, wl),
//     strmangle.WhereClause("`", "`", 0, userDataPrimaryKeyColumns),
// )
// TODO: Hardcoded query -- NEED OTHER SOLUTION -- SHOULD NOT UPDATE GENERATED CODE
cache.query = "UPDATE `user_data` SET `provided_data`=CONVERT(? using utf8mb4) WHERE `id`=?"

[stephenafamo]

What driver are you using? I believe the values are sent as arguments to the query and it seems they are being sent by the driver as a string instead of bytes.

[elrayle]

MySQL installed using

go install github.com/volatiletech/sqlboiler/v4/drivers/sqlboiler-mysql@latest

[elrayle]

I printout what FormatDSN() is returning in case that is helpful...

dbCfg.FormatDSN():  root@tcp(127.0.0.1:5006)/data_service_test?checkConnLiveness=false&interpolateParams=true&parseTime=true&maxAllowedPacket=0&charset=utf8mb4

[stephenafamo]

So, reading through JSON releated issues in https://github.com/go-sql-driver/mysql, I found this issue which seems to be what you're facing. Matter of fact, there is an old SQLBoiler issue based on it.

At the moment, it seems using json.RawMessage instead of types.JSON should work as expected because of this merged PR. You can change the generated type through your SQLBoiler configuration and see if that would work.

[elrayle]

Thanks. I'll give that a try. I appreciate you looking into this.

[elrayle]

I'm not sure I understand what should be changed in the configuration based on...

You can change the generated type through your SQLBoiler configuration and see if that would work.

I'm not finding in the documentation how you can change the generated type. But I did find TypeReplaces in the config code.

[elrayle]

I found one of your responses showing an example of the [[types]] config.

[elrayle]

For reference for anyone following this thread, here's what I have now with the switch to json.RawMessage.

This change in implementation fixes the issue with insert/update raising errors for the JSON column. But it required changing the database column's type from JSON to varchar. This removes MySQL provided functionality for the JSON Data Type.

Skeema definition

NOTE: provided_data changed from type JSON to varchar. This may not be acceptable as it limits our ability to leverage the column using JSON Data Type support.

CREATE TABLE `user_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `provided_data` varchar(4096) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When the type was set to JSON, the following error occurs on insert, with a similar error for update. NOTE: This error occurred when all changes described in this modified implementation were made except updating the column type in the database.

models: unable to insert into user_data: Error 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

Configure remapping of the type

Modification: Add [[types]] section to sqlboiler.toml to explicitly set the type of the column.

[[types]]
  [types.match]
    tables = ['user_data']
    name = "provided_data"
    nullable = true

  [types.replace]
    type = "json.RawMessage"    # The name of the type to replace with

  [types.imports]
    third_party = ['json "encoding/json"']

SQL Boiler Generated struct (for reference)

Modification: ProvidedData's type changed from types.JSON to json.RawMessage

type UserData struct {
    ID           uint64     `boil:"id" json:"id" toml:"id" yaml:"id"`
    Name         string     `boil:"name" json:"name" toml:"name" yaml:"name"`
    ProvidedData json.RawMessage `boil:"provided_data" json:"provided_data" toml:"provided_data" yaml:"provided_data"`

    R *userDataR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L userDataL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

My struct for holding the data

Modification: ProvidedData's type changed from []byte to json.RawMessage

package data
type UserData struct {
    ID           uint64
    Name         string
    ProvidedData json.RawMessage // was []byte
}

Function to convert from my struct to SQL Boiler struct

Modification: Removed casting for ProvidedData since both structs now have the same datatype for this field.

func toDBUserData(ud *data.UserData) *models.UserData {
    return &models.UserData{
        ID:           ud.ID,
        Name:         ud.Name,
        ProvidedData: ud.providedData, // was []byte, but now is json.RawMessage for both structs
    }
}

[elrayle]

@stephenafamo Would you have expected the database column type to need to change to varchar? I'm wondering if I missed something in the implementation change to use json.RawMessage.

[stephenafamo]

I don't fully understand the question, but I don't see why the column should change to varchar. I assume you have a JSON column because you want semi-structured queryable data in there.

[elrayle]

If I leave the column as JSON type, insert and update get errors. The error for insert is...

models: unable to insert into user_data: Error 3144: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

We prefer to have the column as type JSON. This provides automatic validation of JSON documents, optimized storage, and the ability to query based on the JSON structure.