[BUG]: SQLite does not support BigInt as an integer type

[mateuszradomski]

What version of drizzle-orm are you using?

master

What version of drizzle-kit are you using?

master

Describe the Bug

Even tough bigint is listed as a supported integer mode in sqlite-core/README.md

https://github.com/drizzle-team/drizzle-orm/blob/474f1a3534446b8679c97eba43d0667900f9af45/drizzle-orm/src/sqlite-core/README.md?plain=1#L223

The code/type does not actually list bigint

https://github.com/drizzle-team/drizzle-orm/blob/474f1a3534446b8679c97eba43d0667900f9af45/drizzle-orm/src/sqlite-core/columns/integer.ts#L152-L156

It seems like an omission that resulted from copy-pasting from README's of Postgres/MySQL. I don't see why SQLite shouldn't support bigint as an integer type.

Expected behavior

SQLite supports bigint as an integer type

Environment & setup

master branch

[tacomanator]

Probably an omission, but anyway BigInt support looks to be on the way #277 #558

[MrRahulRamkumar]

Yes the docs are incorrect. One thing to note is my PR (https://github.com/drizzle-team/drizzle-orm/pull/558) that adds bigint support doesn't actually store the values in an integer column since SQLite does not have a native bigint data type. So I instead opted to store it in a blob type column.

A consequence of this decision is that queries using operators like > or < would not work. (though equality checks do work )

Would love to hear your feedback on this implementation.

[GustavoOS]

@MrRahulRamkumar why was blob chosen over integer, as SQLite offers 8-byte integers? using as blob makes it hard to query

[MrRahulRamkumar]

@MrRahulRamkumar why was blob chosen over integer, as SQLite offers 8-byte integers? using as blob makes it hard to query

@GustavoOS If you want to store values that would not fit into an 8 byte integer you would use the bigint mode. SQLite does not have a native bigint type so sadly I don't think there is any way to make querying easier (operators like > or < )

If your values can be stored in an 8-byte integer you can just use an integer column like so:

integer('id', { mode: 'number' })

[GustavoOS]

@MrRahulRamkumar why was blob chosen over integer, as SQLite offers 8-byte integers? using as blob makes it hard to query

@GustavoOS If you want to store values that would not fit into an 8 byte integer you would use the bigint mode. SQLite does not have a native bigint type so sadly I don't think there is any way to make querying easier (operators like > or < )

If your values can be stored in an 8-byte integer you can just use an integer column like so:

integer('id', { mode: 'number' })

But this would be mapped into a JS number type, which is a double-precision floating-point (loses information). As SQLite offers 8-byte integers, shouldn't they be mapped into bigints? It really seems like 8 byte integers are the same as having a bigint type.

[MrRahulRamkumar]

@GustavoOS Yes I see your point now, somebody else has also submitted a feature request in https://github.com/drizzle-team/drizzle-orm/issues/1980. I will try and create a PR soon.

[GustavoOS]

The main reason for this should be classified as bug is because a BLOB type cannot be queried. Meaning that you cant have a select * from id where id=234567890123456789012345 if a column is of type BLOB.