PHPackages                             ocallit/sqler - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. ocallit/sqler

ActiveLibrary[Database & ORM](/categories/database)

ocallit/sqler
=============

A php sql builder, helpers and mysqli wrapper

1.15.0(3mo ago)0461MITPHPPHP ^8.2

Since Jan 20Pushed 2mo ago1 watchersCompare

[ Source](https://github.com/ocallit/Sqler)[ Packagist](https://packagist.org/packages/ocallit/sqler)[ RSS](/packages/ocallit-sqler/feed)WikiDiscussions main Synced 1mo ago

READMEChangelogDependenciesVersions (18)Used By (1)

Sqler - PHP Database Access Library
===================================

[](#sqler---php-database-access-library)

- A php sql builder, database & query metadata, auditor, helpers and mysqli wrapper.
- A powerful, feature-rich PHP database access layer built on MySQLi, providing intelligent query building, comprehensive error handling, database metadata introspection, and complete audit trail functionality.

Key Features
------------

[](#key-features)

- **🔄 Intelligent Error Recovery** - Automatic retry on deadlocks, timeouts, and connection issues
- **🛡️ Robust Error Handling** - Comprehensive exception handling with specific error type detection
- **📊 Multiple Result Formats** - Get data in the shape you need: single values, rows, arrays, key-value pairs
- **🔍 Database Introspection** - Complete metadata access for tables, columns, relationships
- **📝 Audit Trail System** - Full change tracking with diff analysis and user attribution
- **⚡ Smart Query Building** - Parameterized queries with MySQL function recognition
- **🔒 Transaction Management** - Nested transaction support with automatic cleanup
- **📋 Query & Error Logging** - Comprehensive logging for debugging and monitoring

AI Assistant Documentation
--------------------------

[](#ai-assistant-documentation)

**For AI assistants and comprehensive API reference**: See [Sqler\_AI\_DOCUMENTATION.md](Sqler_AI_DOCUMENTATION.md) - This contains detailed method signatures, return types, usage patterns, and examples specifically formatted for AI assistance.

Installation & Quick Start
------------------------------

[](#installation--quick-start)

### Requirements

[](#requirements)

- PHP 8.2+
- MySQLi extension
- MySQL 5.7+ / MariaDB 10.2+

### Installation via Composer

[](#installation-via-composer)

```
composer require ocallit/sqler
```

### Basic Usage

[](#basic-usage)

```
use Ocallit\Sqler\SqlExecutor;
use Ocallit\Sqler\QueryBuilder;
use Ocallit\Sqler\DatabaseMetadata;
use Ocallit\Sqler\Historian;

// Initialize database connection
$sql = new SqlExecutor([
    'hostname' => 'localhost',
    'username' => 'user',
    'password' => 'password',
    'database' => 'mydb'
]);

// Execute queries in different result formats
$userCount = $sql->firstValue("SELECT COUNT(*) FROM users WHERE active = ?", [1]);
$user = $sql->row("SELECT * FROM users WHERE id = ?", [123]);
$users = $sql->array("SELECT * FROM users WHERE department = ?", ['IT']);
$usersByDept = $sql->arrayKeyed("SELECT * FROM users", 'department');

// Build queries safely
$qb = new QueryBuilder();
$insert = $qb->insert('users', [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'created_at' => 'NOW()',  // MySQL functions recognized
    'active' => 1
], true); // Enable ON DUPLICATE KEY UPDATE

$sql->query($insert['query'], $insert['parameters']);
```

Core Components
---------------

[](#core-components)

### SqlExecutor - Database Query Engine

[](#sqlexecutor---database-query-engine)

The heart of the library, providing multiple ways to execute and retrieve data:

```
// Different result shapes for different needs
$count = $sql->firstValue("SELECT COUNT(*) FROM orders");           // Single value
$order = $sql->row("SELECT * FROM orders WHERE id = ?", [123]);    // Single row
$orders = $sql->array("SELECT * FROM orders WHERE status = ?", ['pending']); // All rows
$ordersByStatus = $sql->arrayKeyed("SELECT * FROM orders", 'status'); // Keyed by column
$statusList = $sql->vector("SELECT DISTINCT status FROM orders");    // Single column values
$statusCounts = $sql->keyValue("SELECT status, COUNT(*) FROM orders GROUP BY status"); // Key-value pairs
```

**Error Handling with Smart Recovery:**

```
try {
    $sql->query("UPDATE inventory SET quantity = quantity - ? WHERE product_id = ?", [5, 123]);
} catch (Exception $e) {
    if ($sql->is_last_error_duplicate_key()) {
        // Handle unique constraint violation
    } elseif ($sql->is_last_error_table_not_found()) {
        // Handle missing table
    }
    // Other specific error checks available
}
```

### QueryBuilder - Safe Query Construction

[](#querybuilder---safe-query-construction)

Build parameterized queries with automatic escaping and MySQL function recognition:

```
$qb = new QueryBuilder();

// INSERT with ON DUPLICATE KEY UPDATE
$insert = $qb->insert('products', [
    'name' => 'Widget',
    'price' => 29.99,
    'updated_at' => 'NOW()',
    'stock' => 100
], true, ['created_at']); // Update on duplicate, but don't touch created_at

// UPDATE with WHERE conditions
$update = $qb->update('products',
    ['price' => 34.99, 'updated_at' => 'NOW()'],
    ['category' => 'electronics', 'active' => 1]
);

// Complex WHERE clauses
$where = $qb->where([
    'category' => ['electronics', 'gadgets'],  // IN clause
    'price' => 50,                             // Exact match
    'created_date' => 'CURDATE()',             // MySQL function
    'active' => 1
], 'AND');

$sql->query($update['query'], $update['parameters']);
```

### DatabaseMetadata - Schema Introspection

[](#databasemetadata---schema-introspection)

Get complete information about your database structure:

```
// Initialize once
DatabaseMetadata::initialize($sql);
$meta = DatabaseMetadata::getInstance();

// Get table structure
$userColumns = $meta->table('users');
foreach ($userColumns as $column) {
    echo "{$column['Field']} - {$column['Type']} - {$column['Key']}\n";
}

// Get relationships
$primaryKeys = $meta->primaryKeys();
$foreignKeys = $meta->getForeignKeys('orders');
$allRelationships = $meta->foreignKeysAll();

// Analyze query results
$queryMeta = $meta->query("SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id");
// Returns detailed info about each column including source table and type
```

### Historian - Complete Audit Trail

[](#historian---complete-audit-trail)

Track all changes with automatic diff analysis:

```
$historian = new Historian($sql, 'users', ['id']); // Track 'users' table, primary key is 'id'

// Track an INSERT
$sql->query("INSERT INTO users (name, email) VALUES (?, ?)", ['Alice', 'alice@example.com']);
$newUser = $sql->row("SELECT * FROM users WHERE id = ?", [$sql->last_insert_id()]);
$historian->register('insert', ['id' => $newUser['id']], $newUser, 'admin', 'New user registration');

// Track an UPDATE
$sql->query("UPDATE users SET email = ? WHERE id = ?", ['alice.smith@example.com', 123]);
$updatedUser = $sql->row("SELECT * FROM users WHERE id = ?", [123]);
$historian->register('update', ['id' => 123], $updatedUser, 'admin', 'Email change request');

// Track a DELETE
$userToDelete = $sql->row("SELECT * FROM users WHERE id = ?", [123]);
$sql->query("DELETE FROM users WHERE id = ?", [123]);
$historian->register('delete', ['id' => 123], $userToDelete, 'admin', 'Account deactivation');

// Get change history with automatic diff analysis
$changes = $historian->getChanges(['id' => 123]);
foreach ($changes as $change) {
    echo "Action: {$change['action']} by {$change['user_nick']} on {$change['date']}\n";
    foreach ($change['diff'] as $field => $diff) {
        echo "  {$field}: '{$diff['before']}' → '{$diff['after']}'\n";
    }
}
```

Advanced Features
-----------------

[](#advanced-features)

### Transaction Management

[](#transaction-management)

```
// Automatic transaction with retry
$sql->transaction([
    "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
    "UPDATE accounts SET balance = balance + 100 WHERE id = 2",
    "INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100)"
], 'Money transfer');

// Manual transaction control
$sql->begin('Complex operation');
try {
    $sql->query("UPDATE inventory SET quantity = quantity - ? WHERE id = ?", [5, 123]);
    $sql->query("INSERT INTO orders (product_id, quantity) VALUES (?, ?)", [123, 5]);
    $sql->commit('Complex operation');
} catch (Exception $e) {
    $sql->rollback('Complex operation');
    throw $e;
}
```

### Error Recovery & Logging

[](#error-recovery--logging)

```
// The library automatically retries on:
// - Deadlocks (1213)
// - Lock wait timeouts (1205)
// - Connection lost errors (2006, 2013)
// - Other transient errors

// Access logs for debugging
$queryLog = $sql->getLog();        // All executed queries
$errorLog = $sql->getErrorLog();   // All errors with retry attempts

// Specific error type checking
if ($sql->is_last_error_foreign_key_violation()) {
    echo "Referenced record doesn't exist";
} elseif ($sql->is_last_error_child_records_exist()) {
    echo "Cannot delete: child records exist";
}
```

Configuration Options
---------------------

[](#configuration-options)

### SqlExecutor Configuration

[](#sqlexecutor-configuration)

```
$sql = new SqlExecutor(
    connect: [
        'hostname' => 'localhost',
        'username' => 'user',
        'password' => 'pass',
        'database' => 'mydb',
        'port' => 3306,
        'socket' => null,
        'flags' => 0
    ],
    connect_options: [
        MYSQLI_INIT_COMMAND => 'SET AUTOCOMMIT = 1',
        MYSQLI_OPT_CONNECT_TIMEOUT => 10
    ],
    charset: 'utf8mb4',
    coalition: 'utf8mb4_0900_ai_ci'
);
```

### QueryBuilder Options

[](#querybuilder-options)

```
$qb = new QueryBuilder(
    useNewOnDuplicate: true  // Use MySQL 8.0.19+ syntax for ON DUPLICATE KEY UPDATE
);
```

Best Practices
--------------

[](#best-practices)

1. **Always use parameterized queries** - The library handles this automatically
2. **Initialize DatabaseMetadata once** at application startup
3. **Use appropriate result methods** based on expected data shape
4. **Implement proper error handling** with specific error type checking
5. **Close connections properly** in finally blocks
6. **Use transactions for multi-query operations**
7. **Leverage audit trails** for compliance and debugging

Error Handling Reference
------------------------

[](#error-handling-reference)

The library provides specific error detection methods:

- `is_last_error_table_not_found()` - Missing tables
- `is_last_error_duplicate_key()` - Unique constraint violations
- `is_last_error_foreign_key_violation()` - Invalid foreign key references
- `is_last_error_child_records_exist()` - Cannot delete parent with children
- `is_last_error_column_not_found()` - Invalid column names

License
-------

[](#license)

MIT License - see LICENSE file for details.

Contributing
------------

[](#contributing)

Contributions are welcome! Please ensure:

- PHP 8.2+ compatibility
- Comprehensive error handling
- Unit tests for new features
- Documentation updates

Support
-------

[](#support)

For issues, feature requests, or questions, please use the GitHub issue tracker.

###  Health Score

42

—

FairBetter than 90% of packages

Maintenance83

Actively maintained with recent releases

Popularity8

Limited adoption so far

Community9

Small or concentrated contributor base

Maturity59

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~24 days

Recently: every ~57 days

Total

16

Last Release

111d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/3a34af07568b6b9357495f80448b3d552289d3a6dffbac5ffb4e6c04cb16843a?d=identicon)[rauljose](/maintainers/rauljose)

---

Top Contributors

[![rauljose](https://avatars.githubusercontent.com/u/5035806?v=4)](https://github.com/rauljose "rauljose (12 commits)")

### Embed Badge

![Health badge](/badges/ocallit-sqler/health.svg)

```
[![Health](https://phpackages.com/badges/ocallit-sqler/health.svg)](https://phpackages.com/packages/ocallit-sqler)
```

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[mongodb/mongodb

MongoDB driver library

1.6k64.0M546](/packages/mongodb-mongodb)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90340.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)