drizzle-team
[BUG]: SQLite does not support BigInt as an integer type
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
Probably an omission, but anyway BigInt support looks to be on the way #277 #558
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.
@MrRahulRamkumar why was blob chosen over integer, as SQLite offers 8-byte integers? using as blob makes it hard to query
@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' })
@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
bigintmode. SQLite does not have a nativebiginttype 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.
@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.