Foreign Key Peek: seeing relationships on cell hover

In a SQL client, reading a table full of foreign keys is frustrating. An author_id column shows 42, and to find out who that is you have to open a new tab, write SELECT * FROM authors WHERE id = 42, then go back to the original row. Context is lost, friction accumulates.

Foreign Key Peek addresses this need in QoreDB. When a cell contains a foreign key value, hovering over it triggers a tooltip that shows the columns of the referenced row. No click, no new tab, no hand-written query. The feature is simple on paper, but every technical decision matters for it to stay fast and predictable.

On-demand query, never preloaded

The Peek doesn't anticipate. As long as the user doesn't hover over a cell, no query is sent. This choice avoids two classic traps. First, automatic preloading would multiply network round-trips on a page that easily contains 50 rows with 3 FKs each, i.e. 150 sub-queries for nothing most of the time. Second, such preloading would push the driver to run expensive queries at page-load time, whereas the goal is to display the main result quickly.

The logic is as follows: on hover, the useForeignKeyPeek hook calls ensurePeekLoaded, which triggers the Tauri command peek_foreign_key if the value is neither loading nor already cached. A Set of in-flight requests avoids duplicates when the mouse quickly enters and leaves the cell.

What the driver actually executes

The DataEngine trait exposes a peek_foreign_key(session, namespace, foreign_key, value, limit) method. The default implementation returns NotSupported. Each SQL driver overrides this method and builds a parameterized query that always has the same shape:

SELECT * FROM {referenced_table} WHERE {referenced_column} = ? LIMIT {limit}

Three precautions accompany this query. The table name and column name go through quote_ident (double quotes on PostgreSQL, backticks on MySQL and MariaDB, brackets on SQL Server). The value, however, is always passed as a bound parameter, never concatenated into the SQL. The limit is clamped on the driver side between 1 and 50, then re-clamped on the Tauri command side to 25 maximum, then further reduced by the governance policy if the environment is marked Prod. The default value requested by the frontend is 6 rows.

PostgreSQL, MySQL, MariaDB, SQLite, DuckDB, CockroachDB, SQL Server, TimescaleDB, Neon and Supabase implement the method. MongoDB and Redis don't: they don't expose real foreign keys, and QoreDB refuses to emulate a relationship that doesn't exist at the engine level.

The React-side cache

The same FK value often appears several times in a page of results. Three rows with author_id = 42 should not generate three queries. The hook therefore maintains a cache in a Map<string, PeekState>, indexed by a key that combines the database, the schema, the referenced table, the referenced column and the serialized value.

The state of each entry follows a simple cycle: idle, loading, ready or error. The cache lives with the Data Grid component: it's cleared when the user changes table or re-runs the main query. It's a display cache, not an application cache. Nothing is persisted to disk, nothing is shared between tabs. The goal is solely to avoid redundant queries within a reading session.

The tooltip is built on Radix UI with a delayDuration of 300 ms. This delay is a compromise: too short, and the tooltip flashes during keyboard navigation or fast scrolling; too long, and it loses its usefulness. The content shows at most 3 rows and 6 columns of the referenced row. These bounds aren't technical but ergonomic: a tooltip that fills half the screen becomes a panel, and a panel justifies its own separate navigation.

When the referenced table has more columns, the tooltip explicitly indicates +N more columns. An Open table button opens the referenced table with a filter pre-applied on the FK. This filter is propagated as a RelationFilter object rather than a generated SQL query, which leaves the pagination and sorting logic intact on the new table.

Cross-schema and cross-database FKs

A foreign key can point to another schema (PostgreSQL) or to another database (MySQL). The ForeignKey model carries two optional fields, referenced_schema and referenced_database, that are filled in when the metadata allows. The driver uses them to correctly prefix the table reference in the query. If these fields are absent, the current namespace serves as a fallback.

A table can also have several FKs on the same column. It's rare but legal, especially in legacy schemas. The tooltip explicitly signals Multiple relations detected and the hook exposes a foreignKeyMap indexed by column so as not to lose the secondary FKs.

What the user sees in practice

In normal reading, you browse an orders table that references customers by customer_id and products by product_id. FK cells are subtly underlined on hover. After 300 ms, a tooltip appears with the key columns (name, email, category). A click on Open table opens the referenced table already filtered on the relevant row. No SQL was written by hand.

On a flat or denormalized database without declared FK constraints, the Peek does nothing. This is consistent with the general principle: QoreDB doesn't guess relationships, it exploits the ones the engine exposes. For cases where you want to define a relationship client-side without touching the schema, the Virtual Relations feature covers the need and feeds the same Peek mechanism.

A small feature, a clear architectural choice

Foreign Key Peek illustrates a pattern found everywhere in QoreDB: every UX interaction is paid for by a real query on the database, but always on demand, always bounded, always transparent. No proxy, no intermediate indexing layer, no agent pre-warming caches in the background. The tooltip that appears under the mouse is the exact reflection of a SELECT ... LIMIT 6 that a DBA could have written themselves. It's this readability that makes the feature acceptable in a client aimed at backend developers.