Database

ApnaPHP provides a powerful database system with support for multiple database drivers and an elegant ORM inspired by Laravel Eloquent and Mongoose.

Supported Databases

ApnaPHP supports multiple database drivers:

• MySQL/MariaDB - Most popular choice for web applications
• PostgreSQL - Advanced open-source database
• SQLite - Lightweight file-based database
• MongoDB - NoSQL document database

Configuration

The boilerplate comes with pre-configured database settings in config/database.php:

<?php

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

    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', 3306),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
        ],

        'mariadb' => [
            'driver' => 'mariadb', // Uses MySQL driver (fully compatible)
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', 3306),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
        ],

        'postgresql' => [
            'driver' => 'postgresql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', 5432),
            'database' => env('DB_DATABASE', ''),
            'username' => env('DB_USERNAME', ''),
            'password' => env('DB_PASSWORD', ''),
        ],

        'sqlite' => [
            'driver' => 'sqlite',
            'database' => env('DB_DATABASE', 'storage/database/database.sqlite'),
        ],

        'mongodb' => [
            'driver' => 'mongodb',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '27017'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
        ],
    ],
];

Models

Models represent database tables and provide an elegant way to interact with your data.

Creating Models

# Create a new model
php apna make:model User

<?php
// app/Models/User.php
namespace App\Models;

use ApnaPHP\Database\Model;

class User extends Model
{
    protected string $table = 'users';
    protected string $primaryKey = 'id';
    
    // Enable auto-migration (Mongoose-style)
    protected bool $autoMigrate = true; // ✅ Auto-creates table on first use

    // Define the schema for this model (Pipe-separated format)
    protected array $schema = [
        'name' => 'required|type:string|length:255',
        'email' => 'required|unique|type:string|length:255',
        'phone' => 'required|unique|type:string|length:20',
        'password' => 'required|type:string',
        'role' => 'type:string|default:user|length:50',
        'status' => 'type:string|default:active|length:50',
        'bio' => 'type:text|nullable',
    ];

    // Define fillable attributes
    protected array $fillable = [
        'name', 'email', 'password', 'phone', 'role', 'status', 'bio'
    ];

    // Define hidden attributes (won't show in JSON)
    protected array $hidden = ['password'];

    // Define attribute casts
    protected array $casts = [
        // Don't cast timestamps - keep as strings
    ];

    /**
     * Hash password before saving
     */
    public function setPasswordAttribute(string $value): void
    {
        $this->attributes['password'] = password_hash($value, PASSWORD_DEFAULT);
    }

    /**
     * Check if user is admin
     */
    public function isAdmin(): bool
    {
        return $this->role === 'admin';
    }

    /**
     * Check if user is active
     */
    public function isActive(): bool
    {
        return $this->status === 'active';
    }

    /**
     * Verify password
     */
    public function verifyPassword(string $password): bool
    {
        return password_verify($password, $this->password);
    }
    
    // Mutators
    public function setPasswordAttribute($value)
    {
        $this->attributes['password'] = bcrypt($value);
    }
    
    // Accessors
    public function getNameAttribute($value)
    {
        return ucwords($value);
    }
}

Schema Definition

ApnaPHP uses a pipe-separated schema format inspired by Mongoose:

protected $schema = [
    // Basic types
    'name' => 'required|type:string|length:255',
    'email' => 'required|type:string|length:255|unique',
    'age' => 'nullable|type:integer|min:18|max:100',
    'price' => 'nullable|type:decimal|precision:8|scale:2',
    'description' => 'nullable|type:text',
    'content' => 'nullable|type:longtext',
    'is_active' => 'nullable|type:boolean|default:true',
    'birth_date' => 'nullable|type:date',
    'created_at' => 'nullable|type:datetime',
    'settings' => 'nullable|type:json',
    'uuid' => 'nullable|type:uuid',
    
    // With modifiers
    'slug' => 'required|type:string|length:255|unique|index',
    'score' => 'nullable|type:float|precision:5|scale:2|unsigned',
    'priority' => 'nullable|type:integer|auto_increment',
    'status' => 'nullable|type:string|length:50|default:active|comment:User status',
];

Supported Schema Rules

Rule	Description	Example
required	Field is required	required
nullable	Field can be null	nullable
type:string	String type	type:string
type:text	Text type	type:text
type:longtext	Long text type	type:longtext
type:integer	Integer type	type:integer
type:boolean	Boolean type	type:boolean
type:decimal	Decimal type	type:decimal
type:float	Float type	type:float
type:date	Date type	type:date
type:datetime	DateTime type	type:datetime
type:timestamp	Timestamp type	type:timestamp
type:json	JSON type	type:json
type:uuid	UUID type	type:uuid
length:255	String length	length:255
min:0	Minimum value	min:0
max:100	Maximum value	max:100
unique	Unique constraint	unique
default:value	Default value	default:active
precision:8	Decimal precision	precision:8
scale:2	Decimal scale	scale:2
unsigned	Unsigned integer	unsigned
auto_increment	Auto increment	auto_increment
index	Create index	index
comment:Text	Column comment	comment:User status

Query Builder

ApnaPHP provides a fluent query builder that works with both SQL and MongoDB:

Basic Queries

<?php
use App\Models\User;

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

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

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

// Find or fail
$user = User::findOrFail(1);

// Find by column
$user = User::where('email', 'john@example.com')->first();

// Get multiple records
$activeUsers = User::where('is_active', true)->get();

// Count records
$count = User::where('is_active', true)->count();

// Check if exists
$exists = User::where('email', 'john@example.com')->exists();

Advanced Queries

<?php
// Where conditions
$users = User::where('age', '>', 18)
    ->where('is_active', true)
    ->orWhere('role', 'admin')
    ->get();

// Where in
$users = User::whereIn('id', [1, 2, 3])->get();

// Where not in
$users = User::whereNotIn('role', ['banned', 'suspended'])->get();

// Where null/not null
$users = User::whereNull('deleted_at')->get();
$users = User::whereNotNull('email_verified_at')->get();

// Where between
$users = User::whereBetween('age', [18, 65])->get();

// Where like
$users = User::whereLike('name', '%john%')->get();

// Order by
$users = User::orderBy('created_at', 'desc')->get();
$users = User::orderBy('name')->orderBy('created_at', 'desc')->get();

// Limit and offset
$users = User::limit(10)->offset(20)->get();

// Select specific columns
$users = User::select(['name', 'email'])->get();

// Group by
$stats = User::groupBy('role')
    ->selectRaw('role, COUNT(*) as count')
    ->get();

// Having
$stats = User::groupBy('role')
    ->having('count', '>', 5)
    ->selectRaw('role, COUNT(*) as count')
    ->get();

Joins

<?php
// Inner join
$users = User::join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select('users.*', 'profiles.bio')
    ->get();

// Left join
$users = User::leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

// Multiple joins
$posts = Post::join('users', 'posts.user_id', '=', 'users.id')
    ->join('categories', 'posts.category_id', '=', 'categories.id')
    ->select('posts.*', 'users.name as author', 'categories.name as category')
    ->get();

Aggregates

<?php
// Count
$count = User::count();
$activeCount = User::where('is_active', true)->count();

// Sum
$totalSales = Order::sum('amount');

// Average
$avgAge = User::avg('age');

// Min/Max
$oldestUser = User::max('age');
$youngestUser = User::min('age');

// Pluck specific column
$names = User::pluck('name');
$nameEmailMap = User::pluck('email', 'name');

Creating and Updating

Creating Records

<?php
// Create single record
$user = User::create([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'password' => 'secret123',
    'age' => 30,
    'is_active' => true
]);

// Create multiple records
$users = User::create([
    [
        'name' => 'Jane Doe',
        'email' => 'jane@example.com',
        'password' => 'secret123'
    ],
    [
        'name' => 'Bob Smith',
        'email' => 'bob@example.com',
        'password' => 'secret123'
    ]
]);

// Using fill and save
$user = new User();
$user->fill([
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);
$user->save();

Updating Records

<?php
// Update single record
$user = User::find(1);
$user->update([
    'name' => 'John Updated',
    'age' => 31
]);

// Update multiple records
User::where('is_active', false)->update(['status' => 'inactive']);

// Update or create
$user = User::updateOrCreate(
    ['email' => 'john@example.com'],
    ['name' => 'John Doe', 'age' => 30]
);

// First or create
$user = User::firstOrCreate(
    ['email' => 'john@example.com'],
    ['name' => 'John Doe', 'age' => 30]
);

Deleting Records

<?php
// Delete single record
$user = User::find(1);
$user->delete();

// Delete multiple records
User::where('is_active', false)->delete();

// Soft delete (if enabled)
$user = User::find(1);
$user->delete(); // Sets deleted_at timestamp

// Force delete (bypass soft delete)
$user->forceDelete();

// Restore soft deleted record
$user->restore();

Relationships

One-to-One

<?php
// User.php
class User extends Model
{
    public function profile()
    {
        return $this->hasOne(Profile::class, 'user_id');
    }
}

// Profile.php
class Profile extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}

// Usage
$user = User::with('profile')->find(1);
$profile = $user->profile;
$user = $profile->user;

One-to-Many

<?php
// User.php
class User extends Model
{
    public function posts()
    {
        return $this->hasMany(Post::class, 'user_id');
    }
}

// Post.php
class Post extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class, 'user_id');
    }
}

// Usage
$user = User::with('posts')->find(1);
$posts = $user->posts;

$post = Post::with('user')->find(1);
$author = $post->user;

Many-to-Many

<?php
// User.php
class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany(Role::class, 'user_roles', 'user_id', 'role_id');
    }
}

// Role.php
class Role extends Model
{
    public function users()
    {
        return $this->belongsToMany(User::class, 'user_roles', 'role_id', 'user_id');
    }
}

// Usage
$user = User::with('roles')->find(1);
$roles = $user->roles;

// Attach/detach roles
$user->roles()->attach(1);
$user->roles()->detach(1);
$user->roles()->sync([1, 2, 3]);

MongoDB Support

ApnaPHP provides full MongoDB support with the same API:

<?php
// MongoDB Model
class Product extends Model
{
    protected $connection = 'mongodb';
    protected $collection = 'products';
    
    protected $schema = [
        'name' => 'required|type:string',
        'price' => 'required|type:float',
        'tags' => 'nullable|type:array',
        'metadata' => 'nullable|type:json',
        'created_at' => 'nullable|type:datetime',
        'updated_at' => 'nullable|type:datetime',
    ];
}

// Same API works for MongoDB
$products = Product::where('price', '>', 100)->get();
$product = Product::create([
    'name' => 'Laptop',
    'price' => 999.99,
    'tags' => ['electronics', 'computers'],
    'metadata' => ['brand' => 'Apple', 'model' => 'MacBook Pro']
]);

Auto-Migration

ApnaPHP automatically creates and updates database tables based on your model schema:

<?php
// Enable auto-migration in your model
class User extends Model
{
    protected $autoMigrate = true; // Enable auto-migration
    
    protected $schema = [
        'name' => 'required|type:string|length:255',
        'email' => 'required|type:string|length:255|unique',
        // ... other fields
    ];
}

// Tables are automatically created/updated when model is first used
$user = User::create(['name' => 'John', 'email' => 'john@example.com']);

Raw Queries

For complex queries, you can use raw SQL:

<?php
use ApnaPHP\Database\DB;

// Raw select
$users = DB::select('SELECT * FROM users WHERE age > ?', [18]);

// Raw insert
DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['John', 'john@example.com']);

// Raw update
DB::update('UPDATE users SET name = ? WHERE id = ?', ['John Updated', 1]);

// Raw delete
DB::delete('DELETE FROM users WHERE id = ?', [1]);

// Raw statement
DB::statement('CREATE TABLE example (id INT PRIMARY KEY)');

Best Practices

1. Use models for business logic - Keep database operations in models
2. Validate data - Use the built-in validation system
3. Use relationships - Leverage Eloquent relationships for clean code
4. Optimize queries - Use select() to limit columns and with() for eager loading
5. Handle errors - Always check for null values and handle exceptions
6. Use transactions - Wrap related operations in database transactions
7. Index your database - Add indexes for frequently queried columns
8. Use prepared statements - Always use parameter binding to prevent SQL injection