When storing UUIDs in mysql, the underlying type (based on the community suggestions) is BINARY(16), and the way of working with it through strings is to wrap it with BIN_TO_UUID when selecting and UUID_TO_BIN when inputting (as a filter or insert / update value).
Having this approach works out properly with sqlc, the only issue is more cumbersome queries (loaded with BIN_TO_UUID / UUID_TO_BIN) and strange attribute names in generated structures (UUIDTOBIN, UUIDTOBIN1 etc.).
If those helper functions are omitted, sqlc will automatically convert types to byte[] (or sql.NullString if it is nullable), which then requires manual conversions from and to []bytes from uuid.UUID directly in the code (making it more obfuscated).
Solution found on sqlc docs is to use type overrides in config file, where type for appropriate fields should be auto converted to uuid.UUID, which works good for selecting from db (automatically transfers types to uuid.UUID properly), but does not work for inputting into db (filter, insert / update). Using this approach generates code that accepts uuid.UUID and passes it to the query preparation, but the underlying query fails (either not matching filter or simply failing on column constraints - Error 1406 (22001): Data too long for column 'id' at row 1).
Basically my understanding is that BIN_TO_UUID part works correctly, but UUID_TO_BIN seems to miss the type to which uuid.UUID should be converted, and most likely just converts it to the string by default (not checking the underlying type on the schema) - this is just my assumption.
Any suggestion would be helpful, since at least for now the only solution I see is to either revert queries back to have UUID_TO_BIN or to remove type mapping and do it myself (neither seem like a optimal solution).
Relevant log output
No response
Database schema
CREATE TABLE users (
id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
email VARCHAR(254) NOT NULL,
password BINARY(60) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
SQL queries
SELECT * FROM users WHERE id = ?; -- will not find the user with the right id
INSERT INTO users (id, email, password) VALUES (?, ?, ?); -- this will fail with "Error 1406 (22001): Data too long for column 'id' at row 1"
Version
1.26.0
What happened?
When storing UUIDs in mysql, the underlying type (based on the community suggestions) is BINARY(16), and the way of working with it through strings is to wrap it with
BIN_TO_UUIDwhen selecting andUUID_TO_BINwhen inputting (as a filter or insert / update value).Having this approach works out properly with
sqlc, the only issue is more cumbersome queries (loaded withBIN_TO_UUID/UUID_TO_BIN) and strange attribute names in generated structures (UUIDTOBIN,UUIDTOBIN1etc.).If those helper functions are omitted, sqlc will automatically convert types to
byte[](orsql.NullStringif it is nullable), which then requires manual conversions from and to[]bytesfromuuid.UUIDdirectly in the code (making it more obfuscated).Solution found on sqlc docs is to use type overrides in config file, where type for appropriate fields should be auto converted to
uuid.UUID, which works good for selecting from db (automatically transfers types touuid.UUIDproperly), but does not work for inputting into db (filter, insert / update). Using this approach generates code that acceptsuuid.UUIDand passes it to the query preparation, but the underlying query fails (either not matching filter or simply failing on column constraints -Error 1406 (22001): Data too long for column 'id' at row 1).Basically my understanding is that
BIN_TO_UUIDpart works correctly, butUUID_TO_BINseems to miss the type to whichuuid.UUIDshould be converted, and most likely just converts it to the string by default (not checking the underlying type on the schema) - this is just my assumption.Any suggestion would be helpful, since at least for now the only solution I see is to either revert queries back to have
UUID_TO_BINor to remove type mapping and do it myself (neither seem like a optimal solution).Relevant log output
No response
Database schema
SQL queries
Configuration
Playground URL
No response
What operating system are you using?
Linux
What database engines are you using?
MySQL
What type of code are you generating?
Go