Database — DotApp PHP Framework 2.0

Miščík, Štefan

Databaser

Run queries from a module controller. Entry points: DB::module('RAW') (arrays) or DB::module('ORM') (Entity/Collection). Read rows with all(), then $rows[0] ?? null when you need a single row. Always pass both callbacks to execute($ok, $err) — without an error callback, a failure throws. Schema changes belong in the module’s Installation.php. Module tables use the {modulename}_* naming pattern.

1. Introduction

1.1. What is Databaser?

Databaser is a robust, flexible library for database work, built into DotApp PHP Framework 2.0. It provides a simple, safe, and efficient way to run both basic operations and advanced queries. Databaser removes the need to write raw SQL (while still allowing it) and offers a modern approach through an intuitive QueryBuilder and an optional ORM (Object-Relational Mapping) layer. The goal is to make database work easier for developers without sacrificing flexibility or performance.

Databaser is part of the DotApp PHP Framework 2.0 core, so you do not install or configure it separately. After you define database connections in the framework, it is ready to use.

1.2. Key Features

Databaser offers a wide set of features for working with databases:

Simple SQL construction and execution: Prepared statements keep data work safe and straightforward.
Multiple database connections: Define and switch between databases with stored credentials.
Custom driver support: Besides the default drivers (MySQLi and PDO), you can implement your own database drivers.
Optional ORM: Available for both MySQLi and PDO, with Entity (a single row) and Collection (a set of rows) classes that treat data as objects.
Lazy loading and relations: Load related rows with relation methods on an Entity ($user->hasMany('shop_posts', 'user_id')).
Advanced relations: Adjust the QueryBuilder inside a relation (for example add limit, orderBy, where) through an optional callback parameter.
Validation: The ORM validates attributes during save(). Iterate a Collection and save each Entity individually.
Integrated QueryBuilder: An intuitive query builder covering simple SELECTs through complex JOINs and subqueries.
SUCCESS and ERROR callbacks: Every operation returns results and debug data through callbacks, which simplifies success and error handling.
Transaction support: Straightforward transaction handling with automatic commit or rollback.

1.3. RAW vs. ORM: When to Use Which Approach?

Databaser offers two main ways to work with data: RAW and ORM. The choice depends on your project’s needs:

RAW MODE:
- Query results are returned directly (for example arrays or database resources).
- Ideal for simple applications, quick prototypes, or situations where you need full control over SQL.
- Example: A simple SELECT to list users without object mapping.
- Benefits: Fast execution, minimal overhead, full flexibility when writing queries.
ORM MODE:
- Data is mapped to objects (Entity for one row, Collection for many rows), so you work with data as objects.
- Suitable for complex applications that need table relations, data validation, or object-oriented row handling.
- Example: Managing users and their posts (a HasMany relation) and saving changes automatically.
- Benefits: Object-oriented approach, relation support, straightforward data handling.

When to use which approach?

Choose RAW when you need fast performance and simple queries.
Choose ORM when you work with complex data structures and want a cleaner object-oriented solution.

1.4. Support for Database Drivers (MySQLi, PDO)

Databaser supports two main database drivers that cover most common needs:

MySQLi

QueryBuilder and ORM.
A good fit for projects that already use MySQLi, or for simpler applications with MySQL databases.
Supports all QueryBuilder and ORM features.

PDO

Support for multiple databases (MySQL, PostgreSQL, SQLite, and others).
More flexible thanks to a dynamic DSN (Data Source Name), which lets you connect to different database types.
Also supports QueryBuilder and ORM.

Both drivers are designed to be interchangeable — code written for one driver works with the other without major changes, as long as you respect the specifics of the target database system.

1.5. Integrated Query Builder

QueryBuilder is the heart of Databaser. It lets you build SQL with chainable methods, which makes safe, readable queries easier to write. It supports:

Basic operations: select, insert, update, delete.
Conditions: where, orWhere, nested conditions via a Closure.
Table joins: join, leftJoin.
Aggregations: groupBy, having.
Sorting and limits: orderBy, limit, offset.
Raw queries: raw with both question-mark placeholders (?) and named variables (:name).
Table source: from when the table is not passed to select() or delete().

QueryBuilder manages prepared statements and bindings automatically, which protects against SQL injection. Every value used in a query (for example in where conditions or in data passed to insert) is escaped and replaced with placeholders (? or named variables :name). That reduces the risk of security issues and keeps the code easier to read.

1.6. Callbacks for SUCCESS and ERROR

Databaser uses callbacks to handle results and errors. Every operation (for example execute(), save()) can take two optional callbacks:

SUCCESS callback

Runs when the operation succeeds. It receives three parameters:

$result: The operation result (for example an array of data in RAW mode, or an object in ORM mode).
$db: The Databaser instance, which you can use for further queries.
$debug: Debug data (for example the generated SQL query and bindings).

ERROR callback

Runs when an error occurs. It also receives three parameters:

$error: An array with error details (error — error text, errno — error code).
$db: The Databaser instance for any follow-up operations.
$debug: Debug data for analyzing the problem.

This approach simplifies handling and lets you chain operations directly in callbacks. For example, if one query should immediately start another, call $db->q() from the SUCCESS callback. Success and error logic stay separate and easy to follow. Always pass both callbacks to execute($ok, $err). If you omit the ERROR callback, execute() throws on failure, so you can catch the exception with a try/catch block. If the ERROR callback is set, try/catch will not run for that failure — error handling is fully delegated to the callback.

2. Getting Started

2.1. Installing and Configuring Databaser

Databaser is an integral part of DotApp PHP Framework 2.0, so you do not install it separately. Once the framework is set up in your project, Databaser is available through the DB:: facade. For module code, use DB::module('RAW') or DB::module('ORM'). This chapter assumes the framework is configured and ready to use.

2.2. Adding a Database Connection

Databaser can add and manage multiple database connections. Register them in app/config.php with Config::addDatabase(). Example:

Config::addDatabase(
    'main',           // Connection name
    'localhost',      // Host
    'root',           // Username
    'password123',    // Password
    'my_database',    // Database name
    'utf8mb4',        // Charset
    'MYSQL',          // Database type
    'pdo'             // Driver
);

2.3. Choosing a Driver (MySQLi or PDO)

Databaser supports both MySQLi and PDO. The driver and the main database (maindb) are normally chosen in configuration, not in every query. In the samples, use DB::module('RAW') or DB::module('ORM'). Leave manual driver selection for advanced custom drivers.

2.4. First Database Connection

After you define a connection in configuration, the framework uses the driver and the main connection automatically. You can check the connection like this:

if (DB::isConnected()) {
    echo 'Database is connected.';
}

Example of a first simple query:

DB::module('RAW')->q(function ($qb) {
        $qb->select('*', 'shop_items');
    })
    ->execute(
        function ($result, $db, $debug) {
            echo "Generated query: " . $debug['query'] . "\n";
            var_dump($result);
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']} (code: {$error['errno']})\n";
        }
    );

Explanation

DB::module('RAW'): Canonical entry. Driver and default database come from app/config.php.
execute($ok, $err): Always pass both callbacks. Without $err a database error throws.
q() (alias qb()): Starts QueryBuilder and defines the query (in this case SELECT * FROM shop_items).
execute(): Runs the query with callbacks for success and error.
$result: An array of results (in RAW mode).
$debug: Contains the generated SQL query and other information.

Output (example):

Generated query: SELECT * FROM shop_items

array(2) {
  [0] => array(3) {
    ["id"] => string(1) "1"
    ["name"] => string(4) "Jane"
    ["age"] => string(2) "25"
  }
  [1] => array(3) {
    ["id"] => string(1) "2"
    ["name"] => string(5) "Maria"
    ["age"] => string(2) "30"
  }
}

3. Query Builder: Detailed Overview

QueryBuilder is a core tool in Databaser. It lets you build SQL with chainable methods. The main advantages are simplicity, readability, and safety — it manages prepared statements and bindings automatically, which protects against SQL injection. This chapter covers how it works, the available methods, and examples from simple to complex queries.

3.1. Basic Principles of Query Builder

QueryBuilder is an object of the Dotsystems\App\Parts\QueryBuilder class. You use it inside q() or qb() on the DB:: facade (typically DB::module('RAW')->q(...)). You build the query by calling methods in sequence; each method adds a part of the SQL statement (for example select, where, join). Finish the query with execute(). Read rows with all(), then take $rows[0] ?? null when you need a single row.

Core characteristics:

Chainability: Methods return the QueryBuilder instance, so you can chain them.
Prepared statements: All values are escaped automatically and replaced with placeholders (?).
Flexibility: Raw SQL is available through raw() for special cases.
Debuggability: After execution, $debug contains the generated SQL and bindings.

Basic usage example:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('age', '>', 18);
})->execute(
    function ($result, $db, $debug) {
        echo $debug['query']; // "SELECT * FROM shop_items WHERE age > ?"
        var_dump($debug['bindings']); // [18]
        var_dump($result);
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']} (code: {$error['errno']})\n";
    }
);

3.2. List of Query Builder Methods

Here is a detailed overview of the main QueryBuilder methods, with explanations and examples.

3.2.1. select

The select() method defines which columns to read and from which table.

Syntax: select($columns = '*', $table = null)

Parameters:

$columns: A string or an array of columns (for example 'id, name' or ['id', 'name']).
$table: Table name (optional if you use from()).

SQL equivalent: SELECT columns FROM table

Example:

$qb->select('id, name', 'shop_items');
// SQL: SELECT id, name FROM shop_items

3.2.2. insert

The insert() method inserts a new row into a table.

Syntax: insert($table, array $data)

Parameters:

$table: Table name.
$data: Associative array of data (column => value).

SQL equivalent: INSERT INTO table (columns) VALUES (values)

Example:

$qb->insert('shop_items', ['name' => 'Jane', 'age' => 25]);
// SQL: INSERT INTO shop_items (name, age) VALUES (?, ?)
// Bindings: ['Jane', 25]

3.2.3. update

The update() and set() methods update existing rows.

Syntax: update($table) + set(array $data)

Parameters:

$table: Table name.
$data: Associative array of updated values.

SQL equivalent: UPDATE table SET column = value

Example:

$qb->update('shop_items')->set(['age' => 26])->where('id', '=', 1);
// SQL: UPDATE shop_items SET age = ? WHERE id = ?
// Bindings: [26, 1]

3.2.4. delete

The delete() method removes rows from a table.

Syntax: delete($table = null)

Parameters:

$table: Table name (optional if it is defined elsewhere).

SQL equivalent: DELETE FROM table

Example:

$qb->delete('shop_items')->where('id', '=', 1);
// SQL: DELETE FROM shop_items WHERE id = ?
// Bindings: [1]

3.2.5. where and orWhere

The where() and orWhere() methods add conditions.

Syntax: where($column, $operator = null, $value = null, $boolean = 'AND')

Parameters:

$column: A column or a Closure for nested conditions.
$operator: Operator (for example =, >, <).
$value: A value or a Closure for a subquery.
$boolean: Logical join (default AND).

SQL equivalent: WHERE column operator value

Example:

$qb->select('*', 'shop_items')
   ->where('age', '>', 18)
   ->orWhere('name', '=', 'Jane');
// SQL: SELECT * FROM shop_items WHERE age > ? OR name = ?
// Bindings: [18, 'Jane']

3.2.6. join (INNER, LEFT)

The join() and leftJoin() methods join tables.

Syntax: join($table, $first, $operator, $second, $type = 'INNER')

Parameters:

$table: A table or a subquery (QueryBuilder).
$first: First column of the join condition.
$operator: Join operator.
$second: Second column of the join condition.
$type: Join type (INNER, LEFT).

SQL equivalent: INNER JOIN table ON condition

Example:

$qb->select('shop_items.name, shop_posts.title', 'shop_items')
   ->join('shop_posts', 'shop_items.id', '=', 'shop_posts.user_id');
// SQL: SELECT shop_items.name, shop_posts.title FROM shop_items INNER JOIN shop_posts ON shop_items.id = shop_posts.user_id

3.2.7. groupBy

The groupBy() method groups results.

Syntax: groupBy($columns)

Parameters:

$columns: A column or an array of columns.

SQL equivalent: GROUP BY columns

Example:

$qb->select('age', 'shop_items')->groupBy('age');
// SQL: SELECT age FROM shop_items GROUP BY age

3.2.8. having

The having() method filters grouped results.

Syntax: having($column, $operator, $value)

Parameters:

$column: Column.
$operator: Operator.
$value: Value.

SQL equivalent: HAVING column operator value

Example:

$qb->select('age', 'shop_items')->groupBy('age')->having('age', '>', 20);
// SQL: SELECT age FROM shop_items GROUP BY age HAVING age > ?
// Bindings: [20]

3.2.9. orderBy

The orderBy() method sorts results.

Syntax: orderBy($column, $direction = 'ASC')

Parameters:

$column: Column.
$direction: Direction (ASC or DESC).

SQL equivalent: ORDER BY column direction

Example:

$qb->select('*', 'shop_items')->orderBy('age', 'DESC');
// SQL: SELECT * FROM shop_items ORDER BY age DESC

3.2.10. limit and offset

The limit() and offset() methods limit how many rows are returned.

Syntax: limit($limit) + offset($offset)

Parameters:

$limit: Number of rows.
$offset: Starting offset.

SQL equivalent: LIMIT count OFFSET offset

Example:

$qb->select('*', 'shop_items')->limit(5)->offset(10);
// SQL: SELECT * FROM shop_items LIMIT ? OFFSET ?
// Bindings: [5, 10]

3.2.11. raw

The raw() method lets you run a raw SQL query.

Syntax: raw($sql, array $bindings = [])

Parameters:

$sql: Raw SQL string.
$bindings: Array of values for placeholders.

SQL equivalent: The query you pass in.

Example:

$qb->raw('SELECT * FROM shop_items WHERE age > ?', [18]);
// SQL: SELECT * FROM shop_items WHERE age > ?
// Bindings: [18]

3.2.12. from

The from() method sets the table when you did not pass it to select(), delete(), or a similar method.

Syntax: from($table)

Parameters:

$table: Table name.

SQL equivalent: FROM table

Example:

$qb->select('id, name')->from('shop_items');
// SQL: SELECT id, name FROM shop_items

3.3. Examples from Simple to Complex Queries

Simple select

$qb->select('*', 'shop_items');
// SQL: SELECT * FROM shop_items

select with a where condition

$qb->select('name', 'shop_items')->where('age', '>', 18);
// SQL: SELECT name FROM shop_items WHERE age > ?
// Bindings: [18]

Nested conditions (Closure)

$qb->select('*', 'shop_items')->where(function ($qb) {
    $qb->where('age', '>', 18)->orWhere('name', '=', 'Jane');
});
// SQL: SELECT * FROM shop_items WHERE (age > ? OR name = ?)
// Bindings: [18, 'Jane']

join with multiple tables

$qb->select('shop_items.name, shop_posts.title', 'shop_items')
   ->join('shop_posts', 'shop_items.id', '=', 'shop_posts.user_id')
   ->leftJoin('shop_comments', 'shop_posts.id', '=', 'shop_comments.post_id');
// SQL: SELECT shop_items.name, shop_posts.title FROM shop_items
//      INNER JOIN shop_posts ON shop_items.id = shop_posts.user_id
//      LEFT JOIN shop_comments ON shop_posts.id = shop_comments.post_id

Subquery as a value

$qb->select('name', 'shop_items')->where('id', '=', function ($qb) {
    $qb->select('user_id', 'shop_posts')->where('title', '=', 'News');
});
// SQL: SELECT name FROM shop_items WHERE id = (SELECT user_id FROM shop_posts WHERE title = ?)
// Bindings: ['News']

raw query with named variables

$qb->raw('SELECT * FROM shop_items WHERE age > :age AND name = :name', [
    'age' => 18,
    'name' => 'Jane'
]);
// SQL: SELECT * FROM shop_items WHERE age > ? AND name = ?
// Bindings: [18, 'Jane']

4. Working with Databaser in DotApp

This chapter covers practical use of Databaser in the DotApp Framework: setting the return type, running queries, working with ORM, managing transactions, and inspecting results. Databaser is designed for flexibility and simplicity, whether you prefer the RAW approach or the object-oriented ORM.

4.1. Setting the Return Type (RAW vs. ORM)

Set the return type with DB::module('RAW') or DB::module('ORM') — not with a return() method.

RAW: Returns raw data (for example an array of rows or a database result resource). This is the default.
ORM: Returns data as objects (Entity for a single row, Collection for multiple rows).

Syntax: DB::module($type)

$type: The string 'RAW' or 'ORM' (case does not matter).

Example — RAW:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->execute(
    function ($result, $db, $debug) {
        var_dump($result); // Array of rows
    },
    function ($error) {
        // execute() without this callback throws on error
    }
);

Example — ORM:

DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->execute(
    function ($result, $db, $debug) {
        var_dump($result); // Collection instance
    },
    function ($error) {
        // execute() without this callback throws on error
    }
);

You can change the return type before each query, so different parts of the application can use RAW or ORM as needed.

4.2. Methods for Executing Queries

Databaser provides several methods for running queries built with QueryBuilder. Each method has a specific use.

4.2.1. execute()

The execute() method is the most versatile — it runs the query and delivers results through callbacks.

Syntax: execute($success = null, $error = null)

Parameters:

$success: Success callback (function ($result, $db, $debug)).
$error: Error callback (function ($error, $db, $debug)). Always pass this callback; without it, execute() throws on error.

Output: Depends on the return type (RAW: array/resource, ORM: Collection/Entity).

Example:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('age', '>', 18);
})->execute(
    function ($result, $db, $debug) {
        echo "Query: " . $debug['query'] . "\n";
        var_dump($result);
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

4.2.2. first() — unsafe on empty results

Do not call first() unguarded. Empty RAW triggers an undefined-index warning; empty ORM is fatal. Prefer all() and take index 0:

$rows = DB::module('RAW')->q(function ($qb) {
    $qb->select('*')->from('shop_items')->where('id', '=', 1)->limit(1);
})->all();
$row = $rows[0] ?? null;

4.2.3. all()

The all() method returns every result row. This is also the safe way to read a single row: take $rows[0] ?? null.

Syntax: all()

Output: RAW — array of rows; ORM — Collection.

Example:

$users = DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();
foreach ($users as $user) {
    echo $user['name'] . "\n";
}

4.2.4. raw()

The raw() method is a terminal that returns the driver result (for example a mysqli_result or PDO statement). Fetch rows from that result with DB::fetchArray().

Syntax: raw()

Output: Depends on the driver (for example mysqli_result or a PDO statement).

Example:

$result = DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->raw();
while ($row = DB::fetchArray($result)) {
    echo $row['name'] . "\n";
}

4.3. Working with ORM

ORM mode lets you work with rows as objects, which simplifies updates and relations between tables.

4.3.1. Entity and Collection

Entity: Represents one table row. It exposes attributes that match columns, plus methods for updates and relations.

Collection: A group of Entity objects with iteration and helpers such as filter(), map(), and pluck().

Example:

$users = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();
foreach ($users as $user) {
    echo $user->name . "\n"; // Collection yields Entity objects
}

4.3.2. Saving data (save())

Entity::save($ok, $err) writes changes to the database. It returns void — always use the callbacks; do not write if ($entity->save()). Read a row with all() and $rows[0] ?? null before you save.

Example:

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('id', '=', 1);
})->all();
$user = $rows[0] ?? null;
if ($user) {
    $user->age = 26;
    $user->save(
        function ($result, $db, $debug) {
            echo "User saved!\n";
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}

4.3.3. Relations (hasOne, hasMany)

ORM supports relations between tables. Load related rows with a method call on the entity — not a magic property such as $user->posts:

hasOne: One-to-one.
hasMany: One-to-many.

Example:

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('id', '=', 1);
})->all();
$user = $rows[0] ?? null;
$posts = $user ? $user->hasMany('shop_posts', 'user_id') : [];
foreach ($posts as $post) {
    echo $post->title . "\n";
}

4.3.4. Lazy loading and Collection methods

Related rows load when you call the relation method (lazy loading). Collection provides helpers such as filter(), map(), and pluck(). pluck('name') returns a Collection of that field’s values; call all() on it if you need a plain PHP array.

Example:

$users = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();
$names = $users->pluck('name');
var_dump($names);

4.4. Transactions

Databaser supports transactions so a group of writes either all succeed or all roll back.

4.4.1. transaction(), commit(), rollback()

Manual control with DB::module('RAW')->transaction(), then commit() or rollback():

$db = DB::module('RAW');
$db->transaction();
$db->q(function ($qb) {
    $qb->insert('shop_items', ['name' => 'Jane']);
})->execute(
    function ($result, $db, $debug) {
        $db->commit();
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
        $db->rollback();
    }
);

Automatic transaction with transact(). The operations callback receives the Databaser instance plus success and error callbacks — pass both through to every execute() so the transaction can commit or roll back:

DB::module('RAW')->transact(function ($db, $ok, $err) {
    $db->q(function ($qb) {
        $qb->insert('shop_items', ['name' => 'Jane']);
    })->execute($ok, $err);
}, function ($result, $db, $debug) {
    echo "Transaction succeeded!\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

4.5. Debugging and Working with Output

Each operation exposes three main values:

result: The query result (RAW: array, ORM: objects).
db: The Databaser instance for follow-up queries.
debug: An array of information (for example query and bindings). The same payload also includes insert_id and affected_rows.

Debug example:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('age', '>', 18);
})->execute(
    function ($result, $db, $debug) {
        echo "SQL: " . $debug['query'] . "\n";
        echo "Bindings: " . implode(', ', $debug['bindings']) . "\n";
        var_dump($result);
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

5. Practical Examples

This chapter shows practical Databaser usage in the DotApp Framework. We cover common CRUD operations (Create, Read, Update, Delete), advanced queries with join and subqueries, transactions, and error handling. After an insert or update, read the new ID and the affected-row count with $db->inserted_id() and $db->affected_rows() (underscores). The same values are also available as $execution_data['insert_id'] and $execution_data['affected_rows'] in the execute callbacks.

5.1. Basic CRUD Operations in RAW Mode

Create:

DB::module('RAW')->q(function ($qb) {
    $qb->insert('shop_items', ['name' => 'Jane', 'age' => 25]);
})->execute(
    function ($result, $db, $debug) {
        $id = $db->inserted_id(); // ID of the new row
        echo "New user with ID: $id has been created.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Read:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('age', '>', 20);
})->execute(
    function ($result, $db, $debug) {
        foreach ($result as $user) {
            echo "Name: {$user['name']}, Age: {$user['age']}\n";
        }
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Update:

DB::module('RAW')->q(function ($qb) {
    $qb->update('shop_items')->set(['age' => 26])->where('name', '=', 'Jane');
})->execute(
    function ($result, $db, $debug) {
        $rows = $db->affected_rows(); // Number of affected rows
        echo "$rows row(s) updated.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Delete:

DB::module('RAW')->q(function ($qb) {
    $qb->delete('shop_items')->where('name', '=', 'Jane');
})->execute(
    function ($result, $db, $debug) {
        $rows = $db->affected_rows();
        echo "$rows row(s) deleted.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

5.2. Basic CRUD Operations in ORM Mode

Create:

DB::module('ORM')->q(function ($qb) {
    $qb->insert('shop_items', ['name' => 'Maria', 'age' => 30]);
})->execute(
    function ($result, $db, $debug) {
        $id = $db->inserted_id();
        $items = $db->q(function ($qb) use ($id) {
            $qb->select('*', 'shop_items')->where('id', '=', $id);
        })->all();
        $user = $items[0] ?? null;
        if ($user) {
            echo "Created user: {$user->name}\n";
        }
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Read:

$users = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();
foreach ($users as $user) {
    echo "Name: {$user->name}, Age: {$user->age}\n";
}

Update:

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('name', '=', 'Maria');
})->all();
$user = $rows[0] ?? null;
if ($user) {
    $user->age = 31;
    $user->save(
        function ($result, $db, $debug) use ($user) {
            echo "User {$user->name} updated.\n";
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}

Delete:

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('name', '=', 'Maria');
})->all();
$user = $rows[0] ?? null;
if ($user) {
    DB::module('RAW')->q(function ($qb) use ($user) {
        $qb->delete('shop_items')->where('id', '=', $user->id);
    })->execute(
        function ($result, $db, $debug) {
            $rows = $db->affected_rows();
            echo "$rows row(s) deleted.\n";
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}

5.3. Advanced Examples with JOIN and Subquery

JOIN across tables:

DB::module('RAW')->q(function ($qb) {
    $qb->select('shop_items.name, shop_posts.title', 'shop_items')
       ->join('shop_posts', 'shop_items.id', '=', 'shop_posts.user_id')
       ->where('shop_items.age', '>', 25);
})->execute(
    function ($result, $db, $debug) {
        foreach ($result as $row) {
            echo "User: {$row['name']}, Post: {$row['title']}\n";
        }
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Subquery in ORM:

DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')
       ->where('id', '=', function ($subQb) {
           $subQb->select('user_id', 'shop_posts')
                  ->where('title', '=', 'News');
       });
})->execute(
    function ($users, $db, $debug) {
        foreach ($users as $user) {
            echo "User with News post: {$user->name}\n";
        }
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

5.4. Working with Transactions

Automatic transaction:

DB::module('RAW')->transact(function ($db, $ok, $err) {
    $db->q(function ($qb) {
        $qb->insert('shop_items', ['name' => 'Peter', 'age' => 28]);
    })->execute(function ($result, $db, $debug) use ($ok, $err) {
        $id = $db->inserted_id();
        $db->q(function ($qb) use ($id) {
            $qb->insert('shop_posts', ['user_id' => $id, 'title' => 'First post']);
        })->execute($ok, $err);
    }, $err);
}, function ($result, $db, $debug) {
    echo "Transaction succeeded. Last insert ID: " . $db->inserted_id() . "\n";
}, function ($error, $db, $debug) {
    echo "Transaction error: {$error['error']}\n";
});

Manual transaction:

$db = DB::module('RAW');
$db->transaction();
$db->q(function ($qb) {
    $qb->insert('shop_items', ['name' => 'Anna', 'age' => 22]);
})->execute(
    function ($result, $db, $debug) {
        $id = $db->inserted_id();
        $db->q(function ($qb) use ($id) {
            $qb->insert('shop_posts', ['user_id' => $id, 'title' => 'Test']);
        })->execute(
            function ($result, $db, $debug) {
                $db->commit();
                echo "Transaction completed.\n";
            },
            function ($error, $db, $debug) {
                $db->rollback();
                echo "Rollback: {$error['error']}\n";
            }
        );
    },
    function ($error, $db, $debug) {
        $db->rollback();
        echo "Error: {$error['error']}\n";
    }
);

5.5. Debugging and Error Handling

Debugging a query:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('age', '>', 18);
})->execute(
    function ($result, $db, $debug) {
        echo "SQL: " . $debug['query'] . "\n";
        echo "Bindings: " . implode(', ', $debug['bindings']) . "\n";
        echo "Affected rows: " . $db->affected_rows() . "\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']} (code: {$error['errno']})\n";
        echo "SQL: " . $debug['query'] . "\n";
    }
);

Handling an error:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'missing_table'); // Invalid query
})->execute(
    function ($result, $db, $debug) {
        echo "Success\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
        // Follow-up query on a table that exists
        $db->q(function ($qb) {
            $qb->select('*', 'shop_items');
        })->execute(
            function ($result, $db, $debug) {
                echo "Recovery query succeeded.\n";
            },
            function ($error, $db, $debug) {
                echo "Error: {$error['error']}\n";
            }
        );
    }
);

6. Working with SchemaBuilder

SchemaBuilder is a Databaser tool for defining and managing database structure. It lets you create, alter, and drop tables from PHP without writing raw DDL by hand. It is integrated with QueryBuilder through createTable(), alterTable(), and dropTable(). This chapter covers its methods, arguments, and practical examples.

6.1. SchemaBuilder fundamentals

SchemaBuilder is the class Dotsystems\App\Parts\SchemaBuilder. You receive it in the callback of createTable(), alterTable(), and related helpers. Those helpers are called from QueryBuilder inside q() or from schema(). The goal is a programmatic way to define tables, columns, indexes, and foreign keys. The resulting statements are converted to SQL and executed through the active driver (MySQLi or PDO).

Key characteristics:

Chainable methods: Like QueryBuilder, SchemaBuilder is designed for chaining.
Abstraction: It works independently of the database driver, although some features depend on the engine.
Simplicity: You can define a schema without writing full SQL syntax.

6.2. SchemaBuilder methods

Overview of the main methods, their arguments, and examples.

Column helpers return a column definition. Chain modifiers on that object: nullable(), default(), unsigned() (MySQL only), and comment(). Do not pass a nullable flag as a trailing argument to string() or integer(). There is no timestamps() helper — declare created_at and updated_at yourself with datetime() (or timestamp() when you explicitly want a TIMESTAMP column).

Other column helpers include text(), decimal($name, $precision = 10, $scale = 2), timestamp(), date(), boolean(), bigInteger(), and tinyInteger().

6.2.1. id()

Adds a BIGINT AUTO_INCREMENT primary key.

Syntax: id($name = 'id')

Parameters:

$name: Column name (default 'id').

SQL equivalent: id BIGINT NOT NULL AUTO_INCREMENT plus a primary-key constraint. On MySQL you may chain ->unsigned().

Example:

$schema->id(); // Creates the `id` column

6.2.2. string()

Adds a VARCHAR column.

Syntax: string($name, $length = 255)

Parameters:

$name: Column name.
$length: Length (default 255).

Allow NULL by chaining nullable(): $schema->string('name', 100)->nullable().

SQL equivalent: VARCHAR(length) [NOT NULL | NULL]

Example:

$schema->string('name', 100)->nullable(); // `name` VARCHAR(100) NULL

6.2.3. integer()

Adds an INT column.

Syntax: integer($name)

Parameters:

$name: Column name.

Allow NULL by chaining nullable(): $schema->integer('age')->nullable().

SQL equivalent: INT [NOT NULL | NULL]

Example:

$schema->integer('age'); // `age` INT NOT NULL
$schema->integer('age')->nullable(); // `age` INT NULL

6.2.4. created_at / updated_at

Declare datetime columns with datetime(). Do not call timestamps() — that method does not exist. Use timestamp() only when you want a TIMESTAMP column.

Syntax: datetime('created_at') / datetime('updated_at')

SQL equivalent:

created_at DATETIME NOT NULL
updated_at DATETIME NOT NULL

Example:

$schema->datetime('created_at');
$schema->datetime('updated_at');

6.2.5. foreign()

Adds a foreign key.

Syntax: foreign($column, $name = null) then chain ->references($col)->on($table)->onDelete($action).

Parameters:

$column: Local column that holds the foreign key.
$name: Optional constraint name.

Chain references(), on(), and onDelete() on the object returned by foreign().

SQL equivalent: FOREIGN KEY (column) REFERENCES table (references) ON DELETE CASCADE

Example:

$schema->foreign('user_id')->references('id')->on('shop_items')->onDelete('CASCADE');

6.2.6. index()

Adds an index on one or more columns.

Syntax: index($columns, $name = null)

Parameters:

$columns: Column name or an array of column names.
$name: Optional index name.

SQL equivalent: INDEX (column)

Example:

$schema->index('name');

6.2.7. addColumn() (for ALTER TABLE)

Adds a new column to an existing table.

Syntax: addColumn($name, $type, $length = null, $nullable = false, $default = null, $comment = null)

Parameters:

$name: Column name.
$type: Type (for example VARCHAR, INT).
$length: Length (optional).
$nullable: Allow NULL (default false).
$default: Default value (optional).
$comment: Column comment (optional).

SQL equivalent: ADD column type [length] [NOT NULL | NULL]

Example:

$schema->addColumn('email', 'VARCHAR', 150, true);

6.2.8. dropColumn() (for ALTER TABLE)

Removes a column from a table.

Syntax: dropColumn($name)

Parameters:

$name: Column name.

SQL equivalent: DROP COLUMN column

Example:

$schema->dropColumn('email');

6.3. Using SchemaBuilder

Use SchemaBuilder with QueryBuilder methods createTable(), alterTable(), and dropTable(). Run them inside DB::module('RAW')->q(function ($qb) { ... })->execute($ok, $err). Alternatively, wrap the same QueryBuilder work in DB::module('RAW')->schema($callback, $success, $error). Always pass both callbacks to execute(), and always pass an error callback to schema().

6.3.1. Creating a table

createTable() creates a new table.

Example:

DB::module('RAW')->q(function ($qb) {
    $qb->createTable('shop_items', function ($schema) {
        $schema->id();
        $schema->string('name', 50);
        $schema->integer('age')->nullable();
        $schema->datetime('created_at');
        $schema->datetime('updated_at');
        $schema->index('name');
    });
})->execute(
    function ($result, $db, $debug) {
        echo "Table 'shop_items' was created.\n";
        echo "SQL: {$debug['query']}\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

The same DDL through schema():

DB::module('RAW')->schema(
    function ($qb) {
        $qb->createTable('shop_items', function ($schema) {
            $schema->id();
            $schema->string('name', 50);
            $schema->integer('age')->nullable();
            $schema->datetime('created_at');
            $schema->datetime('updated_at');
            $schema->index('name');
        });
    },
    function ($result, $db, $debug) {
        echo "Table 'shop_items' was created.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Generated SQL:

CREATE TABLE shop_items (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `age` INT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `idx_name` (`name`),
    CONSTRAINT `pk_id` PRIMARY KEY (`id`)
)

6.3.2. Altering a table

alterTable() changes an existing table.

Example:

DB::module('RAW')->q(function ($qb) {
    $qb->alterTable('shop_items', function ($schema) {
        $schema->addColumn('email', 'VARCHAR', 100, true);
        $schema->foreign('user_id')->references('id')->on('shop_items')->onDelete('CASCADE');
        $schema->dropColumn('age');
    });
})->execute(
    function ($result, $db, $debug) {
        echo "Table 'shop_items' was altered.\n";
        echo "SQL: {$debug['query']}\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Generated SQL:

ALTER TABLE shop_items
    ADD `email` VARCHAR(100) NULL,
    ADD FOREIGN KEY (`user_id`) REFERENCES `shop_items` (`id`) ON DELETE CASCADE,
    DROP COLUMN `age`

6.3.3. Dropping a table

dropTable() removes a table.

Example:

DB::module('RAW')->q(function ($qb) {
    $qb->dropTable('shop_items');
})->execute(
    function ($result, $db, $debug) {
        echo "Table 'shop_items' was dropped.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Generated SQL:

DROP TABLE shop_items

6.4. Advanced examples

Creating a table with foreign keys:

DB::module('RAW')->q(function ($qb) {
    $qb->createTable('shop_posts', function ($schema) {
        $schema->id();
        $schema->string('title', 200);
        $schema->integer('user_id');
        $schema->foreign('user_id')->references('id')->on('shop_items')->onDelete('CASCADE');
        $schema->datetime('created_at');
        $schema->datetime('updated_at');
    });
})->execute(
    function ($result, $db, $debug) {
        echo "Table 'shop_posts' created.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Bulk schema change in a transaction:

DB::module('RAW')->transact(
    function ($db, $commitOnSuccess, $rollbackOnError) {
        $db->q(function ($qb) {
            $qb->createTable('shop_items', function ($schema) {
                $schema->id();
                $schema->string('name');
            });
        })->execute($commitOnSuccess, $rollbackOnError);
        $db->q(function ($qb) {
            $qb->createTable('shop_posts', function ($schema) {
                $schema->id();
                $schema->integer('user_id');
                $schema->foreign('user_id')->references('id')->on('shop_items')->onDelete('CASCADE');
            });
        })->execute($commitOnSuccess, $rollbackOnError);
    },
    function ($result, $db, $debug) {
        echo "Schema change succeeded.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

6.5. Notes and limitations

Compatibility: Some features (for example ON DELETE CASCADE) do not behave the same on every engine. SQLite in particular has limited support for dropping columns, indexes, and foreign keys.

Transactions: For larger schema changes use DB::module('RAW')->transact(...) so the work stays consistent.

Debugging: Always inspect $debug['query'] (the third execute argument) to verify the generated SQL.

Exceptions: SchemaBuilder throws \InvalidArgumentException for invalid identifiers, unsupported types, and missing foreign-key targets. Wrap DDL in try/catch.

7. CacheDriverInterface

Databaser in the DotApp framework can cache query results so repeated requests for the same data skip the database. Query caching is enabled when Config::db('cache') === true. A custom store must expose get and set; attach it with DB::module()->cache($driverObject) (prefer that over DB::cache()). For ORM writes you also need deleteKeys(). This chapter describes the expected driver contract and how to use it with Databaser.

7.1. What is CacheDriverInterface?

CacheDriverInterface is the contract for storing and loading cached query results. Databaser can talk to any backing store (Memcached, Redis, the filesystem) as long as your object implements the methods below. After you assign a driver with cache(), Databaser tries get() before running a query and set() after a successful execution.

Important: Entity::save() with cache enabled requires deleteKeys() on the driver. No shipped driver implements deleteKeys(). Keep db.cache off unless you supply a custom driver that implements all four methods, including deleteKeys().

Benefits:

Lower database load.
Faster access to frequently requested data.
Flexibility — you can use any cache backend.

7.2. CacheDriverInterface methods

The interface defines four methods. Query caching uses get and set. Invalidation on Entity::save() also requires deleteKeys():

interface CacheDriverInterface {
    public function get($key);
    public function set($key, $value, $ttl = null);
    public function delete($key);
    public function deleteKeys($pattern);
}

7.2.1. get($key)

Loads a value from cache by key.

Parameter:

$key: String — unique key for the stored data.

Return value: The stored value, or null if the key does not exist.

Purpose: Databaser calls this method to check whether the query result is already cached.

7.2.2. set($key, $value, $ttl = null)

Stores a value in cache under the given key.

Parameters:

$key: String — storage key.
$value: Data to store (array, object, and so on).
$ttl: Lifetime in seconds (optional; null means no expiry at the driver level). Databaser always passes 3600.

Return value: None (or true/false depending on the implementation).

Purpose: After a successful query, Databaser stores the result in cache.

7.2.3. delete($key)

Removes a single key from cache.

Parameter:

$key: String — key to remove.

Return value: None (or true/false).

Purpose: Explicit deletion of one cache entry.

7.2.4. deleteKeys($pattern)

Removes multiple keys that match a pattern.

Parameter:

$pattern: String — key pattern (for example "shop_items:*").

Return value: None (or the number of deleted keys).

Purpose: Databaser calls this method when data changes (for example Entity::save() in ORM) so related cache entries are invalidated. If a cache driver is set and this method is missing, save() throws.

7.3. Implementing a custom cache driver

Example of a simple file-based cache driver. Treat this as a sample custom driver, not a shipped framework class:

class FileCacheDriver implements CacheDriverInterface {
    private $cacheDir;

    public function __construct($cacheDir = '/tmp/cache') {
        $this->cacheDir = $cacheDir;
        if (!is_dir($cacheDir)) {
            mkdir($cacheDir, 0777, true);
        }
    }

    public function get($key) {
        $file = $this->cacheDir . '/' . md5($key);
        if (file_exists($file)) {
            $data = unserialize(file_get_contents($file));
            if ($data['expires'] === null || $data['expires'] > time()) {
                return $data['value'];
            }
            unlink($file); // Expired — remove it
        }
        return null;
    }

    public function set($key, $value, $ttl = null) {
        $file = $this->cacheDir . '/' . md5($key);
        $expires = $ttl ? time() + $ttl : null;
        $data = ['value' => $value, 'expires' => $expires];
        file_put_contents($file, serialize($data));
        return true;
    }

    public function delete($key) {
        $file = $this->cacheDir . '/' . md5($key);
        if (file_exists($file)) {
            unlink($file);
            return true;
        }
        return false;
    }

    public function deleteKeys($pattern) {
        $count = 0;
        foreach (glob($this->cacheDir . '/*') as $file) {
            $key = basename($file);
            if (fnmatch($pattern, $key)) {
                unlink($file);
                $count++;
            }
        }
        return $count;
    }
}

Explanation:

get(): Reads data from a file if it has not expired.
set(): Writes data to a file with an optional TTL.
delete(): Removes a specific file.
deleteKeys(): Removes files matching a pattern (uses fnmatch).

7.4. Using a cache driver with Databaser

Enable query caching with Config::db('cache') === true. Then assign your store with DB::module()->cache($driverObject). The object must expose get($key) and set($key, $value, $lifetime). Prefer that over DB::cache().

$cacheDriver = new FileCacheDriver('/tmp/myapp_cache');
DB::module()->cache($cacheDriver);

// Example query with caching
DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('age', '>', 18);
})->execute(
    function ($result, $db, $execution_data) {
        echo "Results (from cache or DB):\n";
        var_dump($result);
        // On a cache hit, $execution_data is an empty array.
    },
    function ($error, $db, $execution_data) {
        echo "Error: {$error['error']}\n";
    }
);

How it works:

Databaser builds a key in the form "{table}:{returnType}:" . md5($query . serialize($bindings)) (for example shop_items:RAW: followed by the hash).
It checks cache with get(). On a hit it returns the stored value without querying the database and delivers an empty $execution_data to the success callback.
On a miss it runs the query and stores the result with set(). TTL is hardcoded to 3600 seconds.
On an ORM update such as Entity::save() it invalidates related keys with deleteKeys().

7.5. Advanced caching example

Caching with ORM and invalidation:

$cacheDriver = new FileCacheDriver();
DB::module()->cache($cacheDriver);

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();

$user = $rows[0] ?? null;
if ($user !== null) {
    $user->age = 40;
    $user->save(
        function ($result, $db, $execution_data) {
            echo "Item saved, cache invalidated.\n";
        },
        function ($error, $db, $execution_data) {
            echo "Error: {$error['error']}\n";
        }
    );
}

What happens:

The first query stores the Collection in cache.
On save(), deleteKeys("shop_items:ORM:*") runs and invalidates all ORM cache entries for shop_items.
If the assigned driver has no deleteKeys(), save() throws. Keep db.cache off unless your custom driver implements all four methods.

7.6. Notes and tips

TTL: Databaser stores query results for 3600 seconds.

Key format: Keys use "{table}:{returnType}:" . md5(...), so patterns such as "shop_items:*" match a table's entries.

Cache hits: The success callback still runs, but $execution_data is empty.

deleteKeys(): No shipped driver implements it. Keep Config::db('cache') off unless you supply a custom driver that implements get, set, delete, and deleteKeys().

Performance: For production, prefer a fast store such as Redis over files — still only after that store implements the four methods above.

Testing: Verify that deleteKeys() actually invalidates cache so you do not serve stale rows after save().

8. Working with Entity

An Entity represents one table row in the ORM module. Obtain entities through DB::module('ORM'), but read the first row safely with all() and $rows[0] ?? null.

Note on ORM relations: with(), whereHas(), and withCount() only store state in DotApp PHP Framework 2.0 and do not affect SQL. Do not present them as eager loading. Load relations by calling Entity methods, for example $user->hasMany('shop_posts', 'user_id').

8.2. Basic Entity usage

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('id', '=', 1)->limit(1);
})->all();

$user = $rows[0] ?? null;
if ($user) {
    echo $user->name;
    $user->age = 26;
    $user->save(
        function ($result, $db, $debug) {
            echo "User saved.\n";
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}

8.3. Relations

Relations are methods called on an entity. The optional callback can adjust the related query, for example with where(), orderBy(), or limit().

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('id', '=', 1)->limit(1);
})->all();

$user = $rows[0] ?? null;
$posts = $user ? $user->hasMany('shop_posts', 'user_id', null, function ($qb) {
    $qb->orderBy('created_at', 'DESC')->limit(2);
}) : [];

foreach ($posts as $post) {
    echo $post->title . "\n";
}

Relation methods available on Entity:

hasOne($relatedTable, $foreignKey, $localKey = null, $callback = null) → Entity|null
belongsTo($relatedTable, $foreignKey, $ownerKey = null, $callback = null) → Entity|null
hasMany($relatedTable, $foreignKey, $localKey = null, $callback = null) → Collection
morphOne($relatedTable, $typeField, $idField, $typeValue, $localKey = null, $callback = null) → Entity|null
morphMany($relatedTable, $typeField, $idField, $typeValue, $localKey = null, $callback = null) → Collection
morphTo($name = null, $type = null, $id = null, $ownerKey = null) is also available and returns Entity|null

Polymorphic relationship with a filter

A polymorphic relation stores the parent type and id on the related row. Pass a callback to filter, order, or limit the related query. Read the parent with all() and $rows[0] ?? null:

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('id', '=', 1)->limit(1);
})->all();
$item = $rows[0] ?? null;
if ($item) {
    $recentImages = $item->morphMany('shop_images', 'imageable_type', 'imageable_id', 'shop_items', null, function ($qb) {
        $qb->orderBy('created_at', 'DESC')->limit(3);
    });
    foreach ($recentImages as $image) {
        echo "Latest image: {$image->url}\n";
    }
}

8.4. Inserting a new Entity

$item = DB::newEntity();
$item->table('shop_items');
$item->name = 'Jane Novak';
$item->age = 30;
$item->save(
    function ($result, $db, $debug) {
        echo "New record created with ID: {$db->inserted_id()}.\n";
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

9. Working with Collection

A Collection is a set of entities returned by all(). Use iteration, filter(), map(), pluck(), toArray(), and count(). Do not use saveAll(); Entity::save() returns void, so save each entity individually and always pass an error callback.

9.2. Basic Collection usage

$items = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();

foreach ($items as $item) {
    echo "Name: {$item->name}\n";
}

$allItems = $items->all();
$first = $allItems[0] ?? null;

9.3. Filter, map, and individual saves

$items = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();

$active = $items->filter(function ($item) {
    return (int) $item->active === 1;
});

foreach ($active as $item) {
    $item->checked_at = date('Y-m-d H:i:s');
    $item->save(
        null,
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}

10. Database schema

You define table structure in code with SchemaBuilder and, in a module, through a versioned Installation.php installer. You can create, alter, and drop tables and columns. Wrap batch operations in a transaction with transact(). Never call DB::migrate(). There is no timestamps() helper — add datetime() columns explicitly when you need them.

10.2. What is schema management?

You define tables and relations programmatically. In Databaser this is done with SchemaBuilder; wrap batch changes in a transaction with transact(). Main advantages:

Automation: Database changes live in code and can be versioned.
Transactions: Batch operations are safe and reversible on error.
Multi-platform: Support for different drivers (MySQLi, PDO) with syntax adapted to the database.

10.3. Basic principles

Schema management in Databaser rests on these principles:

SchemaBuilder: Definition of tables and columns (for example id(), string(), foreign(), datetime()).
Installation.php: Versioned install and uninstall of module tables, guarded with self::alreadyDone and self::markDone.
Transactions: Batch changes via transact(), where several operations run as one unit.
Driver support: MySQLi and PDO adapt syntax to the database type (for example MySQL, PostgreSQL, SQLite).

10.4. Using the schema

Define table structure and apply it. Always pass an error callback to schema(). Example of creating a table:

DB::module('RAW')->schema(function ($schema) {
    $schema->createTable('shop_items', function ($table) {
        $table->id();
        $table->string('name');
    });
}, function ($result, $db, $debug) {
    echo "Table 'shop_items' was created successfully.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Output:

Table 'shop_items' was created successfully.

Batch change with a transaction (several tables):

DB::module('RAW')->transact(function ($db) {
    $db->q(function ($qb) {
        $qb->createTable('shop_items', function ($schema) {
            $schema->id();
            $schema->string('name');
        });
    })->execute(null, function ($error) {
        echo "Error: {$error['error']}\n";
    });
    $db->q(function ($qb) {
        $qb->createTable('shop_posts', function ($schema) {
            $schema->id();
            $schema->integer('user_id');
            $schema->foreign('user_id')->references('id')->on('shop_items')->onDelete('CASCADE');
        });
    })->execute(null, function ($error) {
        echo "Error: {$error['error']}\n";
    });
}, function ($result, $db, $debug) {
    echo "Schema change succeeded.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Output:

Schema change succeeded.

10.5. Available schema methods

Databaser provides the following methods for working with schema:

10.5.1. schema($callback, $success, $error)

Defines and runs a single schema operation (for example, creating a table). Always pass the error callback.

Syntax: schema(callable $callback, callable $success = null, callable $error = null)

Parameters:

$callback: Closure that defines the operation through SchemaBuilder.
$success: Callback on success.
$error: Callback on error (always pass this).

Example:

DB::module('RAW')->schema(function ($schema) {
    $schema->createTable('shop_items', function ($table) {
        $table->id();
        $table->string('email', 100);
    });
}, function ($result, $db, $debug) {
    echo "Table created.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Output:

Table created.

10.5.2. Module schema with `Installation.php`

Create and version module tables from Installation.php. Guard each version with self::alreadyDone and record it with self::markDone. Never call DB::migrate().

DB::module('RAW')->q(function ($qb) {
    $qb->raw(
        "CREATE TABLE IF NOT EXISTS `shop_items` (
            `id` INT NOT NULL AUTO_INCREMENT,
            `title` VARCHAR(200) NOT NULL,
            `created_at` DATETIME NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        []
    );
})->execute(
    function () { /* self::markDone('1.0.0'); */ },
    function ($error) { \Dotsystems\App\Parts\Logger::use()->error('schema failed', $error); }
);

10.5.3. transact($operations, $success, $error)

Runs a batch of schema changes inside a transaction. Call it on the module instance: DB::module('RAW')->transact(...).

Syntax: transact(callable $operations, callable $success = null, callable $error = null)

Parameters:

$operations: Closure with several schema operations. The first argument is the module instance ($db).
$success: Callback on success (committed).
$error: Callback on error (rolled back). Always pass this.

Example:

DB::module('RAW')->transact(function ($db) {
    $db->q(function ($qb) {
        $qb->createTable('shop_comments', function ($schema) {
            $schema->id();
            $schema->integer('post_id');
        });
    })->execute(null, function ($error) {
        echo "Error: {$error['error']}\n";
    });
}, function ($result, $db, $debug) {
    echo "Batch schema change succeeded.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Output:

Batch schema change succeeded.

10.5.4. SchemaBuilder::createTable($table, $callback)

Creates a new table with a defined structure.

Syntax: createTable(string $table, callable $callback)

Example:

DB::module('RAW')->schema(function ($schema) {
    $schema->createTable('shop_products', function ($table) {
        $table->id();
        $table->string('name');
        $table->decimal('price', 8, 2);
    });
}, function ($result, $db, $debug) {
    echo "Table 'shop_products' created.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

10.5.5. SchemaBuilder::alterTable($table, $callback)

Alters an existing table (for example, adds a column).

Syntax: alterTable(string $table, callable $callback)

Example:

DB::module('RAW')->schema(function ($schema) {
    $schema->alterTable('shop_items', function ($table) {
        $table->addColumn('age', 'INT', null, true);
    });
}, function ($result, $db, $debug) {
    echo "Column added.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

10.5.6. SchemaBuilder::dropTable($table)

Drops a table.

Syntax: dropTable(string $table)

Example:

DB::module('RAW')->schema(function ($schema) {
    $schema->dropTable('shop_items');
}, function ($result, $db, $debug) {
    echo "Table dropped.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

10.6. Practical examples

Creating tables with a foreign key:

DB::module('RAW')->transact(function ($db) {
    $db->q(function ($qb) {
        $qb->createTable('shop_items', function ($schema) {
            $schema->id();
            $schema->string('username');
        });
    })->execute(null, function ($error) {
        echo "Error: {$error['error']}\n";
    });
    $db->q(function ($qb) {
        $qb->createTable('shop_posts', function ($schema) {
            $schema->id();
            $schema->string('title');
            $schema->integer('user_id');
            $schema->foreign('user_id')->references('id')->on('shop_items')->onDelete('CASCADE');
        });
    })->execute(null, function ($error) {
        echo "Error: {$error['error']}\n";
    });
}, function ($result, $db, $debug) {
    echo "Tables created.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Output:

Tables created.

Altering a table (adding a column):

DB::module('RAW')->schema(function ($schema) {
    $schema->alterTable('shop_items', function ($table) {
        $table->string('email', 100);
    });
}, function ($result, $db, $debug) {
    echo "Email column added.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Output:

Email column added.

Drop tables from Installation::uninstaller() with DB::module('RAW')->q(...)->execute($ok, $err).

10.7. Notes

Transactions: Use transact() on the module instance for batch schema changes so the database stays consistent. You can also call transaction(), commit(), and rollback() on that instance.
Driver support: Syntax is adapted to the driver (for example MySQL vs. SQLite), but some features (for example ON UPDATE on Oracle) may not be fully supported.
Installing schema: Create module tables in Installation.php with self::alreadyDone / self::markDone. Never call DB::migrate().
Error callbacks: Always pass an error callback to schema(), execute(), and transact().

11. Case study: e-shop with ORM

This chapter is a practical case study that shows how to use Databaser and its ORM to build a simple e-shop. We design the database structure, create tables, seed them with data, and work with that data through Entity and Collection. The examples include error callbacks so you can apply robust error handling.

11.1. Designing the database structure

For the e-shop we will use these tables:

shop_customers: Customers and administrators.
shop_products: Products in the catalog.
shop_product_descriptions: Product descriptions (one product can have several, for example in different languages).
shop_orders: Orders.
shop_order_items: Order lines (products linked to orders).

SQL to create the tables

You can copy these statements and run them in a MySQL database:

-- Customers
CREATE TABLE shop_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    role ENUM('customer', 'admin') DEFAULT 'customer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products
CREATE TABLE shop_products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Product descriptions
CREATE TABLE shop_product_descriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    language VARCHAR(10) NOT NULL,
    description TEXT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES shop_products(id) ON DELETE CASCADE
);

-- Orders
CREATE TABLE shop_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES shop_customers(id) ON DELETE CASCADE
);

-- Order items
CREATE TABLE shop_order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES shop_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES shop_products(id) ON DELETE CASCADE
);

SQL to seed data

These statements fill the tables with sample data:

-- Customers
INSERT INTO shop_customers (name, email, role) VALUES
('Jane Novak', 'jane@example.com', 'customer'),
('Admin Peter', 'admin@example.com', 'admin');

-- Products
INSERT INTO shop_products (name, price, stock) VALUES
('White t-shirt', 15.99, 50),
('Black shoes', 49.99, 20),
('Winter jacket', 89.99, 10);

-- Product descriptions
INSERT INTO shop_product_descriptions (product_id, language, description) VALUES
(1, 'en', 'Comfortable white cotton t-shirt.'),
(1, 'sk', 'Comfortable white cotton t-shirt.'),
(2, 'en', 'Elegant black shoes for any occasion.'),
(3, 'en', 'Warm winter jacket with a hood.');

-- Orders
INSERT INTO shop_orders (customer_id, total_price, status) VALUES
(1, 65.98, 'pending'),
(1, 89.99, 'shipped');

-- Order items
INSERT INTO shop_order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 2, 15.99),
(1, 2, 1, 49.99),
(2, 3, 1, 89.99);

11.2. Implementation in Databaser with ORM

ORM examples use DB::module('ORM'), safe reads through all(), and explicit relations through hasMany(). Do not use with() as if it loaded related rows in SQL — it does not emit SQL.

11.2.1. Fetching a customer and their orders

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_customers')->where('id', '=', 1)->limit(1);
})->all();

$customer = $rows[0] ?? null;
$orders = $customer ? $customer->hasMany('shop_orders', 'customer_id') : [];
foreach ($orders as $order) {
    echo "Order #{$order->id}: {$order->status}\n";
}

11.2.2. Adding a new product with a description

DB::module('RAW')->transact(function ($db) {
    $product = $db->newEntity();
    $product->table('shop_products');
    $product->name = 'Green scarf';
    $product->price = 19.99;
    $product->stock = 30;
    $product->save(
        function ($result, $db, $debug) {
            $description = $db->newEntity();
            $description->table('shop_product_descriptions');
            $description->product_id = $db->inserted_id();
            $description->language = 'en';
            $description->description = 'Warm green scarf for winter.';
            $description->save(null, function ($error, $db, $debug) {
                echo "Error: {$error['error']}\n";
            });
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}, function ($result, $db, $debug) {
    echo "Product added.\n";
}, function ($error, $db, $debug) {
    echo "Transaction error: {$error['error']}\n";
});

11.2.3. Displaying an order with its items

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_orders')->where('id', '=', 1)->limit(1);
})->all();

$order = $rows[0] ?? null;
$items = $order ? $order->hasMany('shop_order_items', 'order_id') : [];
foreach ($items as $item) {
    $productRows = DB::module('ORM')->q(function ($qb) use ($item) {
        $qb->select('name', 'shop_products')->where('id', '=', $item->product_id)->limit(1);
    })->all();
    $product = $productRows[0] ?? null;
    if ($product) {
        echo "Item: {$product->name}, quantity: {$item->quantity}\n";
    }
}

11.3. Using validation

Add validation for a product before saving.

$product = DB::newEntity();
$product->table('shop_products');
$product->setRules([
    'name' => ['required', 'string', 'max:100'],
    'price' => ['required', 'numeric', 'min:0'],
    'stock' => ['integer', 'min:0']
]);
$product->name = 'This English product name is deliberately written to be longer than one hundred characters so that Databaser validation rejects it';
$product->price = -5;
$product->stock = 10;
$product->save(
    function ($result, $db, $debug) {
        echo "Product saved successfully.\n";
    },
    function ($error, $db, $debug) {
        echo "Validation failed: {$error['error']}\n";
    }
);

11.4. Notes on the case study

Transactions: Using transact() on the module instance keeps related writes consistent; error callbacks report failures.

Relations: Load related rows with explicit Entity methods such as hasMany(). with(), whereHas(), and withCount() are stubs and do not emit SQL — they are not a way to load relations.

Validation: Rules protect against invalid data and produce a clear error message.

Error handling: error callbacks let you react to problems (for example logging or user-facing notices). Always pass an error callback to Entity::save().

12. Tips and tricks

This chapter offers practical advice on using Databaser effectively in the DotApp Framework. It covers query optimization, security, and extension points.

12.1. Query optimization

Efficient queries are key to a fast application. A few tips:

Select only the columns you need: Instead of select('*', 'shop_items'), use specific columns, for example select('id, name', 'shop_items'). That reduces the amount of data transferred.

DB::module('RAW')->q(function ($qb) {
    $qb->select('id, name', 'shop_items')->where('age', '>', 18);
})->execute(
    function ($result, $db, $debug) {
        var_dump($result);
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

Use indexes: For frequent filters in where() (for example id, age), add indexes with schema():

DB::module('RAW')->schema(function ($schema) {
    $schema->alterTable('shop_items', function ($table) {
        $table->index('age');
    });
}, function ($result, $db, $debug) {
    echo "Index created.\n";
}, function ($error, $db, $debug) {
    echo "Error: {$error['error']}\n";
});

Paginate lists that can grow: Prefer paginate($perPage, $page) over raw limit() / offset() for accumulating lists (items, orders, logs). Use limit() and offset() only when you need a one-off slice.

$page = DB::module('RAW')->q(function ($qb) {
    $qb->select('id, name', 'shop_items')->orderBy('id', 'DESC');
})->paginate(20, 1);

foreach ($page['data'] as $row) {
    echo "{$row['name']}\n";
}

Cache repeated queries: If you have a cache driver implemented, use it to store results:

DB::module('RAW')->cache($myCacheDriver)->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->execute(
    function ($result, $db, $debug) {
        echo "Results from cache or DB: ";
        var_dump($result);
    },
    function ($error, $db, $debug) {
        echo "Error: {$error['error']}\n";
    }
);

12.2. Security (SQL injection prevention)

Databaser is designed with security in mind, but it is still worth knowing the proven practices:

Always use prepared statements: QueryBuilder escapes values automatically, so never interpolate variables into the query string.

Correct:

DB::module('RAW')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('name', '=', 'Jane');
})->execute(null, function ($error) {
    echo "Error: {$error['error']}\n";
});

Incorrect:

$name = "Jane'; DROP TABLE shop_items; --";
DB::module('RAW')->q(function ($qb) use ($name) {
    $qb->raw("SELECT * FROM shop_items WHERE name = '$name'");
})->execute(null, function ($error) {
    echo "Error: {$error['error']}\n";
}); // Dangerous!

Raw queries with RAW: If you use raw(), always pass values through bindings:

DB::module('RAW')->q(function ($qb) {
    $qb->raw('SELECT * FROM shop_items WHERE age > ?', [18]);
})->execute(null, function ($error) {
    echo "Error: {$error['error']}\n";
});

Validation rules in ORM: When saving data through Entity, set rules:

$rows = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items')->where('id', '=', 1)->limit(1);
})->all();
$user = $rows[0] ?? null;
if ($user) {
    $user->setRules(['name' => 'required|string|max:50']);
    $user->name = 'Jane';
    $user->save(
        null,
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
}

12.3. Extending Databaser with custom drivers

Register a custom database driver with Databaser::customDriver($name, $class). The class must expose public static function create(Databaser $db) and, inside create(), register the driver closures on that instance.

DB::addDriver() is not the module API for registering a driver. Driver classes may use it internally from create(); application and module code should call Databaser::customDriver($name, $class).

Closures to register: select_db, q, return, execute, first, all, raw, fetchArray, fetchFirst, newEntity, newCollection, inserted_id, affected_rows, schema, transaction, transact, commit, rollback.

Databaser::customDriver('custom', CustomDriver::class);
// CustomDriver::create(Databaser $db) registers the closures listed above.

12.4. ORM relations

Load related rows with explicit Entity methods such as hasMany(). with() in DotApp 2.0 does not generate SQL and is not a way to load relations:

$items = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();

foreach ($items as $item) {
    foreach ($item->hasMany('shop_posts', 'user_id') as $post) {
        echo "Item: {$item->name}, Post: {$post->title}\n";
    }
}

12.5. Batch operations with Collection

Do not use saveAll(). After map(), save each entity individually with save() and always pass an error callback:

$items = DB::module('ORM')->q(function ($qb) {
    $qb->select('*', 'shop_items');
})->all();

$items->map(function ($item) {
    $item->age += 1;
    $item->save(
        function ($result, $db, $debug) {
            echo "Item saved.\n";
        },
        function ($error, $db, $debug) {
            echo "Error: {$error['error']}\n";
        }
    );
    return $item;
});

Methods: filter(), map(), pluck().