Convert ID: buffer to UUID in Node.js 18.17.0 and MariaDB 10.4

Question

I am trying to retrieve from a DB a set of movies, whose IDs are in UUID format.

I am using Node.js 18.17.0 and MariaDB 10.4 (latest XAMPP version).

I did the following:

CREATE TABLE movie(
  id BINARY(36) PRIMARY KEY DEFAULT UUID(),
  title VARCHAR(255) NOT NULL,
  year INT NOT NULL,
  director VARCHAR(255) NOT NULL,
  duration INT NOT NULL,
  poster TEXT,
  rate DECIMAL(2, 1) UNSIGNED NOT NULL
);

import mariadb from 'mariadb'.

const config = {
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: '',
  database: 'moviesdb'
}

const connection = await mariadb.createConnection(config)

export class MovieModel{
  static getAll = async ({genre}) => {
    const result = await connection.query('SELECT id FROM movie')
    console.log(result)
  }
}

This shows me:

[
  {
    id: <Buffer 63 34 30 30 37 37 37 33 33 31 30 2d 62 31 34 34 66 2d 31 31 65 65 2d 39 64 31 36 2d 34 30 31 36 37 65 61 65 63 66 37 64>
  },
  ...
]

When I am actually expecting it to show me:

[
  {
    id: c4077310-b14f-11ee-9d16-40167eaecf7d
  },
  ...
]

Is there a simple way to solve this?

Answer 1

Not a problem. Binary and Varchar are [somewhat] compatible. UUIDs have nothing but ascii characters.

This displays the BINARY column in Hex:

id: <Buffer 63 34 30 30 37 37 37...

As you say, you are expecting characters:

id: c40777...

This will put characters from calling UUID() into a BINARY column.

id BINARY(36) PRIMARY KEY DEFAULT UUID()

You could just as well put into a character column:

id CHAR(36) CHARACTER SET ascii PRIMARY KEY DEFAULT UUID()

in which case, you would see "c50777..." instead of the hex equivalent.

In either case, your program should work the same.

Here's my discussion of UUIDs and what can be done to improve performance if you have millions of rows with UUIDs