PowerLite PDO: A Powerful PHP Database Abstraction Layer

PowerLite PDO is a PHP library that provides an efficient way to interact with multiple types of databases

Why this project?

PHP's PDO (PHP Data Objects) extension is a powerful tool for interacting with databases. However, its syntax can be verbose and cumbersome, especially for complex queries. While popular PHP frameworks like Laravel and Symfony offer their own Database Abstraction Layers (DBAL), these are often tightly coupled with the rest of the framework. If you're not using these frameworks, or if you prefer a more lightweight solution, you're left to deal with PDO's verbosity.

There are many standalone DBAL packages available, but finding a high-quality, well-maintained library can be a challenge. That's where PowerLite PDO comes in.

Introducing PowerLite PDO

PowerLite PDO is a PHP library that provides a lightweight and powerful abstraction layer for PDO. It's designed with a clear separation of concerns in mind, and it uses Dependency Injection (DI) to manage dependencies between different parts of the system.

The library is organized into three main components:

• Db: The main interface for interacting with the database. It handles connection management, query execution, and result processing.
• QueryBuilder: Used to build SQL queries in a flexible and extensible way.
• Pagination: Handles pagination of query results.

Getting Started

To start using PowerLite PDO, you first need to enter your database connection parameters in the connection.php file. Then, you load the Dependency Injection (DI) container, which will provide you with instances of Db, QueryBuilder, and Pagination.

use Migliori\PowerLitePdo\Db;

$container = require_once __DIR__ . '/../src/bootstrap.php';

$db = $container->get(Db::class);

Now you can start using the Db, QueryBuilder, and Pagination classes to interact with your database.

Using the Db Class

The Db class is your main interface with the database. Here's a simple example of how to use it:

use Migliori\PowerLitePdo\Db;

// Load the DI container
$container = require_once __DIR__ . '/../src/bootstrap.php';

// Get an instance of the Db class from the container
$db = $container->get(Db::class);

// Set up the query parameters
$from = 'users'; // The table name
$fields = ['id', 'name', 'email']; // The columns you want to select
$where = ['status' => 'active']; // The conditions for the WHERE clause

// Execute the query
$db->select($from, $fields, $where);

// Fetch the results
$records = [];
while ($record = $db->fetch()) {
    $records[] = $record->id . ', ' . $record->name . ', ' . $record->email;
}

The Db class has taken care of preparing and executing the query in a safe and efficient way, using PDO's prepared statements. It has also fetched the results and returned them to you in a convenient format.

Others methods are available in the Db class for executing different types of queries, such as insert, update, and delete.

Here are a few quick examples:

// Insert a new record
$db->insert('users', ['name' => 'John Doe', 'email' => '[e-mail]']);

// Update an existing record with id = 1
$db->update('users', ['email' => '[e-mail]'], ['id' => 1]);

// Delete a record with id = 1
$db->delete('users', ['id' => 1]);

Building Queries with QueryBuilder

The QueryBuilder class provides another way to build and execute queries, for those who prefer a fluent and flexible syntax.

Here's an example of how to use the QueryBuilder class:

use Migliori\PowerLitePdo\QueryBuilder;

// Load the DI container
$container = require_once __DIR__ . '/../src/bootstrap.php';

// Get an instance of the QueryBuilder class from the container
$queryBuilder = $container->get(QueryBuilder::class);

// Build and execute a SELECT query
$queryBuilder->select(['id', 'name', 'email'])
    ->from('users')
    ->where('status', 'active')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->offset(0)
    ->execute();

// Fetch the results
$records = [];
while ($record = $queryBuilder->fetch()) {
    $records[] = $record->id . ', ' . $record->name . ', ' . $record->email;
}

Paginating Results with Pagination

The Pagination class helps you paginate the results of your queries. Here's for instance how you can use it:

use Migliori\PowerLitePdo\Db;

$container = require_once __DIR__ . '/../src/bootstrap.php';

$pagination = $container->get(Pagination::class);

// Set the pagination options for URL rewriting
$pagination->setOptions([
    'querystring' => '',
    'rewriteLinks' => true,
    'rewriteTransition' => '-',
    'rewriteExtension' => '.html'
]);

$from = 'users'; // The SELECT FROM clause
$fields = ['id', 'username', 'email']; // The columns you want to select
$where = ['status' => 'active']; // The conditions for the WHERE clause

$pagination->select($from, $fields, $where);

$records2 = [];
while ($record = $db->fetch()) {
    $records2[] = $record->id . ', ' . $record->username . ', ' . $record->email;
}

$url = '/examples/pagination-examples'; // The URL for the pagination links
echo $pagination->pagine($url);

The Pagination class will take care of paginating the results and generating the pagination links for you.

It generates the HTML markup for the pagination links, which you can then output in your view. Of course, you can customize the pagination markup (which is built with a simple unordered list) to fit your needs.

Key Features of PowerLite PDO

The examples above show just a few of the features of PowerLite PDO, but there's much more you can do with this library.

Here are some of the main features:

• Dependency Injection (DI): The library uses DI to manage dependencies between different parts of the system, making it easy to swap out components or extend functionality.

• Safe Query Execution: The Db class uses PDO's prepared statements to execute queries safely, protecting against SQL injection attacks.

• Query Building: The QueryBuilder class provides a fluent and flexible way to build SQL queries, making it easy to construct complex queries with ease.

• Pagination: The Pagination class helps you paginate the results of your queries, generating pagination links and handling the logic of fetching the correct subset of results.

• Customization: The library is designed to be extensible and customizable, so you can easily add new features or modify existing ones to fit your needs.

• Performance: PowerLite PDO is designed for performance, with efficient query execution and result processing to minimize overhead and maximize speed.

• Security: The library follows best practices for database security, using prepared statements and other techniques to protect against common security vulnerabilities.

• Flexibility: The library is designed to be flexible and adaptable, so you can use it with different types of databases and adapt it to your specific requirements.

• Ease of Use: The library is designed to be easy to use, with a simple and intuitive API that makes it easy to get started and start building queries right away.

• Debugging: The library provides a global and a local debug mode that allow to see the queries that are being executed and the parameters that are being passed to them.

• Error Handling: The library provides detailed error messages and exceptions to help you debug and troubleshoot any issues that arise.

• Documentation: The library comes with comprehensive documentation and examples to help you get started and learn how to use its features effectively.

• Active Development: The library is actively maintained and updated, with new features and improvements added regularly.

Learn More

For more detailed information and examples, visit the PowerLite PDO website and check out the GitHub repository.

Comments

[david duymelinck]

I appreciate the work you did. I would like to see how your package compares to other DBAL packages.

I use Doctrine DBAL as an example.

I saw your package has firebird and OCI platforms out of the box.

I saw your package has DriverManager, but no documentation on how to create a new driver.

I hope you keep doing the work, and create great things.

[Gilles Migliori]

The PowerLite PDO project offers a lightweight, easy-to-use database abstraction layer for PHP applications. Doctrine DBAL can be more complex and heavy for simple projects.

I started working on this project a few years ago. I was looking for a high-quality DBAL that met certain specific needs, such as being able to switch to a debugging mode that displays details of SQL queries with their parameters and the results obtained, or being able to save local and production connection information in an external configuration file.

I also needed a tool whose code I could control, so that I could adapt it to my needs and make it evolve accordingly.

PowerLite PDO is much lighter than Doctrine DBAL, it's easier to customise, and makes it easier to manage special cases. If I have a problem, I want to be able to solve it quickly, without having to open an issue on GitHub and wait for an answer.

Concerning the Driver Manager:

Each driver class implements the abstract DriverBase class. To add a driver, simply create its class and add it to the list of available drivers in the Driver Manager.

It's actually a good idea to add the instructions to the documentation, I'm going to do it.

Thanks for your comment, and don't hesitate to let me know if you have any suggestions or comments.