@kitlangton/sqli

Agent-friendly database investigation tools for Postgres and Mongo. The sqli CLI is designed for scripted investigation: normal query paths are read-only, while any Postgres mutation is a separate command that requires a human approval in the sqli-tui terminal app and is audit recorded.

Install

sqli is a Bun executable package. Install it globally for an agent or operator shell:

bun add --global @kitlangton/sqli
sqli --help

Or run without a global install:

bunx @kitlangton/sqli --help

The installed commands are:

| Command | Purpose | | --- | --- | | sqli | Non-interactive CLI intended for agents and scripts | | sqli-tui | Interactive query UI and human approval queue for writes |

Running sqli with no arguments opens the TUI when attached to an interactive terminal. In a pipe, subprocess, or other non-TTY agent context, running it with no arguments prints help instead.

Agent Quick Start

An agent should discover capabilities and connections before investigating data:

sqli --help
sqli pg --help
sqli pg profiles
sqli pg doctor --profile staging
sqli pg tables --profile staging --format json
sqli pg describe --profile staging --format json jobs
sqli pg query --profile staging --format json --sql "SELECT id, status FROM jobs ORDER BY id DESC LIMIT 20"

Rules for agent usage:

• Prefer pg query --format json --sql "..." for Postgres reads so output can be processed reliably.
• Start with bounded queries using LIMIT and identify schema through tables and describe before guessing table shape.
• Treat /pg and /mongo as slash-style read paths; validate an unfamiliar slash query with /dry-run first.
• Never attempt mutations through a read command. For a necessary Postgres write, use exactly one statement with pg execute and expect a human approval step.
• Do not request a write unless a human is available to run sqli-tui, inspect the exact queued SQL, and approve or deny it.

Read-only and validation examples:

sqli pg query --profile staging --format json --sql "SELECT id, status FROM jobs LIMIT 20"
sqli /dry-run /mongo "db.jobs.find({}).limit(5)"
sqli /mongo "db.jobs.find({}).limit(5)"

Write approval workflow:

# Human terminal: keep the interactive approver open.
sqli-tui

# Agent terminal: request one statement after explaining why it is needed.
sqli pg execute --profile staging --sql "UPDATE jobs SET status = 'retry' WHERE id = 42"

See docs/agent-usage.md for the operating contract an autonomous agent should follow.

Configuration

Postgres profiles are loaded from SQLI_CONFIG=/path/to/config.json or ~/.config/sqli/config.json. If neither exists, sqli creates an environment-backed env profile from SQLI_PG_*/PG* variables.

Use sqli.config.example.json as a safe template. Store password values only in environment variables named by passwordEnv.

mkdir -p ~/.config/sqli
cp sqli.config.example.json ~/.config/sqli/config.json
export SQLI_STAGING_PASSWORD="..."
sqli pg profiles
sqli pg doctor --profile staging

Profile commands can establish a configured tunnel before connecting. pg doctor prints connection resolution and missing secret information without printing password values.

Mongo

Mongo slash commands are constrained to the read-only expression allowlist and execute through mongosh. Configure SQLI_MONGO_URI, SQLI_MONGO_USERNAME, and SQLI_MONGO_PASSWORD; SQLI_MONGO_AUTH_DATABASE defaults to admin. Any proxy requirements can be supplied in the environment accepted by mongosh, such as ALL_PROXY. The runtime enforces the secondaryPreferred read preference and never prints configured credentials in the external command summary.

export SQLI_MONGO_URI="mongodb+srv://cluster.example.test/application"
export SQLI_MONGO_USERNAME="readonly_agent"
export SQLI_MONGO_PASSWORD="..."
sqli /dry-run /mongo "db.jobs.find({}).limit(5)"

Local Seeded Postgres

Start a disposable local Postgres database populated with generic sample workspaces, jobs, users, runs, and audit events:

bun run db:up
bun run cli:local pg tables
bun run cli:local pg query --format json --sql "SELECT * FROM jobs ORDER BY workspace_id"
bun run start:local

The seeded database runs in Docker at 127.0.0.1:55432/sqli_dev. It contains 48 jobs and 604 audit events so bounded loading and result navigation can be exercised. In the TUI, select postgres, type a query, press Tab to accept completions, and use result navigation controls after execution.

The TUI initially loads at most 200 Postgres rows. Results with more data show a + count and support L to load another 200 rows. Add ORDER BY when loading multiple chunks so paging remains stable. Result exploration supports arrow keys or h/j/k/l, ctrl-d/ctrl-u, horizontal reveal for wide results, and mouse-wheel vertical scrolling.

The CLI's ordinary query paths remain read-only. In the TUI, composing a write-capable Postgres statement opens an explicit approval screen; no write executes until it is approved and audited.

Useful pagination queries:

SELECT * FROM jobs ORDER BY id;
SELECT * FROM audit_events ORDER BY received_at DESC;

Reset all seeded data or stop the database with:

bun run db:reset
bun run db:down

For source development preflight, run bun run cli /doctor. CONNECTIONS.md documents project-local connection handoff details and is intentionally not part of the published package.

Package Development

bun install
bun run typecheck
bun run test
bun run cli --help
bun pm pack --dry-run

The NPM package is scoped as @kitlangton/sqli, installs the sqli and sqli-tui binaries, and intentionally packages runtime source, public docs, and the non-secret config template only. It does not expose a GitHub repository URL in package metadata.

License

MIT. See LICENSE.