Database

ZephyrPHP uses Doctrine ORM for database operations, providing a powerful and flexible way to work with your data.

Configuration

Configure your database connection in .env:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Supported Databases

Database	Connection	Default Port
MySQL	mysql	3306
PostgreSQL	pgsql	5432
SQLite	sqlite	N/A

SQLite Configuration

For SQLite, just set the connection type:

DB_CONNECTION=sqlite

The database file will be created at storage/database.sqlite.

Database Connection

Access the database connection:

<?php

use ZephyrPHP\Database\Database;

// Get the database instance
$db = Database::getInstance();

// Get the Doctrine EntityManager
$em = $db->getEntityManager();

// Get the DBAL Connection
$connection = $db->getConnection();

Raw Queries

Execute raw SQL queries when needed:

<?php

$db = Database::getInstance();
$connection = $db->getConnection();

// Select query
$users = $connection->fetchAllAssociative(
    'SELECT * FROM users WHERE active = ?',
    [true]
);

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

// Update query
$connection->update('users',
    ['name' => 'Jane Doe'],  // Data
    ['id' => 1]              // Where
);

// Delete query
$connection->delete('users', ['id' => 1]);

Query Builder

Use Doctrine's query builder for more complex queries:

<?php

$db = Database::getInstance();
$qb = $db->getConnection()->createQueryBuilder();

$users = $qb
    ->select('u.*')
    ->from('users', 'u')
    ->where('u.active = :active')
    ->andWhere('u.created_at > :date')
    ->setParameter('active', true)
    ->setParameter('date', '2024-01-01')
    ->orderBy('u.name', 'ASC')
    ->setMaxResults(10)
    ->fetchAllAssociative();

Transactions

Wrap database operations in transactions:

<?php

$db = Database::getInstance();
$connection = $db->getConnection();

try {
    $connection->beginTransaction();

    $connection->insert('orders', ['user_id' => 1, 'total' => 100]);
    $orderId = $connection->lastInsertId();

    $connection->insert('order_items', [
        'order_id' => $orderId,
        'product_id' => 5,
        'quantity' => 2
    ]);

    $connection->commit();
} catch (\Exception $e) {
    $connection->rollBack();
    throw $e;
}

Or use the transactional helper:

$db->getConnection()->transactional(function($connection) {
    $connection->insert('orders', ['user_id' => 1, 'total' => 100]);
    // More operations...
});

Using Models

While you can use raw queries, ZephyrPHP encourages using Models with Doctrine ORM for type-safety and cleaner code.

<?php

use App\Models\User;

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

// Find with conditions
$users = User::query()
    ->where('active', '=', true)
    ->orderBy('created_at', 'DESC')
    ->all();

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

// Update
$user = User::find(1);
$user->name = 'Jane Doe';
$user->save();

// Delete
$user = User::find(1);
$user->delete();

Schema Management

Use Doctrine's schema tool or migrations to manage your database schema:

# Create schema from entities
php craftsman doctrine:schema:create

# Update schema to match entities
php craftsman doctrine:schema:update

# Validate schema
php craftsman doctrine:schema:validate

Multiple Connections

Configure multiple database connections in config/database.php:

<?php

return [
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        'mysql' => [
            'driver' => 'pdo_mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            // ...
        ],

        'analytics' => [
            'driver' => 'pdo_pgsql',
            'host' => env('ANALYTICS_DB_HOST'),
            'database' => env('ANALYTICS_DB_DATABASE'),
            // ...
        ],
    ],
];