Shiberal ORM

A lightweight PHP ORM with schema-based migrations, model generation, and validation.

Features

• 📝 Schema-based migrations - Define your database schema in a simple text format
• 🔄 Automatic migration generation - Generate SQL migrations from schema changes
• ✅ Schema validation - Validate schema syntax before generating migrations
• 🏗️ Model generation - Auto-generate PHP model classes from schema
• 🔗 Relationship support - Define relationships between models
• 📊 Enum support - Define and use enums in your schema
• 🛡️ Data loss protection - Warns before removing tables/columns

Installation

Via Composer

composer require shiberal/orm

Manual Installation

1. Clone or download this repository
2. Run composer install in the package directory

Requirements

• PHP 8.0 or higher
• PDO extension

Compatibility

• MySQL/MariaDB database

Soon(tm) with:

• [] Postgres
• [] Generate Schema from DB

Quick Start

1. Create a Schema File

Create a schema.o file in your project root:

enum Status { 
    active
    inactive
    pending
}

model User {
    id: int @id @autoincrement
    email: string @unique @not_null
    name: string @not_null
    status: Status
    created_at: datetime @default(now())
    updated_at: datetime @updatedAt
}

model Post {
    id: int @id @autoincrement
    title: string @not_null
    content: text
    user_id: int @not_null
    user: User @relatedTo(User)
    created_at: datetime @default(now())
    @@unique([title, user_id])
}

2. Validate Your Schema

vendor/bin/validate schema.o

3. Generate Migrations

# Generate SQL migration files (dry-run)
vendor/bin/migrate schema.o

# Generate and execute migrations
vendor/bin/migrate schema.o --execute

# Generate migrations with data loss protection disabled
vendor/bin/migrate schema.o --execute --accept-data-loss

4. Generate PHP Models

vendor/bin/generate schema.o

This will generate PHP model classes in the db/ directory.

Schema Syntax

Models

model ModelName {
    field_name: type @annotations
    // ...
}

Field Types

• int - Integer
• string - VARCHAR(255)
• text - TEXT
• datetime - DATETIME
• date - DATE
• bool / boolean - BOOLEAN
• ModelName - Relationship (requires @relatedTo)
• EnumName - Enum reference

Annotations

• @id - Primary key (must be int)
• @autoincrement - Auto-incrementing (must be int)
• @unique - Unique constraint
• @not_null - NOT NULL constraint
• @nullable - Explicitly nullable
• @default("value") - Default value
• @default(now()) - Current timestamp default
• @updatedAt - Auto-update timestamp (must be datetime)
• @relatedTo(ModelName) - Foreign key relationship

Composite Unique Constraints

model ModelName {
    field1: string
    field2: string
    @@unique([field1, field2])
}

Enums

enum Status {
    active
    inactive
    pending
}

Enums are stored as separate tables with foreign key relationships.

Command Line Tools

migrate

Generate and execute database migrations.

# Generate migration files
vendor/bin/migrate schema.o

# Generate and execute
vendor/bin/migrate schema.o --execute

# Specify output directory
vendor/bin/migrate schema.o --output=./migrations

# Accept data loss (removes tables/columns not in schema)
vendor/bin/migrate schema.o --execute --accept-data-loss

# View migration history
vendor/bin/migrate --history
vendor/bin/migrate --history=10

# Reset (remove all migration files)
vendor/bin/migrate --reset schema.o

generate

Generate PHP model classes from schema.

vendor/bin/generate schema.o

validate

Validate schema syntax.

vendor/bin/validate schema.o

Programmatic Usage

Migration Class

use shiberal\orm\Migration;

// Create migration instance
$migration = new Migration(
    dbHost: 'localhost',
    dbPort: '3306',
    dbName: 'mydb',
    dbUser: 'user',
    dbPass: 'password'
);

// Or use environment variables
// MYSQL_HOST, MYSQL_PORT, MYSQL_DATABASE, MYSQL_USER, MYSQL_PASSWORD

// Generate migrations
$migration->generate('schema.o', './migrations');

// Generate and execute
$migration->migrate('schema.o', execute: true);

// Get migration history
$history = $migration->getMigrationHistory(limit: 50);

Database Configuration

The Migration class uses environment variables by default:

• MYSQL_HOST (default: 192.168.10.139)
• MYSQL_PORT (default: 3307)
• MYSQL_DATABASE (default: banana)
• MYSQL_USER (default: root)
• MYSQL_PASSWORD (default: username)

Or pass them directly to the constructor:

$migration = new Migration(
    dbHost: 'localhost',
    dbPort: '3306',
    dbName: 'mydb',
    dbUser: 'user',
    dbPass: 'password'
);

Migration Tracking

Migrations are tracked in a _migrations table that is automatically created. Each migration is recorded with:

• Migration file name
• SHA-256 hash of the file content
• Execution timestamp
• Execution time in milliseconds

This prevents duplicate executions and allows you to track migration history.

DataTables Integration with CSRF Protection

The ORM includes built-in support for generating DataTables endpoints with CSRF protection and secure session management.

Generate DataTables Files

Generate DataTables server-side endpoints for your models:

# Generate DataTables files for all models in schema
vendor/bin/dtgen schema.o

# Specify custom output directory
vendor/bin/dtgen schema.o api/dt/

This generates:

• DataTable endpoints (api/dt/{model}DataTable.php) - Server-side data endpoints
• Editor endpoints (DTables/{model}.php) - CRUD operations endpoints

Backend Setup (Server-Side)

The generated DataTables files automatically include CSRF protection. They validate tokens from:

• POST data: csrf_token
• HTTP header: X-CSRF-Token

Example generated endpoint structure:

<?php
use shiberal\orm\Security;

// Configure secure session and validate CSRF token
Security::configureSecureSession();
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Validate CSRF token for all requests
if (!Security::validateCsrfToken()) {
    http_response_code(403);
    header('Content-Type: application/json');
    echo json_encode(['error' => 'Invalid or missing CSRF token']);
    exit;
}

// ... DataTables Editor setup ...

Frontend Setup (Client-Side)

1. Initialize Session and Get CSRF Token

In your HTML page, use the genTokenSession() method to configure the session and get the CSRF token:

<?php
require __DIR__ . '/vendor/autoload.php';

use shiberal\orm\Security;

// Configure secure session and generate CSRF token
[$csrfToken, $session] = Security::genTokenSession();
?>

<html>
<head>
    <title>DataTables Example</title>
    <!-- Store CSRF token in meta tag for JavaScript access -->
    <meta name="csrf-token" content="<?php echo htmlspecialchars($csrfToken); ?>">
    
    <!-- DataTables CSS and JS -->
    <link rel="stylesheet" href="/path/to/dataTables.bootstrap5.min.css">
    <script src="/path/to/jquery.min.js"></script>
    <script src="/path/to/dataTables.min.js"></script>
    <script src="/path/to/dataTables.bootstrap5.min.js"></script>
    <script src="/path/to/dataTables.editor.min.js"></script>
    <script src="/path/to/editor.bootstrap5.min.js"></script>
</head>
<body>
    <!-- Your table -->
    <table id="myTable" class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
            </tr>
        </thead>
    </table>

    <script>
        // Get CSRF token from meta tag
        var csrfToken = document.querySelector('meta[name="csrf-token"]').content;

        // Initialize DataTables Editor
        var editor = new $.fn.dataTable.Editor({
            ajax: {
                url: 'DTables/users.php',
                data: function(d) {
                    d.csrf_token = csrfToken;
                    return d;
                }
            },
            table: '#myTable',
            fields: [
                { label: 'ID:', name: 'id' },
                { label: 'Name:', name: 'name' },
                { label: 'Email:', name: 'email' }
            ]
        });

        // Initialize DataTable
        $('#myTable').DataTable({
            dom: 'Bfrtip',
            ajax: {
                url: 'api/dt/usersDataTable.php',
                type: 'POST',
                data: function(d) {
                    d.csrf_token = csrfToken;
                    return d;
                }
            },
            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'email' }
            ],
            select: true,
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit', editor: editor },
                { extend: 'remove', editor: editor }
            ]
        });
    </script>
</body>
</html>

2. Alternative: Using HTTP Headers

You can also send the CSRF token via HTTP header:

$('#myTable').DataTable({
    ajax: {
        url: 'api/dt/usersDataTable.php',
        type: 'POST',
        headers: {
            'X-CSRF-Token': csrfToken
        },
        data: function(d) {
            d.csrf_token = csrfToken;
            return d;
        }
    },
    // ... rest of configuration
});

Security Features

The Security class provides:

• Secure session configuration - HttpOnly, Secure (HTTPS), SameSite cookies
• CSRF token generation - Cryptographically secure random tokens
• Token validation - Timing-safe comparison using hash_equals()
• Automatic session management - Handles session start/configuration

Manual Security Setup

If you need to set up security manually (without using genTokenSession()):

<?php
use shiberal\orm\Security;

// Method 1: Configure and generate separately
Security::configureSecureSession();
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}
$csrfToken = Security::generateCsrfToken();

// Method 2: Use the combined method (recommended)
[$csrfToken, $sessionActive] = Security::genTokenSession();

Advanced: Combined Tables with Relationships

For tables with relationships, the generated endpoints automatically handle JOINs:

// Example: AuthTokens table with User relationship
$('#myTable').DataTable({
    ajax: {
        url: 'api/dt/authtokensCombinedDataTable.php',
        type: 'POST',
        data: function(d) {
            d.csrf_token = csrfToken;
            return d;
        }
    },
    columns: [
        { data: 'id' },
        { data: 'token' },
        { data: 'user_name' },    // From joined User table
        { data: 'user_email' }    // From joined User table
    ]
});

Troubleshooting

403 Forbidden Error:

• Ensure CSRF token is included in POST data or X-CSRF-Token header
• Verify session is started before generating token
• Check that token is not expired (tokens persist for session lifetime)

Session Issues:

• Ensure session_start() is called before using genTokenSession()
• Check PHP session configuration in php.ini
• Verify cookie settings if using HTTPS

Token Mismatch:

• Ensure the same session is used for token generation and validation
• Check that cookies are being sent/received correctly
• Verify no session regeneration between token creation and use

License

MIT License - see LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Author

Francesco Grelli - francesco.grelli98@gmail.com