Why specify number mode in SQLite integer column?

[hyunbinseo]

The integer column's default return type is number.

What is the purpose of { mode: 'number' }?

https://orm.drizzle.team/docs/column-types/sqlite#integer

const table = sqliteTable('table', {
  id: integer('id')
});

// you can customize integer mode to be number, boolean, timestamp, timestamp_ms
integer('id', { mode: 'number' })

// to make integer primary key auto increment
integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true })

Note that both schemas generate same SQL.

integer('id').primaryKey();
integer('id', { mode: 'number' }).primaryKey();

`id` integer PRIMARY KEY NOT NULL

[Vishtar]

It's for the primary key. Primary key should be unique.

Boolean: only two possible unique keys.
Timestamps: what if data arrives at the same time?

[hyunbinseo]

Doesn't the SQLite integer primary key automatically ensure integer and uniqueness?

A PRIMARY KEY in an SQLite table is usually just a UNIQUE constraint.

The value of an INTEGER PRIMARY KEY column must always be a non-NULL integer because the INTEGER PRIMARY KEY is an alias for the ROWID.

https://www.sqlite.org/quirks.html