README

PDO connection, fluent Query Builder, Active Record ORM, Relations, Pagination, Soft Deletes, Migration engine, and Seeders for the Luany ecosystem.

Version: v1.1.0  |  PHP: >= 8.2  |  License: MIT Author: António Ambrósio Ngola  |  Org: luany-ecosystem

Table of Contents

1. Installation
2. Connection
3. QueryBuilder
   • Fluent API
   • Aggregates
   • Pagination
   • Raw Methods
4. Model
   • Defining a Model
   • CRUD
   • Relationships
   • Eager Loading
   • Soft Deletes
5. Transactions
6. Migrations
7. Seeders
8. Changelog

1. Installation

composer require luany/database

2. Connection

Connection is a PDO wrapper. It does not enforce a singleton — that responsibility belongs to the application's DatabaseServiceProvider.

use Luany\Database\Connection;

// Create from config array
$connection = Connection::make([
    'host'     => '127.0.0.1',
    'port'     => '3306',
    'database' => 'my_app',
    'username' => 'root',
    'password' => '',
    'charset'  => 'utf8mb4',
]);

// Create from an existing PDO (useful in tests)
$connection = Connection::fromPdo($pdo);

// Access the underlying PDO instance
$pdo = $connection->getPdo();

Connection Methods

3. QueryBuilder

Fluent query builder with full prepared-statement safety. All user-supplied values are bound — never interpolated.

use Luany\Database\QueryBuilder;

$qb = new QueryBuilder($connection);

3.1 Fluent API

SELECT

// All rows
$users = $qb->table('users')->get();

// With column selection
$users = $qb->table('users')
    ->select('id', 'name', 'email')
    ->get();

// First row or null
$user = $qb->table('users')
    ->where('email', '=', 'antonio@example.com')
    ->first();

WHERE Clauses

// AND WHERE (default)
$qb->table('users')->where('age', '>', 18)->where('active', '=', 1)->get();

// OR WHERE
$qb->table('users')
    ->where('city', '=', 'Luanda')
    ->orWhere('city', '=', 'Lisbon')
    ->get();

// WHERE IN
$qb->table('users')->whereIn('id', [1, 2, 3])->get();

// WHERE NULL / NOT NULL
$qb->table('users')->whereNull('deleted_at')->get();
$qb->table('users')->whereNotNull('email_verified_at')->get();

ORDER BY, LIMIT, OFFSET

$qb->table('users')
    ->orderBy('name', 'ASC')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->offset(20)
    ->get();

INSERT

$qb->table('users')->insert([
    'name'  => 'António',
    'email' => 'antonio@example.com',
]);

$lastId = $qb->lastInsertId();

UPDATE

$affected = $qb->table('users')
    ->where('id', '=', 42)
    ->update(['name' => 'António Ngola']);
// returns int — number of affected rows

DELETE

$affected = $qb->table('users')
    ->where('id', '=', 42)
    ->delete();
// returns int — number of affected rows

3.2 Aggregates

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

$filtered = $qb->table('users')
    ->where('active', '=', 1)
    ->count();

$exists = $qb->table('users')
    ->where('email', '=', 'antonio@example.com')
    ->exists(); // bool

3.3 Pagination

paginate() executes two queries: one COUNT(*) to get the total, one SELECT with LIMIT/OFFSET for the page data. All WHERE and ORDER BY clauses are respected by both queries.

$page = $qb->table('users')
    ->where('active', '=', 1)
    ->orderBy('name', 'ASC')
    ->paginate(perPage: 15, page: 2);

// PaginationResult properties
$page->data;        // array of rows for this page
$page->total;       // total matching rows across ALL pages
$page->perPage;     // 15
$page->currentPage; // 2
$page->lastPage;    // ceil(total / perPage)
$page->from;        // first row number on this page (1-based), null if empty
$page->to;          // last row number on this page (1-based), null if empty
$page->hasMore();   // bool — true if there is a next page
$page->hasPrev();   // bool — true if there is a previous page

// Serialize for JSON API responses
$array = $page->toArray();
// keys: data, total, per_page, current_page, last_page, from, to, has_more, has_prev

Edge cases handled:

• $page < 1 is clamped to 1
• $perPage < 1 throws \InvalidArgumentException
• Empty result set: total=0, lastPage=1, from=null, to=null

3.4 Raw Methods

Available for backward-compatibility and complex queries that the fluent API cannot express.

// Raw SELECT → returns Result
$result = $qb->raw('SELECT * FROM users WHERE age > ? AND city = ?', [18, 'Luanda']);
$rows   = $result->fetchAll();
$row    = $result->fetchOne();    // ?array
$col    = $result->fetchColumn(); // array of values from first column

// Alias: query()
$result = $qb->query('SELECT COUNT(*) FROM users');

// Raw INSERT / UPDATE / DELETE → returns affected row count
$affected = $qb->statement('DELETE FROM sessions WHERE expires_at < ?', [time()]);

// Last inserted ID
$id = $qb->lastInsertId();

4. Model

Model is an abstract Active Record base class. Extend it to define your application models.

4.1 Defining a Model

namespace App\Models;

use Luany\Database\Model;

class User extends Model
{
    protected string $table      = 'users';
    protected string $primaryKey = 'id';       // default: 'id'
    protected array  $fillable   = ['name', 'email', 'password'];
    protected array  $hidden     = ['password']; // excluded from toArray() / toJson()
}

Register the connection (done once in your DatabaseServiceProvider):

User::setConnection($connection);

// Or pass a lazy closure (resolved on first use):
User::setConnection(fn() => Connection::make($config));

4.2 CRUD

// Find by primary key — returns ?static
$user = User::find(42);

// All records (ORDER BY is validated against a strict whitelist)
$users = User::all();
$users = User::all('name ASC, created_at DESC');

// Raw WHERE clause
$users = User::where('active = ? AND age > ?', [1, 18]);
$user  = User::firstWhere('email = ?', ['antonio@example.com']); // ?static

// Count
$total    = User::count();
$filtered = User::count('active = ?', [1]);

// Create — inserts and returns hydrated instance
$user = User::create(['name' => 'António', 'email' => 'antonio@example.com']);

// Update — on an instance
$user->name = 'António Ngola';
$user->save();

// Shorthand update
$user->update(['name' => 'António Ngola', 'email' => 'new@example.com']);

// Delete — hard-delete (overridden by SoftDeletes)
$user->delete(); // bool

// Instance state
$user->exists();   // bool — true if loaded from / saved to DB
$user->toArray();  // respects $hidden
$user->toJson();   // JSON string, respects $hidden

// Attribute access (magic)
echo $user->name;
$user->name = 'Ngola';

// Mass-assign fillable attributes
$user->fill(['name' => 'Ngola', 'email' => 'ngola@example.com']);

4.3 Relationships

Define relationship methods on your model. They return Relation descriptor objects — no query is executed when the method is called. The query runs only on property access (lazy) or via with() (eager).

hasOne — one-to-one (FK on related table)

// In User model:
use Luany\Database\Relations\HasOne;

public function profile(): HasOne
{
    return $this->hasOne(Profile::class, 'user_id');
    //                                    ^ FK on profiles table
}

// Usage:
$user    = User::find(1);
$profile = $user->profile;   // ?Profile — lazy-loaded and cached

hasMany — one-to-many (FK on related table)

// In User model:
use Luany\Database\Relations\HasMany;

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

// Usage:
$user  = User::find(1);
$posts = $user->posts;   // Post[] — lazy-loaded and cached

belongsTo — inverse (FK on THIS table)

// In Post model:
use Luany\Database\Relations\BelongsTo;

public function author(): BelongsTo
{
    return $this->belongsTo(User::class, 'user_id');
    //                                    ^ FK on THIS (posts) table
}

// Usage:
$post   = Post::find(1);
$author = $post->author;   // ?User — lazy-loaded and cached

Key signatures:

// hasOne($related, $foreignKey = null, $localKey = null)
// hasMany($related, $foreignKey = null, $localKey = null)
// belongsTo($related, $foreignKey = null, $ownerKey = null)

All FK/owner-key arguments are optional. Defaults follow the convention {table_singular}_id (e.g. users → user_id). Pass explicit keys for irregular plurals or non-standard schemas.

Relation results are cached after the first access:

$user->posts; // query executed
$user->posts; // returns cached result — no second query

Manual relation management:

$user->getRelation('posts');           // load and cache
$user->setRelation('posts', $myArray); // set directly (bypasses query)

4.4 Eager Loading

Prevents N+1 queries. Instead of executing one query per model, with() executes one batched WHERE IN query per relation for the entire collection.

// Load all users with their posts and profile — 3 queries total (not 1 + N + N)
$users = User::with('posts', 'profile')->all();

foreach ($users as $user) {
    echo $user->name;
    echo count($user->posts);  // already loaded — no extra query
    echo $user->profile?->bio; // already loaded — no extra query
}

// Also works with find()
$user = User::with('posts')->find(1);

// Accepts ORDER BY
$users = User::with('posts')->all('name ASC');

with() returns an EagerProxy that delegates to all() and find(). After execution, the eager-load state is consumed and reset — no cross-request leakage.

4.5 Soft Deletes

Add the SoftDeletes trait to a model. Requires a deleted_at DATETIME DEFAULT NULL column.

namespace App\Models;

use Luany\Database\Model;
use Luany\Database\Concerns\SoftDeletes;

class Article extends Model
{
    use SoftDeletes;

    protected string $table    = 'articles';
    protected array  $fillable = ['title', 'body'];

    // Optional: override the column name (default: 'deleted_at')
    protected string $deletedAtColumn = 'deleted_at';
}

Migration:

ALTER TABLE articles ADD COLUMN deleted_at DATETIME DEFAULT NULL;

Behaviour:

// Standard queries automatically exclude soft-deleted records
$articles = Article::all();         // only WHERE deleted_at IS NULL
$article  = Article::find(1);       // null if soft-deleted
$count    = Article::count();       // excludes soft-deleted

// Soft-delete (sets deleted_at — row stays in DB)
$article->delete();
$article->trashed(); // true

// Restore
$article->restore();
$article->trashed(); // false

// Permanently remove the row
$article->forceDelete();

// Include soft-deleted records
$all     = Article::withTrashed();
$all     = Article::withTrashed('title DESC');

// Only soft-deleted records
$trashed = Article::onlyTrashed();
$trashed = Article::onlyTrashed('deleted_at ASC');

5. Transactions

// Manual
$connection->beginTransaction();

try {
    $connection->execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
    $connection->execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollBack();
    throw $e;
}

// Callback (auto-commit on success, auto-rollback on exception)
$result = $connection->transaction(function (Connection $conn) {
    $conn->execute('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [1]);
    $conn->execute('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [2]);
    return 'transferred';
});
// $result === 'transferred'

// Check state
$connection->inTransaction(); // bool

6. Migrations

Migrations live in database/migrations/ and are ordered by filename timestamp.

Defining a Migration

use Luany\Database\Migration\Migration;

class CreateUsersTable extends Migration
{
    public function up(\PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS `users` (
                `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                `name`       VARCHAR(255) NOT NULL,
                `email`      VARCHAR(150) NOT NULL UNIQUE,
                `deleted_at` DATETIME DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }

    public function down(\PDO $pdo): void
    {
        $pdo->exec("DROP TABLE IF EXISTS `users`");
    }
}

MigrationRunner

use Luany\Database\Migration\MigrationRunner;

$runner = new MigrationRunner($connection, '/path/to/database/migrations');

// Run all pending migrations
$runner->run(function (string $name) {
    echo "Migrated: {$name}\n";
});

// Roll back last batch
$runner->rollback(function (string $name) {
    echo "Rolled back: {$name}\n";
});

// Inspect status
$status = $runner->status();
// [['name' => '...', 'ran' => true, 'batch' => 1], ...]

// Drop all tables and re-run (dev use only)
$runner->dropAll($connection->getPdo());
$runner->run();

The _migrations table is created automatically on first use.

7. Seeders

Seeders live in database/seeders/ and populate the database with initial or test data.

Defining a Seeder

use Luany\Database\Seeder\Seeder;

class UserSeeder extends Seeder
{
    public function run(\PDO $pdo): void
    {
        $stmt = $pdo->prepare("
            INSERT IGNORE INTO `users` (`name`, `email`) VALUES (?, ?)
        ");

        $stmt->execute(['António Ngola', 'antonio@example.com']);
        $stmt->execute(['Luany António',  'luany@example.com']);
    }
}

DatabaseSeeder — entry point

DatabaseSeeder is the root seeder. It chains other seeders via $this->call():

use Luany\Database\Seeder\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run(\PDO $pdo): void
    {
        $this->call(UserSeeder::class);
        $this->call(ProductSeeder::class);
    }
}

All classes referenced via call() are loaded automatically before any seeder runs — no manual require needed.

SeederRunner

use Luany\Database\Seeder\SeederRunner;

$runner = new SeederRunner($pdo, '/path/to/database/seeders');

// Run DatabaseSeeder (default entry point)
$runner->run('DatabaseSeeder', function (string $class, string $status) {
    echo "[{$status}] {$class}\n";
});

// Run a specific seeder
$runner->run('UserSeeder');

SeederRunner loads all PHP files in the seeders directory before executing — call() chains always resolve regardless of file order.

Seeder base class

8. Changelog

v1.1.0 — Seeders

New — src/Seeder/Seeder.php

• Abstract base class for all seeders. Defines run(\PDO $pdo): void contract.
• call(string ...$seederClasses): void — chain seeders from within DatabaseSeeder. All called seeders receive the same PDO instance.
• PDO injected via setPdo() before run() — managed by SeederRunner.

New — src/Seeder/SeederRunner.php

• Discovers and executes seeders from a given directory.
• Loads all PHP files in the seeders directory before running — call() chains always resolve.
• Optional output callback fn(string $class, string $status): void.
• Throws \RuntimeException if target class not found or does not extend Seeder.

Tests added: SeederRunnerTest (9 tests)
Total: 178 → 187 tests, 284 → 296 assertions — all green.

v0.3.0 — Phase 3: Relations, Eager Loading, Pagination, Soft Deletes

New — src/Relations/

• Relation — abstract base with getResults() and batchLoad() contracts
• HasOne — one-to-one relation with true eager batch loading (WHERE IN)
• HasMany — one-to-many relation with true eager batch loading (WHERE IN + group by FK)
• BelongsTo — inverse relation with true eager batch loading (WHERE IN + key by owner key)

New — src/Concerns/SoftDeletes.php

• Trait: overrides newQuery() to add WHERE deleted_at IS NULL scope
• Adds delete() (sets deleted_at), forceDelete(), restore(), trashed()
• Adds static withTrashed() and onlyTrashed() (both accept ORDER BY)

New — src/EagerProxy.php

• Returned by Model::with() — delegates all() and find() with eager-load injection
• No cross-request state leakage (eager-load list consumed and reset after each query)

New — src/PaginationResult.php

• Immutable value object: data, total, perPage, currentPage, lastPage, from, to
• Methods: hasMore(), hasPrev(), toArray()

Modified — src/QueryBuilder.php

• Added paginate(int $perPage = 15, int $page = 1): PaginationResult
• Two queries per call: COUNT(*) + SELECT with LIMIT/OFFSET
• Page clamped to ≥ 1; perPage < 1 throws \InvalidArgumentException

Modified — src/Model.php

• $attributes, $relations, $exists changed from private to protected (required by SoftDeletes trait)
• getAttribute() changed from protected to public (required by Relation classes)
• validateOrderBy() changed from private to protected (required by SoftDeletes trait)
• Added public static hydrateFromRow(array $row): static (required by Relation classes)
• Added public static setEagerLoad(array $relations): void (used by EagerProxy)
• with() now returns EagerProxy instead of a plain instance
• Relationship methods (hasOne, hasMany, belongsTo) now return typed Relation descriptor objects instead of executing queries directly
• getRelation() now resolves Relation descriptors via getResults() (lazy load with cache)
• eagerLoadRelation() now delegates to Relation::batchLoad() (true N+1-free eager loading)
• Added fill(array $attributes): void

Tests added: RelationsTest (15), EagerLoadTest (13), PaginateTest (27), SoftDeletesTest (21)
Total: 170 tests, 274 assertions — all green.

v0.2.0 — Phase 1: Fluent QueryBuilder & Transactions

New — QueryBuilder fluent API

• table(), select(), where(), orWhere(), whereIn(), whereNull(), whereNotNull()
• orderBy(), limit(), offset()
• get(), first(), insert(), update(), delete(), count(), exists()
• raw() / query() / statement() preserved for backward-compatibility
• All queries use prepared statements — no string interpolation of user values

New — Connection transaction methods

• beginTransaction(), commit(), rollBack(), inTransaction()
• transaction(callable) — auto-commit / auto-rollback wrapper

New — Connection::fromPdo() and Connection::make()

• Replaces the legacy configure() / getInstance() singleton pattern

Modified — Model

• find(), all(), create(), save(), delete() refactored to use the fluent QueryBuilder internally
• all() ORDER BY clause validated against a strict regex whitelist (SQL injection prevention)
• Added where(), firstWhere(), count() static methods
• Added setConnection() / getConnection() (replaces global singleton)

Tests: 94 tests, 136 assertions.

v0.1.3 and earlier

Initial release. Raw-query-only QueryBuilder. Basic Model with hand-built SQL strings. MigrationRunner, MigrationRepository, Migration base class.