[FEATURE]: Support setting collation of columns

[tmcw]

trafficstars

Describe want to want

Another fun Postgres feature! Let's say you're using fractional indexing and want to sort strings in a particular way that has nothing to do with their ordering in English or any other language, but with how they'd compare by codepoints, like ASCII strings. You could use COLLATE "C" to do that.

This would be an addition to the text() type in the schema builder.

[danielsharvey]

Collation support would also be good for MySQL

[zgayjjf]

+1

[cmarker0]

Collation support would be great for UTF-8 support as well.

[delight]

For Postgres:

It would be nice to support the basic scenario: After (manually creating a collation)

   CREATE COLLATION case_insensitive (
      provider = icu,
      locale = 'und-u-ks-level2',
      deterministic = false
    );

Note: you can always lookup your collations by SELECT * FROM pg_collation;

it would be nice to have something that can use that collation maybe in a form something like:

db.select().from(person)
.where(eq(person.name, 'Jack').collate('case_insensitive'));

resulting in the sql:

SELECT * FROM person WHERE name = 'Jack' collate case_insensitive;

Is there maybe another way to achieve this already?

[205g0]

This is actually more important than I thought. I worked around this till now by using case-insensitive ids, e.g. no base64, but this is for ids I can control but what if I get ids from oauth providers I can't control? Would be great if this somehow gets in the backlog soon, thanks 🚀

Edit: Just to illustrate and have all in one thread, enabling case-sensitive queries with MySQL is something like id CHAR(20) PRIMARY KEY COLLATE utf8mb4_bin,

Edit2: related to https://github.com/drizzle-team/drizzle-orm/issues/735 which I'll close in favor of this one

[statusunknown418]

I guess this is related to LIKE which is something I (and a lot of people) also use a lot so upvoting here (although I'm not entirely sure what collations are lol)

[VergeDX]

+1

[aaroned]

this seems like a pretty basic feature... how can we get it on the roadmap?

[eslym]

the default collation is case insensitive, means if using something like nanoid as id, less characters could be use, higher collision chance with same length

[eslym]

found a work around, kinda working

import type { ColumnBaseConfig } from 'drizzle-orm';
import { mysqlTable, char, varchar, type MySqlColumn } from 'drizzle-orm/mysql-core';
import { nanoid } from 'nanoid';

function collate<
    T extends 'MySqlChar' | 'MySqlVarChar' | 'MySqlText',
    C extends MySqlColumn<ColumnBaseConfig<any, T>>
>(col: C, collation: string) {
    col.getSQLType = function (this: C) {
        return Object.getPrototypeOf(this).getSQLType.call(this) + ' COLLATE ' + collation;
    };
    return col;
}

export const User = mysqlTable('users', {
    id: char('id', { length: 10 })
        .$defaultFn(() => nanoid(10))
        .primaryKey(),
    email: varchar('email', { length: 255 }).unique().notNull()
});

collate(User.id, 'ascii_bin');

which generates follow sql using drizzle-kit generate:mysql

CREATE TABLE `users` (
	`id` char(10) COLLATE ascii_bin NOT NULL,
	`email` varchar(255) NOT NULL,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_email_unique` UNIQUE(`email`)
);

[CTOJoe]

We just create a custom type of citext for our sqlite databases. It replicates the text type functionality and appends COLLATE NOCASE as a workaround.

import { customType } from "drizzle-orm/sqlite-core";

export const citext = customType<{
  data: string;
  notNull: true;
  default: true;
  config: { length?: number };
}>({
  dataType(config) {
    return `text${config?.length ? `(${config.length})` : ""} COLLATE NOCASE`;
  },
});

[ingadi]

+1

[selemessaied]

+1 for MySQL too