PHPackages                             vielhuber/dbhelper - 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. vielhuber/dbhelper

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

vielhuber/dbhelper
==================

Small PHP wrapper for mysql/pgsql databases.

2.5.5(1mo ago)343.2k↑242.9%94MITPHPPHP >=8.1

Since Feb 16Pushed 1w ago3 watchersCompare

[ Source](https://github.com/vielhuber/dbhelper)[ Packagist](https://packagist.org/packages/vielhuber/dbhelper)[ RSS](/packages/vielhuber-dbhelper/feed)WikiDiscussions main Synced 2w ago

READMEChangelogDependencies (7)Versions (156)Used By (4)

[![build status](https://github.com/vielhuber/dbhelper/actions/workflows/ci.yml/badge.svg)](https://github.com/vielhuber/dbhelper/actions)[![GitHub Tag](https://camo.githubusercontent.com/c64152fa24ea31cf161d9ea5f63eacad43d777ab50a8f554edfe7eeb3e1f61c8/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f762f7461672f7669656c68756265722f646268656c706572)](https://github.com/vielhuber/dbhelper/tags)[![Code Style](https://camo.githubusercontent.com/1540f8ce219727155ab62506c77b818b720421c22c4cf0b18a5f160942132e2d/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f636f64655f7374796c652d7073722d2d31322d6666363962342e737667)](https://www.php-fig.org/psr/psr-12/)[![License](https://camo.githubusercontent.com/432e2b767926b5b0dc2a9f4f8415ba2f020b080e6a15e8fbd092b0a9a1e4f6f5/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f7669656c68756265722f646268656c706572)](https://github.com/vielhuber/dbhelper/blob/main/LICENSE.md)[![Last Commit](https://camo.githubusercontent.com/91519e6325f4af775346e8d6837eac00ab61a5f6491da8c75f20d1a8b10d776c/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6173742d636f6d6d69742f7669656c68756265722f646268656c706572)](https://github.com/vielhuber/dbhelper/commits)[![PHP Version Support](https://camo.githubusercontent.com/a26ca230e14540c44abea3ad08838b0fac66535d702a2d613fe17117f2855e34/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f7068702d762f7669656c68756265722f646268656c706572)](https://packagist.org/packages/vielhuber/dbhelper)[![Packagist Downloads](https://camo.githubusercontent.com/288f7acb15aa3edd5cc96a6ca3b95a2a6611d83c5f71dd38232f8c7f49ba604f/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f7669656c68756265722f646268656c706572)](https://packagist.org/packages/vielhuber/dbhelper)

🍗 dbhelper 🍗
============

[](#-dbhelper-)

dbhelper is a small php wrapper for mysql/postgres/sqlite databases.

installation
------------

[](#installation)

install once with composer:

```
composer require vielhuber/dbhelper

```

then add this to your project:

```
require __DIR__ . '/vendor/autoload.php';
use vielhuber\dbhelper\dbhelper;
$db = new dbhelper();
```

configuration options:

```
$db = new dbhelper([
    'return_format' => null // null|array|object
]);
```

usage
-----

[](#usage)

```
/* connect to database */
$db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
$db->connect('pdo', 'postgres', '127.0.0.1', 'username', 'password', 'database', 5432);
$db->connect('pdo', 'sqlite', 'database.db');
$db->connect('pdo', 'sqlite', 'database.db', null, null, null, null, 120); // specify a manual timeout of 120 seconds
$db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306); // database must not be available

/* disconnect from database */
$db->disconnect();

/* insert/update/delete */
$id = $db->insert('tablename', ['col1' => 'foo']);
$db->update('tablename', ['col1' => 'bar'], ['id' => $id]);
$db->delete('tablename', ['id' => $id]);

/* select */
$db->fetch_all('SELECT * FROM tablename WHERE name = ? AND number > ?', 'foo', 42);
$db->fetch_row('SELECT * FROM tablename WHERE ID = ?', 1);
$db->fetch_col('SELECT col FROM tablename WHERE ID > ?', 1);
$db->fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);

/* count */
$db->count('tablename') // 42
$db->count('tablename', ['col1' => 'foo']) // 7

/* automatic flattened arguments */
$db->fetch_all('SELECT * FROM tablename WHERE ID = ?', [1], 2, [3], [4,[5,6]]);
// gets transformed to
$db->fetch_all('SELECT * FROM tablename WHERE ID = ?', 1, 2, 3, 4, 5, 6);

/* automatic in-expansion */
$db->fetch_all('SELECT * FROM tablename WHERE col1 = ? AND col2 IN (?)', 1, [2,3,4]);

/* inline query args for dynamic query strings */
$params = [];
$query = 'SELECT * FROM tablename WHERE col1 = ' . $db->query_arg($params, 'foo');
$db->fetch_all($query, ...$params);

/* support for null values */
$db->query('UPDATE tablename SET col1 = ? WHERE col2 = ? AND col3 != ?', null, null, null);
// gets transformed to
$db->query('UPDATE tablename SET col1 = NULL WHERE col2 IS NULL AND col3 IS NOT NULL');

/* clean up */
$db->clear(); // delete all tables (without dropping the whole database)
$db->clear('tablename'); // delete all rows in a table

/* delete table */
$db->delete_table('tablename');

/* create table */
$db->create_table('tablename', [
    'id' => 'SERIAL PRIMARY KEY', // use INTEGER instead of SERIAL on sqlite to get auto ids
    'col1' => 'varchar(255)',
    'col2' => 'varchar(255)',
    'col3' => 'varchar(255)'
]);

/* index helpers */
$db->create_index('tablename', 'col1_lookup', ['col1']);
$db->create_index('tablename', 'col2_col3_lookup', ['col2', 'col3']);
$db->create_index('tablename', 'col1_unique', ['col1'], unique: true);
$db->get_indexes('tablename'); // database index names
$db->has_index('tablename', 'col1_lookup'); // true
$db->delete_index('tablename', 'col1_lookup');

/* create if not exists and connect to database */
$db->connect_with_create('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
    // this is a shorthand for
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306);
    $db->create_database('database');
    $db->disconnect();
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);

/* delete database */
$db->disconnect_with_delete();
    // this is a shorthand for
    $db->disconnect();
    $db->connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', null, 3306);
    $db->delete_database('database');
    $db->disconnect();

/* raw queries */
$db->query('INSERT INTO tablename(row1, row2) VALUES(?, ?, ?)', 1, 2, 3);
$db->query('UPDATE tablename SET row1 = ? WHERE ID = ?', 1, 2);
$db->query('DELETE FROM tablename WHERE ID = ?', 1);

/* quickly debug raw queries */
$db->debug('DELETE FROM tablename WHERE row1 = ?', null); // DELETE FROM tablename WHERE row1 IS NULL

/* last insert id */
$db->insert('tablename', ['col1' => 'foo']);
$db->last_insert_id();

/* some more little helpers */
$db->get_tables() // ['tablename', ...]
$db->has_table('tablename') // true
$db->get_columns('tablename') // ['col1', 'col2', ...]
$db->has_column('tablename', 'col1') // true
$db->get_datatype('tablename', 'col1') // varchar
$db->get_primary_key('tablename') // id
$db->uuid() // generate uuid (v4) from inside the database
$db->get_foreign_keys('users') // [['address_id' => ['addresses','id'], ...]
$db->is_foreign_key('users', 'address_id') // true
$db->get_foreign_tables_out('users') // [['addresses' => [['address_id','id']], ...]
$db->get_foreign_tables_in('addresses') // [['users' => [['address_id','id']], ...]

/* handle duplicates */
$db->get_duplicates() // ['count' => ['tbl1' => 3, 'tbl2' => 17], 'data' => ['tbl1' => [...], 'tbl2' => [...]]
$db->delete_duplicates('tablename') // delete duplicates based on all columns except the primary key
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1']) // based on specific columns
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], false) // null values are considered equal by default; you can disable this untypical behaviour for sql with "false"
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], true, ['id' => 'asc']) // keep row with lowest primary key "id" (normally this is 'id' => 'desc')
$db->delete_duplicates('tablename', ['common_col1','common_col1','common_col1'], true, ['id' => 'asc'], false) // case insensitive match (normally this is case sensitive)

/* globally trim values */
$db->trim_values() // [['table' => 'tbl1', 'column' => 'col1', 'id' => 1, 'before' => ' foo', 'after' => 'foo'], ...]
$db->trim_values(false) // by default trim_values does a dry run (no updates)
$db->trim_values(true) // do real updates
$db->trim_values(false, ['table1', 'table2' => ['col1', 'col2']]) // ignore tables and columns

/* batch functions (they create only one query) */
$db->insert('tablename', [
    ['id' => 1, 'name' => 'foo1'],
    ['id' => 2, 'name' => 'foo2'],
    ['id' => 3, 'name' => 'foo3']
]);
$db->delete('tablename', [
    ['id' => 1],
    ['id' => 7],
    ['id' => 42]
]);
$db->update('tablename', [
    [['col1' => 'var1', 'col2' => 1], ['id' => 1, 'key' => '1']],
    [['col1' => 'var2', 'col2' => 2], ['id' => 2, 'key' => '2']],
    [['col1' => 'var3', 'col2' => 3], ['id' => 3, 'key' => '3']]
]);
/*
this generates the following query:
UPDATE tablename SET
col1 = CASE WHEN (id = 1 AND key = '1') THEN 'var1' WHEN (id = 2 AND key = '2') THEN 'var2' WHEN (id = 3 AND key = '3') THEN 'var3' END,
col2 = CASE WHEN (id = 1 AND key = '1') THEN 1 WHEN (id = 2 AND key = '2') THEN 2 WHEN (id = 3 AND key = '3') THEN 3 END
WHERE id IN (1,2,3) AND key IN ('1','2','3');
*/
```

### logging

[](#logging)

dbhelper can support setting up a mature logging system on mysql/postgres databases.

```
$db = new dbhelper([
    'logging_table' => 'logs',
    'exclude' => [
        'tables' => ['table1'],
        'columns' => ['table2' => ['col1', 'col2', 'col3']]
    ],
    'delete_older' => 12, // months
    'updated_by' => get_current_user_id()
]);
$db->connect('...');
$db->setup_logging();
```

`setup_logging()` does four things:

- it creates a logging table (if not exists)
- it appends a single column `updated_by` to every table in the database (if not exists)
- it creates triggers for all insert/update/delete events (if not exists)
- it deletes old logging entries based on the `delete_older` option

you should run this method after a schema change (e.g. in your migrations) and you can also run it on a daily basis via cron. it is recommened to exclude blob/bytea columns.

the logging table has the following schema:

- `id`: unique identifier of that single change
- `log_event`: insert/update/delete
- `log_table`: name of the table of the modified row
- `log_key`: key of the modified row
- `log_column`: column of the modified row
- `log_value`: value of the modified row
- `log_uuid`: unique identifier of that row change
- `updated_by`: who did make that change
- `updated_at`: date and time of the event

we now have to adjust our queries. `updated_by` must be populated by the web application on all insert/update queries and our logging table must be manually populated before delete queries:

```
$db->insert('tablename', ['col1' => 'foo', 'updated_by' => get_current_user_id()]);

$db->update('tablename', ['col1' => 'foo', 'updated_by' => get_current_user_id()], ['id' => 42]);

$db->insert('logs', [
    'log_event' => 'delete',
    'log_table' => 'tablename',
    'log_key' => 42,
    'log_uuid' => $db->uuid(),
    'updated_by' => get_current_user_id()
]);
$db->delete('tablename', ['id' => 42]);
```

instead of all this we can let dbhelper magically do the heavy lifting on every insert/update/delete for us:

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

dbhelper then automatically injects the `updated_by` column on all insert/update statements and inserts a log entry before every delete query (all queries are handled, even those who are sent with `$db->query`).

important note: if we manipulate data outside of our web application, the triggers also work, except with accurate values in `updated_by`. this is especially true for delete statements (they also work without the manual insert query upfront).

call the following helper functions, if you (temporarily) need to disable logging by triggers:

```
$db->disable_logging(['mega_big_table']);
try {
    $db->query('DELETE FROM mega_big_table');
} finally {
    $db->enable_logging(['mega_big_table']);
}
```

always use a `try`/`finally` block. if the code in between throws, logging stays off until someone turns it back on – and missing log entries cannot be recreated afterwards.

both methods take an optional list of tables. without it every table of the database loses its logging, which is rarely what you want – pass the tables you are about to touch.

`disable_logging()` drops the triggers and, on postgres, their trigger functions along with them – otherwise the functions pile up in databases where logging is removed and set up again. if you are only suspending logging for a moment and want the recreation to stay cheap, keep them:

```
$db->disable_logging(delete: false);
$db->disable_logging(['mega_big_table'], delete: false);
```

on mysql the flag has no effect, there are no separate trigger functions there.

`enable_logging()` recreates missing triggers and switches disabled ones back on, so it is also the repair path. `setup_logging()` rebuilds everything from scratch.

to find out whether logging is currently in place, use:

```
$db->check_logging();
// ['missing' => [['table' => 'foo', 'trigger' => 'trigger-logging-insert-foo']], 'disabled' => []]
```

both lists are empty if everything is fine. `missing` catches an aborted `disable_logging()` and tables that never had logging set up, `disabled` catches triggers someone switched off by hand with `ALTER TABLE ... DISABLE TRIGGER` (postgres) or a dump that was restored in that state. run this on a schedule – a lost trigger produces no error, it just stops logging.

all logging methods at a glance:

```
$db->setup_logging(); // creates the logging table, adds updated_by to every table, (re)creates all triggers, deletes old entries
$db->enable_auto_inject(); // fills updated_by on every insert/update and writes the log row before every delete, for this connection
$db->check_logging(); // returns which triggers are missing or switched off, both lists empty means everything is fine
$db->check_logging(['table1']); // ... only for the given tables
$db->disable_logging(); // drops all logging triggers and, on postgres, their trigger functions
$db->disable_logging(['table1']); // ... only for the given tables
$db->disable_logging(delete: false); // ... but keeps the trigger functions, so that recreating the triggers stays cheap
$db->enable_logging(); // recreates missing triggers and switches disabled ones back on, also the repair path
$db->enable_logging(['table1']); // ... only for the given tables
```

that's it – happy logging.

### wordpress support

[](#wordpress-support)

this also works for wordpress (using wpdb, prepared statements and stripslashes\_deep under the hood):

```
$db->connect('wordpress');
$db->fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);
```

### locking in sqlite

[](#locking-in-sqlite)

sqlite is nice but database locking can be tricky.
dbhelper provides a default timeout of `60` seconds, which prevents most database locks.
you can manually define a timeout in the `connect()` function.
checkout the following sqlite lock tests:

- `php tests/lock/run.php 1`: runs into database locking
- `php tests/lock/run.php 120`: does not run into database locking

also consider enabling [wal](https://sqlite.org/wal.html) via `$db->query('PRAGMA journal_mode=WAL;');`.

### return values

[](#return-values)

as return values after fetching results dbhelper usually returns associative arrays.
if you use it with wordpress, objects are returned.
dbhelper throws exceptions on all occured errors.
on an `insert` operation, the primary key (id) is returned.
on any `delete`, `update` or even `query` operation, the number of affected rows are returned.

### static version

[](#static-version)

here is also a static version with static function calls (this makes sense, if you use a single instance of dbhelper):

```
$db = new dbhelper();
require_once $_SERVER['DOCUMENT_ROOT'] . '/vendor/vielhuber/dbhelper/src/static.php';
db_connect('pdo', 'mysql', '127.0.0.1', 'username', 'password', 'database', 3306);
db_fetch_var('SELECT col FROM tablename WHERE ID = ?', 1);
$params = [];
db_fetch_all('SELECT * FROM tablename WHERE col1 = ' . db_query_arg($params, 'foo'), ...$params);
```

###  Health Score

64

—

FairBetter than 99% of packages

Maintenance96

Actively maintained with recent releases

Popularity33

Limited adoption so far

Community22

Small or concentrated contributor base

Maturity90

Battle-tested with a long release history

 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 ~22 days

Recently: every ~1 days

Total

155

Last Release

41d ago

Major Versions

1.9.9 → 2.0.02019-10-04

PHP version history (5 changes)1.0.0PHP ^7.0

1.0.6PHP >=5.6

2.1.9PHP >=8.0

2.2.4PHP >=7.4

2.4.1PHP >=8.1

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/3183737?v=4)[David Vielhuber](/maintainers/vielhuber)[@vielhuber](https://github.com/vielhuber)

---

Top Contributors

[![vielhuber](https://avatars.githubusercontent.com/u/3183737?v=4)](https://github.com/vielhuber "vielhuber (197 commits)")

---

Tags

mysqlphppostgresqlsqlsqlite

###  Code Quality

TestsPHPUnit

### Embed Badge

![Health badge](/badges/vielhuber-dbhelper/health.svg)

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

###  Alternatives

[jdorn/sql-formatter

a PHP SQL highlighting library

3.8k117.8M121](/packages/jdorn-sql-formatter)[backup-manager/backup-manager

A framework agnostic database backup manager with user-definable procedures and support for S3, Dropbox, FTP, SFTP, and more with drivers for popular frameworks.

1.7k1.6M11](/packages/backup-manager-backup-manager)[propel/propel1

Propel is an open-source Object-Relational Mapping (ORM) for PHP5.

8351.6M88](/packages/propel-propel1)[insolita/yii2-migration-generator

Set of gii tools for generating files for migration by schema of table , phpdoc or table data

108508.0k5](/packages/insolita-yii2-migration-generator)[xpdo/xpdo

A PDO-based Object/Relational Bridge Library

7088.4k4](/packages/xpdo-xpdo)[voku/session2db

A PHP library acting as a wrapper for PHP's default session handling functions which stores data in a MySQL database, providing both better performance and better security and protection against session fixation and session hijacking.

2920.0k](/packages/voku-session2db)

PHPackages © 2026

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