Support for unsigned integers

Does Drizzle ORM support defining unsigned integers in the schema?

10 Replies

not yet but it will!

J.•2y ago

Curious about this as well, is there currently a workaround using the sql operator?

Rhys•16mo ago

also interested in being able to use an unsigned integer with drizzle and if there's any workarounds existing atm - thanks!

Rhys•16mo ago

Found a workaround from: https://www.answeroverflow.com/m/1141819615467556929 https://github.com/drizzle-team/drizzle-orm/issues/818

const unsignedInt = customType<{
    data: number;
    // I also tried this ↓ https://orm.drizzle.team/docs/custom-types#examples
    // notNull: true;
    // default: true;
}>({
    dataType() {
        return 'int unsigned';
    },
});

const unsignedInt = customType<{
    data: number;
    // I also tried this ↓ https://orm.drizzle.team/docs/custom-types#examples
    // notNull: true;
    // default: true;
}>({
    dataType() {
        return 'int unsigned';
    },
});

Answer Overflow

Custom column type with default not working? - Drizzle Team

I tried to add a new column with a custom type and a default value. but for some reason when I tried to db push, it warned me that it didn't have a default va...

GitHub

[BUG]: Column with custom type not working with default() · Issue...

What version of drizzle-orm are you using? v0.27.0 What version of drizzle-kit are you using? v0.19.2 Describe the Bug With the following schema: import { customType, mysqlTable, serial, varchar } ...

Andrii Sherman•16mo ago

just curious in what cases do you need unsigned vs just simple int?

Rhys•16mo ago

at the moment im using unsigned for the bitfield column although that doesn't actually make a difference since it's the same number of decimal places 😅 unsigned would also make sense for storing discord snowflakes as unsigned bigints since there's no reason to store them as signed, unless there's a risk of the value overflowing with JS not expecting a value that large

Jordy•16mo ago

- Compatibility with current databases (that want to use Drizzle). Introspecting and then pushing changes results in Integer columns not being unsigned anymore - Just having double the amount of integers to store. - No need to support negative values on a Int column if you never going to store that anyway. - Because it’s one of the data types available in a database. Just to name a few I can think of ✌️ Would love to see out of the box support for it in Drizzle

Andrii Sherman•16mo ago

Sure, we will add support for it. I'm working on it now. My question was, why do you need to store such big numbers in the database? I can understand the need for it with integer values. You might not want to use bigint in cases where you only need to extend the range of integers slightly, as it consumes 4 bytes compared to 8 bytes for bigints. However, it's still a valid choice to choose bigints vs ints, especially considering the current state of cheap storage, and in most cases, it's fine to opt for bigints (But there are a lot of systems with a legacy database structure, so I'm mostly speaking about new projects out there) If you are choosing bigint and I can't see any reasons for you to require unsigned (I'm just curious about real-world cases), keep in mind that BIGINT has a range of up to 9,223,372,036,854,775,807. For example, consider a messages table receiving 10 billion messages per day. In this scenario, it would take approximately 2,479,401 years to reach the limits. Do you ever need bigint unsigned?

Angelelz•16mo ago

The use case I've ran into before is the fact that SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. So if you have serial as your table id, whenever you need to reference it in a foreign key constraint on another table, you'll need to create a BIGINT UNSIGNED for it.

Andrii Sherman•16mo ago

yeah, that's why we've removed a recomendation for it in mysql from docs just to not have such issues for new users until we will support unsigned option

Gaming

Programming

Support for unsigned integers