Essentials

Database

Query and manipulate data

Glueful provides a fluent query builder on top of PDO (MySQL, PostgreSQL, SQLite). Write clean, readable queries without raw SQL, with typed failures and automatic soft-delete handling built in.

Quick Start

Everything starts from a Glueful\Database\Connection. Resolve it with the db($context) helper, or inject it into your class constructor. Inside a controller that extends BaseController, the application context is available as $this->context:

use Glueful\Database\Connection;

// In a controller extending BaseController
$db = db($this->context);

// Or inject it
final class UserService
{
    public function __construct(private readonly Connection $db) {}
}

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

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

// Insert and get the new primary key
$userId = $db->table('users')->insertGetId([
    'name' => 'John Doe',
    'email' => '[email protected]',
]);

table() returns a fresh QueryBuilder each time. Rows come back as associative arrays. The examples below assume $db is a Connection.

Retrieving Data

Get All / First

$users = $db->table('users')->get();     // list of rows
$user  = $db->table('users')->first();   // ?array

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

Find by Primary Key

The builder has no find() shortcut; use where() + first() (or the ORM's Model::find($context, $id), see ORM Models):

$user = $db->table('users')->where('uuid', $uuid)->first();

Specific Columns

select() takes an array and replaces the column list:

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

$tags = $db->table('tags')->distinct()->select(['name'])->get();

Value Lists

$emails = $db->table('users')->pluck('email');            // ['[email protected]', '[email protected]']
$names  = $db->table('users')->pluck('name', 'uuid');     // ['uuid-1' => 'Ann', ...]

$exists = $db->table('users')->where('email', $email)->exists();

Where Clauses

Basic Where

$users = $db->table('users')
    ->where('status', 'active')     // two-argument form means "="
    ->where('role', 'admin')
    ->get();

// Array form: column => value pairs (null becomes IS NULL)
$users = $db->table('users')->where(['status' => 'active', 'role' => 'admin'])->get();

Operators

Supported operators: =, !=, <>, <, >, <=, >=, LIKE, NOT LIKE, ILIKE (PostgreSQL), REGEXP/RLIKE (MySQL).

$adults   = $db->table('users')->where('age', '>=', 18)->get();
$popular  = $db->table('posts')->where('views', '>', 1000)->get();

Or Where and Grouping

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

// Closures create a parenthesised group: status = ? AND (role = ? OR role = ?)
$staff = $db->table('users')
    ->where('status', 'active')
    ->where(function ($q) {
        $q->where('role', 'admin')->orWhere('role', 'moderator');
    })
    ->get();

Where In / Null / Between

$db->table('users')->whereIn('id', [1, 2, 3])->get();
$db->table('users')->whereNotIn('status', ['banned', 'suspended'])->get();

$db->table('users')->whereNull('email_verified_at')->get();
$db->table('users')->whereNotNull('email_verified_at')->get();

// whereBetween takes min and max as separate arguments
$db->table('products')->whereBetween('price', 10, 100)->get();

Text Search

// Raw LIKE pattern — you supply the wildcards
$db->table('users')->whereLike('name', 'John%')->get();

// Case-insensitive on every driver; % and _ in the term are matched literally
$db->table('products')->whereContains('name', $search)->get();
$db->table('products')->whereStartsWith('sku', 'ABC-')->get();
$db->table('files')->whereEndsWith('filename', '.pdf')->get();
$db->table('posts')->whereContains('title', $q)->orWhereContains('body', $q)->get();

Prefer whereContains() for user-supplied search terms: it escapes wildcards for you, so a term like 50% doesn't turn into a pattern.

JSON Columns

// Value inside a JSON array column
$db->table('posts')->whereJsonContains('tags', 'php')->get();

// Value at a JSON path
$db->table('users')->whereJsonContains('metadata', 'active', '$.status')->get();

Ordering & Limiting

$users = $db->table('users')
    ->orderBy('status', 'asc')
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->offset(20)
    ->get();

$random = $db->table('quotes')->orderByRandom()->limit(1)->first();

Pagination

paginate() runs the count and page queries for you:

$result = $db->table('users')
    ->where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->paginate(page: 2, perPage: 20);

// $result = [
//   'data' => [...], 'current_page' => 2, 'per_page' => 20, 'total' => 134,
//   'last_page' => 7, 'has_more' => true, 'from' => 21, 'to' => 40,
//   'execution_time_ms' => 3,
// ]

return Response::paginated(
    $result['data'],
    $result['total'],
    $result['current_page'],
    $result['per_page']
);

Aggregates

The builder ships count(), exists() and max(). For other aggregates, use selectRaw():

$count  = $db->table('users')->where('status', 'active')->count();
$latest = $db->table('orders')->max('created_at');

$stats = $db->table('orders')
    ->selectRaw('SUM(total) AS revenue, AVG(total) AS avg_order, MIN(total) AS smallest')
    ->where('status', 'paid')
    ->first();

Inserting Data

// insert() returns the number of affected rows
$db->table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
]);

// insertGetId() returns the generated primary key
$id = $db->table('users')->insertGetId(['name' => 'Jane', 'email' => '[email protected]']);

// Multiple rows in one statement (every row must have the same columns)
$db->table('tags')->insertBatch([
    ['name' => 'php'],
    ['name' => 'api'],
]);

// Insert, or update the listed columns on a unique/primary-key conflict
$db->table('settings')->upsert(
    ['key' => 'theme', 'value' => 'dark'],
    ['value']
);

Updating Data

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

There are no increment()/decrement() helpers. For an atomic counter, use a single modification statement instead of a read-modify-write:

$db->query()->executeModification(
    'UPDATE posts SET views = views + 1 WHERE id = ?',
    [$postId]
);

Deleting Data

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

delete() without any where() throws (the query validator runs in strict mode). To clear a whole table on purpose, issue the statement explicitly: $db->query()->executeModification('DELETE FROM temp_data').

Soft Deletes

Soft deletes are automatic for any table with a deleted_at column. No configuration needed:

// Sets deleted_at instead of removing the row
$db->table('posts')->where('id', 1)->delete();

// Reads exclude soft-deleted rows by default
$posts = $db->table('posts')->get();

$all     = $db->table('posts')->withTrashed()->get();
$trashed = $db->table('posts')->onlyTrashed()->get();

// Undo, or remove permanently
$db->table('posts')->where('id', 1)->restore();
$db->table('posts')->where('id', 1)->forceDelete();

Tables without a deleted_at column get a real DELETE.

Joins

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

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

rightJoin() is also available.

Group By & Having

having() takes an array of column => value equality conditions. For comparisons, use havingRaw() with bindings:

$stats = $db->table('orders')
    ->select(['user_id'])
    ->selectRaw('COUNT(*) AS order_count, SUM(total) AS total_spent')
    ->groupBy('user_id')
    ->havingRaw('COUNT(*) > ?', [5])
    ->get();

$exact = $db->table('orders')
    ->select(['status'])
    ->selectRaw('COUNT(*) AS n')
    ->groupBy('status')
    ->having(['status' => 'paid'])
    ->get();

Transactions

Use transaction(). It commits when the callback returns, rolls back when it throws, and returns the callback's value. Nested calls use savepoints.

$orderId = $db->transaction(function () use ($db, $orderData, $items) {
    $orderId = $db->table('orders')->insertGetId($orderData);

    $db->table('order_items')->insertBatch(array_map(
        fn (array $item) => $item + ['order_id' => $orderId],
        $items
    ));

    return $orderId;
});

Deadlocks, serialization failures, lock contention and connection losses that provably happened before commit are retried automatically, so the callback may run more than once. Build your queries inside the callback from $db, and keep side effects (emails, HTTP calls, events) out of it. Defer them with afterCommit():

$db->transaction(function () use ($db, $user) {
    $db->table('users')->where('id', $user['id'])->update(['status' => 'active']);

    $db->afterCommit(fn () => $this->events->dispatch(new UserActivated($user['uuid'])));
    $db->afterRollback(fn () => $this->logger->warning('Activation rolled back'));
});

$db->withinTransaction();   // bool
$db->transactionLevel();    // 0 when idle, 1+ when nested

afterCommit() runs immediately when no transaction is open. Inside nested transactions, its callbacks only fire when the outermost transaction commits. Callbacks registered inside a nested transaction that rolls back are discarded.

Manual Transaction Control

Prefer transaction(). When you need explicit boundaries, drive the connection's TransactionManager. A begin() inside an open transaction creates a savepoint, and the matching rollback() returns to it without abandoning the outer transaction:

$tm = $db->getTransactionManager();

$tm->begin();
try {
    $db->table('users')->insert($userData);

    $tm->begin();                       // savepoint
    try {
        $db->table('user_preferences')->insert($prefs);
        $tm->commit();                  // release the savepoint
    } catch (\Throwable $e) {
        $tm->rollback();                // back to the savepoint; outer work survives
    }

    $tm->commit();                      // real COMMIT
} catch (\Throwable $e) {
    $tm->rollback();
    throw $e;
}

Manual control gets no automatic retry. Only transaction() replays on deadlocks and connection loss.

Raw Queries

Use raw SQL sparingly and never concatenate untrusted input into it. Always bind values.

// Raw expressions inside a builder query
$users = $db->table('users')
    ->select(['id', 'email'])
    ->selectRaw("CONCAT(first_name, ' ', last_name) AS full_name")
    ->whereRaw('created_at > ?', ['2025-01-01'])
    ->orderByRaw('created_at DESC')
    ->get();

// Arbitrary statements (query() gives a builder with no table)
$report = $db->query()->executeRaw(
    'SELECT role, COUNT(*) AS total FROM users WHERE status = ? GROUP BY role',
    ['active']
);
$first    = $db->query()->executeRawFirst('SELECT * FROM users WHERE email = ? LIMIT 1', [$email]);
$affected = $db->query()->executeModification('UPDATE users SET last_login = ? WHERE id = ?', [date('Y-m-d H:i:s'), $id]);

// Inspect without running
$sql      = $db->table('users')->where('status', 'active')->toSql();
$bindings = $db->table('users')->where('status', 'active')->getBindings();

Bindings protect values only, not identifiers, operators or sort directions. Allowlist dynamic column names before passing them to orderBy() or groupBy().

selectRaw() is also the way to use window functions (MySQL 8+, PostgreSQL, SQLite 3.25+):

$sales = $db->table('sales')
    ->select(['date', 'region', 'amount'])
    ->selectRaw('SUM(amount) OVER (PARTITION BY region) AS region_total')
    ->selectRaw('RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank')
    ->selectRaw('SUM(amount) OVER (ORDER BY date) AS running_total')
    ->whereBetween('date', $from, $to)
    ->orderBy('date')
    ->get();

Error Handling

Database failures are thrown as typed exceptions from Glueful\Database\Exceptions. They all extend DatabaseException, which extends \PDOException, so existing catch (\PDOException) blocks still work.

Exception	When
`UniqueConstraintViolationException`	Duplicate key
`ForeignKeyConstraintViolationException`	Missing/referenced parent row
`NotNullConstraintViolationException`	`NULL` into a `NOT NULL` column
`ConstraintViolationException`	Parent of the three above, and any other constraint failure (e.g. `CHECK`)
`DeadlockException`, `SerializationFailureException`, `LockContentionException`	Retryable transaction failures
`ConnectionLostException`	Server gone away / connection dropped
`CommitOutcomeUnknownException`	Connection lost during COMMIT. Never replayed, because the commit may have succeeded

use Glueful\Database\Exceptions\UniqueConstraintViolationException;

try {
    $db->table('users')->insert(['email' => $email]);
} catch (UniqueConstraintViolationException $e) {
    return Response::error('That email is already registered', 409);
}

Each exception exposes sqlState(), driverCode() and driver(). If you don't catch a unique violation, it renders as HTTP 409 with a generic conflict message.

The subclasses are raised on every driver. SQLite reports constraint failures as a bare SQLITE_CONSTRAINT, so Glueful classifies them by SQLite's fixed message prefixes (UNIQUE constraint failed, FOREIGN KEY constraint failed, NOT NULL constraint failed). Since 1.91, a duplicate key on SQLite is therefore a UniqueConstraintViolationException and a 409, where it used to be a generic ConstraintViolationException and a 500.

Retries and Reconnects

transaction() shares one retry budget across deadlocks and connection losses. It is configured in config/database.php under retry:

DB_RETRY_MAX_ATTEMPTS=3     # total attempts (default 3)
DB_RETRY_BACKOFF_MS=500     # linear backoff base (default 500ms)

These are separate from DB_POOL_RETRY_*, which only govern acquiring a pooled connection.

Plain reads outside a transaction are never replayed on their own. When you know a read is safe to repeat, opt in with idempotentRead(), which reconnects and re-runs it after a connection loss:

$rows = $db->idempotentRead(
    fn (Connection $c) => $c->table('reports')->where('day', $day)->get()
);

idempotentRead() throws a LogicException if called inside a transaction.

Connection Pooling

Pooling is on by default (DB_POOLING_ENABLED=true). Connection borrows a PDO from a per-engine pool, so you keep using db($context) / an injected Connection and never touch the pool directly. The settings live in config/database.php under pooling: defaults apply to every engine, and engines.{mysql,pgsql,sqlite} override them.

Key (`pooling.defaults.*`)	Env	Default
`min_connections` / `max_connections`	`DB_POOL_MIN_CONNECTIONS` / `DB_POOL_MAX_CONNECTIONS`	2 / 10
`idle_timeout`	`DB_POOL_IDLE_TIMEOUT`	300 s
`max_lifetime`	`DB_POOL_MAX_LIFETIME`	3600 s
`acquisition_timeout`	`DB_POOL_ACQUIRE_TIMEOUT`	30 s
`health_check_interval` / `health_check_timeout`	`DB_POOL_HEALTH_CHECK_INTERVAL` / `DB_POOL_HEALTH_CHECK_TIMEOUT`	60 s / 5 s
`max_use_count`	`DB_POOL_MAX_USE_COUNT`	1000
`retry_attempts` / `retry_delay`	`DB_POOL_RETRY_ATTEMPTS` / `DB_POOL_RETRY_DELAY`	3 / 100 ms

Per-engine overrides: DB_MYSQL_POOL_MIN/DB_MYSQL_POOL_MAX (5/20) and DB_PGSQL_POOL_MIN/DB_PGSQL_POOL_MAX (3/15). SQLite is fixed at one connection (single writer).

For monitoring, read the pool manager's statistics. Connection::getPoolManager() returns null when pooling is off or before the first Connection is built:

use Glueful\Database\Connection;

$pools = Connection::getPoolManager();
if ($pools !== null) {
    $pools->getStats();           // per engine: active/idle/total connections, acquisitions, timeouts, health checks
    $pools->getAggregateStats();  // the same counters summed across engines
    $pools->getHealthStatus();    // per engine: healthy, health_check_failure_rate, timeout_rate (percentages)
}

ORM Models

For entity-style access, extend Glueful\Database\ORM\Model. The ORM guide covers models, relationships, eager loading and the N+1 detector in full; the short version is that every static entry point takes the context first:

use Glueful\Database\ORM\Model;
use Glueful\Database\ORM\Relations\HasMany;

class User extends Model
{
    protected string $table = 'users';
    protected array $fillable = ['name', 'email'];

    public function posts(): HasMany
    {
        return $this->hasMany(Post::class);
    }
}

$user  = User::find($context, 1);
$users = User::query($context)->where('status', 'active')->orderBy('name')->get();
$users = User::with($context, 'posts')->where('status', 'active')->get(); // eager load
$user  = User::create($context, ['name' => 'Ann', 'email' => '[email protected]']);

The ORM has an N+1 detector for lazily loaded relations. Set it with DB_LAZY_LOADING_MODE:

Mode	Behaviour
`auto` (default)	`warn` in development, `off` otherwise
`warn`	Logs a `[GLUEFUL-N+1]` warning
`strict`	Throws `LazyLoadingViolationException`
`off`	Disabled

The fix is almost always to eager load the relation with with(). See ORM for the rest.

Best Practices

Bind, don't interpolate: ->where('email', $email) is safe. A string-built whereRaw("email = '$email'") is not.
Select only what you need: ->select(['id', 'name']) instead of SELECT * on wide tables.
Batch writes: insertBatch() instead of looping insert().
Keep transactions pure: queries only, and side effects in afterCommit(), because callbacks can be replayed.
Use whereContains() for search input so % and _ stay literal.

Troubleshooting

Query returns empty?

Check your where conditions and table/column names
Rows with deleted_at set are hidden by default; try withTrashed()

"DELETE without WHERE clause is dangerous"?

Add a where(), or run an explicit executeModification('DELETE FROM ...')

Performance issues?

Add indexes on frequently queried columns
Use select() to limit columns and paginate() for large sets
Watch for [GLUEFUL-N+1] warnings and eager load with with()

Next Steps

ORM - Models and relationships
Migrations - Manage database schema
Controllers - Use queries in controllers
Validation - Validate before inserting
Repositories - Encapsulate data access
Performance - Query caching, profiling and analysis

Controllers

Organize request handling logic with BaseController

Validation

Validate request data with typed DTOs, rule strings, or rule objects