🔄 PostgreSQL 直連遷移指南

本文件說明如何從 Supabase SDK 遷移到 PostgreSQL 直連的詳細步驟和注意事項。

📋 遷移概述

變更內容

✅ 移除 Supabase Python SDK 依賴
✅ 新增 SQLAlchemy + psycopg 直連
✅ 重構資料庫服務層
✅ 新增 ORM 模型定義
✅ 支援 Alembic 資料庫遷移

優勢

🚀 更好的效能: 直連減少中間層開銷
🔧 更多控制: 完整的 SQL 查詢控制
📊 ORM 支援: SQLAlchemy 提供強大的 ORM 功能
🔄 遷移管理: Alembic 提供版本化的資料庫遷移
🛡️ 連線池: 自動連線池管理

🔑 環境變數更新

舊的 Supabase 設定

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_KEY=your-anon-key

新的 PostgreSQL 設定

DB_HOST=db.your-project.supabase.co
DB_PORT=6543
DB_NAME=postgres
DB_USER=postgres
DB_PASSWORD=your-database-password

🔍 如何取得 PostgreSQL 連線資訊

從 Supabase Dashboard 取得

登入 Supabase Dashboard
- 前往 Supabase Dashboard
- 選擇您的專案
取得連線資訊
- 前往 "Settings" → "Database"
- 在 "Connection info" 區域找到：
  - Host: db.your-project-ref.supabase.co
  - Port: 6543 (PostgreSQL 直連埠)
  - Database: postgres
  - User: postgres
  - Password: 您設定的資料庫密碼

連線字串範例

postgresql://postgres:your-password@db.your-project-ref.supabase.co:6543/postgres

從其他 PostgreSQL 提供商取得

如果您使用其他 PostgreSQL 服務：

AWS RDS: 在 RDS Console 中查看端點資訊
Google Cloud SQL: 在 Cloud Console 中查看連線詳情
Azure Database: 在 Azure Portal 中查看連線字串
Railway/Render: 在專案設定中查看資料庫資訊

🛠️ 部署步驟

1. 更新環境變數

在 Hugging Face Spaces 的 Settings 中更新：

# 移除舊的 Supabase 設定
# SUPABASE_URL=...
# SUPABASE_KEY=...

# 新增 PostgreSQL 設定
DB_HOST=db.your-project-ref.supabase.co
DB_PORT=6543
DB_NAME=postgres
DB_USER=postgres
DB_PASSWORD=your-database-password

# 其他設定保持不變
LINE_CHANNEL_ACCESS_TOKEN=your-line-token
LINE_CHANNEL_SECRET=your-line-secret
OPENROUTER_API_KEY=your-openrouter-key
OPENROUTER_MODEL=anthropic/claude-3-haiku

2. 初始化資料庫

部署後，執行資料庫初始化：

# 在容器中執行
python -m backend.database.init_db

或者透過 API 端點觸發初始化（如果實作了相關端點）。

3. 驗證連線

檢查健康檢查端點：

curl https://your-space-url.hf.space/health

應該會看到：

{
  "status": "healthy",
  "message": "LINE Bot API is operational", 
  "database": "connected",
  "database_url": "postgresql://your-host:6543/postgres"
}

📊 資料庫模型對應

新的 SQLAlchemy 模型

# backend/database/models.py

class User(Base):
    __tablename__ = "users"
    user_id = Column(String(255), primary_key=True)
    name = Column(String(255))
    email = Column(String(255))
    # ... 其他欄位

class Product(Base):
    __tablename__ = "products"
    product_id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    price = Column(DECIMAL(10, 2), nullable=False)
    # ... 其他欄位

資料表關聯

# 一對多關聯
class User(Base):
    orders = relationship("Order", back_populates="user")

class Order(Base):
    user = relationship("User", back_populates="orders")
    order_items = relationship("OrderItem", back_populates="order")

🔄 API 變更

查詢方式變更

舊的 Supabase 方式

result = supabase.table("users").select("*").eq("name", "張三").execute()

新的 SQLAlchemy 方式

db = get_database_session()
users = db.query(User).filter(User.name == "張三").all()

錯誤處理改進

def database_operation():
    db = None
    try:
        db = get_database_session()
        # 資料庫操作
        result = db.query(User).all()
        db.commit()
        return result
    except Exception as e:
        if db:
            db.rollback()
        logger.error(f"資料庫操作失敗: {str(e)}")
        raise
    finally:
        if db:
            close_database_session(db)

🚀 效能優化

連線池設定

# backend/database/connection.py
engine = create_engine(
    settings.DATABASE_URL,
    pool_size=10,          # 連線池大小
    max_overflow=20,       # 最大溢出連線
    pool_pre_ping=True,    # 連線前檢查
    pool_recycle=3600,     # 1小時後回收連線
)

查詢優化

# 使用 eager loading 避免 N+1 查詢
from sqlalchemy.orm import joinedload

users_with_orders = db.query(User).options(
    joinedload(User.orders)
).all()

# 使用索引優化查詢
# 在模型中定義索引
class User(Base):
    __tablename__ = "users"
    name = Column(String(255), index=True)  # 建立索引

🔧 開發工具

Alembic 遷移

# 初始化 Alembic
alembic init alembic

# 建立遷移檔案
alembic revision --autogenerate -m "Initial migration"

# 執行遷移
alembic upgrade head

# 回滾遷移
alembic downgrade -1

本地開發

# 安裝依賴
pip install -r requirements.txt

# 設定環境變數
export DB_HOST=localhost
export DB_PORT=5432
export DB_NAME=linebot_dev
export DB_USER=postgres
export DB_PASSWORD=password

# 初始化資料庫
python -m backend.database.init_db

# 執行應用程式
uvicorn backend.main:app --reload --port 7860

🛡️ 安全性考量

連線安全

使用 SSL 連線

DATABASE_URL = f"postgresql+psycopg://{user}:{password}@{host}:{port}/{db}?sslmode=require"

限制資料庫權限
- 建立專用的應用程式使用者
- 只授予必要的資料表權限
- 定期輪換密碼
連線字串保護
- 使用環境變數儲存敏感資訊
- 避免在日誌中記錄連線字串
- 使用密碼管理工具

📈 監控與除錯

日誌設定

# 啟用 SQLAlchemy 日誌
engine = create_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,  # 在 DEBUG 模式下顯示 SQL
)

效能監控

import time
from functools import wraps

def monitor_db_performance(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        execution_time = time.time() - start_time
        logger.info(f"{func.__name__} 執行時間: {execution_time:.2f}秒")
        return result
    return wrapper

🚨 常見問題

Q: 連線失敗怎麼辦？

A: 檢查以下項目：

主機名稱和埠號是否正確
使用者名稱和密碼是否正確
資料庫是否允許外部連線
防火牆設定是否正確

Q: 效能比 Supabase SDK 慢？

A: 可能原因：

連線池設定不當
查詢未優化
缺少適當的索引
網路延遲問題

Q: 如何處理資料庫遷移？

A: 使用 Alembic：

建立遷移腳本
在部署前測試遷移
備份資料庫
執行遷移

📞 技術支援

如果遇到問題：

檢查應用程式日誌
驗證環境變數設定
測試資料庫連線
參考 SQLAlchemy 官方文件

注意: 遷移前請務必備份您的資料庫，並在測試環境中驗證所有功能正常運作。