drizzle-orm: [BUG]: Support MySQL UNSIGNED

Any news on this? This is a major issue making drizzle almost unusable and there’s no real fix:

/ccing the team to get awareness, would be happy if you guys could focus on this before allocating resources to new features/tools, e.g. Studio, thanks for your understanding @dankochetov @AndriiSherman @AlexBlokh

• using serial is not possible because FKs can’t match the exact data type because of lacking unsigned (mysql), check https://github.com/drizzle-team/drizzle-orm/issues/258#issuecomment-1611022520
• custom types are no help either like the solution in https://github.com/drizzle-team/drizzle-orm/issues/258#issuecomment-1586393450 because custom types are broken, see https://github.com/drizzle-team/drizzle-orm/issues/765
• using compact and case-sensitive uuids instead of int ids is also not possible because of https://github.com/drizzle-team/drizzle-orm/issues/1141 and https://github.com/drizzle-team/drizzle-orm/issues/735

the only work-around is to not use drizzle’s ts declaration but rather other tools like Atlas and declare in SQL and let drizzle only introspect which brings you quite far at the cost of a so-so DX

it’s really sad since drizzle is the only lib with a sane architecture and approach

looking fwd to your feedback 🙂

@HaNdTriX I don’t know if it helps, but I’m currently creating a custom type as a workaround:

import { customType, mysqlTable, serial } from "drizzle-orm/mysql-core";

const unsignedBigint = customType<{ data: number }>({
    dataType() {
        return "bigint UNSIGNED";
    }
});

export const users = mysqlTable("users", {
    id: serial("id"),
});

export const pages = mysqlTable("pages", {
    id: serial("id"),
    authorId: unsignedBigint("author_id")
        .notNull()
        .references(() => users.id, { onUpdate: "cascade", onDelete: "cascade" }),
});

If you want, you can also create a custom id column type for any type of integer like this:

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

type IdType = "tinyint" | "smallint" | "mediumint" | "int" | "bigint";
interface UIntConfig {
    type?: IdType;
}

export const unsignedIntAutoIncrement = customType<{ data: number; config: UIntConfig; primaryKey: true; default: true }>({
    dataType: (config) => {
        return `${config?.type ?? "int"} UNSIGNED AUTO_INCREMENT`;
    }
});


export function id(dbName: string, config?: UIntConfig) {
    return unsignedIntAutoIncrement(dbName, config).primaryKey()
};

@HaNdTriX thanks so much!

We had same problem with unsigned on db introspect command. Drizzle was generating simple int(for example) and used in a code simple int(without unsigned option). It was causing to generating new migration with changing column type. Also good suggestion on a syntax. We were thinking to place it same way

Good thing - it’s on a list to be included in one of next releases, so will update under this ticket as well

This appears to be coming soon! https://github.com/drizzle-team/drizzle-orm/pull/1271

Would love to migrate to drizzle from prisma but definitely need unsigned ints

Bumping this issues as well. I’ve commented also on other threads but this seems to be the right one to track unsigned support.

It’s also important for MySQL’s clustered index means it’s very common to use unsigned bigint as primary key and not some uuid. With a unsigned bigint you’ve an infinite primary key, with signed it’s good too but yeah… why make compromises here and it should be an easy fix or no?

@AndriiSherman any update on the progress of this bug. Thanks

There’s even a bigger problem: if you use SERIAL in mysql, mysql creates an UNSIGNED column (next to BIGINT, UNIQUE and others). Now, when you want to define foregin keys you must match the exact type of SERIAL which is UNSIGNED but your don’t want to have SERIAL auto increment feature or unique because not every foregin key fields need it. But you can’t uses UNSIGNED because Drizzle has no API call for this. tldr you can’t use FK properly

Thank you for you fast response!

One addition regarding my proposal:

UNSIGNED is applicable for every number type in MySQL so it makes sense to make apply this setting to all number types.

@thisisandreww one issue we face with your workaround is that every time we run drizzle-kit push:mysql, it always alter the table:

ALTER TABLE `example` MODIFY COLUMN `another_id` bigint UNSIGNED NOT NULL;

It’s an issue when the table has data, because drizzle truncates the table even if the column is not really changing.

Any updates?

Can this be closed as complete in v0.29?

I would also like to bump this issue (w/ #670). @AndriiSherman, while this gets sorted out maybe @mymatsubara’s type could be added as an example in the custom types docs, with a note in the MySQL column types docs.