Cross-Database Federation: Joining PostgreSQL and MongoDB in a Single Query

There’s a question we get asked a lot: "My user data is in PostgreSQL and my analytics events are in MongoDB. Can I join them?"

Until now, the answer meant a Python script, an ETL job, or a migration. With QoreDB’s cross-database federation, the answer is a single SQL query.

SELECT u.email, COUNT(e._id) as events
FROM prod_pg.public.users u
LEFT JOIN analytics_mongo.events e
ON e.user_id = u.id
GROUP BY u.email
ORDER BY events DESC

PostgreSQL on the left (with its public schema), MongoDB on the right (the collection directly), a JOIN in the middle. That’s it. In this article, we explain how it works under the hood — and why it was far from trivial to build.

The concrete problem

Modern architectures fragment data. The transactional database lives in PostgreSQL. Application logs end up in MongoDB. The data warehouse runs on a legacy SQL Server. And one day, someone needs to cross-reference data between two of these systems.

The classic solutions are heavy: writing a script that exports from one side, transforms, and loads into the other. Standing up an ETL pipeline. Or duplicating the data into a data lake. For a one-off exploration, these approaches are disproportionate.

QoreDB is already connected to all of these databases. The idea behind federation is simple: since the data is already accessible, why not join it directly?

The idea: DuckDB as an ephemeral layer

Federation rests on an architectural intuition: use DuckDB — an in-memory OLAP engine — as a temporary working table.

The principle is as follows. QoreDB fetches the data from each source in parallel, loads it into an ephemeral DuckDB instance created in memory, then runs the federated query inside DuckDB. Once the results are returned, the DuckDB instance is released. Nothing persists.

DuckDB is ideal for this role: it is embeddable (no server), optimized for analytical queries (aggregations, joins, sorts), and able to ingest data quickly. It is a temporary query engine, not permanent storage.

The four phases of the pipeline

Phase 1: query parsing

When the user writes prod_pg.public.users, QoreDB detects a compound identifier whose first part matches a known connection alias. That’s the signal that this is a federated query.

The format adapts to the source engine. PostgreSQL uses three or four parts (connection.database.table or connection.database.schema.table) to reflect its schema-based hierarchy. MySQL, MongoDB, and the other drivers use two parts (connection.table), since the database is already selected in the connection. The parser handles both formats transparently.

On the frontend, a quick regex detection shows a "Federated Query" badge in real time as you type. But the real analysis happens on the Rust side, with full AST parsing via the sqlparser crate. The parser extracts all federated references from the FROM and JOIN clauses, checks that the query is a single SELECT (mutations are rejected), and validates that each connection alias matches an active session.

This AST parsing is essential — no fragile regexes, no string-manipulation hacks. The query is analyzed as a syntax tree, which makes it possible to correctly handle subqueries, CTEs, CASE expressions, and all the complexity of real-world SQL.

Phase 2: planning

The planner takes the extracted references and builds an execution plan. For each source table, it resolves the connection alias to the session identifier, determines the query to send to the source, and generates a temporary table name for DuckDB (for example __fed_users_0, __fed_events_1).

An important detail: the planner generates the source queries with quoting adapted to each driver’s SQL dialect. PostgreSQL receives identifiers in double quotes ("users"), MySQL receives backticks (`profile`), and MongoDB receives a structured JSON query. This detail avoids the syntax errors that would arise if you sent PostgreSQL SQL to MySQL, or vice versa.

Then it rewrites the original query, replacing the federated identifiers with the DuckDB temporary table names. The SQL the user wrote is transformed into locally executable SQL, while preserving the full structure — joins, conditions, aggregations, sorting.

Phase 3: parallel data fetching

This is where Rust’s concurrency comes into play. The manager spawns a tokio task for each source and runs them in parallel. Each task sends a query tailored to its source database: a SELECT with the driver’s quoting for SQL databases, and a structured JSON query for MongoDB.

Each source has a 30-second timeout, and the overall pipeline is capped at 60 seconds. A default guard also limits each source to 100,000 rows — beyond that, a warning tells the user that the results may be truncated.

Parallelism is a real performance win. Joining two 10,000-row tables from different databases? Both fetches happen simultaneously in ~500ms, instead of ~1s sequentially.

Phase 4: execution in DuckDB

The fetched data is loaded into an in-memory DuckDB instance. For each source, QoreDB creates a temporary table with the detected schema, then inserts the rows in batches of 1,000.

For MongoDB sources, an extra step comes in: automatic flattening. BSON documents, which normally arrive as a single JSON column, are automatically flattened into individual columns — one per top-level key. This lets you write e.profileId or e.level in the federated query, as if the MongoDB collection were an ordinary relational table. Without this flattening, you would have to use JSON-extraction functions in every clause — which would ruin the ergonomics.

Types are converted along the way through a comprehensive mapping of more than 60 types. PostgreSQL arrays become VARCHAR (serialized as JSON), UUIDs are converted to text, network types (inet, cidr) and geometric types (point, polygon) are serialized as text, and date/time types are mapped to their DuckDB equivalents with time-zone handling. MySQL and its specific types (TINYINT, MEDIUMTEXT, YEAR) are also covered. This conversion is pragmatic — it favors compatibility over perfect type fidelity.

Once all the tables are loaded, DuckDB runs the rewritten query. This is where the aggregations, sorts, and joins actually happen. DuckDB is optimized for this kind of analytical operation, far more than any of the individual source databases would be for a cross-database query.

The results are returned with detailed metadata: row count per source, fetch time per source, DuckDB execution time, and total pipeline time.

The interface: designed for exploration

Federation wouldn’t be usable without a suitable interface. QoreDB provides a source bar — a horizontal bar showing all active connections as clickable pills. Each pill shows the driver icon and the connection alias.

Clicking a connection opens a popover showing the database tree: schemas, tables, columns. Clicking a table inserts it intelligently into the editor based on the cursor context — as a JOIN, LEFT JOIN, WHERE clause, or a simple reference. Keyboard shortcuts speed up the process: J for a JOIN, L for a LEFT JOIN.

For new users, a contextual empty state guides onboarding. If only one connection is active, QoreDB points out that at least two are needed. With two or more connections, a query template is offered with a "Try it" button that pre-fills the editor.

Concrete example: cross-database audit

Let’s take a real case. You have a production PostgreSQL database with user accounts, and a MongoDB database collecting application events. Your PM wants to know how many users created in 2025 generated more than 50 events.

SELECT
u.id,
u.email,
u.created_at,
COUNT(e._id) as event_count
FROM prod_pg.public.users u
LEFT JOIN analytics_mongo.events e
ON e.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.email, u.created_at
HAVING COUNT(e._id) > 50
ORDER BY event_count DESC

Here’s what happens behind the scenes:

1. The parser identifies prod_pg and analytics_mongo as federated references — three parts for PostgreSQL (with the public schema), two parts for MongoDB (direct collection).
2. The planner generates two dialect-adapted source queries: SELECT * FROM "users" LIMIT 100000 with double quotes for PostgreSQL, and a structured JSON query for MongoDB.
3. Both fetches run in parallel via tokio.
4. The MongoDB documents are automatically flattened into individual columns.
5. The results are loaded into DuckDB, which runs the GROUP BY, HAVING, and ORDER BY.
6. The final result appears in the results grid, with the stats for each source.

The query took 800ms. Without federation, you would have written a script, waited for it to run, debugged incompatible types, and lost half an hour.

Security and safeguards

Federation is constrained by design. Only SELECT queries are allowed — any attempt to INSERT, UPDATE, or DELETE on a federated source is rejected by the parser. Multi-statement queries are also blocked.

Full AST parsing prevents SQL injection: connection identifiers are validated against the list of active sessions, and source queries are built programmatically with dialect-adapted quoting, never by string concatenation.

The 100,000-row cap per source protects against memory overflows. And the timeouts (30s per source, 60s global) prevent zombie queries from blocking the application.

What’s left to build

Federation v1 is deliberately conservative on some points. WHERE filters are not yet "pushed down" to the sources — filtering happens entirely in DuckDB after loading. In practice, this means QoreDB potentially loads more data than necessary before filtering locally.

Likewise, each source is currently loaded with SELECT * — projecting only the needed columns is not yet implemented.

These optimizations (predicate pushdown and columnar projection) are on the roadmap. They will reduce the volume of data transferred and speed up queries on large volumes. But even without them, federation is already functional and performant for everyday data exploration.

Why it matters

Cross-database federation is not a gimmick feature. It’s the answer to a problem every developer runs into: data doesn’t live in a single database. It’s spread across relational stores, NoSQL, legacy systems, and sometimes a stray CSV file.

Existing tools — DBeaver, pgAdmin, MongoDB Compass — each excel on their own engine, but stop at the border of their database. QoreDB crosses that border. Not by abstracting away the differences between engines, but by making them cooperate for the duration of a single query.

SQL stays SQL. The databases stay independent. QoreDB just builds an ephemeral bridge between them, long enough to find the answer.