PHP 資料庫抽象層實戰：從 PDO 到 Doctrine DBAL、ORM 的架構演進指南

為什麼需要資料庫抽象層？

• 可攜性：切換資料庫 (MySQL → PostgreSQL) 僅需修改設定
• 安全性：統一參數化查詢，杜絕 SQL Injection
• 維護性：集中管理連線、交易、錯誤處理、日誌
• 測試性：輕鬆 Mock/Stub 資料庫層進行單元測試
• 開發效率：Query Builder、ORM 減少樣板碼

層級 0：原生 PDO (基礎但足夠強大)

<?php
// Database.php - 單例連線管理
class Database {
    private static ?PDO $instance = null;
    
    public static function getInstance(): PDO {
        if (self::$instance === null) {
            $config = require __DIR__ . '/config/db.php';
            $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
            self::$instance = new PDO($dsn, $config['user'], $config['pass'], [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::ATTR_PERSISTENT         => false,
            ]);
        }
        return self::$instance;
    }
    
    // 禁止複製/序列化
    private function __construct() {}
    private function __clone() {}
    public function __wakeup() {}
}

// Repository 範例
class UserRepository {
    public function findById(int $id): ?array {
        $stmt = Database::getInstance()->prepare('SELECT * FROM users WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }
    
    public function findByEmail(string $email): ?array {
        $stmt = Database::getInstance()->prepare('SELECT * FROM users WHERE email = ?');
        $stmt->execute([$email]);
        return $stmt->fetch() ?: null;
    }
    
    public function create(array $data): int {
        $pdo = Database::getInstance();
        $sql = 'INSERT INTO users (email, name, password_hash, created_at) VALUES (?, ?, ?, NOW())';
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$data['email'], $data['name'], $data['password_hash']]);
        return (int)$pdo->lastInsertId();
    }
}

層級 1：Query Builder (靈活、類 SQL、無實體映射)

# composer require doctrine/dbal

<?php
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;

$config = new Configuration();
$conn = DriverManager::getConnection([
    'driver'   => 'pdo_mysql',
    'host'     => '127.0.0.1',
    'dbname'   => 'my_db',
    'user'     => 'user',
    'password' => 'pass',
    'charset'  => 'utf8mb4',
], $config);

$qb = $conn->createQueryBuilder();

// 查詢
$users = $qb->select('id', 'name', 'email')
    ->from('users')
    ->where('status = :status')
    ->andWhere('created_at > :date')
    ->setParameter('status', 'active')
    ->setParameter('date', '2024-01-01')
    ->orderBy('created_at', 'DESC')
    ->setMaxResults(20)
    ->fetchAllAssociative();

// 插入
$qb->insert('users')
    ->values([
        'email' => '?', 'name' => '?', 'password_hash' => '?', 'created_at' => 'NOW()'
    ])
    ->setParameter(0, 'test@example.com')
    ->setParameter(1, 'Test User')
    ->setParameter(2, password_hash('secret', PASSWORD_DEFAULT))
    ->executeStatement();

// 更新
$qb->update('users')
    ->set('last_login', 'NOW()')
    ->where('id = ?')
    ->setParameter(0, 123)
    ->executeStatement();

// 交易
$conn->transactional(function($conn) {
    $qb = $conn->createQueryBuilder();
    $qb->insert('orders')->values([...])->executeStatement();
    $qb->update('users')->set('balance', 'balance - ?')->where('id = ?')->executeStatement();
});

層級 2：Doctrine ORM (物件關聯映射、豐富領域模型)

# composer require doctrine/orm

<?php
// src/Entity/User.php
#[Entity]
#[Table(name: 'users')]
class User {
    #[Id, Column(type: 'integer'), GeneratedValue]
    private int $id;
    
    #[Column(type: 'string', length: 180, unique: true)]
    private string $email;
    
    #[Column(type: 'string', length: 100)]
    private string $name;
    
    #[Column(type: 'string', length: 255)]
    private string $passwordHash;
    
    #[Column(type: 'datetime_immutable')]
    private DateTimeImmutable $createdAt;
    
    #[OneToMany(targetEntity: Order::class, mappedBy: 'user')]
    private Collection $orders;
    
    // Getters/Setters / 方法
    public function getId(): int { return $this->id; }
    public function getEmail(): string { return $this->email; }
    public function addOrder(Order $order): void { $this->orders->add($order); $order->setUser($this); }
    public function getOrders(): Collection { return $this->orders; }
}

// Repository
class UserRepository extends ServiceEntityRepository {
    public function findActiveUsers(int $limit = 20): array {
        return $this->createQueryBuilder('u')
            ->where('u.status = :status')
            ->setParameter('status', 'active')
            ->orderBy('u.createdAt', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}

// 使用 (在 Controller/Service 中)
$user = $entityManager->getRepository(User::class)->find(123);
$newUser = new User();
$newUser->setEmail('new@example.com');
$newUser->setName('New User');
$newUser->setPasswordHash(password_hash('secret', PASSWORD_DEFAULT));
$entityManager->persist($newUser);
$entityManager->flush(); // 同步到資料庫

原教學碼 (PHPLib DB_Sql) 對照現代做法

架構決策指南：選哪一層？

關鍵最佳實踐 (跨所有層級)

1. 連線設定外部化：環境變數 / .env / config/packages/doctrine.yaml
2. 嚴格模式：PDO::ATTR_EMULATE_PREPARES => false、strict_types=1
3. 交易邊界明確：Service 層管理交易，Repository 不自行 commit
4. N+1 問題防範：ORM 使用 join/fetch、batchSize、EXTRA_LAZY 集合
5. 讀寫分離：主庫寫入、從庫讀取 (Proxy/HAProxy/中介軟體)
6. 遷移工具：doctrine/migrations、phinx、laravel migrate 版本化 Schema
7. 測試策略：單元測試 Mock Repository，整合測試用 Testcontainers (真實 DB)

總結

PHP 資料庫抽象層已從 PHPLib 時代的 DB_Sql 演進為成熟的三層生態：**PDO (核心)** → **Doctrine DBAL (Query Builder)** → **Doctrine ORM / Eloquent (全功能 ORM)**。選型原則：**從簡單開始，隨複雜度成長而升級**。現代專案啟動建議：

• 小型/中型：PDO + 手寫 Repository 或 Laravel Eloquent
• 大型/企業級：Doctrine ORM + Symfony 生態
• 高效能/特殊需求：自訂 PDO 封裝 + Swoole/RoadRunner

核心不變：**參數化查詢防注入、連線設定外部化、交易邊界清晰、Schema 版本化遷移**。