knex should use binary(16) for UUID outside of PG

[ItalyPaleAle]

Currently, on databases other than PostgreSQL, knex is creating UUID tables as char(36), wasting a lot of space (and making it inconvenient to use those columns as primary keys).

knex should store UUIDs as binary(16) instead, as each UUID is a 128bit (=16 byte) number and it's only represented with 36 hex characters for convenience.

[PlasmaPower]

I've stumbled upon this issue while researching a slightly related topic: do binary fields even work in knex? If using the postgres backend, it would appear they are at least somewhat broken: brianc/node-postgres#738.

Are binary fields ready for prod use, or should I stick to hex strings for now?

[PlasmaPower]

We should have tests for using binary fields (currently we just have tests for creating them it seems). I would expect them to work fully with node Buffers across SQL backends.

[ItalyPaleAle]

I've had success with passing Buffer objects for binary data (with MySQL), but I'm still in the early test stages.

[PlasmaPower]

Interesting. That might be a good reason for me to use the MySQL backend, but I'd prefer to not be locked into it...

[isocroft]

I can work on this and send a PR if it's okay... i also need this too ( binary(16) ) columns

[kibertoad]

Makes sense for me. @elhigu @lorefnon Thoughts?
This would be a breaking change, though.

[kibertoad]

One possible workaround to make it non-breaking would be to introduce new column type binaryUuid (that would work exactly same as UUID for postgres) so that it's an opt-in functionality.

[elhigu]

This needs to be done backwards compatible way with new opts parameter.

Generally there should not be made breaking changes to migration APIs since they are super dangerous and nasty to migrate.

[isocroft]

Ok... so i go ahead with the non-breaking binaryUuid option as @kibertoad suggests OR do i create an additional param on existing uuid() table object method as @elhigu suggests ?

Folks could still do this :

specificType('id', 'binary(16)')

however, don't want to introduce some irrelevance to knex just because i wish to support MySQL binary(16)

[kibertoad]

@isocroft If I understand @elhigu idea correctly, he is suggesting to extend table.uuid(name) to be table.uuid(name, opts), where opts would have something like 'useBinaryUuid' parameter that is defaulted to false unless on postgres.

[isocroft]

Yes, i do agree with @elhigu on this approach it is safe and will be backward compatible above all.

So, i will send PR as soon as i get a chance over the weekend... Cheers!

[edgarbjorntvedt]

What is the status on this?

[grefrit]

Any updates or workarounds?? We use binary uuids in our project too with directus and we need to patch knex through yarn patch to fix this issue. Maybe some thoughts how to make binary uuids work without some scary patches

[OlivierCavadenti]

Any updates or workarounds?? We use binary uuids in our project too with directus and we need to patch knex through yarn patch to fix this issue. Maybe some thoughts how to make binary uuids work without some scary patches

I will push something quickly.

[OlivierCavadenti]

@grefrit I push, you can check at #4836 if all is ok for you.

[grefrit]

@OlivierCavadenti Awesome, thank you a lot. But I didn't understand in your PR, how UUID selects or updates. It should convert to normal UUID when you SELECT something, and when u UPDATE it should to convert from normal UUID to binary(16), because you won't use binary(16) on website for example. Or I misunderstood something :)?

[OlivierCavadenti]

@OlivierCavadenti Awesome, thank you a lot. But I didn't understand in your PR, how UUID selects or updates. It should convert to normal UUID when you SELECT something, and when u UPDATE it should to convert from normal UUID to binary(16), because you won't use binary(16) on website for example. Or I misunderstood something :)?

Ok and I see the other same comment in my PR. I will check what you talking about :D.

[kibertoad]

Released in 1.0.1.