DT
Drizzle TeamSylvain

Using BIN_TO_UUID / UUID_TO_BIN

I’m trying to understand the best way to use BIN_TO_UUID and UUID_TO_BIN (MySQL). The below is working fine... 
export const users = mysqlTable('User', {
 uuid: binary('uuid', { length: 16 }).default(sql`(UUID_TO_BIN(UUID(), 1))`).primaryKey(),
 username: varchar('username', { length: 256 }).notNull(),
}

const select = await db
 .select({ uuid: sql<string>`BIN_TO_UUID(${users.uuid}, 1)` })
 .from(users)

const update = await db
 .update(users)
 .set({ username: 'foo' })
 .where(eq(users.uuid, sql<string>`UUID_TO_BIN(${myUserUuid}, 1)`))
export const users = mysqlTable('User', {
 uuid: binary('uuid', { length: 16 }).default(sql`(UUID_TO_BIN(UUID(), 1))`).primaryKey(),
 username: varchar('username', { length: 256 }).notNull(),
}

const select = await db
 .select({ uuid: sql<string>`BIN_TO_UUID(${users.uuid}, 1)` })
 .from(users)

const update = await db
 .update(users)
 .set({ username: 'foo' })
 .where(eq(users.uuid, sql<string>`UUID_TO_BIN(${myUserUuid}, 1)`))
...but I was wondering if there was a better approach, so that the UUID<>BIN conversion would automatically apply - without having to manually specify it on each query? Here is an example of what I’m looking for (fromSQL and toSQL are made up functions): 
const customBinary = customType<{
 data: string; driverData: string; config: { length?: number }
}>({
 dataType(config) {
  return typeof config?.length !== 'undefined' ? `binary(${config.length})` : `binary`
 },
 // automatically apply on select + return
 fromSQL(value: string): string {
  return `BIN_TO_UUID(${value}, 1)`
 },
 // automatically apply on insert/update + select filters
 toSQL(value: string): string {
  return `UUID_TO_BIN(${value}, 1)`
 }
});

export const users = mysqlTable('User', {
 uuid: customBinary('uuid', { length: 16 }).default(sql`(UUID_TO_BIN(UUID(), 1))`).primaryKey(),
 username: varchar('username', { length: 256 }).notNull(),
}
const customBinary = customType<{
 data: string; driverData: string; config: { length?: number }
}>({
 dataType(config) {
  return typeof config?.length !== 'undefined' ? `binary(${config.length})` : `binary`
 },
 // automatically apply on select + return
 fromSQL(value: string): string {
  return `BIN_TO_UUID(${value}, 1)`
 },
 // automatically apply on insert/update + select filters
 toSQL(value: string): string {
  return `UUID_TO_BIN(${value}, 1)`
 }
});

export const users = mysqlTable('User', {
 uuid: customBinary('uuid', { length: 16 }).default(sql`(UUID_TO_BIN(UUID(), 1))`).primaryKey(),
 username: varchar('username', { length: 256 }).notNull(),
}
Is there any way to do something similar right now?
S
Sylvain•360d ago
Nevermind... I found a better way to achieve what I was looking for by using custom types with the binary-uuid package (https://www.npmjs.com/package/binary-uuid): 
import { fromBinaryUUID } from 'binary-uuid'
import { sql, type SQL } from 'drizzle-orm';

export const binary = customType<{
    data: string; driverData: string; config: { length?: number }
}>({
    dataType(config) {
        return typeof config?.length !== 'undefined' ? `binary(${config.length})` : `binary`
    },
    fromDriver(value: string): string {
        const buff = value.startsWith('base64:type254:')
            ? Buffer.from(value.split(":")[2], "base64")
            : Buffer.from(value, 'binary')

        return fromBinaryUUID(buff)
    },
    toDriver(value: string): SQL<unknown> {
        return sql`UUID_TO_BIN(${value}, 1)`
    },
});
import { fromBinaryUUID } from 'binary-uuid'
import { sql, type SQL } from 'drizzle-orm';

export const binary = customType<{
    data: string; driverData: string; config: { length?: number }
}>({
    dataType(config) {
        return typeof config?.length !== 'undefined' ? `binary(${config.length})` : `binary`
    },
    fromDriver(value: string): string {
        const buff = value.startsWith('base64:type254:')
            ? Buffer.from(value.split(":")[2], "base64")
            : Buffer.from(value, 'binary')

        return fromBinaryUUID(buff)
    },
    toDriver(value: string): SQL<unknown> {
        return sql`UUID_TO_BIN(${value}, 1)`
    },
});
Want results from more Discord servers?
Add your server
More Posts
drizzle-kit drop config file does not existI have a `/foo/drizzle.config.ts` file as suggested in the docs (https://orm.drizzle.team/kit-docs/Related object is not typed correctlyI have the following schema ``` export const menus = pgTable('menus', { id: uuid('id').primaryKey(Custom vector type with pgvectorI'm trying to use the `pgvector` extension with a custom type but am running into an issue with the Missing 'with' clause additional operators (where, limit, offset, etc)Have been digging into the docs and the latest update. ❣️ In the docs (https://orm.drizzle.team/d