Add binary uuid option #1778

[OlivierCavadenti]

• Add useBinaryUuid option in 'uuid' function.
• If this option is 'true' use 'binary(16)' type (other than PG).
• Closes knex should use binary(16) for UUID outside of PG #1778

[mishase]

You should create function to transform uuid-s before inserting and after get. You can use something like uuid-parse package.
For example, currently i'm are using query transformer to automatically parse uuid in readable strings for mysql db like this:

function typeCast(field, next) {
	if (field.type === "STRING" && field.length === 16) {
		const buf = field.buffer();
		return buf ? uuidParse.unparse(buf) : null;
	} else {
		return next();
	}
}

this function makes easier to work with uuid-s in backend, but limits some functionality because it will trigger to any varchar(16) columns

[OlivierCavadenti]

Ok, I see the point.
Knex include already uuid package (https://www.npmjs.com/package/uuid#uuidparsestr) so I will check later what I can do.

[OlivierCavadenti]

You should create function to transform uuid-s before inserting and after get. You can use something like uuid-parse package. For example, currently i'm are using query transformer to automatically parse uuid in readable strings for mysql db like this:

function typeCast(field, next) {
	if (field.type === "STRING" && field.length === 16) {
		const buf = field.buffer();
		return buf ? uuidParse.unparse(buf) : null;
	} else {
		return next();
	}
}

this function makes easier to work with uuid-s in backend, but limits some functionality because it will trigger to any varchar(16) columns

As I can see it's not so easy to make that in Knex.
I want to make something generic, where the logic of parse/unparse is defined with column definition (in ColumnCompiler).
When you prepare Insert in querycompiler you dont have any information about the column type and you cant convert your uuid at this time.
Same problem in runner.js when you process the query.
If you have hints about how to get the type of column inside insert compiler and runner I am open @kibertoad.
If it's not possible, I can also make utils to manually convert uuid's before and after inserts.

[kibertoad]

@OlivierCavadenti Knex should never have any info about column it is inserting to. I would suggest adding util method to knex-utils instead.

[OlivierCavadenti]

@OlivierCavadenti Knex should never have any info about column it is inserting to. I would suggest adding util method to knex-utils instead.

One other option is to provide utils in knex (like knex.raw...) to convert in binary with database specific SQL (like BIN_TO_UUID in MYSQL: https://stackoverflow.com/a/45186851/1535159). Need more work btw.

[kibertoad]

yeah, that works too

[intech]

In cockroachdb, pg types are used and I have to fix the response with hexadecimal data, I temporarily did this:

const { types } = require("pg");
types.setTypeParser(17, value => Buffer.from(value.replace("\\x", ""), "hex"));

We need to think about a more beautiful solution.

For insertion the uuid format can be any hex, or formatted with a separator, or guid in curly braces, all of these formats are converted automatically to the specified column type, or text representation, or binary (hex)

[OlivierCavadenti]

In cockroachdb, pg types are used and I have to fix the response with hexadecimal data, I temporarily did this:

const { types } = require("pg");
types.setTypeParser(17, value => Buffer.from(value.replace("\\x", ""), "hex"));

We need to think about a more beautiful solution.

For insertion the uuid format can be any hex, or formatted with a separator, or guid in curly braces, all of these formats are converted automatically to the specified column type, or text representation, or binary (hex)

Thanks for comments. I need to finish another task before this PR, but as I can see, a lot of people have workaround and wait this. I will find a clean solution to make the use as smooth as possible :D.

[kibertoad]

@OlivierCavadenti BTW, can you add yourself to the list of maintainers in package.json?

[OlivierCavadenti]

I choose to add two functions in FunctionHelper.js to avoid dependencies and SQL db specific generation.

To use :

Create table with option actived on uuid column:

await knex.schema.createTable('uuid_table', (t) => {
  t.uuid('uuid_col_binary', { useBinaryUuid: true });
});

Insert your uuid in binary format with uuidToBin function:

await knex('uuid_table').insert({
  uuid_col_binary:  knex.fn.uuidToBin('3f06af63-a93c-11e4-9797-00505690773f'),
});

Retreive it and convert it:

const uuid = await knex('uuid_table').select('uuid_col_binary');
knex.fn.binToUuid(uuid[0].uuid_col_binary)

[OlivierCavadenti]

@mishase @intech the solution is good for you (see comment above) ?

Add uuid functions