Retrieving ids from FusionAuth database tables

dan

For analytical purposes I want to run query directly against the Fusionauth DB (I am running this locally).

But the id fields are stored as binary blobs (probably because they are guids).

Do you have a function (java or sql) I can use to convert this blob into a string?

Duplicated and expanded from https://stackoverflow.com/questions/64253472/how-can-i-get-string-from-the-blob-an-id-stored-in-fusionauth-db

dan

Hiya,

We don't guarantee any database level compatibility (which is why we recommend doing everything through the API, which does have those guarantees).

But here's an option. I'm assuming you are using MySQL. FusionAuth uses UUIDs for unique Ids, and in MySQL we store these as BINARY(16).

If you want to select this value in a human readable form, you can perform a select such as SELECT HEX(id) FROM table_name.

If you want to select this column and deserialize it into a Java UUID type, you can use code similar to the following:

public UUID fromByteArray(byte[] ba) {
  long msb = 0;
  long lsb = 0;
  for (int i = 0; i < 8; i++) {
    msb = (msb << 8) | (ba[i] & 0xff);
  }
  for (int i = 8; i < 16; i++) {
    lsb = (lsb << 8) | (ba[i] & 0xff);
  }
  return new UUID(msb, lsb);
}

Another option, depending on your data size, would be to export all the data using the relevant APIs, and import it into your analytics database.