README

A simple, elegant database abstraction layer for MySQL/MariaDB, PostgreSQL, SQLite, and Microsoft SQL Server databases. Built with PHP 8.2+ features, providing a fluent API for common database operations without the complexity of full ORMs.

Features

• Multi-database support: MySQL/MariaDB, PostgreSQL, SQLite, and Microsoft SQL Server
• Simple API: Intuitive methods for CRUD operations
• Advanced filtering: Powerful filter syntax with support for operators, nested conditions, and array values
• Type-safe: Full PHP 8.2+ type declarations throughout
• Modern PHP: Built with enums, match expressions, readonly properties, and more
• Transaction support: Built-in batch transaction handling with automatic rollback
• Aggregation functions: Built-in support for COUNT, SUM, AVG, MIN, MAX
• SQL injection protection: Automatic escaping and prepared statements
• Table prefixing: Support for table name prefixes
• Column/table aliasing: Flexible column and table aliasing support

Requirements

• PHP >= 8.2
• PDO extension
• One of: ext-pdo_mysql, ext-pdo_pgsql, ext-pdo_sqlite, or ext-pdo_sqlsrv (depending on your database)

Installation

composer require vaibhavpandeyvpz/databoss

Quick Start

Basic Connection

<?php use Databoss\Connection; use Databoss\DatabaseDriver; // MySQL/MariaDB (default) $db = new Connection([ Connection::OPT_DATABASE => 'mydb', Connection::OPT_USERNAME => 'root', Connection::OPT_PASSWORD => 'password', ]); // PostgreSQL $db = new Connection([ Connection::OPT_DRIVER => DatabaseDriver::POSTGRES->value, Connection::OPT_DATABASE => 'mydb', Connection::OPT_USERNAME => 'postgres', Connection::OPT_PASSWORD => 'password', ]); // SQLite (file-based) $db = new Connection([ Connection::OPT_DRIVER => DatabaseDriver::SQLITE->value, Connection::OPT_DATABASE => '/path/to/database.sqlite', ]); // SQLite (in-memory) $db = new Connection([ Connection::OPT_DRIVER => DatabaseDriver::SQLITE->value, Connection::OPT_DATABASE => ':memory:', ]); // Microsoft SQL Server $db = new Connection([ Connection::OPT_DRIVER => DatabaseDriver::SQLSRV->value, Connection::OPT_HOST => 'localhost', Connection::OPT_PORT => 1433, Connection::OPT_DATABASE => 'mydb', Connection::OPT_USERNAME => 'sa', Connection::OPT_PASSWORD => 'YourStrong!Passw0rd', ]);

Basic CRUD Operations

// Insert $db->insert('users', [ 'name' => 'John Doe', 'email' => 'john@example.com', 'age' => 30, ]); // Select $users = $db->select('users'); $user = $db->first('users', ['id' => 1]); // Update $db->update('users', ['age' => 31], ['id' => 1]); // Delete $db->delete('users', ['id' => 1]); // Check if table exists if ($db->exists('users')) { // Table exists } // Check if records exist if ($db->exists('users', ['email' => 'john@example.com'])) { // User exists }

Advanced Filtering

Comparison Operators

// Greater than $db->select('products', '*', ['price{>}' => 100]); // Less than or equal $db->select('products', '*', ['price{<=}' => 50]); // Not equal $db->select('users', '*', ['status{!}' => 'inactive']); // LIKE $db->select('users', '*', ['name{~}' => '%John%']); // NOT LIKE $db->select('users', '*', ['email{!~}' => '%@spam.com']);

Array Values (IN/NOT IN)

// IN clause $db->select('products', '*', ['category' => ['electronics', 'books', 'clothing']]); // NOT IN clause $db->select('products', '*', ['category{!}' => ['discontinued', 'archived']]);

NULL Handling

// IS NULL $db->select('users', '*', ['deleted_at' => null]); // IS NOT NULL $db->select('users', '*', ['deleted_at{!}' => null]);

Nested Conditions (AND/OR)

// Complex nested conditions $db->select('products', '*', [ 'price{>}' => 100, 'OR' => [ 'category' => 'electronics', 'featured' => true, ], 'AND' => [ 'stock{>}' => 0, 'active' => true, ], ]);

Sorting

// Single column sort $db->select('users', '*', [], ['created_at' => 'DESC']); // Multiple column sort $db->select('products', '*', [], [ 'category' => 'ASC', 'price' => 'DESC', ]);

Pagination

// Limit results $db->select('users', '*', [], [], 10); // First 10 records // Limit with offset $db->select('users', '*', [], [], 10, 20); // Records 21-30 // Note: SQL Server requires ORDER BY when using OFFSET // The API will throw an InvalidArgumentException if OFFSET is used without ORDER BY on SQL Server $db->select('users', '*', [], ['id' => 'ASC'], 10, 20); // SQL Server requires sort parameter

Aggregation Functions

// Count $count = $db->count('users'); $activeCount = $db->count('users', '*', ['active' => true]); // Sum $totalRevenue = $db->sum('orders', 'amount', ['status' => 'completed']); // Average $avgAge = $db->average('users', 'age'); // Min/Max $oldestUser = $db->min('users', 'age'); $newestUser = $db->max('users', 'created_at'); // Generic math function (for custom aggregations) $customAgg = $db->math('users', 'AVG', 'age', ['active' => true]);

Transactions

// Execute multiple operations in a transaction $db->batch(function (ConnectionInterface $db) { $db->insert('orders', ['user_id' => 1, 'total' => 100]); $db->insert('order_items', ['order_id' => $db->id(), 'product_id' => 5]); // If any operation fails, all changes are rolled back });

Column Selection

// Select all columns $users = $db->select('users'); // Select specific column $emails = $db->select('users', 'email'); // Select multiple columns $users = $db->select('users', ['id', 'name', 'email']); // Select with aliases $users = $db->select('users', ['id', 'name{full_name}']);

Table and Column Aliasing

// Table alias $user = $db->first('users{u}', ['u.id' => 1]); // Column alias $result = $db->select('users{u}', ['id', 'u.name{user_name}'], ['id' => 1]);

Table Prefixing

$db = new Connection([ Connection::OPT_DATABASE => 'mydb', Connection::OPT_USERNAME => 'root', Connection::OPT_PREFIX => 'app_', ]); // This will query the 'app_users' table $users = $db->select('users');

DDL Operations

Create Database or Table

// Create the current database $db->create(); // Create a table $db->create('users', [ 'id' => [ 'type' => 'INTEGER', 'auto_increment' => true, 'primary' => true, ], 'name' => [ 'type' => 'VARCHAR(255)', 'null' => false, ], 'email' => [ 'type' => 'VARCHAR(255)', 'null' => true, ], ]); // Create table with explicit primary key $db->create('order_items', [ 'order_id' => ['type' => 'INTEGER', 'null' => false], 'product_id' => ['type' => 'INTEGER', 'null' => false], 'quantity' => ['type' => 'INTEGER', 'null' => false], ], ['order_id', 'product_id']);

Drop Database, Table, or Column

// Drop the current database $db->drop(); // Drop a table $db->drop('users'); // Drop a column $db->drop('users', 'email');

Modify Column

// Modify a column (MySQL, PostgreSQL, and SQL Server only, SQLite not supported) $db->modify('users', 'email', [ 'type' => 'VARCHAR(500)', 'null' => false, ]);

Indexes

// Create an index with auto-generated name $db->index('users', ['id', 'client_id']); // Create an index with custom name $db->index('users', ['id', 'client_id'], 'id_client_id_index'); // Create a unique index $db->unique('users', ['email'], 'unique_email'); // Drop an index by name $db->unindex('users', 'id_client_id_index'); // Drop an index by columns (auto-generates name) $db->unindex('users', ['id', 'client_id']);

Foreign Keys

// Create a foreign key with auto-generated name $db->foreign('users', 'client_id', ['clients', 'id']); // Create a foreign key with custom name $db->foreign('users', 'client_id', ['clients', 'id'], 'fk_users_client_id');

Note: DDL operations handle database-specific differences automatically:

• Column Type Translation: Common types are automatically translated to database-specific equivalents:

  • BOOLEAN → TINYINT(1) (MySQL), BOOLEAN (PostgreSQL), INTEGER (SQLite), BIT (SQL Server)
  • TEXT types → Appropriate text types for each database (NVARCHAR(MAX) for SQL Server)
  • BLOB/BYTEA → Corrected for each database (VARBINARY(MAX) for SQL Server)
  • SERIAL/BIGSERIAL → INT AUTO_INCREMENT (MySQL), SERIAL/BIGSERIAL (PostgreSQL), INTEGER (SQLite), INT IDENTITY(1,1) (SQL Server)
  • DECIMAL/NUMERIC → DECIMAL (MySQL/PostgreSQL/SQL Server), REAL (SQLite)
  • DATETIME → DATETIME (MySQL), TIMESTAMP (PostgreSQL), TEXT (SQLite), DATETIME2 (SQL Server)
  • JSON → JSON (MySQL/PostgreSQL), TEXT (SQLite), NVARCHAR(MAX) (SQL Server)
  • And many more...

• Auto-increment columns: MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL/BIGSERIAL, SQLite uses INTEGER PRIMARY KEY AUTOINCREMENT, SQL Server uses IDENTITY(1,1)

• MODIFY COLUMN: Not supported in SQLite (requires table recreation). Supported in MySQL, PostgreSQL, and SQL Server.

• Index syntax: Automatically adjusted for each database:

  • MySQL: DROP INDEX ... ON table
  • PostgreSQL/SQLite: DROP INDEX ...
  • SQL Server: DROP INDEX ... ON table

• Foreign keys: SQLite has limited support (requires PRAGMA foreign_keys = ON). Fully supported in MySQL, PostgreSQL, and SQL Server.

• ORDER BY with LIMIT/OFFSET: SQL Server requires ORDER BY when using OFFSET. The API enforces this requirement.

• IF NOT EXISTS/IF EXISTS: Handled automatically:

  • MySQL: Native IF NOT EXISTS/IF EXISTS support
  • PostgreSQL: IF NOT EXISTS for tables, exception handling for databases
  • SQLite: Native IF NOT EXISTS/IF EXISTS support
  • SQL Server: Uses IF OBJECT_ID checks for tables, sys.databases checks for databases

You can use common type names and they will be automatically translated. For example, 'type' => 'BOOLEAN' works across all databases without needing to know the database-specific type.

Raw SQL Queries

// Execute raw SQL $db->execute('UPDATE users SET last_login = NOW() WHERE id = ?', [1]); // Query with parameters $results = $db->query('SELECT * FROM users WHERE age > ? AND active = ?', [18, true]);

Connection Options

Using Array Options

$db = new Connection([ Connection::OPT_DRIVER => DatabaseDriver::MYSQL->value, Connection::OPT_HOST => 'localhost', Connection::OPT_PORT => 3306, Connection::OPT_DATABASE => 'mydb', Connection::OPT_USERNAME => 'root', Connection::OPT_PASSWORD => 'password', Connection::OPT_CHARSET => 'utf8mb4', Connection::OPT_PREFIX => 'app_', Connection::OPT_OPTIONS => [ \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, ], ]);

Using Option Builders (Recommended)

For a more fluent and type-safe approach, use the option builder classes:

MySQL/MariaDB

use Databoss\Connection; use Databoss\Options\MySqlOptions; $db = new Connection( (new MySqlOptions()) ->withHost('127.0.0.1') ->withPort(3306) ->withDatabase('mydb') ->withUsername('root') ->withPassword('password') ->withCharset('utf8mb4') ->withPrefix('app_') ->withPdoOptions([ \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, ]) ->toArray() );

PostgreSQL

use Databoss\Connection; use Databoss\Options\PostgresOptions; $db = new Connection( (new PostgresOptions()) ->withHost('127.0.0.1') ->withPort(5432) ->withDatabase('mydb') ->withUsername('postgres') ->withPassword('password') ->withCharset('utf8') ->withPrefix('app_') ->withPdoOptions([ \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, ]) ->toArray() );

SQLite

use Databoss\Connection; use Databoss\Options\SqliteOptions; // File-based database $db = new Connection( (new SqliteOptions()) ->withDatabase('/path/to/database.db') ->withPrefix('app_') ->withPdoOptions([ \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, ]) ->toArray() ); // In-memory database (default) $db = new Connection( (new SqliteOptions()) ->toArray() );

Microsoft SQL Server

use Databoss\Connection; use Databoss\Options\SqlsrvOptions; $db = new Connection( (new SqlsrvOptions()) ->withHost('127.0.0.1') ->withPort(1433) ->withDatabase('mydb') ->withUsername('sa') ->withPassword('YourStrong!Passw0rd') ->withCharset('UTF-8') ->withPrefix('app_') ->withPdoOptions([ \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ, \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, ]) ->toArray() ); // For test environments with self-signed certificates (e.g., Docker containers) // ODBC Driver 18+ requires TrustServerCertificate to be enabled $testDb = new Connection( (new SqlsrvOptions()) ->withHost('127.0.0.1') ->withPort(1433) ->withDatabase('testdb') ->withUsername('sa') ->withPassword('YourStrong!Passw0rd') ->withTrustServerCertificate() // Enable for test environments ->toArray() );

Filter Syntax Reference

Testing

The project includes Docker Compose configuration for running tests:

# Start database containers (MySQL, PostgreSQL, and SQL Server) docker compose up -d # Wait for databases to be ready, then run tests ./vendor/bin/phpunit # Run tests with coverage XDEBUG_MODE=coverage ./vendor/bin/phpunit --coverage-text # Stop database containers docker compose down

Note: SQL Server requires the ext-pdo_sqlsrv extension to be installed. On macOS, you can install it via PECL:

pecl install sqlsrv

Tests run against MySQL, PostgreSQL, SQLite, and SQL Server to ensure compatibility across all supported databases.

API Reference

Connection Methods

• average(string $table, string $column, array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Calculate average
• batch(callable $callback): mixed - Execute callback in transaction
• count(string $table, string $column = '*', array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Count records
• delete(string $table, array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Delete records
• escape(string $value, EscapeMode $mode = EscapeMode::VALUE): string|false - Escape value/identifier
• execute(string $sql, ?array $params = null): int|false - Execute raw SQL
• exists(string $table, array $filter = []): bool - Check if table exists (empty filter) or if records exist (with filter)
• first(string $table, array $filter = [], array $sort = [], int $start = 0): object|array|false - Get first record
• id(?string $sequence = null): string|false - Get last insert ID
• insert(string $table, array $values): int|false - Insert record
• max(string $table, string $column, array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Get maximum value
• min(string $table, string $column, array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Get minimum value
• math(string $table, string $operation, string $column = '*', array $filter = [], array $sort = [], int $start = 0, int $max = 0): int|false - Execute a mathematical aggregation function (AVG, COUNT, MAX, MIN, SUM)
• pdo(): \PDO - Get underlying PDO instance
• query(string $sql, array $params = []): array|false - Execute SELECT query
• select(string $table, array|string|null $columns = null, array $filter = [], array $sort = [], int $max = 0, int $start = 0): array|false - Select records
• sum(string $table, string $column, array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Calculate sum
• update(string $table, array $values, array $filter = [], array $sort = [], int $max = 0, int $start = 0): int|false - Update records
• create(?string $table = null, ?array $columns = null, ?array $primaryKey = null): bool - Create database or table
• drop(?string $table = null, ?string $column = null): bool - Drop database, table, or column
• modify(string $table, string $column, array $definition): bool - Modify a column (MySQL and PostgreSQL only)
• index(string $table, string|array $columns, ?string $indexName = null): bool - Create an index
• unique(string $table, string|array $columns, ?string $indexName = null): bool - Create a unique index
• foreign(string $table, string $column, array $references, ?string $constraintName = null): bool - Create a foreign key
• unindex(string $table, string|array $identifier): bool - Drop an index (by name or columns)

License

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