Support for unsigned integers

dandyDandelion31156 · 2023-06-27T07:29:17.747Z

Does Drizzle ORM support defining unsigned integers in the schema?

A

Andrii Sherman•6/29/23, 7:30 PM

not yet

A

Andrii Sherman•6/29/23, 7:30 PM

but it will!

J

j.•8/5/23, 5:35 PM

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

sql

sql

operator?

R

Rhys•9/6/23, 5:42 AM

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

R

Rhys•9/6/23, 5:46 AM

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';
    },
});

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

db push

db push

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 } ...

RRhys also interested in being able to use an unsigned integer with drizzle and if the...

A

Andrii Sherman•9/6/23, 10:08 AM

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

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

R

Rhys•9/7/23, 4:48 AM

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

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

J

Jordy•9/7/23, 4:56 AM

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

JJordy - Compatibility with current databases (that want to use Drizzle). Introspecting...

A

Andrii Sherman•9/11/23, 9:00 AM

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?

A

Angelelz•9/11/23, 12:50 PM

The use case I've ran into before is the fact that

SERIAL

SERIAL

SERIAL

SERIAL is an alias for

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

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

BIGINT UNSIGNED

BIGINT UNSIGNED

BIGINT UNSIGNED for it.

A

Andrii Sherman•9/11/23, 1:12 PM

yeah, that's why we've removed a recomendation for it in mysql from docs

A

Andrii Sherman•9/11/23, 1:12 PM

just to not have such issues for new users until we will support unsigned option

Support for unsigned integers

Similar Threads

Similar Threads

Similar Threads