Database

An Active Record ORM that returns arrays rather than objects, a complete Query Builder, and migrations portable across SQLite, MySQL and PostgreSQL — all checked against packages/database/src/Database/*.php.

Array-based Active Record

Niang\Core\Database\Model does not wrap a row in an object: every record stays an ordinary PHP array (['id' => 1, 'title' => '...']). No hydration, no proxy, no dirty tracking — a var_dump() of a result shows exactly what was read from the database.

namespace App\Models;

use Niang\Core\Database\Model;

class Post extends Model
{
    // protected static string $table = 'posts'; // otherwise inferred from the class name: Post -> "posts"
}

Without a declared $table, Model::table() infers the table name from the class name: Post → posts, Comment → comments (lowercase + s). $primaryKey defaults to 'id'.

CRUD on a Model

Mass assignment: $fillable

create() and update() only keep the columns declared in $fillable: User::create($request->all()) cannot write role or email_verified_at, even if a visitor adds those fields to the form (the ignored keys include _token).

class User extends Model
{
    // `role` and `email_verified_at` are deliberately left out.
    protected static array $fillable = ['name', 'email', 'password'];
}

User::create(['name' => 'Awa', 'email' => 'awa@example.test', 'password' => $hash, 'role' => 'admin']);
// -> INSERT of name, email, password only: role keeps its default value

A model without $fillable makes create()/update() throw a MassAssignmentException, rather than silently accepting (or dropping) everything — niang make:model generates the property for you to fill in. For trusted code that writes a sensitive column — a seeder, a role assigned by an administrator after validation, an email verification date — forceCreate() and forceUpdate() bypass the filter:

User::forceUpdate($id, ['email_verified_at' => date('Y-m-d H:i:s')]);
User::forceCreate(['name' => 'Administrator', 'email' => $email, 'password' => $hash, 'role' => 'admin']);

Factories go through forceCreate(): their data is written by the developer, not by a visitor.

Timestamps, casts and soft deletes

class Article extends Model
{
    protected static array $fillable = ['title', 'published', 'options'];

    // created_at / updated_at filled in by create(), updated_at by update().
    // True by default; false for a table without these columns.
    protected static bool $timestamps = true;

    // Types when reading, whatever the DBMS; json: PHP array <-> JSON text.
    protected static array $casts = ['published' => 'bool', 'views' => 'int', 'options' => 'json'];

    // destroy() fills in deleted_at instead of deleting the row.
    protected static bool $softDeletes = true;
}

Article::destroy($id);             // UPDATE ... SET deleted_at = now
Article::onlyTrashed()->get();     // the trash
Article::withTrashed()->count();   // everything, deleted included
Article::restore($id);
Article::forceDestroy($id);        // real DELETE

Model::query()->where(...)->update([...]) (a mass update through the Query Builder) does not touch updated_at: pass it explicitly.

Query Builder

Post::query() returns a chainable QueryBuilder for everything the shortcuts above do not cover:

Post::query()
    ->where('published', true)
    ->where('views', '>', 100)
    ->orWhere('featured', '=', true)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

where($column, $value) (2 arguments, implicit equality) and where($column, $operator, $value) (3 arguments) are both accepted — same convention for orWhere() and having().

insert(), update() and delete() are also available directly on the Query Builder (used internally by Model::create/update/destroy).

leftJoin, pluck, chunk, increment

// Outer join: posts without an author are kept (name is then null)
Post::query()->select('posts.title', 'users.name')
    ->leftJoin('users', 'posts.author_id', '=', 'users.id')->get();

// A single column, optionally keyed by another
Post::query()->pluck('title');          // ['First', 'Second', ...]
Post::query()->pluck('title', 'id');    // [1 => 'First', 2 => 'Second', ...]

// Walk a large table in batches, without loading it all into memory
User::query()->where('active', true)->chunk(500, function (array $users, int $batch) {
    foreach ($users as $user) { /* ... */ }
    // return false; stops the walk
});

// Computed by the database: two simultaneous orders don't lose an update
Product::query()->where('id', 5)->decrement('stock', 2);
Post::query()->where('id', 5)->increment('views', 1, ['last_viewed_at' => date('Y-m-d H:i:s')]);

chunk() orders by id if you don't give an orderBy(): without a stable order, two batches could overlap. Inside the callback, don't modify the column the query filters on (active above), or the following batches shift. pluck() accepts a qualified column (posts.title) and applies the model's $casts.

Subqueries and unions

// Posts with at least one comment (correlated subquery)
Post::query()->whereExists(
    (new QueryBuilder('comments'))->select('id')->whereColumn('comments.post_id', 'posts.id')
)->get();

// Posts and archives in one list, sorted and paginated
Post::query()->select('title', 'published_at')
    ->union((new QueryBuilder('archives'))->select('title', 'published_at'))   // unionAll() keeps duplicates
    ->orderBy('published_at', 'desc')->paginate(20);

Also: whereNotExists(), whereNotIn(). orderBy(), limit(), count() and paginate() apply to the combined result of a union (portable across the three engines): sort by the column name of the result (published_at, not posts.published_at).

Aggregates and pagination

Post::query()->count();               // COUNT(*)
Post::query()->where('published', true)->sum('views');
Post::query()->avg('rating');
Post::query()->min('price');
Post::query()->max('price');

Post::query()->where('slug', $slug)->exists();  // bool, without fetching any row
Post::query()->first();                          // ?array
Post::query()->firstOrFail();                    // array, or 404

paginate($perPage, $page) returns a Niang\Core\Database\Paginator:

$paginator = Post::paginate(10, (int) $request->query['page'] ?? 1);

$paginator->items;         // array[] of the current page
$paginator->total;         // total across all pages
$paginator->currentPage;
$paginator->lastPage();
$paginator->hasMorePages();
$paginator->links('/blog'); // <nav class="pagination">... Previous/1 2 3/Next links

Used as-is in the views of this documentation — see .pagination in public/css/niang.css for the styling.

Paginating large tables

// "Previous / Next" only: no COUNT(*)
$page = Post::simplePaginate(20, (int) $request->input('page', 1));

// By cursor: resumes after the last id seen (WHERE id > ?) instead of an OFFSET
$page = Post::cursorPaginate(20, $request->input('cursor'));
$page = Post::cursorPaginate(20, $request->input('cursor'), 'id', 'desc');
// $page->items, $page->nextCursor (null at the end), $page->links('/feed')

An OFFSET gets slower page after page and, if a row arrives between two pages, repeats or skips one; a cursor has neither flaw, but can't jump straight to page 7. Sort by a unique column. The cursor is an opaque, signed value (key derived from APP_KEY): a modified or forged cursor is ignored and pagination starts over. JsonResource::collection() accepts all three paginators.

Relations

No relation declared through attributes, no lazily loaded proxy: each relation is an explicit static method on the Model, built with the parent class's protected helpers:

public static function comments(int|string $postId): array
{
    return static::hasMany($postId, Comment::class, 'post_id');
}

public static function tags(int|string $postId): array
{
    return static::belongsToMany($postId, Tag::class, 'post_tag', 'post_id', 'tag_id');
}

Called from a controller: Post::comments($post['id']) — one query per call.

Eager loading (with())

Calling a relation for each row of a list is N+1 (one query per row). Model::with([...]) loads each declared relation in a single query for the whole collection (WHERE ... IN (...)), through eagerLoadable():

/** Usable with Post::with(['comments', 'tags'])->get() — one query each, no N+1. */
public static function eagerLoadable(): array
{
    return [
        'comments' => fn (array $posts) => static::loadMany($posts, 'comments', Comment::class, 'post_id'),
        'tags' => fn (array $posts) => static::loadManyToMany($posts, 'tags', Tag::class, 'post_tag', 'post_id', 'tag_id'),
    ];
}

$posts = Post::with(['comments', 'tags'])
    ->orderBy('created_at', 'desc')
    ->paginate(10);
// $posts->items[0]['comments'] and ['tags'] already loaded — 3 queries in total (posts + comments + tags),
// however many posts are on the page.

Model::with() returns an EagerLoadBuilder that delegates where()/orderBy()/whereIn()/limit()/offset() to the underlying Query Builder, then loads the requested relations after get()/first()/paginate(). Three low-level helpers exist to write your own eager-loadable relation: loadMany() (hasMany), loadOne() (belongsTo) and loadManyToMany() (pivot).

Migrations

Each migration is an anonymous class returned by the file, with up()/down():

use Niang\Core\Database\Migration;
use Niang\Core\Database\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::create('posts', function ($table) {
            $table->id();
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::drop('posts');
    }
};

./bin/niang make:migration create_posts_table
./bin/niang migrate
./bin/niang migrate:rollback
./bin/niang migrate:fresh

make:migration create_posts_table prefixes the file with a timestamp (2026_09_10_150001_...) and infers the table name (posts) from the create_<table>_table pattern. Migrations run in file-name order and are tracked in a migrations table (columns migration, batch) created automatically on the first migrate. migrate:rollback only undoes the last batch (batch); migrate:fresh undoes everything then replays everything from scratch.

Schema also exposes table() (modify an existing table), rename(), drop() and dropIfExists():

Schema::table('posts', function ($table) {
    $table->string('slug')->nullable();
    $table->renameColumn('body', 'content');
    $table->dropColumn('legacy_field');
});

Changing a column: change()

Schema::table('posts', function ($table) {
    $table->string('title', 500)->change();            // widen
    $table->text('summary')->nullable()->change();     // change the type, allow NULL
    $table->integer('views')->default(0)->change();
});

The full definition replaces the old one: repeat nullable() or default() if they must be kept. MySQL uses MODIFY, PostgreSQL ALTER COLUMN (type conversion with USING). SQLite cannot alter a column: the table is rebuilt (data copied, indexes recreated, foreign keys disabled for the duration).

Schema Builder: every column type

Blueprint methods actually implemented (one ADD COLUMN statement per column for Schema::table() — a deliberate limit, portable across the three engines):

Chainable column modifiers:

Schema::create('posts', function ($table) {
    $table->id();
    $table->string('title');
    $table->string('slug')->nullable()->unique();
    $table->foreignId('author_id')->constrained('users');
    $table->decimal('price', 8, 2)->default(0);
    $table->timestamps();

    $table->unique(['author_id', 'slug']);   // multi-column UNIQUE constraint
    $table->index('slug');                    // separate CREATE INDEX
});

Explicit foreign key constraint (independently of constrained()) and ON DELETE actions:

$table->foreign('user_id')->references('id')->on('users')->cascadeOnDelete();
// ->nullOnDelete() or ->restrictOnDelete() instead of ->cascadeOnDelete()

Multi-DBMS grammar

Niang\Core\Database\Grammar\{SQLiteGrammar,MySqlGrammar,PostgresGrammar} translate the Blueprint's abstract types into the actual SQL of the engine chosen by DB_CONNECTION (.env) — it is the only layer of the framework that knows about these differences:

Identifiers are also quoted differently ("col" for SQLite/PostgreSQL, `col` for MySQL) — a detail that Grammar::wrap() handles on its own, never visible from Blueprint, Schema or an application migration. The Query Builder also quotes every table and column (where, orderBy, select, insert...): a column named rank, key, group or order works on all three engines. In select(), an expression ('COUNT(*) AS total', 'UPPER(name) AS n') is passed through as is.

Transactions

DB::transaction(function () use ($postId, $tagIds) {
    Post::update($postId, ['published' => true]);

    foreach ($tagIds as $tagId) {
        DB::statement('INSERT INTO post_tag (post_id, tag_id) VALUES (?, ?)', [$postId, $tagId]);
    }
});
// automatic rollback if the callback throws; otherwise commits and returns its value

DB::beginTransaction(), DB::commit() and DB::rollBack() remain available for manual control.

Transactions truly nest through SAVEPOINT (PDO only allows one active transaction at a time): a DB::transaction() called from business code while a transaction is already open further up (for example RefreshDatabase in a test) does not throw — each nested level only undoes its own writes on failure, never those of the enclosing transaction. DB::inTransaction() tells whether a transaction (or a nesting level) is currently open.

Splitting reads and writes

DB_READ_HOST/DB_READ_DATABASE/DB_READ_CONNECTION in .env point reads to a replica; without them, reads and writes share the same connection (automatic fallback, nothing to configure in development). Query Builder methods that read (get(), first(), aggregates...) use the 'read' connection by default, those that write use 'write' — ->onConnection('write') forces a read on the write connection (e.g. right after an insert(), to read your own write without replication lag).

Factories and seeders

Model::factory($definition) does not generate fake data automatically (zero external dependencies): you provide the closure that builds a record.

use App\Models\Comment;
use App\Models\Post;
use Niang\Core\Database\Seeder;

return new class extends Seeder {
    public function run(): void
    {
        $postIds = Post::factory(fn () => [
            'title' => 'Demo article',
            'body' => 'Content generated by the NiangPro seeder.',
        ])->count(3)->create();

        foreach ($postIds as $postId) {
            Comment::factory(fn () => [
                'post_id' => $postId,
                'body' => 'Test comment.',
            ])->count(2)->create();
        }
    }
};

->make($overrides = []) returns the array(s) without inserting them; ->create($overrides = []) inserts and returns the list of created ids. The $overrides are merged on top of the base definition.

./bin/niang make:seeder DatabaseSeeder
./bin/niang db:seed              # runs database/seeders/DatabaseSeeder.php
./bin/niang db:seed Tags         # runs database/seeders/TagsSeeder.php