EasyDB - Simple Database Abstraction Layer

PDO lacks brevity and simplicity; EasyDB makes separating data from instructions easy (and aesthetically pleasing).

EasyDB was created by Paragon Initiative Enterprises as part of our effort to encourage better application security practices.

Check out our other open source projects too.

If you're looking for a full-fledged query builder, check out Latitude and Aura.SqlQuery, which can be used with EasyDB.

Installing EasyDB

First, get Composer, if you don't already use it.

Next, run the following command:

/path/to/your/local/composer.phar require paragonie/easydb:^2

If you've installed Composer in /usr/bin, you can replace /path/to/your/local/composer.phar with just composer.

Why Use EasyDB? Because it's cleaner!

Let's refactor a dangerous PHP snippet that previously used string concatenation to pass user input instead of prepared statements. For example, imagine something that just dropped {$_GET['blogpostid']} into the middle of a mysql_query() statement. Let's make it secure.

The PDO Way

$db = new \PDO(
    'mysql:host=localhost;dbname=something',
    'username',
    'putastrongpasswordhere'
);

$statement = $db->prepare('SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC');
$exec = $statement->execute([$_GET['blogpostid']]);
$rows = $exec->fetchAll(\PDO::FETCH_ASSOC);
foreach ($rows as $row) {
    $template_engine->render('comment', $row);
}

That's a little wordy for such a simple task. If we do this in multiple places, we end up repeating ourselves a lot.

The EasyDB Solution

$db = \ParagonIE\EasyDB\Factory::create(
    'mysql:host=localhost;dbname=something',
    'username',
    'putastrongpasswordhere'
);

$rows = $db->run('SELECT * FROM comments WHERE blogpostid = ? ORDER BY created ASC', $_GET['blogpostid']);
foreach ($rows as $row) {
    $template_engine->render('comment', $row);
}

We made it a one-liner.

What else can EasyDB do quickly?

Insert a row into a database table

$db->insert('comments', [
    'blogpostid' => $_POST['blogpost'],
    'userid' => $_SESSION['user'],
    'comment' => $_POST['body'],
    'parent' => isset($_POST['replyTo']) ? $_POST['replyTo'] : null
]);

Build an insert without executing

$sql = $db->buildInsertQuery('comments', [
    'blogpostid',
    'userid',
    'comment'
]);

// INSERT INTO comments (blogpostid, userid, comment) VALUES (?, ?, ?)

$result = $db->q(
    $sql,
    $values,
    \PDO::FETCH_BOTH,
    true
);

Update a row from a database table

$db->update('comments', [
    'approved' => true
], [
    'commentid' => $_POST['comment']
]);

Delete a row from a database table

// Delete all of this user's comments
$db->delete('comments', [
    'userid' => 3
]);

Fetch a single row from a table

$userData = $db->row(
    "SELECT * FROM users WHERE userid = ?",
    $_GET['userid']
);

Fetch a single column from a single row from a table

$exists = $db->cell(
    "SELECT count(id) FROM users WHERE email = ?",
    $_POST['email']
);
/* OR YOU CAN CALL IT THIS WAY: */
$exists = $db->single(
    "SELECT count(id) FROM users WHERE email = ?", 
    array(
        $_POST['email'] 
    )
);

Try to perform a transaction

$save = function (EasyDB $db) use ($userData, $query) {
    $db->safeQuery($query, [$userData['userId']]);
    \Some\Other\Package::CleanUpTable($db);
};
// auto starts, commits and rolls back a transaction as necessary
$db->tryFlatTransaction($save);

Generate dynamic query conditions

$statement = EasyStatement::open()
    ->with('last_login IS NOT NULL');

if (strpos($_POST['search'], '@') !== false) {
    // Perform a username search
    $statement->orWith('username LIKE ?', '%' . $db->escapeLikeValue($_POST['search']) . '%');
} else {
    // Perform an email search
    $statement->orWith('email = ?', $_POST['search']);
}

// The statement can compile itself to a string with placeholders:
echo $statement; /* last_login IS NOT NULL OR username LIKE ? */

// All the values passed to the statement are captured and can be used for querying:
$user = $db->single("SELECT * FROM users WHERE $statement", $statement->values());

Note: Passing values with conditions is entirely optional but recommended.

Variable number of "IN" arguments

// Statements also handle translation for IN conditions with variable arguments,
// using a special ?* placeholder:
$roles = [1];
if ($_GET['with_managers']) {
    $roles[] = 2;
}

$statement = EasyStatement::open()->in('role IN (?*)', $roles);

// The ?* placeholder is replaced by the correct number of ? placeholders:
echo $statement; /* role IN (?, ?) */

// And the values will be unpacked accordingly:
print_r($statement->values()); /* [1, 2] */

Grouping of conditions

// Statements can also be grouped when necessary:
$statement = EasyStatement::open()
    ->group()
        ->with('subtotal > ?')
        ->andWith('taxes > ?')
    ->end()
    ->orGroup()
        ->with('cost > ?')
        ->andWith('cancelled = 1')
    ->end();

echo $statement; /* (subtotal > ? AND taxes > ?) OR (cost > ? AND cancelled = 1) */

What if I need PDO for something specific?

$pdo = $db->getPdo();

Can I create an EasyDB wrapper for an existing PDO instance?

Yes! It's as simple as doing this:

$easy = new \ParagonIE\EasyDB\EasyDB($pdo, 'mysql');

How do I run tests ?

vendor/bin/phpunit

henrywood/easydb