The QueryBuilder is JiFramework's database abstraction layer. It provides a fluent PHP API for building and executing SQL queries without writing raw SQL. Every query is parameterised automatically, eliminating SQL injection at its root.

• Access via: $app->db
• Named connection: $app->db('connection_name')

Key features:

• Fluent chainable interface — build queries step by step
• Full SELECT, INSERT, UPDATE, DELETE support
• JOINs, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
• Aggregates: COUNT, SUM, AVG, MIN, MAX
• Batch processing with chunk() for large datasets
• Upsert, INSERT IGNORE, atomic increment/decrement
• Transaction support
• Conditional query building with when()
• Raw SQL escape hatch with query() and bind()
• Throws DatabaseException with the full SQL on error

The query state resets automatically after every terminal call (get(), insert(), update(), etc.), so the same $app->db instance can be reused for multiple independent queries.

table(string $table): static

Sets the database table for the query. Always the first call in any chain.

$app->db->table('users')->get();
$app->db->table('order_items')->where('order_id', $id)->get();

select(array|string $columns): static

Sets the columns to retrieve. Defaults to *. Accepts an array, multiple string arguments, or column aliases using AS.

// All columns (default)
$app->db->table('users')->get();

// Specific columns - array
$app->db->table('users')
    ->select(['id', 'name', 'email'])
    ->get();

// Specific columns - multiple arguments
$app->db->table('users')
    ->select('id', 'name', 'email')
    ->get();

// With aliases
$app->db->table('users')
    ->select(['id', 'name AS full_name', 'email AS contact'])
    ->get();
// Result rows: ['id' => 1, 'full_name' => 'Alice', 'contact' => 'alice@...']

selectRaw(string $expression): static

Sets a raw SQL expression as the entire SELECT clause. Use for SQL functions, calculations, or CASE expressions that select() cannot express.

// Multiple aggregate functions in one query
$result = $app->db->table('orders')
    ->selectRaw('COUNT(*) AS total, SUM(amount) AS revenue, AVG(amount) AS avg_order')
    ->where('status', 'completed')
    ->first();
echo $result['revenue']; // e.g. 49850.00

// CASE expression
$users = $app->db->table('users')
    ->selectRaw('id, name, IF(status = "active", "Yes", "No") AS is_active')
    ->get();

// Date extraction
$daily = $app->db->table('orders')
    ->selectRaw('DATE(created_at) AS day, COUNT(*) AS count, SUM(amount) AS revenue')
    ->groupBy('day')
    ->get();

distinct(): static

Adds SELECT DISTINCT to the query, returning only unique rows based on the selected columns.

// Get list of unique countries from users table
$countries = $app->db->table('users')
    ->select('country')
    ->distinct()
    ->orderBy('country')
    ->get();

// Get unique category IDs that have at least one published post
$categoryIds = $app->db->table('posts')
    ->select('category_id')
    ->distinct()
    ->where('status', 'published')
    ->pluck('category_id');

where(string|array $column, mixed $operator, mixed $value): static

Adds an AND WHERE condition to the query. Supports three calling styles: 2-argument shorthand, 3-argument with operator, and array of conditions.

// 2 arguments: operator defaults to =
$app->db->table('users')->where('status', 'active')->get();

// 3 arguments: explicit operator
$app->db->table('users')->where('age', '>=', 18)->get();
$app->db->table('products')->where('price', '<', 100)->get();
$app->db->table('posts')->where('title', 'LIKE', '%php%')->get();
$app->db->table('users')->where('role', '!=', 'admin')->get();

// Array shorthand - multiple AND conditions at once
$app->db->table('users')->where([
    'status'   => 'active',
    'verified' => 1,
    'role'     => 'editor',
])->get();

// Chaining multiple where() calls
$app->db->table('orders')
    ->where('status', 'pending')
    ->where('amount', '>', 500)
    ->where('created_at', '>', '2025-01-01')
    ->get();
// WHERE status = 'pending' AND amount > 500 AND created_at > '2025-01-01'

orWhere(string $column, mixed $operator, mixed $value): static

Adds an OR WHERE condition. Same calling styles as where(). Conditions are appended with OR instead of AND.

// Find users who are admin OR superadmin
$app->db->table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'superadmin')
    ->get();

// Search across multiple fields
$term = 'john';
$app->db->table('users')
    ->where('name', 'LIKE', '%' . $term . '%')
    ->orWhere('email', 'LIKE', '%' . $term . '%')
    ->get();

// Mix AND and OR
$app->db->table('products')
    ->where('stock', '>', 0)
    ->where('category', 'electronics')
    ->orWhere('featured', 1)
    ->get();
// WHERE stock > 0 AND category = 'electronics' OR featured = 1

whereIn(string $column, array $values): static

Adds a WHERE column IN (...) condition. All values are parameterised automatically. If an empty array is passed, the query returns no rows safely.

// Get specific records by ID list
$users = $app->db->table('users')
    ->whereIn('id', [1, 5, 10, 42])
    ->get();

// Get posts in selected categories
$posts = $app->db->table('posts')
    ->whereIn('category_id', [3, 7, 12])
    ->where('status', 'published')
    ->orderByDesc('created_at')
    ->get();

// Dynamic list from another query
$subscribedIds = $app->db->table('subscriptions')
    ->where('status', 'active')
    ->pluck('user_id');

$subscribers = $app->db->table('users')
    ->whereIn('id', $subscribedIds)
    ->get();

whereNotIn(string $column, array $values): static

Adds a WHERE column NOT IN (...) condition. Excludes rows where the column matches any value in the array.

// Exclude cancelled and refunded orders
$orders = $app->db->table('orders')
    ->whereNotIn('status', ['cancelled', 'refunded'])
    ->get();

// Get all products not in a specific set
$others = $app->db->table('products')
    ->whereNotIn('id', $featuredProductIds)
    ->where('status', 'active')
    ->get();

whereNull(string $column): static

Adds a WHERE column IS NULL condition.

// Users who have not verified their email
$unverified = $app->db->table('users')
    ->whereNull('email_verified_at')
    ->get();

// Soft-delete pattern: get non-deleted records
$posts = $app->db->table('posts')
    ->whereNull('deleted_at')
    ->latest()
    ->get();

// Orders with no assigned agent
$unassigned = $app->db->table('orders')
    ->whereNull('agent_id')
    ->where('status', 'pending')
    ->get();

whereNotNull(string $column): static

Adds a WHERE column IS NOT NULL condition.

// Users who have verified their email
$verified = $app->db->table('users')
    ->whereNotNull('email_verified_at')
    ->get();

// Posts that have been published (published_at is set)
$published = $app->db->table('posts')
    ->whereNotNull('published_at')
    ->orderByDesc('published_at')
    ->get();

// Orders that have a tracking number
$shipped = $app->db->table('orders')
    ->whereNotNull('tracking_number')
    ->where('status', 'shipped')
    ->get();

whereBetween(string $column, mixed $min, mixed $max): static

Adds a WHERE column BETWEEN min AND max condition. Inclusive on both ends. Works for numbers, dates, and datetime strings.

// Orders in a price range
$orders = $app->db->table('orders')
    ->whereBetween('amount', 100, 500)
    ->get();

// Records created in a date range
$janOrders = $app->db->table('orders')
    ->whereBetween('created_at', '2025-01-01 00:00:00', '2025-01-31 23:59:59')
    ->get();

// Products in a stock level range
$normal = $app->db->table('products')
    ->whereBetween('stock', 10, 200)
    ->get();

whereNotBetween(string $column, mixed $min, mixed $max): static

Adds a WHERE column NOT BETWEEN min AND max condition. Returns rows where the value falls outside the given range.

// Products that are overstocked or out of stock
$anomalies = $app->db->table('products')
    ->whereNotBetween('stock', 1, 100)
    ->get();

// Orders outside a normal price range (potential fraud check)
$unusual = $app->db->table('orders')
    ->whereNotBetween('amount', 1, 10000)
    ->latest()
    ->get();

whereRaw(string $sql, array $bindings = []): static

Injects a raw SQL expression into the WHERE clause. Use when no fluent method covers the condition. Always pass user-controlled values through $bindings, never by string concatenation.

// SQL date function
$app->db->table('orders')
    ->whereRaw('YEAR(created_at) = :year', ['year' => 2025])
    ->get();

// Calculated condition
$app->db->table('products')
    ->whereRaw('(price * quantity) > :threshold', ['threshold' => 1000])
    ->get();

// Full-text search
$app->db->table('articles')
    ->whereRaw('MATCH(title, body) AGAINST(:term IN BOOLEAN MODE)', ['term' => 'php framework'])
    ->get();

// Combined with fluent conditions
$app->db->table('users')
    ->where('status', 'active')
    ->whereRaw('TIMESTAMPDIFF(DAY, last_login, NOW()) <= :days', ['days' => 30])
    ->get();

join(string $table, string $first, string $operator, string $second): static

Adds an INNER JOIN clause. Returns only rows that have a match in both tables.

// Posts with their author name
$posts = $app->db->table('posts')
    ->select(['posts.id', 'posts.title', 'users.name AS author'])
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->where('posts.status', 'published')
    ->get();

// Multiple JOINs - order items with product and category details
$items = $app->db->table('order_items')
    ->select([
        'order_items.id',
        'products.name AS product',
        'categories.name AS category',
        'order_items.quantity',
        'order_items.price',
    ])
    ->join('products',   'order_items.product_id',  '=', 'products.id')
    ->join('categories', 'products.category_id',    '=', 'categories.id')
    ->where('order_items.order_id', $orderId)
    ->get();

leftJoin(string $table, string $first, string $operator, string $second): static

Adds a LEFT JOIN clause. Returns all rows from the left (main) table, with matched data from the right table. Unmatched right-side columns are NULL.

// All users, with their order count (0 if no orders)
$users = $app->db->table('users')
    ->select(['users.id', 'users.name', 'COUNT(orders.id) AS order_count'])
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->groupBy('users.id')
    ->get();

// All categories, with their post count (including empty categories)
$categories = $app->db->table('categories')
    ->select(['categories.id', 'categories.name', 'COUNT(posts.id) AS post_count'])
    ->leftJoin('posts', 'categories.id', '=', 'posts.category_id')
    ->groupBy('categories.id')
    ->orderByDesc('post_count')
    ->get();

rightJoin(string $table, string $first, string $operator, string $second): static

Adds a RIGHT JOIN clause. Returns all rows from the right table, with matched data from the left table. Unmatched left-side columns are NULL.

// All products, even those not yet in any order
$products = $app->db->table('order_items')
    ->select(['products.id', 'products.name', 'SUM(order_items.quantity) AS units_sold'])
    ->rightJoin('products', 'order_items.product_id', '=', 'products.id')
    ->groupBy('products.id')
    ->get();
// Products with no sales will have units_sold = NULL

orderBy(string $column, string $direction = 'ASC'): static

Adds an ORDER BY clause. Direction is ASC (default) or DESC. Can be called multiple times for multi-column sorting.

// Single column ascending
$app->db->table('users')->orderBy('name')->get();

// Single column descending
$app->db->table('posts')->orderBy('created_at', 'DESC')->get();

// Multiple columns - sort by role first, then name
$app->db->table('users')
    ->orderBy('role')
    ->orderBy('name')
    ->get();

orderByDesc(string $column): static

Shorthand for orderBy($column, 'DESC').

$topProducts = $app->db->table('products')
    ->orderByDesc('sales_count')
    ->take(10)
    ->get();

latest(string $column = 'created_at'): static

Orders results by created_at DESC by default, returning the newest rows first. Pass a custom column name to order by a different field.

// 10 newest posts
$app->db->table('posts')->latest()->take(10)->get();

// Latest by a custom column
$app->db->table('orders')->latest('placed_at')->get();

oldest(string $column = 'created_at'): static

Orders results by created_at ASC by default, returning the oldest rows first.

// Process jobs in the order they were created (FIFO)
$app->db->table('jobs')->where('status', 'pending')->oldest()->get();

// Oldest users first
$app->db->table('users')->oldest()->get();

limit(int $limit): static

take(int $n): static — alias for limit()

Limits the number of rows returned by the query.

// Return at most 20 rows
$app->db->table('products')->orderBy('name')->limit(20)->get();

// Expressive alias
$top5 = $app->db->table('products')->orderByDesc('rating')->take(5)->get();

offset(int $offset): static

skip(int $n): static — alias for offset()

Skips a number of rows before returning results. Used together with limit() for pagination.

// Manual pagination
$page    = max(1, (int)($_GET['page'] ?? 1));
$perPage = 20;

$users = $app->db->table('users')
    ->orderBy('name')
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->get();

// Expressive aliases
$page2 = $app->db->table('articles')
    ->latest()
    ->take(10)
    ->skip(10)
    ->get();

groupBy(string|array $columns): static

Adds a GROUP BY clause. Accepts a single column, multiple arguments, or an array. Always combine with aggregate functions in selectRaw() or select().

// Count users per country
$app->db->table('users')
    ->select(['country', 'COUNT(*) AS total'])
    ->groupBy('country')
    ->orderByDesc('total')
    ->get();

// Revenue per category
$app->db->table('order_items')
    ->select(['category_id', 'SUM(price * quantity) AS revenue'])
    ->join('products', 'order_items.product_id', '=', 'products.id')
    ->groupBy('category_id')
    ->get();

// Group by multiple columns
$app->db->table('sales')
    ->selectRaw('YEAR(created_at) AS year, MONTH(created_at) AS month, SUM(amount) AS total')
    ->groupBy(['year', 'month'])
    ->orderBy('year')->orderBy('month')
    ->get();

having(string $column, string $operator, mixed $value): static

Adds a HAVING condition to filter grouped results. Use having() instead of where() when filtering on aggregate values (COUNT, SUM, AVG, etc.).

// Categories with more than 5 published posts
$app->db->table('posts')
    ->select(['category_id', 'COUNT(*) AS post_count'])
    ->where('status', 'published')
    ->groupBy('category_id')
    ->having('post_count', '>', 5)
    ->get();

// Users who have spent more than $1000 total
$app->db->table('orders')
    ->select(['user_id', 'SUM(amount) AS total_spent'])
    ->where('status', 'completed')
    ->groupBy('user_id')
    ->having('total_spent', '>', 1000)
    ->orderByDesc('total_spent')
    ->get();

orHaving(string $column, string $operator, mixed $value): static

Adds an OR HAVING condition. Works the same as having() but joined with OR.

// Categories with many posts OR high total views
$app->db->table('posts')
    ->select(['category_id', 'COUNT(*) AS post_count', 'SUM(views) AS total_views'])
    ->groupBy('category_id')
    ->having('post_count', '>', 10)
    ->orHaving('total_views', '>', 50000)
    ->get();

get(): array — alias: fetchAll()

Executes the SELECT query and returns all matching rows as an array of associative arrays. Returns an empty array [] if no rows match. This is the primary method for retrieving multiple rows.

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

// Get filtered rows
$active = $app->db->table('users')
    ->where('status', 'active')
    ->orderBy('name')
    ->get();

foreach ($active as $user) {
    echo $user['name'] . ' - ' . $user['email'];
}

// Safe to use even when no results - returns []
$results = $app->db->table('posts')->where('id', 99999)->get();
// $results === []

first(): array|null — alias: fetch()

Returns the first matching row as an associative array. Automatically adds LIMIT 1 to the query. Returns null if no row matches.

// Find a user by email
$user = $app->db->table('users')
    ->where('email', '[email protected]')
    ->first();

if ($user) {
    echo 'Welcome, ' . $user['name'];
} else {
    echo 'User not found.';
}

// Find-or-404 pattern
$post = $app->db->table('posts')->where('id', $postId)->first();
if (!$post) {
    $app->exit(404, 'Post not found');
}

// Find latest pending order for a user
$pending = $app->db->table('orders')
    ->where('user_id', $userId)
    ->where('status', 'pending')
    ->latest()
    ->first();

value(string $column): mixed

Returns the value of a single column from the first matching row. Returns null if no row is found. Use when you need exactly one scalar value from the database.

// Get a user's email by ID
$email = $app->db->table('users')->where('id', $userId)->value('email');
// "[email protected]" or null

// Get the title of the latest post
$title = $app->db->table('posts')
    ->where('status', 'published')
    ->latest()
    ->value('title');

// Get a config value from a settings table
$siteName = $app->db->table('settings')->where('key', 'site_name')->value('value');

pluck(string $column): array

Returns a flat array of values for a single column across all matching rows. Supports column aliases. Use this instead of get() when you only need one column from many rows.

// Get all user IDs
$ids = $app->db->table('users')->pluck('id');
// [1, 2, 3, 4, ...]

// Get all active user emails
$emails = $app->db->table('users')
    ->where('status', 'active')
    ->pluck('email');
// ["[email protected]", "[email protected]", ...]

// Use as input to whereIn()
$activeUserIds = $app->db->table('subscriptions')
    ->where('plan', 'pro')
    ->pluck('user_id');

$proUsers = $app->db->table('users')
    ->whereIn('id', $activeUserIds)
    ->get();

// With alias
$names = $app->db->table('users')->pluck('name as label');
// ["Alice", "Bob", ...]

count(): int

Returns the number of rows matching the current query conditions. Runs SELECT COUNT(*) internally.

$total  = $app->db->table('users')->count();
$active = $app->db->table('users')->where('status', 'active')->count();

// Use in pagination
$total = $app->db->table('posts')->where('status', 'published')->count();
$pages = ceil($total / $perPage);

exists(): bool

Returns true if at least one row matches the conditions, false otherwise. More readable than checking count() > 0.

// Check before inserting to avoid duplicates
$emailTaken = $app->db->table('users')
    ->where('email', $_POST['email'])
    ->exists();

if ($emailTaken) {
    $app->exit(409, json_encode(['error' => 'Email already registered']));
}

// Check if user has any orders
if ($app->db->table('orders')->where('user_id', $userId)->exists()) {
    echo 'Returning customer';
}

doesntExist(): bool

The inverse of exists(). Returns true if no rows match the conditions.

// Only send welcome email if user has no previous orders
if ($app->db->table('orders')->where('user_id', $userId)->doesntExist()) {
    sendWelcomeEmail($user);
}

max(string $column): mixed

Returns the maximum value of a column across all matching rows. Returns null if no rows match.

$highestPrice = $app->db->table('products')->max('price');

// Max within a filtered set
$maxOrder = $app->db->table('orders')
    ->where('user_id', $userId)
    ->where('status', 'completed')
    ->max('amount');

// Most recent date
$lastLogin = $app->db->table('users')->max('last_login_at');

min(string $column): mixed

Returns the minimum value of a column across all matching rows. Returns null if no rows match.

$cheapest = $app->db->table('products')
    ->where('status', 'active')
    ->min('price');

// Earliest record date
$firstOrder = $app->db->table('orders')->where('user_id', $userId)->min('created_at');

sum(string $column): mixed

Returns the total sum of a numeric column across all matching rows. Returns 0 if no rows match.

// Total revenue
$revenue = $app->db->table('orders')
    ->where('status', 'completed')
    ->sum('amount');

// Revenue for a specific month
$monthRevenue = $app->db->table('orders')
    ->whereBetween('created_at', '2025-06-01', '2025-06-30')
    ->where('status', 'completed')
    ->sum('amount');

// Total items sold for a product
$unitsSold = $app->db->table('order_items')
    ->where('product_id', $productId)
    ->sum('quantity');

avg(string $column): mixed

Returns the average value of a numeric column across all matching rows. Returns null if no rows match.

$avgOrder = $app->db->table('orders')
    ->where('status', 'completed')
    ->avg('amount');

$avgRating = $app->db->table('reviews')
    ->where('product_id', $productId)
    ->avg('rating');
echo number_format($avgRating, 1) . ' / 5';

insert(array $data): bool

Inserts a new row into the table. Pass an associative array of column name => value pairs. Returns true on success.

$app->db->table('posts')->insert([
    'title'      => 'My First Post',
    'body'       => 'Hello world!',
    'user_id'    => $userId,
    'status'     => 'draft',
    'created_at' => date('Y-m-d H:i:s'),
]);

// Insert a log entry
$app->db->table('activity_log')->insert([
    'user_id'    => $userId,
    'action'     => 'login',
    'ip'         => $_SERVER['REMOTE_ADDR'],
    'created_at' => date('Y-m-d H:i:s'),
]);

insertGetId(array $data): string|false

Inserts a new row and returns the auto-increment ID of the newly created record. Returns false on failure. Use this whenever you need the new ID immediately after inserting.

// Register a user and use the new ID right away
$userId = $app->db->table('users')->insertGetId([
    'name'       => $_POST['name'],
    'email'      => strtolower($_POST['email']),
    'password'   => password_hash($_POST['password'], PASSWORD_BCRYPT),
    'created_at' => date('Y-m-d H:i:s'),
]);

// Create a profile row linked to the new user
$app->db->table('profiles')->insert([
    'user_id' => $userId,
    'bio'     => '',
    'avatar'  => 'default.png',
]);

// Create an order and then insert its items
$orderId = $app->db->table('orders')->insertGetId([
    'user_id'    => $userId,
    'amount'     => $total,
    'status'     => 'pending',
    'created_at' => date('Y-m-d H:i:s'),
]);

foreach ($cartItems as $item) {
    $app->db->table('order_items')->insert([
        'order_id'   => $orderId,
        'product_id' => $item['product_id'],
        'quantity'   => $item['quantity'],
        'price'      => $item['price'],
    ]);
}

insertOrIgnore(array $data): bool

Runs INSERT IGNORE. If the row violates a unique key constraint, the insert is silently skipped and no exception is thrown. Returns true on execution (even if skipped).

// Safe bulk import - skip duplicates without failing
foreach ($importedContacts as $contact) {
    $app->db->table('contacts')->insertOrIgnore([
        'email'      => $contact['email'],
        'name'       => $contact['name'],
        'created_at' => date('Y-m-d H:i:s'),
    ]);
}

// Ensure a unique tag is registered only once
$app->db->table('tags')->insertOrIgnore(['name' => strtolower($tagName)]);

upsert(array $data, array $uniqueKeys = []): bool

Runs INSERT ... ON DUPLICATE KEY UPDATE. If the row does not exist it is inserted; if a duplicate unique key is found, the non-unique columns are updated. Pass $uniqueKeys to exclude those columns from the UPDATE clause.

// Sync a product by SKU - insert if new, update price and stock if existing
$app->db->table('products')->upsert([
    'sku'        => 'WIDGET-PRO',
    'name'       => 'Widget Pro',
    'price'      => 29.99,
    'stock'      => 100,
    'updated_at' => date('Y-m-d H:i:s'),
], ['sku']); // 'sku' is unique - excluded from UPDATE

// Track daily stats - insert first visit, update count on repeat
$app->db->table('page_stats')->upsert([
    'page'       => '/home',
    'date'       => date('Y-m-d'),
    'view_count' => 1,
], ['page', 'date']);
// On duplicate: view_count is updated to 1 (use increment() for true counting)

update(array $data): int

Updates matching rows with the given data. Returns the number of affected rows. Always chain a where() before update() — calling it without conditions will update every row in the table.

// Update a single record
$app->db->table('users')
    ->where('id', $userId)
    ->update([
        'name'       => $_POST['name'],
        'updated_at' => date('Y-m-d H:i:s'),
    ]);

// Publish a post
$app->db->table('posts')
    ->where('id', $postId)
    ->update([
        'status'       => 'published',
        'published_at' => date('Y-m-d H:i:s'),
    ]);

// Bulk update - expire old unverified accounts
$affected = $app->db->table('users')
    ->where('status', 'unverified')
    ->where('created_at', '<', date('Y-m-d', strtotime('-30 days')))
    ->update(['status' => 'expired']);
echo $affected . ' accounts expired.';

increment(string $column, int $amount = 1): int

Atomically increments a numeric column by the given amount using a single SQL UPDATE col = col + n statement. Safer than read-modify-write. Returns the number of affected rows.

// Increment a post view counter by 1
$app->db->table('posts')
    ->where('id', $postId)
    ->increment('views');

// Add credits to a user account
$app->db->table('users')
    ->where('id', $userId)
    ->increment('credits', 50);

// Increment download count for a file
$app->db->table('files')
    ->where('id', $fileId)
    ->increment('download_count');

decrement(string $column, int $amount = 1): int

Atomically decrements a numeric column using UPDATE col = col - n. Returns the number of affected rows.

// Reduce stock when an order is placed
$app->db->table('products')
    ->where('id', $productId)
    ->decrement('stock', $quantity);

// Deduct credits from a user
$app->db->table('users')
    ->where('id', $userId)
    ->decrement('credits', 10);

delete(): int

Deletes all rows matching the current conditions. Returns the number of deleted rows. Always chain a where() — calling delete() without conditions removes every row in the table.

// Delete a specific record
$app->db->table('comments')
    ->where('id', $commentId)
    ->delete();

// Delete with multiple conditions
$deleted = $app->db->table('tokens')
    ->where('user_id', $userId)
    ->where('expires_at', '<', date('Y-m-d H:i:s'))
    ->delete();
echo $deleted . ' expired tokens removed.';

// Delete all notifications for a user
$app->db->table('notifications')
    ->where('user_id', $userId)
    ->where('read_at', '!=', null)
    ->delete();

truncate(): void

Runs TRUNCATE TABLE — removes every row and resets the auto-increment counter. Faster than DELETE for emptying a whole table. Use with extreme care in production.

// Wipe a temp import table before re-importing
$app->db->table('temp_imports')->truncate();

// Reset test data
$app->db->table('email_queue')->truncate();

beginTransaction(): void

Starts a database transaction. All queries executed after this call are held in a pending state until you call commit() or rollBack(). Always use inside a try/catch block.

$app->db->beginTransaction();
try {
    // ... multiple queries ...
    $app->db->commit();
} catch (Exception $e) {
    $app->db->rollBack();
}

commit(): void

Commits the current transaction, writing all pending changes to the database permanently.

$app->db->beginTransaction();
try {
    $app->db->table('accounts')->where('id', $from)->decrement('balance', $amount);
    $app->db->table('accounts')->where('id', $to)->increment('balance', $amount);
    $app->db->commit(); // both changes saved together
} catch (Exception $e) {
    $app->db->rollBack();
}

rollBack(): void

Rolls back the current transaction, discarding all pending changes made since beginTransaction() was called. Call this in the catch block to guarantee data consistency on failure.

$app->db->beginTransaction();
try {
    // Step 1
    $orderId = $app->db->table('orders')->insertGetId([...]);
    // Step 2
    foreach ($items as $item) {
        $app->db->table('order_items')->insert(['order_id' => $orderId, ...]);
        $app->db->table('products')->where('id', $item['id'])->decrement('stock', $item['qty']);
    }
    $app->db->commit();
} catch (Exception $e) {
    $app->db->rollBack(); // undo all steps if anything failed
    $app->logger->error('Order failed', ['error' => $e->getMessage()]);
    $app->exit(500, 'Order could not be placed.');
}

query(string $sql): static

Sets a raw SQL string to execute. Chain bind() for parameter values, then call get(), first(), or execute() to run it. Use this when the fluent builder cannot express the query you need.

// Simple raw select
$users = $app->db
    ->query('SELECT * FROM users WHERE status = :status')
    ->bind('status', 'active')
    ->get();

// Complex raw query with multiple bindings
$report = $app->db->query('
    SELECT u.name, COUNT(o.id) AS orders, SUM(o.amount) AS spent
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id AND o.status = :status
    WHERE u.created_at >= :since
    GROUP BY u.id
    HAVING spent > :min
    ORDER BY spent DESC
')->bind([
    'status' => 'completed',
    'since'  => '2025-01-01',
    'min'    => 500,
])->get();

// Raw write statement
$app->db->query('UPDATE products SET price = price * :factor WHERE category_id = :cat')
    ->bind(['factor' => 1.1, 'cat' => 3])
    ->execute();

bind(string|array $param, mixed $value): static

Binds a named parameter to a raw query set with query(). Accepts a single param => value pair or an associative array. The : prefix is added automatically if omitted. PDO types are detected automatically from the PHP value type.

// Single binding
$app->db->query('SELECT * FROM users WHERE id = :id')
    ->bind('id', 42)
    ->first();

// Array of bindings
$app->db->query('SELECT * FROM orders WHERE user_id = :uid AND amount > :min')
    ->bind(['uid' => $userId, 'min' => 100])
    ->get();

// With colon prefix (both forms accepted)
$app->db->query('SELECT * FROM posts WHERE slug = :slug')
    ->bind(':slug', $slug)
    ->first();

execute(): int

Executes the current query (built fluently or via query()) and returns the number of affected rows. Use for write statements (UPDATE, DELETE, INSERT) when you do not need the result set.

// Execute a raw update
$affected = $app->db
    ->query('UPDATE users SET status = :s WHERE last_login < :d')
    ->bind(['s' => 'inactive', 'd' => '2024-01-01'])
    ->execute();
echo $affected . ' users marked inactive.';

toSql(): string

Returns the SQL string that would be executed, without running the query. Useful for debugging to inspect what SQL is being generated.

$sql = $app->db->table('users')
    ->select(['id', 'name'])
    ->where('status', 'active')
    ->orderBy('name')
    ->limit(10)
    ->toSql();

echo $sql;
// SELECT id, name FROM users WHERE status = :status_0 ORDER BY name ASC LIMIT 10

chunk(int $size, callable $callback): void

Processes large result sets in batches of $size rows, passing each batch to the callback. Keeps memory usage constant regardless of total row count. Return false from the callback to stop processing early.

// Send newsletter to all active users without loading all into memory
$app->db->table('users')
    ->where('status', 'active')
    ->chunk(200, function($users) use ($mailer) {
        foreach ($users as $user) {
            $mailer->send($user['email'], 'Monthly Newsletter', $template);
        }
    });

// Export all orders to CSV in batches
$file = fopen('exports/orders.csv', 'w');
fputcsv($file, ['id', 'user_id', 'amount', 'status', 'created_at']);
$app->db->table('orders')->oldest()->chunk(500, function($rows) use ($file) {
    foreach ($rows as $row) {
        fputcsv($file, $row);
    }
});
fclose($file);

// Stop early when condition is met
$found = null;
$app->db->table('transactions')->oldest()->chunk(100, function($rows) use (&$found) {
    foreach ($rows as $row) {
        if ((float)$row['amount'] > 10000) {
            $found = $row;
            return false; // stops chunking
        }
    }
});

when(mixed $condition, callable $callback): static

Applies the callback to the query builder only when $condition is truthy. Keeps query-building code clean when filters come from optional user input or config flags.

// Apply optional filters from GET parameters
$status   = $_GET['status']   ?? null;
$category = $_GET['category'] ?? null;
$search   = $_GET['search']   ?? null;
$fromDate = $_GET['from']     ?? null;

$products = $app->db->table('products')
    ->when($status,   fn($q) => $q->where('status', $status))
    ->when($category, fn($q) => $q->where('category_id', $category))
    ->when($search,   fn($q) => $q->where('name', 'LIKE', '%' . $search . '%'))
    ->when($fromDate, fn($q) => $q->where('created_at', '>=', $fromDate))
    ->orderBy('name')
    ->get();
// Only conditions with non-null values are added to the query

// Conditional ordering
$orderByRating = (bool)($_GET['top_rated'] ?? false);
$app->db->table('products')
    ->where('status', 'active')
    ->when($orderByRating, fn($q) => $q->orderByDesc('rating'), )
    ->when(!$orderByRating, fn($q) => $q->orderBy('name'))
    ->get();

lastInsertId(): string

Returns the ID generated by the last INSERT statement on the connection. In most cases, use insertGetId() instead — it calls this internally. Use lastInsertId() directly only after a raw query() + execute() insert.

// After a raw insert via query()
$app->db->query('INSERT INTO logs (message, created_at) VALUES (:msg, NOW())')
    ->bind('msg', 'App started')
    ->execute();

$logId = $app->db->lastInsertId();

A realistic reporting query combining JOINs, GROUP BY, HAVING, date filtering, and multiple aggregates:

<?php
require __DIR__ . '/vendor/autoload.php';
$app = new App();

$from = $_GET['from'] ?? date('Y-m-01');
$to   = $_GET['to']   ?? date('Y-m-t');

// Top 10 products by revenue this period
$topProducts = $app->db->table('order_items')
    ->select([
        'products.id',
        'products.name',
        'products.sku',
    ])
    ->selectRaw('SUM(order_items.quantity) AS units_sold, SUM(order_items.price * order_items.quantity) AS revenue')
    ->join('orders',   'order_items.order_id',  '=', 'orders.id')
    ->join('products', 'order_items.product_id', '=', 'products.id')
    ->where('orders.status', 'completed')
    ->whereBetween('orders.created_at', $from . ' 00:00:00', $to . ' 23:59:59')
    ->groupBy('products.id')
    ->having('revenue', '>', 0)
    ->orderByDesc('revenue')
    ->take(10)
    ->get();

// Daily revenue breakdown
$dailyRevenue = $app->db->table('orders')
    ->selectRaw('DATE(created_at) AS day, COUNT(*) AS orders, SUM(amount) AS revenue')
    ->where('status', 'completed')
    ->whereBetween('created_at', $from . ' 00:00:00', $to . ' 23:59:59')
    ->groupBy('day')
    ->orderBy('day')
    ->get();

// Period summary totals
$summary = $app->db->table('orders')
    ->selectRaw('COUNT(*) AS total_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order')
    ->where('status', 'completed')
    ->whereBetween('created_at', $from, $to)
    ->first();

header('Content-Type: application/json');
echo json_encode(compact('summary', 'topProducts', 'dailyRevenue'));

A search and filter endpoint with pagination using when() and paginate(). The single paginate() call handles the count query and data query automatically — all when() conditions are applied to both.

<?php
require __DIR__ . '/vendor/autoload.php';
$app = new App();

$search   = trim($_GET['search']      ?? '');
$status   = $_GET['status']           ?? null;
$category = $_GET['category_id']      ?? null;
$minPrice = $_GET['min_price']        ?? null;
$maxPrice = $_GET['max_price']        ?? null;

$result = $app->db->table('products')
    ->select(['products.id', 'products.name', 'products.price', 'products.stock', 'products.status', 'categories.name AS category'])
    ->leftJoin('categories', 'products.category_id', '=', 'categories.id')
    ->whereNull('products.deleted_at')
    ->when($search,   fn($q) => $q->where('products.name', 'LIKE', '%' . $search . '%'))
    ->when($status,   fn($q) => $q->where('products.status', $status))
    ->when($category, fn($q) => $q->where('products.category_id', $category))
    ->when($minPrice, fn($q) => $q->where('products.price', '>=', (float)$minPrice))
    ->when($maxPrice, fn($q) => $q->where('products.price', '<=', (float)$maxPrice))
    ->orderBy('products.name')
    ->paginate(24);

header('Content-Type: application/json');
echo json_encode([
    'data'         => $result->data,
    'current_page' => $result->currentPage,
    'total_pages'  => $result->totalPages,
    'total_items'  => $result->totalItems,
    'per_page'     => $result->perPage,
]);

For a full HTML page with rendered navigation links, replace the JSON output with:

// In a view file
echo $app->paginator->renderLinks($result, '/products');

A full order placement flow using transactions, stock validation, atomic decrements, and a rollback on failure:

<?php
require __DIR__ . '/vendor/autoload.php';
$app = new App();

header('Content-Type: application/json');

$userId = $app->auth->getUserId();
if (!$userId) {
    $app->exit(401, json_encode(['error' => 'Not authenticated']));
}

$body  = json_decode(file_get_contents('php://input'), true);
$items = $body['items'] ?? [];

if (empty($items)) {
    $app->exit(400, json_encode(['error' => 'Cart is empty']));
}

// Load all products in a single query
$productIds = array_column($items, 'product_id');
$products   = $app->db->table('products')
    ->whereIn('id', $productIds)
    ->get();
$map = array_column($products, null, 'id');

// Validate stock before touching the database
$total = 0;
foreach ($items as $item) {
    $product = $map[$item['product_id']] ?? null;
    if (!$product) {
        $app->exit(404, json_encode(['error' => 'Product ' . $item['product_id'] . ' not found']));
    }
    if ($product['stock'] < $item['quantity']) {
        $app->exit(409, json_encode(['error' => 'Insufficient stock for: ' . $product['name']]));
    }
    $total += $product['price'] * $item['quantity'];
}

// Place order inside a transaction
$app->db->beginTransaction();
try {
    $orderId = $app->db->table('orders')->insertGetId([
        'user_id'    => $userId,
        'amount'     => $total,
        'status'     => 'pending',
        'created_at' => date('Y-m-d H:i:s'),
    ]);

    foreach ($items as $item) {
        $app->db->table('order_items')->insert([
            'order_id'   => $orderId,
            'product_id' => $item['product_id'],
            'quantity'   => $item['quantity'],
            'price'      => $map[$item['product_id']]['price'],
        ]);
        $app->db->table('products')
            ->where('id', $item['product_id'])
            ->decrement('stock', $item['quantity']);
    }

    $app->db->commit();
    $app->logger->info('Order placed', ['order_id' => $orderId, 'total' => $total]);
    $app->exit(201, json_encode(['order_id' => $orderId, 'total' => $total]));

} catch (Exception $e) {
    $app->db->rollBack();
    $app->logger->error('Order failed', ['error' => $e->getMessage()]);
    $app->exit(500, json_encode(['error' => 'Order could not be placed']));
}

paginate(int $perPage, ?int $page = null): object

Paginate the query in one call. Runs two queries automatically: a COUNT(*) with all WHERE / JOIN / GROUP BY conditions, then a SELECT with LIMIT and OFFSET. Returns a unified result object that works directly with $app->paginator->renderLinks().

• $perPage — (int) Number of rows per page.
• $page — (int|null) Current page number. Reads $_GET['page'] automatically when null.

Returns: object with the following properties:

• data — (array) The rows for the current page.
• currentPage, totalPages, totalItems, perPage, offset — pagination metadata.
• hasNext, hasPrevious — (bool) navigation flags.
• nextPage, previousPage — (int) adjacent page numbers.
• queryParams — (string) current $_GET params (minus page) encoded for use in links.

See the Paginator page for full documentation of the result object and HTML rendering.

// Reads page from $_GET['page'] automatically
$result = $app->db->table('products')
    ->where('active', 1)
    ->orderBy('name')
    ->paginate(20);

foreach ($result->data as $product) { ... }

echo "Page {$result->currentPage} of {$result->totalPages} ({$result->totalItems} total)";
echo $app->paginator->renderLinks($result, '/products');

// Explicit page number (e.g. from a REST API route)
$result = $app->db->table('orders')->paginate(15, 3); // page 3

// All query builder features work before paginate()
$result = $app->db->table('posts')
    ->select(['posts.*', 'users.name AS author'])
    ->leftJoin('users', 'posts.user_id', '=', 'users.id')
    ->where('posts.published', 1)
    ->when($search, fn($q) => $q->where('posts.title', 'LIKE', '%' . $search . '%'))
    ->orderByDesc('posts.created_at')
    ->paginate(10);