[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

drizzle-orm/drizzle-orm/src/sqlite-core/README.md

Line 223 in 474f1a3

integer('...', { mode: 'number' | 'timestamp' | 'bigint' })

The code/type does not actually list bigint

drizzle-orm/drizzle-orm/src/sqlite-core/columns/integer.ts

Lines 152 to 156 in 474f1a3

	export interface IntegerConfig<
	TMode extends 'number' | 'timestamp' | 'timestamp_ms' = 'number' | 'timestamp' | 'timestamp_ms',
	> {
	mode: TMode;
	}

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 (#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 #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.