D1 (SQLite) に BigInt の値 (Snowflake ID) を入れる

2025/8/19

Tech

「D1 に Snowflake ID を含むレコードを入れて、Snowflake ID のカラムでソートした結果を取得する」に際して、調べたことメモ

Snowflake ID

Snowflake ID は Twitter (現 X) が開発した識別子のフォーマット。

時間に基づいた 64 bit の符号なし整数なので、ID をソートするだけで時系列順のリストを作ることが出来る。64bit は以下のように割り当てられる:

[ 1bit 予約 (0) ] [ 41bit タイムスタンプ ] [ 10bit マシンID ] [ 12bit シーケンス ]

適当な投稿の ID (1807340962626290079) を見てみる。 2進数に変換すると 61 ビットのデータ量であることが分かる。

BigInt("1807340962626290079").toString(2);
// '1100100010100111101110000011110100001010110100100000110011111'

BigInt("1807340962626290079").toString(2).length;
// 61

下位 12ビット・10ビットを切り捨ててタイムスタンプを取得する。タイムスタンプは基準時刻 (Twitter の場合は 1288834974657 ¹) からの経過ミリ秒。

new Date(
  Number(((BigInt("1807340962626290079") >> 12n) >> 10n) + 1288834974657n),
).toISOString();
// '2024-06-30T09:10:38.918Z'

SQLite のデータ型

SQLite は5種類のデータ型がある。

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.

Snowflake ID (整数) を入れるなら INTEGER 型が第一候補になりそうだが、結局 TEXT にした (後述)。

Edge/JSON/ブラウザ環境で BigInt を使う

JavaScript の整数は53ビットまでしか表現出来ない。

Number.MAX_SAFE_INTEGER.toString(2).length;
// 53

そのため、仮に SQLite に Snowflake ID (BigInt) を数値として入れても、取り出したときに丸め誤差の影響を受ける。

1807340962626290079;
// 1807340962626290200

またJSON は BigInt に対応していないため、シリアライズすると誤差が出る。

JSON.stringify({ a: 12345678901234567890 });
// '{"a":12345678901234567000}'

Twitter も言っているので、BigIntの値は文字列としてやりとりする。

TEXT 型の ID でソートする

SQLite でソートする場合、INTEGER 型なら普通に大小比較をしてくれる。

$ echo "CREATE TABLE numbers(value INTEGER);
INSERT INTO numbers VALUES(2), (10), (100);
SELECT * FROM numbers ORDER BY value" | sqlite3
2
10
100

一方TEXT 型だと辞書順になるので、桁が異なると困る。

$ echo "CREATE TABLE numbers(value TEXT);
INSERT INTO numbers VALUES('2'), ('10'), ('100');
SELECT * FROM numbers ORDER BY value" | sqlite3
10
100
2

そのため SQLite へ INSERT するまえに 0 埋めをして桁を揃えることで辞書順でも大小比較出来るようにする。また、SELECT した後は 0 を消す。

drizzle の custom type を定義する場合は、こんな処理を入れる。

const snowflakeId = customType<{
  data: string;
  driverData: string;
}>({
  dataType() {
    return "text";
  },
  toDriver(value) {
    // Pad-zero
    return value.padStart(SNOWFLAKE_ID_MAX_DIGIT_NUMBER, "0");
  },
  fromDriver(value) {
    // Remove padded zeros
    return value.replace(/^0+/, "");
  },
});

ちなみに SNOWFLAKE_ID_MAX_DIGIT_NUMBER は 20 で計算している。

(2n ** 64n - 1n).toString(2);
// '1111111111111111111111111111111111111111111111111111111111111111'
(2n ** 64n - 1n).toString().length;
// 20;

基準時刻が 1288834974657 であることのソースが見つからない。検索するとさも当然な感じで計算する記事が沢山ある。 ↩

Snowflake ID

SQLite のデータ型

Edge/JSON/ブラウザ 環境で BigInt を使う

TEXT 型の ID でソートする

Footnotes

Edge/JSON/ブラウザ環境で BigInt を使う