trail / db
Database addon for trail
Requires
- php: >=8.4
- ext-pdo: *
- trail/framework: ^0.5.0
Requires (Dev)
None
Suggests
- trail/twig: `pageUrl`, `previousUrl`, and `nextUrl` functions
Provides
None
Conflicts
None
Replaces
None
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)