LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Eve04lim

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

Posted at

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

# 必要なパッケージのインストール
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つのコンポーネントに分割しました：

  1. Webアプリケーション: データ表示、分析機能
  2. データ収集スクリプト: 別プロセスとして実行
# 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でも永続的なデータ管理が可能に
  • スケーラビリティの確保: 今後のユーザー数・データ量の増加に対応できる基盤を構築
  • 開発ワークフローの改善: 複数環境からの共通データベースアクセスが可能に

学んだこと

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

おわりに

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

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

参考リソース

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?