DuckDB as a federation engine: why and how

When you work with several databases in parallel, one question comes up quickly: how do you cross-reference PostgreSQL data with MongoDB data without exporting everything by hand? Most tools sidestep the problem by offering superficial unified views or by delegating to an external middleware. QoreDB takes a different direction: embedding DuckDB as an ephemeral SQL engine to run cross-database joins locally.

The central idea is simple. Rather than forcing each driver to support a federation protocol, you extract the data from each source in parallel, load it into an in-memory DuckDB instance, and run the federated query locally. DuckDB serves as a temporary compute engine, not as storage. Each query creates a fresh instance that disappears after execution.

The five-step federation pipeline

The mechanism rests on a pipeline orchestrated by the FederationManager on the Rust side. The first step is parsing. The user writes a standard SQL query, but uses three- or four-part identifiers to reference tables on different connections. For example, prod_pg.public.users designates the users table in the public schema on the connection aliased prod_pg. The parser, based on a SQL AST, detects these compound identifiers and builds a map of the sources to query.

Second step: planning. The planner resolves each alias to an active connection session and generates a plan of source queries. Each federated table is associated with a temporary local alias, such as __fed_users_0, and the original query is rewritten to use these aliases in place of the compound identifiers. This rewrite traverses the entire AST: FROM, JOIN, WHERE, SELECT, GROUP BY clauses, subqueries, and CTEs.

Third step: the parallel fetch. The manager uses tokio::spawn to fire the queries on all the sources simultaneously. Each source has a 30-second timeout and a limit of 100,000 rows by default. If a source hits this limit, a warning is surfaced in the result metadata, but execution continues.

Fourth step: the load. The retrieved data is inserted into temporary DuckDB tables via transactions in batches of 1,000 rows. A type-mapping system converts each source engine's native types to the corresponding DuckDB types. More than 50 mappings are handled, with a fallback to VARCHAR for unrecognized types.

Fifth and final step: execution. The rewritten query is run on the local DuckDB instance. DuckDB handles the joins, aggregations, and filters with its OLAP optimizer. The results are returned to the frontend, accompanied by detailed metadata: fetch time per source, row count, DuckDB execution time, and any warnings.

Why DuckDB and not another engine

DuckDB ticks several essential boxes for this use case. It is an embeddable analytical SQL engine, designed to run in-process, with no external server. It integrates natively in Rust through C bindings, which matches QoreDB's Tauri architecture. Its OLAP orientation makes it performant on joins and aggregations, exactly the kind of operations you expect from a federation.

The choice to instantiate DuckDB ephemerally, in memory, for each federated query is deliberate. It avoids any consistency problem between the local data and the remote sources. There is no cache to invalidate, no persistent state to maintain. Each execution starts from scratch with freshly retrieved data.

The MongoDB case: flattening NoSQL into relational

Federating SQL databases with each other is relatively straightforward: the data already has a tabular structure. MongoDB poses a different challenge. BSON documents have a flexible schema, sometimes heterogeneous from one document to another. The federation pipeline includes a dedicated flattening step. When the data comes from MongoDB, the manager extracts all the unique keys from the whole set of retrieved documents, creates dynamic columns, and converts the JSON values into relational types. A document with the fields userId and name becomes a row with two typed columns. Complex values (nested objects, arrays) are serialized as JSON text.

This mechanism lets you write a join between a PostgreSQL table and a MongoDB collection in a single SQL query. The developer does not need to export by hand, transform the data in an intermediate script, or set up an ETL infrastructure. Everything happens locally, in the QoreDB process.

Batch and streaming: two execution modes

The federation supports two modes. In batch mode, all the data is loaded into DuckDB, the query is executed, and the complete result is returned in one block. This is the default mode, suited to results of reasonable size. In streaming mode, the pipeline stays identical for the fetch and load phases, but the results are sent row by row to the frontend through the Tauri channel. The frontend first receives the columns, then the rows as they come, and finally an end event. This mode is useful when the result of the federated join is large and the user wants to see the first rows quickly.

In practice: transparent SQL rewriting

From the user's point of view, federation comes down to a naming convention. You prefix the tables with the connection alias and the namespace. The rest of the SQL syntax is standard. For example, to join PostgreSQL users with MySQL orders, you write something like SELECT u.name, o.total FROM prod_pg.public.users u JOIN staging_mysql.shop.orders o ON u.id = o.user_id. The parser detects the two sources, the planner generates the extraction queries, and DuckDB executes the join.

The federation metadata returned to the frontend includes the fetch time per source, the number of rows extracted, the DuckDB execution time, and the list of any warnings (row limit reached, timeout approached). This information is visible in the interface, which gives the developer a clear view of what happened during execution.

A local compute engine, not a data warehouse

DuckDB in QoreDB is not used as a data warehouse. There is no persistence, no view materialization, no refresh scheduling. It is a one-off compute engine, activated on demand, that disappears after each query. This positioning is consistent with QoreDB's desktop philosophy: everything stays local, everything is ephemeral by default, and the user keeps control over what is executed.

Federation via DuckDB turns QoreDB from a simple multi-database client into a tool capable of cross-referencing data from any combination of supported sources, in a single SQL query, with no additional infrastructure. The pipeline is entirely local, parallelized, and designed for interactive use by a developer or an SRE who needs quick answers over distributed data.