README

Lite Eloquent is a lightweight, high-performance PHP database query builder and ORM-inspired library.
It provides an easy-to-use, fluent API for building SQL queries with support for joins, where clauses, pagination, and bindings—without the heavy dependencies.

✨ Features

🚀 High-performance PHP database query builder
📦 Lightweight and dependency-free (only requires PDO)
🔗 Fluent, chainable API similar to Eloquent
🔒 Secure with proper parameter bindings (SQL injection protection)
🗄️ Multi-dialect support (MySQL, Pgsql)
📄 Pagination support (simple and length-aware)
🔄 Transaction support
📊 Collection class with array-like operations
🎯 Type-safe with PHP 8.2+ features

Supported Operations

✅ SELECT queries with columns, joins, where clauses, ordering, limit
✅ INSERT queries (single and bulk)
✅ UPDATE queries with where clauses
✅ DELETE queries with where clauses
✅ UPSERT operations (MySQL: ON DUPLICATE KEY UPDATE, Pgsql: ON CONFLICT)
✅ INSERT OR IGNORE operations
✅ Complex WHERE conditions (nested groups, IN, BETWEEN, NULL checks, LIKE, etc.)
✅ JOIN clauses (INNER, LEFT, RIGHT, FULL)
✅ Raw SQL expressions
✅ Pagination (with total count or simple)
✅ Database transactions

📦 Installation

Install via Composer:

composer require craftix/lite-eloquent

Requirements:

PHP >= 8.2
PDO extension

🚀 Quick Start

Basic Setup

<?php

use Craftix\Eloquent\DB;
use PDO;

// Create a PDO connection
$pdo = new PDO(
    'mysql:host=localhost;dbname=db_name',
    'username',
    'password',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);

// Initialize DB instance
$db = new DB($pdo);

// Start querying!
$users = $db->table('users')->get();

Using Singleton Pattern

<?php

use Craftix\Eloquent\DatabaseManager;
use PDO;

// Initialize singleton
DatabaseManager::init(
    new PDO('mysql:host=localhost;dbname=db_name', 'username', 'password')
);

// Use static methods
$users = DatabaseManager::table('users')->get();

📖 Usage Examples

SELECT Queries

Basic Select

// Get all users
$users = $db->table('users')->get();

// Select specific columns
$users = $db->table('users')
    ->select('id', 'name', 'email')
    ->get();

// Get first record
$user = $db->table('users')->first();

// Get single column as array
$emails = $db->table('users')->pluck('email');

WHERE Clauses

// Simple where
$users = $db->table('users')
    ->where('status', '=', 'active')
    ->get();

// Where with default operator (=)
$users = $db->table('users')
    ->where('age', 25)
    ->get();

// Multiple conditions (AND)
$users = $db->table('users')
    ->where('status', 'active')
    ->where('age', '>', 18)
    ->get();

// OR conditions
$users = $db->table('users')
    ->where('status', 'active')
    ->orWhere('status', 'pending')
    ->get();

// WHERE IN
$users = $db->table('users')
    ->whereIn('id', [1, 2, 3, 4, 5])
    ->get();

// WHERE NOT IN
$users = $db->table('users')
    ->whereNotIn('status', ['banned', 'deleted'])
    ->get();

// WHERE BETWEEN
$users = $db->table('users')
    ->whereBetween('age', 18, 65)
    ->get();

// WHERE NULL / NOT NULL
$users = $db->table('users')
    ->whereNull('deleted_at')
    ->get();

$users = $db->table('users')
    ->whereNotNull('email_verified_at')
    ->get();

// WHERE LIKE
$users = $db->table('users')
    ->whereLike('name', '%john%')
    ->get();

// WHERE NOT LIKE
$users = $db->table('users')
    ->whereNotLike('email', '%@spam.com')
    ->get();

// Nested WHERE groups
$users = $db->table('users')
    ->where('status', 'active')
    ->where(function($query) {
        $query->where('age', '>', 18)
              ->orWhere('verified', true);
    })
    ->get();

JOIN Clauses

// INNER JOIN
$users = $db->table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select('users.*', 'profiles.bio')
    ->get();

// LEFT JOIN
$users = $db->table('users')
    ->leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

// RIGHT JOIN
$users = $db->table('users')
    ->rightJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

// Complex JOIN with conditions
$users = $db->table('users')
    ->join('orders', function($join) {
        $join->on('users.id', '=', 'orders.user_id')
             ->where('orders.status', '=', 'completed');
    })
    ->get();

// Multiple JOINs
$orders = $db->table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->join('products', 'orders.product_id', '=', 'products.id')
    ->select('orders.*', 'users.name', 'products.title')
    ->get();

Ordering and Limits

// ORDER BY
$users = $db->table('users')
    ->orderBy('created_at', 'DESC')
    ->get();

// Multiple ORDER BY
$users = $db->table('users')
    ->orderBy('status', 'ASC')
    ->orderBy('name', 'ASC')
    ->get();

// ORDER BY DESC (shortcut)
$users = $db->table('users')
    ->orderByDesc('created_at')
    ->get();

// Random order
$users = $db->table('users')
    ->inRandomOrder()
    ->get();

// LIMIT
$users = $db->table('users')
    ->limit(10)
    ->get();

// OFFSET
$users = $db->table('users')
    ->offset(20)
    ->limit(10)
    ->get();

// DISTINCT
$emails = $db->table('users')
    ->distinct()
    ->select('email')
    ->get();

Pagination

// Length-aware pagination (with total count)
$paginator = $db->table('users')
    ->paginate($page = 1, $perPage = 15);

// Access pagination data
$users = $paginator->items(); // Array of users
$total = $paginator->total(); // Total count
$currentPage = $paginator->currentPage();
$perPage = $paginator->perPage();
$totalPages = $paginator->totalPages();
$hasMore = $paginator->hasMorePages();

// Pagination info
$info = $paginator->getPaginationInfo();
// Returns: ['total', 'perPage', 'from', 'to', 'pages', 'currentPage', 'previousPage', 'nextPage']

// Simple pagination (no total count, faster)
$paginator = $db->table('users')
    ->simplePaginate($page = 1, $perPage = 15);

$hasMore = $paginator->hasMorePages();

INSERT Queries

// Single insert
$affected = $db->table('users')
    ->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'age' => 30
    ]);

// Bulk insert
$affected = $db->table('users')
    ->insert([
        ['name' => 'John', 'email' => 'john@example.com'],
        ['name' => 'Jane', 'email' => 'jane@example.com'],
        ['name' => 'Bob', 'email' => 'bob@example.com']
    ]);

// Insert and get ID
$id = $db->table('users')
    ->insertGetId([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ], 'id'); // Second parameter is the ID column name (default: 'id')

// Insert or ignore (Pgsql: ON CONFLICT DO NOTHING, MySQL: INSERT IGNORE)
$affected = $db->table('users')
    ->insertOrIgnore([
        'email' => 'john@example.com',
        'name' => 'John Doe'
    ], ['email']); // Unique columns for conflict detection

UPSERT Queries

// MySQL: ON DUPLICATE KEY UPDATE
// Pgsql: ON CONFLICT DO UPDATE

// Upsert with auto-update of all non-unique columns
$affected = $db->table('users')
    ->upsert(
        ['email' => 'john@example.com', 'name' => 'John Doe', 'age' => 30],
        ['email'], // Unique columns
        null // Update all non-unique columns
    );

// Upsert with specific columns to update
$affected = $db->table('users')
    ->upsert(
        ['email' => 'john@example.com', 'name' => 'John Doe', 'age' => 30],
        ['email'], // Unique columns
        ['name', 'age'] // Columns to update on conflict
    );

// Bulk upsert
$affected = $db->table('users')
    ->upsert(
        [
            ['email' => 'john@example.com', 'name' => 'John'],
            ['email' => 'jane@example.com', 'name' => 'Jane']
        ],
        ['email'],
        ['name']
    );

UPDATE Queries

// Update with WHERE clause
$affected = $db->table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Updated',
        'email' => 'john.updated@example.com'
    ]);

// Update multiple rows
$affected = $db->table('users')
    ->where('status', 'pending')
    ->update(['status' => 'active']);

// Update with JOIN
$affected = $db->table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->where('profiles.verified', true)
    ->update(['users.status' => 'verified']);

// Get generated SQL (for debugging)
$sql = null;
$affected = $db->table('users')
    ->where('id', 1)
    ->update(['name' => 'John'], $sql);
echo $sql; // Outputs the generated SQL

DELETE Queries

// Delete with WHERE clause
$affected = $db->table('users')
    ->where('status', 'deleted')
    ->delete();

// Delete with multiple conditions
$affected = $db->table('users')
    ->where('status', 'inactive')
    ->where('last_login', '<', '2024-01-01')
    ->delete();

// Delete with JOIN
$affected = $db->table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->where('profiles.banned', true)
    ->delete();

// Get generated SQL
$sql = null;
$affected = $db->table('users')
    ->where('id', 1)
    ->delete($sql);

Raw Expressions

use Craftix\Eloquent\Grammar\Expression;

// Using raw expressions in queries
$users = $db->table('users')
    ->select($db->raw('COUNT(*) as total'))
    ->get();

// Raw expressions in WHERE
$users = $db->table('users')
    ->where($db->raw('DATE(created_at)'), '=', '2024-01-01')
    ->get();

// Raw expressions in JOIN
$users = $db->table('users')
    ->join('profiles', $db->raw('users.id'), '=', $db->raw('profiles.user_id'))
    ->get();

// Or use Expression::make()
$expr = Expression::make('NOW()');
$users = $db->table('users')
    ->where('created_at', '>', $expr)
    ->get();

Transactions

// Using DB instance
$db->beginTransaction();
try {
    $db->table('users')->insert(['name' => 'John']);
    $db->table('profiles')->insert(['user_id' => 1, 'bio' => 'Bio']);
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
    throw $e;
}

// Using transaction closure
$db->transaction(function() use ($db) {
    $db->table('users')->insert(['name' => 'John']);
    $db->table('profiles')->insert(['user_id' => 1, 'bio' => 'Bio']);
});

// Check if in transaction
if ($db->inTransaction()) {
    // Currently in a transaction
}

// Using singleton
DatabaseManager::beginTransaction();
try {
    DatabaseManager::table('users')->insert(['name' => 'John']);
    DatabaseManager::commit();
} catch (Exception $e) {
    DatabaseManager::rollBack();
}

// Or with closure
DatabaseManager::transaction(function() {
    DatabaseManager::table('users')->insert(['name' => 'John']);
});

Working with Collections

All query results return a Collection instance with helpful methods:

$users = $db->table('users')->get();

// Collection methods
$users->count(); // Get count
$users->isEmpty(); // Check if empty
$users->first(); // Get first item
$users->last(); // Get last item
$users->pluck('email'); // Get array of email values
$users->plucked('email'); // Get new collection with emails

// Filtering and mapping
$users->filter(fn($user) => $user['age'] > 18);
$users->map(fn($user) => strtoupper($user['name']));

// Searching
$user = $users->find(fn($user) => $user['id'] === 1);
$exists = $users->contains(['id' => 1]);

// Array access
$firstUser = $users[0];
$users[] = ['id' => 100, 'name' => 'New User'];

// Convert to array/JSON
$array = $users->toArray();
$json = $users->toJson();

Custom Object Conversion

// Convert results to custom objects
$users = $db->table('users')
    ->objectConverter(function($row) {
        return new User($row);
    })
    ->get();

// Now $users contains User objects instead of arrays
foreach ($users as $user) {
    echo $user->getName();
}

Getting SQL Query

// Get the SQL query without executing
$sql = $db->table('users')
    ->where('status', 'active')
    ->toSql();

echo $sql; // SELECT * FROM `users` WHERE `status` = :v1;

Database Dialects

The library automatically detects the database dialect, but you can also set it manually:

use Craftix\Eloquent\Grammar\Dialects\MySqlDialect;
use Craftix\Eloquent\Grammar\Dialects\PostgresDialect;

// Set MySQL dialect
$db->setDialect(new MySqlDialect());

// Set Pgsql dialect
$db->setDialect(new PostgresDialect());

🎯 API Reference

QueryBuilder Methods

Query Execution

get() - Execute SELECT and return Collection
first(...$columns) - Get first record (returns single value if one column, array if multiple)
pluck($column) - Get single column as array
toSql() - Get SQL string without executing
paginate($page, $perPage, $pageName = 'page') - Length-aware pagination
simplePaginate($page, $perPage, $pageName = 'page') - Simple pagination

Query Building

table($table) - Set table name
select(...$columns) - Select columns
distinct() - Add DISTINCT
where($column, $operator, $value, $and = true) - Add WHERE condition
orWhere($column, $operator, $value) - Add OR WHERE condition
whereIn($column, $values, $and = true) - WHERE IN
whereNotIn($column, $values, $and = true) - WHERE NOT IN
whereBetween($column, $value1, $value2, $and = true) - WHERE BETWEEN
whereNotBetween($column, $value1, $value2, $and = true) - WHERE NOT BETWEEN
whereNull($column, $and = true) - WHERE IS NULL
whereNotNull($column, $and = true) - WHERE IS NOT NULL
whereLike($column, $value, $and = true) - WHERE LIKE
whereNotLike($column, $value, $and = true) - WHERE NOT LIKE
join($table, $column1, $operator, $column2, $type = 'INNER') - Add JOIN
leftJoin($table, $column1, $operator, $column2) - LEFT JOIN
rightJoin($table, $column1, $operator, $column2) - RIGHT JOIN
fullJoin($table, $column1, $operator, $column2) - FULL JOIN
orderBy($column, $type = 'ASC') - ORDER BY
orderByDesc($column) - ORDER BY DESC
inRandomOrder($seed = '') - Random order
limit($limit) - LIMIT
offset($offset) - OFFSET

Write Operations

insert($columnsToValues, &$resultedSql = null) - INSERT
insertGetId($columnsToValues, $idColumn = 'id', &$resultedSql = null) - INSERT and get ID
insertOrIgnore($columnsToValues, $uniqueColumns = null, &$resultedSql = null) - INSERT OR IGNORE
upsert($columnsToValues, $uniqueBy, $updateOnDuplicate = null, &$resultedSql = null) - UPSERT
update($columnsToValues, &$resultedSql = null) - UPDATE
delete(&$resultedSql = null) - DELETE

Utilities

raw($value) - Create raw SQL expression
getValues() - Get bound parameter values
objectConverter($callback) - Set result object converter
setDialect($dialect) - Set SQL dialect

DB Class Methods

table($table) - Create QueryBuilder instance
setDialect($dialect) - Set dialect
getPdo() - Get PDO instance
beginTransaction() - Start transaction
commit() - Commit transaction
rollBack() - Rollback transaction
inTransaction() - Check if in transaction
transaction($callback) - Execute in transaction
lastInsertId() - Get last insert ID
exec($query, $params = null) - Execute raw SQL
raw($value) - Create raw expression

Collection Methods

See the Collection documentation section above for available methods.

🔒 Security

This library uses prepared statements with parameter binding by default, protecting against SQL injection attacks. Always use the query builder methods instead of raw SQL when possible.

// ✅ Safe - Uses parameter binding
$users = $db->table('users')
    ->where('email', $userEmail)
    ->get();

// ⚠️ Only use raw expressions when absolutely necessary
$users = $db->table('users')
    ->where($db->raw('email'), '=', $userEmail) // Still safe due to binding
    ->get();

🐛 Error Handling

The library throws QueryBuilderException for query-related errors:

use Craftix\Eloquent\Data\QueryBuilderException;

try {
    $users = $db->table('users')->get();
} catch (QueryBuilderException $e) {
    $errorCode = $e->getCode();
    $message = $e->getMessage();
    
    // Error codes:
    // 0 - Database execution error
    // 1000 - Invalid order type
    // 1001 - Invalid join type
    // 2000 - Missing table
    // 2001 - Missing columns
    // 2002 - Dangerous query (UPDATE/DELETE without WHERE)
    // 2004 - Invalid query
}

📝 License

This project is licensed under the MIT License.

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📧 Support

For issues, questions, or contributions, please visit the GitHub repository.

📜 Changelog

[1.1.0] - 2025-12-02

Improved unit tests
Added documentation for class methods and properties

[1.0.0] - 2025-11-15

Initial release with core query builder and collection methods

craftix / lite-eloquent

Maintainers

Details