README

PdoDb is a lightweight, production‑ready database abstraction layer for PHP's PDO extension. It features a secure query builder, statement caching, nested transactions and support for MySQL, MariaDB, PostgreSQL, SQLite and SQL Server.

Installation

Install via Composer:

composer require lakshanjs/pdodb

Getting started

Simple initialization

use LakshanJS\PdoDb\PdoDb;

$db = new PdoDb('host', 'username', 'password', 'databaseName');

Advanced initialization

$db = new PdoDb([
    'host'     => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'       => 'databaseName',
    'port'     => 3306,
    'prefix'   => 'my_',
    'charset'  => 'utf8mb4',
]);

Table prefix, port and charset are optional. To skip setting a charset, set charset to null.

Selecting the driver

PdoDb uses the MySQL driver by default. To connect to other databases, specify the driver as the final constructor argument or via a driver configuration key. Supported drivers are mysql, pgsql, sqlite and sqlsrv.

// Pass driver as the last parameter
$db = new PdoDb('host', 'username', 'password', 'databaseName', 3306, 'utf8mb4', 'pgsql');

// Or define it in the configuration array
$db = new PdoDb([
    'host'     => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'       => 'databaseName',
    'driver'   => 'sqlsrv',
]);

Reuse existing PDO connection

$pdo = new PDO('mysql:host=host;dbname=databaseName', 'username', 'password');
$db  = new PdoDb($pdo);

Setting prefix later

$db->setPrefix('my_');

Auto reconnect

If the connection is dropped PdoDb will attempt to reconnect by default. Disable this behaviour via:

$db->autoReconnect = false;

Getting the instance elsewhere

function init()
{
    // db stays private here
    $db = new PdoDb('host', 'user', 'pass', 'db');
}

function myFunc()
{
    // obtain the instance created in init()
    $db = PdoDb::getInstance();
}

Multiple database connections

$db->addConnection('slave', [
    'host'     => 'host',
    'username' => 'username',
    'password' => 'password',
    'db'       => 'databaseName',
    'port'     => 3306,
    'prefix'   => 'my_',
    'charset'  => 'utf8mb4',
]);

$users = $db->connection('slave')->get('users');

Quick example

The snippet below demonstrates several common operations—filtering, joins, inserts, updates and transactions—in one place:

require 'vendor/autoload.php';

use LakshanJS\PdoDb\PdoDb;

$db = new PdoDb([
    'host'     => 'localhost',
    'db'       => 'pdodb_test',
    'username' => 'root',
    'password' => 'secret',
    'charset'  => 'utf8mb4',
]);

// fetch active users
$activeUsers = $db->where('status', 'active')->get('users');

// join with profiles table
$usersWithProfiles = $db
    ->join('profiles p', 'u.id = p.user_id', 'INNER')
    ->get('users u', null, ['u.email', 'p.full_name']);

// insert a user and update counters
$userId = $db->insert('users', [
    'email'      => 'test@example.com',
    'login'      => 'testuser',
    'created_at' => $db->now(),
]);

$db->where('id', $userId)->update('users', [
    'views'      => $db->inc(),
    'last_login' => $db->now(),
]);

// simple transaction
$db->startTransaction();
try {
    $db->insert('log', ['msg' => 'demo', 'created_at' => $db->now()]);
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
}

Result formats

Return types can be adjusted per query:

$db->jsonBuilder()->get('users');
$db->arrayBuilder()->get('users');
$db->objectBuilder()->get('users');

Running queries

Selecting data

$users = $db->get('users');
$user  = $db->getOne('users');
$count = $db->getValue('users', 'count(*)');
$exists = $db->where('id', 1)->has('users');
$top   = $db->withTotalCount()->get('users', [0, 10]);
echo $db->totalCount; // total rows matching the query

Inserting data

$id = $db->insert('users', ['login' => 'admin']);
$ids = $db->insertMulti('users', [
    ['login' => 'a'],
    ['login' => 'b']
]);
$db->onDuplicate(['lastLogin' => $db->now()]);
$db->insert('users', ['id' => 1, 'login' => 'admin']);
$db->replace('users', ['id' => 1, 'login' => 'admin']);

Updating data

$db->where('id', 1)->update('users', [
    'views' => $db->inc(),
    'expires' => $db->now('+1 day')
]);

Deleting data

$db->where('id', 1)->delete('users');

Raw queries

$rows  = $db->rawQuery('SELECT * FROM users WHERE id = ?', [1]);
$row   = $db->rawQueryOne('SELECT * FROM users WHERE id = ?', [1]);
$value = $db->rawQueryValue('SELECT count(*) FROM users');
$db->setQueryOption('SQL_NO_CACHE')->query('SELECT * FROM users');

Conditions

$db->where('age', 18, '>')->orWhere('status', 'guest');
$db->whereRaw('JSON_CONTAINS(tags, ?)', ['"php"']);
$db->having('cnt', 5, '>')->orHaving('cnt', 10, '<');
$db->havingRaw('SUM(score) > ?', [100]);

Joins

$db->join('profiles p', 'u.id = p.user_id', 'LEFT');
$db->joinWhere('profiles p', 'p.active', 1);
$users = $db->get('users u');

Aliased joins are supported:

$db->joinWithAlias('profiles', 'u.id = p.user_id', 'LEFT', 'p');

Ordering and grouping

$db->orderBy('createdAt', 'DESC');
$db->groupBy('status');

Subqueries and copies

$sub = PdoDb::subQuery('u');
$sub->where('active', 1)->get('users');
$posts = $db->join($sub, 'p.userId=u.id', 'LEFT')->get('posts p');

$page = 1;
$users = $db->paginate('users', $page);
$total = $db->totalPages;

$mapped = $db->map('id')->get('users');
$copy = $db->where('active', 1)->copy()->get('users');

Transactions

$db->startTransaction();
if (!$db->insert('log', ['msg' => 'test'])) {
    $db->rollback();
} else {
    $db->commit();
}

Table locking

$db->setLockMethod('WRITE')->lock('users');
// ... queries ...
$db->unlock();

Connection and utility methods

$db->disconnect('slave');
$db->disconnectAll();
$db->ping();
$exists = $db->tableExists('users');
$escaped = $db->escape("' and 1=1");
$id = $db->getInsertId();
$error = $db->getLastError();
$query = $db->getLastQuery();
$db->clearStmtCache();
print_r($db->getCacheStats());
$db->setTrace(true);
print_r($db->getTrace());

Security logging:

$db->setSecurityLogCallback(function($type, $msg) {
    error_log("[$type] $msg");
});
$db->setSecurityLogging(false); // disable
$status = $db->getSecurityStatus();

Helper expressions

$db->update('users', [
    'visits' => $db->inc(),
    'quota'  => $db->dec(5),
    'note'   => $db->not('note'),
    'hash'   => $db->func('SHA1(?)', ['secret'])
]);

1) Basic SELECT with WHERE, ORDER BY, LIMIT/OFFSET

SQL

SELECT id, email
FROM users
WHERE status = 'active' AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

PdoDb

$users = $db
    ->where('status', 'active')
    ->where('created_at', '2025-01-01', '>=')
    ->orderBy('createdAt', 'DESC')
    ->get('users', [20, 10], ['id', 'email']);

2) SELECT with OR condition

SQL

SELECT *
FROM users
WHERE role = 'admin' OR role = 'manager';

PdoDb

$rows = $db
    ->where('role', 'admin')
    ->orWhere('role', 'manager')
    ->get('users');

3) INNER/LEFT JOIN with aliased tables

SQL

SELECT u.id, u.email, p.full_name
FROM users AS u
LEFT JOIN profiles AS p ON u.id = p.user_id
WHERE p.active = 1
ORDER BY u.id ASC;

PdoDb

$rows = $db
    ->join('profiles p', 'u.id = p.user_id', 'LEFT')
    ->joinWhere('profiles p', 'p.active', 1)
    ->orderBy('u.id', 'ASC')
    ->get('users u', null, ['u.id', 'u.email', 'p.full_name']);

(Alternate with auto alias)

$rows = $db
    ->joinWithAlias('profiles', 'u.id = p.user_id', 'LEFT', 'p')
    ->joinWhere('profiles p', 'p.active', 1)
    ->get('users u');

4) GROUP BY + HAVING

SQL

SELECT status, COUNT(*) AS cnt
FROM users
GROUP BY status
HAVING COUNT(*) > 5
ORDER BY cnt DESC;

PdoDb

$rows = $db
    ->groupBy('status')
    ->having('COUNT(*)', 5, '>')
    ->orderBy('cnt', 'DESC')
    ->get('users', null, ['status', 'COUNT(*) AS cnt']);

5) INSERT (single row) + ON DUPLICATE KEY UPDATE

SQL

INSERT INTO users (id, login, last_login)
VALUES (1, 'admin', NOW())
ON DUPLICATE KEY UPDATE
  login = VALUES(login),
  last_login = VALUES(last_login);

PdoDb

$db->onDuplicate([
    'login'     => $db->func('VALUES(login)'),
    'lastLogin' => $db->func('VALUES(last_login)')
]);

$db->insert('users', [
    'id'        => 1,
    'login'     => 'admin',
    'last_login'=> $db->now()
]);

(Repo also shows onDuplicate(['lastLogin' => $db->now()]) and replace(...) as options. :contentReference[oaicite:1]{index=1})

6) INSERT multiple rows

SQL

INSERT INTO tags (name) VALUES ('php'), ('pdo'), ('security');

PdoDb

$db->insertMulti('tags', [
    ['name' => 'php'],
    ['name' => 'pdo'],
    ['name' => 'security'],
]);

7) UPDATE with expressions (INC/DEC/NOW/FUNC/NOT)

SQL

UPDATE users
SET views = views + 1,
    quota = quota - 5,
    expires = NOW() + INTERVAL 1 DAY,
    note = NOT note
WHERE id = 42;

PdoDb

$db->where('id', 42)->update('users', [
    'views'   => $db->inc(),
    'quota'   => $db->dec(5),
    'expires' => $db->now('+1 day'),
    'note'    => $db->not('note'),
]);

8) DELETE with condition

SQL

DELETE FROM sessions
WHERE user_id = 7 AND last_seen < '2025-01-01';

PdoDb

$db->where('user_id', 7)
   ->where('last_seen', '2025-01-01', '<')
   ->delete('sessions');

9) Subquery in a JOIN

SQL

SELECT p.id, p.title, u.email
FROM posts AS p
LEFT JOIN (
  SELECT id, email
  FROM users
  WHERE active = 1
) AS u ON p.user_id = u.id;

PdoDb

$sub = PdoDb::subQuery('u');
$sub->where('active', 1)->get('users', null, ['id', 'email']);

$rows = $db
    ->join($sub, 'p.user_id = u.id', 'LEFT')
    ->get('posts p', null, ['p.id', 'p.title', 'u.email']);

(Subquery + join usage is shown in README. :contentReference[oaicite:2]{index=2})

10) Pagination with total pages

SQL (concept)

-- Page 3, 10 per page:
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- Then run a separate COUNT(*) to get total pages server-side.

PdoDb

$page  = 3;
$users = $db->orderBy('id', 'ASC')->paginate('users', $page, 10);
$total = $db->totalPages; // provided by withTotalCount logic inside paginate

(Pagination and totalPages are documented. :contentReference[oaicite:3]{index=3})

11) Raw query (when you must)

SQL

SELECT * FROM users WHERE id = ?;

PdoDb

$row = $db->rawQueryOne('SELECT * FROM users WHERE id = ?', [1]);

12) Transactions

SQL (concept)

START TRANSACTION;
INSERT INTO log (msg) VALUES ('test');
COMMIT;
-- or ROLLBACK on failure

PdoDb

$db->startTransaction();
if (!$db->insert('log', ['msg' => 'test'])) {
    $db->rollback();
} else {
    $db->commit();
}

13) JSON/Array/Object result formats

$json  = $db->jsonBuilder()->get('users');
$array = $db->arrayBuilder()->get('users');
$obj   = $db->objectBuilder()->get('users');

14) WITH TOTAL COUNT window (e.g., “show me top 10 + total”)

SQL (concept)

SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 10;
SELECT FOUND_ROWS();

PdoDb

$top = $db->withTotalCount()->get('users', [0, 10]);
$total = $db->totalCount;

API reference

Connection management

connect — Establish a connection to the configured database.

$db->connect();

disconnect — Close a specific connection.

$db->disconnect('slave');

disconnectAll — Close all active connections.

$db->disconnectAll();

connection — Switch to a named connection.

$db->connection('slave')->get('users');

addConnection — Register an additional connection.

$db->addConnection('slave', [...]);

pdo — Retrieve the underlying PDO instance.

$pdo = $db->pdo();

Set result format

jsonBuilder — Return results as JSON strings.

$db->jsonBuilder()->get('users');

arrayBuilder — Return results as arrays.

$db->arrayBuilder()->get('users');

objectBuilder — Return results as stdClass objects.

$db->objectBuilder()->get('users');

Set table prefix

setPrefix — Set table name prefix.

$db->setPrefix('my_');

Execute raw SQL queries

rawQuery — Run a raw SQL query.

$db->rawQuery('SELECT 1');

rawQueryOne — Fetch a single row from a raw SQL query.

$db->rawQueryOne('SELECT * FROM users WHERE id = ?', [1]);

rawQueryValue — Fetch a single value from a raw SQL query.

$db->rawQueryValue('SELECT COUNT(*) FROM users');

query — Execute a prepared statement with bindings.

$db->query('SELECT * FROM users WHERE id = :id', [':id' => 1]);

SQL options

setQueryOption — Set SQL query options.

$db->setQueryOption('SQL_NO_CACHE');

withTotalCount — Calculate total row count for the last query.

$db->withTotalCount()->get('users');

Retrieve data

get — Fetch rows from a table.

$users = $db->get('users');

getOne — Fetch the first matching row.

$user = $db->getOne('users');

getValue — Fetch a single column value.

$count = $db->getValue('users', 'COUNT(*)');

has — Check if records exist.

$exists = $db->where('id',1)->has('users');

Insert data

insert — Insert a row into a table.

$id = $db->insert('users', ['login' => 'admin']);

insertMulti — Insert multiple rows.

$db->insertMulti('users', [['login' => 'a'], ['login' => 'b']]);

replace — Replace existing row (MySQL).

$db->replace('users', ['id' => 1, 'login' => 'admin']);

onDuplicate — Define data for ON DUPLICATE KEY UPDATE.

$db->onDuplicate(['login' => 'new']);

Modify data

update — Update existing rows.

$db->where('id',1)->update('users',['login'=>'u']);

delete — Remove rows from a table.

$db->where('id',1)->delete('users');

Filtering

where — Add a WHERE condition.

$db->where('id',1);

orWhere — Add an OR WHERE condition.

$db->orWhere('status','active');

whereRaw — Add raw WHERE expression.

$db->whereRaw('id > ?', [10]);

having — Add a HAVING condition.

$db->having('COUNT(id)', 1);

orHaving — Add an OR HAVING condition.

$db->orHaving('SUM(score)', '>', 10);

havingRaw — Add raw HAVING expression.

$db->havingRaw('SUM(score) > ?', [10]);

Join tables

join — Join another table.

$db->join('profiles p', 'u.id = p.user_id', 'LEFT');

joinWithAlias — Join using automatic aliasing.

$db->joinWithAlias('profiles', 'u.id = p.user_id', 'LEFT', 'p');

joinWhere — Add a WHERE clause on joined table.

$db->joinWhere('profiles p', 'p.active', 1);

joinOrWhere — Add an OR WHERE clause on joined table.

$db->joinOrWhere('profiles p', 'p.active', 0);

Sorting and grouping

orderBy — Order the results.

$db->orderBy('createdAt', 'DESC');

groupBy — Group the results.

$db->groupBy('status');

Subqueries and pagination

subQuery — Create a subquery builder.

$sub = PdoDb::subQuery('u');

getSubQuery — Retrieve SQL of a subquery.

$sql = $sub->getSubQuery();

copy — Clone current query builder.

$copy = $db->copy();

map — Map results by a column.

$mapped = $db->map('id')->get('users');

paginate — Retrieve paginated results.

$users = $db->paginate('users', 1);

Transaction control

startTransaction — Begin a transaction.

$db->startTransaction();

commit — Commit the current transaction.

$db->commit();

rollback — Roll back the current transaction.

$db->rollback();

Table locking

setLockMethod — Define lock method.

$db->setLockMethod('WRITE');

lock — Lock tables.

$db->lock('users');

unlock — Unlock tables.

$db->unlock();

Utility getters

escape — Escape a string.

$escaped = $db->escape("' and 1=1");

getInsertId — Get last inserted ID.

$id = $db->getInsertId();

getLastError — Get last error message.

$error = $db->getLastError();

getLastErrno — Get last error number.

$errno = $db->getLastErrno();

getLastQuery — Get last executed query.

$query = $db->getLastQuery();

Query tracing

setTrace — Enable or disable tracing.

$db->setTrace(true);

getTrace — Get trace log.

$trace = $db->getTrace();

getLastTrace — Get last trace entry.

$last = $db->getLastTrace();

clearTrace — Clear trace log.

$db->clearTrace();

Expression helpers

inc — Increment a numeric column.

$db->update('users',['visits'=>$db->inc()]);

dec — Decrement a numeric column.

$db->update('users',['quota'=>$db->dec(5)]);

not — Apply NOT operator to a column.

$db->update('users',['active'=>$db->not('active')]);

func — Use a custom SQL function.

$db->update('users',['hash'=>$db->func('SHA1(?)',['secret'])]);

now — Use current timestamp.

$db->insert('log',['created'=>$db->now()]);

interval — Use an INTERVAL expression.

$db->where('created_at', $db->interval('-1', 'DAY'), '>');

Connection utilities

ping — Check the connection is alive.

$db->ping();

Statement cache control

clearStmtCache — Clear prepared statement cache.

$db->clearStmtCache();

getCacheStats — Get cache stats.

$stats = $db->getCacheStats();

Database metadata helpers

tableExists — Check if a table exists.

$exists = $db->tableExists('users');

isValidIdentifier — Validate identifier name.

$valid = $db->isValidIdentifier('users');

Security logging

setSecurityLogCallback — Set callback for security logs.

$db->setSecurityLogCallback(fn($t,$m)=>error_log("[$t] $m"));

setSecurityLogging — Enable or disable security logging.

$db->setSecurityLogging(false);

getSecurityStatus — Get logging status.

$status = $db->getSecurityStatus();

Version info

getVersion — Get library version.

$version = $db->getVersion();

getMysqlVersion — Get MySQL server version.

$mysql = $db->getMysqlVersion();

Static helpers

getInstance — Retrieve singleton instance of PdoDb.

$db = PdoDb::getInstance();

License

GPL-3.0-or-later

lakshanjs / pdodb

Maintainers

Details

README

Installation

Getting started

Simple initialization

Advanced initialization

Selecting the driver

Reuse existing PDO connection

Setting prefix later

Auto reconnect

Getting the instance elsewhere

Multiple database connections

Quick example

Result formats

Running queries

Selecting data

Inserting data

Updating data

Deleting data

Raw queries

Conditions

Joins

Ordering and grouping

Subqueries and copies

Transactions

Table locking

Connection and utility methods

Helper expressions

1) Basic SELECT with WHERE, ORDER BY, LIMIT/OFFSET

2) SELECT with OR condition

3) INNER/LEFT JOIN with aliased tables

4) GROUP BY + HAVING

5) INSERT (single row) + ON DUPLICATE KEY UPDATE

6) INSERT multiple rows

7) UPDATE with expressions (INC/DEC/NOW/FUNC/NOT)

8) DELETE with condition

9) Subquery in a JOIN

10) Pagination with total pages

11) Raw query (when you must)

12) Transactions

13) JSON/Array/Object result formats

14) WITH TOTAL COUNT window (e.g., “show me top 10 + total”)

API reference

Connection management

Set result format

Set table prefix

Execute raw SQL queries

SQL options

Retrieve data

Insert data

Modify data

Filtering

Join tables

Sorting and grouping

Subqueries and pagination

Transaction control

Table locking

Utility getters

Query tracing

Expression helpers

Connection utilities

Statement cache control

Database metadata helpers

Security logging

Version info

Static helpers

License