NSY Model & DB — User Tutorial

Query builder for NSY (System/Core/DB.php, System/Core/NSY_DB.php). Unified via NSY_DB::connect() (single source, declare(strict_types=1), PDO::ERRMODE_EXCEPTION), declare(strict_types=1) in both.

• DB: System\Core\DB — DML/query (SELECT/INSERT/UPDATE/DELETE) + fetch*, exec, multiInsert
• NSY_DB: System\Core\NSY_DB — low-level factory (mysql/dblib/pgsql/sqlsrv) used by DB and NSY_Migration
• Config: env.php connections (4: primary/mysql/pgsql/sqlsrv) + System/Config/App.php (transaction, csrf_token)

Table of Contents

1. Connections
2. Basic Query
3. Binding
4. Fetching
5. Execute & Multi Insert
6. Transactions
7. Migrated Connection (NEW)
8. Quick Reference

Connections

Connections are declared once in env.php under connections and selected by name.

// env.php
'connections' => [
  'primary'   => ['DB_CONNECTION' => 'mysql', 'DB_HOST' => 'localhost', 'DB_NAME' => 'nsy', ...],
  'secondary' => ['DB_CONNECTION' => 'pgsql', ...],
  'mysql'     => [...], // blank template
  'pgsql'     => [...],
  'sqlsrv'    => [...],
]

DB::connect()->query($q)->fetchAll();              // primary
DB::connect('secondary')->query($q)->fetchAll();   // secondary
DB::connect('pgsql')->query($q)->fetchAll();       // custom

DB::connect(string $conn='primary'): object System/Core/DB.php:33 delegates to NSY_DB::connect($conn) System/Core/NSY_DB.php:24 — one match selects the driver (no duplicated switch).

Environment keys

Missing DB_CONNECTION, DB_HOST or DB_NAME raises a configuration error through NSY_Desk::staticErrorHandler().

Supported drivers

Any other value falls through to handleUnknownDriver() and reports an error.

Connection options

normalizeOptions() applies secure defaults, which your DB_ATTR overrides:

\PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES   => false,

Every attempt is logged on the db channel (driver / host / port / dbname + duration) — credentials are never logged.

Backwards-compatible proxies

NSY_DB also exposes the per-driver entry points connectMysql(), connectDblib() and connectPgsql() (generic DSN) plus connectSqlsrv() (SQLSRV DSN). They are kept for backwards compatibility; for new code prefer connect($conn).

Basic Query

$q = 'SELECT * FROM users WHERE id = :id';
DB::connect()->query($q)->vars([':id' => 2])->fetch();
// Same: DB::connect('primary')->query($q)->vars([...])->fetchAll();

query(string $q) validates via is_filled() → NSY_Desk::staticErrorHandler().

Binding

// Simple bind (auto)
$q = "SELECT * FROM users WHERE id = :id";
DB::connect()->query($q)->vars([':id' => 2])->fetch();

// Typed BINDVALUE/BINDPARAM
$vars = [':id' => [2, PAR_INT], ':name' => ['%a%', PAR_STR]];
DB::connect()->query($q)->vars($vars)->bind(BINDVAL)->fetch();
DB::connect()->query($q)->vars($vars)->bind(BINDPAR)->fetch();

Fetching

DB::connect()->query($q)->vars()->style(FETCH_ASSOC)->fetchAll(); // all rows
DB::connect()->query($q)->fetch();              // single row
DB::connect()->query($q)->fetchColumn(0);      // single value
DB::connect()->query($q)->rowCount();          // affected rows

// FETCH_* constants: FETCH_NUM, FETCH_ASSOC, FETCH_BOTH, FETCH_COLUMN, FETCH_OBJ, FETCH_KEY_PAIR...

Execute & Multi Insert

// Update/delete/insert
$q = "UPDATE users SET name = :name WHERE id = :id";
DB::connect()->query($q)->vars($p)->bind()->exec(); // true, with transaction + CSRF check

// Multi insert
$q = "INSERT INTO users (id, name, user_name)";
$arr = [[1,'A','a'], [2,'B','b']];
DB::connect()->query($q)->vars($arr)->multiInsert();

exec() checks config_app('csrf_token') via SecurityMiddleware::validateAdvancedCSRF + transaction (on → beginTransaction/commit/rollback).

Transactions

// Auto via config — each exec() write is wrapped on its own
// System/Config/App.php: 'transaction' => config_env('DB_TRANSACTION') ?? 'off'
DB::connect()->query($q)->vars($p)->exec(); // handles begin/commit/rollback internally

Recommended: DB::transaction() commits on success and rolls back + re-throws on error.

use System\Core\DB;

$orderId = DB::transaction(function ($pdo) use ($data) {
    $id = qb('orders')->insert($data);
    qb('order_items')->insertBatch($items);
    return $id;
}); // commits here; on any exception it rolls back and re-throws

Manual control, when you need to decide inside the flow:

DB::beginTransaction();
try {
    qb('accounts')->where('id', 1)->decrement('balance', 100);
    qb('accounts')->where('id', 2)->increment('balance', 100);
    DB::commit();
} catch (\Throwable $e) {
    DB::rollBack();
    throw $e;
}

DB::inTransaction(); // bool — is a transaction open on the shared connection?

DB::transaction() / beginTransaction() / commit() / rollBack() / inTransaction() are the public API and operate on the shared connection, so qb() calls inside the callback join the same transaction. The lower-level beginTrans() / commitTrans() / rollbackTrans() are protected (internal). Do not nest DB::transaction() with the auto mode above (transaction = on).

Migrated Connection (NEW)

Before (duplicated):

switch(config_db(...)) { case 'mysql': NSY_DB::connectMysql(...) }

After (unified):

// System/Core/DB.php:33 & System/Core/NSY_Migration.php:27
NSY_DB::connect(string $conn): ?PDO // match driver → createConnection/buildDsn/normalizeOptions

Benefits: declare(strict_types=1), ?PDO type, buildDsn() + quoteIdent() safety, normalizeOptions() defaults (ERRMODE_EXCEPTION, FETCH_ASSOC).

Quick Reference

Related: System/Core/DB.php:33, System/Core/NSY_DB.php:24, env.php connections, System/Core/NSY_Migration.php (DDL counterpart).