Download the PHP package marwen-brini/bob-the-builder without Composer

Bob Query Builder

A highly optimized, standalone PHP query builder with Laravel-like fluent syntax. Originally designed to enhance Quantum ORM's query building capabilities, Bob Query Builder is a fully independent package that can be used in ANY PHP project - from WordPress plugins to modern PHP frameworks, microservices, or standalone applications.

Why Bob Query Builder?

While initially created to modernize Quantum ORM's query building capabilities, Bob Query Builder was designed from the ground up as a universal PHP query builder that can enhance ANY PHP application:

• ✅ Framework Agnostic - Use it with Laravel, Symfony, WordPress, or vanilla PHP
• ✅ Zero Lock-in - No framework dependencies, just pure PHP and PDO
• ✅ Modern PHP - Built for PHP 8.1+ with full type safety
• ✅ Production Ready - 1773 tests, 100% passing, battle-tested
• ✅ High Performance - <10ms query building overhead, handles 50k+ rows efficiently

Features

• 🚀 Full ORM with ActiveRecord - Complete ORM layer with models and relationships (v2.0)
• 🔗 Laravel-like Relationships - HasOne, HasMany, BelongsTo, BelongsToMany with eager loading
• 🗄️ Database Migrations - Complete migration system with versioning and rollbacks (v3.0)
• 🏗️ Schema Builder - Fluent interface for creating and modifying database tables
• 🔍 Schema Inspector - Reverse engineer existing databases and generate migrations
• 🌐 WordPress Schema Support - Specialized helpers for WordPress/WooCommerce tables
• 💼 Laravel-like Fluent Interface - Familiar, expressive query builder syntax
• 🔧 Database Agnostic - Support for MySQL, PostgreSQL, SQLite via PDO
• 🎯 Zero Dependencies - Only requires PHP and PDO
• ⚡ High Performance - Query caching, prepared statements, 1M+ rows/second streaming
• 🧪 Fully Tested - 1773 tests with Pest, 100% code coverage
• 🔒 Secure - Automatic SQL injection prevention via parameter binding
• 📦 Modular - Easy integration with ANY PHP project, use as query builder or full ORM
• 🔄 Transaction Support - Including savepoints for nested transactions
• 📊 PSR-3 Logging - Built-in query logging with slow query detection
• 💾 Memory Efficient - Stream 50k+ rows with minimal memory usage
• 🎁 Collections - Powerful collection class for working with result sets
• 🎪 Event System - Hook into migration lifecycle with custom event listeners

Recent Updates (v3.0.0) 🎉

🗄️ Complete Migration System & Schema Builder

Bob v3.0 introduces a comprehensive database migration and schema management system:

• Schema Builder - Fluent interface for creating and modifying database tables across MySQL, PostgreSQL, and SQLite
• Migration System - Complete migration lifecycle management with versioning, rollbacks, and batch tracking
• WordPress Support - Specialized WordPressBlueprint with helpers for WordPress and WooCommerce schemas
• Schema Inspector - Reverse engineer existing databases and generate migration files automatically
• Event System - Hook into migration lifecycle events for logging and custom workflows
• Migration Features:
  • Dependency resolution between migrations
  • Transaction support with automatic rollback on failure
  • Lifecycle hooks (before(), after())
  • Pretend mode for safe testing
  • Batch tracking and status reporting

See the Database Migrations & Schema Builder section for complete documentation.

Previous Updates (v2.2.2)

🎯 100% Code Coverage Achieved!

The test suite now has complete 100% code coverage across all components:

• ✅ 1773 tests passing with 4387 assertions
• ✅ Model class: Full coverage including edge cases for existing ID updates
• ✅ All test failures resolved: Fixed naming conflicts and relationship configurations
• ✅ Enhanced test reliability: Better handling of complex scenarios

🛠️ Test Suite Improvements

• Fixed class naming conflicts in Issue #13 debug tests
• Updated Issue #15 test expectations to reflect corrected behavior
• Improved BelongsToMany relationship tests for WordPress-style tables
• Added comprehensive tests for models with existing database IDs

Previous Updates (v2.2.1)

🛠️ Bug Fixes

Global Scope Field References in WHERE Clauses

Fixed an important issue with global scopes and field references:

• ✅ Global scopes now apply in toSql() - SQL generation now includes global scope modifications
• ✅ Prevents duplicate scope application - Added tracking to ensure scopes apply only once
• ✅ No side effects - Clone builder in toSql() to avoid modifying original instance

Previous Updates (v2.1.1)

🛠️ Critical Bug Fixes

Table Prefix Handling in JOIN Clauses

Fixed all table prefix issues reported in production environments:

• ✅ Double prefix bug fixed - No more duplicate prefixes in JOIN WHERE clauses
• ✅ Global scopes with JOINs - Global scopes containing JOINs now work correctly
• ✅ Table aliases - Proper handling of aliases in SELECT statements with JOINs
• ✅ Subqueries - whereIn() with subquery builders handles prefixes correctly

New Model Method: forceFill()

Added Laravel-compatible forceFill() method for bypassing mass assignment:

Previous Updates (v2.1.0)

🚀 New Features

Query Caching for exists()

Optimize repeated existence checks with the new opt-in caching mechanism:

Global Scopes in Relationships

Relationships now properly inherit global scopes:

Previous Updates (v2.0.7)

• Global Scopes Support - Laravel-style instance-level global scopes
• Nested WHERE Closures - Fixed SQL generation for nested conditions
• Delete Bindings Isolation - Fixed parameter binding in delete operations
• Timestamp Handling - Properly respects $timestamps = false
• Scope Chaining - Full support for chaining custom scope methods
• Aggregate Functions - Automatic detection and handling
• Subquery Support - Fixed whereIn() with Builder subqueries

🔒 PHP 8.4+ Compatibility

• Full compatibility with PHP 8.1, 8.2, 8.3, and 8.4
• All implicit nullable parameter warnings fixed

Requirements

• PHP 8.1, 8.2, 8.3, or 8.4
• PDO extension
• Database-specific PDO driver (pdo_mysql, pdo_pgsql, pdo_sqlite)

Installation

Install via Composer:

Quick Start

Configuration

Fetch Mode

By default, Bob Query Builder returns query results as associative arrays. You can configure this behavior:

Available fetch modes:

• PDO::FETCH_ASSOC - Associative arrays (default)
• PDO::FETCH_OBJ - stdClass objects
• PDO::FETCH_NUM - Numeric arrays
• PDO::FETCH_BOTH - Both numeric and associative arrays
• Any other PDO fetch mode constant

Basic Usage

Select Queries

Where Clauses

Joins

Aggregates

Insert

Update

Delete

Transactions

Raw Expressions

Pagination

Chunking

Process large datasets efficiently:

Cursor Streaming

For extremely large datasets (50k+ rows), use cursor for memory-efficient processing:

Query Debugging

Database Configuration

MySQL/MariaDB

PostgreSQL

SQLite

ORM & Model Features

Model Definition

Bob v2.0 introduces a full ORM layer with Laravel-inspired models:

Relationships

One-to-One (HasOne)

One-to-Many (HasMany)

Many-to-One (BelongsTo)

Many-to-Many (BelongsToMany)

Eager Loading

Prevent N+1 queries with eager loading:

Model Queries

Models provide an intuitive ActiveRecord interface:

Collections

Model queries return powerful Collection objects:

Database Migrations & Schema Builder (v3.0)

Schema Builder

Create and modify database tables with an expressive, fluent interface:

WordPress Schema Support

Bob includes specialized helpers for WordPress and WooCommerce table creation:

Migration System

Manage database schema changes with version-controlled migrations:

Running Migrations

Migration Features

• Dependency Resolution - Migrations can declare dependencies on other migrations
• Transaction Support - Run migrations within database transactions
• Batch Tracking - Track which migrations ran together
• Lifecycle Hooks - before() and after() methods for setup/cleanup
• Event System - Hook into migration events for logging and monitoring
• Pretend Mode - See what migrations would do without running them
• Version Control - Each migration is tracked with execution time and batch number

Schema Inspector

Reverse engineer existing databases and generate migration files:

Column Types

Bob supports all standard database column types:

Indexes and Constraints

Advanced Features

Query Builder Cloning

Subqueries

Custom Grammars

Extend the grammar for custom SQL dialects:

Testing

Run the test suite:

Performance Benchmarks

Bob Query Builder is highly optimized for real-world applications:

Query Building Performance

• Simple SELECT: ~0.02ms average overhead
• Complex queries with joins: ~0.1ms average overhead
• Subqueries: ~0.07ms average overhead
• All query types: <10ms overhead guaranteed

Large Dataset Handling

• Streaming: 1M+ rows per second throughput
• Memory usage: ~8MB for 15,000 rows with cursor
• 50,000 rows: Handled efficiently with <30MB memory
• Chunk processing: Process millions of rows without memory issues

Optimization Features

The query builder includes several optimization features:

• Prepared Statement Caching: Reuses prepared statements for identical queries
• Connection Pooling: Efficient connection management
• Query Result Caching: Optional caching of query results
• Lazy Loading: Use cursor() for memory-efficient iteration
• Bulk Operations: Optimized bulk inserts and updates
• Statement Caching: Second run of cached statements is significantly faster

Use Cases

Bob Query Builder is perfect for:

• WordPress Plugins - Modern query building without the overhead
• Legacy PHP Applications - Modernize database interactions incrementally
• Microservices - Lightweight, efficient database layer
• API Development - Clean, readable query construction
• Any PHP Project - From simple scripts to complex applications

Integration Examples

WordPress / Quantum ORM

Standalone PHP Application

Integration with Any Framework

Bob Query Builder can be registered as a service in any dependency injection container:

📚 Documentation

Full documentation is available at https://marwen-brini.github.io/bob-the-builder/

The documentation includes:

• Getting Started Guide
• Complete API Reference
• Database-specific Features
• Performance Optimization Tips
• WordPress Integration Guide
• CLI Tool Usage
• Migration from Other Query Builders
• Troubleshooting Guide

Contributing

We welcome contributions! Please see CONTRIBUTING.md for details.

Testing Requirements

• Tests must be written before implementation (TDD)
• All tests must pass before merging
• Maintain 100% code coverage
• Follow PSR-12 coding standards

License

The Bob Query Builder is open-sourced software licensed under the MIT license.

Project Repository

🔗 GitHub: https://github.com/Marwen-Brini/bob-the-builder

Credits

• Originally built to enhance Quantum ORM, but designed as a standalone solution for ANY PHP project
• Inspired by Laravel's Eloquent Query Builder
• Designed with modern PHP best practices and 100% test coverage
• Built with love for the PHP community

Support

For bugs and feature requests, please use the GitHub issues.

Roadmap

Completed ✅

• [x] Core query building functionality
• [x] Multi-database support (MySQL, PostgreSQL, SQLite)
• [x] Transaction support
• [x] Prepared statement caching
• [x] Query result caching
• [x] Connection pooling
• [x] Performance profiling
• [x] PSR-3 logging integration
• [x] Slow query detection
• [x] Memory-efficient streaming (cursor/chunk)
• [x] CLI tools for testing and query building
• [x] Comprehensive test suite (1644 tests)
• [x] Performance benchmarks

v3.0 - Completed ✅

• [x] Schema builder with fluent interface
• [x] Migration system with dependency resolution
• [x] WordPress/WooCommerce schema helpers
• [x] Schema inspector for reverse engineering
• [x] Migration event system
• [x] Transaction support for migrations

Planned Features

• [ ] Database seeding system
• [ ] Query builder macros/extensions
• [ ] Additional database support (SQL Server, Oracle)
• [ ] Query builder IDE helpers
• [ ] Advanced migration features (squashing, etc.)