Database Layer

Apartado de Salas uses PDO (PHP Data Objects) for database connectivity, providing a secure, object-oriented interface to MySQL. The database layer implements the Singleton pattern to ensure a single connection per request.

Database Architecture

The database layer consists of two main components:

Database Class - Manages PDO connection (Singleton pattern)
Model Classes - Encapsulate queries and business logic

Database Class

The Database class provides a centralized connection manager:

app/core/Database.php

<?php

class Database
{
    private static ?PDO $connection = null;

    /**
     * Returns an active PDO connection
     */
    public static function getConnection(): PDO
    {
        if (self::$connection === null) {
            self::connect();
        }

        return self::$connection;
    }

    /**
     * Creates the PDO connection
     */
    private static function connect(): void
    {
        $config = require dirname(__DIR__) . '/config/database.php';

        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";

        try {
            self::$connection = new PDO(
                $dsn,
                $config['user'],
                $config['password'],
                [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ]
            );
        } catch (PDOException $e) {
            die('Error de conexión a la base de datos');
        }
    }
}

Singleton Pattern

The Database class implements the Singleton pattern to ensure only one connection exists:

Static Property
Lazy Initialization
Private Constructor

A static property stores the single connection:

private static ?PDO $connection = null;

This property is shared across all instances and requests.

The connection is created only when first needed:

public static function getConnection(): PDO
{
    if (self::$connection === null) {
        self::connect();
    }
    return self::$connection;
}

The connect() method is private to prevent external instantiation:

private static function connect(): void
{
    // Connection logic
}

The Singleton pattern prevents connection overhead by reusing the same PDO instance throughout the request lifecycle.

PDO Configuration

The Database class configures PDO with secure defaults:

DSN (Data Source Name)

$dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";

The DSN specifies:

host - Database server hostname (e.g., localhost)
dbname - Database name (e.g., apartado_salas)
charset - Character encoding (e.g., utf8mb4)

PDO Attributes

Two critical attributes are configured:

[
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]

Error Mode
Fetch Mode

ERRMODE_EXCEPTION throws exceptions on database errors:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION

This allows proper error handling with try-catch blocks instead of checking return values.

FETCH_ASSOC returns rows as associative arrays:

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC

Example result:

[
    'id' => 1,
    'username' => 'admin',
    'email' => '[email protected]'
]

Database Configuration

Database credentials are stored in app/config/database.php:

app/config/database.php

<?php

return [
    'host'     => 'localhost',
    'dbname'   => 'apartado_salas',
    'user'     => 'root',
    'password' => '',
    'charset'  => 'utf8mb4',
];

Security: In production, database credentials should be stored in environment variables, not committed to version control.

Model Database Patterns

Models use the Database class to execute queries. Here are common patterns:

Basic Query Pattern

Models obtain a PDO connection in the constructor:

class Reservation
{
    private \PDO $db;

    public function __construct()
    {
        $this->db = Database::getConnection();
    }
}

SELECT Queries

Using prepared statements to fetch data:

public function findById(int $id): ?array
{
    $sql = "
        SELECT 
            r.id,
            r.user_id,
            r.room_id,
            r.event_name,
            r.status,
            r.notes,
            r.created_at,
            u.username,
            rm.name AS room_name
        FROM reservations r
        JOIN users u ON u.id = r.user_id
        JOIN rooms rm ON rm.id = r.room_id
        WHERE r.id = :id
        LIMIT 1
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();

    $reservation = $stmt->fetch(PDO::FETCH_ASSOC);

    return $reservation ?: null;
}

The ?: operator returns null if fetch() returns false (no results found).

INSERT Queries

Inserting data and returning the new ID:

public function create(
    int $userId,
    int $roomId,
    string $eventName,
    ?string $notes = null
): int {
    $sql = "
        INSERT INTO reservations (user_id, room_id, event_name, notes)
        VALUES (:user_id, :room_id, :event_name, :notes)
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
    $stmt->bindParam(':room_id', $roomId, PDO::PARAM_INT);
    $stmt->bindParam(':event_name', $eventName);
    $stmt->bindParam(':notes', $notes);

    $stmt->execute();

    return (int) $this->db->lastInsertId();
}

UPDATE Queries

Updating records with validation:

public function updateStatus(int $reservationId, string $status): bool
{
    $allowedStatuses = ['pendiente', 'aprobado', 'rechazado'];

    if (!in_array($status, $allowedStatuses, true)) {
        throw new InvalidArgumentException('Estado de reservación inválido');
    }

    $sql = "
        UPDATE reservations
        SET status = :status
        WHERE id = :id
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':status', $status);
    $stmt->bindParam(':id', $reservationId, PDO::PARAM_INT);

    return $stmt->execute();
}

DELETE Queries

Deleting with prepared statements:

public function delete(int $reservationId): void
{
    $this->db->prepare(
        "DELETE FROM reservation_materials WHERE reservation_id = :id"
    )->execute([':id' => $reservationId]);
}

Prepared Statements

All queries use prepared statements to prevent SQL injection:

Parameter Binding

bindParam()
execute() Array

Bind variables by reference:

$stmt = $this->db->prepare($sql);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();

Changes to $id before execute() affect the query.

Pass parameters directly to execute:

$stmt = $this->db->prepare($sql);
$stmt->execute([':id' => $reservationId]);

Cleaner for simple queries with few parameters.

Parameter Types

Specify parameter types for better security:

$stmt->bindParam(':id', $id, PDO::PARAM_INT);        // Integer
$stmt->bindParam(':name', $name, PDO::PARAM_STR);    // String
$stmt->bindParam(':active', $active, PDO::PARAM_BOOL); // Boolean

Security: Never concatenate user input into SQL queries. Always use prepared statements with parameter binding.

Query Result Handling

Single Row Results

Use fetch() to retrieve a single row:

$user = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$user) {
    return false; // No user found
}

return $user; // Array with user data

Multiple Row Results

Use fetchAll() to retrieve all rows:

public function getAll(): array
{
    $sql = "
        SELECT 
            r.id,
            r.event_name,
            r.status,
            r.created_at,
            rm.name AS room_name,
            u.username
        FROM reservations r
        JOIN rooms rm ON rm.id = r.room_id
        JOIN users u ON u.id = r.user_id
        ORDER BY r.created_at DESC
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Advanced Patterns

Batch Operations

Inserting multiple related records:

public function attachMaterials(int $reservationId, array $materialIds): void
{
    // 1. Delete existing materials
    $this->db->prepare(
        "DELETE FROM reservation_materials WHERE reservation_id = :id"
    )->execute([':id' => $reservationId]);

    // 2. Normalize IDs
    $materialIds = array_unique(
        array_filter(
            array_map('intval', $materialIds),
            fn($id) => $id > 0
        )
    );

    // 3. Insert new materials
    $stmt = $this->db->prepare(
        "INSERT INTO reservation_materials (reservation_id, material_id)
        VALUES (:reservation_id, :material_id)"
    );

    foreach ($materialIds as $materialId) {
        $stmt->execute([
            ':reservation_id' => $reservationId,
            ':material_id'    => $materialId
        ]);
    }
}

JOIN Queries

Fetching related data with JOINs:

SELECT 
    r.id,
    r.event_name,
    r.status,
    u.username,           -- From users table
    rm.name AS room_name  -- From rooms table
FROM reservations r
JOIN users u ON u.id = r.user_id
JOIN rooms rm ON rm.id = r.room_id
WHERE r.status = :status
ORDER BY r.created_at DESC

Subqueries

Fetching materials for a reservation:

public function getMaterials(int $reservationId): array
{
    $sql = "
        SELECT m.id, m.name
        FROM reservation_materials rm
        JOIN materials m ON m.id = rm.material_id
        WHERE rm.reservation_id = :reservation_id
        ORDER BY m.name
    ";

    $stmt = $this->db->prepare($sql);
    $stmt->bindParam(':reservation_id', $reservationId, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Error Handling

PDO is configured to throw exceptions on errors:

try {
    self::$connection = new PDO(
        $dsn,
        $config['user'],
        $config['password'],
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]
    );
} catch (PDOException $e) {
    die('Error de conexión a la base de datos');
}

Production: In production, log the actual error message and show a generic message to users. Never expose database errors publicly.

Security Best Practices

The database layer implements several security measures:

Prepared Statements - All queries use parameter binding
Input Validation - Models validate data before queries
Type Safety - PDO parameter types prevent type juggling
Password Hashing - Passwords never stored in plain text
Sensitive Data Filtering - Passwords removed from query results

Password Security Example

public function authenticate(string $username, string $password)
{
    $db = Database::getConnection();

    $sql = "
        SELECT id, username, email, password, role
        FROM users
        WHERE username = :username
        LIMIT 1
    ";

    $stmt = $db->prepare($sql);
    $stmt->bindParam(':username', $username, PDO::PARAM_STR);
    $stmt->execute();

    $user = $stmt->fetch();

    if (!$user) {
        return false;
    }

    // Verify password using password_verify()
    if (!password_verify($password, $user['password'])) {
        return false;
    }

    // Never return the password hash
    unset($user['password']);

    return $user;
}

Database Schema

The application uses the following core tables:

-- Users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL
);

-- Rooms
CREATE TABLE rooms (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    capacity INT NOT NULL
);

-- Reservations
CREATE TABLE reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    room_id INT NOT NULL,
    event_name VARCHAR(200) NOT NULL,
    status ENUM('pendiente', 'aprobado', 'rechazado') DEFAULT 'pendiente',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (room_id) REFERENCES rooms(id)
);

-- Materials
CREATE TABLE materials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    room_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (room_id) REFERENCES rooms(id)
);

-- Reservation Materials (many-to-many)
CREATE TABLE reservation_materials (
    reservation_id INT NOT NULL,
    material_id INT NOT NULL,
    PRIMARY KEY (reservation_id, material_id),
    FOREIGN KEY (reservation_id) REFERENCES reservations(id),
    FOREIGN KEY (material_id) REFERENCES materials(id)
);

Performance Considerations

Connection Pooling
Query Optimization
Indexes

The Singleton pattern reuses connections:

// First call - creates connection
$db = Database::getConnection();

// Subsequent calls - reuses connection
$db = Database::getConnection();

Use LIMIT for single row queries:

SELECT * FROM users WHERE id = :id LIMIT 1

Create indexes on frequently queried columns:

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_status ON reservations(status);

Next Steps

Architecture Overview - Return to architecture overview
MVC Pattern - Learn how models fit into MVC

Get Started

Core Features

User Guides

Architecture

Database Architecture

Database Class

Singleton Pattern

PDO Configuration

DSN (Data Source Name)

PDO Attributes

Database Configuration

Model Database Patterns

Basic Query Pattern

SELECT Queries

INSERT Queries

UPDATE Queries

DELETE Queries

Prepared Statements

Parameter Binding

Parameter Types

Query Result Handling

Single Row Results

Multiple Row Results

Advanced Patterns

Batch Operations

JOIN Queries

Subqueries

Error Handling

Security Best Practices

Password Security Example

Database Schema

Performance Considerations

Next Steps

Build docs developers (and LLMs) love

Get Started

Core Features

User Guides

Architecture

​Database Architecture

​Database Class

​Singleton Pattern

​PDO Configuration

​DSN (Data Source Name)

​PDO Attributes

​Database Configuration

​Model Database Patterns

​Basic Query Pattern

​SELECT Queries

​INSERT Queries

​UPDATE Queries

​DELETE Queries

​Prepared Statements

​Parameter Binding

​Parameter Types

​Query Result Handling

​Single Row Results

​Multiple Row Results

​Advanced Patterns

​Batch Operations

​JOIN Queries

​Subqueries

​Error Handling

​Security Best Practices

​Password Security Example

​Database Schema

​Performance Considerations

​Next Steps

Build docs developers (and LLMs) love

Database Architecture

Database Class

Singleton Pattern

PDO Configuration

DSN (Data Source Name)

PDO Attributes

Database Configuration

Model Database Patterns

Basic Query Pattern

SELECT Queries

INSERT Queries

UPDATE Queries

DELETE Queries

Prepared Statements

Parameter Binding

Parameter Types

Query Result Handling

Single Row Results

Multiple Row Results

Advanced Patterns

Batch Operations

JOIN Queries

Subqueries

Error Handling

Security Best Practices

Password Security Example

Database Schema

Performance Considerations

Next Steps