You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
CREATETABLEusers (
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_TIMESTAMPONUPDATECURRENT_TIMESTAMP
);
SQL queries
SELECT*FROM users WHERE id = ?; -- will not find the user with the right idINSERT 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_UUID
when selecting andUUID_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 withBIN_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[]
(orsql.NullString
if it is nullable), which then requires manual conversions from and to[]bytes
fromuuid.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 touuid.UUID
properly), but does not work for inputting into db (filter, insert / update). Using this approach generates code that acceptsuuid.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, butUUID_TO_BIN
seems to miss the type to whichuuid.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
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
The text was updated successfully, but these errors were encountered: