Database & Eloquent

Configuration & Connections

Database connections are defined in config/database.php. Lacelium supports MySQL, MariaDB, PostgreSQL, and SQLite out of the box. Connection settings are loaded from your environment variables.

// config/database.php
return [
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        'mysql' => [
            'driver'    => 'mysql',
            'host'      => env('DB_HOST', '127.0.0.1'),
            'port'      => env('DB_PORT', 3306),
            'database'  => env('DB_DATABASE', 'lacelium'),
            'username'  => env('DB_USERNAME', 'root'),
            'password'  => env('DB_PASSWORD', ''),
            'charset'   => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix'    => '',
            'strict'    => true,
        ],
        'sqlite' => [
            'driver'   => 'sqlite',
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix'   => '',
        ],
    ],
];

Defining Eloquent Models

Models live inside app/Models. Extend the base Eloquent Model class to interact with database tables through an expressive object-oriented API.

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Task extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'tasks';

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'title',
        'description',
        'is_completed',
    ];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'is_completed' => 'boolean',
        'created_at'   => 'datetime',
    ];
}

Basic CRUD Operations

Eloquent allows you to retrieve, create, update, and delete database records using intuitive, chained methods.

Retrieving Records

// Fetch all records
$tasks = Task::all();

// Find by primary key (or fail)
$task = Task::find(1);
$task = Task::findOrFail(1);

// Query with constraints
$completed = Task::where('is_completed', true)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

// First matching record
$active = Task::where('is_completed', false)->first();

Creating & Updating

// Insert new record via mass-assignment
$task = Task::create([
    'title'        => 'Audit API Security',
    'is_completed' => false,
]);

// Update an existing model instance
$task->is_completed = true;
$task->save();

// Batch update via query
Task::where('is_completed', false)
    ->where('due_date', '<', now())
    ->update(['status' => 'overdue']);

Deleting Records

// Delete via instance
$task->delete();

// Delete by primary key
Task::destroy(1);
Task::destroy([1, 2, 3]);

// Delete matching query
Task::where('is_completed', true)->delete();

Eloquent Relationships

Relationships are defined as methods on your Eloquent model classes. Lacelium supports all core relationship types:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class User extends Model
{
    // One-to-Many: A user has many tasks
    public function tasks(): HasMany
    {
        return $this->hasMany(Task::class, 'user_id');
    }
}

class Task extends Model
{
    // Inverse: A task belongs to a user
    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}

Eager Loading (Preventing N+1 Queries)

Always eager-load relationships using with() to reduce execution time from dozens of round-trips to a single optimized query:

// Eager load tasks for all users (2 queries total instead of N+1)
$users = User::with('tasks')->get();

foreach ($users as $user) {
    echo $user->name . ' has ' . $user->tasks->count() . ' tasks';
}

Atomic Sequences & Auto-Ordering

Eliminate traditional, race-condition-prone SELECT MAX(order) followed by INSERT statements. Lacelium provides atomic sequence calculation that executes in a single atomic SQL transaction.

1. Query Builder / Capsule (next_add & next_sub)

use Illuminate\Database\Capsule\Manager as Capsule;

Capsule::table('project_tasks')->insert([
    'task_id'    => uuidV4(),
    'project_id' => $projectId,
    'title'      => 'Compile Production Assets',
    // Calculates MAX(order) + 1 scoped to project_id in 1 query:
    'order'      => next_add(scope: ['project_id']),
    'priority'   => next_sub(), // MIN(priority) - 1 globally
    'created_at' => date('Y-m-d H:i:s'),
]);

2. Eloquent Model Trait (HasOrder)

namespace App\Models;

use Lacelium\Core\Database\Model;
use Lacelium\Core\Database\Traits\HasUuid;
use Lacelium\Core\Database\Traits\HasOrder;

class ProjectTask extends Model
{
    use HasUuid, HasOrder;

    protected $table = 'project_tasks';
    protected $primaryKey = 'task_id';
    public $incrementing = false;
    protected $keyType = 'string';

    // Auto-increment sequence scoped to project_id
    protected $nextAdditional = 'order';
    protected $nextAdditionalScope = ['project_id'];
}

// Inserting automatically assigns UUID and Order atomically:
ProjectTask::create([
    'project_id' => $projectId,
    'title'      => 'Optimize Database Indexes',
]);

Simplified Joins & Nested Output (joinTable)

Lacelium eliminates multi-line join boilerplate. Related tables can be joined in a single declarative line, and output columns are automatically hydrated into clean nested associative structures.

// Query Builder simplified join
$users = Capsule::table('users')
    ->joinTable('role_id', 'roles')
    ->where('active', 1)
    ->get();

// Result structure is automatically nested under role_id:
// [
//     'user_id' => 1,
//     'name'    => 'Sarah Connor',
//     'role_id' => [
//         'id'          => 2,
//         'name'        => 'Administrator',
//         'permissions' => ['all']
//     ]
// ]

SQLar Storage Engine & Archiver

Lacelium natively integrates the SQLite Archiver (SQLar) specification. Single-file transactional SQLite databases can be utilized as compressed virtual filesystems and offline storage archives.

// Put, retrieve, and inspect compressed files in SQLar
sqlar()->put('reports/q3_summary.pdf', $fileData);
$binary = sqlar()->get('reports/q3_summary.pdf');

// Archive an entire folder with Deflate compression
sqlar()->archiveDir(__DIR__ . '/storage/uploads');

// Query files via standard Eloquent Capsule
$largeFiles = table('sqlar', 'sqlar')->where('sz', '>', 1048576)->get();

SQLar CLI Management

# Create a new compressed SQLar archive
php dipperlook sqlar:create storage/backups/archive.sqlar

# Add a directory into the archive
php dipperlook sqlar:add storage/backups/archive.sqlar public/uploads uploads

# List contents and compression ratio
php dipperlook sqlar:list storage/backups/archive.sqlar

# Extract archive contents
php dipperlook sqlar:extract storage/backups/archive.sqlar storage/restored

Next Steps

• Define structured schema changes and rollback scripts in Migrations Guide.
• Paginate large result sets with search and ordering in Pagination Guide.
• Implement client-rendered data grids in DataTable Pattern.