mathsgod/light-db

Light-DB ORM

1.8.0 2026-06-08 04:59 UTC

Requires

• php: >=8.2
• ext-pdo: *
• illuminate/collections: ^11.0 || ^12.0
• laminas/laminas-db: ^2.20
• laminas/laminas-paginator: ^2.20
• vlucas/phpdotenv: ^5.6

Requires (Dev)

• phpstan/phpstan: ^2.0
• phpunit/phpunit: ^10.5

MIT

• Raymond Chong

README

Light-DB

Light-DB is a lightweight PHP ORM/database access layer built on top of Laminas DB, designed for modern PHP 8.2+ applications. It provides an Eloquent-like Active Record experience with support for auto-mapping, dynamic queries, relationship queries, JSON field operations, and pagination — with first-class compatibility for both MySQL 8.0 and MariaDB 10.11 / 11.4.

✨ Features

• 🚀 Modern PHP: Built on PHP 8.2+ features with type declarations and modern PHP syntax
• 🔗 Multi-Database Support: Based on Laminas DB — supports MySQL 8.0, MariaDB 10.11/11.4, PostgreSQL, SQLite, SQL Server
• 🧩 MariaDB-Aware: Detects MariaDB at runtime and adjusts column metadata (JSON detection, default-value parsing) automatically
• 📦 Eloquent-Style: Familiar Active Record pattern with an Eloquent-like API powered by illuminate/collections
• 🎯 Smart Queries: Complex conditional queries, sorting, grouping, and aggregation functions
• 📄 Pagination Support: Built-in Laminas Paginator integration
• 🔄 JSON Fields: Native JSON field operations with automatic serialization/deserialization
• 🔗 Relationship Queries: Inter-model relationship queries and dynamic property access
• ✅ CI-Tested: Automated test matrix on GitHub Actions across PHP 8.2 / 8.3 / 8.4 / 8.5 × MySQL 8.0 / MariaDB 10.11 / MariaDB 11.4

📋 Requirements

• PHP 8.2 or higher
• PDO extension
• A supported database (tested against):
  • MySQL 8.0
  • MariaDB 10.11 / 11.4

🚀 Installation

Install via Composer:

composer require mathsgod/light-db

⚙️ Configuration

Create a .env file in your project root:

DATABASE_DRIVER=Pdo_Mysql
DATABASE_HOSTNAME=localhost
DATABASE_PORT=3306
DATABASE_DATABASE=your_database
DATABASE_USERNAME=your_username
DATABASE_PASSWORD=your_password
DATABASE_CHARSET=utf8mb4

🎯 Basic Usage

Defining Models

<?php

use Light\Db\Model;

class User extends Model
{
    // Uses class name as table name by default
    // Customize with: protected static $_table = 'custom_table_name';
}

class Post extends Model
{
    protected static $_table = 'posts';
}

CRUD Operations

Creating Records

// Method 1: Create + Save
$user = User::Create([
    'name' => 'Raymond Chong',
    'email' => '[e-mail]',
    'age' => 30
]);
$user->save();

// Method 2: Direct property assignment
$user = User::Create();
$user->name = 'John Doe';
$user->email = '[e-mail]';
$user->save();

Querying Records

// Query by primary key
$user = User::Get(1);

// Basic queries
$users = User::Query(['status' => 'active'])->toArray();

// Complex queries
$activeUsers = User::Query()
    ->filters([
        'age' => ['gte' => 18, 'lte' => 65],
        'status' => ['in' => ['active', 'premium']],
        'email' => ['contains' => '@gmail.com']
    ])
    ->sort('created_at:desc')
    ->toArray();

// Get first record
$firstUser = User::Query(['status' => 'active'])->first();

Updating Records

// Single record update
$user = User::Get(1);
$user->name = 'Updated Name';
$user->email = '[e-mail]';
$user->save();

// Batch update
$affected = User::Query(['status' => 'inactive'])
    ->update(['status' => 'archived']);

Deleting Records

// Single record deletion
$user = User::Get(1);
$user->delete();

// Batch deletion
$deleted = User::Query(['status' => 'spam'])
    ->delete();

🔍 Advanced Queries

Query Conditions

$query = User::Query()->filters([
    'age' => ['eq' => 25],           // Equal to
    'score' => ['gt' => 80],         // Greater than
    'salary' => ['gte' => 50000],    // Greater than or equal
    'rating' => ['lt' => 5],         // Less than
    'points' => ['lte' => 100],      // Less than or equal
    'status' => ['in' => ['active', 'premium']], // In array
    'name' => ['contains' => 'john'],  // Contains pattern
    'category' => ['ne' => 'spam']   // Not equal
]);

Sorting and Limiting

$users = User::Query()
    ->sort('created_at:desc,name:asc')  // Multi-field sorting
    ->limit(10)                         // Limit results
    ->offset(20)                        // Offset
    ->toArray();

OR / AND Logic with filters()

Use the _or and _and magic keys inside filters() to build boolean logic.

use Light\Db\Model;

class User extends Model {}

// WHERE (age >= 18 OR name = 'Peter')
$users = User::Query()->filters([
    '_or' => [
        ['age' => ['gte' => 18]],
        ['name' => 'Peter']
    ]
])->toArray();

Mixing outer where() with inner _or / _and:

// WHERE status = 'active' AND (age >= 30 OR name = 'Test User 1')
$users = User::Query()
    ->where(['status' => 'active'])
    ->filters([
        '_or' => [
            ['age' => ['gte' => 30]],
            ['name' => 'Test User 1']
        ]
    ])
    ->toArray();

Nested _or and _and for deeper boolean trees:

// WHERE (age > 28 AND status = 'active')
//    OR (name = 'Test User 3' AND status = 'inactive')
$users = User::Query()->filters([
    '_or' => [
        [
            '_and' => [
                ['age' => ['gt' => 28]],
                ['status' => 'active']
            ]
        ],
        [
            '_and' => [
                ['name' => 'Test User 3'],
                ['status' => 'inactive']
            ]
        ]
    ]
])->toArray();

Alternatively, the underlying Laminas\Db\Sql\Select::where() accepts a combination operator:

use Laminas\Db\Sql\Predicate\PredicateSet;

// WHERE (age >= 30 OR name = 'Test User 1')
$users = User::Query()
    ->where(['age >= ?' => 30], PredicateSet::OP_OR)
    ->where(['name = ?' => 'Test User 1'], PredicateSet::OP_OR)
    ->toArray();

Tip: filters(['_or' => ...]) is generally easier to read and supports arbitrary nesting. Use the direct where(..., OP_OR) form when you need fine-grained control over a single predicate.

Aggregate Functions

$userCount = User::Query()->count();
$avgAge = User::Query()->avg('age');
$totalSalary = User::Query()->sum('salary');
$minAge = User::Query()->min('age');
$maxAge = User::Query()->max('age');

📄 Pagination

$query = User::Query(['status' => 'active']);
$paginator = $query->getPaginator();

// Set items per page
$paginator->setItemCountPerPage(20);
$paginator->setCurrentPageNumber(1);

// Get current page data
$currentItems = $paginator->getCurrentItems();
$totalItems = $paginator->getTotalItemCount();
$totalPages = $paginator->getPages()->pageCount;

🔄 JSON Field Operations

// Create record with JSON data
$user = User::Create([
    'name' => 'John',
    'profile' => [
        'avatar' => 'avatar.jpg',
        'settings' => [
            'theme' => 'dark',
            'notifications' => true
        ],
        'tags' => ['developer', 'php', 'mysql']
    ]
]);
$user->save();

// Read JSON data
$user = User::Get(1);
echo $user->profile['settings']['theme']; // 'dark'

// Update JSON data
$user->profile['settings']['theme'] = 'light';
$user->profile['tags'][] = 'javascript';
$user->save();

Note: MariaDB stores JSON columns as LONGTEXT with a JSON_VALID() CHECK constraint. Light-DB detects this at runtime and treats them as json data type for transparent encoding/decoding.

🔗 Relationship Queries

// Assuming UserList model with user_id column
$user = User::Get(1);

// Get related UserList query object
$userLists = $user->UserList;  // Returns Query object

// Further querying
$activeLists = $user->UserList
    ->filters(['status' => 'active'])
    ->sort('created_at:desc')
    ->toArray();

🛠️ Advanced Features

Collection Operations

$users = User::Query(['status' => 'active']);

// Map operation
$names = $users->map(fn($user) => $user->name)->toArray();

// Filter operation
$premiumUsers = $users->filter(fn($user) => $user->type === 'premium');

// Method chaining
$emailList = User::Query()
    ->filters(['status' => 'active'])
    ->map(fn($user) => $user->email)
    ->toArray();

Custom Sorting

// Register custom sorting logic
User::RegisterOrder('popular', function($query) {
    return $query->order(['score DESC', 'views DESC']);
});

// Use custom sorting
$popularUsers = User::Query()->sort('popular')->toArray();

Binding Input Parameters to Prepared Statements

Both cursor() and execute() accept an optional array $input_parameters = [] argument. These parameters are bound to the underlying Laminas\Db\Adapter\ParameterContainer and matched against the ? placeholders that Light-DB/Laminas auto-generates for where conditions. Both methods iterate the same way and return hydrated model instances.

// cursor() returns a Laravel LazyCollection — perfect for streaming large result sets
foreach (User::Query()->cursor() as $user) {
    echo $user->name;
}

// execute() returns a regular Collection eagerly materialized
$users = User::Query()->execute()->toArray();

input_parameters is a thin pass-through to the prepared statement. Use it when you want to be explicit about what gets bound:

// The framework already binds values from where() conditions automatically,
// so this is normally a no-op. It exists for symmetry and advanced cases
// (e.g. when the Select contains Expression objects with named placeholders).
User::Query(['status' => 'active'])->execute([
    'minAge' => 18,
])->toArray();

Note: If you pass keys that don't match any ? token in the query, the database driver will reject the statement with HY093 (PDO: "number of bound variables does not match number of tokens"). The placeholder count is determined by your where/filter/join expressions — Light-DB does not invent extra ? tokens for unused parameters.

🗄️ Database Compatibility

Database	Version	Status
MySQL	8.0	✅ Fully supported
MariaDB	10.11	✅ Fully supported
MariaDB	11.4	✅ Fully supported

Light-DB automatically detects MariaDB at connection time by inspecting SELECT VERSION(). Internally, this enables:

• JSON column detection — MariaDB reports json columns as longtext in INFORMATION_SCHEMA; Light-DB queries CHECK_CONSTRAINTS for json_valid() clauses to recover the real type
• Default-value parsing — MariaDB quotes string defaults (e.g. 'foo') while MySQL 8.0 does not; Light-DB normalizes both formats
• Connection setup — utf8mb4_0900_ai_ci collation is only set on MySQL 8.x

🧪 Running Tests

A .env file with valid database credentials is required (see Configuration).

# Run all tests
composer test

# Run basic functionality tests
composer test-basic

# Run CRUD operation tests
composer test-crud

# Run JSON field tests
composer test-json

# Run unit tests only
composer test-unit

# Run integration tests only
composer test-integration

✅ Continuous Integration

Tests run automatically on every push and pull request via GitHub Actions. The CI matrix covers:

• PHP: 8.2, 8.3, 8.4, 8.5
• Database: MySQL 8.0, MariaDB 10.11, MariaDB 11.4

That gives 12 parallel jobs ensuring compatibility across the supported matrix.

📊 Test Coverage

• 96 test cases
• 380 assertions
• Covers all core functionality
• Includes unit and integration tests
• Supports error handling and edge case testing

📦 Dependencies

Package	Version	Purpose
laminas/laminas-db	^2.20	Database abstraction layer
laminas/laminas-paginator	^2.20	Pagination support
illuminate/collections	^11.0 || ^12.0	Eloquent-style collections
vlucas/phpdotenv	^5.6	Environment variable loading

📝 License

This project is licensed under the MIT License.

👨‍💻 Author

Raymond Chong

• Email: [e-mail]
• GitHub: @mathsgod

🤝 Contributing

Issues and Pull Requests are welcome!

1. Fork the project
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📚 More Examples

Check the test files in the tests/ directory for more usage examples and best practices.