README

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
Features
Usage
API Reference
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.

pardnchiu / mysql-cli

Maintainers

Details