NDTDBF

Introduction

NDT DBF is a single-file, secure-by-default PHP database framework with a compact, beginner-friendly API and enterprise-grade features. Use it as a standalone DBF.php or via Composer/PSR-4.

Key ideas
  • Safe by default: prepared statements for values, identifier quoting per driver, soft-delete guard, policies, readonly mode.
  • Compact & portable: one-file drop-in, or Composer package; dialect-aware features like upsert(), keyset pagination.
  • Enterprise hooks: retryable transactions, middleware, policy guard, test mode, logging.

Requirements

  • PHP 8.1+
  • PDO extension for your driver (pdo_mysql, pdo_pgsql, pdo_sqlite, pdo_sqlsrv…)

Installation

Composer

composer require ndtan/dbf
<?php
require __DIR__ . '/vendor/autoload.php';

use ndtan\DBF;

$db = new DBF([
  'type'     => 'mysql',
  'host'     => '127.0.0.1',
  'database' => 'app',
  'username' => 'root',
  'password' => 'secret',
  'charset'  => 'utf8mb4'
]);

Single file

<?php
require __DIR__ . '/DBF.php';
$db = new ndtan\DBF('mysql://root:secret@127.0.0.1/app?charset=utf8mb4');

Connection

DSN / URI (one line)

$db = new ndtan\DBF('sqlite::memory:');               // SQLite (memory)
$db = new ndtan\DBF('mysql://root:secret@127.0.0.1/app?charset=utf8mb4');
$db = new ndtan\DBF('pgsql://user:pass@127.0.0.1:5432/app');

Array config

$db = new ndtan\DBF([
  'type'     => 'sqlite',
  'database' => ':memory:',
  'features' => [
    'soft_delete'   => ['enabled' => true, 'column' => 'deleted_at'],
    'max_in_params' => 1000
  ]
]);

Inject an existing PDO

$pdo = new PDO('sqlite::memory:');
$db  = new ndtan\DBF(['pdo' => $pdo, 'type' => 'sqlite']);

WHERE Syntax

Goal: express complex predicates safely. All values are parameterized; identifiers are quoted per driver.

Basic conditions

$rows = $db->table('users')
  ->where('status','=','active')
  ->orWhere('email','=','a@ndtan.net')
  ->get();

IN / BETWEEN / NULL

$ids  = $db->table('users')->whereIn('id',[1,2,3])->get();
$band = $db->table('orders')->whereBetween('total',[100,500])->get();
$nul  = $db->table('users')->whereNull('deleted_at')->get();

Nesting via closure

$rows = $db->table('users')
  ->where(fn($q) => $q->where('status','=','active')->orWhere('status','=','vip'))
  ->where('email','LIKE','%@ndtan.net')
  ->get();
Notes
  • whereIn() throws LengthException if list size exceeds features.max_in_params.
  • Soft-delete filter is appended automatically if the table has that column.

select()

Choose result columns (names or expressions).

$rows = $db->table('users')
  ->select(['id','email'])
  ->get();

// Expressions (use carefully)
$rows = $db->table('orders')
  ->select(['id', 'total', 'total * 1.1 AS total_with_tax'])
  ->get();

Group / Having

$rows = $db->table('orders')
  ->select(['user_id', 'COUNT(*) AS cnt'])
  ->groupBy('user_id')
  ->having('cnt','>',1)
  ->get();
Notes
  • Identifiers are auto-quoted per driver.
  • Values are always bound as parameters.

join()

Combine rows from related tables with ON conditions.

$rows = $db->table('orders')
  ->join('users','orders.user_id','=','users.id') // inner join
  ->select(['orders.id','users.email'])
  ->orderBy('orders.id','asc')
  ->get();
Notes
  • Available joins: join(), leftJoin() (others depend on driver).
  • Paths like users.id are quoted part-by-part.

insert()

Insert one row and get last insert id.

$id = $db->table('users')->insert(['email'=>'a@ndtan.net','status'=>'active']);

insertMany()

$db->table('users')->insertMany([
  ['email'=>'p1@ndtan.net','status'=>'active'],
  ['email'=>'p2@ndtan.net','status'=>'vip'],
]);

insertGet()

$row = $db->table('users')
  ->insertGet(['email'=>'b@ndtan.net','status'=>'vip'], ['id','email']);
Notes
  • insertGet() uses RETURNING when available; otherwise a follow-up SELECT.

update()

Modify rows matching the current WHERE.

$affected = $db->table('users')
  ->where('id','=', $id)
  ->update(['status'=>'vip']);
Notes
  • Readonly mode or policy guard can block updates.
  • Values are parameterized; identifiers quoted.

delete()

Soft or hard delete depending on your feature settings / column presence.

$db->table('users')->where('id','=', $id)->delete();     // soft if deleted_at exists
$db->table('users')->where('id','=', $id)->restore();    // set deleted_at = NULL
$db->table('users')->where('id','=', $id)->forceDelete();// hard delete

upsert()

Insert or update on conflict (dialect-aware).

$db->table('users')->upsert(
  ['email'=>'a@ndtan.net','status'=>'vip'],
  conflict: ['email'],
  updateColumns: ['status']
);
Dialects
  • MySQL/MariaDB: ON DUPLICATE KEY UPDATE
  • PostgreSQL/SQLite: ON CONFLICT (...) DO UPDATE
  • SQL Server: safe two-phase fallback inside a transaction.

orderBy() · limit()

Sorting and windowing.

$rows = $db->table('users')
  ->orderBy('id','desc')
  ->limit(20)
  ->get();
Tip
  • For large datasets, prefer keyset pagination instead of deep offsets.

get() · first() · exists()

$list   = $db->table('users')->limit(50)->get();               // array<array>
$first  = $db->table('users')->where('id','=',1)->first();     // ?array
$exists = $db->table('users')->where('email','=','a@ndtan.net')->exists(); // bool

count()

$n = $db->table('users')->where('status','=','active')->count();

sum()

$v = $db->table('orders')->sum('total');

avg()

$v = $db->table('orders')->avg('total');

min()

$v = $db->table('orders')->min('total');

max()

$v = $db->table('orders')->max('total');

Keyset pagination

Fast, stable pagination using the last seen key.

$page1 = $db->table('posts')->orderBy('id','desc')->limit(50)->getKeyset(null, 'id');
$page2 = $db->table('posts')->orderBy('id','desc')->limit(50)->getKeyset($page1['next'], 'id');
Notes
  • Operator depends on sort: > for ASC, < for DESC.
  • Encode the cursor if you expose it externally.

tx (action)

Run a transactional function with deadlock/busy retry (exponential backoff + jitter).

$db->tx(function(ndtan\DBF $tx){
  $oid = $tx->table('orders')->insert(['user_id'=>10,'total'=>200]);
  $tx->table('order_items')->insert(['order_id'=>$oid,'sku'=>'A','qty'=>1]);
}, attempts: 3);
Notes
  • Throws last exception if all attempts fail.

Soft delete

Enable via config (features.soft_delete) and a column like deleted_at. The filter is appended only if the column exists.

$db->table('users')->where('id','=', $id)->delete();     // sets deleted_at
$db->table('users')->onlyTrashed()->get();
$db->table('users')->withTrashed()->get();
$db->table('users')->where('id','=', $id)->restore();
$db->table('users')->where('id','=', $id)->forceDelete();

policy

Register a guard to block unsafe operations based on context.

$db->policy(function(array $ctx){
  if (($ctx['action'] ?? '') === 'delete' && str_starts_with($ctx['table'] ?? '', 'system_')) {
    throw new RuntimeException('Deleting system tables is not allowed.');
  }
});

middleware

Wrap execution for audit, metrics, multitenancy…

$db->use(function(array $ctx, callable $next){
  $t0 = microtime(true);
  $res = $next($ctx);
  error_log("[dbf] {$ctx['type']} {$ctx['table']} in ".round((microtime(true)-$t0)*1000,1)."ms");
  return $res;
});

Raw SQL

For specialized queries. Parameters remain bound safely.

$rows = $db->raw('SELECT * FROM users WHERE email LIKE ?', ['%ndtan.net%']);
$one  = $db->raw('SELECT * FROM users WHERE email = :e', ['e'=>'a@ndtan.net']);

Security

  • Prepared statements for all values.
  • Identifier quoting by driver for table/column names.
  • IN-list guard: whereIn() throws if parameter count is too large.
  • Readonly mode: block DML with setReadonly(true).
  • Soft-delete guard: filter applied only if the column exists.
  • Policies to deny dangerous actions.

Config reference

$db = new ndtan\DBF([
  'type'     => 'mysql',       // mysql|mariadb|pgsql|sqlite|sqlsrv
  'host'     => '127.0.0.1',
  'database' => 'app',
  'username' => 'root',
  'password' => 'secret',
  'charset'  => 'utf8mb4',
  'prefix'   => 'ndt_',        // optional table prefix
  'readonly' => false,
  'features' => [
    'soft_delete'   => ['enabled' => true, 'column' => 'deleted_at', 'mode' => 'timestamp'],
    'max_in_params' => 1000
  ]
]);

DSN (URI) shortcuts

$db = new ndtan\DBF('mysql://root:secret@127.0.0.1/app?charset=utf8mb4');
$db = new ndtan\DBF('sqlite::memory:');