morris/dop

An immutable API on top of PDO to compose and execute SQL statements

DOP

, _(*1)

DOP is an immutable API on top of PDO to compose and execute SQL statements., _(*2)

• Extended parameters (::param and ??) allow binding to arbitrary values like arrays, null and SQL fragments.
• Provides helpers for writing common queries, e.g. selects, inserts, updates, deletes.
• Tested with SQLite, PostgreSQL, and MySQL.

Installation

DOP requires PHP >= 5.3.0 and PDO. Install via composer:, _(*3)

composer require morris/dop

Usage

// Connect to a database
$pdo = new PDO('sqlite:blog.sqlite3');
$dop = new Dop\Connection($pdo);

// Find posts by author IDs using DOP parametrization
$authorIds = [1, 2, 3];
$orderByTitle = $dop('ORDER BY title ASC');
$posts = $dop(
    'SELECT * FROM post WHERE author_id IN (??) ??',
    [$authorIds, $orderByTitle]
)->fetchAll();

// Find published posts using DOP helpers for common queries
$posts = $dop->query('post')->where('is_published = ?', [1])->fetchAll();

// Get categorizations of posts using DOP's map function
$categorizations = $dop(
    'SELECT * FROM categorization WHERE post_id IN (??)',
    [$dop->map($posts, 'id')]
)->fetchAll();

// Find posts with more than 3 categorizations using a sub-query as a parameter
$catCount = $dop('SELECT COUNT(*) FROM categorization WHERE post_id = post.id');
$posts = $dop(
    'SELECT * FROM post WHERE (::catCount) >= 3',
    ['catCount' => $catCount]
)->fetchAll();

Internally, ?? and ::named parameters are resolved before statement preparation. Note that due to the current implementation using regular expressions, you should never use quoted strings directly. Always use bound parameters., _(*4)

Reference

See API.md for a complete API reference., _(*5)

Contributors

• jayaddison

Thanks!, _(*6)