Search by

luany / database

Ngola-Programador-Full-Stack

Database layer for the Luany ecosystem — PDO connection, Model base, QueryBuilder, and Migration engine.

Package info

github.com/luany-ecosystem/luany-database

pkg:composer/luany/database

Security

Advisories: 0

Aikido package health analysis

Transparency log

Statistics

Installs: 126

Dependents: 1

Suggesters: 0

Stars: 0

Open Issues: 0

v1.7.0 2026-11-11 03:51 UTC

Requires

  • php: >=8.2

Requires (Dev)

Suggests

None

Provides

None

Conflicts

None

Replaces

None

MIT a93e16db1441affa856cf512a089d11f249e7b8c

databaseormmigrationsphppdoactiverecordquery-builderluany

Published on Packagist.org on 2026-11-11 03:52 UTC

This package is auto-updated.

Last update: 2026-11-11 03:52:14 UTC

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
  4. Model
  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

Method Signature Description
make static make(array $config): static Create from config array
fromPdo static fromPdo(\PDO $pdo): static Wrap an existing PDO
getPdo getPdo(): \PDO Get the underlying PDO
execute execute(string $sql, array $bindings = []): \PDOStatement Prepare and execute
lastInsertId lastInsertId(): string Last inserted row ID
beginTransaction beginTransaction(): bool Begin a transaction
commit commit(): bool Commit the transaction
rollBack rollBack(): bool Roll back the transaction
transaction transaction(callable $callback): mixed Execute callback in a transaction
inTransaction inTransaction(): bool Whether inside a transaction

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  (where('col', '=', null) also compiles to IS NULL)
$qb->table('users')->whereNull('deleted_at')->get();
$qb->table('users')->whereNotNull('email_verified_at')->get();

// WHERE NOT IN
$qb->table('users')->whereNotIn('role', ['banned', 'guest'])->get();

// Grouped conditions: WHERE active = ? AND (city = ? OR city = ?)
$qb->table('users')
    ->where('active', '=', 1)
    ->whereNested(fn ($q) => $q->where('city', '=', 'Luanda')->orWhere('city', '=', 'Lisbon'))
    ->get();

Identifier safety

Every table, column and alias passed to the builder is validated and quoted (users, users.id, users.*, name AS username). Operators are limited to = != <> < <= > >= LIKE NOT LIKE. Anything else throws InvalidArgumentException — user input can never become SQL structure.

When you genuinely need an expression, use the explicit raw methods and keep values in bindings:

$qb->table('orders')
    ->selectRaw('SUM(total) AS revenue')
    ->whereRaw('created_at >= ?', ['2026-01-01'])
    ->orderByRaw('revenue DESC')
    ->get();

Raw methods are not escaped. Never interpolate user input into them.

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

$qb->table('orders')->where('status', '=', 'paid')->sum('total');  // int|float|null
$qb->table('orders')->avg('total');                                // ?float
$qb->table('orders')->min('created_at');
$qb->table('orders')->max('total');

Joins, grouping and DISTINCT

$rows = $qb->table('posts AS p')
    ->select('p.title', 'u.name AS author')
    ->join('users AS u', 'u.id', '=', 'p.user_id')      // INNER JOIN
    ->leftJoin('categories AS c', 'c.id', '=', 'p.category_id')
    ->where('u.active', '=', 1)
    ->get();

$perAuthor = $qb->table('posts')
    ->select('user_id')
    ->selectRaw('SUM(views) AS total')
    ->groupBy('user_id')
    ->havingRaw('SUM(views) > ?', [1000])
    ->get();

$qb->table('posts')->distinct()->select('user_id')->count();   // counts distinct rows / groups

Tables, aliases and join columns are validated and quoted like every other identifier. count() and paginate() honour joins, DISTINCT and GROUP BY. update() / delete() refuse joined queries. On PostgreSQL, reference aggregate expressions in havingRaw() (aliases in HAVING are a MySQL/SQLite extension).

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()
}

Mass assignment is closed by default. fill(), create() and update() only accept columns listed in $fillable. A model with an empty $fillable accepts nothing unless you opt out explicitly:

protected array $guarded = ['is_admin'];  // everything except is_admin
protected array $guarded = [];            // everything (only for trusted input)

Direct property assignment ($user->is_admin = 1; $user->save();) and forceFill() are trusted and bypass the guard.

Non-incrementing keys (UUID / string):

protected string $primaryKey   = 'uuid';
protected bool   $incrementing = false;

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');

// Fluent, fully escaped query scoped to the model (respects SoftDeletes)
$rows = User::query()->where('active', '=', 1)->orderBy('name')->get();

// Raw WHERE clause — $conditions is raw SQL: ALWAYS use ? placeholders
$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 (mass assignment — respects $fillable)
$user->update(['name' => 'António Ngola', 'email' => 'new@example.com']);

// Dirty tracking — save() only writes changed columns
$user->isDirty('name');   // bool
$user->getDirty();        // ['name' => '...']
$user->getOriginal('name');
$user->refresh();         // reload from the database

// 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)

belongsToMany — many-to-many through a pivot table

// users ←→ role_user ←→ roles
public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class)          // pivot role_user, keys user_id / role_id
        ->withPivot('granted_by');                     // extra pivot columns → $role->pivot
}

$user->roles;                                          // array<Role>
$user->roles[0]->pivot['granted_by'];

$user->roles()->attach($roleId);
$user->roles()->attach([$a, $b]);
$user->roles()->attach([$a => ['granted_by' => 'admin']]);
$user->roles()->detach($roleId);                       // detach() with no argument removes all
$user->roles()->sync([$a, $c]);                        // ['attached' => [...], 'detached' => [...]]

Defaults: pivot table from the singular names in alphabetical order (role_user), keys {singular}_id. All can be passed explicitly: belongsToMany(Tag::class, 'post_tags', 'post_id', 'tag_id').

hasManyThrough — distant relation

// countries ← users.country_id ← posts.user_id
public function posts(): HasManyThrough
{
    return $this->hasManyThrough(Post::class, User::class);
}

$country->posts;                          // one JOIN query
Country::with('posts')->get();            // one query for all countries

Keys default to {country}_id on the through table and {user}_id on the related table; pass them explicitly when they differ.

Polymorphic relations — morphMany / morphOne / morphTo

// comments: id, body, commentable_type, commentable_id
class Post extends Model {
    public function comments(): MorphMany { return $this->morphMany(Comment::class, 'commentable'); }
    public function image(): MorphOne     { return $this->morphOne(Image::class, 'imageable'); }
}
class Video extends Model {
    public function comments(): MorphMany { return $this->morphMany(Comment::class, 'commentable'); }
}
class Comment extends Model {
    public function commentable(): MorphTo { return $this->morphTo(); }   // name from the method
}

$post->comments()->create(['body' => 'Nice']);
$comment->commentable;                                // Post or Video
$comment->commentable()->associate($video)->save();
Comment::with('commentable')->get();                  // one query per type

// Store short aliases instead of class names in *_type columns
Model::morphMap(['post' => Post::class, 'video' => Video::class]);

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');

Nested relations use dot notation — one query per level:

$users = User::with('posts.comments.author', 'roles')->get();

with() returns a ModelQuery, so eager loading combines with any condition:

$users = User::with('posts')->where('active', '=', 1)->orderBy('name')->get();

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');

4.6 Model Queries & Scopes

Model::query() starts a fluent query that returns models. Every QueryBuilder method is available (where, whereIn, whereNested, orderBy, limit, …), global scopes such as SoftDeletes are already applied, and eager loading works.

$posts = Post::query()
    ->where('views', '>', 100)
    ->whereNested(fn ($q) => $q->where('lang', '=', 'pt')->orWhere('lang', '=', 'en'))
    ->with('author')
    ->orderByDesc('published_at')
    ->get();                     // array<Post>

Post::query()->first();          // ?Post
Post::query()->find(5);          // ?Post
Post::findOrFail(5);             // Post or ModelNotFoundException
Post::query()->where('slug', '=', $slug)->firstOrFail();
Post::query()->paginate(15, $page);   // PaginationResult, ->data holds Post models
Post::query()->pluck('title');        // ['First', 'Second', …]
Post::query()->count();
Post::query()->toBase()->get();       // raw rows (QueryBuilder)

Local scopes are model methods prefixed with scope. Call them statically to start a query, or chain them:

class Post extends Model
{
    public function scopePublished(ModelQuery $query): void
    {
        $query->whereNotNull('published_at');
    }

    public function scopePopular(ModelQuery $query, int $min = 100): void
    {
        $query->where('views', '>=', $min);
    }
}

Post::published()->popular(500)->orderByDesc('views')->get();
Post::query()->published()->count();

Bulk update() / delete() on a model query run directly on the table: they skip casts, timestamps and per-model logic.

4.7 Attribute Casting

class Event extends Model
{
    protected array $casts = [
        'active'    => 'bool',          // stored as 1/0 on every driver
        'seats'     => 'int',
        'price'     => 'decimal:2',     // '19.90'
        'meta'      => 'array',         // JSON column <-> PHP array
        'starts_at' => 'datetime',      // DateTimeImmutable
        'birthday'  => 'date',
        'amount'    => MoneyCast::class // implements Luany\Database\Casts\CastsAttributes
    ];
}

$event->meta = ['seats' => 3];          // stored as JSON
$event->starts_at->format('d/m/Y');     // DateTimeImmutable
$event->toArray();                      // dates serialised with $dateFormat
$event->getRawAttribute('meta');        // stored value

Supported: int/integer, float/double, decimal:N, bool/boolean, string, array/json, datetime, date, timestamp, and any class implementing CastsAttributes (get() / set()). Storage format: protected string $dateFormat = 'Y-m-d H:i:s'.

4.8 Timestamps

Opt in per model; the model writes the values, so it behaves the same on MySQL, PostgreSQL and SQLite:

class Post extends Model
{
    protected bool $timestamps = true;   // created_at + updated_at

    // Optional: custom column names, or null to disable one
    public const CREATED_AT = 'created_at';
    public const UPDATED_AT = 'updated_at';
}

$post->touch();   // only bump updated_at

updated_at changes only when something else changed. Explicit values you set are kept.

4.9 Model Events

class User extends Model
{
    protected static function booted(): void
    {
        static::saving(fn (self $u) => $u->email = strtolower($u->email));
        static::deleting(fn (self $u) => !$u->is_admin);        // false cancels the delete
    }
}

Post::created(fn (Post $post) => Cache::forget('posts'));
Post::observe(PostObserver::class);    // methods named after events

Events: creating, created, updating, updated, saving, saved, deleting, deleted, and restoring / restored with SoftDeletes. A listener of an -ing event returning false cancels the operation (save() / delete() / restore() return false). updating / updated only fire when something changed. Bulk query updates / deletes do not fire events.

4.10 Relationship Queries

// Filter by related rows (correlated EXISTS / COUNT subqueries)
User::query()->has('posts')->get();
User::query()->has('posts', '>=', 3)->get();
User::query()->doesntHave('posts')->get();
User::query()->whereHas('posts', fn (ModelQuery $q) => $q->published())->get();
User::query()->whereHas('posts.comments', fn ($q) => $q->where('approved', '=', 1))->get();
User::query()->whereDoesntHave('roles', fn ($q) => $q->where('name', '=', 'banned'))->get();
User::query()->where('active', '=', 1)->orWhereHas('roles')->get();

// Count related rows as columns
$users = User::query()->withCount('posts', ['posts as published_count' => fn ($q) => $q->published()])->get();
$users[0]->posts_count;
$users[0]->published_count;

// Constrained eager loading
User::with(['posts' => fn ($q) => $q->published()->orderByDesc('created_at')], 'roles')->get();
User::with(['posts.comments' => fn ($q) => $q->where('approved', '=', 1)])->get();

// Find-or-create helpers
User::firstOrNew(['email' => $email]);
User::firstOrCreate(['email' => $email], ['name' => $name]);
User::updateOrCreate(['email' => $email], ['name' => $name]);
$user->fresh();   // new instance from the database

Self-referencing relations work (Category::query()->has('children')): subqueries alias the related table. Global scopes such as SoftDeletes apply inside subqueries.

4.11 Accessors & Mutators

class User extends Model
{
    protected array $appends = ['full_name'];   // virtual attributes in toArray()/JSON

    public function getFullNameAttribute(): string
    {
        return "{$this->first_name} {$this->last_name}";
    }

    public function getFirstNameAttribute(?string $value): ?string
    {
        return $value === null ? null : ucfirst($value);   // receives the cast value
    }

    public function setEmailAttribute(?string $value): void
    {
        $this->attributes['email'] = strtolower(trim((string) $value));
    }
}

Accessors run on property access, getAttribute() and toArray(); mutators run on property assignment, fill() and create(). getRawAttribute() returns the stored value.

4.12 Large Result Sets & Bulk Writes

// Batch insert: one statement
$qb->table('logs')->insert([
    ['level' => 'info', 'message' => 'a'],
    ['level' => 'warn', 'message' => 'b'],
]);

// Upsert: insert or update on a unique key (MySQL ON DUPLICATE KEY / PostgreSQL & SQLite ON CONFLICT)
$qb->table('stock')->upsert($rows, uniqueBy: ['sku'], update: ['price', 'qty']);

// Chunks (eager loading applied per chunk)
Order::query()->where('status', '=', 'pending')->chunkById(500, function (array $orders) {
    foreach ($orders as $order) { /* … update or delete safely … */ }
});
Post::query()->orderBy('id')->chunk(200, fn (array $posts, int $page) => /* return false to stop */ null);

// One row at a time
foreach (Post::query()->cursor() as $post) { /* … */ }

Batch inserts and upserts run directly on the table (no casts, timestamps or events).

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 the schema builder — the same migration runs on MySQL, PostgreSQL and SQLite. Anonymous classes avoid class-name collisions between migrations:

use Luany\Database\Migration\Migration;
use Luany\Database\Schema\Blueprint;

return new class extends Migration
{
    public function up(\PDO $pdo): void
    {
        $this->schema($pdo)->create('posts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('body')->nullable();
            $table->decimal('price', 10, 2)->default(0);
            $table->boolean('published')->default(false);
            $table->enum('status', ['draft', 'live'])->default('draft');
            $table->json('meta')->nullable();
            $table->morphs('owner');                // owner_type, owner_id + index
            $table->timestamps();                   // created_at, updated_at (nullable)
            $table->softDeletes();                  // deleted_at
        });
    }

    public function down(\PDO $pdo): void
    {
        $this->schema($pdo)->dropIfExists('posts');
    }
};

Raw SQL ($pdo->exec(...)) and named migration classes keep working.

Schema Builder

Columns
Keys id(), increments(), bigIncrements(), foreignId()->constrained()
Text string($n = 255), char($n), text(), longText(), uuid(), enum([...])
Numbers integer(), bigInteger(), smallInteger(), tinyInteger(), unsignedInteger(), unsignedBigInteger(), decimal($p, $s), float(), double(), boolean()
Dates & data date(), dateTime(), time(), timestamp(), json(), binary()
Helpers timestamps(), softDeletes(), morphs($name)

Modifiers: ->nullable(), ->default($value) (or Schema::raw('CURRENT_TIMESTAMP')), ->unsigned(), ->unique(), ->index(), ->primary(), ->useCurrent().

Indexes and foreign keys:

$table->index(['user_id', 'created_at']);
$table->unique('email');
$table->primary(['post_id', 'tag_id']);
$table->foreign('author_id')->references('id')->on('users')->nullOnDelete();

Altering tables, and other operations:

$schema = Schema::for($pdo);   // or $this->schema($pdo) in a migration

$schema->table('posts', function (Blueprint $table) {
    $table->string('subtitle')->nullable();
    $table->renameColumn('body', 'content');
    $table->dropColumn('meta');
    $table->dropIndex('subtitle');      // by column(s) or full index name
    $table->dropUnique('slug');
    $table->dropForeign('user_id');     // not supported on SQLite
});

$schema->rename('posts', 'articles');
$schema->drop('articles');
$schema->dropIfExists('articles');
$schema->hasTable('posts');
$schema->hasColumn('posts', 'slug');
$schema->getColumnListing('posts');

Per driver: MySQL uses native ENUM, JSON, UNSIGNED and InnoDB/utf8mb4; PostgreSQL uses BIGSERIAL, BOOLEAN, JSONB, UUID and a CHECK for enums; SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT and CHECK for enums (enable PRAGMA foreign_keys = ON to enforce foreign keys). SQLite cannot add or drop foreign keys on existing tables — the builder says so instead of silently ignoring them.

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

Method Description
run(\PDO $pdo): void Abstract — implement your insert logic here
call(string ...$classes): void Chain one or more seeders. All receive the same PDO instance.

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.