PHPackages                             lakshanjs/pdodb - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. lakshanjs/pdodb

ActiveLibrary[Database & ORM](/categories/database)

lakshanjs/pdodb
===============

Production-ready PDO database abstraction for PHP with a secure query builder, statement caching, nested transactions, and MySQL 8+ compatibility.

v8.0.1(10mo ago)131GPL-3.0-or-laterPHPPHP ^8.0

Since Aug 15Pushed 10mo agoCompare

[ Source](https://github.com/lakshanjs/PdoDb)[ Packagist](https://packagist.org/packages/lakshanjs/pdodb)[ Docs](https://github.com/lakshanjs/PdoDb)[ RSS](/packages/lakshanjs-pdodb/feed)WikiDiscussions main Synced today

READMEChangelog (2)Dependencies (1)Versions (3)Used By (0)

PdoDb
=====

[](#pdodb)

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
------------

[](#installation)

Install via [Composer](https://getcomposer.org/):

```
composer require lakshanjs/pdodb
```

Getting started
---------------

[](#getting-started)

### Simple initialization

[](#simple-initialization)

```
use LakshanJS\PdoDb\PdoDb;

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

### Advanced initialization

[](#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

[](#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

[](#reuse-existing-pdo-connection)

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

### Setting prefix later

[](#setting-prefix-later)

```
$db->setPrefix('my_');
```

### Auto reconnect

[](#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

[](#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

[](#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
-------------

[](#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
--------------

[](#result-formats)

Return types can be adjusted per query:

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

Running queries
---------------

[](#running-queries)

### Selecting data

[](#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

[](#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

[](#updating-data)

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

### Deleting data

[](#deleting-data)

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

### Raw queries

[](#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
----------

[](#conditions)

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

Joins
-----

[](#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
---------------------

[](#ordering-and-grouping)

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

Subqueries and copies
---------------------

[](#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
------------

[](#transactions)

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

Table locking
-------------

[](#table-locking)

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

Connection and utility methods
------------------------------

[](#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
------------------

[](#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
--------------------------------------------------

[](#1-basic-select-with-where-order-by-limitoffset)

**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
---------------------------

[](#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
--------------------------------------

[](#3-innerleft-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
--------------------

[](#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
------------------------------------------------

[](#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
-----------------------

[](#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)
-------------------------------------------------

[](#7-update-with-expressions-incdecnowfuncnot)

**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
------------------------

[](#8-delete-with-condition)

**SQL**

```
DELETE FROM sessions
WHERE user_id = 7 AND last_seen where('user_id', 7)
   ->where('last_seen', '2025-01-01', ' ?', [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-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

[](#sorting-and-grouping)

**orderBy** — Order the results.

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

**groupBy** — Group the results.

```
$db->groupBy('status');
```

---

### Subqueries and pagination

[](#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

[](#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

[](#table-locking-1)

**setLockMethod** — Define lock method.

```
$db->setLockMethod('WRITE');
```

**lock** — Lock tables.

```
$db->lock('users');
```

**unlock** — Unlock tables.

```
$db->unlock();
```

---

### Utility getters

[](#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

[](#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

[](#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

[](#connection-utilities)

**ping** — Check the connection is alive.

```
$db->ping();
```

---

### Statement cache control

[](#statement-cache-control)

**clearStmtCache** — Clear prepared statement cache.

```
$db->clearStmtCache();
```

**getCacheStats** — Get cache stats.

```
$stats = $db->getCacheStats();
```

---

### Database metadata helpers

[](#database-metadata-helpers)

**tableExists** — Check if a table exists.

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

**isValidIdentifier** — Validate identifier name.

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

---

### Security logging

[](#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

[](#version-info)

**getVersion** — Get library version.

```
$version = $db->getVersion();
```

**getMysqlVersion** — Get MySQL server version.

```
$mysql = $db->getMysqlVersion();
```

---

### Static helpers

[](#static-helpers)

**getInstance** — Retrieve singleton instance of PdoDb.

```
$db = PdoDb::getInstance();
```

---

License
-------

[](#license)

GPL-3.0-or-later

###  Health Score

30

—

LowBetter than 62% of packages

Maintenance54

Moderate activity, may be stable

Popularity9

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity42

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~0 days

Total

2

Last Release

321d ago

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/7899504?v=4)[Lakshan Jayasinghe](/maintainers/lakshanjs)[@lakshanjs](https://github.com/lakshanjs)

---

Top Contributors

[![lakshanjs](https://avatars.githubusercontent.com/u/7899504?v=4)](https://github.com/lakshanjs "lakshanjs (20 commits)")

---

Tags

phpdatabasemysqlsqlitepdopgsqlsqlsrvquery builder

###  Code Quality

TestsPHPUnit

### Embed Badge

![Health badge](/badges/lakshanjs-pdodb/health.svg)

```
[![Health](https://phpackages.com/badges/lakshanjs-pdodb/health.svg)](https://phpackages.com/packages/lakshanjs-pdodb)
```

###  Alternatives

[doctrine/dbal

Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management.

9.7k605.0M6.8k](/packages/doctrine-dbal)[aura/sqlschema

Provides facilities to read table names and table columns from a database using PDO.

44243.2k4](/packages/aura-sqlschema)[delight-im/db

Safe and convenient SQL database access in a driver-agnostic way

50174.0k7](/packages/delight-im-db)[popphp/pop-db

Pop Db Component for Pop PHP Framework

1816.5k12](/packages/popphp-pop-db)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)