Convert UUID to/from binary in Node

Question

The project I work on switched to MySQL. The keys we use are UUID strings (like 43d597d7-2323-325a-90fc-21fa5947b9f3), but the database field, rather than be a string, is defined as binary(16) - 16-byte unsigned binary.

I understand that a UUID is basically a 16-byte binary, but I have no idea how to convert from/to a binary number.

I'm using node-mysql to access the database, and I tried using node-uuid to parse the UUID, but that yields an array of integers. I also tried using Node's Buffer, but that just yields a buffer object.

How do I convert a UUID string to fit into that field? And how do I turn a value I read from that field into a UUID?

Answer 1

Due to lack of time, I'll paste the comment that provided valid result(s) and modify the answer later so it's clearer.

---

Right, if you have a UUID 43d597d7-2323-325a-90fc-21fa5947b9f3 in that string format already in your JS app, you'd send the following query to MySQL:

SELECT col FROM table WHERE uuid_col = UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''));

If you want to pull data out and have UUID in readable format, you have to convert it to hexadecimal notation.

SELECT HEX(uuid_col) FROM table;

That one will give you the UUID without dashes. It appears that the node-uuid.parse method works if you give it hex string without dashes.

Answer 2

While N.B.'s answer works I stumbled upon another solution.

UUID v1 starts with character segments that are time based; however, the smallest units come first making distribution rather scattered in an index.

If you aren't stuck on the precise UUID v1 format than there is a NodeJS module that can generate unique IDs based on UUID v1 that also monotonically increase and scale about as well as auto incremented IDs. It also works with node-mysql.

Checkout: monotonic-id

An example with node-mysql:

var MID = require('monotonic-id');

var mid = new MID();

client.query('INSERT INTO `...` SET `mid`=?', mid.toBuffer(), function(err, res) {
   ...
})