How binary/UUID columns can be mapped?

Question

The column is defined as binary(16) in the database and the values are UUID.

The database is MySql.

Answer 1

You can select binary field as string by formatting it using HEX function. Typical Cube.js schema in this case will look like:

cube(`Users`, {
  // ...

  dimensions: {
    companyId: {
      sql: `HEX(${CUBE}.company_uuid)`,
      type: `string`
    }
  }
});

Filter for Users.companyId field in this case will look like

{
  // ...,
  filters: [{
    dimension: "Users.companyId",
    operator: "equals",
    values: ["0123456789ABCDED0123456789ABCDEF"]
  }]
}

If you have an index on this binary column you probably want to hint MySQL query planner with FILTER_PARAMS so it can be used:

cube(`Users`, {
  sql: `SELECT * FROM users WHERE ${FILTER_PARAMS.Users.companyId.filter(
    v => `company_uuid = UNHEX(${v})`
  )}`,
  // ...

  dimensions: {
    companyId: {
      sql: `HEX(${CUBE}.company_uuid)`,
      type: `string`
    }
  }
});

Learn more on FILTER_PARAMS here: https://cube.dev/docs/cube#context-variables-filter-params.