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.

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

Requirements

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

Installation

Install via Composer:

composer require buildql/query-builder

Quick Start

Basic Configuration

<?php
require 'vendor/autoload.php';

use BuildQL\Database\Query\DB;

// Manual configuration
DB::setConnection(
    'localhost',  // host
    'root',       // username
    '',           // password
    'my_database',// database (optional)
    3306          // port (optional, default: 3306)
);

// Now you can use the query builder
$users = DB::table('users')->get();

Using .env File (Recommended)

Create a .env file in your project root directory:

DB_HOST=127.0.0.1 // localhost
DB_PORT=3306      // default port
DB_USERNAME=your_username
DB_PASSWORD=your_password
DB_DATABASE=your_database

Bootstrap the connection:

<?php
require 'vendor/autoload.php';
use BuildQL\Database\Query\DB;

// Automatically loads from .env in project's root if exists
DB::boot();

// Or specify custom path (directory only, not .env file)
DB::boot("/path/to/your/env/directory");

---

API Reference

Database Connection Methods

DB::setConnection()

Manually establish a database connection.

public static function setConnection(
    string $server, 
    string $username, 
    string $pass, 
    ?string $database = null, 
    int $port = 3306
): void

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:

DB::setConnection('localhost', 'root', '', 'my_app', 3306);

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.

public static function boot(?string $absoluteEnvPath = null): void

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:

// Load from project root
DB::boot();

// Load from custom directory
DB::boot("/var/www/config");

// If path contains .env filename, it will be automatically removed
DB::boot("/var/www/config/.env"); // Works the same as above

Throws: BuilderException if credentials are missing or invalid.

---

DB::table()

Create a new query builder instance for a specific table.

public static function table(string $table, ?string $database = null): Builder

Parameters:

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

Example:

$query = DB::table('users');
$query = DB::table('users:u'); // With alias
$query = DB::table('users', 'other_db'); // Different database

---

DB::raw()

Execute raw SQL queries with parameter binding.

public static function raw(string $sql, array $bind = []): mixed

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:

// SELECT query
$results = DB::raw("SELECT * FROM users WHERE age > ?", [18]);

// INSERT query
$success = DB::raw("INSERT INTO users (name, email) VALUES (?, ?)", ['John', 'john@example.com']);

// UPDATE query
$success = DB::raw("UPDATE users SET status = ? WHERE id = ?", ['active', 1]);

// ❌ Wrong way to bind values
$success = DB::raw("UPDATE users SET status = :active WHERE id = :id", ['active' => 'active', 'id' => 1]);

Note: Only positional placeholders allowed, not named placeholders.

---

DB::setDatabaseGlobally()

Set the default database for all queries.

public static function setDatabaseGlobally(string $database): void

Example:

DB::setDatabaseGlobally('my_app');

---

DB::getGlobalDatabase()

Get the currently selected global database name.

public static function getGlobalDatabase(): ?string

Returns: Database name or null if not set

Example:

DB::setDatabaseGlobally('my_app');
$currentDb = DB::getGlobalDatabase(); // Returns: 'my_app'

---

DB::resetConnection()

Close the current database connection.

public static function resetConnection(): void

Example:

DB::resetConnection();

---

Query Builder Methods

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

---

select()

Specify columns to retrieve.

public function select(array $columns = ['*']): self

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:

// Select all columns
DB::table('users')->select(['*'])->get();

// Select specific columns
DB::table('users')->select(['name', 'email'])->get();

// With aliases
DB::table('users')->select(['name:user_name', 'email:user_email'])->get();

// Qualified columns (with table name)
DB::table('users')
    ->join('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.name', 'profiles.bio', 'users.email:user_email'])
    ->get();

// Aggregate functions in select
DB::table('orders')
    ->select(['user_id', 'count(*):total_orders', 'sum(amount):total_spent'])
    ->groupBy('user_id')
    ->get();

---

selectAggregate()

Add aggregate functions to the query.

public function selectAggregate(
    ?string $count = null,
    ?string $sum = null,
    ?string $min = null,
    ?string $max = null,
    ?string $avg = null
): self

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

Examples:

// Count records
DB::table('users')
    ->selectAggregate(count: '*:total_users')
    ->first();
// Result: ['total_users' => 150]

// Multiple aggregates
DB::table('orders')
    ->select(['user_id'])
    ->selectAggregate(
        count: '*:order_count',
        sum: 'amount:total',
        avg: 'amount:average'
    )
    ->groupBy('user_id')
    ->get();

---

distinct()

Return only unique rows.

public function distinct(): self

Example:

// Get unique cities
DB::table('users')
    ->select(['city'])
    ->distinct()
    ->get();

---

where()

Add a WHERE condition to filter results.

public function where(string $column, $operator, $value = null): self

Parameters:

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

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

Examples:

// Equal comparison (shorthand)
DB::table('users')->where('status', 'active')->get();

// Explicit operator
DB::table('users')->where('age', '>', 18)->get();

// LIKE operator
DB::table('users')->where('name', 'LIKE', '%John%')->get();

// Multiple WHERE conditions (AND)
DB::table('users')
    ->where('status', 'active')
    ->where('age', '>=', 18)
    ->where('country', 'USA')
    ->get();

---

orWhere()

Add an OR WHERE condition.

public function orWhere(string $column, $operator, $value = null): self

Example:

// WHERE status = 'active' OR status = 'pending'
DB::table('users')
    ->where('status', 'active')
    ->orWhere('status', 'pending')
    ->get();

// WHERE (age < 18) OR (age > 65)
DB::table('users')
    ->where('age', '<', 18)
    ->orWhere('age', '>', 65)
    ->get();

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

---

whereIn()

Filter by a set of values.

public function whereIn(string $column, array $values): self

Example:

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

// Multiple whereIn conditions (AND)
DB::table('products')
    ->whereIn('category_id', [1, 2, 3])
    ->whereIn('status', ['active', 'featured'])
    ->get();

---

orWhereIn()

Add an OR WHERE IN condition.

public function orWhereIn(string $column, array $values): self

Example:

DB::table('users')
    ->whereIn('role', ['admin', 'moderator'])
    ->orWhereIn('permission', ['write', 'delete'])
    ->get();

---

whereNotIn()

Exclude records matching a set of values.

public function whereNotIn(string $column, array $values): self

Example:

// Exclude specific user IDs
DB::table('users')
    ->whereNotIn('id', [1, 2, 3])
    ->get();

// Exclude blocked statuses
DB::table('accounts')
    ->whereNotIn('status', ['banned', 'suspended', 'deleted'])
    ->get();

---

orWhereNotIn()

Add an OR WHERE NOT IN condition.

public function orWhereNotIn(string $column, array $values): self

Example:

DB::table('users')
    ->whereNotIn('status', ['banned'])
    ->orWhereNotIn('role', ['guest'])
    ->get();

---

whereNull()

Filter records where a column is NULL.

public function whereNull(string $column): self

Example:

// Find users without email verification
DB::table('users')
    ->whereNull('email_verified_at')
    ->get();

// Multiple NULL checks
DB::table('profiles')
    ->whereNull('phone')
    ->whereNull('address')
    ->get();

---

orWhereNull()

Add an OR WHERE NULL condition.

public function orWhereNull(string $column): self

Example:

DB::table('users')
    ->whereNull('deleted_at')
    ->orWhereNull('banned_at')
    ->get();

---

whereNotNull()

Filter records where a column is NOT NULL.

public function whereNotNull(string $column): self

Example:

// Find verified users
DB::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

---

orWhereNotNull()

Add an OR WHERE NOT NULL condition.

public function orWhereNotNull(string $column): self

Example:

DB::table('users')
    ->whereNotNull('phone')
    ->orWhereNotNull('mobile')
    ->get();

---

join()

Join tables using INNER JOIN.

public function join(
    string $table, 
    ?string $primaryKey = null, 
    ?string $foreignKey = null, 
    string $type = "inner"
): self

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:

// Simple INNER JOIN
DB::table('users')
    ->join('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.name', 'profiles.bio'])
    ->get();

// JOIN with table aliases
DB::table('users:u')
    ->join('profiles:p', 'u.id', 'p.user_id')
    ->join('posts:po', 'u.id', 'po.user_id')
    ->select(['u.name', 'p.bio', 'count(po.id):post_count'])
    ->groupBy('u.id')
    ->get();

// Multiple JOINs
DB::table('orders')
    ->join('users', 'orders.user_id', 'users.id')
    ->join('products', 'orders.product_id', 'products.id')
    ->select(['users.name', 'products.title', 'orders.amount'])
    ->get();

---

leftJoin()

Perform a LEFT JOIN.

public function leftJoin(string $table, string $primaryKey, string $foreignKey): self

Example:

// Get all users, including those without profiles
DB::table('users')
    ->leftJoin('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.name', 'profiles.bio'])
    ->get();

---

rightJoin()

Perform a RIGHT JOIN.

public function rightJoin(string $table, string $primaryKey, string $foreignKey): self

Example:

DB::table('orders')
    ->rightJoin('users', 'orders.user_id', 'users.id')
    ->get();

---

crossJoin()

Perform a CROSS JOIN (Cartesian product).

public function crossJoin(string $table): self

Example:

// Generate all combinations of sizes and colors
DB::table('sizes')
    ->crossJoin('colors')
    ->get();

---

groupBy()

Group results by one or more columns.

public function groupBy(string ...$columns): self

Examples:

// Group by single column
DB::table('orders')
    ->select(['user_id', 'count(*):total_orders'])
    ->groupBy('user_id')
    ->get();

// Group by multiple columns
DB::table('sales')
    ->select(['country', 'city', 'sum(amount):total'])
    ->groupBy('country', 'city')
    ->get();

---

having()

Filter grouped results (use after groupBy()).

public function having(string $column, $operator, $value = null): self

Examples:

// Users with more than 5 orders
DB::table('orders')
    ->select(['user_id', 'count(*):order_count'])
    ->groupBy('user_id')
    ->having('order_count', '>', 5)
    ->get();

// Multiple HAVING conditions
DB::table('sales')
    ->select(['product_id', 'sum(amount):total', 'count(*):sales_count'])
    ->groupBy('product_id')
    ->having('total', '>', 1000)
    ->having('sales_count', '>=', 10)
    ->get();

---

orHaving()

Add an OR HAVING condition.

public function orHaving(string $column, $operator, $value = null): self

Example:

DB::table('orders')
    ->select(['user_id', 'count(*):total', 'sum(amount):revenue'])
    ->groupBy('user_id')
    ->having('total', '>', 10)
    ->orHaving('revenue', '>', 5000)
    ->get();

---

orderBy()

Sort results by one or more columns.

public function orderBy(string $column, string $sort = "ASC"): self

Parameters:

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

Examples:

// Sort ascending (default)
DB::table('users')->orderBy('name')->get();

// Sort descending
DB::table('users')->orderBy('created_at', 'DESC')->get();

// Multiple sort columns
DB::table('products')
    ->orderBy('category', 'ASC')
    ->orderBy('price', 'DESC')
    ->get();

---

limit()

Limit the number of results.

public function limit(int $limit, ?int $offset = null): self

Parameters:

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

Examples:

// Get first 10 records
DB::table('users')->limit(10)->get();

// Skip 20 records, get next 10 (pagination)
DB::table('users')->limit(10, 20)->get();

---

offset()

Skip a number of records.

public function offset(int $offset): self

Example:

// Page 3 of results (20 per page)
DB::table('posts')
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->offset(40)
    ->get();

---

Query Execution Methods

These methods execute the query and return results.

---

get()

Execute the query and return all matching records.

public function get(array $columns = ["*"]): array

Parameters:

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

Returns: Array of associative arrays

Examples:

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

// Get specific columns
$users = DB::table('users')->get(['id', 'name', 'email']);

// With WHERE condition
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->get();

// Result format:
// [
//     ['id' => 1, 'name' => 'John', 'email' => 'john@example.com'],
//     ['id' => 2, 'name' => 'Jane', 'email' => 'jane@example.com'],
// ]

---

all()

Get all records from the table (no filtering).

public function all(): array

Example:

$allUsers = DB::table('users')->all();

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

---

first()

Get the first matching record.

public function first(array $columns = ['*']): array

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

Examples:

// Get first user
$user = DB::table('users')->first();
// Result: ['id' => 1, 'name' => 'John', ...]

// Get first matching record
$admin = DB::table('users')
    ->where('role', 'admin')
    ->first();

// Specific columns
$user = DB::table('users')->first(['id', 'name']);

---

find()

Find a record by its ID.

public function find(int $id, array $columns = ['*']): array

Parameters:

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

Returns: Single record or empty array

Examples:

// Find user by ID
$user = DB::table('users')->find(1);

// With specific columns
$user = DB::table('users')->find(1, ['name', 'email']);

// Works with table aliases
$user = DB::table('users:u')
    ->join('profiles:p', 'u.id', 'p.user_id')
    ->find(1, ['u.name', 'p.bio']);

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.

public function count(): int

Returns: Integer count

Examples:

// Count all users
$total = DB::table('users')->count();

// Count with condition
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->count();

// Count distinct values
$uniqueCities = DB::table('users')
    ->select(['city'])
    ->distinct()
    ->count();

---

insert()

Insert a new record into the table.

public function insert(array $data): bool

Parameters:

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

Returns: true on success

Examples:

// Insert single record
$success = DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'created_at' => date('Y-m-d H:i:s')
]);

// Insert with NULL values
DB::table('profiles')->insert([
    'user_id' => 1,
    'bio' => 'Software Developer',
    'phone' => null  // NULL value
]);

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

---

update()

Update existing records.

public function update(array $data): bool

Parameters:

• $data: Associative array of columns to update

Returns: true on success

Important: Requires at least one WHERE condition

Examples:

// Update single record
DB::table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Smith',
        'updated_at' => date('Y-m-d H:i:s')
    ]);

// Update multiple records
DB::table('users')
    ->where('status', 'pending')
    ->update(['status' => 'active']);

// Update with multiple conditions
DB::table('orders')
    ->where('status', 'pending')
    ->where('created_at', '<', date('Y-m-d', strtotime('-30 days')))
    ->update(['status' => 'cancelled']);

// ❌ wrong way without where condition. In that case exception will be thrown
DB::table('users')
    ->update(['status' => 'active']);

---

delete()

Delete records from the table.

public function delete(): bool

Returns: true on success

Important: Requires at least one WHERE condition

Examples:

// Delete single record
DB::table('users')
    ->where('id', 1)
    ->delete();

// Delete multiple records
DB::table('logs')
    ->where('created_at', '<', date('Y-m-d', strtotime('-90 days')))
    ->delete();

// Delete with multiple conditions
DB::table('spam')
    ->where('reported', '>', 5)
    ->whereNull('verified_at')
    ->delete();

// ❌ Wrong way without where condition. In that case exception will be thrown
DB::table('users')
    ->delete();

---

insertOrUpdate()

Insert a new record or update if it exists.

public function insertOrUpdate(array $data, array $where = []): bool

Parameters:

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

Returns: true on success

Examples:

// Update if exists, insert if not
DB::table('settings')->insertOrUpdate(
    ['value' => 'dark'],
    ['key' => 'theme']
);

// With multiple conditions
DB::table('user_preferences')->insertOrUpdate(
    [
        'user_id' => 1,
        'preference' => 'notifications',
        'value' => 'enabled'
    ],
    [
        'user_id' => 1,
        'preference' => 'notifications'
    ]
);

// With whereIn condition
DB::table('inventory')->insertOrUpdate(
    ['stock' => 100],
    ['product_id' => [1, 2, 3]]  // Array value = whereIn
);

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)

public function toRawSql(): string

Returns: SQL query string with ? placeholders

Examples:

$sql = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 18)
    ->toRawSql();
// Result: "SELECT * FROM `users` WHERE `status` = ? AND `age` > ?"

// Complex query preview
$sql = DB::table('orders:o')
    ->join('users:u', 'o.user_id', 'u.id')
    ->select(['u.name', 'count(o.id):total'])
    ->where('o.status', 'completed')
    ->groupBy('u.id')
    ->having('total', '>', 5)
    ->orderBy('total', 'DESC')
    ->toRawSql();

// Result: SELECT `u`.`name`, count(`o`.`id`) as `total` FROM `orders` as `o` INNER JOIN `users` as `u` ON `o`.`user_id` = `u`.`id` WHERE `o`.`status` = ? GROUP BY `u`.`id` HAVING `total` > ? ORDER BY `total` DESC

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

---

Practical Examples

User Management System

// Register new user
DB::table('users')->insert([
    'name' => 'Alice Johnson',
    'email' => 'alice@example.com',
    'password' => password_hash('secure_password', PASSWORD_DEFAULT),
    'role' => 'user',
    'created_at' => date('Y-m-d H:i:s')
]);

// Authenticate user
$user = DB::table('users')
    ->where('email', 'alice@example.com')
    ->whereNotNull('email_verified_at')
    ->first();

if ($user && password_verify('secure_password', $user['password'])) {
    // Login successful
}

// Get user with profile
$userProfile = DB::table('users:u')
    ->leftJoin('profiles:p', 'u.id', 'p.user_id')
    ->where('u.id', 1)
    ->first([
        'u.id', 'u.name', 'u.email',
        'p.bio', 'p.avatar', 'p.location'
    ]);

E-commerce Product Search

// Search products with filters
$products = DB::table('products')
    ->where('name', 'LIKE', '%laptop%')
    ->where('price', '>=', 500)
    ->where('price', '<=', 2000)
    ->whereIn('category_id', [1, 2, 3])
    ->whereNotNull('stock')
    ->where('status', 'active')
    ->orderBy('price', 'ASC')
    ->limit(20)
    ->get(['id', 'name', 'price', 'stock', 'image']);

// Product details with reviews
$product = DB::table('products:p')
    ->leftJoin('reviews:r', 'p.id', 'r.product_id')
    ->select([
        'p.*',
        'count(r.id):review_count',
        'avg(r.rating):avg_rating'
    ])
    ->where('p.id', 1)
    ->groupBy('p.id')
    ->first();

Analytics Dashboard

// Sales by month
$monthlySales = DB::table('orders')
    ->select([
        'DATE_FORMAT(created_at, "%Y-%m"):month',
        'count(*):order_count',
        'sum(total):revenue'
    ])
    ->where('status', 'completed')
    ->where('created_at', '>=', date('Y-01-01'))
    ->groupBy('month')
    ->orderBy('month', 'DESC')
    ->get();

// Top customers
$topCustomers = DB::table('orders:o')
    ->join('users:u', 'o.user_id', 'u.id')
    ->select([
        'u.id',
        'u.name',
        'count(o.id):order_count',
        'sum(o.total):lifetime_value'
    ])
    ->where('o.status', 'completed')
    ->groupBy('u.id')
    ->having('order_count', '>', 5)
    ->orderBy('lifetime_value', 'DESC')
    ->limit(10)
    ->get();

Blog System

// Published posts with author info
$posts = DB::table('posts:p')
    ->join('users:u', 'p.author_id', 'u.id')
    ->leftJoin('categories:c', 'p.category_id', 'c.id')
    ->select([
        'p.id', 'p.title', 'p.slug', 'p.excerpt',
        'p.published_at', 'u.name:author_name',
        'c.name:category_name'
    ])
    ->whereNotNull('p.published_at')
    ->where('p.status', 'published')
    ->orderBy('p.published_at', 'DESC')
    ->limit(10)
    ->get();

// Post with comments count
$post = DB::table('posts:p')
    ->leftJoin('comments:c', 'p.id', 'c.post_id')
    ->select(['p.*', 'count(c.id):comment_count'])
    ->where('p.slug', 'my-first-post')
    ->groupBy('p.id')
    ->first();

---

Security Best Practices

1. Always Use Prepared Statements

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

// ✅ SAFE - Uses prepared statements (Recommended way)
$email = $_POST['email'];
$user = DB::table('users')->where('email', $email)->first();

// ❌ NEVER DO THIS
$user = DB::raw("SELECT * FROM users WHERE email = '$email'"); // SQL Injection risk!

// ✅ SAFE - If you want to manually write query then replace actual value to placeholder (?)  
//           and attach value to second param of raw() method as an array Like
$user = DB::raw("SELECT * FROM users WHERE email = ?", [$email]); // No SQL Injection risk!

2. Validate Column Names

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

// ✅ SAFE - Whitelist allowed columns
$allowedSortColumns = ['name', 'created_at', 'price'];
$sortBy = $_GET['sort'] ?? 'created_at';

if (in_array($sortBy, $allowedSortColumns)) {
    $products = DB::table('products')->orderBy($sortBy)->get();
}

3. Sanitize User Input

Always validate and sanitize data before insertion:

// ✅ SAFE - Validate before insert
$name = trim($_POST['name']);
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);

if ($email && strlen($name) > 0) {
    DB::table('users')->insert([
        'name' => $name,
        'email' => $email
    ]);
}

4. Use Transactions for Critical Operations

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

DB::raw("START TRANSACTION");

try {
    DB::table('orders')->insert([
        'user_id' => 1,
        'total' => 99.99
    ]);
    
    DB::table('inventory')->where('product_id', 1)->update(['stock' => 5]);
    
    DB::raw("COMMIT");
} catch (BuilderException $e) {
    DB::raw("ROLLBACK");
    throw $e;
}

---

Error Handling

BuildQL throws BuilderException for all database errors:

use BuildQL\Database\Query\DB;
use BuildQL\Database\Query\Exception\BuilderException;

try {
    $users = DB::table('users')->where('age', '>', 18)->get();
} catch (BuilderException $e) {
    // Get detailed error message with trace
    echo $e->getErrorMessage();
    // Output: "Query Execution Failed: Table 'database.users' doesn't exist - Check your code in app.php at line 25"
    
    // Log error
    error_log($e->getErrorMessage());
}

Exception Methods

The BuilderException class provides two parameters:

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

// With trace (default behavior)
throw new BuilderException("Error message"); // Shows: "Error message - Check your code in file.php at line 25"

// Without trace (useful for production)
throw new BuilderException("Error message", false); // Shows: "Error message"

Common Exceptions

Connection Errors:

// Connection is not established right now.
// Connection already established. Connection will not be established more than once.
// Connection Failed Due to : Access denied for user

Query Errors:

// Where method is not optional in update case
// Where method is not optional in delete case
// Invalid column name : user_id;DROP TABLE users
// Query Preparation Failed: You have an error in your SQL syntax
// Query Execution Failed: You have an error in your SQL syntax

Validation Errors:

// $values parameter must be a non-empty array
// Select method must be contain a proper non-empty array of columns name
// Invalid join clause (INVALID_TYPE)

---

Advanced Usage

Complex Queries

// Subquery-like behavior using multiple queries
$userIds = DB::table('orders')
    ->select(['user_id'])
    ->where('status', 'completed')
    ->where('created_at', '>', date('Y-m-d', strtotime('-30 days')))
    ->groupBy('user_id')
    ->having('count(*)', '>', 5)
    ->get();

$topBuyers = array_column($userIds, 'user_id');

$users = DB::table('users')
    ->whereIn('id', $topBuyers)
    ->orderBy('name')
    ->get();

Dynamic Query Building

$query = DB::table('products');

// Apply filters conditionally
if (isset($_GET['category'])) {
    $query->where('category_id', $_GET['category']);
}

if (isset($_GET['min_price'])) {
    $query->where('price', '>=', $_GET['min_price']);
}

if (isset($_GET['max_price'])) {
    $query->where('price', '<=', $_GET['max_price']);
}

if (isset($_GET['search'])) {
    $query->where('name', 'LIKE', '%' . $_GET['search'] . '%');
}

// Apply sorting
$sortBy = $_GET['sort'] ?? 'name';
$sortDir = $_GET['dir'] ?? 'ASC';
$query->orderBy($sortBy, $sortDir);

// Pagination
$page = $_GET['page'] ?? 1;
$perPage = 20;
$query->limit($perPage, ($page - 1) * $perPage);

$products = $query->get();

Pagination Helper (Example Implementation)

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

function paginate($table, $perPage = 15, $page = 1, $conditions = []) {
    $query = DB::table($table);
    
    // Apply conditions
    foreach ($conditions as $column => $value) {
        if (is_array($value)) {
            $query->whereIn($column, $value);
        } else {
            $query->where($column, $value);
        }
    }
    
    // Get total count
    $total = $query->count();
    
    // Get paginated results
    $offset = ($page - 1) * $perPage;
    $data = $query->limit($perPage, $offset)->get();
    
    return [
        'data' => $data,
        'total' => $total,
        'per_page' => $perPage,
        'current_page' => $page,
        'last_page' => ceil($total / $perPage)
    ];
}

// Usage
$result = paginate('products', 20, 2, ['status' => 'active']);

Query Logging (Example Implementation)

// Create a query logger
function logQuery($query) {
    $sql = $query->toRawSql();
    $timestamp = date('Y-m-d H:i:s');
    file_put_contents('query.log', "[$timestamp] $sql\n", FILE_APPEND);
}

// Use in your application
$query = DB::table('users')->where('status', 'active');
logQuery($query);
$users = $query->get();

---

Performance Tips

1. Use Specific Columns

// ❌ SLOW - Retrieves all columns
$users = DB::table('users')->get();

// ✅ FASTER - Only needed columns
$users = DB::table('users')->get(['id', 'name', 'email']);

2. Add Indexes

Ensure frequently queried columns have database indexes:

CREATE INDEX idx_status ON users(status);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_user_email ON users(email);

3. Limit Results

Always use limit() for large tables:

// ✅ Good practice
$recentPosts = DB::table('posts')
    ->orderBy('created_at', 'DESC')
    ->limit(50)
    ->get();

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

// ❌ Less efficient
$user = DB::table('users')->where('id', 1)->get()[0];

// ✅ More efficient (adds LIMIT 1)
$user = DB::table('users')->where('id', 1)->first();

5. Avoid N+1 Queries

// ❌ BAD - N+1 queries
$users = DB::table('users')->get();
$profiles = [];
foreach ($users as $user) {
    $profile = DB::table('profiles')->where('user_id', $user['id'])->first();
    if ($profile){
        $profiles[] = $profile;
    }
    else{
        $profiles[] = null;
    }
    // Process...
}

// ✅ GOOD - Single query with JOIN
$usersWithProfiles = DB::table('users')
    ->leftJoin('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.*', 'profiles.bio', 'profiles.avatar'])
    ->get();

---

Testing

BuildQL includes comprehensive Pest PHP tests. Run tests:

./vendor/bin/pest

Writing Tests

use BuildQL\Database\Query\DB;
use BuildQL\Database\Query\Exception\BuilderException;

beforeEach(function() {
    DB::resetConnection(); // for security perpective
    DB::setConnection('localhost', 'root', '', 'test_db');
});

test('select query generates correct SQL', function() {
    $sql = DB::table('users')
        ->where('status', 'active')
        ->toRawSql();
    
    expect($sql)->toContain('SELECT * FROM `users` WHERE `status` = ?');
});

test('insert method works correctly', function() {
    $result = DB::table('users')->insert([
        'name' => 'Test User',
        'email' => 'test@example.com'
    ]);
    
    expect($result)->toBeTrue();
});

afterEach(function() {
    DB::resetConnection(); // after the end of the script, reset the database connection
});

---

Migration from Other Query Builders

From Laravel Eloquent

BuildQL syntax is very similar to Laravel:

// Laravel
DB::table('users')->where('status', 'active')->get();

// BuildQL (same!)
DB::table('users')->where('status', 'active')->get();

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

// PDO  (it's to long and messy)
$stmt = $pdo->prepare("SELECT * FROM users WHERE status = ?");
$stmt->execute(['active']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// BuildQL (simpler and easy to use!)
$users = DB::table('users')->where('status', 'active')->get();

---

Troubleshooting

Issue: "Connection is not established"

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

DB::setConnection('localhost', 'root', '', 'mydb');

---

Issue: "Connection already established"

Solution: You can only connect once. To reconnect:

DB::resetConnection();
DB::setConnection('localhost', 'root', '', 'new_db');

---

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

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

// ❌ Will throw exception
DB::table('users')->update(['status' => 'active']);

// ✅ Add WHERE condition
DB::table('users')->where('id', 1)->update(['status' => 'active']);

---

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 :

// ❌ Invalid
DB::table('users')->where('user name', 'John');
DB::table('users')->where('`user_name`', 'John');
DB::table('posts')->where('count(`user_id`):user_post_count', 'John');

// ✅ Valid
DB::table('users')->where('user_name', 'John');
DB::table('posts')->where('count(user_id):user_post_count', 'John');

---

Issue: Query returns empty array

Debugging:

// 1. Check the generated SQL
$sql = DB::table('users')->where('status', 'active')->toRawSql();
echo $sql;

// 2. Test with raw SQL
$result = DB::raw("SELECT * FROM users WHERE status = ?", ['active']);
print_r($result);

// 3. Check database connection
$db = DB::raw("SELECT DATABASE() as db");
print_r($db);

---

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: umar.pwu786@gmail.com

---

Credits

Author: Umar Ali

Inspired by: Laravel Query Builder

Built with ❤️ for the PHP community.

---

Quick Reference Card

// Connection
DB::setConnection('host', 'user', 'pass', 'db'); // Manually
// OR
DB::boot(); // Dynamic from .env

// Basic Queries
DB::table('users')->get();
DB::table('users')->first();
DB::table('users')->find(1);
DB::table('users')->count();
DB::table('users')->all();

// Filtering
->where('column', 'value')
->orWhere('column', 'value')
->whereIn('column', [1, 2, 3])
->orWhereIn('column', [1, 2, 3])
->whereNotIn('column', [1, 2, 3])
->orWhereNotIn('column', [1, 2, 3])
->whereNull('column')
->orWhereNull('column')
->whereNotNull('column')
->orWhereNotNull('column')

// Joins
->join('table', 'key1', 'key2') // inner join
->leftJoin('table', 'key1', 'key2')
->rightJoin('table', 'key1', 'key2')
->crossJoin('table')

// Sorting & Limiting
->orderBy('column', 'DESC')
->limit(10)
->offset(20)

// Grouping
->groupBy('column1', 'column2')
->having('count', '>', 5)
->orHaving('count', '>', 5)

// Aggregates
->selectAggregate(count: '*:total', sum: "amount:total_amount")

// Modifications
->insert(['name' => 'John'])
->update(['status' => 'active'])
->delete()

// Utilities
->toRawSql()
->select(['col1', 'col2', 'table.column:alias', 'count(*):total'])
->distinct()

// Raw Queries
DB::raw('SELECT * FROM users WHERE id = ?', [1])

---

Happy Querying with BuildQL!