Download the PHP package kevinpirnie/kpt-database without Composer

KPT Database

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:

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:

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

Basic Usage

Initialization

Connection Pooling

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

Select Operations

Insert Operations

Update Operations

Delete Operations

Parameter Binding

Transactions

Raw Queries

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

Helper Methods

Batch Insert

Upsert and Replace

Query Profiling

Enable query profiling to debug slow queries:

Quoting Values

For edge cases where manual escaping is needed:

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:

Error Handling

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

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

1. Fork the repository
2. Create a feature branch
3. Add tests for new functionality
4. Ensure all tests pass
5. Submit a pull request

License

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

Author

Kevin Pirnie - [email protected]