UUID used as a primary key causes panic when loading

[korECM]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

v4.16.2

What is your database and version (eg. Postgresql 10)

MySQL 8.0

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE IF NOT EXISTS `events`
(
    `id`              BINARY(16)                         NOT NULL PRIMARY KEY,
    `name`            VARCHAR(255)                       NOT NULL,
    `insert_dt`       DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `update_dt`       DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
)
    ENGINE = InnoDB
    DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `schedules`
(
    `id`                  BINARY(16)                         NOT NULL PRIMARY KEY,
    `event_id`            BINARY(16)                         NOT NULL,
    `insert_dt`           DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    `update_dt`           DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT `schedules_event_id_fk` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
)
    ENGINE = InnoDB
    DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

Further information. What did you do, what did you expect?

I am using UUID as a PK, and the generated code is as follows:

type Event struct {
	ID             []byte    `boil:"id" json:"id" toml:"id" yaml:"id"`
	Name           string    `boil:"name" json:"name" toml:"name" yaml:"name"`
	InsertDT       time.Time `boil:"insert_dt" json:"insert_dt" toml:"insert_dt" yaml:"insert_dt"`
	UpdateDT       time.Time `boil:"update_dt" json:"update_dt" toml:"update_dt" yaml:"update_dt"`

	R *eventR `boil:"-" json:"-" toml:"-" yaml:"-"`
	L eventL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

As you can see in the code above, the ID column uses the []byte type.

func (eventL) LoadSchedules(ctx context.Context, e boil.ContextExecutor, singular bool, maybeEvent interface{}, mods queries.Applicator) error {
	var slice []*Event
	var object *Event

	// ...

	args := make(map[interface{}]struct{})
	if singular {
		if object.R == nil {
			object.R = &eventR{}
		}
		args[object.ID] = struct{}{}
	} else {
		for _, obj := range slice {
			if obj.R == nil {
				obj.R = &eventR{}
			}
			args[obj.ID] = struct{}{}
		}
	}

	if len(args) == 0 {
		return nil
	}

	argsSlice := make([]interface{}, len(args))
	i := 0
	for arg := range args {
		argsSlice[i] = arg
		i++
	}

	// ...
	return nil
}

However, this leads to a problem in the code as follows:

		args[object.ID] = struct{}{}

Here, the type of the ID value is []byte, but a slice in Golang is not hashable. Therefore, the following error occurs:

	panic: runtime error: hash of unhashable type []uint8

#1337

This issue is suspected to have arisen from the above PR. If you use version v4.15.0, this problem does not occur. It's necessary to solve the performance issue with Load, but it would be good if it could be used without any problems even when using UUID as a PK.

[stephenafamo]

Since you're using UUIDs, I would suggest doing a type replacement to use [16]byte instead. This should be usable as a map key.

[[types]]
  tables = ['events', 'schedules']
  [types.match]
    name = "id"
  [types.replace]
    type = "[16]byte"

Until better detection of the DB type is done to use arrays instead of slices, this would be the best solution.

[korECM]

Thanks, I was wondering if there was a way to do that, but there is!

[korECM]

@stephenafamo
Sadly... Using a replace like the one above will cause the following problems

create event: pdbmodel: unable to insert into events: sql: converting argument $1 type: unsupported type [16]uint8, a array

Is there anything else I can try?

[stephenafamo]

Perhaps you can try an actual UUID type?

[[types]]
  tables = ['events', 'schedules']
  [types.match]
    name = "id"
  [types.replace]
    type = "uuid.UUID"
  [types.imports]
    third_party = ['"github.com/google/uuid"']

[korECM]

I created a custom UUID type that implemented sql.Scanner, driver.Valuer and replaced it with this type and it worked! Thank you very much!

[stephenafamo]

created a custom UUID type that implemented sql.Scanner, driver.Valuer and replaced it with this type and it worked!

Glad it worked.

Both github.com/google/uuid and github.com/gofrs/uuid/v5 implement the interfaces, so it may be better to use one of them. It's likely to be better optimised, depending on your needs.

[xux1217]

Perhaps you can try an actual UUID type?

[[types]]
  tables = ['events', 'schedules']
  [types.match]
    name = "id"
  [types.replace]
    type = "uuid.UUID"
  [types.imports]
    third_party = ['"github.com/google/uuid"']

hi, I have many tables use UUID, and these are different field name, can you suggest some method to generate it?
I can't write it more than one hundred times, can we use field type? like BINARY(16) means UUID?

[xux1217]

Or is it possible for us to optimize the primary key of the binary field?
Many of our codes have encountered binary compatibility issues.
Can this be implemented at the library level?
Binary itself is also sortable.

[stephenafamo]

I have many tables use UUID, and these are different field name, can you suggest some method to generate it?

The best suggestion I have is for you to write a small script to generate the config.
While I understand how this is tedious, unfortunately, I can't think of something easier.