Not all databases declare their relations. MongoDB collections have no notion of a foreign key. SQL schemas inherited from a decade of migrations often end up with columns linked to one another, but without a FOREIGN KEY constraint to formalize it. Analytical databases sometimes come with no constraints at all, by choice, for ingestion performance reasons.
In a database client, this absence is quickly felt. FKs enable navigation between tables, the Foreign Key Peek, the rendering of the ER diagram, or simply help you understand how the data fits together. Without declared FKs, these features fall away. Virtual Relations make it possible to fill this gap locally, without touching the database's schema.
A client-side metadata layer
A Virtual Relation is an explicit declaration: column X of table A references column Y of table B. This information is stored entirely within QoreDB. No query is run on the database to create it, no constraint is added, no schema modification takes place.
The data model is minimal: a unique identifier, a source (database, optional schema, table, column) and a target (table, column, plus optionally a different schema and database). You can therefore link two tables in the same database, two tables from different schemas, or even two collections belonging to distinct databases on the same server. An optional label lets you name the relation so you can find it easily.
Merging with the real schema
When QoreDB asks a driver for the description of a table via describe_table, it retrieves the FKs declared in the database: for PostgreSQL via the system catalogs, for MySQL via information_schema, for SQLite via PRAGMA foreign_key_list. The Virtual Relations are then loaded from the local store and merged with these real FKs.
The merge follows a simple rule: if a Virtual Relation duplicates a real FK (same source column, same target table, same target column), it is ignored. Otherwise, it is added to the list with an is_virtual flag set to true. The rest of the application works with a uniform list of FKs, without having to distinguish their origin, except when that distinction is useful for display.
Local persistence, one file per connection
Virtual Relations are persisted on disk, in the application's data directory, as JSON files. Each connection has its own file, identified by its ID. This separation simplifies management: deleting a connection removes its associated Virtual Relations, and exporting a connection also exports the virtual FKs defined on it.
The format is versioned by a version field, which leaves the door open to evolving the storage schema without breaking existing files. The in-memory store is protected by a RwLock on the Rust side: reads are concurrent, writes are serialized, and the cache is reloaded from disk only the first time a connection is consulted.
Rendering in the ER diagram
The main value of a Virtual Relation is seeing it appear in the ER diagram. QoreDB renders virtual FKs with a dashed line distinct from real FKs, and a (virtual) label on hover. This visual distinction is intentional: a developer reading the diagram should be able to separate what the database officially declares from what has been added on the client side.
The Foreign Key Peek also uses these relations. Hovering over a cell whose column is referenced by a Virtual Relation triggers the same preview query as for a real FK. Navigation between tables works too: a click on a referenced value opens the target table and filters on the corresponding value.
Creating a Virtual Relation in practice
The user opens the Virtual Relations panel from a table's context menu, or via the ER diagram. The dialog offers to select the source table, the source column, then the target table and the target column. The list of tables comes from listCollections, the list of columns from describeTable: QoreDB relies on the drivers to offer only valid targets.
Once confirmed, the relation is persisted immediately. The ER diagram and the table detail views refresh to incorporate the new FK. The operation is purely local, instantaneous, and reversible: deleting the relation removes it from the JSON file and from the diagram, without leaving any trace in the database.
A local layer on top of the schema
Virtual Relations solve a specific case: enriching the schema as perceived by the client without modifying the database. It is a strictly local metadata layer, aligned with QoreDB's local-first model. The database remains the source of truth for what it declares, and the user can add their own relational annotations on top, with no risk to the data. For MongoDB databases, legacy schemas, or analytical datasets without constraints, this layer turns a partial view of the model into something usable day to day.
Stay updated on new releases
Subscribe to get product releases, new drivers notifications, and technical tutorials.