MySQL CLI

Lightweight PHP MySQL client with chainable syntax, query builder and read-write separation.
Following stateless architecture principles, providing stable and reliable database operation experience.

• Three Core Features
  • Chainable Syntax
  • Read-Write Separation
  • Stable Connection
• Features
• Usage
  • Installation
  • Environment Variables Setup
  • Basic Usage
• API Reference
  • Query Builder
  • JOIN Operations
  • Data Operations
• Error Handling
• License
• Author

Three Core Features

Chainable Syntax

Intuitive query builder syntax that makes complex SQL queries simple and readable with low learning curve

Read-Write Separation

Automatically identifies query types and routes to corresponding database connections, supports read-write separation architecture, effectively distributes database load and improves overall system performance

Stable Connection

Retry mechanism automatically handles network jitter and temporary connection failures, ensuring reliability in unstable network environments

Features

• Environment Variable Configuration: Flexible environment variable settings, supports multi-environment deployment
• Slow Query Monitoring: Automatically logs queries over 20ms, assists with performance optimization
• Secure Parameter Binding: Prepared statements prevent SQL injection attacks
• Complete CRUD: Supports full database operations for create, read, update, delete
• SQL Function Support: Built-in common MySQL function recognition and processing
• Stateless Design: Independent cleanup for each request

Usage

Installation

composer require pardnchiu/mysql-cli

Environment Variables Setup

Read Database (Optional)

DB_READ_HOST=localhost
DB_READ_PORT=3306
DB_READ_USER=read_user
DB_READ_PASSWORD=read_password
DB_READ_DATABASE=your_database
DB_READ_CHARSET=utf8mb4

Write Database (Required for write operations)

DB_WRITE_HOST=localhost
DB_WRITE_PORT=3306
DB_WRITE_USER=write_user
DB_WRITE_PASSWORD=write_password
DB_WRITE_DATABASE=your_database
DB_WRITE_CHARSET=utf8mb4

Basic Usage

<?php

use pardnchiu\SQL;

// Basic query
$users = SQL::table("users")
  ->where("status", "active")
  ->where("age", ">", 18)
  ->get();

// Complex query with aggregation
$reports = SQL::table("orders")
  ->select("user_id", "COUNT(*) as order_count", "SUM(amount) as total")
  ->where("created_at", ">=", "2024-01-01")
  ->groupBy("user_id")
  ->orderBy("total", "DESC")
  ->limit(10)
  ->get();

API Reference

Query Builder

• table($table, $target = "READ") - Set target table and connection type

  SQL::table("users")           // Read operation (default)
  SQL::table("users", "WRITE")  // Write operation

• select($fields) - Specify query fields

  SQL::table("users")->select("id", "name", "email");
  SQL::table("products")->select("COUNT(*) as total");

• where($column, $operator, $value) - Add conditions

  // Basic conditions
  SQL::table("users")->where("status", "active");
  SQL::table("orders")->where("amount", ">", 100);
  
  // LIKE search (automatically adds wildcards)
  SQL::table("users")->where("name", "LIKE", "John");

• orderBy($column, $direction) - Sorting

  SQL::table("users")->orderBy("created_at", "DESC");
  SQL::table("products")->orderBy("price", "ASC");

• limit($count) / offset($count) - Pagination

  SQL::table("users")->limit(20)->offset(40);

JOIN Operations

// Inner join
SQL::table("users")
  ->join("profiles", "users.id", "profiles.user_id")
  ->get();

// Left join
SQL::table("users")
  ->leftJoin("orders", "users.id", "orders.user_id")
  ->select("users.name", "COUNT(orders.id) as order_count")
  ->get();

// Right join
SQL::table("departments")
  ->rightJoin("employees", "departments.id", "employees.dept_id")
  ->get();

Data Operations

// Insert data and get ID
$userId = SQL::table("users", "WRITE")
  ->insertGetId([
    "name"        => "John Doe",
    "email"       => "john@example.com", 
    "created_at"  => "NOW()"
  ]);

// Update data
$result = SQL::table("users", "WRITE")
  ->where("id", $userId)
  ->update([
    "last_login"  => "NOW()",
    "login_count" => "login_count + 1"
  ]);

// Raw query
$customData = SQL::read(
  "SELECT u.name, COUNT(o.id) as orders FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > ? GROUP BY u.id",
  ["2024-01-01"]
);

Error Handling

try {
  $result = SQL::table("users", "WRITE")
    ->where("id", 1)
    ->update([
      "status"      => "active", 
      "updated_at"  => "NOW()"
    ]);
  
  // Check slow query warnings
  if (!empty($result["info"])) {
    error_log("Slow query warning: " . $result["info"]);
  }
  
  echo "Update successful, affected rows: " . $result["affected_rows"];
    
} catch (\PDOException $e) {
  // Database related errors
  error_log("Database error: " . $e->getMessage());
  
  // Handle based on error code
  $errorCode = $e->getCode();
  if ($errorCode === 2006 || $errorCode === 2013) {
    // Connection interrupted, system will auto retry
    echo "Connection exception, please try again later";
  } else {
    echo "Database operation failed";
  }
    
} catch (\InvalidArgumentException $e) {
  // Parameter errors
  error_log("Parameter error: " . $e->getMessage());
  echo "Request parameters are incorrect";
    
} catch (\Exception $e) {
  // Other errors
  error_log("System error: " . $e->getMessage());
  echo "System temporarily unavailable, please contact administrator";
}

Performance Monitoring

// Enable detailed logging
error_reporting(E_ALL);

// Automatically log slow queries (over 20ms)
$users = SQL::table("users")
  ->where("status", "active")
  ->get();

// Check system logs:
// [Info] PD\SQL: [Slow Query: 25.43ms] [SELECT * FROM users WHERE status = ?]

License

This project is licensed under MIT.

Author

邱敬幃 Pardn Chiu

---

©️ 2024 邱敬幃 Pardn Chiu