DEV Community

Cover image for I turned the psql commands I keep forgetting into buttons
ひとし 田畑
ひとし 田畑

Posted on

I turned the psql commands I keep forgetting into buttons

#postgres #django #showdev #webdev

You know the command exists. You've run it a dozen times. But every single time you need to see what's actually running on your database, you end up typing some variant of:

SELECT pid, state, query, wait_event_type
FROM pg_stat_activity
WHERE state != 'idle' AND pid != pg_backend_pid();
Enter fullscreen mode Exit fullscreen mode

…and then googling pg_stat_activity columns halfway through because you forgot whether it's wait_event or wait_event_type.

I got tired of that. So I built cli2ui — a small web UI that turns the database CLI commands you keep half-remembering into buttons.

psql -c "SELECT * FROM pg_stat_activity"   →  one "running queries" button
pg_dump -t users mydb                       →  one "back up this table" button
SELECT pg_terminate_backend(pid)            →  one "kill this process" button
Enter fullscreen mode Exit fullscreen mode

No AI. No SaaS. No magic. It runs on your machine, right next to your database, and your connection credentials never leave your network.

What it actually does

It's built for app developers and solo developers — not DBAs. The pitch is "you're looking at your tables 3 minutes after deciding to": docker compose up, connect, done. No install marathon, no connection-wizard maze, no digging through nested trees.

A few of the things it puts one click away (PostgreSQL today):

  • Browse tables with estimated row counts, column definitions (\d table), and a row preview.
  • A SQL runner that's read-only by default — it opens the statement in a SET TRANSACTION READ ONLY transaction (so the server refuses writes), with a statement_timeout and a 1000-row cap. Write mode is opt-in and takes a whole-database safety snapshot before it commits.
  • EXPLAIN snapshots + diff — save two plans and diff them (before/after an index) instead of pasting plans into a scratch file.
  • A what-if index lab — try a hypothetical index inside a transaction that's always rolled back, and see the before/after plan and timing. Nothing is committed.
  • A scale simulation — EXPLAIN your query at 1×, 100×, and 10000× the real row counts to see where the plan shape breaks as data grows.
  • Activity / Locks — pg_stat_activity and the blocking tree from pg_locks + pg_blocking_pids, with one-click cancel / kill.
  • Health — largest tables, unused indexes, dead rows, and a stats-only bloat estimate (no scan).
  • Replication — a readiness check (wal_level / max_wal_senders), connected standbys, slot create/drop, and a copy-paste standby-setup recipe with your host/port/user already filled into the pg_basebackup command.
  • postgresql.conf editor — read/edit via pg_settings + ALTER SYSTEM SET + pg_reload_conf(), with reload-vs-restart badges.
  • Backup / restore — automatic pg_dump snapshots before every destructive change, restore of an uploaded dump streamed to the client tool (not buffered in memory).

The UI ships in English and Japanese with a header toggle.

The design decisions that made it simple

Local-only, on purpose

cli2ui has no authentication layer. That sounds reckless until you look at the threat model: it's a trusted local tool sitting next to your database, not a multi-tenant service on the internet. No accounts, no outbound calls, no AI. Your DB credentials stay on your machine.

The moment you'd hold someone's database connection info on a server, the liability-and-encryption story swallows the whole project. Staying local keeps it honest — and the README is very loud about "don't expose this to an untrusted network."

Destructive stuff is taken seriously anyway

Being local doesn't mean being careless:

  • Schema / table / column names are bound with psycopg2.sql.Identifier; the few raw-SQL spots (e.g. index access method, column type) go through fixed allow-lists.
  • The SQL runner enforces read-only at the server, not by scanning your SQL for the word "DELETE."
  • The what-if features run with autocommit=False and always ROLLBACK.
  • Every drop / truncate / rename / write takes an automatic snapshot first, and those snapshots are capped by total size so the local SQLite store can't grow forever.
  • CSRF is on (so a random webpage can't fire a cross-origin POST at localhost and drop your database), and X-Frame-Options: DENY stops clickjacking the destructive buttons.

Boring stack, fast to build

  • Django + htmx + a sprinkle of Alpine.js. No SPA, no build step, no node_modules. Panels are htmx partials; the page swaps innerHTML and that's the whole interaction model.
  • SQLite for the management DB (saved connections, command history, snapshots) — it's a single-user local tool, so that's plenty.
  • One engine interface hides the database dialect, so each panel is a self-contained "1 engine method + 1 view + 1 template + a nav button."

That last bit is the thing I'm happiest about: adding a feature is small and mechanical, which is why the PostgreSQL coverage got deep instead of wide.

Try it 

git clone https://github.com/MR-TABATA/cli2ui
cd cli2ui
docker compose up
# then open http://localhost:8000
Enter fullscreen mode Exit fullscreen mode

The connection form is pre-filled to point at a bundled sample database — hit Connect and you're looking at its tables. To point at your own PostgreSQL, change the form fields. (Connecting to a database in another container trips everyone up — there's a whole networking guide for that.)

What it's not doing (on purpose)

  • Not multi-user, not a hosted service. Local-only is the whole point.
  • No MySQL yet. The engine layer is ready for it; PostgreSQL just came first.
  • Not a replacement for a real DBA toolkit — it's the "3 minutes after deciding to" tool.

It's MIT-licensed and the code is on GitHub. The landing page is at cli2ui.com.

If you've ever rage-googled pg_stat_replication columns, I'd love your feedback — especially on what command you reach for most that should be a button.

Top comments (0)