Muhammad Usman

Crazy tech lover, endless learner
Home

Pixie - A Database Query Builder for PHP

It took me few weeks to build Pixie, a lightweight, expressive, framework agnostic query builder for PHP, it can also be referred as a Database Abstraction Layer. Pixie supports MySQL, SQLite and PostgreSQL and it takes care of query sanitization, table prefixing and many other things with a unified API. At least PHP 5.3 is required.

It has some advanced features like:

  • Query Events
  • Nested Criteria
  • Sub Queries
  • Nested Queries
  • Multiple Database Connections.

Check out the installation instructions and documentation on GitHub.

Install or View on GitHub

Example

   
// Make sure you have Composer's autoload file included  
require 'vendor/autoload.php';

// Create a connection, once only.  
$config = array(  
    'driver' => 'mysql', // Db driver  
    'host' => 'localhost',  
    'database' => 'your-database',  
    'username' => 'root',  
    'password' => 'your-password',  
    'charset' => 'utf8', // Optional  
    'collation' => 'utf8_unicode_ci', // Optional  
    'prefix' => 'cb_', // Table prefix, optional  
);

new \Pixie\Connection('mysql', $config, 'QB');  

Simple Query:

The query below returns the row where id = 3, null if no rows.

   
$row = QB::table('my_table')->find(3);  

Full Queries:

   
$query = QB::table('my_table')->where('name', '=', 'Sana');

// Get result  
$query->get();  

Query Events:

After the code below, every time a select query occurs on users table, it will add this where criteria, so banned users don’t get access.

   
QB::registerEvent('before-select', 'users', function($qb)  
{  
    $qb->where('status', '!=', 'banned');  
});  

There are many advanced features which are documented on GitHub project page. Sold? Let’s install.