Membuat Tool Database MySQL untuk AI Agent PHP

Terakhir diperbarui: 22 Agustus 2026

Implementasi Class DatabaseTool untuk AI Agent PHP

Dalam materi belajar ai-agent-php tingkat lanjut, memberikan akses database langsung ke Large Language Model (LLM) memerlukan standar arsitektur keamanan yang sangat ketat. Alih-alih mengizinkan model AI menyusun query SQL mentah secara bebas, kita membangun class DatabaseTool dengan pendekatan Whitelist Action & Parameterized Queries menggunakan PDO PHP murni:

Diagram Alur Data: Proteksi Keamanan DatabaseTool MySQL pada PHP

WebP Lossless • 66 KB • Schema Ready

Penjelasan Parameter dan Spesifikasi Operasi DatabaseTool

Berikut adalah tabel rincian parameter fungsi yang diekspos oleh DatabaseTool untuk memandu model AI dalam memilih operasi data yang tepat:

Kode Sumber Lengkap Class DatabaseTool di PHP

Berikut adalah implementasi class DatabaseTool berbasis ToolInterface:

<?php

namespace App\Tools;

use PDO;

class DatabaseTool implements ToolInterface
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function getDefinition(): array
    {
        return [
            'type'     => 'function',
            'function' => [
                'name'        => 'database_query',
                'description' => 'Mengakses basis data relasional untuk mencari pengguna, produk, artikel, atau menyimpan pesan masukan. Gunakan operasi: searchUser, getProduct, searchArticle, insertData.',
                'parameters'  => [
                    'type'       => 'object',
                    'properties' => [
                        'operation' => [
                            'type'        => 'string',
                            'enum'        => ['searchUser', 'getProduct', 'searchArticle', 'insertData'],
                            'description' => 'Pilihan operasi basis data yang diizinkan.',
                        ],
                        'params' => [
                            'type'        => 'object',
                            'description' => 'Parameter objek terstruktur untuk operasi yang dipilih.',
                        ],
                    ],
                    'required' => ['operation'],
                ],
            ],
        ];
    }

    public function execute(array $args): mixed
    {
        $operation = $args['operation'] ?? '';
        $params    = $args['params'] ?? [];

        return match ($operation) {
            'searchUser'    => $this->searchUser($params),
            'getProduct'    => $this->getProduct($params),
            'searchArticle' => $this->searchArticle($params),
            'insertData'    => $this->insertData($params),
            default         => ['status' => 'error', 'message' => "Operasi '$operation' tidak diizinkan."],
        };
    }

    private function searchUser(array $params): array
    {
        $keyword = trim($params['keyword'] ?? '');
        if (empty($keyword) || strlen($keyword) < 2) {
            return ['status' => 'error', 'message' => 'Kata kunci pencarian user minimal 2 karakter.'];
        }

        $stmt = $this->pdo->prepare(
            'SELECT id, name, email, role, created_at
             FROM users
             WHERE name LIKE ? OR email LIKE ?
             ORDER BY name ASC
             LIMIT 10'
        );
        $like = '%' . $keyword . '%';
        $stmt->execute([$like, $like]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return ['status' => 'success', 'count' => count($rows), 'data' => $rows];
    }

    private function getProduct(array $params): array
    {
        $productId = (int) ($params['id'] ?? 0);
        if ($productId <= 0) {
            return ['status' => 'error', 'message' => 'ID produk wajib berupa bilangan bulat positif.'];
        }

        $stmt = $this->pdo->prepare(
            'SELECT id, name, sku, price, stock, category, description
             FROM products
             WHERE id = ? AND is_active = 1
             LIMIT 1'
        );
        $stmt->execute([$productId]);
        $product = $stmt->fetch(PDO::FETCH_ASSOC);

        return $product 
            ? ['status' => 'success', 'data' => $product]
            : ['status' => 'error', 'message' => "Produk ID $productId tidak ditemukan."];
    }

    private function searchArticle(array $params): array
    {
        $keyword  = trim($params['keyword'] ?? '');
        $category = trim($params['category'] ?? '');

        $sql    = 'SELECT id, title, slug, category, excerpt, created_at FROM articles WHERE status = "published"';
        $values = [];

        if (!empty($keyword)) {
            $sql     .= ' AND (title LIKE ? OR content LIKE ?)';
            $values[] = '%' . $keyword . '%';
            $values[] = '%' . $keyword . '%';
        }

        if (!empty($category)) {
            $sql     .= ' AND category = ?';
            $values[] = $category;
        }

        $sql .= ' ORDER BY created_at DESC LIMIT 5';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($values);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        return ['status' => 'success', 'count' => count($rows), 'data' => $rows];
    }

    private function insertData(array $params): array
    {
        $table   = $params['table'] ?? '';
        $data    = $params['data'] ?? [];
        $allowed = ['feedback', 'contact_messages'];

        if (!in_array($table, $allowed, true)) {
            return ['status' => 'error', 'message' => "Tabel '$table' tidak diizinkan untuk operasi insert."];
        }

        if (empty($data) || !is_array($data)) {
            return ['status' => 'error', 'message' => 'Data insert tidak boleh kosong.'];
        }

        $columns      = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $stmt = $this->pdo->prepare("INSERT INTO $table ($columns) VALUES ($placeholders)");
        $stmt->execute(array_values($data));

        return ['status' => 'success', 'inserted_id' => (int) $this->pdo->lastInsertId()];
    }
}

Integrasi Function Calling DatabaseTool ke AI Agent Loop

Berikut adalah contoh skrip implementasi menghubungkan koneksi database PDO ke objek Agent:

<?php
// run-agent-database.php

require_once __DIR__ . '/vendor/autoload.php';

use App\OpenAIClient;
use App\Agent\Agent;
use App\Tools\DatabaseTool;

// Inisialisasi koneksi PDO aman
$pdo = new PDO('mysql:host=127.0.0.1;dbname=infokoding;charset=utf8mb4', 'db_user', 'secret_pass', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

$client = new OpenAIClient(getenv('OPENAI_API_KEY'));
$agent  = new Agent($client, maxIterations: 5);

// Registrasikan DatabaseTool
$agent->registerTool(new DatabaseTool($pdo));

// Prompt pencarian multi-kriteria
$prompt = 'Carikan pengguna dengan nama "Rusmawan" dan tampilkan peran (role) akunnya.';
$response = $agent->run($prompt);

echo "Jawaban Agent:\n" . $response . "\n";

<?php

namespace App\Tools;

use PDO;

class TransactionalDatabaseTool implements ToolInterface
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Eksekusi mutasi multi-langkah dengan jaminan ACID Transaction.
     */
    public function executeOrderTransaction(int $userId, int $productId, int $qty): array
    {
        try {
            // 1. Mulai transaksi database (Atomic)
            $this->pdo->beginTransaction();

            // 2. Periksa stok dengan lock baris (SELECT ... FOR UPDATE)
            $stmt = $this->pdo->prepare("SELECT stock, price FROM products WHERE id = ? FOR UPDATE");
            $stmt->execute([$productId]);
            $product = $stmt->fetch();

            if (!$product || $product['stock'] < $qty) {
                throw new \RuntimeException("Stok produk tidak mencukupi atau produk tidak ditemukan.");
            }

            // 3. Potong stok produk
            $updateStmt = $this->pdo->prepare("UPDATE products SET stock = stock - ? WHERE id = ?");
            $updateStmt->execute([$qty, $productId]);

            // 4. Catat riwayat pesanan
            $orderStmt = $this->pdo->prepare("INSERT INTO orders (user_id, product_id, quantity, created_at) VALUES (?, ?, ?, NOW())");
            $orderStmt->execute([$userId, $productId, $qty]);
            $orderId = (int) $this->pdo->lastInsertId();

            // 5. Commit perubahan data permanen jika seluruh tahap berhasil
            $this->pdo->commit();

            return [
                'status'   => 'success',
                'message'  => 'Transaksi pesanan berhasil diproses secara atomik.',
                'order_id' => $orderId,
            ];
        } catch (\Throwable $e) {
            // 6. Rollback instan jika terjadi exception / deadlock / timeout
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }

            return [
                'status'  => 'error',
                'message' => 'Transaksi dibatalkan (Rollback): ' . $e->getMessage(),
            ];
        }
    }
}

FAQ: Pertanyaan Seputar Tool Database MySQL AI Agent PHP