Database & ORM API

DatabaseConnection

Manages database connections and provides low-level database operations. The DatabaseConnection class handles connection pooling, query execution, and transaction management for multiple database types.

Constructor & Configuration

__construct(array $config)

Create a new database connection with configuration.

$connection = new DatabaseConnection([
    'driver' => 'mysql',
    'host' => 'localhost',
    'port' => 3306,
    'database' => 'myapp',
    'username' => 'root',
    'password' => 'password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null
]);

Query Execution Methods

query(string $sql, array $bindings = []): array

Execute a raw SQL query and return results.

$results = $connection->query('SELECT * FROM users WHERE active = ?', [true]);
$user = $connection->query('SELECT * FROM users WHERE id = ?', [$id])[0] ?? null;

execute(string $sql, array $bindings = []): int

Execute a raw SQL statement and return affected rows.

$affected = $connection->execute('UPDATE users SET active = ? WHERE id = ?', [false, $id]);
$connection->execute('DELETE FROM sessions WHERE expired_at < ?', [now()]);

select(string $query, array $bindings = []): array

Execute a SELECT query and return results.

$users = $connection->select('SELECT * FROM users WHERE created_at > ?', ['2023-01-01']);
$count = $connection->select('SELECT COUNT(*) as total FROM users')[0]['total'];

Transaction Methods

beginTransaction(): void

Start a new database transaction.

$connection->beginTransaction();

commit(): void

Commit the current transaction.

$connection->commit();

rollBack(): void

Rollback the current transaction.

$connection->rollBack();

transaction(callable $callback): mixed

Execute a callback within a transaction.

$result = $connection->transaction(function() use ($connection) {
    $userId = $connection->execute('INSERT INTO users (name, email) VALUES (?, ?)', ['John', 'john@example.com']);
    $connection->execute('INSERT INTO profiles (user_id, bio) VALUES (?, ?)', [$userId, 'Developer']);
    return $userId;
});

Model

Base ORM model class providing database table mapping, relationships, and query building capabilities. Models represent database tables and provide an intuitive way to interact with your data.

Basic Model Definition

Creating a Model

Extend the base Model class to create your own models.

class User extends Model
{
    protected string $table = 'users';
    protected string $primaryKey = 'id';
    protected array $fillable = ['name', 'email', 'password'];
    protected array $hidden = ['password', 'remember_token'];
    protected array $casts = [
        'email_verified_at' => 'datetime',
        'is_active' => 'boolean'
    ];
    
    // Define relationships
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
    
    public function profile()
    {
        return $this->hasOne(Profile::class);
    }
    
    public function roles()
    {
        return $this->belongsToMany(Role::class);
    }
}

Query Methods

find(mixed $id): ?Model

Find a model by its primary key.

$user = User::find(1);
$user = User::find('550e8400-e29b-41d4-a716-446655440000');

where(string $column, mixed $operator = null, mixed $value = null): Builder

Add a where clause to the query.

$users = User::where('active', true)->get();
$users = User::where('age', '>', 18)->get();
$users = User::where('email', 'like', '%@example.com')->get();

get(): Collection

Execute the query and get the results.

$users = User::where('active', true)->get();
$users = User::where('role', 'admin')->limit(10)->get();

first(): ?Model

Get the first result of the query.

$user = User::where('email', 'john@example.com')->first();
$admin = User::where('role', 'admin')->first();

CRUD Operations

create(array $attributes): Model

Create a new model instance and save it to the database.

$user = User::create([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT)
]);

save(): bool

Save the model to the database.

$user = new User();
$user->name = 'Jane Doe';
$user->email = 'jane@example.com';
$user->save();

update(array $attributes): bool

Update the model in the database.

$user->update(['name' => 'John Smith']);
User::where('role', 'user')->update(['active' => false]);

delete(): bool

Delete the model from the database.

$user->delete();
User::where('last_login', '<', '2023-01-01')->delete();

QueryBuilder

Provides a fluent interface for building database queries. The QueryBuilder allows you to construct complex SQL queries programmatically with method chaining and parameter binding.

Query Construction

table(string $table): QueryBuilder

Start a new query on the given table.

$users = QueryBuilder::table('users')->get();
$posts = QueryBuilder::table('posts')->where('published', true)->get();

WHERE Clauses

where(string $column, mixed $operator = null, mixed $value = null): QueryBuilder

Add a basic where clause to the query.

$users = QueryBuilder::table('users')
    ->where('active', true)
    ->where('age', '>', 18)
    ->where('email', 'like', '%@example.com')
    ->get();

whereIn(string $column, array $values): QueryBuilder

Add a where in clause to the query.

$users = QueryBuilder::table('users')
    ->whereIn('role', ['admin', 'moderator'])
    ->get();

whereBetween(string $column, array $values): QueryBuilder

Add a where between clause to the query.

$users = QueryBuilder::table('users')
    ->whereBetween('created_at', ['2023-01-01', '2023-12-31'])
    ->get();

whereNull(string $column): QueryBuilder

Add a where null clause to the query.

$users = QueryBuilder::table('users')
    ->whereNull('deleted_at')
    ->get();

JOIN Operations

join(string $table, string $first, string $operator = null, string $second = null): QueryBuilder

Add a join clause to the query.

$users = QueryBuilder::table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select('users.*', 'profiles.bio')
    ->get();

leftJoin(string $table, string $first, string $operator = null, string $second = null): QueryBuilder

Add a left join clause to the query.

$users = QueryBuilder::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', QueryBuilder::raw('COUNT(orders.id) as order_count'))
    ->groupBy('users.id')
    ->get();

Aggregation Methods

count(string $column = '*'): int

Get the count of records for the query.

$count = QueryBuilder::table('users')->count();
$activeCount = QueryBuilder::table('users')->where('active', true)->count();

max(string $column): mixed

Get the maximum value for the given column.

$maxPrice = QueryBuilder::table('products')->max('price');
$latestLogin = QueryBuilder::table('users')->max('last_login');

sum(string $column): float

Get the sum of values for the given column.

$totalSales = QueryBuilder::table('orders')->sum('amount');
$totalRevenue = QueryBuilder::table('orders')->where('status', 'completed')->sum('amount');

DatabaseConfig

Manages database configuration and connection settings. Provides a centralized way to configure database connections, manage multiple database environments, and handle connection pooling settings.

Configuration Methods

setConnection(string $name, array $config): void

Set configuration for a named connection.

$config = new DatabaseConfig();
$config->setConnection('mysql', [
    'driver' => 'mysql',
    'host' => 'localhost',
    'database' => 'myapp',
    'username' => 'root',
    'password' => 'password'
]);

$config->setConnection('redis', [
    'driver' => 'redis',
    'host' => 'localhost',
    'port' => 6379
]);

getConnection(string $name = null): array

Get configuration for a connection.

$mysqlConfig = $config->getConnection('mysql');
$defaultConfig = $config->getConnection(); // Gets default connection

setDefaultConnection(string $name): void

Set the default connection name.

$config->setDefaultConnection('mysql');

Usage Examples

Common patterns and examples for working with the Database & ORM classes.

Basic CRUD Operations

// Create
$user = User::create([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => password_hash('secret', PASSWORD_DEFAULT)
]);

// Read
$user = User::find(1);
$users = User::where('active', true)->get();
$admin = User::where('role', 'admin')->first();

// Update
$user->name = 'John Smith';
$user->save();
User::where('role', 'user')->update(['active' => false]);

// Delete
$user->delete();
User::where('last_login', '<', '2023-01-01')->delete();

Complex Queries with Relationships

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

// Get posts with user information
$posts = Post::with('user')->where('published', true)->get();

// Get users who have posts
$users = User::has('posts')->get();

// Get users with more than 5 posts
$users = User::has('posts', '>', 5)->get();

// Get posts with specific user
$posts = Post::whereHas('user', function($query) {
    $query->where('active', true);
})->get();

// Eager loading with conditions
$users = User::with(['posts' => function($query) {
    $query->where('published', true)
          ->orderBy('created_at', 'desc');
}])->get();

Transaction Management

// Manual transaction handling
$connection = new DatabaseConnection($config);
$connection->beginTransaction();

try {
    $user = User::create([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ]);
    
    $profile = Profile::create([
        'user_id' => $user->id,
        'bio' => 'Software Developer'
    ]);
    
    $connection->commit();
} catch (Exception $e) {
    $connection->rollBack();
    throw $e;
}

// Using transaction method
$result = $connection->transaction(function() {
    $user = User::create(['name' => 'Jane Doe', 'email' => 'jane@example.com']);
    Profile::create(['user_id' => $user->id, 'bio' => 'Designer']);
    return $user;
});

Raw SQL Queries

// Complex joins
$results = QueryBuilder::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->select('users.name', QueryBuilder::raw('SUM(order_items.quantity * order_items.price) as total_spent'))
    ->where('orders.status', 'completed')
    ->groupBy('users.id')
    ->having('total_spent', '>', 1000)
    ->orderBy('total_spent', 'desc')
    ->limit(10)
    ->get();

// Raw queries with bindings
$users = $connection->query('
    SELECT u.*, COUNT(p.id) as post_count 
    FROM users u 
    LEFT JOIN posts p ON u.id = p.user_id 
    WHERE u.active = ? 
    GROUP BY u.id 
    HAVING post_count > ?
    ORDER BY post_count DESC
', [true, 5]);

Best Practices

Recommended patterns and guidelines for working with the Database & ORM layer.

Do's

• Use prepared statements and parameter binding
• Leverage eager loading to avoid N+1 queries
• Use transactions for related operations
• Index frequently queried columns
• Use query builders for complex queries
• Validate data before database operations
• Use migrations for schema changes
• Implement proper error handling

Don'ts

• Don't use raw SQL for simple queries
• Don't ignore SQL injection vulnerabilities
• Don't load all relationships at once
• Don't ignore database connection pooling
• Don't skip transaction rollback on errors
• Don't ignore query performance optimization
• Don't store sensitive data in plain text
• Don't ignore database backup strategies

API Reference