README

A modern, fluent PHP database wrapper built on top of PDO, providing an elegant and secure way to interact with databases.

Features

Fluent Interface: Chain methods for readable and intuitive database operations
Multi-Driver Support: MySQL, PostgreSQL, SQLite, SQL Server, and Oracle
PSR-12 Compliant: Follows PHP coding standards with camelCase method names
Prepared Statements: Built-in protection against SQL injection
Flexible Fetching: Return results as objects or arrays, single records or collections
Transaction Support: Full transaction management with commit/rollback
Type-Safe Parameter Binding: Automatic parameter type detection and binding (positional and named)
Raw Query Support: Execute custom SQL when needed
Comprehensive Logging: Debug and error logging throughout
Query Profiling: Built-in query logging for performance debugging
Connection Pooling: Singleton pattern support for managing multiple connections
Batch Operations: Efficient batch inserts and upsert support
Method Chaining: Build complex queries with readable, chainable methods

Requirements

PHP 8.2 or higher
PDO extension
Supported databases: MySQL 5.7+, MariaDB 10.2+, PostgreSQL, SQLite, SQL Server, Oracle

Installation

Install via Composer:

composer require kevinpirnie/kpt-database

Configuration

The database class requires a settings object to be passed to the constructor and expects a Logger class to be available in the KPT namespace:

$db_settings = (object) [
    'driver' => 'mysql', // mysql, pgsql, sqlite, sqlsrv, oci
    'server' => 'localhost',
    'schema' => 'your_database',
    'username' => 'your_username', 
    'password' => 'your_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'persistent' => false // Set to true for persistent connections
];

$db = new Database($db_settings);

Note: You'll need to have a KPT\Logger class available with static debug() and error() methods for logging functionality.

Basic Usage

Initialization

use KPT\Database;

$db_settings = (object) [
    'driver' => 'mysql',
    'server' => 'localhost',
    'schema' => 'my_database',
    'username' => 'db_user',
    'password' => 'db_password',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci'
];

$db = new Database($db_settings);

Connection Pooling

For applications that need multiple database connections or want to reuse connections:

// Create or retrieve a named connection
$db = Database::getInstance('default', $db_settings);

// Later, retrieve the same connection without settings
$db = Database::getInstance('default');

// Create additional connections
$analytics_db = Database::getInstance('analytics', $analytics_settings);

// Close a specific connection when done
Database::closeInstance('analytics');

Select Operations

// Fetch all users
$users = $db->query("SELECT * FROM users")->fetch();

// Fetch single user by ID
$user = $db->query("SELECT * FROM users WHERE id = ?")
           ->bind([123])
           ->single()
           ->fetch();

// Or use the first() shorthand
$user = $db->query("SELECT * FROM users WHERE id = ?")
           ->bind([123])
           ->first();

// Fetch as arrays instead of objects
$users = $db->query("SELECT * FROM users")
            ->asArray()
            ->fetch();

// Fetch with limit
$recent_users = $db->query("SELECT * FROM users ORDER BY created_at DESC")
                   ->fetch(10);

Insert Operations

// Insert new user
$user_id = $db->query("INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())")
              ->bind(['John Doe', 'john@example.com'])
              ->execute();

// The execute() method returns the last insert ID for INSERT queries
echo "New user ID: " . $user_id;

Update Operations

// Update user
$affected_rows = $db->query("UPDATE users SET name = ?, updated_at = NOW() WHERE id = ?")
                    ->bind(['Jane Doe', 123])
                    ->execute();

echo "Updated {$affected_rows} rows";

Delete Operations

// Delete user
$affected_rows = $db->query("DELETE FROM users WHERE id = ?")
                    ->bind([123])
                    ->execute();

echo "Deleted {$affected_rows} rows";

Parameter Binding

// Positional parameters (?)
$db->query("SELECT * FROM users WHERE id = ?")->bind(123);

// Multiple positional parameters
$db->query("SELECT * FROM users WHERE name = ? AND email = ?")
   ->bind(['John Doe', 'john@example.com']);

// Named parameters (:name)
$db->query("SELECT * FROM users WHERE name = :name AND email = :email")
   ->bind(['name' => 'John Doe', 'email' => 'john@example.com']);

// Automatic type detection handles strings, integers, booleans, and nulls
$db->query("SELECT * FROM users WHERE active = ? AND age > ? AND name LIKE ?")
   ->bind([true, 25, '%John%']);

Transactions

// Start transaction
$db->transaction();

try {
    // Perform multiple operations
    $user_id = $db->query("INSERT INTO users (name, email) VALUES (?, ?)")
                  ->bind(['John Doe', 'john@example.com'])
                  ->execute();
    
    $db->query("INSERT INTO user_profiles (user_id, bio) VALUES (?, ?)")
       ->bind([$user_id, 'Software Developer'])
       ->execute();
    
    // Commit if all operations succeed
    $db->commit();
    
} catch (Exception $e) {
    // Rollback on any error
    $db->rollback();
    throw $e;
}

// Check if currently in a transaction
if ($db->inTransaction()) {
    // ...
}

Raw Queries

For complex queries that don't fit the builder pattern:

// Raw SELECT
$results = $db->raw("
    SELECT u.*, p.bio 
    FROM users u 
    LEFT JOIN profiles p ON u.id = p.user_id 
    WHERE u.created_at > ?
", ['2023-01-01']);

// Raw INSERT with parameters
$insert_id = $db->raw("
    INSERT INTO complex_table (col1, col2, col3) 
    SELECT ?, ?, ? 
    FROM another_table 
    WHERE condition = ?
", ['value1', 'value2', 'value3', 'condition_value']);

Helper Methods

// Count records
$total_users = $db->count('users');
$active_users = $db->count('users', '*', 'active = ?', [true]);
$unique_emails = $db->count('users', 'DISTINCT email');

// Check if records exist
if ($db->exists('users', 'email = ?', ['john@example.com'])) {
    echo "User exists!";
}

// Get first record (shorthand for ->single()->fetch())
$user = $db->query("SELECT * FROM users WHERE email = ?")
           ->bind(['john@example.com'])
           ->first();

Batch Insert

// Insert multiple rows efficiently
$columns = ['name', 'email', 'created_at'];
$rows = [
    ['John Doe', 'john@example.com', '2024-01-01'],
    ['Jane Doe', 'jane@example.com', '2024-01-02'],
    ['Bob Smith', 'bob@example.com', '2024-01-03'],
];

$inserted = $db->insertBatch('users', $columns, $rows);
echo "Inserted {$inserted} rows";

Upsert and Replace

// Insert or update on duplicate key (MySQL)
$db->upsert(
    'users',
    ['id' => 1, 'name' => 'John Doe', 'email' => 'john@example.com'], // insert data
    ['name' => 'John Doe', 'email' => 'john@example.com'] // update data on duplicate
);

// Replace (delete + insert if exists)
$db->replace('users', [
    'id' => 1,
    'name' => 'John Doe',
    'email' => 'john@example.com'
]);

Query Profiling

Enable query profiling to debug slow queries:

// Enable profiling
$db->enableProfiling();

// Run your queries
$users = $db->query("SELECT * FROM users")->fetch();
$posts = $db->query("SELECT * FROM posts WHERE user_id = ?")->bind([1])->fetch();

// Get the query log
$log = $db->getQueryLog();
foreach ($log as $entry) {
    echo "Query: {$entry['query']}\n";
    echo "Duration: {$entry['duration_ms']}ms\n";
    echo "Timestamp: {$entry['timestamp']}\n";
}

// Clear the log
$db->clearQueryLog();

// Disable profiling
$db->disableProfiling();

Quoting Values

For edge cases where manual escaping is needed:

$quoted = $db->quote("O'Brien");
// Returns: 'O\'Brien'

Method Reference

Query Building

query(string $sql) - Set the SQL query to execute
bind(mixed $params) - Bind parameters (single value, array, or named parameters)
single() - Set mode to fetch single record
many() - Set mode to fetch multiple records (default)
asArray() - Return results as associative arrays
asObject() - Return results as objects (default)

Execution

fetch(?int $limit = null) - Execute SELECT queries and return results
first() - Fetch the first record (shorthand for single()->fetch())
execute() - Execute INSERT/UPDATE/DELETE queries
raw(string $query, array $params = []) - Execute raw SQL

Helper Methods

count(string $table, string $column = '*', ?string $where = null, array $params = []) - Count records
exists(string $table, string $where, array $params = []) - Check if records exist
insertBatch(string $table, array $columns, array $rows) - Insert multiple rows
upsert(string $table, array $data, array $update) - Insert or update on duplicate
replace(string $table, array $data) - Replace record
quote(string $value, int $type = PDO::PARAM_STR) - Quote a string for safe use

Transactions

transaction() - Begin a transaction
commit() - Commit the current transaction
rollback() - Roll back the current transaction
inTransaction() - Check if currently in a transaction

Connection Management

configure(array|object $config) - Static method to create a configured instance
getInstance(string $name, ?object $settings) - Get or create a named connection
closeInstance(string $name) - Close a named connection

Profiling

enableProfiling() - Enable query logging
disableProfiling() - Disable query logging
getQueryLog() - Get logged queries
clearQueryLog() - Clear the query log

Utilities

getLastId() - Get the last inserted ID
reset() - Reset the query builder state

Method Chaining

All query building methods return $this, allowing for fluent chaining:

$user = $db->query("SELECT * FROM users WHERE email = ?")
           ->bind('john@example.com')
           ->single()
           ->asArray()
           ->fetch();

Error Handling

The class throws exceptions for database errors. Always wrap database operations in try-catch blocks:

try {
    $result = $db->query("SELECT * FROM users")->fetch();
} catch (Exception $e) {
    // Handle database error
    error_log("Database error: " . $e->getMessage());
}

Logging

The class includes comprehensive logging through a Logger class:

Debug logs for successful operations
Error logs for failures and exceptions
Parameter binding information
Query execution details

Security

Prepared Statements: All queries use prepared statements to prevent SQL injection
Parameter Type Detection: Automatic binding with appropriate PDO parameter types
Input Validation: Validates queries and parameters before execution

Contributing

Fork the repository
Create a feature branch
Add tests for new functionality
Ensure all tests pass
Submit a pull request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Author

Kevin Pirnie - me@kpirnie.com

kevinpirnie / kpt-database

Maintainers

Details

README

Features

Requirements

Installation

Configuration

Basic Usage

Initialization

Connection Pooling

Select Operations

Insert Operations

Update Operations

Delete Operations

Parameter Binding

Transactions

Raw Queries

Helper Methods

Batch Insert

Upsert and Replace

Query Profiling

Quoting Values

Method Reference

Query Building

Execution

Helper Methods

Transactions

Connection Management

Profiling

Utilities

Method Chaining

Error Handling

Logging

Security

Contributing

License

Author