PHP code example of luany / database

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

luany / database example snippets

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

use Luany\Database\QueryBuilder;

$qb = new QueryBuilder($connection);

// 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', '=', '[email protected]')
    ->first();

// 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
$qb->table('users')->whereNull('deleted_at')->get();
$qb->table('users')->whereNotNull('email_verified_at')->get();

$qb->table('users')
    ->orderBy('name', 'ASC')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->offset(20)
    ->get();

$qb->table('users')->insert([
    'name'  => 'António',
    'email' => '[email protected]',
]);

$lastId = $qb->lastInsertId();

$affected = $qb->table('users')
    ->where('id', '=', 42)
    ->update(['name' => 'António Ngola']);
// returns int — number of affected rows

$affected = $qb->table('users')
    ->where('id', '=', 42)
    ->delete();
// returns int — number of affected rows

$total = $qb->table('users')->count();

$filtered = $qb->table('users')
    ->where('active', '=', 1)
    ->count();

$exists = $qb->table('users')
    ->where('email', '=', '[email protected]')
    ->exists(); // bool

$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

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

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

User::setConnection($connection);

// Or pass a lazy closure (resolved on first use):
User::setConnection(fn() => Connection::make($config));

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

// Raw WHERE clause
$users = User::where('active = ? AND age > ?', [1, 18]);
$user  = User::firstWhere('email = ?', ['[email protected]']); // ?static

// Count
$total    = User::count();
$filtered = User::count('active = ?', [1]);

// Create — inserts and returns hydrated instance
$user = User::create(['name' => 'António', 'email' => '[email protected]']);

// Update — on an instance
$user->name = 'António Ngola';
$user->save();

// Shorthand update
$user->update(['name' => 'António Ngola', 'email' => '[email protected]']);

// 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' => '[email protected]']);

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

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

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

// hasOne($related, $foreignKey = null, $localKey = null)
// hasMany($related, $foreignKey = null, $localKey = null)
// belongsTo($related, $foreignKey = null, $ownerKey = null)

$user->posts; // query executed
$user->posts; // returns cached result — no second query

$user->getRelation('posts');           // load and cache
$user->setRelation('posts', $myArray); // set directly (bypasses query)

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

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

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

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

use Luany\Database\Migration\Migration;

class CreateUsersTable extends Migration
{
    public function up(\PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS `users` (
                `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                `name`       VARCHAR(255) NOT NULL,
                `email`      VARCHAR(150) NOT NULL UNIQUE,
                `deleted_at` DATETIME DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }

    public function down(\PDO $pdo): void
    {
        $pdo->exec("DROP TABLE IF EXISTS `users`");
    }
}

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

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', '[email protected]']);
        $stmt->execute(['Luany António',  '[email protected]']);
    }
}

use Luany\Database\Seeder\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run(\PDO $pdo): void
    {
        $this->call(UserSeeder::class);
        $this->call(ProductSeeder::class);
    }
}

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