SQLiteからSupabase (PostgreSQL) への移行 - EC価格追跡アプリケーション開発記

# SQLiteからSupabase (PostgreSQL) への移行 - EC価格追跡アプリケーション開発記

## はじめに

ECサイト（Amazon、楽天市場、Yahoo!ショッピング）の商品価格を自動的に追跡するツールを開発していました。当初はSQLiteを使用していましたが、アプリケーションの成長に伴い、より堅牢なデータベースソリューションが必要になりました。本記事では、SQLiteからSupabase（PostgreSQL）への移行について、その背景、実装方法、そして得られた利点を共有します。

## 目次
1. プロジェクト概要
2. SQLiteの限界
3. なぜSupabaseを選んだのか
4. 移行プロセス
5. 発生した問題と解決策
6. Herokuへのデプロイで直面した課題
7. 結果と学び

## 1. プロジェクト概要

このプロジェクトは、ECサイトから商品情報を自動的に収集し、価格や在庫の変動を追跡・分析するシステムです。主な機能は以下の通りです：

- 複数のECサイトからの商品情報収集（Selenium/BeautifulSoup）
- 価格履歴のグラフ表示と分析（Matplotlib/Pandas）
- 「買い時」分析と価格予測（Scikit-learn）
- 価格変動時の通知機能（Email/Slack）
- Webインターフェース（Flask）

## 2. SQLiteの限界

開発初期段階では、シンプルさと導入のしやすさからSQLiteを選択しました。しかし、以下の問題に直面しました：

- **同時接続の制限**: Webアプリケーションとバックグラウンドのデータ収集プロセスが同時にデータベースにアクセスすると、ロックの問題が発生
- **リモートアクセスの難しさ**: ローカルファイルベースのため、複数環境からのアクセスが困難
- **スケーラビリティの制約**: データ量の増加に伴いパフォーマンスが低下
- **クラウドデプロイの課題**: Herokuのようなステートレス環境では、アプリケーションの再起動時にデータが失われる

## 3. なぜSupabaseを選んだのか

様々なオプション（MySQL、PostgreSQL、Firebase）を検討した結果、Supabaseを選択しました：

- **PostgreSQLベース**: 信頼性が高く成熟したRDBMS
- **無料枠の寛大さ**: 十分なストレージと接続数が無料枠で提供される
- **簡単なセットアップ**: クラウドホスティングですぐに使い始められる
- **RESTful API**: データベース操作をHTTPリクエストで行える
- **リアルタイム機能**: 将来の機能拡張に役立つ
- **認証機能**: 将来的にユーザー認証を実装する際に便利

## 4. 移行プロセス

移行作業は以下の手順で行いました：

### 1. Supabaseのセットアップ

```bash
# 必要なパッケージのインストール
pip install psycopg2-binary python-dotenv
```

### 2. 環境変数の設定

```ini
# .env
DB_TYPE=postgresql
DATABASE_URL=postgresql://postgres.[YOUR_REF]:[YOUR_PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?sslmode=require
```

### 3. データベース抽象化レイヤーの修正

SQLiteとPostgreSQLの両方に対応できるよう、データベース接続部分を抽象化しました：

```python
# database.py
import logging
import os
import sqlite3
import psycopg2
from psycopg2.extras import RealDictCursor
from datetime import datetime
import threading

from config import DATABASE_URL, DB_TYPE, DB_NAME

logger = logging.getLogger(__name__)

class Database:
    _local = threading.local()

    def __init__(self):
        self.connect()
        self.create_tables()

    def connect(self):
        try:
            if DB_TYPE.lower() == "sqlite":
                self._local.connection = sqlite3.connect(DB_NAME)
                self._local.connection.row_factory = sqlite3.Row
                self._local.cursor = self._local.connection.cursor()
            elif DB_TYPE.lower() == "postgresql":
                conn_str = DATABASE_URL if DATABASE_URL else f"dbname={DB_NAME}"
                self._local.connection = psycopg2.connect(conn_str, cursor_factory=RealDictCursor)
                self._local.cursor = self._local.connection.cursor()
            else:
                raise ValueError(f"Unsupported database type: {DB_TYPE}")

            logger.info(f"Connected to {DB_TYPE} database")
        except Exception as e:
            logger.error(f"Failed to connect to database: {e}")
            raise
```

### 4. SQLクエリの互換性対応

SQLite固有の構文をPostgreSQLと互換性のある形に修正しました：

```python
# SQLiteの場合
self.cursor.execute("""
    INSERT INTO products (name, url, platform, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?)
""", (name, url, platform, now, now))

# PostgreSQLの場合
self.cursor.execute("""
    INSERT INTO products (name, url, platform, created_at, updated_at) 
    VALUES (%s, %s, %s, %s, %s) RETURNING id
""", (name, url, platform, now, now))
```

### 5. テーブル作成スクリプトの修正

```python
def create_tables(self):
    try:
        # SQLiteとPostgreSQLの違いを吸収する構文
        create_products_table = """
        CREATE TABLE IF NOT EXISTS products (
            id SERIAL PRIMARY KEY,
            name TEXT NOT NULL,
            url TEXT NOT NULL UNIQUE,
            image_url TEXT,
            product_code TEXT,
            platform TEXT NOT NULL,
            created_at TIMESTAMP NOT NULL,
            updated_at TIMESTAMP NOT NULL
        )
        """
        self.cursor.execute(create_products_table)
        # 他のテーブル作成処理...
        self.connection.commit()
    except Exception as e:
        logger.error(f"Failed to create tables: {e}")
        self.connection.rollback()
        raise
```

### 6. Flaskアプリでのスレッドセーフ対応

Flaskのマルチスレッド環境でデータベース接続を正しく管理するため、以下の変更を加えました：

```python
# app.py
from flask import g

# リクエストごとにデータベース接続を取得する関数
def get_db():
    if not hasattr(g, 'db'):
        g.db = Database()
    return g.db

# リクエスト終了時にデータベース接続を閉じる
@app.teardown_appcontext
def close_db(error):
    if hasattr(g, 'db'):
        g.db.close()
```

## 5. 発生した問題と解決策

### 日付型の扱いの違い

SQLiteとPostgreSQLでは日付・時間の扱いが異なりました：

```python
# 互換性のある日付処理
if isinstance(price_info['fetched_at'], str):
    fetched_at = price_info['fetched_at']
else:
    fetched_at = price_info['fetched_at'].strftime('%Y-%m-%d %H:%M:%S')
```

### RETURNING句の対応

PostgreSQLでは挿入時にIDを返す`RETURNING`句が使えますが、SQLiteにはありません：

```python
if DB_TYPE.lower() == "postgresql":
    self.cursor.execute(
        """INSERT INTO products (...) VALUES (...) RETURNING id""",
        (...)
    )
    product_id = self.cursor.fetchone()['id']
else:
    self.cursor.execute(
        """INSERT INTO products (...) VALUES (...)""",
        (...)
    )
    product_id = self.cursor.lastrowid
```

### リターン型の違い

SQLiteとpsycopg2では結果セットの構造が異なります。抽象化レイヤーで対応しました：

```python
# Database.pyでRealDictCursorを使用
self._local.connection = psycopg2.connect(conn_str, cursor_factory=RealDictCursor)
```

## 6. Herokuへのデプロイで直面した課題

Herokuにデプロイする際に、いくつかの問題に直面しました：

### Seleniumとスラグサイズの制限

Seleniumと関連依存関係を含めると、Herokuの無料枠のスラグサイズ制限（500MB）を超えてしまいました。解決策として、アプリケーションを2つのコンポーネントに分割しました：

1. **Webアプリケーション**: データ表示、分析機能
2. **データ収集スクリプト**: 別プロセスとして実行

```python
# config.py - 環境に応じた振り分け
IS_PRODUCTION = os.getenv("FLASK_ENV") == "production"

# app.py - Heroku環境での条件分岐
if IS_PRODUCTION:
    try:
        from main import ECTracker
        tracker = ECTracker()
        # 直接関数呼び出し
    except Exception as e:
        flash(f'エラー: {str(e)}', 'error')
else:
    # 開発環境ではサブプロセスを使用
    result = subprocess.run([sys.executable, 'main.py', ...])
```

### データベースURL形式の変更

Herokuでは、PostgreSQLの接続URLが特殊な形式になっています：

```python
# config.py
DATABASE_URL = os.getenv("DATABASE_URL")
if DATABASE_URL and DATABASE_URL.startswith("postgres://"):
    # Herokuのpostgres://をpostgresql://に置換（SQLAlchemy 1.4+対応）
    DATABASE_URL = DATABASE_URL.replace("postgres://", "postgresql://", 1)
```

### 環境変数の設定

Herokuでは環境変数を使用して設定を管理します：

```bash
heroku config:set DATABASE_URL=postgresql://...
```

## 7. 結果と学び

SQLiteからSupabaseへの移行により、以下の利点が得られました：

- **信頼性の向上**: データベースのロック問題が解消され、安定性が向上
- **パフォーマンスの向上**: 特に多数の商品を追跡する際に顕著
- **クラウドデプロイへの対応**: Herokuでも永続的なデータ管理が可能に
- **スケーラビリティの確保**: 今後のユーザー数・データ量の増加に対応できる基盤を構築
- **開発ワークフローの改善**: 複数環境からの共通データベースアクセスが可能に

### 学んだこと

1. **抽象化の重要性**: データベース操作を抽象化することで、バックエンドの切り替えがスムーズに
2. **環境変数の活用**: 異なる環境での設定を環境変数で管理する重要性
3. **スレッドセーフなコード**: Webアプリケーションでのデータベース接続管理の重要性
4. **アーキテクチャの分離**: 重いタスク（スクレイピング）を分離することで、デプロイの問題を解決

## おわりに

SQLiteからSupabaseへの移行は、初期段階では追加の作業が必要でしたが、長期的には開発効率とアプリケーションの品質向上につながりました。小規模プロジェクトからスタートしても、将来的なスケーリングを見据えた設計判断は非常に重要です。

このプロジェクトのコードは[GitHub](https://github.com/yourusername/ec-price-tracker)で公開しています。興味がある方は、ぜひ参考にしてください。

## 参考リソース

- [Supabase公式ドキュメント](https://supabase.io/docs)
- [PostgreSQL公式ドキュメント](https://www.postgresql.org/docs/)
- [Heroku PostgreSQL](https://devcenter.heroku.com/articles/heroku-postgresql)
- [psycopg2ドキュメント](https://www.psycopg.org/docs/)