To have case sensitive searches—how to create a custom type for COLLATE utf8mb4_bin?

[205g0]

Edit: I think, the easiest option is to add a custom type .collate('utf8mb4_bin') to CREATE TABLE columns which makes that column case sensitive, so the question is how to create such a custom type? Anyone likes to help?

My first guess would be something like this (?):

import { customType } from 'drizzle-orm/mysql-core';
 
const csId = customType<{ data: string, notNull: true  }>({
  dataType() {
    return 'CHAR(20) PRIMARY KEY COLLATE utf8mb4_bin';
  },
});

Bonus question, how would this work when inspecting a db? Will COLLATE be recognized?

OG post:
In MySQL most used coalitions are case insensitive (e.g. PlanetScale) but for some use-case like a base64 id you need to have case sensitivity. You can achieve this with raw SQL:

SELECT *  FROM `table` WHERE `column` = BINARY 'value'

Important is not to put the BINARY before column, otherwise the index is not used, from https://stackoverflow.com/a/38578098

How can I achieve this with Drizzle?

[Senbonzakura1234]

There is ilike query

ilike(table.column, `%${value}%`)

[205g0]

I just tested it on MySQL with a utf8mb4_0900_ai_ci table. Believe me, it's really not case sensitive. 🙂

[Senbonzakura1234]

I just tested it on MySQL with a utf8mb4_0900_ai_ci table. Believe me, it's really not case sensitive. 🙂

Mine using postgres here thou

[205g0]

Ah ok, Postgres is different in this regards, still thanks!

[Senbonzakura1234]

Ah ok, Postgres is different in this regards, still thanks!

Oh, just read the docs again, ilike only support postgres

[Senbonzakura1234]

Ah ok, Postgres is different in this regards, still thanks!

Oh, just read the docs again, ilike only support postgres

What strange is notIlike is support all :v