Drizzle Team•17mo ago

Binary typing for Mysql & typescript

hey hopefully quick question, I have a mysql column of type binary(32), but the type coertion seems to only be accepting strings

// type definition
signingKey: binary('signing_key', { length: 32 })

// in use, typescript gives an error:
tx.insert(auth).values({signingKey:signKeyBytes})

// type definition
signingKey: binary('signing_key', { length: 32 })

// in use, typescript gives an error:
tx.insert(auth).values({signingKey:signKeyBytes})

It won't accept signKeyBytes as Uint8Array which is what I'd expect. I suppose I could just use a longer string column but I'd prefer to use the binary as that's how I need it on the server end and I'd like to avoid the base64 encode/decode step each time. This is what the type error says is the accepted type for signingKey: string | SQL<unknown> | Placeholder<string, any> | null | undefined. Any idea how I could do this cleanly?

3 Replies

rmtmOP•17mo ago

I've also tried just pushing the binary as-is into a string

const signKeyMessyString = String.fromCharCode(...signKeyBytes);

const signKeyMessyString = String.fromCharCode(...signKeyBytes);

but it's then complaining about the length (even though signKeyMessyString.length = 32).

Andrii Sherman•17mo ago

Could you please create a GH issue for that. Would help us to track it properly

rmtmOP•17mo ago

Okay so I finally figured this out, turns out the string type is correct as MYSQL accepts hex strings as input to a binary row. This might not be the most efficient way of handling this but it does seem to work

tx.insert(auth).values({signingKey: sql`UNHEX(${signKeyBytesString})`})

await db.select({
  signingKey: sql<string>`HEX(${auth.signingKey})`,
}).from(auth).limit(1).where(...);

tx.insert(auth).values({signingKey: sql`UNHEX(${signKeyBytesString})`})

await db.select({
  signingKey: sql<string>`HEX(${auth.signingKey})`,
}).from(auth).limit(1).where(...);

Gaming

Programming

Binary typing for Mysql & typescript