Search by

trail / db

dodgycoffee

Database addon for trail

Package info

source.tube/brindly/trail-db

Issues

Type:trail-addon

pkg:composer/trail/db

Statistics

Installs: 135

Dependents: 5

Suggesters: 0

0.5.0 2026-11-16 22:04 UTC

This package is auto-updated.

Last update: 2026-11-16 23:53:56 UTC


README

Database addon for trail framework

install

composer require trail/db

run migrations

trail/trail db::migrate

write migrations

Theres a helper at $database->id() which gives an auto increment primary key for the db engine. made it easier for me

$database->jsonType() does the same for json columns. $database->driver() says which db engine it is (mariadb is mysql)

$database->textType() is for large text. mariadb text caps at 64 KB

$database->exact() mariadb case-sensitive solution

return static function (Connection $database): void {
    $database->run("
        create table notes (
            id {$database->id()},
            body text not null
        )
    ");
};

where clauses

Clauses builds where that changes at runtime

use Trail\Db\Query\Clauses;

$where = new Clauses();
$where->add('section = ?', 'news');

if ($status) {
    $where->add('status = ?', $status);
}

$where->in('author_id', $authorIds);

$database->all("select * from entries {$where->sql()} order by posted_at desc", $where->params());

pagination

There's pagination helpers now

use Trail\Db\Query\Pagination;

$posts = $database->page('select * from posts where board = ? order by id desc', [$boardId], Pagination::requested($request), 20);

$posts->total;
$posts->pages();
$posts->window();          // [1, null, 4, 5, 6, null, 20]
$posts->nextUrl($request);

// cap the count for large tables
$database->page($sql, $params, $page, 20, cap: 10000);

// anything else
Pagination::slice(count($rows), $page, 20, fn (int $limit, int $offset): array => array_slice($rows, $offset, $limit));

scroll

Only works for next/previous. Good for feeds, logs, etc. Scrolly stuff

use Trail\Db\Query\Scroll;

$payments = $database->scroll("
    select p.*, coalesce(p.paid_at, p.created_at) as paid_on
    from payments p
    where p.client_id = ?
", [$clientId], ['paid_on' => 'desc', 'id' => 'desc'], Scroll::requested($request), 20);

$payments->nextUrl($request);       // ?after=
$payments->previousUrl($request);   // ?before=
{# Twig::extension(PaginationTwig::class) #}
<a href="{{ previousUrl(posts) }}">newer</a>
<a href="{{ pageUrl(posts, 3) }}">3</a>
<a href="{{ nextUrl(posts) }}">older</a>

settings

Allows an app to save config keys in the database instead of just the config file. By default a config file value will override the DB. But you can reverse that

'settings' => true,

// database priority
'settings' => ['order' => 'db'],
use Trail\Db\Settings\Settings;

$settings->set(['name' => 'EWWWW', 'timezone' => 'Asia/Tokyo', 'auth.passwordSignup' => false]);
$settings->set(['super.secret' => $key], isSecret: true);   // encrypted with TRAIL_KEY
$settings->set(['unsplash' => '$UNSPLASH_KEY']);            // read from env

Config::main('timezone');                                    // normal
$settings->isPinned('timezone');                             // file set. lock field
$settings->get('unsplash');                                  // get value
$settings->forget('name');                                   // on next request

If you rotate your key with trail/trail key::rotate, make sure to run trail/trail settings::reseal afterward

checkpoint (sqlite)

trail/trail db::checkpoint

sqlite runs with a persistent connection, so writes sit in db.sqlite-wal till they get added to the db. Run this before copying db file anywhere to make sure its updated

Notes

Supports sqlite, mariadb, and postgres. Not proper mysql (f*ck oracle)