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(8mo ago)131GPL-3.0-or-laterPHPPHP ^8.0

Since Aug 15Pushed 8mo 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 1mo ago

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

31

—

LowBetter than 68% of packages

Maintenance58

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

267d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/7646f42a475b03098fb92e7dbec224cfabc712ac70950ee0b7337b361036d994?d=identicon)[lakshanjs](/maintainers/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.7k578.4M5.6k](/packages/doctrine-dbal)[aura/sql

A PDO extension that provides lazy connections, array quoting, query profiling, value binding, and convenience methods for common fetch styles. Because it extends PDO, existing code that uses PDO can use this without any changes to the existing code.

5632.5M43](/packages/aura-sql)[aura/sqlquery

Object-oriented query builders for MySQL, Postgres, SQLite, and SQLServer; can be used with any database connection library.

4572.9M34](/packages/aura-sqlquery)[aura/sqlschema

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

41234.1k4](/packages/aura-sqlschema)[vcian/laravel-db-auditor

Database DB Auditor provide leverage to audit your MySql,sqlite, PostgreSQL database standards and also provide options to add constraints in table.

28535.1k1](/packages/vcian-laravel-db-auditor)[delight-im/db

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

49156.8k7](/packages/delight-im-db)

PHPackages © 2026

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