PHPackages                             jdz/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. jdz/database

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

jdz/database
============

Unified database abstraction layer supporting MySQL, MariaDB, PostgreSQL, and SQLite via PDO and MySQLi drivers

2.1.0(1w ago)0991MITPHPPHP >=8.2

Since Dec 13Pushed 1w ago1 watchersCompare

[ Source](https://github.com/joffreydemetz/database)[ Packagist](https://packagist.org/packages/jdz/database)[ Docs](https://jdz.joffreydemetz.com/database)[ RSS](/packages/jdz-database/feed)WikiDiscussions main Synced today

READMEChangelog (10)Dependencies (6)Versions (14)Used By (1)

JDZ Database
============

[](#jdz-database)

A developer-friendly database abstraction layer for PHP 8.2+ applications.

**Unified Interface** - Write once, run anywhere. Switch between MySQL, PostgreSQL, and SQLite without changing your code.

**Type-Safe** - Built with PHP 8.2+ features including enums, typed properties, and strict types for reliability.

**Developer-Friendly** - Fluent query builder, intuitive methods, and comprehensive error handling make database operations straightforward.

**Production-Ready** - Thoroughly tested with 200+ unit and integration tests covering all drivers and features.

**Flexible** - Use query builders for type safety or raw SQL when you need full control. Mix and match as needed.

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

[](#installation)

```
composer require jdz/database
```

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

[](#requirements)

- PHP 8.2 or higher

**Required Extensions (install as needed)**

- `ext-pdo` - PDO extension (core requirement for PDO-based drivers)
- `ext-pdo_mysql` - For MySQL/MariaDB via PDO
- `ext-pdo_pgsql` - For PostgreSQL support
- `ext-pdo_sqlite` - For SQLite support (usually built-in)
- `ext-mysqli` - For MySQL/MariaDB via MySQLi native driver

**Check Available Drivers:**The library includes methods to detect which drivers are available on your system.

Configuration Options
---------------------

[](#configuration-options)

### DSN String (recommended)

[](#dsn-string-recommended)

Instead of spelling out every connection field, pass a single Doctrine-style DSN string via the `dsn` key (or `DatabaseFactory::createFromDsn()`):

```
use JDZ\Database\DatabaseFactory;

// Inline via create()
$db = DatabaseFactory::create([
    'dsn'       => 'mysql://user:password@localhost:3306/mydb?charset=utf8mb4',
    'tblprefix' => 'app_',
]);

// Or the dedicated helper
$db = DatabaseFactory::createFromDsn('pgsql://user:password@localhost:5432/mydb');
```

**DSN format:** `driver://user:pass@host:port/dbname?charset=...&socket=...`

Supported schemes (and aliases):

Scheme(s)Resolves to`mysql`, `pdo-mysql`, `pdo_mysql`, `mysql2`MySQL (PDO)`mysqli`, `mariadb`MySQL/MariaDB (native MySQLi)`pgsql`, `pdo-pgsql`, `postgresql`, `postgres`PostgreSQL (PDO)`sqlite`, `sqlite3`, `pdo-sqlite`SQLite (PDO)```
mysql://root:secret@localhost:3306/app?charset=utf8mb4
pgsql://user:secret@db.example.com:5432/app
sqlite:///var/data/app.sqlite     # absolute file path
sqlite:///:memory:                # in-memory database

```

Notes:

- Explicit options passed alongside a DSN **override** the parsed values.
- Credentials are URL-decoded — percent-encode reserved characters in passwords (e.g. `p@ss` → `p%40ss`).
- Query-string parameters (`?charset=...`, `?socket=...`) are merged into the config.

In `config.php`, setting `database.dsn` makes it win over the per-driver blocks; leave it empty to fall back to the explicit `driver` + nested config.

### Common Options

[](#common-options)

- **dsn** - Doctrine-style DSN string (see above); when set it supplies the other options
- **driver** - Database type: mysql, mysqli, pgsql, sqlite, mariadb
- **tblprefix** - Table prefix for `#__` replacement (default: empty string)
- **driverOptions** - PDO-specific options array

### Connection Options (MySQL, PostgreSQL)

[](#connection-options-mysql-postgresql)

- **host** - Database server hostname (default: localhost)
- **port** - Server port (default: 3306 for MySQL, 5432 for PostgreSQL)
- **dbname** - Database name to connect to
- **user** - Database username
- **pass** - Database password
- **charset** - Character encoding (default: utf8mb4 for MySQL, utf8 for others)
- **socket** - Unix socket path (alternative to host/port)

### SQLite Options

[](#sqlite-options)

- **dbname** - Database file path or `:memory:` for in-memory database

### MySQLi Options

[](#mysqli-options)

- **sqlModes** - Array of SQL modes (STRICT\_TRANS\_TABLES, NO\_ZERO\_DATE, etc.)

Supported Database Drivers
--------------------------

[](#supported-database-drivers)

### PDO-Based Drivers

[](#pdo-based-drivers)

- **MySQL 5.7+** - Via PDO MySQL driver with full MySQL 8.0 support
- **MariaDB 10.3+** - Via PDO MySQL driver with automatic MariaDB detection
- **PostgreSQL 12+** - Via PDO PostgreSQL driver with PostgreSQL-specific features
- **SQLite 3+** - Via PDO SQLite driver for file-based and in-memory databases

### Native Drivers

[](#native-drivers)

- **MySQLi** - Native MySQL/MariaDB driver offering MySQL-specific optimizations like profiling and table locking

All drivers share the same interface, making it easy to switch between them or support multiple databases in the same application.

Core Features
-------------

[](#core-features)

### Database Operations

[](#database-operations)

- **Connection Management** - Automatic connection handling, reconnection support, and connection pooling
- **CRUD Operations** - Full support for Create, Read, Update, Delete with intuitive methods
- **Transaction Support** - BEGIN, COMMIT, ROLLBACK with savepoint support for PostgreSQL
- **Prepared Statements** - Automatic SQL injection protection through parameter binding
- **Multiple Result Formats** - Load as objects, arrays, single values, or custom classes

### Query Building

[](#query-building)

- **SelectQuery** - Build SELECT statements with WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT
- **InsertQuery** - INSERT with VALUES or SET syntax, INSERT IGNORE, multiple row insertion
- **UpdateQuery** - UPDATE with SET, WHERE, JOIN, ORDER BY, LIMIT
- **DeleteQuery** - DELETE with WHERE, JOIN, ORDER BY, LIMIT
- **UnionQuery** - Combine queries with UNION and UNION DISTINCT
- **StringQuery** - Use raw SQL with parameter binding when needed

### Advanced Features

[](#advanced-features)

- **Table Prefix Replacement** - Automatically replace `#__` placeholder with configured prefix
- **Driver Factory** - Create database instances from arrays or DSN strings
- **Database Introspection** - List tables, get columns, check existence
- **Type-Safe Enums** - FetchMode and ParamType enums for better IDE support
- **Error Handling** - Comprehensive exceptions for debugging
- **Metadata Access** - Database version, collation, table structures

How It Works
------------

[](#how-it-works)

### Database Factory Pattern

[](#database-factory-pattern)

The library uses a factory pattern to create database instances. You can instantiate databases from configuration arrays or DSN strings, with automatic driver selection based on your environment. The factory also provides methods to check available drivers on your system.

### Query Builder Architecture

[](#query-builder-architecture)

Instead of writing raw SQL strings, you can use dedicated query builder classes. Each query type (SELECT, INSERT, UPDATE, DELETE, UNION) has its own builder with methods corresponding to SQL clauses. Builders use method chaining for a fluent interface and maintain type safety through PHP's type system.

### Connection Management

[](#connection-management)

Database connections are lazy-loaded and managed automatically. The library handles connection state, automatic reconnection on failure, and proper resource cleanup. You can check connection status, manually connect/disconnect, and manage multiple database connections simultaneously.

### Parameter Binding

[](#parameter-binding)

All queries support parameter binding to prevent SQL injection. Named parameters (`:name`) and typed parameters are supported. The ParamType enum provides type hints (INT, STR, BOOL, NULL, LOB) for proper data handling across different database engines.

### Data Loading Methods

[](#data-loading-methods)

Results can be loaded in various formats:

- **loadObject()** - Single row as standard object or custom class instance
- **loadObjectList()** - Multiple rows as array of objects, optionally keyed by column
- **loadAssoc()** - Single row as associative array
- **loadAssocList()** - Multiple rows as associative arrays
- **loadColumn()** - Single column values as array
- **loadResult()** - Single scalar value (useful for COUNT, SUM, etc.)

### Table Prefix System

[](#table-prefix-system)

Use `#__` as a placeholder in table names, which gets replaced with your configured prefix. This allows writing portable code that works across different installations with different prefixes.

Query Builders
--------------

[](#query-builders)

***SelectQuery*** Builds SELECT statements with full SQL feature support. Chain methods to add columns, specify tables, add WHERE conditions, JOIN other tables, group results, filter groups with HAVING, order results, and limit/offset for pagination. Supports complex nested queries and all JOIN types (INNER, LEFT, RIGHT, CROSS).

***InsertQuery*** Constructs INSERT statements supporting two syntaxes: VALUES (traditional column/value pairs) and SET (key=value pairs). Handles single or multiple row insertion, INSERT IGNORE for duplicate handling, and parameter binding for all values. Returns insert ID after execution.

***UpdateQuery*** Builds UPDATE statements with SET clause for field updates. Supports WHERE conditions for targeting specific rows, JOIN for updating based on related tables, ORDER BY for controlling update order, and LIMIT for restricting update count. All values support parameter binding.

***DeleteQuery*** Creates DELETE statements with WHERE conditions for row targeting, JOIN support for deleting based on related tables, ORDER BY for deletion order control, and LIMIT for restricting deletion count. Ensures safe deletions through parameter binding.

***UnionQuery*** Combines multiple SELECT queries using UNION (remove duplicates) or UNION ALL (keep duplicates). Each subquery can be a SelectQuery object or raw SQL string. Supports ORDER BY and LIMIT on the combined result set. Useful for merging data from multiple tables or queries.

***StringQuery*** Wraps raw SQL strings when you need full control. Still supports parameter binding for security. Useful for complex queries, database-specific features, or when query builders don't cover your use case. Integrates seamlessly with the database instance.

Database Introspection
----------------------

[](#database-introspection)

The library provides methods to inspect database structure and metadata:

**Table Operations**

- List all tables in the database
- Check if a table exists before operations
- Drop tables with CASCADE support (PostgreSQL)
- Truncate tables to remove all data
- Rename tables (driver-dependent)
- Get CREATE TABLE statement (MySQL/MariaDB)

**Column Information**

- List all columns in a table
- Get column data types and properties
- Check for specific columns before queries

**Database Metadata**

- Database version (MySQL 8.0.34, PostgreSQL 15.2, etc.)
- Character set and collation information
- Database name and connection details

Transaction Handling
--------------------

[](#transaction-handling)

Transactions ensure data consistency across multiple operations. Start a transaction with `transactionStart()`, execute your queries, then either `transactionCommit()` to save changes or `transactionRollback()` to undo them. PostgreSQL supports savepoints for fine-grained control within transactions.

Examples
--------

[](#examples)

The `examples/` directory contains comprehensive, runnable examples demonstrating all library features.

### Run examples

[](#run-examples)

```
# Run all examples (requires the configured databases to be reachable)
composer examples

# Or run directly
php examples/run.php

# Run individual examples
php examples/query_builder_example.php
php examples/factory_example.php
php examples/mysql_example.php
php examples/postgresql_example.php
php examples/sqlite_example.php
```

### Notes

[](#notes)

- All examples use `#__` table prefix (replaced with `app_` by default)
- Examples automatically clean up created tables
- SQLite files are created in examples/ directory
- SQLite examples run out of the box; MySQL/PostgreSQL examples need a reachable server with the credentials in `config.php`

Testing
-------

[](#testing)

The library includes a comprehensive test suite with 200+ unit and integration tests covering all drivers and features.

### Running Tests

[](#running-tests)

```
# Run the full test suite
composer test
# Or
composer phpunit
# Or
vendor/bin/phpunit --colors=always

# Run unit tests only (fast, no database needed)
vendor/bin/phpunit --testsuite Unit

# Run integration tests (requires databases)
vendor/bin/phpunit --testsuite Integration

# Run specific test file
vendor/bin/phpunit tests/Unit/DatabaseFactoryTest.php
vendor/bin/phpunit tests/Integration/PdoSqliteDatabaseTest.php
```

Unit tests and the SQLite integration tests run with no setup. MySQL and PostgreSQL integration tests are skipped automatically unless a server with the configured credentials is reachable.

### Test Coverage

[](#test-coverage)

**Unit Tests** (no database required)

- **DatabaseFactoryTest** - Factory pattern and driver creation
- **FetchModeTest** - FetchMode enum validation
- **ParamTypeTest** - ParamType enum with fromString() conversion

**Query Builder Tests**

- **SelectQueryTest** - SELECT with WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT
- **InsertQueryTest** - INSERT with columns/values, SET syntax, IGNORE modifier
- **UpdateQueryTest** - UPDATE with SET, WHERE, JOIN, ORDER BY, LIMIT
- **DeleteQueryTest** - DELETE with WHERE, JOIN, ORDER BY, LIMIT
- **UnionQueryTest** - UNION ALL, UNION DISTINCT, ORDER BY, LIMIT
- **StringQueryTest** - Raw SQL with parameter binding **Integration Tests** (automatically skip if driver unavailable)
- **PdoDatabaseTest** - MySQL/MariaDB via PDO driver
- **MysqliDatabaseTest** - MySQL/MariaDB via MySQLi native driver
- **PdoPostgresqlDatabaseTest** - PostgreSQL-specific features
- **PdoSqliteDatabaseTest** - SQLite in-memory and file databases

### What's Tested

[](#whats-tested)

**Core Database Functionality**

- Connection management (connect, disconnect, reconnect)
- Query execution (SQL queries, prepared statements)
- Data operations (INSERT, UPDATE, DELETE, SELECT)
- Data retrieval (loadObject, loadObjectList, loadAssoc, loadColumn, loadResult)
- Transaction handling (start, commit, rollback)
- Parameter binding (named parameters, type hints)
- Table operations (create, drop, truncate, rename, exists)
- Table introspection (columns, metadata)
- Quote/escape functions

**Driver-Specific Features**

- **MySQL PDO**: Profiling, collation, standard operations
- **MySQLi**: Table locking, profiling, prepared statements
- **PostgreSQL**: Double-quoted identifiers, SERIAL primary keys, ALTER TABLE
- **SQLite**: In-memory databases, backtick identifiers, limited ALTER TABLE

**Factory & Configuration**

- DatabaseFactory creation from arrays
- DatabaseFactory creation from DSN strings
- Driver availability detection
- Environment variable configuration
- Error handling for unsupported drivers

License
-------

[](#license)

MIT License - Free for personal and commercial use. See [LICENSE](LICENSE) file for full text.

###  Health Score

47

—

FairBetter than 93% of packages

Maintenance98

Actively maintained with recent releases

Popularity11

Limited adoption so far

Community11

Small or concentrated contributor base

Maturity58

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

Every ~50 days

Recently: every ~32 days

Total

12

Last Release

13d ago

Major Versions

1.0.4 → 2.0.02025-12-30

PHP version history (2 changes)1.0.2PHP >=8.1

2.0.0PHP >=8.2

### Community

Maintainers

![](https://www.gravatar.com/avatar/5e83e3701566e43438525ed14578487e732b849d152b5071aa1613a0dad96913?d=identicon)[jdz](/maintainers/jdz)

---

Top Contributors

[![joffreydemetz](https://avatars.githubusercontent.com/u/15113527?v=4)](https://github.com/joffreydemetz "joffreydemetz (29 commits)")

---

Tags

databasemysqlsqlitepostgresqlmariadbpdodatabase abstractionmysqliquery builderJDZ

###  Code Quality

TestsPHPUnit

### Embed Badge

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

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

###  Alternatives

[doctrine/dbal

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

9.7k605.0M6.8k](/packages/doctrine-dbal)[ezsql/ezsql

Advance database access library. Make interacting with a database ridiculously easy. An universal interchangeable CRUD system.

86849.1k](/packages/ezsql-ezsql)[jv2222/ezsql

Advance database access library. Make interacting with a database ridiculously easy. An universal interchangeable CRUD system.

87211.6k2](/packages/jv2222-ezsql)[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.

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

A Laravel package for enjoyably managing database queries.

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

PHPackages © 2026

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