データベースサーバー

SQLite

概要

SQLiteは世界で最も広く配布されている軽量で高速なファイルベースのSQLデータベースエンジンです。サーバー不要で組み込み用途に最適化されており、設定なしで動作するゼロ設定データベースとして知られています。単一のディスクファイルに完全なデータベースを格納し、全てのデータ、インデックス、メタデータを管理します。モバイルアプリ、IoTデバイス、デスクトップアプリケーション、Webアプリケーションの組み込みデータベースとして標準的に採用され、特に軽量性と信頼性により継続的な需要があります。

詳細

SQLite 2025年版は、3.47系列の最新機能を含む成熟したデータベースエンジンとして、完全なACID準拠トランザクション、強力なSQL方言サポート、豊富な拡張機能を提供しています。Public Domainライセンスにより完全に自由に使用でき、C言語で書かれた単一のライブラリファイルとして配布されます。FTS5フルテキスト検索、R-Tree空間インデックス、JSON1拡張、ウィンドウ関数、CTE（共通テーブル式）、パーシャルインデックスなど先進的な機能をサポート。メモリマップI/O、WALモード、並行読み取り、自動VACUUM、外部キー制約など、エンタープライズレベルの機能も備えています。

主な特徴

• ゼロ設定: サーバー不要、インストール不要で即座に利用可能
• 軽量性: 単一ファイル形式で数百KBのライブラリサイズ
• ACID準拠: 完全なトランザクション処理と整合性保証
• 豊富なSQL機能: SQL-92標準準拠とSQLite独自の拡張機能
• クロスプラットフォーム: すべての主要OSとアーキテクチャで動作
• パブリックドメイン: 完全に自由なライセンスで商用利用可能

メリット・デメリット

メリット

• 組み込み用途で圧倒的シェアと20年以上の開発実績による高い信頼性
• サーバー設定不要でローカル開発や小規模アプリケーションに最適
• 単一ファイル形式によりバックアップとデプロイが極めて簡単
• メモリ使用量が少なく、限られたリソース環境でも快適に動作
• パブリックドメインライセンスにより法的制約なしで自由に使用可能
• 豊富なプログラミング言語バインディングとツールエコシステム

デメリット

• 同時書き込みが制限されており、高負荷Webアプリケーションには不向き
• ネットワーク透過性がなく、複数のクライアントからの同時アクセスに制約
• データベースサイズが大きくなると（数TB）パフォーマンスが低下
• 組み込み関数やストアドプロシージャなど一部の高度なSQL機能が制限的
• レプリケーション機能がないため高可用性構成が困難
• 大規模なデータ分析やウェアハウス用途には適さない

参考ページ

• SQLite公式サイト
• SQLite日本語ドキュメント
• SQLiteダウンロード

書き方の例

インストールと基本セットアップ

# Ubuntu/Debianでのインストール
sudo apt update
sudo apt install sqlite3 sqlite3-tools

# CentOS/RHELでのインストール
sudo dnf install sqlite

# macOS（Homebrew）
brew install sqlite

# Windowsでのインストール
# https://www.sqlite.org/download.html からダウンロード

# データベース作成（ファイルが存在しない場合は自動作成）
sqlite3 myapp.db

# SQLiteシェル内での確認
.version
.help
.databases
.quit

# コマンドラインからSQL実行
sqlite3 myapp.db "SELECT sqlite_version();"

# 既存データベースへの接続
sqlite3 /path/to/existing/database.db

基本的なデータベース操作

-- データベース作成（ファイルベース）
-- sqlite3 example.db コマンドで自動作成

-- テーブル作成
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    age INTEGER CHECK (age >= 0),
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- インデックス作成
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- データ挿入
INSERT INTO users (username, email, first_name, last_name, age) VALUES
('john_doe', '[email protected]', '太郎', '田中', 30),
('jane_smith', '[email protected]', '花子', '佐藤', 28),
('bob_wilson', '[email protected]', 'ボブ', 'ウィルソン', 35);

-- データ取得
SELECT * FROM users;
SELECT username, email FROM users WHERE age > 25;
SELECT COUNT(*) FROM users WHERE is_active = 1;

-- データ更新
UPDATE users SET age = 31 WHERE username = 'john_doe';
UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = 1;

-- データ削除
DELETE FROM users WHERE is_active = 0;

-- テーブル構造確認
.schema users
PRAGMA table_info(users);

-- データベース全体の構造確認
.tables
.schema

高度なSQL機能とSQLite拡張

-- JSON操作（JSON1拡張）
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    details JSON
);

INSERT INTO products (name, details) VALUES
('ノートPC', '{"price": 89800, "specs": {"cpu": "Intel i7", "memory": "16GB"}, "tags": ["laptop", "work"]}'),
('スマートフォン', '{"price": 65000, "specs": {"os": "Android", "storage": "128GB"}, "tags": ["mobile", "android"]}');

-- JSON値の取得
SELECT name, json_extract(details, '$.price') as price FROM products;
SELECT name, details ->> '$.specs.cpu' as cpu FROM products;

-- JSON配列操作
SELECT name, json_each.value as tag 
FROM products, json_each(products.details, '$.tags');

-- JSON更新
UPDATE products 
SET details = json_set(details, '$.price', 85000, '$.updated_at', datetime('now'))
WHERE name = 'ノートPC';

-- ウィンドウ関数
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    sale_date DATE,
    amount INTEGER
);

INSERT INTO sales (product_name, sale_date, amount) VALUES
('商品A', '2024-01-15', 1000),
('商品B', '2024-01-16', 1500),
('商品A', '2024-01-17', 1200),
('商品C', '2024-01-18', 800);

-- ウィンドウ関数の例
SELECT 
    product_name,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_name ORDER BY sale_date) as running_total,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as rank_by_amount,
    LAG(amount, 1) OVER (PARTITION BY product_name ORDER BY sale_date) as prev_amount
FROM sales;

-- CTE（共通テーブル式）
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', sale_date) as month,
        product_name,
        SUM(amount) as total_amount
    FROM sales
    GROUP BY strftime('%Y-%m', sale_date), product_name
),
top_products AS (
    SELECT month, product_name, total_amount,
           ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_amount DESC) as rank
    FROM monthly_sales
)
SELECT month, product_name, total_amount
FROM top_products
WHERE rank <= 2;

-- 再帰CTE（階層データ処理）
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
(1, '電子機器', NULL),
(2, 'コンピュータ', 1),
(3, 'スマートフォン', 1),
(4, 'ノートPC', 2),
(5, 'デスクトップPC', 2);

-- 階層の全パス取得
WITH RECURSIVE category_path(id, name, path, level) AS (
    -- ベースケース：ルートカテゴリ
    SELECT id, name, name as path, 0 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 再帰ケース：子カテゴリ
    SELECT c.id, c.name, cp.path || ' > ' || c.name, cp.level + 1
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, name, path, level FROM category_path ORDER BY path;

フルテキスト検索（FTS5）と空間インデックス

-- FTS5フルテキスト検索テーブル作成
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    author,
    content='articles',  -- 元テーブル指定
    content_rowid='id'   -- ROWIDカラム指定
);

-- 元テーブル作成
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- データ挿入
INSERT INTO articles (title, content, author) VALUES
('SQLiteの基本', 'SQLiteは軽量で高速なデータベースエンジンです。ファイルベースで動作し、サーバー設定が不要です。', '山田太郎'),
('データベース設計', 'よいデータベース設計は正規化とパフォーマンスのバランスが重要です。SQLiteでも同様の原則が適用されます。', '佐藤花子'),
('Web開発とSQLite', 'Web開発においてSQLiteは開発環境やプロトタイピングに最適です。Djangoやflaskでもサポートされています。', '田中次郎');

-- FTSテーブルに同期
INSERT INTO articles_fts(rowid, title, content, author)
SELECT id, title, content, author FROM articles;

-- 全文検索実行
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'データベース AND 設計';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'title:Web OR content:開発';

-- 検索結果のハイライト
SELECT 
    highlight(articles_fts, 0, '<mark>', '</mark>') as highlighted_title,
    snippet(articles_fts, 1, '<b>', '</b>', '...', 32) as content_snippet
FROM articles_fts 
WHERE articles_fts MATCH 'SQLite';

-- R-Tree空間インデックス（地理空間データ）
CREATE VIRTUAL TABLE locations USING rtree(
    id,              -- 主キー
    min_x, max_x,    -- X座標範囲
    min_y, max_y     -- Y座標範囲
);

-- 地点データ挿入
INSERT INTO locations VALUES
(1, 139.7, 139.7, 35.68, 35.68),  -- 東京
(2, 135.5, 135.5, 34.69, 34.69),  -- 大阪  
(3, 136.9, 136.9, 35.18, 35.18);  -- 名古屋

-- 範囲検索
SELECT id FROM locations 
WHERE min_x >= 135.0 AND max_x <= 140.0 
AND min_y >= 34.0 AND max_y <= 36.0;

-- 最近傍検索
SELECT id FROM locations
ORDER BY (min_x - 139.7) * (min_x - 139.7) + (min_y - 35.68) * (min_y - 35.68)
LIMIT 3;

トランザクションとPRAGMA設定

-- トランザクション制御
BEGIN TRANSACTION;

INSERT INTO users (username, email, first_name, last_name, age) 
VALUES ('test_user', '[email protected]', 'テスト', 'ユーザー', 25);

UPDATE users SET age = age + 1 WHERE id = 1;

-- 条件によるロールバック
SELECT changes(); -- 影響を受けた行数確認
-- 問題があれば ROLLBACK、なければ COMMIT
COMMIT;

-- セーブポイント使用
BEGIN TRANSACTION;
    INSERT INTO users (username, email) VALUES ('user1', '[email protected]');
    SAVEPOINT sp1;
    
    INSERT INTO users (username, email) VALUES ('user2', '[email protected]');
    SAVEPOINT sp2;
    
    -- user2の挿入のみ取り消し
    ROLLBACK TO SAVEPOINT sp2;
    
    -- user1は残してコミット
COMMIT;

-- 重要なPRAGMA設定
-- 外部キー制約有効化
PRAGMA foreign_keys = ON;

-- WALモード有効化（並行読み取り改善）
PRAGMA journal_mode = WAL;

-- 同期モード設定（パフォーマンス vs 安全性）
PRAGMA synchronous = NORMAL;  -- FULL, NORMAL, OFF

-- キャッシュサイズ設定（MB単位）
PRAGMA cache_size = 10000;  -- 約40MB

-- 自動VACUUM有効化
PRAGMA auto_vacuum = INCREMENTAL;

-- メモリマップI/O設定（パフォーマンス向上）
PRAGMA mmap_size = 268435456;  -- 256MB

-- データベース整合性チェック
PRAGMA integrity_check;
PRAGMA quick_check;

-- テーブル統計情報更新
ANALYZE;

-- データベース最適化
VACUUM;
PRAGMA optimize;

-- データベース情報確認
PRAGMA database_list;
PRAGMA table_info(users);
PRAGMA index_list(users);
PRAGMA compile_options;

ファイル操作とバックアップ

# データベースダンプ作成
sqlite3 myapp.db .dump > backup.sql

# 特定テーブルのダンプ
sqlite3 myapp.db "SELECT sql FROM sqlite_master WHERE name='users';" > users_schema.sql
sqlite3 myapp.db ".dump users" > users_data.sql

# CSVエクスポート
sqlite3 -header -csv myapp.db "SELECT * FROM users;" > users.csv

# CSVインポート
sqlite3 myapp.db << EOF
.mode csv
.import users.csv users
EOF

# SQLファイルからリストア
sqlite3 new_database.db < backup.sql

# データベースファイルコピー（最も簡単なバックアップ）
cp myapp.db myapp_backup_$(date +%Y%m%d).db

# WALモードでのバックアップ（オンライン中も可能）
sqlite3 myapp.db "VACUUM INTO 'backup_$(date +%Y%m%d).db';"

# データベース統計表示
sqlite3 myapp.db << EOF
.schema
.tables
SELECT name, sql FROM sqlite_master WHERE type='table';
SELECT COUNT(*) as table_count FROM sqlite_master WHERE type='table';
PRAGMA page_count;
PRAGMA page_size;
PRAGMA freelist_count;
EOF

プログラミング言語での活用例

# Python SQLite3ライブラリ使用例
import sqlite3
from datetime import datetime

# データベース接続
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row  # 辞書ライクなアクセス

# カーソル作成
cursor = conn.cursor()

# テーブル作成
cursor.execute('''
CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT NOT NULL,
    level TEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')

# データ挿入（プリペアドステートメント）
cursor.execute(
    "INSERT INTO logs (message, level) VALUES (?, ?)",
    ("アプリケーション開始", "INFO")
)

# 複数データ挿入
log_data = [
    ("ユーザーログイン", "INFO"),
    ("データ処理中", "DEBUG"),
    ("エラーが発生", "ERROR")
]
cursor.executemany(
    "INSERT INTO logs (message, level) VALUES (?, ?)",
    log_data
)

# トランザクション管理
try:
    cursor.execute("BEGIN TRANSACTION")
    cursor.execute("INSERT INTO logs (message, level) VALUES (?, ?)", 
                  ("重要な処理", "INFO"))
    # 何らかの処理...
    cursor.execute("COMMIT")
except Exception as e:
    cursor.execute("ROLLBACK")
    print(f"エラー: {e}")

# データ取得
cursor.execute("SELECT * FROM logs WHERE level = ? ORDER BY timestamp DESC", ("INFO",))
rows = cursor.fetchall()

for row in rows:
    print(f"[{row['timestamp']}] {row['level']}: {row['message']}")

# 接続終了
conn.close()

// Node.js SQLite3使用例
const sqlite3 = require('sqlite3').verbose();

// データベース接続
const db = new sqlite3.Database('example.db');

// テーブル作成
db.serialize(() => {
    db.run(`CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        completed BOOLEAN DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    // データ挿入
    const stmt = db.prepare("INSERT INTO tasks (title) VALUES (?)");
    ["SQLiteを学ぶ", "アプリを作る", "テストを書く"].forEach(task => {
        stmt.run(task);
    });
    stmt.finalize();

    // データ取得
    db.all("SELECT * FROM tasks ORDER BY created_at", (err, rows) => {
        if (err) {
            console.error(err);
        } else {
            console.log('タスク一覧:');
            rows.forEach(row => {
                console.log(`${row.id}: ${row.title} [${row.completed ? '完了' : '未完了'}]`);
            });
        }
    });
});

// 接続終了
db.close();