EasyDB Documentation

To install EasyDB, use Composer to require the paragonie/easydb package version ^3.

composer require paragonie/easydb:^3

The EasyStatement class provides a fluent interface for building complex SQL WHERE clauses safely. It allows you to chain conditions using logical AND and OR operators, handle nested groups, and manage parameter binding to prevent SQL injection.

To start building a statement, use the static EasyStatement::open() method. You can then append conditions using andWith() (or its alias with()) and orWith(). To compile the statement into a raw SQL string for PDO, call sql() or cast the object to a string.

use ParagonIE//EasyDB

$statement = EasyStatement::open()
    ->andWith('status = ?', 'active')
    ->orWith('role = ?', 'admin');

echo $statement->sql(); // "status = ? AND role = ?" (Note: actual logic depends on internal joiner state)
// To get values for PDO execution:
$params = $statement->values(); // ['active', 'admin']

You can create nested logical groupings (e.g., WHERE (a = 1 OR b = 2) AND c = 3) using andGroup() and orGroup().

• andGroup() starts a new sub-statement that will be joined to the parent with AND.
• orGroup() starts a new sub-statement that will be joined to the parent with OR.
• end() (or endGroup()) exits the current group and returns the context to the parent statement.

If you call end() when there is no active group, a RuntimeException is thrown.

$statement = EasyStatement::open()
    ->andWith('active = ?', 1)
    ->andGroup()
        ->orWith('type = ?', 'A')
        ->orWith('type = ?', 'B')
    ->end();

echo $statement->sql(); // "active = ? AND (type = ? OR type = ?)"

To use EasyDB, instantiate the EasyDB class by passing an existing PDO instance. You can optionally specify the dbEngine (e.g., 'mysql', 'sqlite', 'pgsql', 'mssql') and an options array. If dbEngine is omitted, EasyDB will automatically detect it from the PDO driver name.

Note: The constructor automatically sets PDO::ATTR_EMULATE_PREPARES to false and PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION to ensure security and proper error handling.

$pdo = new PDO('sqlite:./database.db');
$db = new EasyDB($pdo); // Engine detected automatically
// OR
$db = new EasyDB($pdo, 'mysql', [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

Use delete() to remove rows. Pass the table name and an associative array of conditions for the WHERE clause.

$db->delete('comments', [
    'userid' => 3
]);

Use tryFlatTransaction() to execute a block of code within a transaction. The callback receives the EasyDB instance. The transaction will automatically commit if the callback returns successfully or roll back if an exception is thrown.

$save = function (\EasyDB\EasyDB $db) use ($userData, $query) : int {
    $db->safeQuery($query, [$userData['userId']]);
    return \Some\Other\Package::CleanUpTable($db);
};

$returnedInt = $db->tryFlatTransaction($save);

If you need to perform operations not covered by EasyDB, you can retrieve the underlying PDO instance using getPdo().

$pdo = $db->getPdo();

Use cell() to fetch a single value (like a count or a specific column) from a single row. cell() is variadic. If you prefer passing an array of parameters, use single() instead.

// Using cell() with variadic arguments
$exists = $db->cell(
    "SELECT count(id) FROM users WHERE email = ? AND username = ?",
    $_POST['email'],
    $_POST['username']
);

// Using single() with an array of arguments
$exists = $db->single(
    "SELECT count(id) FROM users WHERE email = ?",
    array($_POST['email'])
);

The easiest way to create an EasyDB instance is using \ParagonIE\EasyDB\Factory::fromArray(). You pass an array containing the PDO DSN, username, and password.

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

When using insert() or update(), you can use EasyPlaceholder to include SQL functions (like NOW()) or complex expressions that should not be treated as literal values to be escaped.

use \ParagonIE\EasyDB\EasyPlaceholder;

$db->insert('user_auth', [
    'user_id' => 1,
    'timestamp' => new EasyPlaceholder('NOW()'),
    'location' => new EasyPlaceholder(
        "ST_GeomFromText(CONCAT('POINT(', ?, ' ', ?, ')'))",
        50.4019514,
        39.0639
    )
]);

Use insert() to add a new row. Pass the table name and an associative array where keys are column names and values are the data to insert.

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