visualdb

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, 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:

python -m venv .venv
.\.venv\Scripts\python.exe -m pip install -e .

Database drivers are optional extras: install only the ones you need.

# 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:

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:

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:

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:

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):

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.