Zero-Copy Database

The Database Performance Problem

Traditional database operations in PHP involve multiple data copies:

┌─────────────────────────────────────────────────────┐
│  Database → Driver → PDO → PHP Array → JSON         │
│     1          2       3        4         5          │
└─────────────────────────────────────────────────────┘

Each arrow represents a memory copy:

Database sends result over network
Native driver allocates memory for result
PDO copies data into PHP-compatible structures
Your code converts to PHP arrays
json_encode() serializes for API response

For a 10MB result set, traditional PHP may allocate 50MB+ of memory across all these copies.

Zero-Copy Architecture

Lyger’s Zero-Copy Database keeps data in Rust memory and only copies once:

┌─────────────────────────────────────────────────────┐
│  Database → Rust → [Memory Pointer] → JSON → PHP    │
│     1        2            3             4      5     │
└─────────────────────────────────────────────────────┘

Database sends result to Rust
Rust stores in native memory structures
Rust returns pointer (memory address) to PHP
When needed, Rust serializes directly to JSON
PHP receives final JSON (one copy)

The result set stays in Rust’s memory until you’re ready to use it. No intermediate copies.

How It Works

Query Execution

// From Lyger/Core/Engine.php
public function dbQuery(string $dsn, string $query): int
{
    if ($this->ffi === null) {
        return 0;
    }

    try {
        // Execute query in Rust, return pointer to results
        return (int) $this->ffi->lyger_db_query($dsn, $query);
    } catch (\Throwable $e) {
        return 0;
    }
}

The lyger_db_query FFI function:

Connects to the database (or reuses connection)
Executes the SQL query
Stores results in Rust’s heap memory
Returns the memory address as an integer

The return value is a memory pointer disguised as an integer. PHP doesn’t touch the actual data.

Result Serialization

// From Lyger/Core/Engine.php
public function jsonifyResult(int $ptr): string
{
    if ($this->ffi === null || $ptr === 0) {
        return '[]';
    }

    try {
        // Pass pointer back to Rust for serialization
        $result = $this->ffi->lyger_jsonify_result($ptr);
        $string = FFI::string($result);
        $this->ffi->lyger_free_string($result);
        return $string;
    } catch (\Throwable $e) {
        return '[]';
    }
}

The lyger_jsonify_result FFI function:

Receives the pointer to the result set
Iterates through rows in Rust (fast!)
Serializes directly to JSON string
Returns JSON to PHP

Memory Cleanup

// From Lyger/Core/Engine.php
public function freeResult(int $ptr): void
{
    if ($this->ffi === null || $ptr === 0) {
        return;
    }

    try {
        // Tell Rust to free the result set memory
        $this->ffi->lyger_free_result($ptr);
    } catch (\Throwable $e) {
        // Ignore
    }
}

Always free result sets when done! Failing to call freeResult() causes memory leaks in the Rust process.

High-Level API

The dbQueryJson method combines all three operations:

// From Lyger/Core/Engine.php
public function dbQueryJson(string $dsn, string $query): string
{
    $ptr = $this->dbQuery($dsn, $query);
    if ($ptr === 0) {
        return '[]';
    }

    $json = $this->jsonifyResult($ptr);
    $this->freeResult($ptr);
    return $json;
}

Usage Examples

Basic Query

use Lyger\Core\Engine;

$engine = Engine::getInstance();

$dsn = 'sqlite:database/app.db';
$query = 'SELECT * FROM users WHERE active = 1';

$json = $engine->dbQueryJson($dsn, $query);
$users = json_decode($json, true);

foreach ($users as $user) {
    echo "User: {$user['name']}\n";
}

Manual Memory Management

For advanced use cases, manage the pointer lifecycle manually:

use Lyger\Core\Engine;

$engine = Engine::getInstance();

// Execute query, get pointer
$ptr = $engine->dbQuery('sqlite:database/app.db', 'SELECT * FROM products');

if ($ptr === 0) {
    die('Query failed');
}

// Hold pointer while doing other work
processOrders();
updateInventory();

// Now serialize when needed
$json = $engine->jsonifyResult($ptr);

// Process results
$products = json_decode($json, true);
foreach ($products as $product) {
    echo "{$product['name']}: \${$product['price']}\n";
}

// Clean up
$engine->freeResult($ptr);

Why hold the pointer?

In some scenarios, you may want to defer serialization:

Execute multiple queries concurrently
Perform calculations before fetching results
Reduce memory pressure by serializing only when needed

The result set stays in Rust’s memory (which is more efficient) until you explicitly serialize it.

Database Support

Lyger’s Zero-Copy Database supports multiple database engines through their Rust drivers:

Database	DSN Example	Driver
SQLite	`sqlite:database/app.db`	rusqlite
PostgreSQL	`postgres://user:pass@host/db`	tokio-postgres
MySQL	`mysql://user:pass@host/db`	mysql_async
MariaDB	`mysql://user:pass@host/db`	mysql_async

The Rust driver is determined by the DSN prefix. All drivers support the same zero-copy architecture.

Connection Examples

// SQLite (file-based)
$dsn = 'sqlite:database/app.db';

// PostgreSQL
$dsn = 'postgres://admin:secret@localhost:5432/myapp';

// MySQL/MariaDB
$dsn = 'mysql://root:password@localhost:3306/myapp';

Performance Comparison

Memory Usage

Let’s query 10,000 user records (~1MB result set):

Approach	Memory Allocated	Description
Traditional PDO	~5MB	Multiple copies through PDO layers
`json_encode(PDO)`	~7MB	PDO + JSON serialization
Zero-Copy	~1MB	Single copy of final JSON

Speed Benchmarks

Query: SELECT * FROM users LIMIT 10000

Approach	Time	Notes
PDO + fetch	~45ms	Fetch each row, build array
PDO + fetchAll	~35ms	Fetch all at once
Zero-Copy	~8ms	Query + serialize in Rust

Actual performance depends on database, network latency, and hardware. Zero-Copy shines with large result sets.

Integration with QueryBuilder

While the Engine class provides low-level access, you’ll typically use the QueryBuilder:

use Lyger\Database\QueryBuilder;

// Traditional PDO approach (used internally)
$users = QueryBuilder::table('users')
    ->where('active', 1)
    ->orderBy('created_at', 'DESC')
    ->limit(100)
    ->get();

Currently, QueryBuilder uses PDO internally. Zero-Copy database is used by Cache and internal systems. Full QueryBuilder integration is planned for future releases.

Advanced: Streaming Results

For extremely large result sets, you can stream data:

use Lyger\Core\Engine;

$engine = Engine::getInstance();
$ptr = $engine->dbQuery('sqlite:data.db', 'SELECT * FROM big_table');

// Process in chunks (pseudo-code - requires Rust support)
while ($chunk = $engine->fetchChunk($ptr, 1000)) {
    $rows = json_decode($chunk, true);
    
    foreach ($rows as $row) {
        processRow($row);
    }
    
    // Each chunk is GC'd after processing
    unset($rows, $chunk);
}

$engine->freeResult($ptr);

Streaming API is not yet implemented in v0.1. Currently, you must fetch the entire result set at once.

Connection Pooling

The Rust layer maintains connection pools for better performance:

// First query: establishes connection
$json1 = $engine->dbQueryJson($dsn, 'SELECT COUNT(*) FROM users');

// Second query: reuses connection (fast!)
$json2 = $engine->dbQueryJson($dsn, 'SELECT * FROM users LIMIT 10');

Connection pooling benefits:

No connection overhead for subsequent queries
Connections kept alive across requests (Always-Alive server)
Automatic reconnection on failure
Configurable pool size and timeouts

In Always-Alive mode, database connections persist across HTTP requests, making subsequent queries even faster.

Error Handling

use Lyger\Core\Engine;

$engine = Engine::getInstance();

$ptr = $engine->dbQuery('sqlite:database/app.db', 'SELECT * FROM users');

if ($ptr === 0) {
    // Query failed - handle error
    error_log('Database query failed');
    return [];
}

try {
    $json = $engine->jsonifyResult($ptr);
    $results = json_decode($json, true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        throw new \Exception('Invalid JSON from database');
    }
    
    return $results;
} finally {
    // Always clean up, even on error
    $engine->freeResult($ptr);
}

Common error scenarios

Pointer is 0: Query execution failed (syntax error, connection issue, etc.)
Invalid JSON: Result serialization failed (encoding issue, memory corruption)
Exception during processing: Network timeout, connection lost, out of memory

Always use try-finally to ensure cleanup!

Best Practices

1. Always Free Results

// ✅ Good
$ptr = $engine->dbQuery($dsn, $query);
$json = $engine->jsonifyResult($ptr);
$engine->freeResult($ptr);

// ✅ Better (automatic cleanup)
$json = $engine->dbQueryJson($dsn, $query);

2. Check for Errors

// ✅ Good
$ptr = $engine->dbQuery($dsn, $query);
if ($ptr === 0) {
    // Handle error
    return [];
}

3. Use Prepared Statements

// ❌ Bad: SQL injection risk
$query = "SELECT * FROM users WHERE id = {$userId}";

// ✅ Good: Use parameterized queries
// (Note: Current API doesn't support params - use with caution)
$query = "SELECT * FROM users WHERE id = 123";

The current Zero-Copy API doesn’t support parameter binding. Always sanitize user input before building queries, or use the QueryBuilder for safe queries.

4. Limit Result Size

// ❌ Bad: Could return millions of rows
$json = $engine->dbQueryJson($dsn, 'SELECT * FROM logs');

// ✅ Good: Limit results
$json = $engine->dbQueryJson($dsn, 'SELECT * FROM logs LIMIT 1000');

Troubleshooting

Query Returns Empty Array

$json = $engine->dbQueryJson($dsn, 'SELECT * FROM users');
// $json = '[]'

Possible causes:

Table is empty
Query syntax error
Connection failed
FFI not available

Check the pointer:

$ptr = $engine->dbQuery($dsn, 'SELECT * FROM users');
if ($ptr === 0) {
    error_log('Query failed - check database connection and SQL syntax');
}

Memory Leaks

If memory grows over time:

Ensure you call freeResult() for every query
Use dbQueryJson() which handles cleanup automatically
Check for stored pointers in class properties

// ❌ Bad: Pointer stored, never freed
class Repository {
    private int $lastQuery;
    
    public function query($sql) {
        $this->lastQuery = $engine->dbQuery($dsn, $sql);
        // ← Leaked! Never freed
    }
}

Future Improvements

Planned features for future releases:

Parameter binding - Safe query parameters
Streaming results - Process huge datasets chunk-by-chunk
Query result objects - Type-safe result handling
Async queries - Non-blocking database operations
QueryBuilder integration - Full zero-copy support in QueryBuilder

Next Steps

Architecture Overview

Understand the complete architecture

Rust FFI Integration

Learn about FFI and memory management

Query Builder

Use the fluent query builder

Models

Work with Eloquent-style models

Get Started

Core Concepts

Routing

Database

Frontend

Advanced

The Database Performance Problem

Zero-Copy Architecture

How It Works

Query Execution

Result Serialization

Memory Cleanup

High-Level API

Usage Examples

Basic Query

Manual Memory Management

Database Support

Connection Examples

Performance Comparison

Memory Usage

Speed Benchmarks

Integration with QueryBuilder

Advanced: Streaming Results

Connection Pooling

Error Handling

Best Practices

1. Always Free Results

2. Check for Errors

3. Use Prepared Statements

4. Limit Result Size

Troubleshooting

Query Returns Empty Array

Memory Leaks

Future Improvements

Next Steps

Architecture Overview

Rust FFI Integration

Query Builder

Models

Get Started

Core Concepts

Routing

Database

Frontend

Advanced

​The Database Performance Problem

​Zero-Copy Architecture

​How It Works

​Query Execution

​Result Serialization

​Memory Cleanup

​High-Level API

​Usage Examples

​Basic Query

​Manual Memory Management

​Database Support

​Connection Examples

​Performance Comparison

​Memory Usage

​Speed Benchmarks

​Integration with QueryBuilder

​Advanced: Streaming Results

​Connection Pooling

​Error Handling

​Best Practices

​1. Always Free Results

​2. Check for Errors

​3. Use Prepared Statements

​4. Limit Result Size

​Troubleshooting

​Query Returns Empty Array

​Memory Leaks

​Future Improvements

​Next Steps

Architecture Overview

Rust FFI Integration

Query Builder

Models

The Database Performance Problem

Zero-Copy Architecture

How It Works

Query Execution

Result Serialization

Memory Cleanup

High-Level API

Usage Examples

Basic Query

Manual Memory Management

Database Support

Connection Examples

Performance Comparison

Memory Usage

Speed Benchmarks

Integration with QueryBuilder

Advanced: Streaming Results

Connection Pooling

Error Handling

Best Practices

1. Always Free Results

2. Check for Errors

3. Use Prepared Statements

4. Limit Result Size

Troubleshooting

Query Returns Empty Array

Memory Leaks

Future Improvements

Next Steps