Relational Database Support
Relevant source files
The following files were used as context for generating this wiki page:
KamehaDB provides robust support for relational database management systems (RDBMS), specifically focusing on PostgreSQL, MySQL, and SQLite. The system is designed as a local-first desktop application that allows developers to browse schemas, execute SQL queries, and visualize data structures without exposing credentials to external cloud services.
The architecture utilizes a unified SqlAdapter interface to ensure consistent behavior across different relational engines. This includes shared functionality for metadata extraction, connection testing, and data previewing, while also allowing for engine-specific extensions like PostgreSQL-rich statistics.
Sources: README.md:10-18, PLAN.md:5-15, AGENTS.md:130-135
Core Architecture and Adapter Pattern
Relational database support in KamehaDB is built around a provider-agnostic adapter pattern. This allows the frontend to interact with various SQL engines through a standardized set of methods.
The SqlAdapter Interface
The SqlAdapter interface defines the mandatory contract for any relational database supported by the sidecar. It covers the full lifecycle of database interaction, from initial connection testing to complex schema searches.
The diagram shows how different relational engines implement a shared interface to provide consistent data shapes to the desktop application.
Sources: packages/shared/src/index.ts:133-150, PLAN.md:155-170
Connection Management
Connections are defined by ConnectionProfile objects, which store metadata such as host, port, and database name. Sensitive credentials like passwords are not stored in the local SQLite metadata database but are instead handled via the OS keychain.
| Field | Type | Description |
|---|---|---|
id | string | Unique identifier for the connection profile |
kind | enum | Database type: postgres, mysql, or sqlite |
readonly | boolean | Defaults to true to prevent accidental destructive operations |
ssl | boolean | Optional toggle for encrypted connections (PostgreSQL/MySQL) |
filePath | string | Specific to sqlite for local file selection |
Sources: packages/shared/src/index.ts:10-30, PLAN.md:143-154
Data Flow and Execution
The application follows a strict request-response flow between the React-based desktop UI and the Node.js sidecar.
The flow ensures that all requests are validated for safety and normalized into a consistent format before reaching the UI.
Sources: PLAN.md:105-115, AGENTS.md:70-85
Read-Only Safety Guard
To protect data integrity, KamehaDB implements a "Read-Only" mode by default. The sidecar employs a SQL safety guard that parses incoming queries against a whitelist of safe keywords.
- Safe Keywords:
SELECT,WITH,SHOW,DESCRIBE,EXPLAIN. - Destructive Keywords:
DROP,TRUNCATE,ALTER,CREATE,INSERT,UPDATE,DELETE,MERGE.
If a query contains a destructive keyword while the connection is in read-only mode, the request is blocked before reaching the database.
Sources: packages/shared/src/index.ts:390-425, PLAN.md:320-328
Supported Engines and Features
While the SqlAdapter provides a baseline, specific engines support additional metadata and statistics features.
PostgreSQL
PostgreSQL is the most feature-rich relational engine in KamehaDB, supporting extended statistics for performance monitoring:
- Table Stats: Row estimates, total size, index size, and bloat analysis.
- Index Stats: Scan counts, read counts, and usage percentages.
- Connection Monitoring: Active PIDs, client addresses, and current query duration.
MySQL and SQLite
MySQL and SQLite support standard schema browsing (tables, columns, indexes) and data previewing. SQLite connections utilize local file paths, while MySQL follows a traditional host/port configuration.
Data Model and Seed Examples
Relational support is verified using standardized schemas during development, as seen in the provided seed files for PostgreSQL and MySQL.
| Table | Relation | Purpose |
|---|---|---|
organizations | One-to-Many with users | Manages tenant-level data |
users | Many-to-One with organizations | Stores user profiles and roles |
posts | Many-to-One with users | Content management |
comments | Many-to-One with posts & users | Interaction data |
Sources: docker-init/postgres/01-seed.sql:1-30, docker-init/mysql/01-seed.sql:1-40, packages/shared/src/index.ts:153-185
Schema Exploration and UI Components
The relational database support is surfaced in the UI through several specialized components:
- Schema Browser: A sidebar tree view for navigating databases, schemas, tables, and columns.
- Monaco SQL Editor: Provides syntax highlighting and FK-aware autocomplete for JOIN/ON suggestions.
- ER Diagram: Uses
ReactFlowanddagreto auto-generate visual representations of foreign key relationships derived from theSqlAdapter.getTableColumnsmetadata. - Table Preview: A grid-based view using
TanStack Tablefor paginated data browsing with sorting and filtering.
Sources: README.md:20-35, AGENTS.md:95-105, PLAN.md:275-295
Summary
Relational database support in KamehaDB provides a secure, local-first environment for managing SQL-based systems. By utilizing a standardized adapter pattern and strict SQL safety guards, the application ensures that developers can explore complex schemas and execute queries across PostgreSQL, MySQL, and SQLite with consistent performance and high reliability. The architecture prioritizes data safety through default read-only connections and leverages OS-native security for credential management.