Download the PHP package lakshanjs/pdodb without Composer

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

Install via Composer:

Getting started

Simple initialization

Advanced initialization

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.

Reuse existing PDO connection

Setting prefix later

Auto reconnect

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

Getting the instance elsewhere

Multiple database connections

Quick example

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

Result formats

Return types can be adjusted per query:

Running queries

Selecting data

Inserting data

Updating data

Deleting data

Raw queries

Conditions

Joins

Aliased joins are supported:

Ordering and grouping

Subqueries and copies

Transactions

Table locking

Connection and utility methods

Security logging:

Helper expressions

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

SQL

PdoDb

2) SELECT with OR condition

SQL

PdoDb

3) INNER/LEFT JOIN with aliased tables

SQL

PdoDb

(Alternate with auto alias)

4) GROUP BY + HAVING

SQL

PdoDb

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

SQL

PdoDb

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

6) INSERT multiple rows

SQL

PdoDb

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

SQL

PdoDb

8) DELETE with condition

SQL

PdoDb

9) Subquery in a JOIN

SQL

PdoDb

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

10) Pagination with total pages

SQL (concept)

PdoDb

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

11) Raw query (when you must)

SQL

PdoDb

12) Transactions

SQL (concept)

PdoDb

13) JSON/Array/Object result formats

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

SQL (concept)

PdoDb

API reference

Connection management

connect — Establish a connection to the configured database.

disconnect — Close a specific connection.

disconnectAll — Close all active connections.

connection — Switch to a named connection.

addConnection — Register an additional connection.

pdo — Retrieve the underlying PDO instance.

Set result format

jsonBuilder — Return results as JSON strings.

arrayBuilder — Return results as arrays.

objectBuilder — Return results as stdClass objects.

Set table prefix

setPrefix — Set table name prefix.

Execute raw SQL queries

rawQuery — Run a raw SQL query.

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

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

query — Execute a prepared statement with bindings.

SQL options

setQueryOption — Set SQL query options.

withTotalCount — Calculate total row count for the last query.

Retrieve data

get — Fetch rows from a table.

getOne — Fetch the first matching row.

getValue — Fetch a single column value.

has — Check if records exist.

Insert data

insert — Insert a row into a table.

insertMulti — Insert multiple rows.

replace — Replace existing row (MySQL).

onDuplicate — Define data for ON DUPLICATE KEY UPDATE.

Modify data

update — Update existing rows.

delete — Remove rows from a table.

Filtering

where — Add a WHERE condition.

orWhere — Add an OR WHERE condition.

whereRaw — Add raw WHERE expression.

having — Add a HAVING condition.

orHaving — Add an OR HAVING condition.

havingRaw — Add raw HAVING expression.

Join tables

join — Join another table.

joinWithAlias — Join using automatic aliasing.

joinWhere — Add a WHERE clause on joined table.

joinOrWhere — Add an OR WHERE clause on joined table.

Sorting and grouping

orderBy — Order the results.

groupBy — Group the results.

Subqueries and pagination

subQuery — Create a subquery builder.

getSubQuery — Retrieve SQL of a subquery.

copy — Clone current query builder.

map — Map results by a column.

paginate — Retrieve paginated results.

Transaction control

startTransaction — Begin a transaction.

commit — Commit the current transaction.

rollback — Roll back the current transaction.

Table locking

setLockMethod — Define lock method.

lock — Lock tables.

unlock — Unlock tables.

Utility getters

escape — Escape a string.

getInsertId — Get last inserted ID.

getLastError — Get last error message.

getLastErrno — Get last error number.

getLastQuery — Get last executed query.

Query tracing

setTrace — Enable or disable tracing.

getTrace — Get trace log.

getLastTrace — Get last trace entry.

clearTrace — Clear trace log.

Expression helpers

inc — Increment a numeric column.

dec — Decrement a numeric column.

not — Apply NOT operator to a column.

func — Use a custom SQL function.

now — Use current timestamp.

interval — Use an INTERVAL expression.

Connection utilities

ping — Check the connection is alive.

Statement cache control

clearStmtCache — Clear prepared statement cache.

getCacheStats — Get cache stats.

Database metadata helpers

tableExists — Check if a table exists.

isValidIdentifier — Validate identifier name.

Security logging

setSecurityLogCallback — Set callback for security logs.

setSecurityLogging — Enable or disable security logging.

getSecurityStatus — Get logging status.

Version info

getVersion — Get library version.

getMysqlVersion — Get MySQL server version.

Static helpers

getInstance — Retrieve singleton instance of PdoDb.

License

GPL-3.0-or-later