CandyQuery

Terminal SQLite browser on the SugarCraft stack — port of jorgerojas26/lazysql, SQLite-only at v1.

composer require sugarcraft/candy-query
candy-query path/to/db.sqlite

Keys

Key	Action
Tab	Switch pane (Tables → Rows → Query)
↑/↓ or j/k	Move cursor in active list pane
Enter / Space	Load the highlighted table into the rows pane
Ctrl+R	Run the SQL in the query pane
Backspace	Delete last character (query pane)
q / Esc	Quit (except inside the query pane — q types)

Architecture

File	Role
Database	Thin PDO/SQLite wrapper. Throws RuntimeException on missing files; promotes PDO errors.
Pane	Enum for pane focus + next().
App (Model)	Tables list, rows pane, in-progress SQL editor buffer, error string, status string.
Renderer	Three rounded-border panes — tables, rows, query — with the focused pane getting a brighter accent.
SchemaBrowser	PRAGMA-based schema introspection (tables, columns, indexes, foreign keys). Returns immutable schema objects.
ResultPager	Cursor-based pagination for SQL result sets. Immutable + fluent nextPage()/prevPage().
CellEditor	Cell-level UPDATE by primary-key identity. updateCell(), updateRow(), readCell().
SnippetStore	File-backed JSON store for named SQL snippets. Immutable + fluent add()/delete()/find()/search(). Persists to /tmp/candy-query-snippets.json.
ExplainView	Renders EXPLAIN QUERY PLAN output as a colour-coded ANSI tree (SEARCH=cyan, SCAN=yellow, SUBQUERY=magenta, etc.). Static run() executes against a Database.
ResultTable	Renders SQL result sets with horizontal scrolling, JSON pretty-print (2-space indent), styled NULL token, and column auto-sizing. scrollLeft()/scrollRight() builders.

The PDO connection is the only stateful dependency; tests use a :memory: SQLite to exercise the full transition surface (load tables, switch panes, run query, error handling) without fixture files.

Multi-driver (MySQL / Postgres / Mongo) is a planned follow-up — the current Database class is a SQLite-only concrete; promoting it to an interface is a one-class job once the second driver lands.

Schema introspection

SchemaBrowser exposes SQLite schema via three PRAGMA queries:

$browser = (new SchemaBrowser($pdo))->refresh();
foreach ($browser->tables as $table) {
    echo $table->name;
    foreach ($table->columns as $col) {
        echo $col->name, ' ', $col->type, $col->primaryKey ? ' PK' : '';
    }
}

Schema value objects (SchemaTable, SchemaColumn, SchemaIndex, SchemaForeignKey) are all readonly classes with bare accessors.

Pagination

ResultPager wraps a full result-set and provides cursor-based navigation:

$pager = new ResultPager($allRows, pageSize: 25);
while ($pager->hasNextPage()) {
    $pageRows = $pager->page();   // list of row arrays
    // process $pageRows
    $pager = $pager->nextPage();  // new immutable instance
}

goToPage(1-based-int), prevPage(), and withPageSize(int) are also available. Page size defaults to 25 rows.

Cell editing

CellEditor targets a table + primary-key column at construction, then performs single-cell or multi-cell updates by row identity:

$editor = new CellEditor($pdo, 'users', 'id');
$editor->updateCell(rowId: 42, column: 'email', newValue: 'new@example.com');
$editor->updateRow(rowId: 42, cells: ['name' => 'Alice', 'email' => 'alice@example.com']);
$current = $editor->readCell(rowId: 42, column: 'email');

All identifiers are safely quoted via str_replace('"', '""', $name). updateCell and updateRow return rows affected (0 or 1).

Snippet store

SnippetStore persists named SQL snippets to a JSON file, surviving process restarts:

$store = SnippetStore::load();                    // load from /tmp/candy-query-snippets.json
$store = $store->add('active-users', 'SELECT * FROM users WHERE active = 1', 'Fetch active users');
$store->flush();                                   // persist to disk

$store->find('active-users');                     // → Snippet|null
$store->search('users');                           // → list<Snippet> (name or SQL match)
$store->delete('active-users')->flush();          // remove and persist

Snippet(name, description, sql, createdAt) is a plain readonly value object. The store is immutable — every mutation returns a new instance.

Query plan viewer

ExplainView parses and colour-renders SQLite's EXPLAIN QUERY PLAN output:

$view = ExplainView::run($db, 'SELECT * FROM users JOIN orders ON users.id = orders.user_id');
echo $view->render();   // ANSI coloured tree
print_r($view->toArray());  // JSON-serialisable structure

Each detail line is classified by op type: SEARCH (cyan), SCAN (yellow), USING (green), JOIN (purple), SUBQUERY (pink), COMPOUND (orange). Depth is inferred from SQLite's |-- / `-- tree prefixes.

Result table

ResultTable renders a SQL result set with horizontal scrolling, JSON pretty-print, and a styled NULL token:

$table = ResultTable::fromRows($rows)
    ->withVisibleWidth(80)
    ->withJsonPretty(true)
    ->withNullToken('∅');

echo $table->render();       // ANSI coloured
echo $table->renderPlain();  // plain text for copy/export

if ($table->canScrollRight()) {
    $table = $table->scrollRight();
}

Columns auto-size to the widest value; cells exceeding maxCellWidth (default 40) are truncated with …. Array/object values are JSON-encoded with 2-space indent when visibleWidth >= 80, collapsed to a single line otherwise.

Demos

SQL query execution

Query history cycling

Test

composer install
vendor/bin/phpunit