Posted inデータベース

SQLite — 思っている以上に使える軽量データベース

Database tutorial - IT technology blog
Database tutorial - IT technology blog

データベースのやり過ぎ問題

小規模な社内ツールを作っているとしよう — タスクトラッカー、ローカルの設定ストア、あるいは軽量なデータパイプラインかもしれない。習慣でPostgreSQLかMySQLに手を伸ばす。気づけばDockerコンテナを立ち上げ、ユーザーを作成し、コネクションプールを設定し、スキーママイグレーションを書いている… 3人しか触らないものに対して。

使うツールが間違っている。問題は機能不足ではなく、それを必要としない問題にサーバーグレードのインフラを持ち込むことだ。SQLiteはまさにこのような場合のために存在する。

MySQL、PostgreSQL、MongoDBをさまざまなプロジェクトで使ってきた。それぞれに本物の強みがある。しかし、初期の頃によく虚を突かれたことがある：SQLiteが正解であることが多いのに、ついつい飛ばしてしまっていた。

アーキテクチャ：SQLite vs. サーバーベースデータベース

SQLiteをうまく使うには、MySQLやPostgresとどれほど根本的に異なるかを理解する必要がある — 規模だけでなく、設計においても。

サーバーベースデータベース（MySQL、PostgreSQL）

  • 常駐デーモンプロセスが必要
  • クライアント・サーバー通信を使用（TCPまたはUnixソケット）
  • 複数ユーザーの同時アクセスを想定した設計
  • 別途インストール、ユーザー管理、バックアップ設定が必要
  • 同時接続が多いWebアプリに最適

SQLite

  • サーバー不要 — データベースエンジンはアプリに直接リンクされるライブラリ
  • データベース全体が単一の.dbファイルに収まる
  • プロセス単位でアクセス（同時書き込みは制限あり）
  • Pythonに同梱、Android/iOS SDKに含まれ、ブラウザにも組み込まれている
  • シングルユーザーアプリ、組み込みシステム、プロトタイプ、ローカルデータストレージに最適

SQLiteは「ミニMySQL」ではない。まったく異なる種類のツールだ。直接比較することはそもそもズレている — それぞれが異なる問題を解決するのだ。

メリットとデメリット

SQLiteが優れている理由

  • セットアップ不要 — Pythonの標準ライブラリにsqlite3が含まれている。インストール作業は一切不要。
  • ポータブル — データベースはただのファイル。コピー、移動、または十分に小さければバージョン管理にコミットできる。
  • 読み取りが高速 — 小規模で読み取りが多いデータセットでは、ネットワークのラウンドトリップオーバーヘッドがないためSQLiteがPostgresを上回ることが多い。ローカルクエリはTCPスタックを完全にスキップする。
  • 驚異的な信頼性 — SQLiteのテストスイートには9200万行以上のテストコードがある。航空機のフライトシステム、医療機器、数十億のモバイルアプリで動作している。信頼性の水準は本物だ。
  • ACID準拠 — コミット、ロールバック、セーブポイント、外部キーを含む完全なトランザクションサポート。FK強制はデフォルトでオフだが、接続ごとに簡単に有効化できる。

SQLiteが苦手な場面

  • 同時書き込み — SQLiteはデータベースレベルの書き込みロックを使用する。実際の書き込み負荷があると、複数のライターはキューに並ぶか「database is locked」エラーを投げる。
  • アクセス制御なし — ファイルにアクセスできる人なら誰でもデータベースを読める。マルチテナントアプリでは話にならない。
  • ALTER TABLEの制限 — 古いバージョンではスキーマ変更が辛かった。SQLite 3.35（2021年3月）でDROP COLUMNの適切なサポートが追加されたが、スキーマの柔軟性ではまだPostgresに及ばない。
  • 分散システム向けではない — レプリケーション、クラスタリング、自動フェイルオーバー — これらはいずれもSQLiteの領域ではない。

SQLiteが正しい選択である場面

以下の場面で一貫して活躍する：

  • 永続ストレージが必要なCLIツール
  • デスクトップアプリ（Electron、PyQtなど）
  • ローカル開発環境（本番ではPostgresに切り替え）
  • 中間結果を保存するデータパイプラインやETLスクリプト
  • テストスイート — 高速で独立した使い捨てデータベース
  • 実際に必要なデータベースが決まる前のプロトタイピング

私がよく使う構成：開発中はDjangoアプリをSQLiteで動かす。マイグレーションは1秒以内に終わり、テストは完全に独立していて、ローカルマシンにDockerの依存関係もない。スキーマが安定して本物の並行処理が必要になれば、設定を一つ変えるだけでステージングと本番をPostgresに切り替えられる。あとはDjangoがすべて処理してくれる。

実装ガイド

Pythonでの基本的な使い方（依存関係なし）

Pythonのsqlite3モジュールだけで十分だ：

import sqlite3

# 既存のDBを開くか、新しく作成する
conn = sqlite3.connect("myapp.db")
cursor = conn.cursor()

# テーブルを作成
cursor.execute("""
    CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        done INTEGER DEFAULT 0,
        created_at TEXT DEFAULT (datetime('now'))
    )
""")
conn.commit()

# 挿入
cursor.execute("INSERT INTO tasks (title) VALUES (?)", ("SQLiteチュートリアルを書く",))
conn.commit()

# クエリ
cursor.execute("SELECT * FROM tasks WHERE done = 0")
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

?プレースホルダーに注目してほしい。SQLクエリの構築にf文字列を使ってはいけない — パラメータ化クエリはローカルツールであってもSQLインジェクションを防ぐ。良い習慣を身につけることにコストはかからない。

外部キー強制を有効にする

SQLiteには外部キーサポートが組み込まれているが、後方互換性のためデフォルトでは無効になっている。各接続の開始時に有効化する：

conn = sqlite3.connect("myapp.db")
conn.execute("PRAGMA foreign_keys = ON")

コンテキストマネージャを使う

SQLite接続はPythonのコンテキストマネージャとして機能する。ブロックが成功すれば自動コミット、例外が発生すれば自動ロールバック：

with sqlite3.connect("myapp.db") as conn:
    conn.execute("INSERT INTO tasks (title) VALUES (?)", ("本番環境へデプロイ",))
    # 成功時は自動コミット、例外時は自動ロールバック

row_factoryを使った辞書スタイルの行アクセス

行はデフォルトでタプルとして返される — 使えないわけではないが、読みやすくはない。代わりにrow_factoryを設定してカラム名でアクセスできるようにする：

conn = sqlite3.connect("myapp.db")
conn.row_factory = sqlite3.Row

cursor = conn.cursor()
cursor.execute("SELECT * FROM tasks")
rows = cursor.fetchall()

for row in rows:
    print(row["title"], row["done"])  # カラム名でアクセス

CLIからデータベースを調べる

sqlite3 CLIをインストール済みなら、Pythonを一行も書かずにどんなデータベースでも調べられる：

# データベースを開く
sqlite3 myapp.db

# SQLiteシェル内：
.tables              # すべてのテーブルを一覧表示
.schema tasks        # tasksのCREATE TABLEを表示
SELECT * FROM tasks LIMIT 10;
.quit

# Ubuntu/Debianにインストール
sudo apt install sqlite3

# macOSには最初から入っている
sqlite3 --version

SQLAlchemyでSQLiteを使う

SQLAlchemyは広く使われているPython SQLツールキットとORMだ。実用的な利点の一つ：SQLiteとPostgresの切り替えが接続文字列の変更一つで済み、コードの書き直しは不要：

# SQLite（ローカル/開発）
DATABASE_URL = "sqlite:///./myapp.db"

# PostgreSQL（本番）
DATABASE_URL = "postgresql://user:pass@localhost/myapp"

# SQLAlchemyで使う場合
from sqlalchemy import create_engine
engine = create_engine(DATABASE_URL)

テスト用のインメモリSQLite

データベース名として":memory:"を渡すと、SQLiteはデータベース全体をRAM上に作成する。ディスクへの書き込みはなく、後片付けも不要：

import sqlite3

def get_test_db():
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    return conn

def test_insert_user():
    conn = get_test_db()
    conn.execute("INSERT INTO users (name) VALUES (?)", ("alice",))
    row = conn.execute("SELECT name FROM users").fetchone()
    assert row[0] == "alice"

テストを実行するたびにクリーンな状態から始まる。ティアダウン不要、残留状態なし、共有データベース行による不安定なテスト失敗もない。

SQLiteから卒業するタイミング

以下の状況になると限界が見えてくる：

  • 複数のプロセスがデータベースに同時書き込みする必要がある
  • 書き込み負荷が高く、頻繁なロック競合が発生する
  • 行レベルのアクセス制御が必要
  • ファイルシステムが永続的でないコンテナ環境にデプロイする

PostgreSQLへの移行が自然なアップグレードパスだ。最初からSQLAlchemyやDjango ORMで構築していれば、移行はほぼ設定値の変更一つで済む。クエリコードはたいていそのままでいい。

それ以外のすべて — ローカルツール、スクリプト、プロトタイプ、組み込みアプリ、テストスイート — SQLiteが正しいデフォルト選択だ。プロジェクトが必要としないインフラを構築せずに済む。

Share:
Tags: