メインコンテンツへスキップ
見出し画像

SQLiteのページングを設計する:複合カーソルと重複・欠落の検証ワークブック

一覧の二ページ目に、さっき見た項目がもう一度現れる。あるいは、一件だけ読み飛ばしている。ORDER BYとLIMITを付けたのに、更新される一覧ではこのようなことが起きます。ページ番号は、変化し続けるデータの位置を安定して指すとは限りません。

この記事では、Pythonのsqlite3とメモリ内SQLiteを使い、複合カーソルによるページングを実装します。対象は、SQLのSELECTを使える学生、一覧APIを実装する若手エンジニア、設計と検証まで理解したい社会人です。「速いSQL」を一つ覚えるのではなく、並び順、更新時の意味、拒否条件を含む契約を作ります。

無料部分ではOFFSETのずれを再現し、どの方式を選ぶかの判断材料を渡します。有料部分では、LIMIT+1の全実装、同じ時刻を持つ行のテスト、追加・削除時の検証、APIへ広げる際のチェックリストを扱います。実データや本番DBを使わず、掲載した架空データだけで動かせます。

1. 先に結論:順番を一意にする

カーソルは「最後に見た行の並び順の値」です。 本記事ではcreatedとidの二つを降順に並べます。createdだけでは同じ値の行を区別できないので、一意なidを第二の条件にします。次ページは最後の行より小さい組を探します。並び順と比較条件を同じ組で設計することが重要です。

OFFSETも必要な方式です。小さい固定データでページ番号へ直接移動したい場合には分かりやすく、全件が変わらない条件なら位置のずれも起きません。一方、更新されるフィードを先へ読み進める用途では、最後に見た値を境界にする方式が適している場合があります。画面が何を約束するかから方式を選びます。

  • 任意のページ番号へ移動したい:OFFSET等の方式を検討する

  • 次へ順に読むことが中心:カーソル方式を検討する

  • 全件をある時点で漏れなく取得したい:スナップショットやエクスポートを別途設計する

  • 並び順の列が頻繁に変わる:更新時の意味を先に決める

最後の二点が重要です。カーソルを使うだけで、更新中のデータをある時点の完全な集合として取得できるわけではありません。ページを読む間に並び順の値が変われば、行が境界をまたぐことがあります。方式を採用するときは、何を保証し、何を保証しないかを画面やAPIの契約に含めます。

2. 前提と確認環境

Pythonの関数、タプル、例外、SQLのSELECTとWHEREが前提です。Windows上のPython 3.12.10で掲載コードを確認しています。sqlite3は標準ライブラリで、追加パッケージ、外部通信、APIキー、課金サービスは不要です。SQLiteの実行版は、次のコマンドで確認します。

import sqlite3
import sys

print(sys.version.split()[0])
print(sqlite3.sqlite_version)

実際の版はローカル検証記録にも保存します。Pythonの版とSQLiteの版は同じ数字ではありません。SQL機能やクエリ計画を調べるときは、SQLiteの実行版も確認してください。公式資料の確認日は2026年10月10日です。本記事は本番DBでの性能測定や、複数プロセスの同時更新を検証したものではありません。

3. 無料で試せるOFFSETのずれ

次のプログラムをoffset_demo.pyへ保存して実行します。メモリ内DBだけを作り、永続ファイルへ書き込みません。一ページ目を読んだ後で、先頭に新しい行を追加します。その後、二件飛ばして二ページ目を読むと、最初のページにあったid=4がもう一度現れます。

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("CREATE TABLE items(id INTEGER PRIMARY KEY, created INTEGER NOT NULL)")
    db.executemany("INSERT INTO items VALUES (?, ?)", [(5, 50), (4, 40), (3, 30)])
    first = db.execute(
        "SELECT id FROM items ORDER BY created DESC, id DESC LIMIT 2"
    ).fetchall()
    db.execute("INSERT INTO items VALUES (?, ?)", (6, 60))
    second = db.execute(
        "SELECT id FROM items ORDER BY created DESC, id DESC LIMIT 2 OFFSET 2"
    ).fetchall()
    assert first == [(5,), (4,)]
    assert second == [(4,), (3,)]
    print(first, second)

期待結果は[(5,), (4,)]と[(4,), (3,)]です。この実験は、OFFSETが壊れていることを示すものではありません。更新後のデータに対して「二件飛ばす」という指定を正しく実行した結果、利用者が期待した続きとずれたということです。SQLの動作と画面の約束が違う場所を観察します。

この段階で持ち帰れる判断は、「位置を指すページ番号」と「最後に見た値を指すカーソル」を分けることです。全件数や任意ページへのジャンプが必要なら、その要求を残します。カーソル方式へ変えればすべて簡単になると考えず、必要な機能と制約を並べて選びます。

4. 実装前に埋める契約

無料部分だけで仕様の検討を始められるように、次の項目を整理します。並び順の値にNULLを許すか、同じ値を持つ行を何で区別するか、追加された行を途中に含めるかを決めてください。決まっていない部分は、言語やDBの既定動作で埋めず未決として残します。

一覧の用途: 新しい順に次ページへ読み進める
並び順: created DESC, id DESC
同値の区別: 一意で不変のid
NULL: 並び順の列では禁止
ページサイズ: 1〜100
カーソル: 前ページ末尾の(created, id)
次ページ: カーソルより小さい組
更新: 並び順の値は変更しない前提
新規追加: 先頭側の追加は次ページに含めない
全件スナップショット: この方式単独では保証しない
認可: カーソルの内容とは別に毎回確認

ここまでが無料範囲です。

あなたへのおすすめ