ForgeSqlOrm

SQL ORM support for Forge Kernel. Provides attribute-based models, fluent query builder, relationships, repository pattern, query caching, and full transaction support for SQLite, MySQL, and PostgreSQL.

Overview

ForgeSqlOrm provides comprehensive Object-Relational Mapping (ORM) support for Forge Kernel applications. It offers model-based database access, fluent query builder, relationship support, repository pattern, and query caching.

Key Features

Attribute-based model definition
Fluent query builder (immutable)
Relationship support (HasOne, HasMany, BelongsTo)
Eager and lazy loading
Repository pattern with caching
Automatic type casting
Soft deletes support
Full transaction support

What ForgeSqlOrm Provides

  • Model-Based ORM: Clean, attribute-based model definition with automatic property mapping
  • Fluent Query Builder: Immutable, chainable query builder implementing QueryBuilderInterface
  • Relationship Support: HasOne, HasMany, BelongsTo relationships with eager/lazy loading
  • Type Casting: Automatic casting with DTO and enum support
  • Repository Pattern: Clean data access layer with built-in caching
  • Query Caching: Built-in caching for performance optimization
  • Transaction Support: Full transaction support via QueryBuilder
  • Soft Deletes: Soft delete support with onlyTrashed() queries
  • Traits: HasTimeStamps and HasMetaData for common functionality

Core Module: ForgeSqlOrm is a core module (type: 'core', order: 1), providing essential ORM functionality for Forge Kernel applications. It depends on ForgeDatabaseSql for database connectivity.

Architecture & Design Philosophy

ForgeSqlOrm is built with clean architecture, performance, and developer experience in mind.

Model-Based ORM Approach

ForgeSqlOrm uses a model-based approach:

  • Models extend the base Model class
  • Attribute-based definition using PHP 8 attributes
  • Automatic property mapping from database rows
  • Type casting on model hydration
  • Automatic timestamps and soft delete support

Fluent Query Builder Pattern

QueryBuilder implements an immutable, fluent interface:

  • Immutable pattern — each method returns a new instance
  • Chainable methods for building complex queries
  • Implements QueryBuilderInterface for dependency injection
  • Parameter binding for SQL injection prevention
  • Support for raw SQL when needed

Repository Pattern for Data Access

Repository pattern provides clean data access:

  • Repository interface for common operations
  • RecordRepository base class with CRUD operations
  • QueryCache integration for performance
  • Cache invalidation on create/update/delete
  • Custom find methods with caching

Relationship Eager/Lazy Loading

Efficient relationship loading:

  • Eager loading with with() method to prevent N+1 queries
  • Lazy loading with load() method for on-demand loading
  • RelationLoader for efficient batch loading
  • Nested relationship support

Installation

ForgeSqlOrm is a core module and is typically available by default. It can also be installed via ForgePackageManager.

Using ForgePackageManager

# Install with wizard (interactive)
php forge.php package:install-module

# Install directly (skip wizard)
php forge.php package:install-module --module=ForgeSqlOrm

# Install specific version
php forge.php package:install-module --module=ForgeSqlOrm@0.1.8

Dependency

ForgeSqlOrm depends on ForgeDatabaseSql for database connectivity. Ensure ForgeDatabaseSql is installed and configured.

Container Bindings

The module automatically registers QueryBuilderInterface and QueryCache: 

public function register(Container $container): void
{
    // Bind QueryBuilderInterface to QueryBuilder
    $container->bind(QueryBuilderInterface::class, function ($c) {
        return new QueryBuilder($c->get(DatabaseConnectionInterface::class));
    });

    // Register QueryCache as singleton
    $container->singleton(QueryCache::class, function () {
        return new QueryCache(3600); // TTL: 3600 seconds
    });
}

Models Overview

Models in ForgeSqlOrm extend the base Model class and use attributes to define their structure.

Model Characteristics

  • Extend Model: All models extend the abstract Model base class
  • Attribute-Based: Use #[Table] and #[Column] attributes for definition
  • Automatic Property Mapping: Properties are automatically mapped from database rows
  • Type Casting: Automatic casting on model hydration using Cast enum
  • Timestamps Support: Use HasTimeStamps trait for automatic created_at/updated_at
  • Soft Deletes: Built-in soft delete support with deleted_at column
  • Protected Fields: Use #[ProtectedFields] to exclude fields from serialization
  • Hidden Fields: Use #[Hidden] to hide fields from toArray()/JSON

Model Lifecycle

  • Hydration: Models are created from database rows using fromRow()
  • Saving: save() method inserts new records or updates existing ones
  • Deletion: delete() method performs soft delete (or force delete if soft deletes disabled)
  • Serialization: toArray() and jsonSerialize() respect protected/hidden fields

Model Attributes

ForgeSqlOrm uses PHP 8 attributes to define model structure and behavior.

#[Table]

Define the database table name for the model: 

#[Table("users")]
class User extends Model
{
    // ...
}

#[Column]

Define a column with type casting: 

#[Column(primary: true, cast: Cast::INT)]
public int $id;

#[Column(cast: Cast::STRING)]
public string $email;

#[Column(cast: Cast::JSON)]
public ?UserMetadataDto $metadata;

Column attributes support:

  • primary: Mark as primary key
  • cast: Cast enum value for automatic type casting

#[ProtectedFields]

Exclude fields from toArray() and JSON serialization: 

#[ProtectedFields(['password'])]
class User extends Model
{
    #[Column(cast: Cast::STRING)]
    public string $password; // Excluded from toArray()/JSON
}

#[Hidden]

Hide a field from serialization: 

#[Hidden]
#[Column(cast: Cast::STRING)]
public string $internal_field; // Hidden from toArray()/JSON

Creating Models

Models are defined by extending the Model base class and using attributes.

Basic Model Structure

use App\Modules\ForgeSqlOrm\ORM\Attributes\Table;
use App\Modules\ForgeSqlOrm\ORM\Attributes\Column;
use App\Modules\ForgeSqlOrm\ORM\Model;
use App\Modules\ForgeSqlOrm\ORM\Values\Cast;

#[Table("users")]
class User extends Model
{
    #[Column(primary: true, cast: Cast::INT)]
    public int $id;

    #[Column(cast: Cast::STRING)]
    public string $email;
}

Model with Traits

Use traits for common functionality: 

use App\Modules\ForgeSqlOrm\Traits\HasTimeStamps;
use App\Modules\ForgeSqlOrm\Traits\HasMetaData;

#[Table("profiles")]
class Profile extends Model
{
    use HasTimeStamps; // Adds created_at, updated_at
    use HasMetaData;   // Adds metadata JSON column

    #[Column(primary: true, cast: Cast::INT)]
    public int $id;
}

Model with Relationships

use App\Modules\ForgeSqlOrm\ORM\Values\Relate;
use App\Modules\ForgeSqlOrm\ORM\Values\RelationKind;

#[Table("users")]
class User extends Model
{
    use HasTimeStamps;
    use CanLoadRelations;

    #[Column(primary: true, cast: Cast::INT)]
    public int $id;

    #[Relate(RelationKind::HasOne, Profile::class, "user_id")]
    public function profile(): Relation
    {
        return self::describe(__FUNCTION__);
    }
}

Model with Protected Fields

#[Table("users")]
#[ProtectedFields(['password'])]
class User extends Model
{
    #[Column(cast: Cast::STRING)]
    public string $password; // Excluded from toArray()/JSON
}

Model with Tenant Scoping

Models can integrate with ForgeMultiTenant: 

use App\Modules\ForgeMultiTenant\Attributes\TenantScoped;
use App\Modules\ForgeMultiTenant\Traits\TenantScopedTrait;

#[TenantScoped]
#[Table("posts")]
class Post extends Model
{
    use HasTimeStamps;
    use HasMetaData;
    use TenantScopedTrait;

    #[Column(primary: true, cast: Cast::STRING)]
    public int $id;

    #[Column(cast: Cast::STRING)]
    public string $tenant_id;
}

Model Operations

Models provide methods for creating, updating, deleting, and querying records.

save()

Insert a new record or update an existing one: 

$user = new User();
$user->email = 'user@example.com';
$user->password = 'hashed_password';
$user->save(); // Inserts new record

$user->email = 'newemail@example.com';
$user->save(); // Updates existing record

The save() method:

  • Automatically detects if record exists (checks primary key)
  • Inserts new records or updates existing ones
  • Automatically sets created_at and updated_at if HasTimeStamps trait is used
  • Generates UUID primary keys if needed

delete()

Soft delete or force delete a record: 

$user = User::query()->id(1)->first();
$user->delete(); // Soft delete (sets deleted_at)

// Force delete (if soft deletes disabled)
$user->forceDelete();

toArray()

Convert model to array (respects protected fields): 

$user = User::query()->id(1)->first();
$array = $user->toArray(); // Excludes protected fields

jsonSerialize()

JSON serialization (respects protected fields): 

$user = User::query()->id(1)->first();
$json = json_encode($user); // Uses jsonSerialize()

fromRow()

Create model instance from database row: 

$row = ['id' => 1, 'email' => 'user@example.com'];
$user = User::fromRow($row); // Creates User instance with type casting

QueryBuilder

QueryBuilder provides a fluent, immutable interface for building database queries.

Basic Query Methods

use App\Modules\ForgeSqlOrm\ORM\QueryBuilder;
use Forge\Core\Contracts\Database\QueryBuilderInterface;

// Inject QueryBuilderInterface
public function __construct(
    private readonly QueryBuilderInterface $builder
) {}

// Basic query
$results = $this->builder
    ->table('users')
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

Where Clauses

// Simple where
$results = $this->builder->table('users')
    ->where('email', '=', 'user@example.com')
    ->get();

// Where with IN
$results = $this->builder->table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

// Where with NOT IN
$results = $this->builder->table('users')
    ->whereNotIn('status', ['deleted', 'banned'])
    ->get();

// Where NULL
$results = $this->builder->table('users')
    ->whereNull('deleted_at')
    ->get();

// Where NOT NULL
$results = $this->builder->table('users')
    ->whereNotNull('email')
    ->get();

Select Specific Columns

$results = $this->builder->table('users')
    ->select('id', 'email', 'identifier')
    ->where('status', '=', 'active')
    ->get();

Ordering and Pagination

$results = $this->builder->table('users')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->offset(20)
    ->get();

Aggregate Functions

// Count
$count = $this->builder->table('users')
    ->where('status', '=', 'active')
    ->count();

// Sum
$total = $this->builder->table('orders')
    ->sum('amount');

// Average
$avg = $this->builder->table('orders')
    ->avg('amount');

// Min/Max
$min = $this->builder->table('orders')->min('amount');
$max = $this->builder->table('orders')->max('amount');

CRUD Operations

// Insert
$id = $this->builder->table('users')
    ->insert(['email' => 'user@example.com', 'password' => 'hash']);

// Update
$this->builder->table('users')
    ->where('id', '=', 1)
    ->update(['email' => 'newemail@example.com']);

// Delete
$this->builder->table('users')
    ->where('id', '=', 1)
    ->delete();

Locking

$results = $this->builder->table('users')
    ->where('id', '=', 1)
    ->lockForUpdate()
    ->get();

ModelQuery

ModelQuery provides a model-specific query builder that returns Model instances.

Basic Usage

// Get all users
$users = User::query()->get();

// Get first user
$user = User::query()->first();

// Find by ID
$user = User::query()->id(1)->first();

Where Clauses

// Where clause
$users = User::query()
    ->where('status', '=', 'active')
    ->get();

// Where NULL
$users = User::query()
    ->whereNull('deleted_at')
    ->get();

Soft Deletes

// Only trashed (soft deleted)
$deletedUsers = User::query()->onlyTrashed()->get();

// Soft delete
$user = User::query()->id(1)->first();
$user->softDelete();

// Force delete
User::query()->id(1)->forceDelete();

Eager Loading

// Eager load relationships
$users = User::query()
    ->with('profile')
    ->get();

// Nested relationships
$users = User::query()
    ->with('profile', 'posts')
    ->get();

CRUD Operations

// Insert
User::query()->insert(['email' => 'user@example.com']);

// Update
User::query()
    ->where('id', '=', 1)
    ->update(['email' => 'newemail@example.com']);

Relationships

ForgeSqlOrm supports three types of relationships: HasOne, HasMany, and BelongsTo.

Defining Relationships

Use the #[Relate] attribute to define relationships: 

use App\Modules\ForgeSqlOrm\ORM\Values\Relate;
use App\Modules\ForgeSqlOrm\ORM\Values\RelationKind;

#[Table("users")]
class User extends Model
{
    use CanLoadRelations;

    // HasOne relationship
    #[Relate(RelationKind::HasOne, Profile::class, "user_id")]
    public function profile(): Relation
    {
        return self::describe(__FUNCTION__);
    }

    // HasMany relationship
    #[Relate(RelationKind::HasMany, Post::class, "user_id")]
    public function posts(): Relation
    {
        return self::describe(__FUNCTION__);
    }
}

#[Table("posts")]
class Post extends Model
{
    // BelongsTo relationship
    #[Relate(RelationKind::BelongsTo, User::class, "user_id")]
    public function user(): Relation
    {
        return self::describe(__FUNCTION__);
    }
}

Eager Loading

Eager loading prevents N+1 queries: 

// Eager load relationships
$users = User::query()
    ->with('profile')
    ->get();

// Access relationship
foreach ($users as $user) {
    $profile = $user->profile; // Already loaded, no query
}

Lazy Loading

Lazy loading loads relationships on demand: 

$user = User::query()->id(1)->first();
$user->load('profile'); // Loads profile relationship

Nested Relationships

// Load nested relationships
$users = User::query()
    ->with('profile', 'posts')
    ->get();

Type Casting

ForgeSqlOrm automatically casts database values to PHP types using the Cast enum.

Cast Enum Values

enum Cast: string
{
    case INT = "int";
    case FLOAT = "float";
    case BOOL = "bool";
    case STRING = "string";
    case JSON = "json";
    case DATE = "date";
    case DATETIME = "datetime";
    case TIMESTAMP = "timestamp";
    case ENUM = "enum";
}

Using Cast in Models

#[Table("users")]
class User extends Model
{
    #[Column(primary: true, cast: Cast::INT)]
    public int $id;

    #[Column(cast: Cast::STRING)]
    public string $email;

    #[Column(cast: Cast::JSON)]
    public ?UserMetadataDto $metadata; // Casts to DTO

    #[Column(cast: Cast::DATETIME)]
    public ?DateTimeImmutable $created_at;
}

DTO Support

JSON columns can be cast to DTOs: 

#[Column(cast: Cast::JSON)]
public ?UserMetadataDto $metadata;

// Automatically casts JSON string to UserMetadataDto instance
// when loading from database

Enum Support

#[Column(cast: Cast::ENUM)]
public ?UserStatus $status; // BackedEnum instance

Date Types

Date types are cast to DateTimeImmutable: 

#[Column(cast: Cast::DATE)]
public ?DateTimeImmutable $birth_date;

#[Column(cast: Cast::DATETIME)]
public ?DateTimeImmutable $created_at;

#[Column(cast: Cast::TIMESTAMP)]
public ?DateTimeImmutable $updated_at;

Traits

ForgeSqlOrm provides traits for common model functionality.

HasTimeStamps

Automatically adds created_at and updated_at columns: 

use App\Modules\ForgeSqlOrm\Traits\HasTimeStamps;

#[Table("users")]
class User extends Model
{
    use HasTimeStamps;

    // Automatically adds:
    // public ?DateTimeImmutable $created_at = null;
    // public ?DateTimeImmutable $updated_at = null;
}

The save() method automatically sets these timestamps:

  • created_at is set on insert
  • updated_at is set on insert and update

HasMetaData

Automatically adds a metadata JSON column: 

use App\Modules\ForgeSqlOrm\Traits\HasMetaData;

#[Table("profiles")]
class Profile extends Model
{
    use HasMetaData;

    // Automatically adds:
    // public ?array $metadata = null;
}

Soft Deletes

ForgeSqlOrm supports soft deletes, allowing records to be marked as deleted without actually removing them from the database.

Soft Delete Column

Models use a deleted_at column (configurable via SOFT_DELETE_COLUMN constant): 

// Default soft delete column
protected const string SOFT_DELETE_COLUMN = 'deleted_at';

Soft Deleting Records

$user = User::query()->id(1)->first();
$user->delete(); // Sets deleted_at timestamp

Querying Soft Deleted Records

// Only trashed (soft deleted) records
$deletedUsers = User::query()->onlyTrashed()->get();

// Normal queries exclude soft deleted records
$activeUsers = User::query()->get(); // Excludes soft deleted

Force Delete

// Force delete (permanently remove)
$user = User::query()->id(1)->first();
$user->forceDelete();

// Or via ModelQuery
User::query()->id(1)->forceDelete();

Protected & Hidden Fields

ForgeSqlOrm provides two ways to exclude fields from serialization: protected fields and hidden fields.

Protected Fields

Use #[ProtectedFields] to exclude fields from toArray() and JSON serialization: 

#[Table("users")]
#[ProtectedFields(['password'])]
class User extends Model
{
    #[Column(cast: Cast::STRING)]
    public string $password; // Excluded from toArray()/JSON
}

$user = User::query()->id(1)->first();
$array = $user->toArray(); // password is excluded
$json = json_encode($user); // password is excluded

Hidden Fields

Use #[Hidden] attribute on individual fields: 

#[Table("users")]
class User extends Model
{
    #[Hidden]
    #[Column(cast: Cast::STRING)]
    public string $internal_field; // Hidden from toArray()/JSON
}

Use Cases

  • Passwords: Never expose passwords in API responses
  • Internal Fields: Hide internal tracking or metadata fields
  • Sensitive Data: Protect sensitive information from serialization

Repository Pattern

The repository pattern provides a clean data access layer with built-in caching.

Repository Interface

interface Repository
{
    public function create(mixed $data): Model;
    public function update(int|string $id, mixed $data): ?Model;
    public function delete(int|string $id): bool;
    public function find(int|string $id): ?Model;
    public function findBy(string $field, mixed $value): ?Model;
    public function findAll(): array;
}

RecordRepository Base Class

Extend RecordRepository for common CRUD operations: 

use App\Modules\ForgeSqlOrm\ORM\RecordRepository;

final class UserRepository extends RecordRepository
{
    protected function getModelClass(): string
    {
        return User::class;
    }
}

Custom Find Methods

final class UserRepository extends RecordRepository
{
    public function findById(int $id): ?User
    {
        return parent::find($id);
    }

    public function findByIdentifier(string $identifier): ?User
    {
        $key = $this->cache->generateKey($this->tableName, 'findByIdentifier', $identifier);
        $cached = $this->cache->get($key);
        
        if ($cached !== null) {
            return $cached;
        }
        
        $user = User::query()->where('identifier', '=', $identifier)->first();
        
        if ($user !== null) {
            $this->cache->set($key, $user);
        }
        
        return $user;
    }

    public function findByEmail(string $email): ?User
    {
        $key = $this->cache->generateKey($this->tableName, 'findByEmail', $email);
        $cached = $this->cache->get($key);
        
        if ($cached !== null) {
            return $cached;
        }
        
        $user = User::query()->where('email', '=', $email)->first();
        
        if ($user !== null) {
            $this->cache->set($key, $user);
        }
        
        return $user;
    }
}

Cache Invalidation

public function create(mixed $data): User
{
    $user = new User();
    $user->email = $data->email;
    $user->save();
    
    // Invalidate cache
    $this->cache->invalidate($this->tableName);
    
    return $user;
}

Query Caching

ForgeSqlOrm provides built-in query caching for performance optimization.

QueryCache Class

QueryCache is a simple in-memory cache for query results: 

// Default TTL: 3600 seconds (1 hour)
$cache = new QueryCache(3600);

// Get from cache
$cached = $cache->get($key);

// Set in cache
$cache->set($key, $value);

// Forget specific key
$cache->forget($key);

// Invalidate table cache
$cache->invalidate('users');

// Clear all cache
$cache->clear();

Cache Key Generation

$key = $cache->generateKey('users', 'findByIdentifier', $identifier);
// Generates: "users:findByIdentifier:value"

Automatic Caching in Repositories

RecordRepository automatically uses QueryCache for find operations: 

// Repository automatically caches find() results
$user = $repository->find(1); // Cached
$user = $repository->find(1); // Returns from cache

Cache Invalidation

Cache is automatically invalidated on create/update/delete: 

// Create/update/delete invalidates table cache
$repository->create($data); // Invalidates 'users' cache
$repository->update(1, $data); // Invalidates 'users' cache
$repository->delete(1); // Invalidates 'users' cache

Transactions

ForgeSqlOrm provides full transaction support via QueryBuilder.

Transaction Methods

// Begin transaction
$this->builder->beginTransaction();

// Commit transaction
$this->builder->commit();

// Rollback transaction
$this->builder->rollback();

// Check if in transaction
$inTransaction = $this->builder->inTransaction();

Transaction Commit Example

try {
    $this->builder->beginTransaction();
    $id = $this->builder->table('example_table')
        ->insert(['name' => 'orm_transaction_commit']);
    $this->builder->commit();
    return ['status' => 'committed', 'inserted_id' => $id];
} catch (\Exception $e) {
    $this->builder->rollback();
    return ['status' => 'error', 'message' => $e->getMessage()];
}

Transaction Rollback Example

// Count before transaction
$countBefore = $this->builder->table('example_table')
    ->where('name', '=', 'orm_transaction_rollback')
    ->count();

try {
    $this->builder->beginTransaction();
    $this->builder->table('example_table')
        ->insert(['name' => 'orm_transaction_rollback']);
    throw new \Exception('Simulated error to trigger rollback');
    $this->builder->commit();
} catch (\Exception $e) {
    $this->builder->rollback();
    
    // Count after rollback
    $countAfter = $this->builder->table('example_table')
        ->where('name', '=', 'orm_transaction_rollback')
        ->count();
    
    // Verify rollback worked
    return [
        'status' => 'rolled_back',
        'count_before' => $countBefore,
        'count_after' => $countAfter,
        'rollback_worked' => $countAfter == $countBefore
    ];
}

Raw SQL Queries

ForgeSqlOrm supports raw SQL queries when you need full control over the query.

raw() Method

Execute raw SQL with parameter binding: 

$results = $this->builder->raw(
    "SELECT * FROM users WHERE status = :status",
    [':status' => 'active']
);

whereRaw() Method

Add raw WHERE clauses to query builder chain: 

$results = $this->builder
    ->table('users')
    ->whereRaw('status = :status', [':status' => 'active'])
    ->get();

Combining QueryBuilder with Raw SQL

$results = $this->builder
    ->table('users')
    ->select('id', 'email', 'identifier')
    ->where('status', '=', 'active')
    ->whereRaw('identifier IS NOT NULL', [])
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

Security Note: Always use parameter binding with raw SQL to prevent SQL injection attacks. Never concatenate user input directly into SQL queries.

Usage Examples

Comprehensive examples demonstrating ForgeSqlOrm usage.

Model Definition Examples

User model with relationships, casting, and protected fields: 

use App\Modules\ForgeSqlOrm\ORM\Attributes\{Table, Column, ProtectedFields};
use App\Modules\ForgeSqlOrm\ORM\Values\{Cast, Relate, RelationKind};
use App\Modules\ForgeSqlOrm\Traits\HasTimeStamps;

#[Table("users")]
#[ProtectedFields(['password'])]
class User extends Model
{
    use HasTimeStamps;
    use CanLoadRelations;

    #[Column(primary: true, cast: Cast::INT)]
    public int $id;

    #[Column(cast: Cast::STRING)]
    public string $status;

    #[Column(cast: Cast::STRING)]
    public string $identifier;

    #[Column(cast: Cast::STRING)]
    public string $email;

    #[Column(cast: Cast::STRING)]
    public string $password; // Protected from serialization

    #[Column(cast: Cast::JSON)]
    public ?UserMetadataDto $metadata;

    #[Relate(RelationKind::HasOne, Profile::class, "user_id")]
    public function profile(): Relation
    {
        return self::describe(__FUNCTION__);
    }
}

Profile model with traits and nullable fields: 

use App\Modules\ForgeSqlOrm\Traits\{HasMetaData, HasTimeStamps};

#[Table("profiles")]
class Profile extends Model
{
    use HasTimeStamps;
    use HasMetaData;

    #[Column(primary: true, cast: Cast::INT)]
    public int $id;

    #[Column(cast: Cast::INT)]
    public int $user_id;

    #[Column(cast: Cast::STRING)]
    public string $first_name;

    #[Column(cast: Cast::STRING)]
    public ?string $last_name; // Nullable

    #[Column(cast: Cast::STRING)]
    public ?string $avatar; // Nullable
}

Post model with tenant scoping: 

use App\Modules\ForgeMultiTenant\Attributes\TenantScoped;
use App\Modules\ForgeMultiTenant\Traits\TenantScopedTrait;

#[TenantScoped]
#[Table("posts")]
class Post extends Model
{
    use HasTimeStamps;
    use HasMetaData;
    use TenantScopedTrait;

    #[Column(primary: true, cast: Cast::STRING)]
    public int $id;

    #[Column(cast: Cast::STRING)]
    public string $title;

    #[Column(cast: Cast::STRING)]
    public string $content;

    #[Column(cast: Cast::STRING)]
    public string $tenant_id;
}

QueryBuilder Examples

Basic queries with where(), orderBy(), limit(): 

$results = $this->builder
    ->table('users')
    ->select('id', 'email', 'identifier')
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

ModelQuery Examples

Finding by ID and querying with conditions: 

// Find by ID
$user = User::query()->id(1)->first();

// Query with conditions
$activeUsers = User::query()
    ->where('status', '=', 'active')
    ->get();

// Eager load relationships
$users = User::query()
    ->with('profile')
    ->get();

Repository Examples

Using repositories with caching: 

// Find by ID (cached)
$user = $repository->findById(1);

// Find by identifier (cached)
$user = $repository->findByIdentifier('user-123');

// Find by email (cached)
$user = $repository->findByEmail('user@example.com');

// Create (invalidates cache)
$user = $repository->create($data);

Best Practices

Guidelines for using ForgeSqlOrm effectively.

QueryBuilder vs ModelQuery

  • Use QueryBuilder: For generic queries, raw SQL, or when you don't need Model instances
  • Use ModelQuery: When you need Model instances with relationships, type casting, and model methods

Relationship Loading Strategies

  • Eager Loading: Use with() to prevent N+1 queries when you know you'll need relationships
  • Lazy Loading: Use load() for on-demand loading when relationships might not always be needed
  • Avoid N+1: Always eager load relationships when iterating over collections

Caching Considerations

  • Use Repositories: Repositories provide automatic caching for find operations
  • Cache Invalidation: Ensure cache is invalidated on create/update/delete operations
  • Cache Keys: Use descriptive cache keys for debugging
  • TTL: Adjust TTL based on data volatility (default: 3600 seconds)

Transaction Usage

  • Always Use Try-Catch: Wrap transactions in try-catch blocks
  • Rollback on Error: Always rollback on exceptions
  • Keep Transactions Short: Minimize time spent in transactions
  • Verify Results: Verify transaction results when needed

Model Organization

  • Use Attributes: Always use #[Table] and #[Column] attributes
  • Type Casting: Always specify cast types for proper type conversion
  • Protected Fields: Use #[ProtectedFields] for sensitive data like passwords
  • Traits: Use HasTimeStamps and HasMetaData traits when needed
  • Relationships: Define relationships using #[Relate] attribute