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

はじめに

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

目次

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

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のセットアップ

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

2. 環境変数の設定

# .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の両方に対応できるよう、データベース接続部分を抽象化しました：

# 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と互換性のある形に修正しました：

# 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. テーブル作成スクリプトの修正

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

# 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では日付・時間の扱いが異なりました：

# 互換性のある日付処理
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にはありません：

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では結果セットの構造が異なります。抽象化レイヤーで対応しました：

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

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

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

Seleniumとスラグサイズの制限

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

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

# 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が特殊な形式になっています：

# 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では環境変数を使用して設定を管理します：

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

7. 結果と学び

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

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

学んだこと

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

おわりに

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

このプロジェクトのコードはGitHubで公開しています。興味がある方は、ぜひ参考にしてください。

参考リソース