Metadata-Version: 2.5
Name: cli2ui
Version: 1.14.0
Summary: A local web UI over DB CLI commands (PostgreSQL & MySQL)
Author: Hitoshi Tabata
License-Expression: MIT
License-File: LICENSE
Classifier: Framework :: Django
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Requires-Python: >=3.11
Requires-Dist: cryptography>=49.0.0
Requires-Dist: django<6.0,>=5.1
Requires-Dist: psycopg2-binary>=2.9.10
Requires-Dist: pymysql>=1.1.1
Description-Content-Type: text/markdown

# cli2ui

**CLI database ops, as buttons.** A web UI over the database CLI commands you
keep half-remembering. No AI, no magic — just `psql` operations turned
into clicks, running fully on your machine.

> ⚠️ **Local-only by design.** cli2ui has no authentication and binds to your
> machine. Run it on localhost or inside a trusted network — never expose it to
> the public internet. If you must reach it remotely, read
> [README.HOSTED.md](README.HOSTED.md) first.

```
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
```

Built for **app developers and solo developers** — not DBAs. The pitch is
*"you're looking at your tables 3 minutes after deciding to"* — no install
marathon, no connection-wizard maze, no digging through nested trees. And no
"now go find a demo database" homework, either: a sample database is bundled
and pre-wired into the connection form, with a realistic
[Airlines dataset](#want-more-realistic-data) one opt-in away.

The UI ships in **English and Japanese** — a header toggle switches between
them, and it auto-detects your browser's language on first visit.

It ships **light and dark**, too, plus a custom theme: pick a ground, a text
colour and an accent, and the other fourteen are derived from them. The choice
stays in your browser — it is never sent to the server.

## What it looks like

![Workspace overview — every panel one click away, with live counts](docs/screenshots/overview.png)

| Table detail | SQL runner |
| :----------: | :--------: |
| ![Table detail](docs/screenshots/table-detail.png) | ![SQL runner](docs/screenshots/sql-runner.png) |

## Quick start

### Instant start with uv / pipx (No Docker required)

If you have [uv](https://docs.astral.sh/uv/) (recommended):

```bash
uvx cli2ui
```

Or with `pipx`:

```bash
pipx run cli2ui
```

This starts cli2ui locally and opens your browser at `http://127.0.0.1:8000/`. It connects directly to your host databases (`localhost:5432` / `localhost:3306`) with zero Docker networking friction.

### With Docker Compose (includes sample database)

```bash
git clone https://github.com/MR-TABATA/cli2ui
cd cli2ui
docker compose up
```

The `git clone` fetches the `docker-compose.yml` (and the sample database) that `docker compose up` runs.

**Docker only, no clone:** the image runs by itself. You get the app without the bundled sample database,
so connect it to your own PostgreSQL or MySQL in the connection form:

```bash
docker run --rm -p 8000:8000 jiniie/cli2ui
```

To reach a database on your own machine from inside the container, use the host `host.docker.internal`
(on Linux, add `--add-host=host.docker.internal:host-gateway`). See also the
[Docker Hub page](https://hub.docker.com/r/jiniie/cli2ui).

This pulls the published image
([`jiniie/cli2ui`](https://hub.docker.com/r/jiniie/cli2ui), amd64 and arm64) —
there is nothing to build and nothing to compile.

Then open <http://localhost:8000>. The connection form is pre-filled to point at
a bundled sample database — just hit **Connect** and you'll see its tables.

> **Building from source instead:** copy `docker-compose.override.yml.example`
> to `docker-compose.override.yml`. It builds the image from your working tree
> and mounts the tree for live reload — that is the development setup.
>
> **Pinning a version:** set `CLI2UI_IMAGE=jiniie/cli2ui:1.3.0` in `.env`.

To point at your own PostgreSQL, change the form fields (host, port, db, user,
password) and connect.

> **Port 8000 already taken?** It's a common default, so a clash is likely. Just
> remap the *host* side in `docker-compose.yml` — `"8000:8000"` → `"8001:8000"`
> (any free port works) — and open that port instead. Only the number on the
> left changes; the app keeps listening on 8000 *inside* the container.

### Want more realistic data?

The bundled `shop` database is deliberately tiny. For something with real
volume — enough rows for the health checks, activity views, and jsonb tooling
to have something to chew on — there's an opt-in second database: the
[Postgres Pro "Airlines" demo dataset](https://postgrespro.com/education/demodb)
(flights and bookings, ~250MB restored, jsonb columns included).

```bash
seed/demo/fetch.sh                            # downloads the dump (~60MB, not committed)
docker compose --profile demo up -d airlines  # first boot loads it — takes a few minutes
```

Then connect with host `airlines`, database `demo`, user `demo`, password `demo`
(port `5434` if connecting from your host machine). The loaded data persists in
a named volume, so the slow load only happens once. `fetch.sh` also accepts
`small` or `big` if you want a different dataset size.

## Connecting to a database in another container

cli2ui runs in its own container, so "localhost" in the connection form means
*the cli2ui container*, not your machine. Your database almost always lives
somewhere else — another container, another compose project, or a native
install.

> **New to Docker networking?** Don't guess — **[README.NETWORKING.md](README.NETWORKING.md)**
> walks through *why* `localhost` fails and gives a copy-paste fix for every
> setup (native DB, another compose project, a published port), with diagrams
> and a full worked example.

The short version — three ways to reach it (no code changes, just how you fill
the form):

1. **Via the host (simplest).** If the database publishes a port on your machine
   (e.g. `-p 5432:5432`), set the connection **host** to `host.docker.internal`
   and the **port** to the published one. Works the same on macOS, Windows, and
   Linux — the `extra_hosts` entry in `docker-compose.yml` makes the name
   resolve everywhere.

2. **Share a network, connect by container name.** Put both on one external
   network and use the database's container name as the host:

   ```bash
   docker network create cli2ui-net
   # then in BOTH compose files:
   #   networks:
   #     default:
   #       name: cli2ui-net
   #       external: true
   ```

3. **Attach at runtime.** Join the running cli2ui container to the database's
   existing network, then use the DB container name as the host:

   ```bash
   docker network connect <db-network> <cli2ui-app-container>
   ```

## Trying replication locally

The Replication panel reads `pg_stat_replication` / `pg_replication_slots`, so
its **Standbys** table stays empty until a replica is actually attached. To
watch it populate, spin up a throwaway primary + standby pair and point cli2ui
at the primary — see **[README.REPLICATION.md](README.REPLICATION.md)** for a
copy-paste compose file and the exact connection details.

## Status

v1.14.0 — a multi-DB (PostgreSQL & MySQL) ops console. `docker compose up` →
connect → browse your tables in a DB-client layout (table list in the sidebar,
table detail in the main pane).

- ✅ PostgreSQL: connect + list tables (estimated row counts, materialized
  views and foreign tables included, partitions nested under their parent,
  `UNLOGGED` flagged)
- ✅ Table detail: column definitions with column & table comments and
  generated-column expressions (`\d+ table`), an on-demand **JSON shape** view
  on `json`/`jsonb` columns (sampled top-level keys, value types, nesting depth,
  GIN coverage — describes the rows examined, not every row),
  a row preview, and a **filter builder** — stack column / operator / value rows
  (ANDed) into a read-only `SELECT … WHERE`, no SQL typing
- ✅ SQL runner: read-only ad-hoc queries by default (`SET TRANSACTION READ ONLY`
  + `statement_timeout` + 1000-row cap), with an opt-in **write mode** that
  commits — guarded by a whole-database safety snapshot taken before each write
  and, unless you turn it off, the same 2s `lock_timeout` the DDL buttons use
- ✅ Export to CSV / JSON: download a full query result set (past the display cap)
  or an entire table's rows, streamed through a server-side cursor (UTF-8 BOM on
  CSV for Excel)
- ✅ CSV import: append rows from a CSV into an existing table (`COPY`, matched by
  header name) in one all-or-nothing transaction, with a safety snapshot first
- ✅ EXPLAIN snapshots + diff: save query plans and diff two (before/after an
  index) instead of copy-pasting plans into a scratch file
- ✅ Scale simulation: EXPLAIN the same query at 1× / 100× / 10000× the real row
  counts (a what-if `pg_class` edit, always rolled back) to see where the plan
  shape breaks as data grows
- ✅ Index lab: measure a hypothetical index (`EXPLAIN ANALYZE` before/after, the
  index created and rolled back in one transaction) before you commit to creating it
- ✅ Activity: running queries + connections from `pg_stat_activity`, with
  one-click cancel (`pg_cancel_backend`) / kill (`pg_terminate_backend`)
- ✅ Objects browser: databases (`\l`), schemas (`\dn`), roles (`\du`) — a read-only
  listing, with create / rename / alter / drop (and DB clone via `TEMPLATE`) behind
  confirm gates in a side drawer
- ✅ `postgresql.conf` editor: read/edit parameters via `pg_settings` +
  `ALTER SYSTEM SET` + `pg_reload_conf()`, with reload-vs-restart badges
- ✅ Locks: the wait-for chain folded into a tree — the **head blocker** at the
  top (cancel / kill it to free the whole chain beneath) with the blocked
  sessions nested under it (`pg_locks` + `pg_blocking_pids`), blocking cycles
  detected and flagged, one-click cancel / kill on any session
- ✅ Replication: readiness check (`wal_level` / `max_wal_senders`) + WAL position,
  connected standbys (`pg_stat_replication`), and slot create / drop
- ✅ Health: table sizes, unused indexes, **unindexed foreign keys** (FK columns
  with no index the planner can use for them — Postgres doesn't auto-create one,
  and a partial or invalid index doesn't count), **redundant indexes**
  (a non-unique index that's a leading prefix of, or identical to, another),
  **orphan rows** (foreign keys left `NOT VALID`, plus `*_id` columns with no FK
  that name-match a table's primary key — each with an on-demand, read-only
  anti-join count that never validates or changes anything),
  dead-rows / vacuum, and a stats-only bloat estimate — read-only catalog facts,
  no advice
- ✅ Dependencies: the foreign-key graph topologically sorted (`graphlib`) into a
  safe TRUNCATE/DELETE order and its reverse (the load order), with foreign-key
  cycles detected and named — read-only, nothing is truncated (PostgreSQL & MySQL)
- ✅ Extensions: what's installed in this database and what the server could
  install (the Web equivalent of `\dx` unioned with `pg_available_extensions`),
  with an update-available badge when an installed version lags the server's
  default — read-only; `CREATE EXTENSION` stays in the SQL runner
- ✅ Command history: SQL run through the runner, logged to the management DB —
  status, row count, timing, and one-click re-open
- ✅ Structural changes that give up instead of piling up: every `ALTER` / `DROP` /
  `TRUNCATE` / rename on a table, column, schema or role runs with a short
  `lock_timeout` (2s; `lock_wait_timeout` on MySQL, whose own default is a year),
  so a change that can't get its exclusive
  lock fails in seconds — telling you nothing was changed and where to look —
  instead of waiting behind an open transaction while every later query, plain
  `SELECT`s included, queues behind *it*. `CREATE`/`DROP INDEX CONCURRENTLY` are
  exempt on purpose: they block nobody, and timing them out is what leaves an
  invalid index behind. Write mode in the SQL runner offers the same guard as a
  checkbox — on by default, off when you mean to wait
- ✅ Backup / restore: automatic table snapshots (`pg_dump` custom format) before
  every destructive or structural change (drop / truncate / rename / alter), kept
  under a per-connection size budget (oldest pruned automatically — tune with
  `CLI2UI_MAX_AUTO_BACKUP_BYTES` per snapshot and `CLI2UI_MAX_AUTO_BACKUP_TOTAL_BYTES`
  in total), plus restore of an uploaded dump — streamed to the client tool (not
  buffered in memory) — into a new database or, with a type-gate, an existing one
- ✅ Workspace overview: a bento dashboard summarizing every panel (live counts
  for activity, snapshots, backups, replication readiness…), one click away
- ✅ Internationalization: full English / Japanese UI with a header toggle
  (cookie-persisted, falling back to the browser's `Accept-Language`)
- ✅ MySQL (8.0+): the same console over a MySQL connection — browse, read-only +
  write query runner, filter builder, CSV import, CSV/JSON export, `EXPLAIN` (into
  the shared plan tree, so snapshots/diff work), index + table + column DDL,
  database create/drop, activity (`SHOW PROCESSLIST`), locks
  (`performance_schema`), health (unused indexes / sizes), backup & restore
  (`mysqldump`/`mysql`), a settings editor (`SET PERSIST`), and replication
  status. Features with no MySQL equivalent (vacuum/bloat, schemas separate from
  databases, the planner what-if lab, replication slots) show as "not applicable"
  rather than an empty card.

## Stack

| Layer        | Choice                | Why |
| :----------- | :-------------------- | :-- |
| Frontend     | htmx + Alpine.js      | Click → swap in HTML. No SPA build needed. |
| Backend      | Django                | Battle-tested psycopg2; management plumbing for free. |
| Management DB| SQLite                | Saved connections + (later) history. Zero extra infra. |
| Infra        | Docker                | Local-only, self-contained. No DB creds ever leave your network. |

## Not doing (on purpose)

- **No AI.** API key management + sending your schema to a third party is a
  non-starter for the people this is for.
- **No SaaS.** The moment we'd hold your DB connection info, the liability and
  encryption story swamps the project. Local-only keeps it honest.

## Security

cli2ui is **local-only by design** — no SaaS, no auth, no outbound calls — so the
threat model is narrow: it runs next to your database on your machine. Even so,
the destructive surface is taken seriously: identifiers are bound with
`psycopg2.sql.Identifier`, the SQL runner defaults to `SET TRANSACTION READ ONLY`,
what-if features always `ROLLBACK`, CSRF is enforced, and `DEBUG` is off by
default. The full threat model and static-analysis results live in
[specs/security-check.md](specs/security-check.md). To report a vulnerability,
see [SECURITY.md](SECURITY.md).

**Saved passwords are encrypted.** The password of a saved connection is never stored in
plain text: it is encrypted (Fernet) in the management database and decrypted only when a
connection is opened. The key is a file, `secret.key`, created next to `db.sqlite3` the first
time it is needed (in Docker, in the `/data` volume), readable by its owner only — or the keys
you put in `CLI2UI_SECRET_KEYS` (`python manage.py generate_secret_key` makes one). A database
file that leaks on its own does not give up the passwords; one that leaks together with its key
file does, so keep them apart when you back up. Lose the key and the saved passwords are gone:
the connections have to be entered again. `python manage.py encrypt_secrets` encrypts anything
still in plain text and re-encrypts the rest with the newest key (how a key is rotated).

Need to reach it from another network? See
[Hosted mode](README.HOSTED.md) ([日本語](README.HOSTED.ja.md)).

## Local development (without Docker)

```bash
pip install -r requirements.txt
python manage.py migrate
python manage.py runserver
```

You'll need a PostgreSQL to connect to (the `sampledb` service in
`docker-compose.yml` is one option, exposed on `localhost:5433`).

`DEBUG` is **off by default** so error pages don't leak tracebacks, settings, or
SQL. Set `DJANGO_DEBUG=1` while developing if you want Django's rich error pages.

## Contributing

Issues and PRs welcome. See [CONTRIBUTING.md](CONTRIBUTING.md) for how to run the
app, the test suite, and the UI conventions ([STYLE.md](STYLE.md)) new panels
follow.

## License

[MIT](LICENSE) © TABATA Hitoshi
