# visualdb > visualdb user manual: installation, connections, sheets, forms, reports with grouping and subtotals, saved views, snapshots, master-detail, schema management, webhooks, RLS, AI assistant and packaging. Published: 2026-08-24 Updated: 2026-08-25 Practice: software Repository: Page: --- User manual for **visualdb**: a **local**, self-contained application to build **forms, sheets (grids) and reports** over existing databases, in the spirit of Visual DB. It runs entirely on the installation machine — with no cloud service required to run and no remote account. Two integrations are, however, **optional and can be turned off**, and if enabled they do leave the machine: natural-language SQL generation (a cloud LLM provider, with the user's API key) and webhooks. With those off the application is entirely local. The *target* databases may be local or remote, but the app and its metadata always stay local. The authoritative reference is the GitHub repository. ## What it is and what it is for visualdb generates interfaces from a **query-spec** (a JSON specification): forms, sheets and reports are just different *renders* of the same spec. It is a **monolithic application** — a single Python codebase (UI [NiceGUI](https://nicegui.io/), SQLAlchemy Core 2.0 engine), one process, one executable. The same code runs as a **native desktop window** or as a local **web app** on `127.0.0.1`. It is for anyone who already has a database and wants to quickly add a data-entry interface (form), an Excel-like editable sheet (grid) or read-only reporting, without writing a frontend. Supported databases: PostgreSQL, MySQL/MariaDB, SQL Server, Oracle, SQLite and DuckDB. ## Installation Python 3.11 or later is required. A virtual environment is recommended: ```powershell python -m venv .venv .\.venv\Scripts\python.exe -m pip install -e . ``` Database drivers are **optional extras**: install only the ones you need. ```powershell # a specific driver .\.venv\Scripts\python.exe -m pip install -e ".[postgresql]" # or every driver .\.venv\Scripts\python.exe -m pip install -e ".[all-drivers]" ``` Available extras: `postgresql`, `mysql`, `mssql`, `oracle`, `duckdb`, `all-drivers`. SQLite ships with Python. ## Running the app From the project entrypoint: ```powershell python main.py --mode desktop # native desktop window (default) python main.py --mode web # web app at http://127.0.0.1:8080 ``` Or via the installed command: ```powershell dbvisual --mode desktop dbvisual --mode web --host 127.0.0.1 --port 8080 ``` In web mode the app stays bound to `127.0.0.1` (local only). The preferred startup mode can also be set from the Settings page. ## Core concepts - **Connection**: the parameters to reach a target database (credentials are stored as a secret, never in clear text). - **Query-spec**: the JSON spec describing the main table, columns and relations; the single source from which forms, sheets and reports derive. - **Application**: a logical grouping of definitions (form/sheet/report). - **Definition**: a single saved view (`form`, `sheet` or `report`) bound to a connection. Cross-cutting rule: writes happen **only** on the query-spec's `main_table`; related (lookup) columns are read-only. ## Connections On the **Connections** page you create and manage connections: 1. **New connection** → pick the dialect (PostgreSQL, MySQL/MariaDB, SQL Server, Oracle, SQLite, DuckDB, encrypted SQLite). 2. Fill in host, port, database/file path, user and password. 3. **Test** checks the connection; **Save** stores it. 4. **Browse schema** shows tables, columns (type/nullable/PK) and foreign keys. The password is handled as a secret (system keyring, with an encrypted `cryptography.Fernet` fallback). For **encrypted file databases** (SQLCipher for SQLite, native encryption for DuckDB) there is a dedicated passphrase field, also stored as a secret and never in clear text. ## Sheet (Excel-like editable grid) A **sheet** renders a table as an editable grid: - Editing only on `main_table` columns; related columns are read-only. - **Optimistic locking**: if the record was modified by someone else in the meantime, the save is rejected and the grid reloaded. - **Cell-level validation**: errors block the save. - **Computed (formula) columns** and **live totals**. - Toolbar: quick search, add/delete row, **TSV** copy/paste (to/from Excel), **CSV export**, group by column. - **Attachment columns**: a text column can become an attachments column (files on disk via the local store, JSON metadata in the cell); deleting the row cascades the files. - **Batch** save in a single transaction. ## Form (single-record data entry) A **form** shows one record at a time with prev/next navigation: - Typed inputs and **available values** (where the label differs from the stored value). - **Defaults**, per-field validation, **submit rules** (cross-field rules on save) and conditional **form rules** (show/hide/enable fields). - **Attachment fields** as in the sheet. - Transactional save with optimistic locking; deleting the record cascades its attachments. ## Report (read-only) A **report** is designed not to write to the database. The data source is the query builder or a **custom SQL**, filtered by `ensure_readonly` (`SELECT`/`WITH` prefix and lexical constraints). Treat that filter as a first layer, not a guarantee: read-only status must be enforced by the DB account's privileges — see *Security and data folder*. - Multi-value (`WHERE IN`) and cascading **parameters**; nested AND/OR **filters**; **full-text search** over the results. - **Grouping with subtotals**: group over multiple levels and compute subtotals (sum/avg/count/min/max); groups sort by caption or by subtotal. The grid shows group header rows, detail rows and (bold) subtotal rows. Grouping stays consistent with the full-text search. - `ui.echart` **charts**: column, line/time-series with zoom, pie. - **CSV export** and point-in-time **snapshots** (see below). ### Snapshots (point-in-time) Freeze the current rows (already filtered/grouped) into portable artifacts saved in the `snapshots/` folder inside the user data directory: - **HTML** — a single self-contained file (inline style and data, no external assets, no DB), reflecting exactly what is on screen, subtotals included. - **Excel** — an `.xlsx` with the detail rows and, when grouping is active, group header and subtotal rows. ### Saved views Sheets and reports can save named **views** (search, grouping, column config): - **Private** (visible only to the current identity), **shared** (visible to everyone) or **locked** (immutable). - Reload or delete them from the **Views** dialog. They are stored in the local metadata store. ## Master-detail A **master-detail** joins a master form to one or more linked detail grids. The detail query has exactly one parameter bound to the master PK. Saving master + all details happens in a **single transaction**; a new master PK is propagated to the new detail FKs. Covers one-to-many and many-to-many relations. ## Database (schema / DDL management) The **Database** tab is a schema browser and editor. Every change **composes DDL** and shows the exact SQL in a **"Review and execute"** dialog; execution only happens after explicit confirmation (**double confirmation** for destructive operations). - Create/drop tables, add/remove columns, foreign keys. - **CSV import/export**, relationship diagram (`ui.mermaid`), **AI**-generated DDL (optional). - The DDL (write) channel is separate from `ensure_readonly`. A user with DDL privileges is required. ## Automation / Webhooks On create/update/delete an optional non-blocking **HTTP POST (JSON)** can be sent to configured URLs (Zapier/Slack/Discord/custom endpoint). Body placeholders: `{{field}}` / `{{field:formatted}}` / `{{field:bare}}`. Webhook URLs are treated as **secrets**. Configuration is per sheet/form via the **Webhook** button. ## Row-Level Security (PostgreSQL) RLS is **delegated to PostgreSQL** (the user's SQL policies): visualdb only passes the current identity via `SET app.current_user_email`. It is enabled per query-spec (Postgres only), with a local identity email. **Note**: the connection must use a role that is **NOT** superuser and **NOT** the table owner, otherwise RLS is bypassed. ## AI assistant (optional, off by default) Generates **read-only** SQL for Reports from natural language, via a chosen LLM provider (Claude / OpenAI / Gemini / DeepSeek) with a user **API key** stored as a secret. The generated SQL is **always shown for review** and validated by `ensure_readonly`; it is never executed automatically. When using the AI, the DB structure (table and column names) and the request are sent to the chosen cloud provider (per-token cost is on the user). ## Settings The **Settings** page is the **single source** of configuration: - **AI**: enable/disable (off by default), provider, model and **API key** (stored only as a secret; shown as *set / not set*, never the value), with test and delete. - **Identity / RLS**: the email used as `app.current_user_email` (empty = RLS inactive). - **General**: preferred startup mode and the **user data directory** (shown read-only). ## Security and data folder - Application-generated values always go through **bound parameters**. That is the correct defence against injection on values, and it should be stated for what it covers: it does **not** make an arbitrary SQL fragment, a dynamically built identifier or a hand-written query safe. For custom SQL see the next point. - Read-only reporting **must not depend on the parser**. The `ensure_readonly` check (`SELECT`/`WITH` prefix, lexical constraints) is useful as *defence in depth*, but a text filter is not a security boundary: that comes from giving the DB account used for reporting **genuinely** read-only privileges and, where the DBMS supports it, opening the transaction `READ ONLY`. If a report must be guaranteed not to write, the control belongs in the permissions, not in the grammar. - Writes only on the `main_table`; read-only lookup columns. - **Secrets** (passwords, passphrases, API keys, webhook URLs) are never stored in clear text in the metadata store or logs. - The metadata store, attachments, secrets vault and `snapshots/` folder live in the **user data directory** (resolved via `platformdirs`), shown in Settings. ### The threat model, in one table The points above read better lined up, because each has a precise boundary and the third column is the one that matters: | Surface | What defends it | What the defence does **not** cover | |---|---|---| | User values typed into grids and forms | bind parameters, always | dynamically built identifiers (table or column names) and hand-written SQL fragments | | Custom Report SQL | `ensure_readonly` + DB account privileges + `READ ONLY` transaction | `ensure_readonly` alone: it is a text filter, not a boundary. The boundary is the privileges | | Visibility between users on the same tables | PostgreSQL RLS, with user-written policies | nothing on SQLite, which has no RLS; and nothing if the role bypasses it (see below) | | Credentials and keys | secret vault, never in clear text in the metadata store or the logs | the security of the operating system and of the user profile the application runs under | | Data leaving the machine | nothing, by construction: webhooks and AI are **opt-in** | once enabled they are third-party transfers in every sense — see the dedicated section | The row to re-read is the second. A text filter on the SQL is useful because it catches the careless mistake, but an attacker who controls the query text has far more grammatical forms available than a prefix can exclude: the guarantee that a report will not write is obtained by removing the account's permission to write, not by looking at how the string starts. ### The four conditions that switch RLS off The warning about a non-superuser, non-owner role is correct but incomplete. In PostgreSQL, RLS does not apply in four cases, and the first three are silent — no error, you see every row: | Condition | Effect on RLS | How to check it | |---|---|---| | the role is a **superuser** | always bypassed | `SELECT rolsuper FROM pg_roles WHERE rolname = current_user;` | | the role has the **`BYPASSRLS`** attribute | always bypassed | `SELECT rolbypassrls FROM pg_roles WHERE rolname = current_user;` | | the role **owns** the table | bypassed, unless `FORCE ROW LEVEL SECURITY` | `\dt` in psql, colonna Owner | | RLS enabled but **no policy** defined | denies everything: zero rows, not an error | `SELECT * FROM pg_policies WHERE tablename = '...';` | The last case is the most disorienting because it goes the other way: **enabling RLS without writing any policy denies access to everything**, and the application shows an empty table rather than an error message. Anyone who has just run `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` and sees zero rows does not have a connection problem: they have a table with no policy. `FORCE ROW LEVEL SECURITY` is the only way to subject the table's owner as well, and that it is needed when the application account is also the schema owner — a configuration that is convenient in development and switches RLS off without saying so. ## What leaves the machine, and when Two features send data to third parties. Both are **off by default** and must be enabled explicitly, but once active they are transfers in every sense — and on real working data that has consequences which are not technical. **Webhooks** send the content of rows. The `{{field}}` placeholders in the POST body are replaced with the actual values, so the recipient receives data: enabling a webhook on a table containing personal data is a communication to a third party, with the same legal basis any other would need. Treating the URL as a secret protects the *channel*, not the data going through it. The **AI assistant** sends the **schema** and the request, not the data: table and column names. That is a substantial difference and worth stating, but it does not remove the problem, because a schema can be revealing on its own. A table called `oncology_patients` with a `diagnosis_date` column tells you the nature of the processing even while completely empty; a client's name inside a table name is a client's name disclosed. In a regulated context the assessment has to be made on the schema as it would be on the data. The operational consequence is simple, and it lies in the way the project is built: both stay **off** until you decide otherwise, and that decision is the point at which the assessment belongs — not afterwards. ## Packaging (standalone executable) You can produce a standalone executable with **nicegui-pack** (a PyInstaller wrapper): ```powershell nicegui-pack --onefile --name dbvisual main.py ``` In the repository, `docs/packaging.md` lists the required **hidden imports** (DB drivers, `duckdb`, `keyring`, `cryptography`, `openpyxl`, AI modules) and an 8-item manual **acceptance checklist**. User data always resolves via `platformdirs`, never inside the PyInstaller temporary folder; if SQLCipher is absent, encrypted-SQLite connections degrade with a clear message while everything else keeps working.