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: 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.

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'].

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). 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.

<?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->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);
        }
        return Response::json(['status' => 1, 'item' => $row]);
    }

    public static function save($request)
    {
        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) {
            $qb->update('shop_items')->set(['title' => $title])->where('id', '=', $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().