PostgresD

Traditional database clients (such as pgAdmin, DBeaver, or DataGrip) often suffer from high memory overhead (Electron or Java VM footprints exceeding 400MB+ RAM), cluttered user interfaces with hundreds of nested submenus, and complex workflows for standard CRUD operations.

PostgresD was engineered to solve these pain points by offering: • Minimal Memory Footprint: Powered by Tauri v2 and Rust instead of Chromium/Electron, consuming a fraction of the RAM and CPU (~45MB–80MB baseline RAM). • Prisma Studio-Inspired Relational Navigation: Automatic detection of foreign keys and reverse references, enabling developers to traverse parent and child relations without manually writing JOIN queries. • Instant In-Place Editing: Double-click cell modifications, staging dirty edits with a safety review drawer before batch committing them inside an atomic transaction. • Native Security: Passwords are never stored in plain-text localStorage or JSON configuration files; they are delegated to native OS Keychains (macOS Keychain, Windows Credential Manager, Linux Secret Service). • Developer Ergonomics: Integrated Monaco SQL editor with active query cancellation, stream-based export pipelines, dark/light dynamic theme switching, and pixel-perfect header/footer symmetry.

Data manipulation in PostgresD blends instant UI responsiveness with strict transactional safety:

• Double-Click Cell Editing: Double-clicking any non-primary-key cell turns it into an active inline editor matching its schema type. • Dirty State Accumulator: Edits are staged in memory as PendingChange objects without triggering immediate destructive database writes. Dirty cells are visually highlighted. • Pending Changes Review Drawer: A slide-over panel displays the exact structured diff of pending insert, update, and delete operations with target primary keys and new values. • Atomic Batch Commit (apply_changes): Commits all pending changes within a single PostgreSQL transaction (BEGIN ... COMMIT). If any foreign key or check constraint fails, the entire transaction is rolled back (ROLLBACK) and the exact PostgreSQL error message is displayed. • New Record Modal & Batch Deletion: Auto-generates modal input forms based on column types and nullable flags, and supports multi-row checkbox selection for batch deletions. • Virtualized Performance: Utilizes @tanstack/react-virtual to render only visible DOM nodes, enabling smooth 60 FPS scrolling across 10,000+ row datasets with dynamic drag-to-resize columns.

PostgresD revolutionizes relational database exploration by eliminating manual SQL JOIN writing for standard entity navigation:

• Outgoing Foreign Key Badges: Foreign key columns display a distinct badge with an external link indicator. Clicking the badge extracts the foreign key value and queries the referenced parent record in real time. • Incoming Sub-Relation Synthesizer (Table []): PostgresD inspects the information_schema constraint graph to find all other tables referencing the active table. It synthesizes dynamic virtual count columns (e.g. orders [], reviews []). • Dual-Layout Reference Overlay Sheet: - Single Parent Record: Rendered as a clean vertical key-value attribute inspection sheet. - Multiple Child Records: Rendered as a nested, horizontally scrollable relational data table allowing sub-level filtering and inspection.

For complex analytical workflows, PostgresD provides an integrated SQL workspace:

• Full Monaco Editor Integration: Complete SQL syntax highlighting, auto-completion, multi-cursor editing, bracket matching, and multi-tab side-by-side query workspaces. • Asynchronous Tokio Execution: Queries run on independent Tokio worker threads, keeping the desktop UI responsive at all times. • Execution Telemetry: Displays accurate millisecond execution timers (execution_time_ms) and total affected row counts. • Active Query Cancellation (pg_cancel_backend): When a query begins, its PostgreSQL backend PID is registered in a thread-safe QueryRegistry. Clicking "Cancel Query" executes SELECT pg_cancel_backend(pid) on an independent connection, terminating long-running queries instantly without severing the client connection.

Because runtime table structures are dynamic, SQLx cannot use compile-time macros. PostgresD implements a generic type decoder (pg_row_to_json) matching on PostgreSQL column type OIDs:

• Booleans: BOOL → serde_json::Value::Bool • Integers & Floats: INT2, INT4, INT8, FLOAT4, FLOAT8 → serde_json::Value::Number • Strings & Text: VARCHAR, CHAR, TEXT, BPCHAR → serde_json::Value::String • UUIDs: Decoded via uuid::Uuid to canonical hyphenated strings • JSON / JSONB: Parsed directly into nested serde_json::Value objects • Date & Timestamps: TIMESTAMPTZ, TIMESTAMP, DATE, TIME decoded via chrono to ISO-8601 strings • Arrays & Enums: Homogeneous vector decoding with fallback UTF-8 byte conversion.

Dynamic filter expressions are compiled safely using parameterized SQL (, , ...) and identifier sanitization via quote_ident() to prevent SQL injection.

PostgresD includes a high-throughput export pipeline capable of exporting selected rows or entire tables:

• JSON & CSV Formats: Cleanly serializes records while stripping UI-only virtual relation columns. • Direct-to-Disk Streaming: Full-table exports stream asynchronously from PostgreSQL via sqlx::query().fetch() directly into a csv::Writer backed by std::fs::File, exporting gigabyte-scale datasets without hitting webview memory limits. • Excel Compatibility: Automatically injects a UTF-8 Byte Order Mark () at the start of CSV files, guaranteeing international characters render correctly in Microsoft Excel on Windows and macOS.

Security is enforced through native operating system integration and strict input hygiene:

• Zero Plain-Text Credentials: Password credentials are never saved in local storage or unencrypted config files. They are stored in native OS credential vaults (macOS Keychain, Windows Credential Manager, Linux Secret Service) via keyring-rs. • Parameterized Query Execution: All filter builder queries and mutation statements use parameterized SQL (, ), eliminating SQL injection vectors. • Identifier Sanitization: Schema, table, and column names are wrapped with quote_ident() to prevent identifier injection. • Sandboxed Desktop Runtime: The webview runs with minimal privileges restricted by Tauri v2 capability configuration files.

The project includes a comprehensive local Docker development environment and multi-platform build pipeline:

• Docker Relational Test Suite: Provides a complete e-commerce test schema (users, categories with self-referencing hierarchies, products, orders, order items, reviews, and JSONB audit logs). • Automated GitHub Actions CI/CD: Multi-target release workflow triggering on version tags to compile universal binaries for macOS (.dmg, .app), Windows (x86_64 NSIS .msi, .exe), and Linux (.deb, AppImage) with automated GitHub release drafting.

Shipped a production-ready, open-source cross-platform desktop PostgreSQL management studio distributed across macOS (.dmg, .app), Windows (.msi, .exe), and Linux (.deb, AppImage).