TinyMVC Documentation

Database

TinyMVC provides a fluent query builder and database abstraction layer supporting multiple database drivers.

Configuration

Configure your database connection in env.php:

SQLite Configuration

'database' => [
    'driver' => 'sqlite',
    'file' => __DIR__ . '/database/sqlite.db'
]

MySQL Configuration

'database' => [
    'driver' => 'mysql',
    'host' => 'localhost',
    'port' => 3306,
    'name' => 'test_db',
    'user' => 'root',
    'password' => 'password'
]

Query Builder

The query builder provides a fluent interface for database operations.

Basic Usage

// Get all records
$users = query('users')->result();

// Get single record
$user = query('users')->where('id', 1)->first();

// Count records
$count = query('users')->where('active', 1)->count();

Inserting Data

// Insert single record
query('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);

// Insert multiple records
query('users')->insert([
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com']
]);

Updating Data

// Update single record
query('users')
    ->where('id', 1)
    ->update(['name' => 'Updated Name']);

// Update multiple records
query('users')
    ->where('status', 'pending')
    ->update(['status' => 'active']);

Deleting Data

// Delete single record
query('users')->where('id', 1)->delete();

// Delete multiple records
query('users')->where('last_login', '<', '2023-01-01')->delete();

Complex Queries

// Join tables
query('posts')
    ->as('p')
    ->select(['p.*', 'u.name as author'])
    ->join('users as u', 'u.id = p.user_id')
    ->where('p.published', 1)
    ->result();

// Conditional where clauses
query('products')
    ->where('price', '>', 100)
    ->orWhere('featured', 1)
    ->result();

Database Helpers

// Get database instance
$db = database();

// Alternative helper
$db = db();

// Create query builder instance
$query = query('table_name');

Transactions

try {
    database()->beginTransaction();
    
    query('accounts')
        ->where('id', 1)
        ->update(['balance' => 1000]);
        
    query('transactions')
        ->insert(['account_id' => 1, 'amount' => 1000]);
        
    database()->commit();
} catch (Exception $e) {
    database()->rollback();
    throw $e;
}

Performance Tip: Use select() to specify only the columns you need rather than selecting all columns with *.

Full Example

// Blog post query example
$posts = query('posts')
    ->as('p')
    ->select([
        'p.id',
        'p.title',
        'p.content',
        'u.name as author',
        'c.name as category'
    ])
    ->join('users as u', 'u.id = p.user_id')
    ->join('categories as c', 'c.id = p.category_id')
    ->where('p.published', 1)
    ->where('p.created_at', '>', '2023-01-01')
    ->result();

// User registration example
try {
    database()->beginTransaction();
    
    $userId = query('users')->insert([
        'name' => request('name'),
        'email' => request('email'),
        'password' => password_hash(request('password'), PASSWORD_DEFAULT)
    ]);
    
    query('profiles')->insert([
        'user_id' => $userId,
        'bio' => request('bio')
    ]);
    
    database()->commit();
} catch (Exception $e) {
    database()->rollback();
    return response()->with('error', 'Registration failed')->back();
}

Database Models

TinyMVC provides an active record implementation through the Model class, allowing you to interact with your database tables using object-oriented syntax.

Creating Models

Models are stored in the app/Models folder and extend the base Spark\Database\Model class:

<?php

namespace App\Models;

use Spark\Database\Model;

class User extends Model
{
    /**
     * The database table name
     */
    public static string $table = 'users';

    /**
     * Fields that are not mass assignable
     */
    protected array $guarded = ['id', 'created_at'];
}

Basic CRUD Operations

Creating Records

// Create and save in one step
$user = User::create([
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);

// Or create then save
$user = new User();
$user->name = 'John Doe';
$user->email = 'john@example.com';
$user->save();

Reading Records

// Find by ID
$user = User::find(1);

// Get all records
$users = User::result();

// Get first record
$user = User::first();

// Get last record
$user = User::last();

Updating Records

// Find then update
$user = User::find(1);
$user->name = 'Updated Name';
$user->save();

// Mass update
User::where('status', 'pending')
    ->update(['status' => 'active']);

Deleting Records

// Find then delete
$user = User::find(1);
$user->remove();

// Direct delete
User::where('last_login', '<', '2020-01-01')->delete();

Query Builder Integration

Models inherit all QueryBuilder methods:

// Complex queries
$users = User::where('status', 'active')
    ->where('created_at', '>', '2023-01-01')
    ->orderAsc('name')
    ->result();

// Joins
$posts = Post::select(['p.*', 'u.name as author'])
    ->as('p')
    ->join('users as u', 'u.id = p.user_id')
    ->where('p.published', 1)
    ->result();

Pagination

The paginate method makes it easy to paginate query results:

// Basic pagination (10 items per page)
$users = User::paginate();

// Custom pagination (25 items per page)
$users = User::paginate(25);

// Displaying paginated results
foreach ($users->getData() as $user) {
    echo $user->name;
}

// Rendering pagination links
echo $users->getLinks();

Pagination Methods

// Check if data exists
if ($users->hasData()) {
    // Process data
}

// Get paginated data
$data = $users->getData();

// Custom pagination links
if ($users->hasLinks()) {
    echo $users->getLinks(
        links: 3, // Number of links to show
        classes: [
            'container' => 'pagination',
            'item' => 'page-item',
            'link' => 'page-link'
        ],
        entity: [
            'previous' => '«',
            'next' => '»'
        ]
    );
}

Mass Assignment

Control which fields can be mass assigned:

class User extends Model
{
    // Only these fields can be mass assigned
    protected array $fillable = ['name', 'email'];
    
    // Or specify guarded fields (opposite of fillable)
    protected array $guarded = ['id', 'is_admin'];
}

Model Methods

// Convert model to array
$array = $user->toArray();

// Create model from array
$user = User::load(['name' => 'John', 'email' => 'john@example.com']);

// Get query builder instance
$query = User::query();

Full Example

// Blog controller example
public function index()
{
    $posts = Post::with('author')
        ->where('published', 1)
        ->orderDesc('created_at')
        ->paginate(15);

    return view('posts.index', ['posts' => $posts]);
}

// User registration example
public function store(Hash $hash)
{
    $inserted = User::insert([
        'name' => request('name'),
        'email' => request('email'),
        'password' => $hash->hashPassword(request('password'))
    ]);

    return response()
        ->with('message', 'Registration successful!')
        ->redirect('/dashboard');
}

ORM Relationships

TinyMVC provides powerful ORM capabilities through the HasOrm trait, allowing you to define and work with model relationships.

Defining Relationships

Relationships are defined in the orm() method of your model:

<?php

namespace App\Models;

use Spark\Database\Model;
use Spark\Database\Traits\HasOrm;

class User extends Model
{
    use HasOrm;

    protected function orm(): array
    {
        return [
            // One-to-many relationship
            'posts' => [
                'has' => 'many',
                'model' => Post::class,
                'foreignKey' => 'user_id'
            ],
            
            // One-to-one relationship
            'profile' => [
                'has' => 'one',
                'model' => Profile::class,
                'foreignKey' => 'user_id',
                'localKey' => 'id'
            ],
            
            // Many-to-many relationship
            'roles' => [
                'has' => 'many-x',
                'model' => Role::class,
                'table' => 'user_roles', // Pivot table
                'foreignKey' => 'user_id',
                'localKey' => 'role_id',
                'callback' => function($query) {
                    // Additional query constraints
                    return $query->where('active', 1);
                }
            ]
        ];
    }
}

Relationship Types

One-to-One

'profile' => [
    'has' => 'one',
    'model' => Profile::class,
    'foreignKey' => 'user_id'
]

One-to-Many

'posts' => [
    'has' => 'many',
    'model' => Post::class,
    'foreignKey' => 'author_id'
]

Many-to-Many

'roles' => [
    'has' => 'many-x',
    'model' => Role::class,
    'table' => 'user_roles', // Pivot table
    'foreignKey' => 'user_id',
    'localKey' => 'role_id'
]

Loading Relationships

Eager Loading (with)

// Load single relationship
$users = User::with('posts')->result();

// Load multiple relationships
$users = User::with(['posts', 'profile'])->result();

// Load all relationships
$users = User::with('*')->result();

Lazy Loading

$user = User::find(1);

// Access relationship (loaded when first accessed)
$posts = $user->posts;
$profile = $user->profile;

Advanced Usage

Query Callbacks

'recentPosts' => [
    'has' => 'many',
    'model' => Post::class,
    'foreignKey' => 'user_id',
    'callback' => function($query) {
        return $query->where('created_at', '>', '2023-01-01')
                   ->orderDesc('created_at');
    }
]

Disabling Lazy Loading

'profile' => [
    'has' => 'one',
    'model' => Profile::class,
    'foreignKey' => 'user_id',
    'lazy' => false // Must be loaded explicitly with with()
]

Manual Relationship Loading

$users = User::all();
User::runOrm('posts', $users); // Load posts for all users

Full Example

// Blog controller with eager loading
public function show($userId)
{
    $user = User::with(['posts', 'profile'])
        ->where('id', $userId)
        ->first();
    
    return view('users/show', compact('user'));
}

// Accessing relationships in views
<h1><?= $user->name ?>></h1>
<h2>Profile</h2>
<p><?= $user->profile->bio ?>></p>

<h2>Posts</h2>
<?php foreach ($user->posts as $post): ?>
    <article>
        <h3><?= $post->title ?>></h3>
        <p><?= $post->excerpt ?>></p>
    </article>
<?php endforeach ?>

Performance Tip: Always use eager loading (with()) when you know you'll need related data to avoid the N+1 query problem.

Error Handling

try {
    $users = User::with('undefined_relation')->result();
} catch (UndefinedOrmException $e) {
    // Handle undefined relationship
}

try {
    $user->posts; // Attempt lazy loading when disabled
} catch (OrmDisabledLazyLoadingException $e) {
    // Handle disabled lazy loading
}

Database Migrations & Schema Builder

TinyMVC provides a powerful database migration system and fluent schema builder for managing your database structure.

Creating Migrations

Generate a new migration file using the Spark CLI:

php spark make:migration users

This creates a new file in database/migrations/ with timestamp prefix:

<?php

use Spark\Database\Schema\Blueprint;
use Spark\Database\Schema\Schema;

return new class {
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique()->required();
            $table->string('password')->required();
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

Running Migrations

# Run all pending migrations
php spark migrate:run

# Rollback the last migration
php spark migrate:rollback

# Rollback multiple migrations
php spark migrate:rollback 2

# Reset and re-run all migrations
php spark migrate:fresh

Schema Builder

Column Types

$table->id(); // Auto-incrementing ID
$table->string('name', 100); // String with optional length
$table->text('description'); // Long text
$table->integer('votes'); // Integer
$table->boolean('active'); // Boolean
$table->decimal('amount', 8, 2); // Decimal with precision
$table->date('created_at'); // Date only
$table->dateTime('updated_at'); // Date and time
$table->timestamp('published_at'); // Timestamp
$table->enum('status', ['active', 'pending']); // Enumeration

Column Modifiers

$table->string('email')->unique(); // Unique constraint
$table->string('name')->nullable(); // Allows NULL values
$table->integer('count')->default(0); // Default value
$table->string('ip')->required(); // NOT NULL constraint
$table->integer('views')->unsigned(); // Unsigned integer

Indexes

// Single column index
$table->string('email')->unique();

// Composite index
$table->index(['account_id', 'created_at']);

// Primary key
$table->primary(['id', 'parent_id']);

Foreign Keys

// Simple foreign key
$table->foreignId('user_id')->constrained();

// Custom foreign key
$table->foreign('author_id')->references('id')->on('authors');

// With actions
$table->foreign('user_id')
    ->references('id')
    ->on('users')
    ->onDelete('cascade');

Modifying Tables

Schema::table('users', function (Blueprint $table) {
    $table->dropColumn('old_column');
    $table->dropIndex(['email', 'status']);
    $table->dropForeign('user_id', 'users');
});

Migration Structure

Each migration class contains two methods:

up() - Runs when migrating forward
down() - Runs when rolling back

Best Practices

Keep migrations focused on one schema change
Always implement the down() method
Use descriptive migration names
Test migrations in development before production
Consider data migrations for large changes

Full Example

<?php

use Spark\Database\Schema\Blueprint;
use Spark\Database\Schema\Schema;

return new class {
    public function up(): void
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')
                ->constrained('users')
                ->cascadeOnDelete();
            $table->string('title');
            $table->string('slug')->unique();
            $table->text('content');
            $table->boolean('published')->default(false);
            $table->timestamp('published_at')->nullable();
            $table->timestamps();
            
            $table->index(['title', 'published_at']);
        });

        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('post_id')->constrained('posts');
            $table->foreignId('user_id')->constrained('users');
            $table->text('body');
            $table->timestamps();
        });
    }

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