科学と神々株式会社 Advent Calendar 2025

Hybrid License System Day 15: データベース操作とトランザクション

Last updated at 2025-12-14Posted at 2025-12-14

🎄 科学と神々株式会社アドベントカレンダー 2025

Hybrid License System Day 15: データベース操作とトランザクション

Auth Service編 (5/5)

📖 はじめに

Day 15では、データベース操作とトランザクションを学びます。better-sqlite3の最適化、トランザクション管理、データ整合性の保証、パフォーマンスチューニングを実装します。

🗄️ better-sqlite3の特徴

同期APIの利点

// better-sqlite3は同期API（高速・シンプル）
const Database = require('better-sqlite3');

// ❌ 非同期（他のSQLiteライブラリ）
// await db.get('SELECT * FROM users WHERE id = ?', [id]);

// ✅ 同期（better-sqlite3）
const user = db.prepare('SELECT * FROM users WHERE id = ?').get(id);

パフォーマンス比較

ライブラリ	速度	API	用途
better-sqlite3	🚀 最速	同期	サーバーサイド
sqlite3	🐢 遅い	非同期	レガシー
sql.js	⚡ 中速	同期/非同期	ブラウザ

🏗️ DBService クラス設計

完全なDBService実装

// auth-service/src/dbService.js
const Database = require('better-sqlite3');
const path = require('path');

class DBService {
  constructor() {
    const dbPath = process.env.DB_PATH || path.join(__dirname, '../data/licenses.db');

    // データベース接続
    this.db = new Database(dbPath, {
      verbose: process.env.NODE_ENV === 'development' ? console.log : null,
      fileMustExist: false
    });

    // 初期化
    this.initialize();
  }

  /**
   * データベース初期化
   */
  initialize() {
    // WALモード（並行アクセス性能向上）
    this.db.pragma('journal_mode = WAL');

    // 同期モード（パフォーマンス優先）
    this.db.pragma('synchronous = NORMAL');

    // キャッシュサイズ（10MB）
    this.db.pragma('cache_size = 10000');

    // 外部キー制約有効化
    this.db.pragma('foreign_keys = ON');

    // テーブル作成
    this.createTables();

    // インデックス作成
    this.createIndexes();
  }

  /**
   * テーブル作成
   */
  createTables() {
    // usersテーブル
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS users (
        user_id TEXT PRIMARY KEY,
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        plan TEXT NOT NULL DEFAULT 'free',
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      )
    `);

    // licensesテーブル
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS licenses (
        license_id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        client_id TEXT UNIQUE NOT NULL,
        status TEXT NOT NULL DEFAULT 'active',
        created_at TEXT NOT NULL,
        expires_at TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
      )
    `);

    // audit_logsテーブル
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS audit_logs (
        log_id TEXT PRIMARY KEY,
        user_id TEXT,
        action TEXT NOT NULL,
        details TEXT,
        timestamp TEXT NOT NULL,
        severity TEXT DEFAULT 'MEDIUM',
        FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
      )
    `);
  }

  /**
   * インデックス作成（パフォーマンス最適化）
   */
  createIndexes() {
    const indexes = [
      'CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)',
      'CREATE INDEX IF NOT EXISTS idx_licenses_user_id ON licenses(user_id)',
      'CREATE INDEX IF NOT EXISTS idx_licenses_client_id ON licenses(client_id)',
      'CREATE INDEX IF NOT EXISTS idx_licenses_status ON licenses(status)',
      'CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON audit_logs(user_id)',
      'CREATE INDEX IF NOT EXISTS idx_audit_logs_timestamp ON audit_logs(timestamp)',
      'CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON audit_logs(action)'
    ];

    for (const sql of indexes) {
      this.db.exec(sql);
    }
  }

  /**
   * プリペアドステートメント（SQLインジェクション対策）
   */
  getUserByEmail(email) {
    const stmt = this.db.prepare('SELECT * FROM users WHERE email = ?');
    return stmt.get(email);
  }

  getUserById(userId) {
    const stmt = this.db.prepare('SELECT * FROM users WHERE user_id = ?');
    return stmt.get(userId);
  }

  getLicenseByClientId(clientId) {
    const stmt = this.db.prepare('SELECT * FROM licenses WHERE client_id = ?');
    return stmt.get(clientId);
  }

  getLicensesByUserId(userId) {
    const stmt = this.db.prepare('SELECT * FROM licenses WHERE user_id = ?');
    return stmt.all(userId);
  }

  /**
   * データベース接続クローズ
   */
  close() {
    this.db.close();
  }
}

module.exports = DBService;

🔄 トランザクション管理

トランザクションの基本

// auth-service/src/dbService.js

class DBService {
  /**
   * ライセンス作成（トランザクション）
   */
  createLicense(userId, clientId, plan) {
    // トランザクション定義
    const transaction = this.db.transaction((userId, clientId, plan) => {
      // 1. ユーザー存在確認
      const user = this.db.prepare('SELECT * FROM users WHERE user_id = ?').get(userId);
      if (!user) {
        throw new Error('User not found');
      }

      // 2. 重複チェック
      const existingLicense = this.db.prepare(
        'SELECT * FROM licenses WHERE client_id = ?'
      ).get(clientId);

      if (existingLicense) {
        throw new Error('License already exists for this client');
      }

      // 3. ライセンスID生成
      const licenseId = this.generateLicenseId();
      const now = new Date().toISOString();
      const expiresAt = this.calculateExpiryDate(plan);

      // 4. ライセンス挿入
      this.db.prepare(`
        INSERT INTO licenses (license_id, user_id, client_id, status, created_at, expires_at)
        VALUES (?, ?, ?, ?, ?, ?)
      `).run(licenseId, userId, clientId, 'active', now, expiresAt);

      // 5. 監査ログ記録
      this.logAuditEvent(userId, 'LICENSE_CREATED', {
        licenseId,
        clientId,
        plan
      });

      // 6. 作成したライセンスを返す
      return this.db.prepare('SELECT * FROM licenses WHERE license_id = ?').get(licenseId);
    });

    // トランザクション実行
    // 全ステップ成功 → COMMIT
    // エラー発生 → 自動ROLLBACK
    return transaction(userId, clientId, plan);
  }

  /**
   * ユーザー作成（トランザクション）
   */
  createUser(email, passwordHash, plan = 'free') {
    const transaction = this.db.transaction((email, passwordHash, plan) => {
      // 重複チェック
      const existing = this.db.prepare('SELECT * FROM users WHERE email = ?').get(email);
      if (existing) {
        throw new Error('User already exists');
      }

      const userId = this.generateUserId();
      const now = new Date().toISOString();

      // ユーザー作成
      this.db.prepare(`
        INSERT INTO users (user_id, email, password_hash, plan, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?)
      `).run(userId, email, passwordHash, plan, now, now);

      // 監査ログ
      this.logAuditEvent(userId, 'USER_CREATED', { email, plan });

      return this.db.prepare('SELECT * FROM users WHERE user_id = ?').get(userId);
    });

    return transaction(email, passwordHash, plan);
  }

  /**
   * ライセンス更新（トランザクション）
   */
  updateLicenseStatus(licenseId, newStatus) {
    const transaction = this.db.transaction((licenseId, newStatus) => {
      // ライセンス存在確認
      const license = this.db.prepare('SELECT * FROM licenses WHERE license_id = ?').get(licenseId);
      if (!license) {
        throw new Error('License not found');
      }

      // ステータス更新
      this.db.prepare(`
        UPDATE licenses
        SET status = ?
        WHERE license_id = ?
      `).run(newStatus, licenseId);

      // 監査ログ
      this.logAuditEvent(license.user_id, 'LICENSE_STATUS_UPDATED', {
        licenseId,
        oldStatus: license.status,
        newStatus
      });

      return this.db.prepare('SELECT * FROM licenses WHERE license_id = ?').get(licenseId);
    });

    return transaction(licenseId, newStatus);
  }

  /**
   * ライセンス削除（トランザクション + カスケード）
   */
  deleteLicense(licenseId) {
    const transaction = this.db.transaction((licenseId) => {
      const license = this.db.prepare('SELECT * FROM licenses WHERE license_id = ?').get(licenseId);
      if (!license) {
        throw new Error('License not found');
      }

      // 監査ログ（削除前に記録）
      this.logAuditEvent(license.user_id, 'LICENSE_DELETED', {
        licenseId,
        clientId: license.client_id
      });

      // ライセンス削除
      this.db.prepare('DELETE FROM licenses WHERE license_id = ?').run(licenseId);

      return { success: true, deletedLicense: license };
    });

    return transaction(licenseId);
  }
}

📊 パフォーマンス最適化

バッチ挿入の最適化

class DBService {
  /**
   * バッチユーザー作成（高速）
   */
  createUsersBatch(users) {
    // プリペアドステートメント準備
    const insertStmt = this.db.prepare(`
      INSERT INTO users (user_id, email, password_hash, plan, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?)
    `);

    // トランザクション内でバッチ実行
    const insertMany = this.db.transaction((users) => {
      const now = new Date().toISOString();

      for (const user of users) {
        const userId = this.generateUserId();
        insertStmt.run(
          userId,
          user.email,
          user.passwordHash,
          user.plan || 'free',
          now,
          now
        );
      }
    });

    // 実行（トランザクション境界）
    insertMany(users);

    return { inserted: users.length };
  }

  /**
   * バルク更新
   */
  updateExpiredLicenses() {
    const now = new Date().toISOString();

    // 期限切れライセンスを一括更新
    const result = this.db.prepare(`
      UPDATE licenses
      SET status = 'expired'
      WHERE expires_at < ? AND status = 'active'
    `).run(now);

    return { updated: result.changes };
  }
}

クエリ最適化

class DBService {
  /**
   * 統計情報取得（最適化済み）
   */
  getStatistics() {
    // 単一クエリで複数集計（効率的）
    const stats = this.db.prepare(`
      SELECT
        COUNT(DISTINCT u.user_id) as total_users,
        COUNT(DISTINCT l.license_id) as total_licenses,
        SUM(CASE WHEN l.status = 'active' THEN 1 ELSE 0 END) as active_licenses,
        SUM(CASE WHEN l.status = 'expired' THEN 1 ELSE 0 END) as expired_licenses,
        SUM(CASE WHEN u.plan = 'free' THEN 1 ELSE 0 END) as free_users,
        SUM(CASE WHEN u.plan = 'professional' THEN 1 ELSE 0 END) as pro_users,
        SUM(CASE WHEN u.plan = 'enterprise' THEN 1 ELSE 0 END) as enterprise_users
      FROM users u
      LEFT JOIN licenses l ON u.user_id = l.user_id
    `).get();

    return stats;
  }

  /**
   * ページネーション付きクエリ
   */
  getUsersPaginated(page = 1, pageSize = 20) {
    const offset = (page - 1) * pageSize;

    // 総件数
    const totalResult = this.db.prepare('SELECT COUNT(*) as count FROM users').get();
    const total = totalResult.count;

    // データ取得（LIMIT/OFFSET）
    const users = this.db.prepare(`
      SELECT user_id, email, plan, created_at
      FROM users
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `).all(pageSize, offset);

    return {
      data: users,
      pagination: {
        page,
        pageSize,
        total,
        totalPages: Math.ceil(total / pageSize)
      }
    };
  }
}

🔍 データ整合性の保証

外部キー制約

class DBService {
  /**
   * 外部キー制約のテスト
   */
  testForeignKeyConstraints() {
    try {
      // 存在しないuser_idでライセンス作成を試みる
      this.db.prepare(`
        INSERT INTO licenses (license_id, user_id, client_id, status, created_at, expires_at)
        VALUES (?, ?, ?, ?, ?, ?)
      `).run('test-lic', 'non-existent-user', 'test-client', 'active', new Date().toISOString(), new Date().toISOString());
    } catch (error) {
      // FOREIGN KEY constraint failed
      console.log('Foreign key constraint working:', error.message);
    }
  }

  /**
   * カスケード削除のテスト
   */
  testCascadeDelete() {
    const transaction = this.db.transaction(() => {
      // ユーザー削除
      const userId = 'test-user-123';
      this.db.prepare('DELETE FROM users WHERE user_id = ?').run(userId);

      // 関連ライセンスも自動削除されているか確認
      const remainingLicenses = this.db.prepare(
        'SELECT * FROM licenses WHERE user_id = ?'
      ).all(userId);

      console.log('Remaining licenses (should be 0):', remainingLicenses.length);
    });

    transaction();
  }
}

CHECK制約

-- テーブル作成時にCHECK制約追加
CREATE TABLE licenses (
  license_id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  client_id TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'expired', 'revoked')),
  created_at TEXT NOT NULL,
  expires_at TEXT NOT NULL CHECK(expires_at > created_at),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

🧪 テスト戦略

トランザクションテスト

// auth-service/tests/database.test.js
const DBService = require('../src/dbService');

describe('Database Transactions', () => {
  let db;

  beforeEach(() => {
    db = new DBService();
  });

  afterEach(() => {
    db.close();
  });

  it('should rollback transaction on error', () => {
    expect(() => {
      db.createLicense('non-existent-user', 'test-client', 'free');
    }).toThrow('User not found');

    // ライセンスが作成されていないことを確認
    const license = db.getLicenseByClientId('test-client');
    expect(license).toBeUndefined();
  });

  it('should commit transaction on success', () => {
    // ユーザー作成
    const user = db.createUser('test@example.com', 'hash', 'free');

    // ライセンス作成
    const license = db.createLicense(user.user_id, 'test-client', 'free');

    // データベースに保存されているか確認
    const savedLicense = db.getLicenseByClientId('test-client');
    expect(savedLicense.license_id).toBe(license.license_id);
  });

  it('should handle concurrent transactions', () => {
    const user = db.createUser('concurrent@example.com', 'hash', 'free');

    // 同時に2つのライセンスを作成試行（同じclient_id）
    db.createLicense(user.user_id, 'concurrent-client', 'free');

    // 2回目は失敗するはず（UNIQUE制約）
    expect(() => {
      db.createLicense(user.user_id, 'concurrent-client', 'free');
    }).toThrow('License already exists');
  });
});

📈 パフォーマンスベンチマーク

ベンチマークツール

// auth-service/benchmarks/db-performance.js
const DBService = require('../src/dbService');

function benchmarkInserts() {
  const db = new DBService();
  const iterations = 10000;

  console.time('Single Inserts');
  for (let i = 0; i < iterations; i++) {
    db.createUser(`user${i}@example.com`, 'hash', 'free');
  }
  console.timeEnd('Single Inserts');

  // バッチ挿入
  const users = [];
  for (let i = 0; i < iterations; i++) {
    users.push({ email: `batch${i}@example.com`, passwordHash: 'hash', plan: 'free' });
  }

  console.time('Batch Inserts');
  db.createUsersBatch(users);
  console.timeEnd('Batch Inserts');

  db.close();
}

benchmarkInserts();
// 結果例:
// Single Inserts: 5234ms
// Batch Inserts: 187ms (28x faster!)

🎯 次のステップ

Day 16では、Admin Serviceの設計を学びます。管理機能の責務、React SPAダッシュボード、API設計、認可モデル（RBAC）について詳しく解説します。

🔗 関連リンク

次回予告: Day 16では、管理ダッシュボードの設計とRBAC認可モデルを詳しく解説します！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up