Javascript support in MySQL: the UUID example

You may have noticed that MySQL now supports creating functions (and store procedures) in Javascript using GraalVM.

This new functionality is only available in MySQL Enterprise and MySQL HeatWave.

As a developer, you can also get free access to MySQL Enterprise from Oracle Technology Network (OTN): MySQL Enterprise Download.

I also recommend you look at Øystein’s presentation during the Belgian MySQL Days: [1], [2].

Why use a JS function?

As you may know, UUIDs are increasingly popular and their usage in MySQL is limited to the UUID V1.

I already covered this in the blog post titled MySQL & UUIDs and in the following presentation.

So we know that it’s possible to extract the timestamp from the UUIDv1. I’ve created a MySQL Component (C++) that provides 3 UDFs (User Defined Functions) for that purpose. The component is available on GitHub: mysql-component-uuid_v1.When using MySQL DBaaS, it’s not possible to install such components, and it’s the same with MySQL HeatWave. Therefore, the capacity to employ JavaScript functions becomes particularly advantageous in this scenario.Reminder: if you use the native UUIDs in MySQL as Primary Key, you need to specify them like this:
uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY

So we know that it’s possible to extract the timestamp from the UUIDv1. I’ve created a MySQL Component (C++) that provides 3 UDFs (User Defined Functions) for that purpose. The component is available on GitHub: mysql-component-uuid_v1.

When using MySQL DBaaS, it’s not possible to install such components, and it’s the same with MySQL HeatWave. Therefore, the capacity to employ JavaScript functions becomes particularly advantageous in this scenario.

Functions to handle UUIDv1

We will then create functions to parse the MySQL UUID and display the timestamp.

Let’s start with extracting the time stamp as Unix time in milliseconds:

USE test;
DROP function IF EXISTS js_uuid_to_unixtime;
CREATE FUNCTION js_uuid_to_unixtime (uuid_in CHAR(36))
RETURNS CHAR(23) LANGUAGE JAVASCRIPT AS $$
    
const UUID_T_LENGTH = 16;
const UNIX_TS_LENGTH = 6;

function uuidToUnixTs(uuid_str) {
    const MS_FROM_100NS_FACTOR = 10000;
    const OFFSET_FROM_15_10_1582_TO_EPOCH = 122192928000000000n;

    // Split UUID with '-' as delimiter
    let uuid_parts = uuid_str.split('-');

    // Construct UUID timestamp from its parts
    let uuid_timestamp =
        uuid_parts[2].substring(1) +
        uuid_parts[1] +
        uuid_parts[0];

    // Parse hexadecimal timestamp string to integer
    let timestamp = BigInt('0x' + uuid_timestamp);

    // Calculate Unix timestamp in milliseconds
    let unixTimestampMs = Number((timestamp - OFFSET_FROM_15_10_1582_TO_EPOCH) / BigInt(MS_FROM_100NS_FACTOR));

    return unixTimestampMs;
}

function stringToUuid(str) {
    if (str.length !== 36) {
        return 1;
    }
    if (str[14] !== '1') {
        return 1;
    }
    return 0;
}

let result = stringToUuid(uuid_in);
let timestamp_out;
if (result === 0) {
    timestamp_out = uuidToUnixTs(uuid_in)/1000;
} else {
    timestamp_out="Error parsing UUID";
}

return (timestamp_out);

$$    

; 

Let’s test it:

MySQL > select now(), from_unixtime(js_uuid_to_unixtime(uuid()));
+---------------------+--------------------------------------------+
| now()               | from_unixtime(js_uuid_to_unixtime(uuid())) |
+---------------------+--------------------------------------------+
| 2024-03-20 22:24:13 | 2024-03-20 22:24:13.503000                 |
+---------------------+--------------------------------------------+
1 row in set (0.0009 sec)

I’ve created other Javascript functions offering a different output:

• js_uuid_to_datetime()
• js_uuid_to_datetime_long()

Let’s see them in action:

MySQL > select js_uuid_to_datetime(uuid());
+-----------------------------+
| js_uuid_to_datetime(uuid()) |
+-----------------------------+
| 2024-03-20 22:31:20.824     |
+-----------------------------+
1 row in set (0.0009 sec)

MySQL > select js_uuid_to_datetime_long(uuid());
+----------------------------------------------+
| js_uuid_to_datetime_long(uuid())             |
+----------------------------------------------+
| Wednesday, March 20, 2024 at 10:31:30 PM GMT |
+----------------------------------------------+
1 row in set (0.0012 sec)

UUIDv7

More and more people are using UUIDv7. Unfortunately, those are not available in MySQL. UUIDv7 are sequential and they are not using the Mac Address.

We can also create Javascript functions to generate and handle UUIDv7 in MySQL HeatWave.

The functions are available here.

Let’s test UUIDv7 as the Primary Key for a table:

MySQL > CREATE TABLE `item` (
  `id` varbinary(16) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

MySQL > insert into item values (uuid_to_bin(js_uuidv7()),'item01'),
                                (uuid_to_bin(js_uuidv7()),'item02'), 
                                (uuid_to_bin(js_uuidv7()),'item03');
Query OK, 3 rows affected (0.0060 sec)

MySQL > select * from item;
+------------------------------------+--------+
| id                                 | name   |
+------------------------------------+--------+
| 0x018E5E04BA3376AED34A5B5EB51720A5 | item01 |
| 0x018E5E04BA34732799E01BED0C1A06F9 | item03 |
| 0x018E5E04BA34780F6A683BA37F00CA27 | item02 |
+------------------------------------+--------+
3 rows in set (0.0007 sec)

MySQL > select bin_to_uuid(id) uuid, name from item;
+--------------------------------------+--------+
| uuid                                 | name   |
+--------------------------------------+--------+
| 018e5e04-ba33-76ae-d34a-5b5eb51720a5 | item01 |
| 018e5e04-ba34-7327-99e0-1bed0c1a06f9 | item03 |
| 018e5e04-ba34-780f-6a68-3ba37f00ca27 | item02 |
+--------------------------------------+--------+
3 rows in set (0.0008 sec)

We can also retrieve the creation time of each record:

MySQL > insert into item values (uuid_to_bin(js_uuidv7()),'item04');
Query OK, 1 row affected (0.0035 sec)

MySQL > select js_uuidv7_to_datetime(bin_to_uuid(id)) insert_date, 
        name from item;
+-------------------------+--------+
| insert_date             | name   |
+-------------------------+--------+
| 2024-03-20 22:39:11.923 | item01 |
| 2024-03-20 22:39:11.924 | item03 |
| 2024-03-20 22:39:11.924 | item02 |
| 2024-03-20 22:41:54.567 | item04 |
+-------------------------+--------+
4 rows in set (0.0041 sec)

It’s better for performance to store the UUIDs using uuid_to_bin() as BINARY(16). Certainly, if we plan to have secondary indexes too.

But maybe you prefer to directly see the UUID’s representation (value) when querying the table.

We can then modify our table like this:

MySQL > alter table item add column uuid char(36) 
        generated always as (bin_to_uuid(id)) virtual after id,
        alter column id set invisible;

MySQL > insert into item (id, name) 
        values (uuid_to_bin(js_uuidv7()),'item05');

MySQL > select * from item;
+--------------------------------------+--------+
| uuid                                 | name   |
+--------------------------------------+--------+
| 018e5e04-ba33-76ae-d34a-5b5eb51720a5 | item01 |
| 018e5e04-ba34-7327-99e0-1bed0c1a06f9 | item03 |
| 018e5e04-ba34-780f-6a68-3ba37f00ca27 | item02 |
| 018e5e07-3587-73af-6535-f805bee0cc04 | item04 |
| 018e5e15-12bf-7590-a010-7a5b5457a6c3 | item05 |
+--------------------------------------+--------+
5 rows in set (0.0006 sec)

MySQL > select *, js_uuidv7_to_datetime(uuid) inserted_at from item;
+--------------------------------------+--------+-------------------------+
| uuid                                 | name   | inserted_at             |
+--------------------------------------+--------+-------------------------+
| 018e5e04-ba33-76ae-d34a-5b5eb51720a5 | item01 | 2024-03-20 22:39:11.923 |
| 018e5e04-ba34-7327-99e0-1bed0c1a06f9 | item03 | 2024-03-20 22:39:11.924 |
| 018e5e04-ba34-780f-6a68-3ba37f00ca27 | item02 | 2024-03-20 22:39:11.924 |
| 018e5e07-3587-73af-6535-f805bee0cc04 | item04 | 2024-03-20 22:41:54.567 |
| 018e5e15-12bf-7590-a010-7a5b5457a6c3 | item05 | 2024-03-20 22:57:03.167 |
+--------------------------------------+--------+-------------------------+
5 rows in set (0.0014 sec)

Conclusion

Having the possibility to write Javascript programs directly in MySQL is a nice addition, especially when running MySQL HeatWave in the cloud where extra components or plugins are not possible to install.

For those possessing such User-Defined Functions (UDFs), you can now transcribe them into Javascript and use them where MySQL supports GraalVm: MySQL Enterprise and MySQL Heatwave.

The examples are available on GitHub: mysql-graalvm-examples.

Enjoy writing Javascript functions in MySQL!