Essentials

Database

Query and manipulate data

Glueful provides a powerful query builder for working with your database. Write clean, readable queries without raw SQL.

Quick Start

Access the query builder via the db property in controllers (or through repositories if you created them). The builder intentionally keeps a small surface area – some convenience methods you might expect from other frameworks (like pluck, increment, decrement, truncate, insertMany) are intentionally omitted for explicitness.

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

// Find a single record
$user = $this->db->table('users')
    ->where('id', 1)
    ->first();

// UUID example
$userByUuid = $this->db->table('users')
    ->where('uuid', 'abc123')
    ->first();

// Insert data (returns inserted primary key if available)
$userId = $this->db->table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]'
]);

Retrieving Data

Get All Records

$users = $this->db->table('users')->get();

Get First Record

$user = $this->db->table('users')->first();

// With where clause
$user = $this->db->table('users')
    ->where('email', '[email protected]')
    ->first();

Find by Primary Key

There is no dedicated find() shortcut on the builder. Use a where + first:

$user = $this->db->table('users')
    ->where('id', 1)
    ->first();

// UUID primary (common in Glueful apps)
$user = $this->db->table('users')
    ->where('uuid', $uuid)
    ->first();

Get Specific Columns

$users = $this->db->table('users')
    ->select(['id', 'name', 'email'])
    ->get();

// Or using individual calls
$users = $this->db->table('users')
    ->select('id')
    ->select('name')
    ->select('email')
    ->get();

Selecting Value Lists

Use a normal select and map the result:

$rows = $this->db->table('users')
    ->select(['email'])
    ->get();
$emails = array_map(fn($r) => $r['email'], $rows);

Where Clauses

Basic Where

$users = $this->db->table('users')
    ->where('status', 'active')
    ->get();

// Multiple conditions
$users = $this->db->table('users')
    ->where('status', 'active')
    ->where('role', 'admin')
    ->get();

Operators

$users = $this->db->table('users')
    ->where('age', '>=', 18)
    ->get();

$posts = $this->db->table('posts')
    ->where('views', '>', 1000)
    ->get();

$products = $this->db->table('products')
    ->where('price', '<=', 100)
    ->get();

Or Where

$users = $this->db->table('users')
    ->where('role', 'admin')
    ->orWhere('role', 'moderator')
    ->get();

Where In

$users = $this->db->table('users')
    ->whereIn('id', [1, 2, 3, 4, 5])
    ->get();

$users = $this->db->table('users')
    ->whereNotIn('status', ['banned', 'suspended'])
    ->get();

Where Null

$users = $this->db->table('users')
    ->whereNull('deleted_at')
    ->get();

$deletedUsers = $this->db->table('users')
    ->whereNotNull('deleted_at')
    ->get();

Where Between

$products = $this->db->table('products')
    ->whereBetween('price', [10, 100])
    ->get();

Like Queries

$users = $this->db->table('users')
    ->where('name', 'LIKE', '%John%')
    ->get();

// Case-insensitive (PostgreSQL)
$users = $this->db->table('users')
    ->where('name', 'ILIKE', '%john%')
    ->get();

Ordering & Limiting

Order By

$users = $this->db->table('users')
    ->orderBy('created_at', 'desc')
    ->get();

// Multiple orders
$users = $this->db->table('users')
    ->orderBy('status', 'asc')
    ->orderBy('created_at', 'desc')
    ->get();

Limit & Offset

// Get first 10 users
$users = $this->db->table('users')
    ->limit(10)
    ->get();

// Pagination
$page = 2;
$perPage = 20;
$users = $this->db->table('users')
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->get();

Aggregates

Count

$count = $this->db->table('users')->count();

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

Sum, Average, Min, Max

$total = $this->db->table('orders')->sum('total');

$average = $this->db->table('products')->avg('price');

$min = $this->db->table('products')->min('price');

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

Inserting Data

Insert Single Record

$userId = $this->db->table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => password_hash('secret', PASSWORD_DEFAULT)
]);

// Returns the inserted ID

Insert Multiple Records

insertMany() is not implemented. Loop explicitly (or wrap in a transaction if needed):

foreach ($batch as $row) {
    $this->db->table('users')->insert($row);
}

Updating Data

Update Records

$affected = $this->db->table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Smith',
        'updated_at' => date('Y-m-d H:i:s')
    ]);

// Returns number of affected rows

Update Multiple

$this->db->table('users')
    ->where('status', 'pending')
    ->update(['status' => 'active']);

Numeric Adjustments

increment() / decrement() helpers are not implemented. Perform a read‑modify‑write safely inside a transaction if race conditions matter:

$this->db->beginTransaction();
try {
    $post = $this->db->table('posts')
        ->where('id', 1)
        ->first();
    if ($post) {
        $this->db->table('posts')
            ->where('id', 1)
            ->update(['views' => $post['views'] + 1]);
    }
    $this->db->commit();
} catch (\Throwable $e) {
    $this->db->rollback();
    throw $e;
}

Deleting Data

Delete Records

$deleted = $this->db->table('users')
    ->where('id', 1)
    ->delete();

// Delete multiple
$this->db->table('users')
    ->where('status', 'banned')
    ->delete();

Clearing a Table

truncate() is not implemented. Use a guarded bulk delete (be careful):

// Dangerous: deletes everything
$this->db->table('temp_data')->delete();

// Or add a safety check
if ($confirm) {
    $this->db->table('temp_data')->delete();
}

Joins

Inner Join

$posts = $this->db->table('posts')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->select([
        'posts.*',
        'users.name as author_name'
    ])
    ->get();

Left Join

$users = $this->db->table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->select([
        'users.*',
        'COUNT(posts.id) as post_count'
    ])
    ->groupBy('users.id')
    ->get();

Group By & Having

$stats = $this->db->table('orders')
    ->select([
        'user_id',
        'COUNT(*) as order_count',
        'SUM(total) as total_spent'
    ])
    ->groupBy('user_id')
    ->having('order_count', '>', 5)
    ->get();

Transactions

Wrap related changes atomically:

$this->db->beginTransaction();
try {
    $from = $this->db->table('accounts')->where('id', 1)->first();
    $to   = $this->db->table('accounts')->where('id', 2)->first();

    if (! $from || ! $to) {
        throw new RuntimeException('Account missing');
    }
    if ($from['balance'] < 100) {
        throw new RuntimeException('Insufficient funds');
    }

    $this->db->table('accounts')->where('id', 1)->update([
        'balance' => $from['balance'] - 100,
    ]);

    $this->db->table('accounts')->where('id', 2)->update([
        'balance' => $to['balance'] + 100,
    ]);

    $this->db->commit();
} catch (\Throwable $e) {
    $this->db->rollback();
    throw $e;
}

Raw Queries

The query builder exposes multiple raw capabilities. Use them sparingly and NEVER concatenate untrusted input directly into SQL strings.

Raw Expressions in Select / Order / Having

$users = $this->db->table('users')
    ->select(['id', 'email'])
    ->selectRaw("CONCAT(first_name, ' ', last_name) AS full_name")
    ->orderByRaw('created_at DESC')
    ->get();

You can also pass raw snippets via $this->db->raw('...') where an expression object is accepted (e.g. some builder internals) – it returns a RawExpression instance.

Raw WHERE / HAVING Conditions

$recent = $this->db->table('orders')
    ->whereRaw('created_at > ?', ['2025-01-01'])
    ->havingRaw('SUM(total) > ?', [5000])
    ->groupBy('user_id')
    ->get();

Executing Arbitrary SELECT Statements

$report = $this->db->table('users') // table() just sets context; not strictly needed for executeRaw
    ->executeRaw(
        'SELECT role, COUNT(*) AS total FROM users WHERE status = ? GROUP BY role HAVING COUNT(*) > ?',
        ['active', 10]
    );

Executing Modification Statements

$affected = $this->db->table('users')->executeModification(
    'UPDATE users SET last_login = ? WHERE id = ?',
    [date('Y-m-d H:i:s'), $userId]
);

First Row Shortcut

$first = $this->db->table('users')->executeRawFirst(
    'SELECT * FROM users WHERE email = ? LIMIT 1',
    [$email]
);

Security Guidance

  1. Always use positional or named bindings (? or :name) for dynamic values.
  2. Treat raw SQL as a last resort—prefer composable builder methods first.
  3. Keep raw SQL inside repositories/services; avoid scattering it across controllers.
  4. Validate or whitelist column names when constructing dynamic ORDER BY / GROUP BY pieces.
  5. Never log full raw queries with secrets; scrub sensitive data.

Common Patterns

Pagination

If you have a repository abstraction, prefer its paginate() (if implemented). Otherwise manually combine limit, offset, count:

public function index()
{
    $page = (int) $this->request->query->get('page', 1);
    $perPage = (int) $this->request->query->get('per_page', 20);

    $query = $this->db->table('users')
        ->orderBy('created_at', 'desc');

    $items = $query
        ->limit($perPage)
        ->offset(($page - 1) * $perPage)
        ->get();

    $total = $this->db->table('users')->count();

    return Response::paginated($items, $total, $page, $perPage);
}

Search & Filter

public function search()
{
    $query = $this->db->table('products');

    $search   = $this->request->query->get('q');
    $category = $this->request->query->get('category');
    $minPrice = $this->request->query->get('min_price');
    $maxPrice = $this->request->query->get('max_price');
    $sortBy   = $this->request->query->get('sort', 'created_at');
    $sortDir  = $this->request->query->get('order', 'desc');

    if ($search) {
        $query->where('name', 'LIKE', "%{$search}%");
    }
    if ($category) {
        $query->where('category_id', $category);
    }
    if ($minPrice) {
        $query->where('price', '>=', $minPrice);
    }
    if ($maxPrice) {
        $query->where('price', '<=', $maxPrice);
    }

    $query->orderBy($sortBy, $sortDir);

    return Response::success($query->get());
}

Soft Deletes

// Soft delete (set deleted_at timestamp)
$this->db->table('posts')
    ->where('id', 1)
    ->update(['deleted_at' => date('Y-m-d H:i:s')]);

// Query only non-deleted
$posts = $this->db->table('posts')
    ->whereNull('deleted_at')
    ->get();

// Include deleted
$allPosts = $this->db->table('posts')->get();

// Only deleted
$deletedPosts = $this->db->table('posts')
    ->whereNotNull('deleted_at')
    ->get();

Best Practices

Use Parameter Binding

// ✅ Safe - uses parameter binding
$user = $this->db->table('users')
    ->where('email', $email)
    ->first();

// ❌ Dangerous - SQL injection risk
$email = $_POST['email'];
$result = $this->db->raw("SELECT * FROM users WHERE email = '{$email}'");

Select Only Needed Columns

// ✅ Efficient
$users = $this->db->table('users')
    ->select(['id', 'name', 'email'])
    ->get();

// ❌ Wasteful if you only need name/email
$users = $this->db->table('users')->get();

Use Transactions for Related Changes

// ✅ Atomic operation – explicit loop instead of insertMany()
$this->db->beginTransaction();
try {
    $orderId = $this->db->table('orders')->insert($orderData);
    foreach ($items as $item) {
        $this->db->table('order_items')->insert($item + ['order_id' => $orderId]);
    }
    $this->db->commit();
} catch (\Throwable $e) {
    $this->db->rollback();
    throw $e;
}

Troubleshooting

Query returns empty?

  • Check your where conditions
  • Verify table and column names
  • Check if data actually exists in database

Performance issues?

  • Add indexes on frequently queried columns
  • Use select() to limit columns
  • Paginate large result sets

Transaction fails?

  • Ensure all queries in transaction are valid
  • Check for constraint violations
  • Verify table supports transactions (InnoDB for MySQL)

Next Steps

Controllers

Organize request handling logic

Validation

Validate user input using rule objects