DT
Join ServerDrizzle Team
help
Using BIN_TO_UUID / UUID_TO_BIN
I’m trying to understand the best way to use
The below is working fine...
...but I was wondering if there was a better approach, so that the
Here is an example of what I’m looking for (
Is there any way to do something similar right now?
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)`))
...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(),
}
Is there any way to do something similar right now?
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 customBinary = customType<{
data: string; driverData: string; config: { length?: number }
}>({
dataType(config) {
return typeof config?.length !== 'undefined' ? `binary(${config.length})` : `binary`
},
fromDriver(value: string): string {
return fromBinaryUUID(Buffer.from(value, 'binary'))
},
toDriver(value: string): SQL<unknown> {
return sql`UUID_TO_BIN(${value}, 1)`
},
});