AI blog · DotApp PHP Framework 2.0

How to use the database in DotApp PHP Framework

New Shop code talks to SQL through DB::module('RAW'), a query callback, and a terminal method. There is no DB::table() and no DB::get(). Tables this module owns are named shop_items — never items, never dotapp_* for module data. This article is the copy-paste contract: QueryBuilder, named RAW SQL (:email plus an array — that is still bound, not concatenated), all(), guarded singles, execute($ok, $err), paginate(), transactions, and Config::addDatabase.

Common mistakes

Canonical query

q() and qb() are aliases. Both return a query object. The terminal call decides the result. Prefer RAW so each row is an associative array. The chain below is QueryBuilder. Skip ahead to named RAW if you write SQL strings with :email / :iduser — that is the usual Shop style, and it is still bound.

use Dotsystems\App\Parts\DB;

$rows = DB::module('RAW')
    ->q(function ($qb) use ($limit) {
        $qb->select(['id', 'title', 'price'])
           ->from('shop_items')
           ->where('active', '=', 1)
           ->orderBy('id', 'DESC')
           ->limit($limit);
    })
    ->all();
    

all() on an empty match is []. That is always safe to foreach. There is no ->count() on the chain. Count with select('COUNT(*) as total') plus all(), or read paginate()['total'].

Named RAW SQL — still bound, still safe

Most Shop code in the wild is a SQL string plus a named array, not a long QueryBuilder chain. That is still the same DB::module('RAW')->q(...) path. The second argument of $qb->raw($sql, $bindings) is the parameter list. Write WHERE id = :iduser and pass ['iduser' => 7]. The driver binds the value. It does not glue 7 into the string. Same idea as :email with ['email' => $email]. Pick names you can read in the SQL.

Dialect in the string is yours: LIMIT 1, backticks, AUTO_INCREMENT lock the statement to that engine (often MySQL). QueryBuilder stays more portable. Named RAW is what most people type every day — and with bindings it is still the secure DotApp way.

use Dotsystems\App\Parts\DB;

$rows = DB::module('RAW')->q(function ($qb) use ($id) {
    $qb->raw(
        'SELECT id, title, price
         FROM shop_items
         WHERE id = :iduser AND active = :active
         LIMIT 1',
        [
            'iduser' => $id,
            'active' => 1,
        ]
    );
})->all();
$row = $rows[0] ?? null;
    

$term = 'cable';
$perPage = 20;
$offset = 0;
$rows = DB::module('RAW')->q(function ($qb) use ($term, $perPage, $offset) {
    $qb->raw(
        'SELECT id, title
         FROM shop_items
         WHERE title LIKE :q AND active = :active
         ORDER BY id DESC
         LIMIT :lim OFFSET :off',
        [
            'q' => '%' . $term . '%',
            'active' => 1,
            'lim' => $perPage,
            'off' => $offset,
        ]
    );
})->all();
    

Core auth tables use Config::db('prefix') (default dotapp_). Module data still lives in shop_*. A login-style lookup against prefixed users:

use Dotsystems\App\Parts\Config;
use Dotsystems\App\Parts\DB;

$rows = DB::module('RAW')->q(function ($qb) use ($email) {
    $qb->raw(
        'SELECT id
         FROM ' . Config::db('prefix') . 'users
         WHERE email = :email
         LIMIT 1',
        [
            'email' => $email,
        ]
    );
})->all();
$userId = $rows[0]['id'] ?? null;
    

Writes use the same bindings, then execute($ok, $err) — never skip the error callback.

$affected = 0;
DB::module('RAW')->q(function ($qb) use ($id, $title) {
    $qb->raw(
        'UPDATE shop_items
         SET title = :title
         WHERE id = :iduser',
        [
            'title' => $title,
            'iduser' => $id,
        ]
    );
})->execute(
    function ($result, $db, $exec) use (&$affected) {
        $affected = $exec['affected_rows'] ?? 0;
    },
    function ($error) {
        Logger::use()->error('shop_items raw update failed', $error);
    }
);
    

Terminal methods

One row: never unguarded first()

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

$row = $rows[0] ?? null;
if ($row === null) {
    return Response::json(['status' => 0, 'message' => 'Not found'], 404);
}
    

Writes: execute($ok, $err)

Insert, update, and delete end with execute(). The success callback receives $execution_data: affected_rows, insert_id, num_rows, result, query, bindings. On a cache hit that array is empty — always use ??. Passing null as the success callback is fine. Passing null as the error callback is not: a failure then throws.

$newId = null;
DB::module('RAW')->q(function ($qb) use ($title) {
    $qb->insert('shop_items', [
        'title' => $title,
        'active' => 1,
        'created_at' => date('Y-m-d H:i:s'),
    ]);
})->execute(
    function ($result, $db, $execution_data) use (&$newId) {
        $newId = $execution_data['insert_id'] ?? $db->inserted_id();
    },
    function ($error, $db, $execution_data) {
        Logger::use()->error('shop_items insert failed', $error);
    }
);
if ($newId === null) {
    return Response::json(['status' => 0, 'message' => 'Save failed'], 500);
}
    

Update uses update('shop_items')->set([...])->where('id', '=', $id) or the named RAW form UPDATE shop_items SET title = :title WHERE id = :iduser with ['title' => $title, 'iduser' => $id]. Delete uses delete('shop_items')->where('id', '=', $id). Read $exec['affected_rows'] ?? 0 in the success callback. Zero can mean the row was missing or the values did not change.

Pagination

Keys returned by paginate($perPage = 15, $page = 1): data, current_page, per_page, total, last_page, from, to, has_more_pages, prev_page, next_page.

$page = DB::module('RAW')
    ->q(fn($qb) => $qb->select('*')->from('shop_items')->orderBy('id', 'DESC'))
    ->paginate(20, $currentPage);

foreach ($page['data'] as $row) { /* ... */ }
$last = $page['last_page'];
    

Users, logs, items, orders, messages — any list that can grow — must call paginate() on the first ship. “There are only three rows now” is not an exception. Do not ->all() the table into a view. The pager in the browser is interactive AJAX (type="button" + $dotapp().load()), not <a href="?page=2"> and not a full reload. Walkthrough: How to build AJAX lists with pagination in DotApp PHP Framework.

Transactions

DB::module('RAW')->transaction();
try {
    DB::module('RAW')->q(function ($qb) use ($orderId) {
        $qb->insert('shop_orders', ['id' => $orderId, 'created_at' => date('Y-m-d H:i:s')]);
    })->execute(null, function ($error) {
        Logger::use()->error('order insert', $error);
        throw new \RuntimeException('order insert failed');
    });
    DB::module('RAW')->q(function ($qb) use ($orderId, $itemId) {
        $qb->insert('shop_order_items', ['order_id' => $orderId, 'item_id' => $itemId]);
    })->execute(null, function ($error) {
        Logger::use()->error('order item insert', $error);
        throw new \RuntimeException('order item insert failed');
    });
    DB::module('RAW')->commit();
} catch (\Throwable $e) {
    DB::module('RAW')->rollback();
    Logger::use()->error('order rolled back', ['msg' => $e->getMessage()]);
    return Response::json(['status' => 0, 'message' => 'Could not create order'], 500);
}
    

transaction(), commit(), and rollback() return $this. Callback form: transact($work, $onCommit, $onRollback).

Complete CRUD controller

File: app/modules/Shop/Controllers/Items.php. Browser posts still run crcCheck() when they arrive through load() or <fo-rm>. JSON bodies use Response::json. show() and update() use named RAW — that is the form most Shop code uses. index() and save() keep QueryBuilder so paginate() and insert() stay in the same file.

<?php
namespace Dotsystems\App\Modules\Shop\Controllers;

use Dotsystems\App\Parts\Config;
use Dotsystems\App\Parts\DB;
use Dotsystems\App\Parts\Logger;
use Dotsystems\App\Parts\Response;

class Items extends \Dotsystems\App\Parts\Controller
{
    public static function index($request)
    {
        $pageNum = (int) ($request->query()['page'] ?? $request->data()['page'] ?? 1);
        if ($pageNum < 1) { $pageNum = 1; }
        $perPage = (int) (Config::module('Shop', 'itemsPerPage') ?? 20);
        $page = DB::module('RAW')->q(function ($qb) {
            $qb->select(['id', 'title', 'price', 'active'])
               ->from('shop_items')->orderBy('id', 'DESC');
        })->paginate($perPage, $pageNum);
        return Response::json(['status' => 1, 'page' => $page]);
    }

    public static function show($request)
    {
        $id = (int) ($request->matchData()['id'] ?? 0);
        $rows = DB::module('RAW')->q(function ($qb) use ($id) {
            $qb->raw(
                'SELECT * FROM shop_items WHERE id = :iduser LIMIT 1',
                ['iduser' => $id]
            );
        })->all();
        $row = $rows[0] ?? null;
        if ($row === null) {
            return Response::json(['status' => 0, 'message' => 'Not found'], 404);
        }
        return Response::json(['status' => 1, 'item' => $row]);
    }

    public static function save($request)
    {
        // data(true) = original payload. data() is escaped — do not persist that copy.
        if (!$request->crcCheck()) {
            return Response::json(['status' => 0, 'message' => 'Bad request'], 400);
        }
        $title = trim((string) (($request->data(true)['data']['title'] ?? '')));
        if ($title === '') {
            return Response::json(['status' => 0, 'message' => 'Title required'], 422);
        }
        $newId = null;
        DB::module('RAW')->q(function ($qb) use ($title) {
            $qb->insert('shop_items', [
                'title' => $title,
                'active' => 1,
                'created_at' => date('Y-m-d H:i:s'),
            ]);
        })->execute(
            function ($result, $db, $exec) use (&$newId) {
                $newId = $exec['insert_id'] ?? $db->inserted_id();
            },
            function ($error) { Logger::use()->error('insert failed', $error); }
        );
        if ($newId === null) {
            return Response::json(['status' => 0, 'message' => 'Save failed'], 500);
        }
        return Response::json(['status' => 1, 'id' => $newId]);
    }

    public static function update($request)
    {
        if (!$request->crcCheck()) {
            return Response::json(['status' => 0, 'message' => 'Bad request'], 400);
        }
        $id = (int) ($request->matchData()['id'] ?? 0);
        $title = trim((string) (($request->data(true)['data']['title'] ?? '')));
        $affected = 0;
        DB::module('RAW')->q(function ($qb) use ($id, $title) {
            // Named RAW (usual Shop style). QueryBuilder equivalent:
            // $qb->update('shop_items')->set(['title' => $title])->where('id', '=', $id);
            $qb->raw(
                'UPDATE shop_items SET title = :title WHERE id = :iduser',
                ['title' => $title, 'iduser' => $id]
            );
        })->execute(
            function ($result, $db, $exec) use (&$affected) { $affected = $exec['affected_rows'] ?? 0; },
            function ($error) { Logger::use()->error('update failed', $error); }
        );
        return Response::json(['status' => 1, 'affected' => $affected]);
    }

    public static function delete($request)
    {
        if (!$request->crcCheck()) {
            return Response::json(['status' => 0, 'message' => 'Bad request'], 400);
        }
        $id = (int) ($request->matchData()['id'] ?? 0);
        DB::module('RAW')->q(function ($qb) use ($id) {
            $qb->delete('shop_items')->where('id', '=', $id);
        })->execute(
            function ($result, $db, $exec) { /* $exec['affected_rows'] */ },
            function ($error) { Logger::use()->error('delete failed', $error); }
        );
        return Response::json(['status' => 1]);
    }
}
    

Connections in app/config.php

Config::addDatabase('main', '127.0.0.1', 'user', 'pass', 'shopdb', 'UTF8', 'MYSQL', 'pdo');
Config::addDatabase('reporting', '10.0.0.5', 'ro', 'pass', 'reports', 'UTF8', 'MYSQL', 'pdo');
    

Arguments: connection name, host, user, password, database, charset, engine, driver. Config::db keys include prefix (default dotapp_, core auth tables only), driver (pdo), maindb (main), cache (false). A second connection: DB::module('RAW')->selectDb('reporting')->q(...)->all(). Create shop_items with Installation.php, not with DB::migrate().