Database

Name: Slenix Framework
Author: Slenix

Introduction

Slenix provides a fluent, expressive interface for interacting with databases using raw SQL queries or the built-in ORM — a full Active Record implementation modelled after Laravel's Eloquent. All database access goes through PDO, with prepared statements used throughout to prevent SQL injection.

Slenix supports two database drivers out of the box:

MySQL (5.7+, 8.0+)
PostgreSQL (13+)

Configuration

Database configuration is stored in Config/app.php under the db_connect key. The values are read from your .env file via the env() helper:

php

// Config/app.php
'db_connect' => [
    'drive'    => env('DB_CONNECTION'),
    'hostname' => env('DB_HOSTNAME'),
    'port'     => env('DB_PORT'),
    'dbname'   => env('DB_NAME'),
    'username' => env('DB_USERNAME'),
    'password' => env('DB_PASSWORD'),
    'charset'  => env('DB_CHARSET'),
],

Environment File

Set your database credentials in .env:

dotenv

DB_CONNECTION=mysql
DB_HOSTNAME=localhost
DB_PORT=3306
DB_NAME=my_database
DB_USERNAME=root
DB_PASSWORD=secret
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_general_ci

For PostgreSQL, change the driver and port:

dotenv

DB_CONNECTION=pgsql
DB_HOSTNAME=localhost
DB_PORT=5432
DB_NAME=my_database
DB_USERNAME=postgres
DB_PASSWORD=secret
DB_CHARSET=utf8

Note: The DB_COLLATION variable is only used for MySQL. It is ignored when connecting to PostgreSQL.

Supported Drivers

Driver	Value	Notes
MySQL	`mysql`	Requires MySQL 5.7+ or MariaDB 10.3+
PostgreSQL	`pgsql`	Requires PostgreSQL 13+

Passing any other driver value will throw an InvalidArgumentException at boot time.

Connections

Database connections are managed by Slenix\Supports\Database\Connection, which implements the Singleton pattern — a single PDO instance is reused across the entire request lifecycle.

php

use Slenix\Supports\Database\Connection;

// Get the shared PDO instance
$pdo = Connection::getInstance();

You rarely need to interact with the Connection class directly. The Model and QueryBuilder classes obtain the connection automatically.

PDO Configuration

The connection is configured with the following PDO attributes by default:

Attribute	Value	Description
`ATTR_ERRMODE`	`ERRMODE_EXCEPTION`	Throws exceptions on errors
`ATTR_DEFAULT_FETCH_MODE`	`FETCH_OBJ`	Returns objects by default
`ATTR_EMULATE_PREPARES`	`false`	Uses native prepared statements

Running Raw SQL

For cases where you need to execute raw SQL outside of a model, use Connection::raw():

php

use Slenix\Supports\Database\Connection;

$users = Connection::raw('SELECT * FROM users WHERE active = :active', ['active' => 1]);

Running Database Queries

The Query Builder

Every model exposes a static QueryBuilder through Model::newQuery(). You can also call any QueryBuilder method directly on the model class:

php

use App\Models\User;

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

Selecting Columns

php

// All columns
$users = User::all();

// Specific columns
$users = User::select(['id', 'name', 'email'])->get();

// Add columns to an existing select
$users = User::select('id')->addSelect('name')->get();

// Distinct results
$emails = User::select('email')->distinct()->get();

Where Clauses

php

// Basic where (operator defaults to =)
User::where('active', 1)->get();
User::where('age', '>=', 18)->get();

// OR conditions
User::where('role', 'admin')->orWhere('role', 'moderator')->get();

// Grouped conditions
User::where(function ($q) {
    $q->where('role', 'admin')->orWhere('role', 'moderator');
})->where('active', 1)->get();

// whereIn / whereNotIn
User::whereIn('role', ['admin', 'editor'])->get();
User::whereNotIn('status', ['banned', 'suspended'])->get();

// whereBetween
User::whereBetween('age', 18, 65)->get();
User::whereNotBetween('score', 0, 10)->get();

// whereNull / whereNotNull
User::whereNull('deleted_at')->get();
User::whereNotNull('verified_at')->get();

// LIKE
User::whereLike('name', '%João%')->get();

// Raw WHERE (use sparingly — always use bindings)
User::whereRaw('YEAR(created_at) = :year', ['year' => 2024])->get();

// Column comparison
User::whereColumn('updated_at', '>', 'created_at')->get();

Ordering, Grouping, and Limiting

php

// Order
User::orderBy('name', 'ASC')->get();
User::orderByDesc('created_at')->get();
User::latest()->get();           // ORDER BY created_at DESC
User::oldest()->get();           // ORDER BY created_at ASC
User::inRandomOrder()->get();    // ORDER BY RAND()

// Group
User::groupBy('country')->having('count', '>', 5)->get();

// Limit and offset
User::limit(10)->offset(20)->get();
User::take(10, 3)->get(); // 10 per page, page 3
User::forPage(3, 10)->get();

Joins

php

// INNER JOIN
User::join('profiles', 'users.id', '=', 'profiles.user_id')->get();

// LEFT JOIN
User::leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select(['users.*', 'orders.total'])
    ->get();

// RIGHT JOIN
User::rightJoin('orders', 'users.id', '=', 'orders.user_id')->get();

Aggregates

php

$count = User::count();
$avg   = User::where('active', 1)->avg('age');
$max   = User::max('score');
$min   = User::min('score');
$sum   = User::where('role', 'customer')->sum('balance');
$exists = User::where('email', 'a@b.com')->exists();

Retrieving Results

php

// Collection of model instances
$users = User::where('active', 1)->get();

// Single model or null
$user = User::where('email', 'a@b.com')->first();

// Single model or exception
$user = User::where('email', 'a@b.com')->firstOrFail();

// Single column value
$name = User::where('id', 1)->value('name');

// Pluck column values as Collection
$names  = User::pluck('name');
$keyed  = User::pluck('name', 'id'); // ['1' => 'Alice', '2' => 'Bob']

// Raw array (bypasses model hydration)
$rows   = User::allArray();
$first  = User::firstArray();

Pagination

php

$page   = (int) ($_GET['page'] ?? 1);
$result = User::where('active', 1)->paginate(15, $page);

// $result contains:
// [
//   'data'         => Collection,
//   'current_page' => 2,
//   'per_page'     => 15,
//   'total'        => 120,
//   'last_page'    => 8,
//   'from'         => 16,
//   'to'           => 30,
//   'has_more'     => true,
// ]

Debugging Queries

php

// Get the generated SQL without executing
$sql = User::where('active', 1)->orderBy('name')->toSql();

// Get SQL + bindings array
$debug = User::where('active', 1)->dump();
// ['sql' => '...', 'bindings' => [...]]

// Dump and die
User::where('active', 1)->dd();

Models

Generating a Model

Use Celestial to generate a model:

bash

php celestial make:model User

This creates app/Models/User.php with an auto-inferred table name (users). For multi-word model names, the table is derived in snake_case plural:

Model	Table
`User`	`users`
`BlogPost`	`blog_posts`
`OrderItem`	`order_items`

You may override the table name by setting $table explicitly.

Defining a Model

php

namespace App\Models;

use Slenix\Supports\Database\Model;

class User extends Model
{
    // Custom table name (optional — auto-inferred if omitted)
    protected string $table = 'users';

    // Primary key (default: 'id')
    protected string $primaryKey = 'id';

    // Disable auto-increment for UUID keys
    protected bool $incrementing = false;
    protected string $keyType = 'string';

    // Disable automatic timestamps
    protected bool $timestamps = false;

    // Fields hidden from toArray() / toJson()
    protected array $hidden = ['password', 'remember_token'];

    // Mass-assignable fields
    protected array $fillable = ['name', 'email', 'password'];

    // Blocked from mass assignment (alternative to fillable)
    protected array $guarded = ['is_admin'];

    // Automatic type casting
    protected array $casts = [
        'is_active'  => 'boolean',
        'settings'   => 'json',
        'score'      => 'float',
        'born_at'    => 'date',
        'created_at' => 'datetime',
    ];

    // Computed attributes to include in toArray()
    protected array $appends = ['full_name'];

    // Accessor
    public function getFullNameAttribute(): string
    {
        return $this->attributes['first_name'] . ' ' . $this->attributes['last_name'];
    }

    // Mutator
    public function setPasswordAttribute(string $value): void
    {
        $this->attributes['password'] = password_hash($value, PASSWORD_BCRYPT);
    }
}

Available Cast Types

Cast	PHP Type	Description
`int` / `integer`	`int`	Integer
`float` / `double`	`float`	Floating point
`bool` / `boolean`	`bool`	Boolean (accepts `1`, `'true'`, `'yes'`, `'on'`)
`string`	`string`	String
`json` / `array`	`array`	JSON decoded to PHP array, re-encoded on save
`datetime`	`DateTime`	Full datetime
`date`	`DateTime`	Date only — serialized as `Y-m-d`

Retrieving Models

php

// All records
$users = User::all();

// Find by primary key
$user = User::find(1);
$user = User::findOrFail(1); // throws RuntimeException if not found

// Find multiple
$users = User::findMany([1, 2, 3]);

// Find by any column
$user = User::firstWhere('email', 'a@b.com');

// First / Last
$first = User::first();
$last  = User::last();

// Count / Exists
$count  = User::count();
$exists = User::exists();

Inserting and Updating

Create

php

// Create and save in one step
$user = User::create([
    'name'     => 'Cláudio',
    'email'    => 'claudio@example.com',
    'password' => 'secret',
]);

echo $user->id; // auto-incremented ID

Save

php

$user = new User();
$user->name  = 'Cláudio';
$user->email = 'claudio@example.com';
$user->save();

Update

php

// Update specific fields
$user = User::find(1);
$user->update(['name' => 'Novo Nome']);

// Or set individually and save
$user->name = 'Novo Nome';
$user->save();

updateOrCreate / firstOrCreate

php

// Update if exists, create if not
$user = User::updateOrCreate(
    ['email' => 'a@b.com'],          // search criteria
    ['name' => 'Cláudio', 'role' => 'admin'] // data to set
);

// Find or create (only creates if not found)
$user = User::firstOrCreate(
    ['email' => 'a@b.com'],
    ['name' => 'Cláudio']
);

// Find or instantiate without saving
$user = User::firstOrNew(
    ['email' => 'a@b.com'],
    ['name' => 'Cláudio']
);

Deleting

php

$user = User::find(1);
$user->delete();

// Static mass delete via query builder
User::where('active', 0)->delete();

Soft Deletes

Enable soft deletes on a model by setting $softDelete = true. The model must have a deleted_at column:

php

class Post extends Model
{
    protected bool $softDelete = true;
    // Default column is 'deleted_at' — override with $deletedAt
}

Soft-deleted records are automatically excluded from all queries. To include or query them:

php

// Include soft-deleted
Post::withTrashed()->where('user_id', 1)->get();

// Only soft-deleted
Post::onlyTrashed()->get();

// Restore
$post = Post::withTrashed()->find(1);
$post->restore();

// Permanently delete
$post->forceDelete();

Checking State

php

$user = User::find(1);

$user->isDirty();           // true if any field changed
$user->isDirty('name');     // true if 'name' changed
$user->isClean('email');    // true if 'email' not changed
$user->getDirty();          // array of changed fields
$user->isNew();             // true if not yet saved (no primary key)

// Reload from database
$user->refresh();

// Duplicate (without primary key)
$copy = $user->replicate(['email']); // exclude 'email'
$copy->email = 'new@example.com';
$copy->save();

Mass Processing with Chunk

For processing large numbers of records without loading them all into memory:

php

User::chunk(100, function (Collection $chunk) {
    foreach ($chunk as $user) {
        // process $user
    }
});

Query Scopes

Define reusable query constraints as scopeXxx methods on the model:

php

class User extends Model
{
    public function scopeActive(QueryBuilder $query): void
    {
        $query->where('active', 1);
    }

    public function scopeOlderThan(QueryBuilder $query, int $age): void
    {
        $query->where('age', '>', $age);
    }
}

Use them with Model::scope():

php

User::scope('active')->get();
User::scope('olderThan', 18)->get();

Accessors and Mutators

php

class User extends Model
{
    // Accessor: $user->full_name
    public function getFullNameAttribute(): string
    {
        return $this->attributes['first_name'] . ' ' . $this->attributes['last_name'];
    }

    // Mutator: $user->password = 'secret' → hashes automatically
    public function setPasswordAttribute(string $value): void
    {
        $this->attributes['password'] = password_hash($value, PASSWORD_BCRYPT);
    }
}

Add accessors to $appends to include them in toArray() and toJson():

php

protected array $appends = ['full_name'];

Automatic Slug Generation

To automatically generate a URL-friendly slug before saving:

php

class Post extends Model
{
    protected ?string $slugFrom   = 'title';  // source field
    protected string  $slugField  = 'slug';   // target field
    protected bool    $slugUnique = true;     // ensure uniqueness
}

The slug is generated on every save when the source field is dirty. Uniqueness is guaranteed by appending a suffix (-1, -2, etc.):

php

$post = Post::create(['title' => 'Hello World']);
echo $post->slug; // 'hello-world'

// Find by slug
$post = Post::findBySlug('hello-world');

Serialization

php

$user = User::find(1);

// Array (respects $hidden and $appends)
$array = $user->toArray();

// JSON
$json = $user->toJson();

// Used automatically by json_encode()
echo json_encode($user);

// Collection serialization
$users = User::all();
$array = $users->toArray();
$json  = $users->toJson();

Hooks and Observers

Models fire hooks at key points in their lifecycle. Register them anywhere before the event occurs — typically in a service provider or bootstrap file:

php

use App\Models\User;

User::creating(function (User $user) {
    // Runs before INSERT
    $user->uuid = \Ramsey\Uuid\Uuid::uuid4()->toString();
});

User::created(function (User $user) {
    // Runs after INSERT
    sendWelcomeEmail($user->email);
});

User::updating(function (User $user) {
    // Runs before UPDATE
});

User::deleting(function (User $user) {
    // Runs before DELETE
    $user->posts()->get()->each->delete();
});

Available hooks: creating, created, updating, updated, saving, saved, deleting, deleted.

Relationships

Relationships are defined as methods on the model that return a relation instance. Slenix supports four relationship types, all with eager loading support to prevent the N+1 query problem.

One to One — `hasOne`

A User has one Profile. The foreign key (user_id) lives on the profiles table:

php

class User extends Model
{
    public function profile(): HasOne
    {
        return $this->hasOne(Profile::class);
        // Custom keys: hasOne(Profile::class, 'user_id', 'id')
    }
}

Usage:

php

// Lazy loading (executes a query)
$profile = User::find(1)->profile;

// Eager loading (single query per relation)
$users = User::with('profile')->get();

foreach ($users as $user) {
    echo $user->profile->bio;
}

One to Many — `hasMany`

A User has many Posts. The foreign key (user_id) lives on the posts table:

php

class User extends Model
{
    public function posts(): HasMany
    {
        return $this->hasMany(Post::class);
        // Custom keys: hasMany(Post::class, 'user_id', 'id')
    }
}

Usage:

php

// Lazy loading — returns Collection
$posts = User::find(1)->posts;

// With constraints
$published = User::find(1)->posts()->where('status', 'published')->orderBy('created_at', 'DESC')->get();

// Eager loading
$users = User::with('posts')->get();

// Create a related model
$user = User::find(1);
$post = $user->posts()->create(['title' => 'My Post', 'body' => '...']);

// Count
$count = User::find(1)->posts()->count();

Inverse — `belongsTo`

A Post belongs to a User. The foreign key (user_id) lives on the posts table:

php

class Post extends Model
{
    public function author(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id', 'id');
    }
}

Usage:

php

// Lazy loading
$author = Post::find(1)->author;

// Eager loading
$posts = Post::with('author')->get();

// Associate a model (sets the foreign key)
$post = Post::find(1);
$post->author()->associate($user);
$post->save();

// Remove association (sets FK to null)
$post->author()->dissociate();
$post->save();

Many to Many — `belongsToMany`

A User belongs to many Roles through the role_user pivot table:

php

class User extends Model
{
    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class);
        // The pivot table name is auto-derived: 'role_user' (alphabetical)
        // Custom: belongsToMany(Role::class, 'user_roles', 'user_id', 'role_id')
    }
}

Accessing pivot data:

php

class User extends Model
{
    public function roles(): BelongsToMany
    {
        return $this->belongsToMany(Role::class)
                    ->withPivot('expires_at', 'granted_by')
                    ->withTimestamps();
    }
}

$role = User::find(1)->roles->first();
echo $role->pivot_expires_at;

Managing the pivot table:

php

$user = User::find(1);

// Attach roles (INSERT into pivot)
$user->roles()->attach([1, 2, 3]);
$user->roles()->attach(4, ['expires_at' => '2026-12-31']);

// Detach roles (DELETE from pivot)
$user->roles()->detach([2, 3]);
$user->roles()->detach(); // removes all

// Sync (attach new, detach removed)
$user->roles()->sync([1, 3, 5]);

// Sync without detaching
$user->roles()->syncWithoutDetaching([4, 5]);

// Toggle (attach if absent, detach if present)
$user->roles()->toggle([1, 2]);

// Update pivot data
$user->roles()->updateExistingPivot(1, ['expires_at' => '2027-01-01']);

Eager Loading

Load relationships in a single extra query per relation instead of one query per model (solves N+1):

php

// Single relation
$users = User::with('profile')->get();

// Multiple relations
$posts = Post::with(['author', 'comments'])->get();

// Select specific columns from the relation
$users = User::with(['posts:title,created_at'])->get();

The relationship must be defined on the model for with() to work. After eager loading, accessing the relation property reads from the pre-loaded cache:

php

$users = User::with('posts')->get();

foreach ($users as $user) {
    // No additional query — loaded from cache
    echo $user->posts->count();
}

Check if a relation is loaded:

php

if ($user->relationLoaded('posts')) {
    // Already in memory
}

Collections

All multi-row results are returned as Slenix\Supports\Database\Collection, which supports a rich set of in-memory operations:

php

$users = User::all();

// Filtering
$active  = $users->where('active', 1);
$admins  = $users->whereIn('role', ['admin', 'moderator']);
$recent  = $users->where('created_at', '>=', '2025-01-01');

// Transformation
$names   = $users->pluck('name');
$mapped  = $users->map(fn($u) => $u->name . ' <' . $u->email . '>');
$grouped = $users->groupBy('country');
$sorted  = $users->sortBy('name');
$unique  = $users->unique('email');

// Slicing
$first5  = $users->take(5);
$page2   = $users->skip(15)->take(15);
$chunks  = $users->chunk(10); // array of Collections

// Aggregation
$count   = $users->count();
$avg     = $users->avg('age');
$total   = $users->sum('balance');
$max     = $users->max('score');

// Verification
$isEmpty = $users->isEmpty();
$hasAdm  = $users->contains('role', 'admin');
$allActv = $users->every(fn($u) => $u->active);

// Conversion
$array   = $users->toArray();
$json    = $users->toJson();
$keyed   = $users->keyBy('id'); // array keyed by id

// Merging
$merged  = $users->merge($otherUsers);
$diff    = $users->diff($otherUsers);

Raw Queries

For complex queries that cannot be expressed through the query builder, use Connection::raw() or Model::query():

php

use Slenix\Supports\Database\Connection;
use App\Models\User;

// Raw PDO query — returns array of stdObjects
$rows = Connection::raw(
    'SELECT u.*, COUNT(p.id) as post_count FROM users u LEFT JOIN posts p ON p.user_id = u.id GROUP BY u.id',
    []
);

// Raw query returning hydrated model Collection
$users = User::query(
    'SELECT * FROM users WHERE YEAR(created_at) = :year',
    ['year' => 2024]
);

// Truncate a table
User::truncate();

Always use parameter binding (:param) with raw queries — never interpolate user input directly into SQL strings.

Query Builder Reference

Method	Returns	Description
`select(columns)`	`self`	Set columns to SELECT
`addSelect(columns)`	`self`	Add columns to existing SELECT
`distinct()`	`self`	Add DISTINCT
`where(col, op, val)`	`self`	Basic WHERE
`orWhere(col, op, val)`	`self`	OR WHERE
`whereIn(col, values)`	`self`	WHERE IN
`whereNotIn(col, values)`	`self`	WHERE NOT IN
`whereBetween(col, min, max)`	`self`	WHERE BETWEEN
`whereNull(col)`	`self`	WHERE IS NULL
`whereNotNull(col)`	`self`	WHERE IS NOT NULL
`whereLike(col, val)`	`self`	WHERE LIKE
`whereRaw(sql, bindings)`	`self`	Raw WHERE fragment
`whereColumn(a, op, b)`	`self`	Compare two columns
`join(table, a, op, b)`	`self`	INNER JOIN
`leftJoin(...)`	`self`	LEFT JOIN
`rightJoin(...)`	`self`	RIGHT JOIN
`orderBy(col, dir)`	`self`	ORDER BY
`orderByDesc(col)`	`self`	ORDER BY DESC
`latest(col?)`	`self`	ORDER BY col DESC
`oldest(col?)`	`self`	ORDER BY col ASC
`inRandomOrder()`	`self`	ORDER BY RAND()
`groupBy(cols)`	`self`	GROUP BY
`having(col, op, val)`	`self`	HAVING
`limit(n)`	`self`	LIMIT
`offset(n)`	`self`	OFFSET
`take(perPage, page)`	`self`	LIMIT + OFFSET for page
`forPage(page, perPage)`	`self`	Alias for take
`withRelations(relations)`	`self`	Eager load relations
`get()`	`Collection`	Execute and return Collection
`first()`	`?Model`	First result or null
`firstOrFail()`	`Model`	First result or exception
`find(id)`	`?Model`	Find by primary key
`value(col)`	`mixed`	Single column from first row
`pluck(col, key?)`	`Collection`	Values of a column
`count()`	`int`	COUNT
`sum(col)`	`float`	SUM
`avg(col)`	`float`	AVG
`max(col)`	`mixed`	MAX
`min(col)`	`mixed`	MIN
`exists()`	`bool`	Whether any row exists
`paginate(perPage, page)`	`array`	Paginated results
`toSql()`	`string`	Generated SQL
`dump()`	`array`	SQL + bindings
`dd()`	`never`	Dump and die

#Database

#Introduction

#Configuration

#Environment File

#Supported Drivers

#Connections

#PDO Configuration

#Running Raw SQL

#Running Database Queries

#The Query Builder

#Selecting Columns

#Where Clauses

#Ordering, Grouping, and Limiting

#Joins

#Aggregates

#Retrieving Results

#Pagination

#Debugging Queries

#Models

#Generating a Model

#Defining a Model

#Available Cast Types

#Retrieving Models

#Inserting and Updating

#Create

#Save

#Update

#updateOrCreate / firstOrCreate

#Deleting

#Soft Deletes

#Checking State

#Mass Processing with Chunk

#Query Scopes

#Accessors and Mutators

#Automatic Slug Generation

#Serialization

#Hooks and Observers

#Relationships

#One to One — hasOne

#One to Many — hasMany

#Inverse — belongsTo

#Many to Many — belongsToMany

#Eager Loading

#Collections

#Raw Queries

#Query Builder Reference

Database

Introduction

Configuration

Environment File

Supported Drivers

Connections

PDO Configuration

Running Raw SQL

Running Database Queries

The Query Builder

Selecting Columns

Where Clauses

Ordering, Grouping, and Limiting

Joins

Aggregates

Retrieving Results

Pagination

Debugging Queries

Models

Generating a Model

Defining a Model

Available Cast Types

Retrieving Models

Inserting and Updating

Create

Save

Update

updateOrCreate / firstOrCreate

Deleting

Soft Deletes

Checking State

Mass Processing with Chunk

Query Scopes

Accessors and Mutators

Automatic Slug Generation

Serialization

Hooks and Observers

Relationships

One to One — `hasOne`

One to Many — `hasMany`

Inverse — `belongsTo`

Many to Many — `belongsToMany`

Eager Loading

Collections

Raw Queries

Query Builder Reference