use_cases

Reading from or writing to the database from a controller or model

Handling a query result set

Deciding between raw SQL and the fluent query builder

Diagnosing a query that returns nothing or the wrong rows

Holding a database handle in a long-lived worker or daemon

Reading a table's columns, types or foreign keys from code

Pramnos Database API Guide

This guide explains how to properly use the Pramnos database API for database operations in the Pramnos MVC framework.

v1.2 Update: A new fluent QueryBuilder API is now available for more expressive query construction. See Pramnos_QueryBuilder_Guide.md for modern query patterns. The Database::query() and prepareQuery() methods remain unchanged and fully supported.

Overview

The Pramnos framework provides a database abstraction layer that must be used for all database operations. The key principle is to always use prepareQuery() for parameter binding, then query() for execution.

For new code, consider using the QueryBuilder API:

// Modern approach (v1.2+)
$users = $db->queryBuilder()
    ->from('#PREFIX#users')
    ->where('active', 1)
    ->get();

// Legacy approach (still supported)
$sql = $db->prepareQuery("SELECT * FROM users WHERE active = %d", 1);
$result = $db->query($sql);

Database Access in Controllers

In Pramnos controllers, the database connection is accessed through the application instance:

// CORRECT: Access database through application
$sql = $this->application->database->prepareQuery("SELECT * FROM users WHERE id = %d", $userId);
$result = $this->application->database->query($sql);

// INCORRECT: Direct access to database property
$sql = $this->application->database->prepareQuery("..."); // This will cause "Call to a member function on null" error

Important: Always use $this->application->database in controllers, not $this->application->database.

Core Pattern

The Two-Step Process

Prepare Query: Use prepareQuery() with printf-style formatting
Execute Query: Use query() to execute the prepared query

// CORRECT: Pramnos pattern with printf-style formatting
$sql = $this->application->database->prepareQuery("SELECT * FROM users WHERE email = %s AND status = %d", $email, $status);
$result = $this->application->database->query($sql);

// INCORRECT: Don't use ? placeholders
$this->application->database->prepareQuery("SELECT * FROM users WHERE id = ?", [$userId]); // Wrong syntax!

Printf-Style Format Specifiers

%s - String values
%d - Integer values
%% - Literal % character

Note: Only %s and %d are commonly used in the Pramnos framework.

A literal `%` in the SQL, and `LIKE`

prepareQuery() is sprintf underneath, so a % in the query string is a format directive unless you say otherwise. Two rules follow, and the second used to be a crash:

With arguments, write %% for a literal percent. That is what the method documents and what every LIKE needs:

// Wildcards, with a bound argument beside them
$sql = $db->prepareQuery(
    "SELECT * FROM `#PREFIX#users` WHERE `userid` = %d AND `username` LIKE 'a%%'",
    7
);

With no arguments, a single % is left alone. Until 1 September 2026 it was not: vsprintf ran whether or not there was anything to substitute, so

$db->prepareQuery("SELECT * FROM `#PREFIX#queueitems` WHERE `payload` LIKE '%ApplyStart%'");

raised ValueError: Missing padding character — sprintf reads the trailing %' as «pad with the next character» and there is no next character. The @ in front of vsprintf suppressed warnings, not exceptions, so this was a fatal error on an ordinary query. It no longer calls vsprintf when there is nothing to substitute.

%% still collapses to % on that path, because callers have relied on that for years — a query holding DATE_FORMAT(\created`, '%%c')with no arguments still reaches the database as'%c'`.

A %s or %d with no argument passes through to the database instead of raising, which is a malformed query rather than a fatal. If you write a placeholder, pass its value.

What a datatable search actually looks for

_getPaginated() builds its WHERE from the search terms a datatable posts, and four of the rules are not obvious from the outside. Worth knowing before deciding a filter is broken.

A Greek word is searched without its final sigma. Γιάννης becomes %Γιάννη%. Greek inflects the ending — a visitor types the nominative and the row holds the genitive — so dropping the last ς/σ is the cheapest stemming there is and it is why the search box feels like it works. Only the ending: Κώστας becomes Κώστα%, not Κώτα%.

On PostgreSQL, a Greek term is compared through unaccent().

unaccent(CAST(a."name" AS TEXT)) ILIKE unaccent('%Γιάννη%')

So Γιάννης, ΓΙΑΝΝΗΣ and Γιαννης all find each other — a form filled in caps and an import that lost its accents are both ordinary. A term with no Greek in it stays plain ILIKE, without wrapping either side in a function, which on a large table is the difference between using an index and not.

A numeric column is matched with =, not LIKE. Filtering an integer column by 9 with LIKE '%9%' returns 9, 19, 29, 90 and 1999, which reads as a broken filter rather than a broad one. The column's type comes from Model::$columnCache, so this only applies where the model has read the schema — a joined table whose alias cannot be resolved falls back to LIKE.

A term that already contains % is used as it is. Γιαν% is left anchored rather than wrapped again, because the caller has said where the wildcard goes.

And one rule that is a boundary rather than a convenience: a search field that is not in the field list produces no condition at all. The names arrive from the query string, so the list is what keeps a request parameter out of the statement. A filter that silently does nothing on a joined listing is usually the qualified-name mapping — send title and the list may hold posts.title, which the builder maps, but only for a name that is there.

Writing a point: `'geometry'` and the coordinate order

insertDataToTable() and updateTableData() take a 'type' => 'geometry' field and convert it to PostGIS for you, on PostgreSQL. Three shapes are accepted:

$db->insertDataToTable('places', [
    // a "lat, lon" string — what a form posts
    ['fieldName' => 'location', 'value' => '37.9838, 23.7275',                          'type' => 'geometry'],
    // a named pair — what an API request carries
    ['fieldName' => 'location', 'value' => ['latitude' => 37.9838, 'longitude' => 23.7275], 'type' => 'geometry'],
    // anything else: passed to ST_GeomFromText(), so a polygon or a line works
    ['fieldName' => 'area',     'value' => 'POLYGON((23.7 37.9, 23.8 37.9, 23.8 38.0, 23.7 37.9))', 'type' => 'geometry'],
]);

The first two both compile to ST_SetSRID(ST_MakePoint(23.7275, 37.9838), 4326) — longitude first, because that is what ST_MakePoint() takes while people write latitude first. Getting it backwards is silent: nothing errors, every insert succeeds, and ST_MakePoint(37.9838, 23.7275) is a spot in the Indian Ocean rather than Athens. If a map is drawing points in the wrong place and the numbers in the table look right, this is the first thing to check.

Three details worth having:

0, 0 is a coordinate, not an empty value — the origin is in the Gulf of Guinea and the pattern accepts it. So is a negative number in either position.
The conversion is gated on the driver, not the column. On MySQL the value is written through unchanged, because ST_MakePoint and ST_SetSRID are PostGIS spellings — MySQL names its spatial functions differently and takes the SRID as an argument rather than a wrapper.
Each method has its own copy of the conversion. A change to one and not the other gives you a row that is inserted correctly and moves when it is edited.

Reading a table's schema: `getColumns()`

$result = $db->getColumns('stations');
while ($result->fetch()) {
    $result->fields['Field'];       // column name
    $result->fields['Type'];        // 'varchar'      — the bare type
    $result->fields['ColumnType'];  // 'varchar(255)' — as declared (MySQL)
    $result->fields['Null'];        // 'YES' / 'NO'
    $result->fields['Comment'];
    $result->fields['Key'];         // 'PRI' on the primary key
    $result->fields['ForeignKey'], $result->fields['ForeignTable'];
}

Columns come back in the order the table declares them, and the result is cached for an hour per table, because a schema rarely changes and introspection is not cheap.

Two things follow from that cache, and both have a name:

A schema change through the schema builder invalidates it. Every DDL method on SchemaBuilder — createTable(), alterTable(), dropTable(), dropTableIfExists(), renameTable() — flushes the table it touched, so a migration is visible to the next read. A raw $db->query('ALTER TABLE …') does not; call $db->forgetColumns($table) yourself if you do that.

Pass $fresh when a stale answer would be wrong rather than merely old:

$db->getColumns($table, $schema, false, true);   // never from the cache

Code generators do this, because the framework's documented order of work is create:migration, migrate, create:crud — so a generator runs minutes after the schema changed, and an hour-old answer describes the table as it was before the migration.

On PostgreSQL, PrimaryKey and ForeignKey come back as booleans rather than MySQL's 'PRI' / 1; ForeignTable and ForeignColumn name the referenced table and column on both. Code reading these should accept either spelling — true, 't' and 1 all mean yes, depending on how the row was cast on the way out.

Fixed 2026-08-24 on PostgreSQL, two faults in the flags. ForeignKey was computed from information_schema.constraint_column_usage, and for a FOREIGN KEY that view lists the column of the referenced table — so on streams(station_id) → stations(id) the flag was true on id, the primary key, and false on station_id. It was never true for a foreign key on any table, so every generator gated on it saw none: the SPA form rendered a number input where its searchable picker belongs, the MVC form a bare input instead of its select2, and unsigned is decided from the same flag so generated migrations differed too. ForeignTable beside it was correct all along.

PrimaryKey answered correctly through the old view by coincidence — for a PRIMARY KEY constraint it does list the table's own columns — so the two looked symmetric while one of them was luck. Both now read key_column_usage, which is the view that lists what this table constrains.

Fixed 2026-08-24, three faults in one method. Nothing invalidated the cache at all, so the sequence above wrote a model and a form for the old columns and reported success — and because the store is shared, re-running the command did not clear it either. Neither query had an ORDER BY, so column order was whatever the server felt like, usually alphabetical. And only DATA_TYPE was selected on MySQL, so tinyint(1) — the boolean convention — arrived as tinyint and every generated form rendered every boolean column as a number input. ColumnType is the declared type; Type is unchanged for callers that read it.

Real Prepared Statements: `preparedQuery()`

prepareQuery() + query() interpolates escaped values into the SQL string. preparedQuery() is different: it runs the statement through the driver's own prepared-statement engine (pg_prepare/pg_execute, or mysqli), so values are bound as parameters and never become part of the SQL text.

It is the PDO-style bridge, added for code migrating off a raw \PDO handle, and it takes the SQL verbatim — PostgreSQL RETURNING, ON CONFLICT, DISTINCT ON and stored-function calls all survive untouched.

$db = \Pramnos\Database\Database::getInstance();

// Named placeholders — a name may repeat, and binds at each occurrence.
$result = $db->preparedQuery(
    'SELECT * FROM stations WHERE ownerid = :owner AND status = :status',
    ['owner' => $userId, 'status' => 1]
);

// Positional placeholders.
$result = $db->preparedQuery(
    'SELECT * FROM stations WHERE id IN (?, ?)',
    [$first, $second]
);

The two styles are mutually exclusive within one statement, exactly as with PDO. Keys may be written with or without the leading colon ('owner' or ':owner').

Return value. A Result on success, or false when the statement could not be prepared. It does not throw for that case unless the connection is in strict mode (see throwOnError). A caller writing $db->preparedQuery(...) ?: [] to keep a read failure from taking a page down is a deliberate pattern — but be aware that it makes a broken query and an empty table look the same. Turn on throwOnError while developing so the difference is loud.

Missing bindings throw. A :name with no value in the array is a programming error and raises \InvalidArgumentException, as does a positional count that does not match the number of ?.

What counts as SQL, and what does not

Both preparedQuery() and the %s/%d engine behind execute() have to decide which parts of a statement are really SQL before they can find placeholders in it. Two kinds of text are skipped:

Single-quoted string literals. '%' in a LIKE pattern is data, and ':notabind' is a string, not a placeholder. '' is an escaped quote and keeps the literal open.
Comments. -- and /* … */ everywhere, plus # on MySQL. Nothing inside a comment is read: a placeholder written there is prose, and — the part that matters in practice — an apostrophe there is just an apostrophe.

That last point is worth stating plainly, because the parser did not always know it:

// Fine. The possessive in the comment is prose, and :minutes still binds.
$db->preparedQuery(
    "SELECT /* a JOIN's clause */ (:minutes || ' minutes')::interval AS v",
    ['minutes' => 15]
);

Before this was handled, the apostrophe in JOIN's read as the start of a string literal, so every placeholder after it was left in the SQL unbound; the statement failed and preparedQuery() answered false. In a consuming application that turned a working page into an empty one, with no error anywhere — the read did ?: [] and the page truthfully reported that it had no rows.

The two dialects genuinely disagree, and the framework follows each server's own rule rather than picking one:

	MySQL	PostgreSQL
`#` to end of line	comment	operator — still SQL
`--` followed by space	comment	comment
`--` with no space (`5--3`)	arithmetic → `8`	comment → `5`
`/* a /* b / c /`	ends at the first `*/`	nests; ends at the last
`/!40101 … /`	executed — placeholders inside it bind	ordinary comment

You do not need to do anything with this table; it is here so that a statement which behaves differently on two servers has a documented reason. Write comments however you like, apostrophes included.

A prepared statement whose connection died underneath it

Nothing to do; worth knowing it happens and that it is handled, because the failure it prevents is quiet.

Every connection has an idle timeout — MySQL's wait_timeout is eight hours by default, a managed PostgreSQL or a connection pooler is usually far less — and the processes that hold a handle longest are the ones nobody is watching: a queue worker, a scheduled command, a daemon. They prepare a statement once and execute it for hours. A restarted database, a failover, an operator's KILL clearing a lock, and a pooler recycling a backend all produce the same thing.

execute() notices that the connection is gone rather than that the statement failed, reconnects, re-prepares and runs it once. Once, and only for that condition: re-running a statement whose result was never seen is harmless for a SELECT and a duplicate for an INSERT, so a retry is only correct when the server cannot have applied it — which is what «the connection was already gone» means. A retry on any error would silently double writes.

Two things about it are worth writing down, because both were true for a long time and both were invisible:

The retry could not fire on MySQL. The gate read mysqli_errno() after an execute() that returned false — but mysqli's default error mode has been MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT since PHP 8.1, so execute() raises mysqli_sql_exception instead of returning false. The branch holding the gate was unreachable code. The failure the retry existed to survive was the one failure it could not, and the symptom was an uncaught exception in a process nobody is watching.

The retry could not fire on PostgreSQL either, for two different reasons. The gate asked pg_connection_status(), which reports the last known state rather than polling: with the backend already terminated it answers OK before any operation, still OK at the instant the failing pg_execute() returns, and BAD only afterwards — so it was consulted at the one moment the answer was wrong. It reads the error text now, which says «server closed the connection unexpectedly» right there. And once that was fixed the retry still failed, because the prepared-statement cache is keyed on md5($query) alone: after reconnecting it handed back a plan name belonging to a session PostgreSQL had already forgotten. A plan does not outlive its session, so the cache is scoped to the connection that made it.

The same holds for a plain query(), and runQuery()'s reconnect is the whole of it: the statement fails, isConnectionGone() distinguishes a dead connection from a bad statement, the connection is replaced, and the statement is sent once more. On MySQL that used to come out as an uncaught mysqli_sql_exception for the same reason as above — the gate read mysqli_errno() after a call that throws. (On PostgreSQL that path already worked: a failed pg_query marks the connection immediately, unlike a failed pg_execute.)

getConnection() does not probe the handle, and on MySQL it must not. A probe there means a statement of the framework's own before every statement of yours, and that has two costs:

a round trip per query — measured by a consuming application at 355 pings in 476 statements on one page load;
your second statement stops describing your first. FOUND_ROWS(), ROW_COUNT() and LAST_INSERT_ID() are per-connection state: they report on the last statement the connection ran, and with a probe in between that is the probe. A limited select over seven rows reported a total of one, and an UPDATE touching three rows reported -1.

So liveness is remembered, not asked: isConnectionGone() already decides it at the one moment the answer is knowable — when a statement has just failed — and getConnection() replaces a link that has been marked. PostgreSQL additionally checks pg_connection_status(), which is a local read of a cached state rather than a statement, so it costs nothing.

isConnectionAlive() is still there and still probes with SELECT 1. It is a reasonable thing to ask deliberately; just know that it costs a round trip and becomes the connection's last statement, so anything reading per-connection state afterwards describes the probe.

If you keep a Database handle across a long idle period yourself, you need nothing extra — the first statement after the idle fails, reconnects and is retried. If you hold a raw mysqli/PgSql\Connection from getConnectionLink(), you own that problem.

A paginated list that groups

_getPaginated() takes a $group clause, and two shapes of it behave differently in a way worth knowing before you use it.

Grouping by the primary key — a join that multiplies rows, grouped back down to one per parent — works as it always has, and can return models:

$this->_getPaginated(
    20, $page, null, 'ORDER BY a.title', null, null, false,
    'LEFT JOIN #PREFIX#comments c ON c.postid = a.postid',
    'a.postid, a.title, COUNT(c.commentid) AS comments',
    'GROUP BY a.postid, a.title'
);

Grouping by anything else — «a page of categories with counts» — must name its fields and ask for arrays rather than models:

$this->_getPaginated(
    20, $page, null, 'ORDER BY a.category', null, null, false, '',
    'a.category, COUNT(*) AS total',
    'GROUP BY a.category',
    false                              // ← arrays: a group is not a row
);

Two reasons, and neither is arbitrary. The primary key is normally forced into the select, because a returned model without its key cannot be reloaded or saved — and in a grouped query a column that is neither grouped nor aggregated is invalid SQL, refused by PostgreSQL outright and by MySQL under ONLY_FULL_GROUP_BY. So when the group clause does not name the key, the caller's field list is used exactly as given. And a model is a row: with no key to index by, the result is a list, which is what $returnAsModels = false returns.

The total is counted as SELECT COUNT(*) FROM (…) as grouped_query rather than with count(a.key), because the latter counts rows within each group. Getting that wrong is invisible on the first page: a listing of twelve rows in three groups would report the size of the first group as the total, the page arithmetic would follow that number, and the last page would be empty or unreachable — on a screen that renders perfectly.

Result Handling

CRITICAL: Pramnos query results use different patterns for single vs multiple records:

Single Record Queries

For queries that return a single record (e.g., SELECT * FROM users WHERE id = %d or SELECT COUNT(*) as total):

$sql = $database->prepareQuery("SELECT * FROM users WHERE id = %d", $userId);
$result = $database->query($sql);

// Single record - NO fetch() needed, NO [0] index
if ($result->numRows > 0) {
    $userData = $result->fields; // Direct associative array of column => value
    $userName = $result->fields['name'];
    $userEmail = $result->fields['email'];
}

// COUNT queries (single result)
$sql = $database->prepareQuery("SELECT COUNT(*) as total FROM users");
$result = $database->query($sql);
$count = $result->fields['total']; // Direct access, NOT $result->fields[0]['total']

Multiple Record Queries

For queries that return multiple records (e.g., SELECT * FROM applications ORDER BY created_at):

$sql = $database->prepareQuery("SELECT * FROM applications WHERE user_id = %d ORDER BY created_at DESC", $userId);
$result = $database->query($sql);

// Multiple records - USE fetch() to iterate
$applications = [];
while ($result->fetch()) {
    $applications[] = $result->fields; // Each row as associative array
}

Common Mistakes to Avoid

❌ WRONG - Never use [0] index for single records:

// These patterns are INCORRECT for Pramnos framework:
$user = $result->fields[0];           // Wrong!
$count = $result->fields[0]['total']; // Wrong!

✅ CORRECT - Direct access for single records:

// These patterns are CORRECT for Pramnos framework:
$user = $result->fields;           // Correct!
$count = $result->fields['total']; // Correct!

Key Points:

Single records: Check $result->numRows > 0, then access $result->fields directly (no [0] index)
Multiple records: Use while ($result->fetch()) to iterate through rows
Never use $result->fields[0] - this is from other frameworks, not Pramnos
Access column data via $result->fields (associative array)

Database Operations

SELECT Operations

// Single record
$sql = $this->application->database->prepareQuery("SELECT * FROM users WHERE id = %d", $userId);
$result = $this->application->database->query($sql);
if ($result->numRows > 0) {
    $user = $result->fields; // Associative array of columns
}

// Multiple records
$sql = $this->application->database->prepareQuery("SELECT * FROM applications WHERE user_id = %d ORDER BY created_at DESC", $userId);
$result = $this->application->database->query($sql);
$applications = [];
while ($result->fetch()) {
    $applications[] = $result->fields;
}

// String and integer parameters
$sql = $this->application->database->prepareQuery("SELECT * FROM users WHERE email = %s AND status = %d", $email, $status);
$result = $this->application->database->query($sql);

The Modern Way: Fluent Query Builder (v1.2+)

While prepareQuery() and query() are the foundational patterns, v1.2 introduces a fluent Query Builder for cleaner, more maintainable code, especially for complex queries.

Advantages:

No need to remember %s / %d specifiers.
Automatic table quoting.
Easier joins and conditions.
Better cross-database compatibility (MySQL/PostgreSQL).

Examples:

// SELECT with conditions and ordering
$users = $this->application->database->queryBuilder()
    ->from('#PREFIX#users')
    ->where('status', 1)
    ->where('role', 'admin')
    ->orderBy('last_login', 'desc')
    ->limit(10)
    ->get();

// Complex JOINs
$applications = $this->application->database->queryBuilder()
    ->select('a.*', 'u.username as owner_name')
    ->from('applications a')
    ->join('users u', 'u.id', '=', 'a.user_id')
    ->where('a.status', 'active')
    ->get();

// INSERT with auto-binding
$newId = $this->application->database->queryBuilder()
    ->table('#PREFIX#users')
    ->insert([
        'username' => 'newuser',
        'email' => 'user@example.com',
        'status' => 1
    ]);

Raw Expressions

If you need database-specific functions (like PostGIS or native MySQL functions), use raw():

$locations = $this->application->database->queryBuilder()
    ->from('locations')
    ->whereRaw("ST_Distance(geom, ST_MakePoint(?, ?)) < 1000", [23.7, 37.9])
    ->get();

INSERT Operations

$sql = $this->application->database->prepareQuery(
    "INSERT INTO applications (client_id, client_secret, name, redirect_uri, user_id, created_at) VALUES (%s, %s, %s, %s, %d, %s)",
    $clientId, $clientSecret, $name, $redirectUri, $userId, date('Y-m-d H:i:s')
);
$result = $this->application->database->query($sql);

// Get inserted ID
$insertId = $this->application->database->getLastInsertId();

UPDATE Operations

$sql = $this->application->database->prepareQuery(
    "UPDATE applications SET name = %s, redirect_uri = %s WHERE id = %d AND user_id = %d",
    $name, $redirectUri, $id, $userId
);
$result = $this->application->database->query($sql);

DELETE Operations

// Simple delete
$sql = $this->application->database->prepareQuery("DELETE FROM user_tokens WHERE expires_at < %s", date('Y-m-d H:i:s'));
$result = $this->application->database->query($sql);

// With parameters
$sql = $this->application->database->prepareQuery("DELETE FROM applications WHERE id = %d AND user_id = %d", $id, $userId);
$result = $this->application->database->query($sql);

Queries Without Parameters

// For queries without parameters, you can use query() directly
$result = $this->application->database->query("SELECT COUNT(*) as count FROM users");

Model Integration

In Model Classes

Models extend Pramnos\Application\Model and should use the database API through $this->application->database (passed in constructor):

class Application extends \Pramnos\Application\Model
{
    public function loadByClientId($clientId)
    {
        $sql = $this->application->database->prepareQuery("SELECT * FROM applications WHERE client_id = %s", $clientId);
        $result = $this->application->database->query($sql);
        
        if ($result->numRows > 0) {
            foreach (array_keys($result->fields) as $key) {
                if (property_exists($this, $key)) {
                    $this->$key = $result->fields[$key];
                }
            }
            return true;
        }
        return false;
    }
    
    public function save()
    {
        if (isset($this->id) && $this->id > 0) {
            // Update existing record
            $sql = $this->application->database->prepareQuery(
                "UPDATE applications SET name = %s, client_secret = %s, redirect_uri = %s WHERE id = %d",
                $this->name, $this->client_secret, $this->redirect_uri, $this->id
            );
            $this->application->database->query($sql);
        } else {
            // Insert new record
            $sql = $this->application->database->prepareQuery(
                "INSERT INTO applications (name, client_id, client_secret, redirect_uri, created_at) VALUES (%s, %s, %s, %s, %s)",
                $this->name, $this->client_id, $this->client_secret, $this->redirect_uri, date('Y-m-d H:i:s')
            );
            $this->application->database->query($sql);
            $this->id = $this->application->database->getLastInsertId();
        }
    }
}

Model Instantiation

Models should be instantiated with the controller:

// In a Controller
$model = new \Model($this);
$model->load($id);

Controllers and Database Access

In Controller Classes

Controllers should use $this->application->database for database operations:

class Token extends \Pramnos\Application\Controller
{
    public function revokeToken()
    {
        $token = $_POST['token'] ?? '';
        
        // Revoke access token
        $sql = $this->application->database->prepareQuery("UPDATE user_tokens SET revoked = 1 WHERE access_token = %s", $token);
        $this->application->database->query($sql);
        
        // Also revoke refresh token if it matches
        $sql = $this->application->database->prepareQuery("UPDATE user_tokens SET revoked = 1 WHERE refresh_token = %s", $token);
        $this->application->database->query($sql);
        
        $this->response(['revoked' => true]);
    }
}

Common Patterns

Checking if Record Exists

$sql = $this->application->database->prepareQuery("SELECT COUNT(*) as count FROM applications WHERE client_id = %s", $clientId);
$result = $this->application->database->query($sql);
$exists = false;
if ($result->numRows > 0) {
    $exists = $result->fields['count'] > 0;
}

Getting Multiple Records

$sql = $this->application->database->prepareQuery("SELECT * FROM user_tokens WHERE user_id = %d AND revoked = 0 ORDER BY created_at DESC", $userId);
$result = $this->application->database->query($sql);

$tokens = [];
while ($result->fetch()) {
    $tokens[] = $result->fields;
}

Complex Queries with JOINs

$sql = $this->application->database->prepareQuery(
    "SELECT ut.*, a.name as app_name 
     FROM user_tokens ut 
     JOIN applications a ON ut.client_id = a.client_id 
     WHERE ut.user_id = %d AND ut.expires_at > %s",
    $userId, date('Y-m-d H:i:s')
);
$result = $this->application->database->query($sql);
while ($result->fetch()) {
    // Process $result->fields
}

Best Practices

1. Always Use Parameter Binding

Never concatenate user input directly into SQL queries:

// WRONG - SQL injection risk
$result = $this->application->database->query("SELECT * FROM users WHERE email = '{$email}'");

// CORRECT - Use printf-style parameter binding
$sql = $this->application->database->prepareQuery("SELECT * FROM users WHERE email = %s", $email);
$result = $this->application->database->query($sql);

2. Handle Database Errors

Always check if query operations succeed:

try {
    $sql = $this->application->database->prepareQuery("INSERT INTO users (email) VALUES (%s)", $email);
    $result = $this->application->database->query($sql);
    // Handle success
} catch (Exception $e) {
    // Handle database error
    error_log("Database error: " . $e->getMessage());
    return false;
}

3. Use Transactions for Multiple Operations

For operations that need to be atomic:

try {
    $this->application->database->beginTransaction();
    
    // First operation
    $sql1 = $this->application->database->prepareQuery("INSERT INTO applications (name, client_id) VALUES (%s, %s)", $name, $clientId);
    $this->application->database->query($sql1);
    
    // Second operation
    $sql2 = $this->application->database->prepareQuery("INSERT INTO user_tokens (user_id, token) VALUES (%d, %s)", $userId, $token);
    $this->application->database->query($sql2);
    
    $this->application->database->commit();
} catch (Exception $e) {
    $this->application->database->rollback();
    throw $e;
}

4. Model Loading Patterns

Follow consistent patterns for loading data in models:

// Single record by ID
public function loadById($id)
{
    $sql = $this->application->database->prepareQuery("SELECT * FROM table_name WHERE id = %d", $id);
    $result = $this->application->database->query($sql);
    
    if ($result->numRows > 0) {
        foreach (array_keys($result->fields) as $key) {
            if (property_exists($this, $key)) {
                $this->$key = $result->fields[$key];
            }
        }
        return true;
    }
    return false;
}

// Multiple records
public function loadByUserId($userId)
{
    $sql = $this->application->database->prepareQuery("SELECT * FROM table_name WHERE user_id = %d", $userId);
    $result = $this->application->database->query($sql);
    
    $records = [];
    while ($result->fetch()) {
        $records[] = $result->fields;
    }
    return $records;
}

5. Validate Input Before Database Operations

public function updateApplication($id, $name, $redirectUri)
{
    // Validate input
    if (empty($name) || empty($redirectUri)) {
        throw new \InvalidArgumentException('Name and redirect URI are required');
    }
    
    if (!filter_var($redirectUri, FILTER_VALIDATE_URL)) {
        throw new \InvalidArgumentException('Invalid redirect URI');
    }
    
    // Proceed with database operation
    $sql = $this->application->database->prepareQuery("UPDATE applications SET name = %s, redirect_uri = %s WHERE id = %d", $name, $redirectUri, $id);
    $this->application->database->query($sql);
}

Who ran this query: the PostgreSQL session variables

On PostgreSQL the framework labels every connection, so a query caught in pg_stat_activity or a slow-query log can be attributed to a request rather than to a process id. Database::setTrackingInfo() writes them, and Api::execute() and the console commands call it for you.

SELECT pid, application_name, query,
       current_setting('app.userid',       true) AS userid,
       current_setting('app.request_path', true) AS path,
       current_setting('app.client_ip',    true) AS ip
FROM pg_stat_activity
WHERE state = 'active';

Variable	What is in it
`application_name`	The application, plus `_u<userid>` when somebody is signed in, plus the connecting address
`app.userid`	The signed-in user's id, or `guest`
`app.session_id`, `app.client_ip`, `app.user_agent`	The caller
`app.request_path`, `app.http_method`, `app.request_time`	The request

app.userid is guest for anonymous traffic, not null. That is the distinction worth knowing when you write the WHERE: a variable that was never set reads as null, and one set to guest reads as guest. Anonymous is a value here, not an absence — which matters because anonymous traffic is most of what anybody is chasing when they open that view: every login attempt, every public endpoint, every request whose token has just expired.

Pass your own alongside them, and they are readable under app.:

$this->database->setTrackingInfo($userId, 'Reporting', [
    'userid' => $userId,
    'tenant' => $tenantId,      // becomes app.tenant
]);

A null value is skipped rather than written, so current_setting('app.tenant', true) answers null for it — "there was no tenant", which is a different answer from the empty string. The one exception is userid, which has a default: a null there gives you guest.

Nothing here can fail a request. The writes are suppressed and their result is not read, because this runs while the connection is being opened and tracing must never be the reason something does not load.

Error Handling and Best Practices

How database failures surface (and `throwOnError`)

The two query paths signal failure differently, and the prepared path also differs by driver — know which one you are on:

Path	On a SQL/connection error	Notes
`query($sql)` / `runQuery()` (raw execution)	Throws `\Exception` via `setError()`	The message is `"<errno>:<text> ::: SQL QUERY: <sql>"`. Wrap it in try/catch.
`execute($sql, ...)` on PostgreSQL (prepared statements — the fluent Query Builder runs through here)	Returns `false` when the statement cannot be prepared (`pg_prepare` returns false)	Silent: a caller that ignores the return value swallows the error.
`execute($sql, ...)` on MySQL	Throws `mysqli_sql_exception`	Since PHP 8.1 `mysqli` defaults to `MYSQLI_REPORT_STRICT`; existing callers catch `\Exception`.

So on PostgreSQL a failed execute() returns false and code that does not check the return value proceeds as if a write succeeded. To make those failures loud — and to get a single, driver-independent exception type on both drivers — opt into strict mode:

$db = \Pramnos\Framework\Factory::getDatabase();
$db->throwOnError = true; // process-wide, opt-in — default stays false (backward compatible)

try {
    $db->getQueryBuilder()->table('accounts')->insert(['balance' => 100]);
} catch (\Pramnos\Database\QueryException $e) {
    // getDetail() is the whole thing — driver text and SQL. For a log.
    \Pramnos\Logs\Logger::log($e->getDetail(), 'billing');
    throw $e; // don't pretend the write happened
}

`getMessage()` is safe to print. The driver's text is not.

This is the whole point of the class, and it used to be the other way round.

	carries	print it?
`getMessage()`	a short sentence naming nothing — or what the call site chose, like «Could not save App\Models\Token.»	yes
`getDriverMessage()`	the driver's own text: the table, the index that was violated, the value that violated it	log only
`getQuery()`	the failing SQL, values and all	log only
`getDetail()`	all of the above in one string	log only

The driver volunteers more than anyone expects. A PostgreSQL duplicate key reads:

0:ERROR:  duplicate key value violates unique constraint "idx_usertokens_token_lookup"
DETAIL:  Key (token_lookup)=(35153ba8…) already exists.
::: SQL QUERY:
INSERT INTO public.usertokens ("userid", "tokentype", … ) VALUES ($1, … $21) RETURNING tokenid

That was getMessage(), and an authorization server answered it to an API client as an OAuth error_description — because the same catch is where that endpoint raises its own refusals, the ones whose message is the response. The customer read their own schema out of an HTTP 400 and sent it back.

So a call site that can say something useful without saying what, should:

throw new \Pramnos\Database\QueryException(
    $driverText,          // getDriverMessage()
    $sql,                 // getQuery()
    $previous,
    'Could not save ' . static::class . '.'   // getMessage()
);

If you are logging getMessage() today, log getDetail() instead. Nothing is lost; it moved to where printing it has to be deliberate.

Key points:

Default is unchanged (BC). throwOnError is false out of the box and does NOT alter the historical per-driver behaviour: PostgreSQL still returns false on a prepare failure, MySQL still throws mysqli_sql_exception (which existing callers catch via catch (\Exception)). Turn it on only where a silently dropped write would be a correctness bug (billing, migrations, anything transactional).
QueryException extends \RuntimeException and adds getQuery(), getDriverMessage() and getDetail() so you can log what you must not print. In strict mode it replaces the per-driver signals; query() continues to throw its historical \Exception. Model::_save() raises one unconditionally, strict mode or not — a save that did not save has no fail-soft reading, and it used to throw the driver's message as a plain \Exception that no catch could separate from an application's own refusals.
Driver parity in strict mode. With throwOnError = true, a prepare failure becomes a QueryException on both drivers — the PostgreSQL false return and the MySQL mysqli_sql_exception are each translated — so a single catch (QueryException) works everywhere. With throwOnError = false the two drivers keep their (different) historical signals; the framework does not force them to match, precisely to preserve BC.

Turning it on from settings — and why a test suite should

// app/config/settings.php
'database' => [
    // …
    'throwOnError' => true,
],

Read on construction, so it survives the singleton being reset — which a test suite does constantly, and which is why setting the property once at bootstrap did not work.

The framework's own fixtures set it, and finding out why is the argument for yours doing the same. A suite that runs one backend cannot see this asymmetry at all: on MySQL every failure throws whatever the flag says, so the tests look thorough. Point the same tests at PostgreSQL with the flag off and they still pass — while silently exercising the false branch nobody handles.

Switching it on in this framework's lanes, with no other change, immediately surfaced:

an inbox listing that crashed on PostgreSQL and reported the error politely on MySQL, from the same try/catch — the catch is what made it look handled;
two second-factor tests that had been passing against a missing accounts table, asserting a refusal that was happening because the query failed rather than because the password was wrong;
a characterization test that pinned the lenient path and had to be taught that the message can arrive as an exception.

None of those was a new bug. All three were invisible because the lenient answer to a failed query — false, or 0 from count() — is a value a screen will happily print. You have no messages is a sentence; a missing table is not.

Where to put it. In a test environment, always. In production, wherever a silently dropped write would be a correctness bug — and know that turning it on converts existing silent wrong answers into exceptions, which is the point and also a deployment risk worth taking deliberately.

`error_text` is the last error, not the first

$db->error_text (and getError(), which falls back to it) describes the statement that just ran. Read it immediately after the call that returned false:

if ($db->execute($sql) === false) {
    \Pramnos\Logs\Logger::error('query failed: ' . $db->error_text);
}

A statement that succeeds clears it, so an empty error_text means "nothing went wrong here" rather than "nothing has gone wrong yet".

Corrected 2026-08-20. It used to be the first error of the request. The captures in prepare() are guarded with empty($this->error_text) — deliberately, because the PostgreSQL retry path runs DEALLOCATE and overwrites pg_last_error() — but nothing reset the property between statements, so the first failure in a request answered for every failure after it. currentQuery, which setError() appends to the message it throws, was set by query() alone, so anything raised from a prepared statement quoted whichever unprepared query had run last.

Together they produced messages naming a real error and a real query that belonged to two different statements minutes apart — worse than no message, because it sends the reader to the wrong file. A DevPanel query failing on a PostgreSQL syntax error was reported as a bind-parameter mismatch in the session INSERT from application boot.

Proper Error Handling for Database Operations

Always wrap database operations in try-catch blocks to handle potential errors gracefully:

// Single record with error handling
private function getUserData(int $userId): ?array
{
    try {
        $sql = $this->application->database->prepareQuery("SELECT * FROM users WHERE id = %d", $userId);
        $result = $this->application->database->query($sql);
        
        if ($result && $result->numRows > 0) {
            return $result->fields;
        }
        return null;
    } catch (\Exception $e) {
        error_log("Error getting user data: " . $e->getMessage());
        return null;
    }
}

// Multiple records with error handling
private function getUserApplications(int $userId): array
{
    try {
        $sql = $this->application->database->prepareQuery("
            SELECT * FROM applications 
            WHERE user_id = %d 
            ORDER BY created_at DESC
        ", $userId);
        
        $result = $this->application->database->query($sql);
        $applications = [];
        while ($result->fetch()) {
            $applications[] = $result->fields;
        }
        return $applications;
    } catch (\Exception $e) {
        error_log("Error getting user applications: " . $e->getMessage());
        return [];
    }
}

// Insert/Update/Delete with error handling
private function createApplication(array $data): bool
{
    try {
        $sql = $this->application->database->prepareQuery("
            INSERT INTO applications (name, client_id, user_id, created_at) 
            VALUES (%s, %s, %d, NOW())
        ", $data['name'], $data['client_id'], $data['user_id']);
        
        $this->application->database->query($sql);
        return true;
    } catch (\Exception $e) {
        error_log("Error creating application: " . $e->getMessage());
        return false;
    }
}

Common Validation Patterns

Always validate that database results exist before accessing them:

// ✅ CORRECT - Validate result before access
$sql = $this->application->database->prepareQuery("SELECT name FROM users WHERE id = %d", $userId);
$result = $this->application->database->query($sql);

if ($result && $result->numRows > 0) {
    $name = $result->fields['name'];
} else {
    $name = 'Unknown User';
}

// ❌ WRONG - Direct access without validation
$name = $result->fields['name']; // Could cause errors if query fails

Return Type Consistency

Methods should have consistent return types with proper defaults:

// Return array for multiple records (always return array, never null)
private function getItems(): array
{
    try {
        // ... database query ...
        return $items;
    } catch (\Exception $e) {
        return []; // Always return empty array on error
    }
}

// Return nullable for single records
private function getItem(int $id): ?array
{
    try {
        // ... database query ...
        return $item;
    } catch (\Exception $e) {
        return null; // Return null on error for single records
    }
}

Common Mistakes to Avoid

Don't use PDO methods directly - Always use the Pramnos database API
Don't skip parameter binding - Even for "safe" values, always use printf-style formatting
Don't use ? placeholders - Pramnos uses printf-style (%s, %d, etc.)
Don't mix database access patterns - Be consistent throughout your application

Database Connection Access

In different contexts, access the database through:

Models: $this->application->database (passed in constructor)
Controllers: $this->application->database (available as property)
Repositories: $this->application->database (injected via constructor)
Application: $this->application->database (global application instance)

Which server am I talking to?

$database->type holds the configured engine — 'mysql' or 'postgresql'. It is a configuration value, not an observation: MariaDB is configured as 'mysql' and always has been, because it speaks MySQL's wire protocol and is reached through the same driver.

Two additive accessors ask the live connection instead of the configuration file:

$database->getServerVersion(); // raw, e.g. "10.11.6-MariaDB-1:10.11.6+maria~ubu2204"
$database->isMariaDB();        // true only for a MariaDB server

Both degrade safely: with no live connection getServerVersion() returns '' without opening one, and isMariaDB() answers false. The result is cached per connection and re-detected by connect().

For anything beyond identity, go through DatabaseCapabilities — it turns (engine, flavor, version) into "can it?" answers:

$caps = new \Pramnos\Database\DatabaseCapabilities($database);

$caps->isMySQL();    // true on MySQL *and* MariaDB — "the MySQL family"
$caps->isMariaDB();  // narrows it to MariaDB specifically
$caps->getVersion(); // "10.11.6" / "8.0.36" / "14.10" — normalised for version_compare()
$caps->atLeast('10.5');

$caps->hasSequences();        // PostgreSQL, MariaDB >= 10.3
$caps->hasReturning();        // PostgreSQL, MariaDB >= 10.5
$caps->hasNativeJson();       // PostgreSQL, MySQL >= 5.7.8 — *not* MariaDB
$caps->hasCheckConstraints(); // PostgreSQL, MariaDB >= 10.2, MySQL >= 8.0.16

hasNativeJson() is the one worth reading twice: MariaDB accepts the JSON keyword, but the column is really LONGTEXT with a CHECK (json_valid(...)) constraint — not a distinct type and not binary storage. Code that only needs to store JSON is fine either way; code that relies on the type itself must check.

See the Schema Builder guide for the full constant table and capability-conditional DDL.

Error Handling

Check Query Results

$sql = $this->application->database->prepareQuery("SELECT * FROM applications WHERE id = %d", $id);
$result = $this->application->database->query($sql);

if ($result->numRows == 0) {
    throw new \Exception('Application not found');
}

$application = $result->fields;

Transaction Support

try {
    $this->application->database->beginTransaction();
    
    // Multiple database operations
    $sql1 = $this->application->database->prepareQuery("INSERT INTO applications (client_id, name) VALUES (%s, %s)", $clientId, $name);
    $this->application->database->query($sql1);
    
    $sql2 = $this->application->database->prepareQuery("INSERT INTO application_permissions (app_id, permission) VALUES (%d, %s)", $appId, $permission);
    $this->application->database->query($sql2);
    
    $this->application->database->commit();
} catch (\Exception $e) {
    $this->application->database->rollback();
    throw $e;
}

Summary

Advanced Database Features

Database Migrations

The Pramnos framework includes a migration system for version-controlled database schema changes:

Creating Migrations

<?php
namespace MyApp\Migrations;

class CreateUsersTable extends \Pramnos\Database\Migration
{
    public $version = '1.0.1';
    public $description = 'Create users table with authentication fields';
    public $autoExecute = true;

    public function up(): void
    {
        $sql = "CREATE TABLE `#PREFIX#users` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `username` varchar(50) NOT NULL,
            `email` varchar(255) NOT NULL,
            `password_hash` varchar(255) NOT NULL,
            `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
            `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `username` (`username`),
            UNIQUE KEY `email` (`email`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        
        $this->addQuery($sql);
        $this->executeQueries();
    }

    public function down(): void
    {
        $sql = "DROP TABLE IF EXISTS `#PREFIX#users`";
        $this->addQuery($sql);
        $this->executeQueries();
    }
}

Multi-Database Support

// PostgreSQL-specific migrations
class CreateUsersTablePostgreSQL extends \Pramnos\Database\Migration
{
    public function up(): void
    {
        if ($this->application->database->type === 'postgresql') {
            $sql = 'CREATE TABLE "#PREFIX#users" (
                id SERIAL PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(255) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )';
            
            $this->addQuery($sql);
            $this->executeQueries();
        }
    }
}

Advanced Query Patterns

Complex Joins and Subqueries

// Complex join with subquery for analytics
$sql = $this->application->database->prepareQuery("
    SELECT 
        u.id,
        u.username,
        u.email,
        COUNT(o.id) as order_count,
        COALESCE(SUM(o.total), 0) as total_spent,
        recent_orders.last_order_date
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    LEFT JOIN (
        SELECT 
            user_id, 
            MAX(created_at) as last_order_date
        FROM orders 
        WHERE created_at > %s
        GROUP BY user_id
    ) recent_orders ON u.id = recent_orders.user_id
    WHERE u.created_at > %s
    GROUP BY u.id, u.username, u.email, recent_orders.last_order_date
    HAVING COUNT(o.id) > %d
    ORDER BY total_spent DESC
    LIMIT %d",
    date('Y-m-d', strtotime('-30 days')), // Recent orders filter
    date('Y-m-d', strtotime('-1 year')), // User creation filter
    5, // Minimum order count
    50  // Limit results
);

$result = $this->application->database->query($sql);
$analyticsData = [];
while ($result->fetch()) {
    $analyticsData[] = $result->fields;
}

Geospatial Queries (PostgreSQL)

// PostGIS spatial queries
if ($this->application->database->type === 'postgresql') {
    $sql = $this->application->database->prepareQuery("
        SELECT 
            id,
            name,
            ST_AsText(location) as location_text,
            ST_Distance(
                location, 
                ST_SetSRID(ST_MakePoint(%s, %s), 4326)
            ) as distance_meters
        FROM stores 
        WHERE ST_DWithin(
            location, 
            ST_SetSRID(ST_MakePoint(%s, %s), 4326), 
            %d
        )
        ORDER BY distance_meters
        LIMIT %d",
        $longitude, $latitude, // Target point
        $longitude, $latitude, // Search center
        5000, // 5km radius in meters
        10 // Limit results
    );
    
    $result = $this->application->database->query($sql);
}

Transaction Management

Transactions announce themselves

commitTransaction() and rollbackTransaction() fire an event, so code that has been holding work until the outcome is known can act on it without every call site remembering to tell it:

use Pramnos\Event\ChangeFeed;
use Pramnos\Event\Event;

Event::listen(ChangeFeed::EVENT_COMMITTED,   fn() => /* rows are durable */);
Event::listen(ChangeFeed::EVENT_ROLLED_BACK, fn() => /* drop what you held */);

Event	Name	When
`ChangeFeed::EVENT_COMMITTED`	`database.transaction.committed`	after a successful `COMMIT`
`ChangeFeed::EVENT_ROLLED_BACK`	`database.transaction.rolledback`	after `ROLLBACK`, whether or not it succeeded

The asymmetry is deliberate. A COMMIT that failed leaves rows that may not be there, so nothing is announced. A ROLLBACK that failed is still a reason to drop held work: "I could not undo that" is not grounds for announcing it, because a listener that has written an audit row cannot take it back either.

By the time a listener runs, inTransaction() already answers false. A listener that goes on to write or publish is therefore outside the transaction it came from, which is what stops a re-entrant listener from buffering into a transaction that has ended.

The names are for what happened, not for who listens: the change feed uses them to release the model changes it held, and cache invalidation or an outbox wants the same seam.

!!! warning "One flag, not a depth counter" inTransaction() tracks state set by startTransaction() / commitTransaction() / rollbackTransaction(). It does not count depth, so a nested startTransaction() followed by the inner commit fires the committed event while the outer transaction is still open. And a raw BEGIN issued through query() is not tracked at all.

Advanced Transaction Patterns

class OrderProcessor 
{
    private $database;
    
    public function __construct($application)
    {
        $this->database = $application->database;
    }
    
    public function processOrder($orderData, $orderItems)
    {
        try {
            // Start transaction
            $this->database->startTransaction();
            
            // Create order record
            $sql = $this->database->prepareQuery("
                INSERT INTO orders (user_id, total, status, created_at) 
                VALUES (%d, %s, %s, %s)",
                $orderData['user_id'],
                $orderData['total'],
                'pending',
                date('Y-m-d H:i:s')
            );
            $this->database->query($sql);
            $orderId = $this->database->getLastInsertId();
            
            // Add order items
            foreach ($orderItems as $item) {
                $sql = $this->database->prepareQuery("
                    INSERT INTO order_items (order_id, product_id, quantity, price) 
                    VALUES (%d, %d, %d, %s)",
                    $orderId,
                    $item['product_id'],
                    $item['quantity'],
                    $item['price']
                );
                $this->database->query($sql);
                
                // Update product inventory
                $sql = $this->database->prepareQuery("
                    UPDATE products 
                    SET stock_quantity = stock_quantity - %d 
                    WHERE id = %d AND stock_quantity >= %d",
                    $item['quantity'],
                    $item['product_id'],
                    $item['quantity']
                );
                $result = $this->database->query($sql);
                
                // Check if inventory update affected any rows
                if ($result->getAffectedRows() === 0) {
                    throw new \Exception("Insufficient inventory for product " . $item['product_id']);
                }
            }
            
            // Update user's order history
            $sql = $this->database->prepareQuery("
                UPDATE users 
                SET total_orders = total_orders + 1, last_order_date = %s 
                WHERE id = %d",
                date('Y-m-d H:i:s'),
                $orderData['user_id']
            );
            $this->database->query($sql);
            
            // Commit transaction
            $this->database->commitTransaction();
            
            return ['success' => true, 'order_id' => $orderId];
            
        } catch (\Exception $e) {
            // Rollback on any error
            $this->database->rollbackTransaction();
            \Pramnos\Logs\Logger::logError("Order processing failed: " . $e->getMessage());
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }
}

Nested Transactions and Savepoints

class AdvancedTransactionManager 
{
    private $database;
    private $savepointCounter = 0;
    
    public function __construct($application)
    {
        $this->database = $application->database;
    }
    
    public function createSavepoint($name = null)
    {
        if ($name === null) {
            $name = 'sp_' . (++$this->savepointCounter);
        }
        
        if ($this->database->type === 'postgresql') {
            $this->database->query("SAVEPOINT {$name}");
        } else {
            $this->database->query("SAVEPOINT {$name}");
        }
        
        return $name;
    }
    
    public function rollbackToSavepoint($name)
    {
        if ($this->database->type === 'postgresql') {
            $this->database->query("ROLLBACK TO SAVEPOINT {$name}");
        } else {
            $this->database->query("ROLLBACK TO SAVEPOINT {$name}");
        }
    }
    
    public function releaseSavepoint($name)
    {
        if ($this->database->type === 'postgresql') {
            $this->database->query("RELEASE SAVEPOINT {$name}");
        } else {
            $this->database->query("RELEASE SAVEPOINT {$name}");
        }
    }
}

Performance Optimization

Query Optimization and Analysis

class QueryOptimizer 
{
    private $database;
    
    public function __construct($application)
    {
        $this->database = $application->database;
    }
    
    public function analyzeQuery($sql)
    {
        $explainSql = "EXPLAIN " . $sql;
        $result = $this->database->query($explainSql);
        
        $analysis = [];
        while ($result->fetch()) {
            $analysis[] = $result->fields;
        }
        
        return $analysis;
    }
    
    public function getSlowQueries($limit = 10)
    {
        if ($this->database->type === 'mysql') {
            $sql = "
                SELECT 
                    sql_text,
                    exec_count,
                    avg_timer_wait/1000000000000 as avg_time_seconds,
                    sum_timer_wait/1000000000000 as total_time_seconds
                FROM performance_schema.events_statements_summary_by_digest 
                ORDER BY avg_timer_wait DESC 
                LIMIT " . (int)$limit;
            
            $result = $this->database->query($sql);
            $slowQueries = [];
            while ($result->fetch()) {
                $slowQueries[] = $result->fields;
            }
            return $slowQueries;
        }
        
        return [];
    }
}

Connection Pool Management

class ConnectionPoolManager 
{
    private static $connections = [];
    private static $maxConnections = 10;
    private static $currentConnections = 0;
    
    public static function getConnection($config)
    {
        $key = md5(serialize($config));
        
        if (isset(self::$connections[$key]) && self::$connections[$key]->connected) {
            return self::$connections[$key];
        }
        
        if (self::$currentConnections >= self::$maxConnections) {
            throw new \Exception("Connection pool exhausted");
        }
        
        $database = new \Pramnos\Database\Database($config);
        $database->connect();
        
        self::$connections[$key] = $database;
        self::$currentConnections++;
        
        return $database;
    }
    
    public static function releaseConnection($database)
    {
        // In a real implementation, you might pool connections
        // rather than closing them immediately
        $database->close();
        self::$currentConnections--;
    }
}

Adjacency List Implementation

The framework includes specialized support for hierarchical data:

$database = \Pramnos\Framework\Factory::getDatabase();
$categoryTree = new \Pramnos\Database\Adjacencylist(
    $database,
    'categories',     // table name
    'id',            // id field
    'parent_id',     // parent field
    'name'           // title field
);

// Get all categories as hierarchical array
$categories = $categoryTree->getArray();

// Get path to specific category
$categoryPath = $categoryTree->getPathAsArray(15); // category ID 15

// Set custom separator for path display
$categoryTree->separator = ' > ';
$fullPath = $categoryTree->getArray(null, 15);

Advanced Model Patterns

Model Caching and Column Information

class AdvancedModel extends \Pramnos\Application\Model
{
    // Column cache for performance
    private static $columnCache = [];
    
    public function getAvailableFields()
    {
        $database = \Pramnos\Database\Database::getInstance();
        $tableName = $this->getFullTableName();
        
        if (isset(self::$columnCache[$tableName])) {
            return array_column(self::$columnCache[$tableName], 'Field');
        }
        
        $fields = [];
        if ($database->type === 'postgresql') {
            $schema = $this->_dbschema ?? $database->schema;
            $sql = "SELECT column_name as \"Field\", data_type as \"Type\"
                    FROM information_schema.columns 
                    WHERE table_schema = '{$schema}' 
                    AND table_name = '" . str_replace('#PREFIX#', $database->prefix, $this->_dbtable) . "'";
        } else {
            $sql = "SHOW COLUMNS FROM `{$tableName}`";
        }
        
        $result = $database->query($sql);
        while ($result->fetch()) {
            $fields[] = $result->fields['Field'];
            if (!isset(self::$columnCache[$tableName])) {
                self::$columnCache[$tableName] = [];
            }
            self::$columnCache[$tableName][] = $result->fields;
        }
        
        return $fields;
    }
    
    public function getDynamicList($filter = '', $order = '', $page = 0, $itemsPerPage = 50, 
                                  $globalSearch = '', $fieldSearches = [])
    {
        $database = \Pramnos\Database\Database::getInstance();
        $availableFields = $this->getAvailableFields();
        
        // Build dynamic search conditions
        $searchConditions = $this->buildSearchConditions($availableFields, $globalSearch, $fieldSearches);
        
        // Combine with existing filter
        $finalFilter = $this->combineFilters($filter, $searchConditions);
        
        // Validate order fields against available fields
        $validatedOrder = $this->validateOrderFields($order, $availableFields);
        
        return $this->getList($finalFilter, $validatedOrder, $page, $itemsPerPage);
    }
}

Error Handling and Resilience

Comprehensive Error Handling

class ResilientDatabaseOperation 
{
    private $database;
    private $maxRetries = 3;
    private $retryDelay = 1; // seconds
    
    public function __construct($application)
    {
        $this->database = $application->database;
    }
    
    public function executeWithRetry($sql, $params = [])
    {
        $attempt = 0;
        $lastException = null;
        
        while ($attempt < $this->maxRetries) {
            try {
                if (!$this->database->connected) {
                    $this->database->connect();
                }
                
                if (!empty($params)) {
                    $preparedSql = $this->database->prepareQuery($sql, ...$params);
                } else {
                    $preparedSql = $sql;
                }
                
                return $this->database->query($preparedSql);
                
            } catch (\Exception $e) {
                $lastException = $e;
                $attempt++;
                
                \Pramnos\Logs\Logger::logError(
                    "Database query attempt {$attempt} failed: " . $e->getMessage()
                );
                
                if ($attempt < $this->maxRetries) {
                    sleep($this->retryDelay * $attempt); // Exponential backoff
                    $this->database->refresh(); // Reconnect
                }
            }
        }
        
        throw new \Exception(
            "Database operation failed after {$this->maxRetries} attempts. Last error: " . 
            $lastException->getMessage()
        );
    }
    
    public function executeInTransaction($operations)
    {
        try {
            $this->database->startTransaction();
            
            foreach ($operations as $operation) {
                if (is_callable($operation)) {
                    $operation($this->database);
                } elseif (is_array($operation) && isset($operation['sql'])) {
                    $this->executeWithRetry($operation['sql'], $operation['params'] ?? []);
                }
            }
            
            $this->database->commitTransaction();
            return true;
            
        } catch (\Exception $e) {
            $this->database->rollbackTransaction();
            \Pramnos\Logs\Logger::logError("Transaction failed: " . $e->getMessage());
            throw $e;
        }
    }
}

Database-Specific Features

PostgreSQL Advanced Features

class PostgreSQLFeatures 
{
    private $database;
    
    public function __construct($application)
    {
        $this->database = $application->database;
    }
    
    public function useJSONQueries($table, $jsonColumn, $jsonPath, $value)
    {
        if ($this->database->type !== 'postgresql') {
            throw new \Exception("JSON queries are only supported in PostgreSQL");
        }
        
        $sql = $this->database->prepareQuery("
            SELECT * FROM \"{$table}\" 
            WHERE \"{$jsonColumn}\" ->> %s = %s",
            $jsonPath,
            $value
        );
        
        return $this->database->query($sql);
    }
    
    public function useArrayColumns($table, $arrayColumn, $searchValue)
    {
        $sql = $this->database->prepareQuery("
            SELECT * FROM \"{$table}\" 
            WHERE %s = ANY(\"{$arrayColumn}\")",
            $searchValue
        );
        
        return $this->database->query($sql);
    }
    
    public function useFullTextSearch($table, $textColumn, $searchTerm)
    {
        $sql = $this->database->prepareQuery("
            SELECT *, ts_rank(to_tsvector('english', \"{$textColumn}\"), plainto_tsquery('english', %s)) as rank
            FROM \"{$table}\" 
            WHERE to_tsvector('english', \"{$textColumn}\") @@ plainto_tsquery('english', %s)
            ORDER BY rank DESC",
            $searchTerm,
            $searchTerm
        );
        
        return $this->database->query($sql);
    }
}

Database Security Best Practices

SQL Injection Prevention

Always use the framework's parameter binding - never concatenate user input:

// ✅ SECURE - Using parameter binding
$sql = $this->application->database->prepareQuery(
    "SELECT * FROM users WHERE email = %s AND role = %s AND active = %d",
    $userEmail,
    $userRole,
    1
);

// ❌ VULNERABLE - Direct concatenation
$sql = "SELECT * FROM users WHERE email = '" . $userEmail . "'"; // NEVER DO THIS!

Sensitive Data Handling

class SecureDataHandler 
{
    public function hashSensitiveData($data, $salt = null)
    {
        if ($salt === null) {
            $salt = bin2hex(random_bytes(32));
        }
        
        return [
            'hash' => hash_pbkdf2('sha256', $data, $salt, 10000),
            'salt' => $salt
        ];
    }
    
    public function insertUserWithHashedPassword($userData)
    {
        $passwordData = $this->hashSensitiveData($userData['password']);
        
        $sql = $this->application->database->prepareQuery("
            INSERT INTO users (username, email, password_hash, password_salt, created_at) 
            VALUES (%s, %s, %s, %s, %s)",
            $userData['username'],
            $userData['email'],
            $passwordData['hash'],
            $passwordData['salt'],
            date('Y-m-d H:i:s')
        );
        
        return $this->application->database->query($sql);
    }
}

The Pramnos Database API provides a comprehensive, secure, and flexible foundation for all database operations, supporting both MySQL and PostgreSQL with advanced features for modern web applications.

Session time zone (`database.timezone`)

Set database.timezone in the database settings to have the framework apply a session time zone on connect, so NOW() and timestamp rendering match the app's zone:

// app settings
'database' => [
    // ... hostname, database, user, password ...
    'timezone' => 'Europe/Athens', // or 'UTC'
],

On connect the framework issues SET TIME ZONE '<tz>' (PostgreSQL) / SET time_zone = '<tz>' (MySQL) on each connection (write and read replica). The setting is unset by default — when absent, no SET is issued and the server default is left untouched, so existing applications are unaffected.

FilesExpand file tree

Pramnos_Database_API_Guide.md

Latest commit

History

Pramnos_Database_API_Guide.md

File metadata and controls

Pramnos Database API Guide

Overview

Database Access in Controllers

Core Pattern

The Two-Step Process

Printf-Style Format Specifiers

A literal % in the SQL, and LIKE

What a datatable search actually looks for

Writing a point: 'geometry' and the coordinate order

Reading a table's schema: getColumns()

Real Prepared Statements: preparedQuery()

What counts as SQL, and what does not

A prepared statement whose connection died underneath it

A paginated list that groups

Result Handling

Single Record Queries

Multiple Record Queries

Common Mistakes to Avoid

Key Points:

Database Operations

SELECT Operations

The Modern Way: Fluent Query Builder (v1.2+)

Advantages:

Examples:

Raw Expressions

INSERT Operations

UPDATE Operations

DELETE Operations

Queries Without Parameters

Model Integration

In Model Classes

Model Instantiation

Controllers and Database Access

In Controller Classes

Common Patterns

Checking if Record Exists

Getting Multiple Records

Complex Queries with JOINs

Best Practices

1. Always Use Parameter Binding

2. Handle Database Errors

3. Use Transactions for Multiple Operations

4. Model Loading Patterns

5. Validate Input Before Database Operations

Who ran this query: the PostgreSQL session variables

Error Handling and Best Practices

How database failures surface (and throwOnError)

getMessage() is safe to print. The driver's text is not.

Turning it on from settings — and why a test suite should

error_text is the last error, not the first

Proper Error Handling for Database Operations

Common Validation Patterns

Return Type Consistency

Common Mistakes to Avoid

Database Connection Access

Which server am I talking to?

Error Handling

Check Query Results

Transaction Support

Summary

Advanced Database Features

Database Migrations

Creating Migrations

Multi-Database Support

Advanced Query Patterns

Complex Joins and Subqueries

Geospatial Queries (PostgreSQL)

Transaction Management

Transactions announce themselves

Advanced Transaction Patterns

Nested Transactions and Savepoints

Performance Optimization

Query Optimization and Analysis

Connection Pool Management

A literal `%` in the SQL, and `LIKE`

Writing a point: `'geometry'` and the coordinate order

Reading a table's schema: `getColumns()`

Real Prepared Statements: `preparedQuery()`

How database failures surface (and `throwOnError`)

`getMessage()` is safe to print. The driver's text is not.

`error_text` is the last error, not the first

Session time zone (`database.timezone`)