Download the PHP package tommyknocker/pdo-database-class without Composer

PDOdb

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 and Yii2 framework

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

• Requirements
• Installation
• 📖 Documentation
• 📚 Examples
• Quick Example
• 5-Minute Tutorial
• Configuration
• Quick Start
  • AI-Powered Query Analysis
• JSON Operations
• Advanced Usage
• CLI Tools
• Error Handling
• Performance Tips
• Helper Functions
• API Reference
• Dialect Differences
• Frequently Asked Questions
• Migration Guide
• Troubleshooting
• Testing
• Contributing
• License

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:

Installation

Install via Composer:

For specific versions:

Quick Setup with pdodb init

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

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 for more details.

📖 Documentation

Complete documentation is available in the documentation/ directory with 56+ detailed guides covering all features:

• Getting Started - Installation, configuration, your first connection
• Core Concepts - Connection management, query builder, parameter binding, dialects
• Query Builder - SELECT, DML, filtering, joins, aggregations, subqueries
• JSON Operations - Working with JSON across all databases
• Advanced Features - Transactions, batch processing, bulk operations, UPSERT, query scopes, query macros, plugin system
• Error Handling - Exception hierarchy, enhanced error diagnostics with query context, retry logic, logging, monitoring
• Helper Functions - Complete reference for all helper functions
• Best Practices - Security, performance, memory management, code organization
• API Reference - Complete API documentation
• 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

📚 Examples

Comprehensive, runnable examples are available in the examples/ directory:

• Basic - Connection, CRUD, WHERE conditions
• Intermediate - JOINs, aggregations, pagination, transactions, savepoints
• Advanced - Connection pooling, bulk operations, UPSERT, subqueries, MERGE, window functions, CTEs
• JSON Operations - Complete guide to JSON features
• Helper Functions - String, math, date/time, NULL, comparison helpers
• Performance - Query caching, compilation cache, profiling, EXPLAIN analysis
• ActiveRecord - Object-based operations, relationships, scopes

Each example is self-contained with setup instructions. See examples/README.md for the full catalog.

Quick start:

Quick Example

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

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

5-Minute Tutorial

Step 1: Install

Step 2: Initialize Project

Use the interactive wizard:

Alternative: Manual configuration

Step 3: Create Table

Step 4: CRUD Operations

Step 5: Monitor Your Database

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

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

Next: See Quick Start for more examples.

Configuration

Basic Configuration

Advanced Configuration

Connection Pooling:

Read/Write Splitting:

Query Caching:

See Configuration Documentation for complete configuration options.

Quick Start

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

🤖 AI-Powered Query Analysis

Get intelligent optimization recommendations for your queries using AI:

Or use CLI:

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

Model Selection: Configure models via environment variables (PDODB_AI_<PROVIDER>_MODEL) or config array (ai.providers.<provider>.model)

See AI Analysis Documentation for complete guide.

Basic CRUD Operations

Filtering and Joining

Transactions

See Query Builder Documentation for more examples.

JSON Operations

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

See JSON Operations Documentation for complete guide.

Advanced Usage

Raw Queries

Subqueries

Pagination

Batch Processing

Query Caching

See Advanced Features Documentation for complete guide.

CLI Tools

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

🖥️ Interactive TUI Dashboard

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

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

• 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

Install bash completion for enhanced CLI experience:

Examples

See CLI Tools Documentation for complete guide.

Error Handling

PDOdb provides a comprehensive exception hierarchy for better error handling:

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

See Error Handling Documentation for complete guide.

Performance Tips

Enable Query Caching

For applications with repeated queries, enable result caching:

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

Use Batch Operations

Always Limit Result Sets

Use Batch Processing for Large Datasets

See Performance Documentation for more tips.

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 for complete list and examples.

API Reference

PdoDb Main Class

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 for complete method list and signatures.

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:

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 for complete guide.

Frequently Asked Questions

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?

Yes! Use rawQuery() for complete control:

Does it work with frameworks?

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

Is it production-ready?

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

What about security?

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

Can I use it with existing PDO connections?

Yes! Pass your PDO instance:

Which database should I use for development?

SQLite is perfect for development - no server setup needed:

Does it support transactions?

Yes! Full transaction support with savepoints:

Migration Guide

From Raw PDO

Before:

After:

From Eloquent (Laravel)

Before:

After:

See Migration Guide Documentation for more examples.

Troubleshooting

"Driver not found" Error

Solution: Install the required PHP extension:

"JSON functions not available" (SQLite)

Solution: Check if JSON support is available:

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

Problem: Using OFFSET without LIMIT in SQLite.

Solution: Always use LIMIT with OFFSET:

Memory Issues with Large Result Sets

Solution: Use batch processing or streaming:

See Troubleshooting Documentation for more solutions.

Testing

The project includes comprehensive PHPUnit tests for all supported databases.

Running Tests

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

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

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

This project is open source. See LICENSE file for details.

Acknowledgments

Inspired by ThingEngineer/PHP-MySQLi-Database-Class and Yii2 framework

Built with ❤️ for the PHP community.