PHPackages                             tommyknocker/pdo-database-class - 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. tommyknocker/pdo-database-class

ActiveLibrary[Database & ORM](/categories/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.

v2.12.1(1mo ago)845.7k↓33.3%49[1 issues](https://github.com/tommyknocker/pdo-database-class/issues)MITPHPPHP ^8.4CI passing

Since May 27Pushed 1mo ago11 watchersCompare

[ Source](https://github.com/tommyknocker/pdo-database-class)[ Packagist](https://packagist.org/packages/tommyknocker/pdo-database-class)[ Docs](https://github.com/tommyknocker/pdo-database-class)[ RSS](/packages/tommyknocker-pdo-database-class/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (10)Dependencies (20)Versions (34)Used By (0)

PDOdb
=====

[](#pdodb)

[![PHP Version](https://camo.githubusercontent.com/9c2f8ad80d34105266a94c4c06234f8ed18c968d3595039c2d9a7becd1e71c8b/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f7068702d253345253344382e342d626c75652e737667)](https://php.net)[![Latest Version](https://camo.githubusercontent.com/d83222fd496ca9b9416b654fe2175dc87f6edadec5a6cc160dada3760788827e/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f746f6d6d796b6e6f636b65722f70646f2d64617461626173652d636c6173732e737667)](https://packagist.org/packages/tommyknocker/pdo-database-class)[![Tests](https://github.com/tommyknocker/pdo-database-class/actions/workflows/tests.yml/badge.svg)](https://github.com/tommyknocker/pdo-database-class/actions)[![PHPStan Level 8](https://camo.githubusercontent.com/d117944b58da8146f96b4ef7403807610a20eeb3fbcaaaf95157bbcdad1686eb/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048505374616e2d6c6576656c253230382d627269676874677265656e2e737667)](https://phpstan.org/)[![Coverage](https://camo.githubusercontent.com/134de02a01ae6b24b1d6c4af7bf3554809b52ec3484c2fb81034eb5796f18c60/68747470733a2f2f636f6465636f762e696f2f67682f746f6d6d796b6e6f636b65722f70646f2d64617461626173652d636c6173732f6272616e63682f6d61737465722f67726170682f62616467652e737667)](https://codecov.io/gh/tommyknocker/pdo-database-class)[![License](https://camo.githubusercontent.com/8bb50fd2278f18fc326bf71f6e88ca8f884f72f179d3e555e20ed30157190d0d/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d4d49542d677265656e2e737667)](LICENSE)[![Downloads](https://camo.githubusercontent.com/f8ed3d436996b9654b8fbf18b9c13b57184ad57750e718e707452a6e1dbe02b2/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f746f6d6d796b6e6f636b65722f70646f2d64617461626173652d636c6173732e737667)](https://packagist.org/packages/tommyknocker/pdo-database-class)[![GitHub Stars](https://camo.githubusercontent.com/698fb4496e90f29459a9893ad7f1aba59ae09eb7ceca3f4943376ffda3327d89/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f73746172732f746f6d6d796b6e6f636b65722f70646f2d64617461626173652d636c6173733f7374796c653d736f6369616c)](https://github.com/tommyknocker/pdo-database-class)

**PDOdb** is a lightweight, framework-agnostic PHP database library providing a **unified API** across MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server (MSSQL), and Oracle.

Built on top of PDO with **zero external dependencies**, it offers:

**Core Features:**

- **Fluent Query Builder** - Intuitive, chainable API for all database operations
- **Cross-Database Compatibility** - Automatic SQL dialect handling (MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, Oracle)
- **80+ Helper Functions** - SQL helpers for strings, dates, math, JSON, aggregations, and more (REPEAT, REVERSE, LPAD, RPAD emulated for SQLite; REGEXP operations supported across all dialects)

**Performance:**

- **Query Caching** - PSR-16 integration for result caching (10-1000x faster repeated queries)
- **Query Compilation Cache** - Cache compiled SQL strings (10-30% performance improvement)
- **Prepared Statement Pool** - Automatic statement caching with LRU eviction (20-50% faster repeated queries)
- **Query Performance Profiling** - Built-in profiler for tracking execution times, memory usage, and slow query detection

**Advanced Features:**

- **Window Functions** - Advanced analytics with ROW\_NUMBER, RANK, LAG, LEAD, running totals, moving averages
- **Common Table Expressions (CTEs)** - WITH clauses for complex queries, recursive CTEs for hierarchical data, materialized CTEs for performance optimization
- **LATERAL JOINs** - Correlated subqueries in FROM clause for PostgreSQL, MySQL, and MSSQL (CROSS APPLY/OUTER APPLY)
- **Set Operations** - UNION, INTERSECT, EXCEPT for combining query results with automatic deduplication
- **JSON Operations** - Native JSON support with consistent API across all databases
- **Full-Text Search** - Cross-database FTS with unified API (MySQL FULLTEXT, PostgreSQL tsvector, SQLite FTS5)
- **Read/Write Splitting** - Horizontal scaling with master-replica architecture and load balancing
- **Sharding** - Horizontal partitioning across multiple databases with automatic query routing (range, hash, modulo strategies)
- **ActiveRecord Pattern** - Optional lightweight ORM for object-based database operations with relationships (hasOne, hasMany, belongsTo, hasManyThrough), eager/lazy loading, and query scopes

**Developer Experience:**

- **🤖 AI-Powered Analysis** - Get intelligent database optimization recommendations using OpenAI, Anthropic, Google, Microsoft, DeepSeek, Yandex, or Ollama. Analyze queries, optimize schema, and get AI-powered suggestions through `explainAiAdvice()` method or CLI commands. Includes MCP server for IDE/agent integration.
- **🖥️ Interactive TUI Dashboard** - Real-time database monitoring with full-screen terminal interface. 8 panes across 2 screens: Active Queries, Connection Pool, Cache Statistics, Server Metrics, Schema Browser, Migration Manager, Server Variables, and SQL Scratchpad. Features include global search filter, query inspection, performance tracking, query management, and keyboard navigation. Launch with `pdodb ui`
- **CLI Tools** - Database management, user management, dump/restore, migration generator, seed generator, model generator, schema inspector, interactive query tester (REPL), AI analysis commands
- **Enhanced EXPLAIN** - Automatic detection of full table scans, missing indexes, and optimization recommendations
- **Exception Hierarchy** - Typed exceptions for precise error handling
- **Enhanced Error Diagnostics** - Query context, sanitized parameters, and debug information in exceptions
- **SQL Formatter/Pretty Printer** - Human-readable SQL output for debugging with indentation and line breaks
- **Query Debugging** - Comprehensive debug information and query inspection tools
- **PSR-14 Event Dispatcher** - Event-driven architecture for monitoring, auditing, and middleware
- **Plugin System** - Extend PdoDb with custom plugins for macros, scopes, and event listeners

**Production Ready:**

- **Fully Tested** - 3806 tests, 12180 assertions across all dialects
- **Type-Safe** - PHPStan level 8 validated, PSR-12 compliant
- **Zero Memory Leaks** - Production-tested memory management with automatic cursor cleanup
- **Connection Retry** - Automatic retry with exponential backoff
- **Transactions & Locking** - Full transaction support with table locking and savepoints for nested transactions
- **Batch Processing** - Memory-efficient generators for large datasets with zero memory leaks

**Additional Capabilities:**

- **Bulk Operations** - CSV/XML/JSON loaders, multi-row inserts, UPSERT support
- **INSERT ... SELECT** - Fluent API for copying data between tables with QueryBuilder, subqueries, and CTE support
- **UPDATE/DELETE with JOIN** - Update and delete operations with JOIN clauses (MySQL/MariaDB/PostgreSQL/MSSQL)
- **MERGE Statements** - INSERT/UPDATE/DELETE based on match conditions (PostgreSQL/MSSQL native, MySQL/SQLite emulated)
- **Schema Introspection** - Query indexes, foreign keys, and constraints programmatically
- **DDL Query Builder** - Production-ready fluent API for creating, altering, and managing database schema (tables, columns, indexes, foreign keys, constraints) with Yii2-style methods, partial indexes, fulltext/spatial indexes, cross-dialectal support, and **dialect-specific types** (MySQL ENUM/SET, PostgreSQL UUID/JSONB/arrays, MSSQL UNIQUEIDENTIFIER/NVARCHAR, SQLite type affinity)
- **Database Migrations** - Version-controlled schema changes with rollback support (Yii2-inspired)
- **Database Seeds** - Populate database with initial or test data, batch tracking, rollback support
- **Advanced Pagination** - Full, simple, and cursor-based pagination with metadata
- **Export Helpers** - Export results to JSON, CSV, and XML formats
- **DISTINCT & DISTINCT ON** - Remove duplicates with full PostgreSQL DISTINCT ON support
- **FILTER Clause** - Conditional aggregates (SQL:2003 standard) with automatic MySQL fallback to CASE WHEN

Inspired by [ThingEngineer/PHP-MySQLi-Database-Class](https://github.com/ThingEngineer/PHP-MySQLi-Database-Class) and [Yii2 framework](https://github.com/yiisoft/yii2-framework)

---

Why PDOdb?
----------

[](#why-pdodb)

**Perfect for:**

- ✅ **Beginners** - Simple, intuitive API with zero configuration needed
- ✅ **Cross-database projects** - Switch between MySQL, PostgreSQL, SQLite, MSSQL, Oracle without code changes
- ✅ **Performance-critical apps** - Built-in caching, query optimization, profiling
- ✅ **Database monitoring** - Interactive TUI Dashboard for real-time monitoring (`pdodb ui`)
- ✅ **Modern PHP** - Type-safe, PSR-compliant, PHP 8.4+ features

**vs. Raw PDO:**

- ✅ Fluent query builder instead of manual SQL strings
- ✅ Automatic parameter binding (SQL injection protection built-in)
- ✅ Cross-database compatibility out of the box
- ✅ Helper functions for common operations

**vs. Eloquent/Doctrine:**

- ✅ Zero dependencies (no framework required)
- ✅ Lightweight (no ORM overhead)
- ✅ Direct SQL access when needed
- ✅ Better performance for complex queries
- ✅ Optional ActiveRecord pattern available
- ✅ **Built-in TUI Dashboard** - Real-time database monitoring without external tools

---

Table of Contents
-----------------

[](#table-of-contents)

- [Requirements](#requirements)
- [Installation](#installation)
- [📖 Documentation](#-documentation)
- [📚 Examples](#-examples)
- [Quick Example](#quick-example)
- [5-Minute Tutorial](#5-minute-tutorial)
- [Configuration](#configuration)
- [Quick Start](#quick-start)
    - [AI-Powered Query Analysis](#-ai-powered-query-analysis)
- [JSON Operations](#json-operations)
- [Advanced Usage](#advanced-usage)
- [CLI Tools](#cli-tools)
- [Error Handling](#error-handling)
- [Performance Tips](#performance-tips)
- [Helper Functions](#helper-functions)
- [API Reference](#api-reference)
- [Dialect Differences](#dialect-differences)
- [Frequently Asked Questions](#frequently-asked-questions)
- [Migration Guide](#migration-guide)
- [Troubleshooting](#troubleshooting)
- [Testing](#testing)
- [Contributing](#contributing)
- [License](#license)

---

Requirements
------------

[](#requirements)

- **PHP**: 8.4 or higher
- **PDO Extensions**:
    - `pdo_mysql` for MySQL/MariaDB
    - `pdo_pgsql` for PostgreSQL
    - `pdo_sqlite` for SQLite
    - `sqlsrv` for Microsoft SQL Server (requires Microsoft ODBC Driver for SQL Server)
    - `oci` for Oracle Database
- **Supported Databases**:
    - MySQL 5.7+ / MariaDB 10.3+
    - PostgreSQL 9.4+
    - SQLite 3.38+
    - Microsoft SQL Server 2019+ / Azure SQL Database
    - Oracle Database 12c+

Check if your SQLite has JSON support:

```
sqlite3 :memory: "SELECT json_valid('{}')"
```

---

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

[](#installation)

Install via Composer:

```
composer require tommyknocker/pdo-database-class
```

For specific versions:

```
# Latest 2.x version
composer require tommyknocker/pdo-database-class:^2.0

# Latest 1.x version
composer require tommyknocker/pdo-database-class:^1.0

# Development version
composer require tommyknocker/pdo-database-class:dev-master
```

### Quick Setup with `pdodb init`

[](#quick-setup-with-pdodb-init)

**Fastest way to get started:** Use the interactive wizard to configure your project:

```
vendor/bin/pdodb init
```

The wizard will guide you through:

- Database connection settings (MySQL, PostgreSQL, SQLite, MSSQL, Oracle)
- Configuration file format (`.env` or `config/db.php`)
- Directory structure creation (migrations, models, repositories, services)
- Connection testing
- Advanced options (caching, table prefix, multiple connections)

See [CLI Tools Documentation](documentation/05-advanced-features/21-cli-tools.md#project-initialization-pdodb-init) for more details.

---

📖 Documentation
---------------

[](#-documentation)

Complete documentation is available in the [`documentation/`](documentation/) directory with 56+ detailed guides covering all features:

- **[Getting Started](documentation/01-getting-started/)** - Installation, configuration, your first connection
- **[Core Concepts](documentation/02-core-concepts/)** - Connection management, query builder, parameter binding, dialects
- **[Query Builder](documentation/03-query-builder/)** - SELECT, DML, filtering, joins, aggregations, subqueries
- **[JSON Operations](documentation/04-json-operations/)** - Working with JSON across all databases
- **[Advanced Features](documentation/05-advanced-features/)** - Transactions, batch processing, bulk operations, UPSERT, query scopes, query macros, plugin system
- **[Error Handling](documentation/06-error-handling/)** - Exception hierarchy, enhanced error diagnostics with query context, retry logic, logging, monitoring
- **[Helper Functions](documentation/07-helper-functions/)** - Complete reference for all helper functions
- **[Best Practices](documentation/08-best-practices/)** - Security, performance, memory management, code organization
- **[API Reference](documentation/09-reference/)** - Complete API documentation
- **[Cookbook](documentation/10-cookbook/)** - Common patterns, real-world examples, troubleshooting

Each guide includes working code examples, dialect-specific notes, security considerations, and best practices.

Start here: [Documentation Index](documentation/README.md)

📚 Examples
----------

[](#-examples)

Comprehensive, runnable examples are available in the [`examples/`](examples/) directory:

- **[Basic](examples/01-basic/)** - Connection, CRUD, WHERE conditions
- **[Intermediate](examples/02-intermediate/)** - JOINs, aggregations, pagination, transactions, savepoints
- **[Advanced](examples/03-advanced/)** - Connection pooling, bulk operations, UPSERT, subqueries, MERGE, window functions, CTEs
- **[JSON Operations](examples/04-json/)** - Complete guide to JSON features
- **[Helper Functions](examples/05-helpers/)** - String, math, date/time, NULL, comparison helpers
- **[Performance](examples/07-performance/)** - Query caching, compilation cache, profiling, EXPLAIN analysis
- **[ActiveRecord](examples/09-active-record/)** - Object-based operations, relationships, scopes

Each example is self-contained with setup instructions. See [`examples/README.md`](examples/README.md) for the full catalog.

**Quick start:**

```
cd examples

# SQLite (ready to use, no setup required)
php 01-basic/02-simple-crud.php

# MySQL (update config.mysql.php with your credentials)
PDODB_DRIVER=mysql php 01-basic/02-simple-crud.php

# Test all examples on all available databases
./scripts/test-examples.sh
```

---

Quick Example
-------------

[](#quick-example)

**Fastest start:** Run `vendor/bin/pdodb init` for interactive setup, or get started manually:

```
use tommyknocker\pdodb\PdoDb;

// Connect (SQLite - no setup needed!)
$db = new PdoDb('sqlite', ['path' => ':memory:']);

// Create table
$db->rawQuery('CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    age INTEGER
)');

// Insert
$id = $db->find()->table('users')->insert([
    'name' => 'John',
    'email' => 'john@example.com',
    'age' => 30
]);

// Query
$users = $db->find()
    ->from('users')
    ->where('age', 18, '>')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();

// Update
$db->find()
    ->table('users')
    ->where('id', $id)
    ->update(['age' => 31]);
```

**That's it!** No configuration, no dependencies, just works.

---

5-Minute Tutorial
-----------------

[](#5-minute-tutorial)

### Step 1: Install

[](#step-1-install)

```
composer require tommyknocker/pdo-database-class
```

### Step 2: Initialize Project

[](#step-2-initialize-project)

Use the interactive wizard:

```
vendor/bin/pdodb init
```

**Alternative:** Manual configuration

```
use tommyknocker\pdodb\PdoDb;

// SQLite (easiest - no database server needed)
$db = new PdoDb('sqlite', ['path' => ':memory:']);

// Or MySQL/PostgreSQL
$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'dbname' => 'mydb',
    'username' => 'user',
    'password' => 'pass'
]);
```

### Step 3: Create Table

[](#step-3-create-table)

```
// Simple approach (raw SQL)
$db->rawQuery('CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT,
    age INTEGER
)');

// Or use DDL Query Builder
$db->schema()->createTable('products', [
    'id' => $db->schema()->primaryKey(),
    'name' => $db->schema()->string(255)->notNull(),
    'status' => $db->schema()->enum(['draft', 'published', 'archived'])
        ->defaultValue('draft'),
    'created_at' => $db->schema()->timestamp()->defaultExpression('CURRENT_TIMESTAMP')
]);
```

### Step 4: CRUD Operations

[](#step-4-crud-operations)

```
// Create
$id = $db->find()->table('users')->insert([
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'age' => 30
]);

// Read
$users = $db->find()->from('users')->get();
$user = $db->find()->from('users')->where('id', $id)->getOne();

// Update
$db->find()->table('users')
    ->where('id', $id)
    ->update(['name' => 'Bob']);

// Delete
$db->find()->table('users')->where('id', $id)->delete();
```

### Step 5: Monitor Your Database

[](#step-5-monitor-your-database)

Launch the interactive TUI Dashboard to monitor your database in real-time:

```
vendor/bin/pdodb ui
```

Monitor active queries, connection pool, cache statistics, and server metrics. Press `h` for help, `q` to quit.

**Next:** See [Quick Start](#quick-start) for more examples.

---

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

[](#configuration)

### Basic Configuration

[](#basic-configuration)

```
use tommyknocker\pdodb\PdoDb;

// MySQL
$db = new PdoDb('mysql', [
    'host' => '127.0.0.1',
    'username' => 'testuser',
    'password' => 'testpass',
    'dbname' => 'testdb',
    'port' => 3306,
    'charset' => 'utf8mb4',
]);

// PostgreSQL
$db = new PdoDb('pgsql', [
    'host' => '127.0.0.1',
    'username' => 'testuser',
    'password' => 'testpass',
    'dbname' => 'testdb',
    'port' => 5432,
]);

// SQLite
$db = new PdoDb('sqlite', [
    'path' => '/path/to/database.sqlite',  // or ':memory:' for in-memory
]);

// MSSQL
$db = new PdoDb('sqlsrv', [
    'host' => 'localhost',
    'username' => 'testuser',
    'password' => 'testpass',
    'dbname' => 'testdb',
    'port' => 1433,
]);
```

### Advanced Configuration

[](#advanced-configuration)

**Connection Pooling:**

```
$db = new PdoDb();
$db->addConnection('mysql_main', ['driver' => 'mysql', ...]);
$db->addConnection('pgsql_analytics', ['driver' => 'pgsql', ...]);
$db->connection('mysql_main')->find()->from('users')->get();
```

**Read/Write Splitting:**

```
$db->enableReadWriteSplitting(new RoundRobinLoadBalancer());
$db->addConnection('master', [...], ['type' => 'write']);
$db->addConnection('replica-1', [...], ['type' => 'read']);
// SELECTs automatically go to replicas, DML to master
```

**Query Caching:**

```
$cache = CacheFactory::create(['type' => 'filesystem', 'directory' => '/var/cache']);
$db = new PdoDb('mysql', $config, [], null, $cache);
$users = $db->find()->from('users')->cache(3600)->get();
```

See [Configuration Documentation](documentation/01-getting-started/04-configuration.md) for complete configuration options.

---

Quick Start
-----------

[](#quick-start)

**Note**: All query examples start with `$db->find()` which returns a `QueryBuilder` instance.

### 🤖 AI-Powered Query Analysis

[](#-ai-powered-query-analysis)

Get intelligent optimization recommendations for your queries using AI:

```
use tommyknocker\pdodb\PdoDb;

$db = PdoDb::fromEnv();

// Configure AI (or use environment variables: PDODB_AI_OPENAI_KEY, etc.)
$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'dbname' => 'mydb',
    'username' => 'user',
    'password' => 'pass',
    'ai' => [
        'provider' => 'openai',
        'openai_key' => 'sk-...',
    ],
]);

// Get AI-enhanced analysis
$result = $db->find()
    ->from('users')
    ->where('email', 'user@example.com')
    ->explainAiAdvice(tableName: 'users', provider: 'openai');

// Access base analysis (traditional EXPLAIN)
foreach ($result->baseAnalysis->issues as $issue) {
    echo "Issue: {$issue->message}\n";
}

// Access AI recommendations
echo "AI Analysis:\n";
echo $result->aiAnalysis . "\n";
```

Or use CLI:

```
# Set API key
export PDODB_AI_OPENAI_KEY=sk-...
export PDODB_AI_OPENAI_MODEL=gpt-4o-mini  # Optional: gpt-4, gpt-3.5-turbo
export PDODB_AI_GOOGLE_MODEL=gemini-2.5-flash  # Optional: gemini-2.5-pro, gemini-2.0-flash-001, gemini-flash-latest

# Analyze query
pdodb ai query "SELECT * FROM users WHERE email = 'user@example.com'" \
    --provider=openai \
    --table=users
```

**Supported Providers:** OpenAI, Anthropic, Google, Microsoft, DeepSeek, Yandex, Ollama (local, no API key)

**Model Selection:** Configure models via environment variables (`PDODB_AI__MODEL`) or config array (`ai.providers..model`)

See [AI Analysis Documentation](documentation/05-advanced-features/23-ai-analysis.md) for complete guide.

### Basic CRUD Operations

[](#basic-crud-operations)

```
// SELECT
$user = $db->find()
    ->from('users')
    ->where('id', 10)
    ->getOne();

$users = $db->find()
    ->from('users')
    ->where('age', 18, '>=')
    ->get();

// INSERT
$id = $db->find()->table('users')->insert([
    'name' => 'Alice',
    'email' => 'alice@example.com',
    'age' => 30
]);

// UPDATE
$db->find()
    ->table('users')
    ->where('id', $id)
    ->update(['age' => 31]);

// DELETE
$db->find()
    ->table('users')
    ->where('id', $id)
    ->delete();
```

### Filtering and Joining

[](#filtering-and-joining)

```
use tommyknocker\pdodb\helpers\Db;

// WHERE conditions
$users = $db->find()
    ->from('users')
    ->where('status', 'active')
    ->andWhere('age', 18, '>')
    ->andWhere(Db::like('email', '%@example.com'))
    ->get();

// JOIN and GROUP BY
$stats = $db->find()
    ->from('users AS u')
    ->select(['u.id', 'u.name', 'total' => Db::sum('o.amount')])
    ->leftJoin('orders AS o', 'o.user_id = u.id')
    ->groupBy('u.id')
    ->having(Db::sum('o.amount'), 1000, '>')
    ->get();
```

### Transactions

[](#transactions)

```
$db->startTransaction();
try {
    $userId = $db->find()->table('users')->insert(['name' => 'Alice']);
    $db->find()->table('orders')->insert(['user_id' => $userId, 'total' => 100]);
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
}
```

See [Query Builder Documentation](documentation/03-query-builder/) for more examples.

---

JSON Operations
---------------

[](#json-operations)

PDOdb provides a unified JSON API that works consistently across all databases.

```
use tommyknocker\pdodb\helpers\Db;

// Create JSON data
$db->find()->table('users')->insert([
    'name' => 'John',
    'meta' => Db::jsonObject(['city' => 'NYC', 'age' => 30]),
    'tags' => Db::jsonArray('php', 'mysql', 'docker')
]);

// Query JSON
$adults = $db->find()
    ->from('users')
    ->where(Db::jsonPath('meta', ['age'], '>', 25))
    ->get();

// Extract JSON values
$users = $db->find()
    ->from('users')
    ->select([
        'id',
        'name',
        'city' => Db::jsonGet('meta', ['city'])
    ])
    ->get();

// Update JSON
$db->find()
    ->table('users')
    ->where('id', 1)
    ->update([
        'meta' => Db::jsonSet('meta', ['city'], 'London')
    ]);
```

See [JSON Operations Documentation](documentation/04-json-operations/) for complete guide.

---

Advanced Usage
--------------

[](#advanced-usage)

### Raw Queries

[](#raw-queries)

```
$users = $db->rawQuery(
    'SELECT * FROM users WHERE age > :age',
    ['age' => 18]
);
```

### Subqueries

[](#subqueries)

```
$users = $db->find()
    ->from('users')
    ->whereIn('id', function($query) {
        $query->from('orders')
            ->select('user_id')
            ->where('total', 1000, '>');
    })
    ->get();
```

### Pagination

[](#pagination)

```
// Full pagination (with total count)
$result = $db->find()
    ->from('posts')
    ->orderBy('created_at', 'DESC')
    ->paginate(20, 1);

// Cursor pagination (most efficient)
$result = $db->find()
    ->from('posts')
    ->orderBy('id', 'DESC')
    ->cursorPaginate(20);
```

### Batch Processing

[](#batch-processing)

```
// Process in batches
foreach ($db->find()->from('users')->batch(100) as $batch) {
    foreach ($batch as $user) {
        processUser($user);
    }
}

// Stream results (minimal memory)
foreach ($db->find()->from('users')->stream() as $user) {
    processUser($user);
}
```

### Query Caching

[](#query-caching)

```
// Cache for 1 hour
$products = $db->find()
    ->from('products')
    ->where('category', 'Electronics')
    ->cache(3600)
    ->get();
```

See [Advanced Features Documentation](documentation/05-advanced-features/) for complete guide.

---

CLI Tools
---------

[](#cli-tools)

PDOdb provides convenient command-line tools for common development tasks:

```
vendor/bin/pdodb  [subcommand] [arguments] [options]
```

### 🖥️ Interactive TUI Dashboard

[](#️-interactive-tui-dashboard)

**Monitor your database in real-time** with a beautiful full-screen terminal interface:

```
vendor/bin/pdodb ui
```

**Features:**

- 📊 **8 Monitoring Panes** - Active Queries, Connection Pool, Cache Statistics, Server Metrics, Schema Browser, Migration Manager, Server Variables, SQL Scratchpad
- 🔍 **Global Search** - Press `/` to filter tables and server variables in real-time (case-insensitive)
- 📋 **Schema Browser** - Navigate database schema, view tables, columns, indexes, and foreign keys with tree navigation
- 🔧 **Migration Manager** - View migration status, run pending migrations, rollback last migration, create new migrations
- 📈 **Server Variables** - Browse all server configuration variables with highlighting for important performance settings
- 💻 **SQL Scratchpad** - Interactive SQL editor with query history, autocomplete, transaction mode, and result viewing
- 🔍 **Query inspection** - View full query text, execution time, user, database in detail view
- ⚡ **Performance tracking** - Cache hit rates, connection counts, server uptime, query execution times
- 🛑 **Query management** - Kill long-running queries and connections with a single keystroke
- ⌨️ **Keyboard navigation** - Switch between 8 panes (1-8 keys), navigate screens (Tab/arrows), fullscreen mode, refresh intervals
- 🎨 **Color-coded metrics** - Visual indicators for performance and health
- 📱 **Dual-screen support** - Navigate between two screens (panes 1-4 and 5-8)

Perfect for debugging, monitoring production databases, understanding query patterns, and managing database schema.

### Available Commands

[](#available-commands)

- **`ui`** ⭐ - **Interactive TUI Dashboard** - Real-time database monitoring (full-screen terminal interface)
- **`ai`** 🤖 - **AI-Powered Analysis** - Get intelligent database optimization recommendations using OpenAI, Anthropic, Google, Microsoft, DeepSeek, Yandex, or Ollama
- **`db`** - Manage databases (create, drop, list, check existence)
- **`user`** - Manage database users (create, drop, grant/revoke privileges)
- **`dump`** - Dump and restore database (with compression, auto-naming, rotation)
- **`migrate`** - Manage database migrations
- **`schema`** - Inspect database schema
- **`query`** - Test SQL queries interactively (REPL)
- **`model`** - Generate ActiveRecord models
- **`table`** - Manage tables (info, create, drop, truncate)
- **`monitor`** - Monitor database queries and performance
- **`optimize`** - Database optimization analysis (analyze, structure, logs, query, db)

### Bash Completion

[](#bash-completion)

Install bash completion for enhanced CLI experience:

```
# Temporary (current session)
source > ~/.bashrc
```

### Examples

[](#examples)

```
# 🖥️ Launch interactive TUI Dashboard (real-time monitoring)
vendor/bin/pdodb ui

# 🤖 AI-powered analysis (requires API keys or Ollama)
vendor/bin/pdodb ai query "SELECT * FROM users WHERE email = 'user@example.com'" --provider=openai
vendor/bin/pdodb ai query "SELECT * FROM orders" --provider=anthropic --table=orders
vendor/bin/pdodb ai schema --table=users --provider=ollama

# Create database
vendor/bin/pdodb db create myapp

# Dump with compression and rotation
vendor/bin/pdodb dump --auto-name --compress=gzip --rotate=7

# Create migration
vendor/bin/pdodb migrate create create_users_table

# Generate model
vendor/bin/pdodb model make User users app/Models
```

See [CLI Tools Documentation](documentation/05-advanced-features/21-cli-tools.md) for complete guide.

---

Error Handling
--------------

[](#error-handling)

PDOdb provides a comprehensive exception hierarchy for better error handling:

```
use tommyknocker\pdodb\exceptions\{
    DatabaseException,
    ConnectionException,
    QueryException,
    ConstraintViolationException,
    TransactionException
};

try {
    $users = $db->find()->from('users')->get();
} catch (ConnectionException $e) {
    // Handle connection errors
    if ($e->isRetryable()) {
        // Implement retry logic
    }
} catch (QueryException $e) {
    // Handle query errors
    error_log("Query: " . $e->getQuery());
    error_log("Context: " . $e->getDescription());
} catch (ConstraintViolationException $e) {
    // Handle constraint violations
    error_log("Constraint: " . $e->getConstraintName());
}
```

All exceptions extend `PDOException` for backward compatibility and provide rich context information.

See [Error Handling Documentation](documentation/06-error-handling/) for complete guide.

---

Performance Tips
----------------

[](#performance-tips)

### Enable Query Caching

[](#enable-query-caching)

For applications with repeated queries, enable result caching:

```
$cache = new Psr16Cache(new FilesystemAdapter());
$db = new PdoDb('mysql', $config, [], null, $cache);

$products = $db->find()
    ->from('products')
    ->where('category', 'Electronics')
    ->cache(3600)
    ->get();
```

**Performance Impact:** 65-97% faster for repeated queries with cache hits.

### Use Batch Operations

[](#use-batch-operations)

```
// ❌ Slow: Multiple single inserts
foreach ($users as $user) {
    $db->find()->table('users')->insert($user);
}

// ✅ Fast: Single batch insert
$db->find()->table('users')->insertMulti($users);
```

### Always Limit Result Sets

[](#always-limit-result-sets)

```
// ✅ Safe: Limited results
$users = $db->find()->from('users')->limit(1000)->get();
```

### Use Batch Processing for Large Datasets

[](#use-batch-processing-for-large-datasets)

```
// Process in chunks
foreach ($db->find()->from('users')->batch(100) as $batch) {
    processBatch($batch);
}
```

See [Performance Documentation](documentation/08-best-practices/02-performance.md) for more tips.

---

Helper Functions
----------------

[](#helper-functions)

PDOdb provides 80+ helper functions for common SQL operations:

**Core Helpers:**

- `Db::raw()` - Raw SQL expressions
- `Db::concat()` - String concatenation
- `Db::now()` - Current timestamp

**String Operations:**

- `Db::upper()`, `Db::lower()`, `Db::trim()`, `Db::substring()`, `Db::replace()`

**Numeric Operations:**

- `Db::inc()`, `Db::dec()`, `Db::abs()`, `Db::round()`, `Db::mod()`

**Date/Time Functions:**

- `Db::now()`, `Db::date()`, `Db::year()`, `Db::month()`, `Db::day()`

**JSON Operations:**

- `Db::jsonObject()`, `Db::jsonArray()`, `Db::jsonGet()`, `Db::jsonPath()`, `Db::jsonContains()`

**Aggregate Functions:**

- `Db::count()`, `Db::sum()`, `Db::avg()`, `Db::min()`, `Db::max()`

**Full Reference:** See [Helper Functions Documentation](documentation/07-helper-functions/) for complete list and examples.

---

API Reference
-------------

[](#api-reference)

### PdoDb Main Class

[](#pdodb-main-class)

MethodDescription`find()`Returns QueryBuilder instance`rawQuery(string, array)`Execute raw SQL, returns array of rows`rawQueryOne(string, array)`Execute raw SQL, returns first row`startTransaction()`Begin transaction`commit()`Commit transaction`rollBack()`Roll back transaction`describe(string)`Get table structure`indexes(string)`Get all indexes for a table`keys(string)`Get foreign key constraints### QueryBuilder Methods

[](#querybuilder-methods)

**Table & Selection:**

- `table(string)` / `from(string)` - Set target table
- `select(array|string)` - Specify columns to select

**Filtering:**

- `where(...)` / `andWhere(...)` / `orWhere(...)` - Add WHERE conditions
- `whereIn(...)` / `whereNotIn(...)` - IN / NOT IN conditions
- `whereNull(...)` / `whereNotNull(...)` - NULL checks
- `whereBetween(...)` - BETWEEN conditions
- `join(...)` / `leftJoin(...)` / `rightJoin(...)` - Add JOIN clauses

**Data Manipulation:**

- `insert(array)` - Insert single row
- `insertMulti(array)` - Insert multiple rows
- `update(array)` - Update rows
- `delete()` - Delete rows

**Execution:**

- `get()` - Execute SELECT, return all rows
- `getOne()` - Execute SELECT, return first row
- `getValue()` - Execute SELECT, return single value

**Full Reference:** See [API Reference Documentation](documentation/09-reference/) for complete method list and signatures.

---

Dialect Differences
-------------------

[](#dialect-differences)

PDOdb handles most differences automatically, but here are some key points:

**UPSERT:**

- **MySQL**: `ON DUPLICATE KEY UPDATE`
- **PostgreSQL/SQLite**: `ON CONFLICT ... DO UPDATE SET`

Use `onDuplicate()` for portable UPSERT:

```
$db->find()->table('users')->onDuplicate([
    'age' => Db::inc()
])->insert(['email' => 'user@example.com', 'age' => 25]);
```

**JSON Functions:**

- **MySQL**: Uses `JSON_EXTRACT`, `JSON_CONTAINS`
- **PostgreSQL**: Uses `->`, `->>`, `@>` operators
- **SQLite**: Uses `json_extract`, `json_each`

All handled transparently through `Db::json*()` helpers.

**Full Reference:** See [Dialect Differences Documentation](documentation/09-reference/05-dialect-differences.md) for complete guide.

---

Frequently Asked Questions
--------------------------

[](#frequently-asked-questions)

### Is PDOdb an ORM?

[](#is-pdodb-an-orm)

No, PDOdb is a **query builder** with optional ActiveRecord pattern. It's lighter than full ORMs like Eloquent or Doctrine.

### Can I use raw SQL?

[](#can-i-use-raw-sql)

Yes! Use `rawQuery()` for complete control:

```
$users = $db->rawQuery('SELECT * FROM users WHERE age > :age', ['age' => 18]);
```

### Does it work with frameworks?

[](#does-it-work-with-frameworks)

Yes! PDOdb is framework-agnostic. Works with Laravel, Symfony, Yii, or no framework at all.

### Is it production-ready?

[](#is-it-production-ready)

Yes! 3806+ tests, 12180+ assertions, PHPStan level 8, used in production environments.

### What about security?

[](#what-about-security)

All queries use **prepared statements** automatically. SQL injection protection is built-in.

### Can I use it with existing PDO connections?

[](#can-i-use-it-with-existing-pdo-connections)

Yes! Pass your PDO instance:

```
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$db = new PdoDb('mysql', ['pdo' => $pdo]);
```

### Which database should I use for development?

[](#which-database-should-i-use-for-development)

**SQLite** is perfect for development - no server setup needed:

```
$db = new PdoDb('sqlite', ['path' => ':memory:']);
```

### Does it support transactions?

[](#does-it-support-transactions)

Yes! Full transaction support with savepoints:

```
$db->startTransaction();
try {
    // Your operations
    $db->commit();
} catch (\Exception $e) {
    $db->rollBack();
}
```

---

Migration Guide
---------------

[](#migration-guide)

### From Raw PDO

[](#from-raw-pdo)

**Before:**

```
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$stmt = $pdo->prepare('SELECT * FROM users WHERE age > :age');
$stmt->execute(['age' => 18]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
```

**After:**

```
$db = new PdoDb('mysql', [
    'host' => 'localhost',
    'dbname' => 'test',
    'username' => 'user',
    'password' => 'pass'
]);
$users = $db->find()->from('users')->where('age', 18, '>')->get();
```

### From Eloquent (Laravel)

[](#from-eloquent-laravel)

**Before:**

```
User::where('active', 1)
    ->where('age', '>', 18)
    ->orderBy('name')
    ->limit(10)
    ->get();
```

**After:**

```
$db->find()
    ->from('users')
    ->where('active', 1)
    ->andWhere('age', 18, '>')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->get();
```

See [Migration Guide Documentation](documentation/10-cookbook/03-migration-guide.md) for more examples.

---

Troubleshooting
---------------

[](#troubleshooting)

### "Driver not found" Error

[](#driver-not-found-error)

**Solution:** Install the required PHP extension:

```
sudo apt-get install php8.4-mysql php8.4-pgsql php8.4-sqlite3
```

### "JSON functions not available" (SQLite)

[](#json-functions-not-available-sqlite)

**Solution:** Check if JSON support is available:

```
sqlite3 :memory: "SELECT json_valid('{}')"
```

### "SQLSTATE\[HY000\]: General error: 1 near 'OFFSET'"

[](#sqlstatehy000-general-error-1-near-offset)

**Problem:** Using OFFSET without LIMIT in SQLite.

**Solution:** Always use LIMIT with OFFSET:

```
// ✅ Works
$db->find()->from('users')->limit(20)->offset(10)->get();
```

### Memory Issues with Large Result Sets

[](#memory-issues-with-large-result-sets)

**Solution:** Use batch processing or streaming:

```
foreach ($db->find()->from('users')->batch(100) as $batch) {
    processBatch($batch);
}
```

See [Troubleshooting Documentation](documentation/10-cookbook/04-troubleshooting.md) for more solutions.

---

Testing
-------

[](#testing)

The project includes comprehensive PHPUnit tests for all supported databases.

### Running Tests

[](#running-tests)

```
# Run all tests
./vendor/bin/phpunit

# Run specific dialect tests
./vendor/bin/phpunit tests/PdoDbMySQLTest.php

# Run with coverage
./vendor/bin/phpunit --coverage-html coverage
```

### Test Requirements

[](#test-requirements)

- **MySQL/MariaDB**: Running instance on localhost:3306
- **PostgreSQL**: Running instance on localhost:5432
- **SQLite**: No setup required (uses `:memory:`)
- **MSSQL**: Running instance on localhost:1433
- **Oracle**: Running instance on localhost:1521 (requires Oracle Instant Client and `pdo_oci` extension)

---

Contributing
------------

[](#contributing)

Contributions are welcome! Please follow these guidelines:

1. **Open an issue** first for new features or bug reports
2. **Include failing tests** that demonstrate the problem
3. **Follow PSR-12** coding standards
4. **Write tests** for all new functionality
5. **Test against all six dialects** (MySQL, MariaDB, PostgreSQL, SQLite, MSSQL, Oracle)

### Pull Request Process

[](#pull-request-process)

1. Fork the repository
2. Create a feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

---

License
-------

[](#license)

This project is open source. See [LICENSE](LICENSE) file for details.

---

Acknowledgments
---------------

[](#acknowledgments)

Inspired by [ThingEngineer/PHP-MySQLi-Database-Class](https://github.com/ThingEngineer/PHP-MySQLi-Database-Class) and [Yii2 framework](https://github.com/yiisoft/yii2-framework)

Built with ❤️ for the PHP community.

###  Health Score

65

—

FairBetter than 99% of packages

Maintenance89

Actively maintained with recent releases

Popularity39

Limited adoption so far

Community21

Small or concentrated contributor base

Maturity92

Battle-tested with a long release history

 Bus Factor1

Top contributor holds 99.1% 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

Every ~112 days

Recently: every ~28 days

Total

33

Last Release

58d ago

Major Versions

1.1.1 → v2.0.02025-10-10

PHP version history (2 changes)1.0.3PHP >=5.5.0

v2.0.0PHP ^8.4

### Community

Maintainers

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

---

Top Contributors

[![tommyknocker](https://avatars.githubusercontent.com/u/7640707?v=4)](https://github.com/tommyknocker "tommyknocker (545 commits)")[![baligena](https://avatars.githubusercontent.com/u/811053?v=4)](https://github.com/baligena "baligena (2 commits)")[![owenvoke](https://avatars.githubusercontent.com/u/1899334?v=4)](https://github.com/owenvoke "owenvoke (2 commits)")[![m4tlch](https://avatars.githubusercontent.com/u/3687188?v=4)](https://github.com/m4tlch "m4tlch (1 commits)")

---

Tags

active-recordai-analysisai-poweredctedatabasedatabase-abstraction-layerdatabase-adaptermariadbmssqlmysqlormpdophppostgresqlquery-buildershardingsqlsqlite3static-analysisvalidationsecuritydatabaseperformancedumpormmysqlsqlitepostgresqlmariadbdbalpdomssqloraclepsr-14paginationevent dispatcherload balancingpsr-16migrationsopenaiUser managementlightweightquery builderrestoreactiverecordtransactionssql serverSQL Injectionframework agnosticunionregexpphp8cross-platformRelationshipsanthropicAI-poweredfluent-apitype-safedeepseekai-analysisollamaddlupsertpdo\_ociseedsbatch processingctefull text searchcursor-paginationSQL Builderschema builderplugin-systemshardinginsert selecthelper-functionspdodbintersectprepared-statementscli-toolsdatabase-abstraction-layergoogle-aizero-dependenciesbulk-operationsconnection-poolingcsv-loaderxml-loadertable-lockingpdo-dbquery-cachingread-write-splittingschema-introspectionjson-loaderexport-helperswindow-functionscommon-table-expressionsrecursive-cteset-operationsexceptdistinct-onfilter-clausequery-macroslateral-joinquery-profilingquery-compilation-cachemerge-statementssql-formatterconnection-retryexplain-analysiscross-applyouter-applydialect-specific-typesupdate-joindelete-joinsavepointsquery-scopesschema-inspectorquery-monitoringerror-diagnosticsexception-hierarchyquery-debuggingyii2-inspiredmicrosoft-aiyandex-aidatabase-optimization-aiquery-optimization-aiai-database-assistant

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StylePHP CS Fixer

Type Coverage Yes

### Embed Badge

![Health badge](/badges/tommyknocker-pdo-database-class/health.svg)

```
[![Health](https://phpackages.com/badges/tommyknocker-pdo-database-class/health.svg)](https://phpackages.com/packages/tommyknocker-pdo-database-class)
```

###  Alternatives

[doctrine/dbal

Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.

9.7k578.4M5.6k](/packages/doctrine-dbal)[catfan/medoo

The lightweight PHP database framework to accelerate development

4.9k1.5M194](/packages/catfan-medoo)[cycle/database

DBAL, schema introspection, migration and pagination

64690.9k31](/packages/cycle-database)[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)