Anyone with a database in production who has to give someone a controlled way to enter data and pull out reports knows the problem: the database is already there, the schema is already there, and what is missing is the interface. Writing it by hand for every table is work that never ends; picking up an application generator almost always means moving the data — or at least the credentials — into a service that runs somewhere else.
Visual DB solves the first half of the problem cleanly, and its insight is the one we took: in front of an existing database you need three things, not thirty. A form to work on one record at a time, a sheet to work on many records as in a spreadsheet, a read-only report to read them aggregated. Three pillars, and on top of those sits most of the office work done on data.
What did not suit us was the rest: the deployment model. Hence dbvisual.
What we kept and what we inverted
The three pillars stayed. The execution model is the opposite: the application installs and runs on the machine of whoever uses it. No cloud, no remote account, no multi-tenancy — and these are not features still to come, they are declared non-goals in the specification, because a written constraint is the only thing that holds up under the pressure of later requests.
The distinction that matters is between the application and its targets. Target databases can live wherever they like, including another server: pointing at a corporate PostgreSQL is the normal case. But the application and its metadata — definitions, saved views, secrets, attachments — always stay local. The criterion is BYOD in the literal sense: bring your own database, you connect to what is there.
For a supplier working on medical, industrial and scientific applications this is not an aesthetic preference. When data cannot leave the machine that produces it, a tool that requires uploading it elsewhere is not an option to be weighed: it is out of scope. A local tool, by contrast, can be brought inside a controlled environment and discussed with whoever has to authorise it.
The centre of the architecture: one spec, one compiler
The structural choice that paid off most is that there are no two distinct “form” and “report” entities. There is a query-spec, in JSON, declaring:
main_table— the main table, the only one writable in form and sheet;related[]— tables linked by foreign key, read-only;columns[]— the selected columns, with aliases;filters[]— the conditions, parametrised;params[]— the parameters, with multi-value and cascading support.
A single compiler turns the query-spec into a sqlalchemy.select(). Form, sheet and report are three different renders of the same specification; everything else is interface.
The practical consequence shows up in security. If writes are allowed on main_table only and lookup columns are read-only by construction, that is not a rule to remember on every screen: it is a property of the model, and it holds across all renders at once. The same goes for parameters, which are always bound — there is no point in the code where an SQL string is concatenated, so there is no point where injection can get in.
The build
Monolithic application: a single Python codebase, one process, one executable. Interface with NiceGUI, engine on SQLAlchemy Core 2.0. The same code runs as a native desktop window or as a local web app on 127.0.0.1; no separate frontend, no JavaScript build.
Three layers:
- core — dialect-independent: multi-dialect engine creation and connection test, schema reflection (tables, columns, types, foreign keys), Pydantic models of the query-spec, the compiler, generic CRUD with transactional master-detail and optimistic locking, DDL composition and execution;
- meta — local persistence: metadata store on SQLite via
platformdirs, secrets encrypted withkeyringand a Fernet fallback, local attachment store; - app — interface and application services.
Supported dialects are PostgreSQL, MySQL/MariaDB, SQL Server, Oracle, SQLite and DuckDB; drivers are optional extras, you install only what you need. Passphrase-encrypted local files are supported too: SQLite via SQLCipher, and DuckDB with the native encryption available from 1.4 onwards. If the SQLCipher driver is not installed the option is disabled with an explicit message instead of bringing the application down — which is the right behaviour for an optional dependency.
On the feature side, the ones that move real work: optimistic locking and batch saving in a single transaction, computed columns and per-field validation, atomic master-detail with the new master key propagated to new details, reports with multi-level grouping and subtotals, saved views that are private/shared/locked, point-in-time snapshots as self-contained HTML and Excel, CSV import/export, non-blocking webhooks on create/update/delete, and row-level security delegated to PostgreSQL policies — dbvisual passes the current identity with SET app.current_user_email and does not reimplement authorisation, which is the right place not to be creative.
Two choices worth explaining
DDL does not go through the read-only channel. The Database tab allows schema changes, but every change composes the DDL and shows it in a “review and execute” dialog: execution happens only after explicit confirmation, doubled for destructive operations. It is a channel separate from the ensure_readonly that protects reports, because the two have opposite requirements and conflating them is how you lose both.
The AI assistant is optional, off by default and read-only. It turns a natural-language request into SQL for reports, with the user’s API key held as a secret. The generated SQL is always shown for review and validated by ensure_readonly; it is never executed automatically. A query generator is not a reason to give up control over what touches the database.
Status and verification
Core tests run on in-memory SQLite and DuckDB, so they need no external database and no credentials. Integration tests on the server dialects — PostgreSQL, MySQL/MariaDB, SQL Server, Oracle — are opt-in: they run only if the environment variables point at a real database, and are skipped otherwise. Each exercises end-to-end the APIs the application actually uses: table creation via DDL, reflection, CRUD with optimistic locking, a compiled read-back, and a final drop.
The standalone package is built with nicegui-pack. Metadata, attachments, secrets and snapshots always resolve through platformdirs, never inside PyInstaller’s temporary folder: that is the classic defect of packaged executables, and it shows up as data that disappears on exit.
MIT licence. The repository card is in the Open Lab, with the user manual in the wiki.