Download the PHP package buildql/query-builder without Composer

BuildQL Query Builder

A secure, fluent, and lightweight SQL Query Builder for PHP 8.0+, inspired by Laravel's Query Builder. BuildQL provides an expressive and intuitive API for building complex database queries without writing raw SQL.

🎉 What's New in v1.0.4

• New exists() method: Quickly check if records exist without fetching data ← ADD
• Strict Type Comparison: All comparison operators now use === for type-safe queries
• Improved Reliability: Enhanced type safety prevents unexpected query results
• Better Performance: More predictable query execution

View full changelog

Features

• Fluent Interface: Chain methods to build complex queries elegantly
• Security First: Built-in protection against SQL injection using prepared statements
• Laravel-Style Syntax: Familiar API for developers coming from Laravel
• Multiple Join Types: Support for INNER, LEFT, RIGHT, and CROSS joins
• Aggregate Functions: Built-in support for COUNT, SUM, MIN, MAX, AVG
• Advanced Filtering: WHERE, OR WHERE, WHERE IN, WHERE NULL, WHERE NOT NULL conditions
• Query Builder: GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET support
• Raw SQL Support: Execute custom queries when needed
• Environment Configuration: Optional .env file support via phpdotenv
• Comprehensive Testing: Fully tested with Pest PHP
• Type-Safe Comparisons: Strict comparison operators (v1.0.4+) ← ADD THIS
• Existence Checks: Quick exists() method to check record presence (v1.0.4+) ← ADD

Requirements

• PHP >= 8.0
• MySQLi extension
• MySQL/MariaDB database

Installation

Install via Composer:

Quick Start

Basic Configuration

Using .env File (Recommended)

Create a .env file in your project root directory:

Bootstrap the connection:

API Reference

Database Connection Methods

DB::setConnection()

Manually establish a database connection.

Parameters:

• $server: Database host (e.g., 'localhost', '127.0.0.1')
• $username: Database username
• $pass: Database password
• $database: Database name (optional, can be set later)
• $port: MySQL port (default: 3306)

Example:

Note: Connection can only be established at once. Call DB::resetConnection() first to reconnect.

DB::boot()

Load database credentials from .env file and establish connection.

Parameters:

• $absoluteEnvPath: Absolute path to directory containing .env file (optional)

Requirements:

• .env file must exist in your root directory or specified path, else error will be thrown.
• Must contain: DB_HOST, DB_USERNAME, DB_PASSWORD, DB_PORT
• Optional: DB_DATABASE (can be set later)

Example:

Throws: BuilderException if credentials are missing or invalid.

DB::table()

Create a new query builder instance for a specific table.

Parameters:

• $table: Table name, optionally with alias (e.g., 'users', 'users:u')
• $database: Override global database for this query

Example:

DB::raw()

Execute raw SQL queries with parameter binding.

Parameters:

• $sql: Raw SQL query with ? placeholders
• $bind: Array of values to bind to placeholders

Returns:

• array: For SELECT queries (associative array of results)
• bool: For INSERT, UPDATE, DELETE queries

Example:

Note: Only positional placeholders allowed, not named placeholders.

DB::setDatabaseGlobally()

Set the default database for all queries.

Example:

DB::getGlobalDatabase()

Get the currently selected global database name.

Returns: Database name or null if not set

Example:

DB::resetConnection()

Close the current database connection.

Example:

Query Builder Methods

All query builder methods return $this for method chaining unless otherwise specified.

select()

Specify columns to retrieve.

Parameters:

• $columns: Array of column names, with optional aliases

Column Formats:

• 'column' - Simple column
• 'table.column' - Qualified column
• 'column:alias' - Column with alias
• 'table.column:alias' - Qualified column with alias
• 'count(column):total' - Aggregate with alias
• 'count(table.column):total' - Another Aggregate with alias

Column structure:

• table.column:alias // normal define columns
• count(table.column):alias // column with aggregate

Examples:

selectAggregate()

Add aggregate functions to the query.

Parameters: Each parameter accepts 'column' or 'column:alias' format.

Examples:

distinct()

Return only unique rows.

Example:

where()

Add a WHERE condition to filter results.

Parameters:

• $column: Column name
• $operator: Comparison operator or value (if $value is null)
• $value: Value to compare (optional)

Supported Operators: =, !=, >, <, >=, <=, LIKE, NOT LIKE

Examples:

orWhere()

Add an OR WHERE condition.

Example:

Note: AND has higher precedence than OR in MySQL. For complex conditions, be aware of operator precedence.

whereIn()

Filter by a set of values.

Example:

orWhereIn()

Add an OR WHERE IN condition.

Example:

whereNotIn()

Exclude records matching a set of values.

Example:

orWhereNotIn()

Add an OR WHERE NOT IN condition.

Example:

whereNull()

Filter records where a column is NULL.

Example:

orWhereNull()

Add an OR WHERE NULL condition.

Example:

whereNotNull()

Filter records where a column is NOT NULL.

Example:

orWhereNotNull()

Add an OR WHERE NOT NULL condition.

Example:

join()

Join tables using INNER JOIN.

Parameters:

• $table: Table to join (with optional alias using :)
• $primaryKey: First column in ON clause
• $foreignKey: Second column in ON clause
• $type: Join type ('inner', 'left', 'right', 'cross')

Examples:

leftJoin()

Perform a LEFT JOIN.

Example:

rightJoin()

Perform a RIGHT JOIN.

Example:

crossJoin()

Perform a CROSS JOIN (Cartesian product).

Example:

groupBy()

Group results by one or more columns.

Examples:

having()

Filter grouped results (use after groupBy()).

Examples:

orHaving()

Add an OR HAVING condition.

Example:

orderBy()

Sort results by one or more columns.

Parameters:

• $column: Column to sort by
• $sort: Sort direction ('ASC' or 'DESC', default: 'ASC')

Examples:

limit()

Limit the number of results.

Parameters:

• $limit: Maximum number of records to return
• $offset: Number of records to skip (optional)

Examples:

offset()

Skip a number of records.

Example:

Query Execution Methods

These methods execute the query and return results.

get()

Execute the query and return all matching records.

Parameters:

• $columns: Columns to retrieve (optional, overrides select())

Returns: Array of associative arrays

Examples:

all()

Get all records from the table (no filtering).

Example:

Note: Equivalent to DB::table('users')->get() without any conditions.

first()

Get the first matching record.

Returns: Associative array (single record) or empty array if no match

Examples:

find()

Find a record by its ID.

Parameters:

• $id: Primary key value (assumes column name is 'id')
• $columns: Columns to retrieve

Returns: Single record or empty array

Examples:

Note: If your table primary key column is not id then this is not for you because it's a convention that you must be follows.

count()

Count the number of matching records.

Returns: Integer count

Examples:

exists()

Check the presence of the records.

Returns: Boolean presence

Examples:

insert()

Insert a new record into the table.

Parameters:

• $data: Associative array (column => value)

Returns: true on success

Examples:

Note: Does not return the inserted ID. Use DB::raw("SELECT LAST_INSERT_ID()") if needed.

update()

Update existing records.

Parameters:

• $data: Associative array of columns to update

Returns: true on success

Important: Requires at least one WHERE condition

Examples:

delete()

Delete records from the table.

Returns: true on success

Important: Requires at least one WHERE condition

Examples:

insertOrUpdate()

Insert a new record or update if it exists.

Parameters:

• $data: Data to insert/update
• $where: Conditions to check existence (column => value) or (column => [value1, value2])

Returns: true on success

Examples:

Note: Executes a SELECT COUNT query first. For better performance with unique keys, consider using MySQL's INSERT ... ON DUPLICATE KEY UPDATE.

toRawSql()

Get the generated SQL query without executing it. (for debugging)

Returns: SQL query string with ? placeholders

Examples:

Use Case: Debugging, logging, or query optimization analysis.

Practical Examples

User Management System

E-commerce Product Search

Analytics Dashboard

Blog System

Security Best Practices

1. Always Use Prepared Statements

BuildQL automatically uses prepared statements for all values. Never concatenate user input:

2. Validate Column Names

While BuildQL validates column names, always validate user-controlled column names:

3. Sanitize User Input

Always validate and sanitize data before insertion:

4. Use Transactions for Critical Operations

For operations involving multiple tables, use transactions via raw queries:

Error Handling

BuildQL throws BuilderException for all database errors:

Exception Methods

The BuilderException class provides two parameters:

• $msg (string): Error message
• $trace (bool): Include file/line trace (default: true)

Common Exceptions

Connection Errors:

Query Errors:

Validation Errors:

Advanced Usage

Complex Queries

Dynamic Query Building

Pagination Helper (Example Implementation)

E.g; The following is an example helper function you can add to your project:

Query Logging (Example Implementation)

Performance Tips

1. Use Specific Columns

2. Add Indexes

Ensure frequently queried columns have database indexes:

3. Limit Results

Always use limit() for large tables:

4. Use first() Instead of get()[0]

5. Avoid N+1 Queries

Testing

BuildQL includes comprehensive Pest PHP tests. Run tests:

Writing Tests

Migration from Other Query Builders

From Laravel Eloquent

BuildQL syntax is very similar to Laravel:

Key Differences:

• No model system (use arrays instead)
• No automatic timestamps
• find() requires WHERE conditions in BuildQL (except find(id))
• No pluck(), chunk(), or cursor() methods

From PDO

Troubleshooting

Issue: "Connection is not established"

Solution: Call DB::setConnection() or DB::boot() before using queries.

Issue: "Connection already established"

Solution: You can only connect once. To reconnect:

Issue: "Where method is not optional in update case"

Solution: update() and delete() require WHERE conditions:

Issue: "Invalid column name"

Solution: Column names can only contain letters, numbers, underscores, dots, hyphens, and colons (for aliases) and parenthesis () if are using aggregates function :

Issue: Query returns empty array

Debugging:

Limitations

1. No ORM Features: BuildQL is a query builder, not an ORM. No models or relationships.
2. Single Database: Only supports MySQL/MariaDB via MySQLi.
3. No Transactions API: Use DB::raw() for transaction control.
4. No Query Caching: Results are not cached automatically.
5. Basic Aggregates: Complex window functions not supported.
6. AND/OR Precedence: Be aware of SQL operator precedence in complex WHERE clauses.

Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch: git checkout -b feature-name
3. Write tests for new features
4. Ensure all tests pass: ./vendor/bin/pest
5. Submit a pull request

Changelog

Version 1.0.1 (Initial Release)

• Fluent query builder interface
• Support for SELECT, INSERT, UPDATE, DELETE operations
• WHERE, JOIN, GROUP BY, HAVING, ORDER BY clauses
• Aggregate functions (COUNT, SUM, MIN, MAX, AVG)
• Prepared statements for security
• Comprehensive test coverage

License

MIT License - see LICENSE file for details.

Support

• Documentation: GitHub
• Issues: GitHub Issues
• Email: [email protected]

Credits

Author: Umar Ali

Inspired by: Laravel Query Builder

Built with ❤️ for the PHP community.

Quick Reference Card

Happy Querying with BuildQL!