PHPackages                             kevinpirnie/kpt-database - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. kevinpirnie/kpt-database

ActiveLibrary[Database & ORM](/categories/database)

kevinpirnie/kpt-database
========================

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

v1.1.98(3mo ago)0268[1 PRs](https://github.com/kpirnie/kp-database/pulls)1MITPHPPHP >=8.2CI passing

Since Jan 25Pushed 3mo agoCompare

[ Source](https://github.com/kpirnie/kp-database)[ Packagist](https://packagist.org/packages/kevinpirnie/kpt-database)[ Docs](https://github.com/kpirnie/kp-database)[ Fund](https://ko-fi.com/kevinpirnie)[ Fund](https://www.paypal.biz/kevinpirnie)[ RSS](/packages/kevinpirnie-kpt-database/feed)WikiDiscussions main Synced 1mo ago

READMEChangelog (1)Dependencies (3)Versions (3)Used By (1)

KPT Database
============

[](#kpt-database)

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

Features
--------

[](#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
------------

[](#requirements)

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

Installation
------------

[](#installation)

Install via Composer:

```
composer require kevinpirnie/kpt-database
```

Configuration
-------------

[](#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
-----------

[](#basic-usage)

### Initialization

[](#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

[](#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

[](#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-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-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-operations)

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

echo "Deleted {$affected_rows} rows";
```

### Parameter Binding

[](#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

[](#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

[](#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

[](#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

[](#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

[](#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

[](#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

[](#quoting-values)

For edge cases where manual escaping is needed:

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

Method Reference
----------------

[](#method-reference)

### Query Building

[](#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

[](#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

[](#helper-methods-1)

- `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

[](#transactions-1)

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

### Connection Management

[](#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

[](#profiling)

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

### Utilities

[](#utilities)

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

Method Chaining
---------------

[](#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
--------------

[](#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
-------

[](#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
--------

[](#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
------------

[](#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
-------

[](#license)

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

Author
------

[](#author)

**Kevin Pirnie** -

###  Health Score

40

—

FairBetter than 88% of packages

Maintenance80

Actively maintained with recent releases

Popularity15

Limited adoption so far

Community10

Small or concentrated contributor base

Maturity48

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Unknown

Total

1

Last Release

113d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/e0ccd9a87409238ce2f7e258d1ba32b17954f8ad855f3f20382b7f041b6fc943?d=identicon)[kpirnie](/maintainers/kpirnie)

---

Top Contributors

[![kpirnie](https://avatars.githubusercontent.com/u/48323656?v=4)](https://github.com/kpirnie "kpirnie (9 commits)")

---

Tags

databaseormmysqlsqlpdofluentquery builder

###  Code Quality

TestsPHPUnit

Code StylePHP\_CodeSniffer

### Embed Badge

![Health badge](/badges/kevinpirnie-kpt-database/health.svg)

```
[![Health](https://phpackages.com/badges/kevinpirnie-kpt-database/health.svg)](https://phpackages.com/packages/kevinpirnie-kpt-database)
```

###  Alternatives

[cycle/database

DBAL, schema introspection, migration and pagination

64690.9k31](/packages/cycle-database)[tommyknocker/pdo-database-class

Framework-agnostic PHP database library with unified API for MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, and Oracle. Query Builder, caching, sharding, window functions, CTEs, JSON, migrations, ActiveRecord, CLI tools, AI-powered analysis. Zero external dependencies.

845.7k](/packages/tommyknocker-pdo-database-class)[ramadan/easy-model

A Laravel package for enjoyably managing database queries.

101.6k](/packages/ramadan-easy-model)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)