Database Connection Pool (`Core\Database`)
To maintain maximum throughput under heavy API loads (`Performance First`), LilaPHP avoids heavy ORM abstraction layers. Instead, `Core\Database` manages a persistent singleton PDO connection pool configured via `.env` at the root, with automatic reconnection retries and full support for MySQL/MariaDB (`3306`) and SQLite.
Switching Databases via CLI (`php cli.php db:switch`)
LilaPHP makes switching between MySQL and SQLite instantaneous with the db:switch CLI command:
# Switch to SQLite standalone mode
php cli.php db:switch sqlite
# Switch back to MySQL
php cli.php db:switch mysql
Singleton PDO Pool (`Database::getInstance()`)
`Database::getInstance()` returns a ready-to-use, pre-configured `\PDO` object (`PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION`, `PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC`).
<?php
use Core\Response;
use Core\Database;
use Core\Validate;
$clean = Validate::assert($_REQUEST, ['status' => 'required|in:active,pending']);
$pdo = Database::getInstance();
$stmt = $pdo->prepare("SELECT id, title, price, created_at FROM items WHERE status = ? ORDER BY id DESC LIMIT 50");
$stmt->execute([$clean['status']]);
$items = $stmt->fetchAll();
Response::json(['status' => 'success', 'count' => count($items), 'data' => $items]);
Distributed Redis Storage (`Core\Cache::db()`)
In multi-container or clustered deployments (`docker/prod/docker-compose.yml`), your MySQL instance is paired with a shared Redis instance (`6379`). You can use `Cache::db()` to cache SQL query results across all worker containers.
use Core\Cache;
use Core\Database;
use Core\Response;
// Cache exact SQL result in Redis for 10 minutes (600s)
$categories = Cache::db('all_active_categories', function() {
$pdo = Database::getInstance();
return $pdo->query("SELECT id, name, slug FROM categories WHERE is_active = 1")->fetchAll();
}, 600);
Response::json(['categories' => $categories]);Database Transactions (`Database::transaction()`)
`Core\Database` provides built-in transaction helpers. You can use the automatic `transaction()` closure block with auto-commit/rollback, or manual transaction controls:
use Core\Database;
use Core\Response;
// 🟢 Method 1: Automatic transaction closure block
Database::transaction(function () use ($senderId, $receiverId) {
Database::update('accounts', ['balance' => 'balance - 100'], 'id = ?', [$senderId]);
Database::update('accounts', ['balance' => 'balance + 100'], 'id = ?', [$receiverId]);
});
// 🟢 Method 2: Manual transaction control
try {
Database::beginTransaction();
$stmt = Database::query("UPDATE accounts SET balance = balance - ? WHERE id = ?", [100.00, $senderId]);
$stmt2 = Database::query("UPDATE accounts SET balance = balance + ? WHERE id = ?", [100.00, $receiverId]);
Database::commit();
Response::json(['status' => 'transfer_complete']);
} catch (\Throwable $e) {
Database::rollBack();
Response::json(['error' => 'Transfer failed: ' . $e->getMessage()], 500);
}API Models & Soft Deletes (`Models\BaseModel`)
All models in LilaPHP extend Models\BaseModel. They combine high-speed PDO interaction, automatic soft deletes (via deleted_at timestamp), automatic JSON serialization, input validation via Core\Validate, and schema definition for CLI migrations (php cli.php migrate).
1. Model Definition (`app/models/Product.php`)
<?php
namespace Models;
class Product extends BaseModel
{
protected string $table = 'products';
protected string $primaryKey = 'id';
protected bool $softDelete = true;
public ?int $id = null;
public string $name = '';
public float $price = 0.0;
public int $stock = 0;
public string $sku = '';
public ?string $created_at = null;
public ?string $updated_at = null;
public ?string $deleted_at = null;
// Validation rules evaluated by Core\Validate
protected array $rules = [
'name' => 'required|min_length:2|max_length:255',
'price' => 'required|numeric',
'stock' => 'numeric',
'sku' => 'max_length:50'
];
// Schema definition for CLI migrations (`php cli.php migrate`)
public static function getSchema(): array
{
return [
'id' => [
'type' => 'int',
'unsigned' => true,
'nullable' => false,
'autoIncrement' => true,
'primaryKey' => true
],
'name' => [
'type' => 'string',
'length' => 255,
'nullable' => false
],
'price' => [
'type' => 'decimal',
'length' => '10,2',
'default' => '0.00'
],
'stock' => [
'type' => 'int',
'default' => 0
],
'sku' => [
'type' => 'string',
'length' => 50,
'nullable' => true
],
'deleted_at' => [
'type' => 'timestamp',
'nullable' => true,
'default' => null
]
];
}
}2. Common Operations & Code Examples (`find`, `all`, `withTrashed`, `onlyTrashed`, `fill`, `save`, `delete`, `restore`, `forceDelete`)
use Models\Product;
// 🟢 Find record by primary key ID (excludes soft-deleted records by default)
$product = Product::find(1);
// 🟢 Retrieve all active records (excluding soft-deleted)
// Note: BaseModel automatically extracts ORDER BY, GROUP BY, and LIMIT from the $where string
// to properly append soft delete conditions without causing syntax errors.
$allProducts = Product::all("stock > ? ORDER BY price DESC LIMIT 50", [0]);
// 🟢 Query soft-deleted records
$withDeleted = Product::withTrashed("stock > ?", [0]);
$onlyDeleted = Product::onlyTrashed();
// 🟢 Create or Populate instance attributes via fill() or direct property assignment
$product = new Product();
$product->fill([
'name' => 'Wireless Keyboard',
'price' => 49.99,
'stock' => 100,
'sku' => 'KB-WL-01'
]);
$product->price = 45.00;
// 🟢 Validate model attributes against $rules array
$errors = $product->validate();
// 🟢 Assert valid & halt automatically (Emits HTTP 422 JSON response if rules fail)
$product->assertValid();
// 🟢 Save instance (Inserts if new record, Updates if primary key ID exists)
$product->save();
// 🟢 Soft Delete (sets deleted_at = NOW())
$product->delete();
// 🟢 Restore soft-deleted record (sets deleted_at = NULL)
$product->restore();
// 🟢 Permanently hard delete record
$product->forceDelete(); // or $product->delete(true);3. Smart URL Query Filtering, Pagination & Redis Caching (`BaseModel::paginate`)
BaseModel::paginate(array $customFilters = [], int $cacheTtl = 0) provides zero-boilerplate pagination with automatic URL query string filtering, total record counting, and optional Redis caching.
Supported URL Query String Parameters:
page: Target page number (default:1).per_page: Number of records per page (default:15, max limit:100).sort: Target column for ordering (default: primary keyid).order: Sort direction (ASCorDESC, default:DESC).start_date/created_at_from: Filters records created on or after date (YYYY-MM-DD).end_date/created_at_to: Filters records created on or before date (YYYY-MM-DD).
Example: Paginated Route (`app/routes/api/products.php`)
<?php
use Core\Request;
use Core\Response;
use Models\Product;
Request::GET(function () {
$result = Product::paginate(['status' => 'active']);
return [
'status' => 'success',
'data' => $result['data'],
'meta' => $result['meta']
];
});
Standard JSON Response Structure:
{
"status": "success",
"data": [
{ "id": 102, "name": "Wireless Mouse", "price": 29.99, "status": "active" },
{ "id": 101, "name": "Mechanical Keyboard", "price": 89.99, "status": "active" }
],
"meta": {
"total": 142,
"page": 1,
"per_page": 10,
"last_page": 15
}
}