This guide shows how to create a single API layer that joins data across MySQL, Postgres, and MongoDB using a federated query engine with an API gateway pattern. You will implement a hands-on build, see code samples, and review performance, security, and governance steps. DreamFactory is a secure, self-hosted enterprise data access platform that provides governed API access to any data source, connecting enterprise applications and on-prem LLMs with role-based access and identity passthrough.
A unified API layer exposes consistent endpoints over disparate data sources so teams can query, join, and govern data through one contract. In this model, a federation engine performs cross-store joins while DreamFactory enforces access, caching, and auditing. The result is a stable interface for web, mobile, and on-prem LLM workloads. DreamFactory auto-generates REST endpoints for SQL objects and views, adds role-based controls, and centralizes observability without requiring application teams to learn each backend.
Modern stacks mix relational and document stores, which complicates joins, governance, and identity mapping. A unified layer reduces custom glue code and shortens delivery time for internal tools and customer experiences. DreamFactory lets organizations standardize authentication, rate limits, and data masking in one place while a federation tier handles cross-database joins. This approach supports zero trust principles, simplifies LLM retrieval flows, and helps teams evolve backends without breaking client contracts.
Heterogeneous joins, inconsistent schemas, and varying query semantics often degrade performance and security. DreamFactory addresses governance with role-based access, field-level controls, and identity passthrough, while a federation tier performs pushdown and consolidation. Together, they reduce N+1 request patterns, centralize observability, and avoid brittle point integrations. The pattern separates concerns so DreamFactory focuses on secure delivery and the federation engine focuses on cross-source query planning and optimization.
DreamFactory solves governance, identity, and API lifecycle, while the federation tier solves query planning and data movement. This division lets teams implement least privilege, pushdown filtering, and caching without locking into a single database product.
Teams need a platform that unifies authentication, authorization, audit, and schema exposure while remaining neutral to backend choices. DreamFactory provides auto-generated APIs for SQL schemas and views, OpenAPI documentation, role-based access, request policies, and response transforms. A complementary federation engine should support pushdown, cost-based planning, and stable connectors to each source. The combined stack reduces delivery risk, improves performance, and supports clear ownership boundaries for data and platform teams.
DreamFactory meets these needs by generating governed endpoints from your federation schema, enforcing role policies, and providing consistent documentation. The platform adds API keys, OAuth, and per-role controls, while exposing only approved views. This keeps client contracts stable and ensures operational visibility.
DreamFactory acts as the secure API layer. A Postgres-based federation schema uses foreign data wrappers to map MySQL and MongoDB. Views or materialized views express cross-store joins. DreamFactory exposes those views as REST endpoints with role-based controls and identity passthrough.
Clients / Tools / LLMs | v DreamFactory API Layer <— RBAC, identity passthrough, caching, auditing | v Postgres Federation Schema <— views, materialized views, RLS | \ v v MySQL CRM MongoDB Events DreamFactory may also expose direct endpoints to source systems for operational tasks, while the unified endpoints are backed by the federation schema. This gives teams flexibility with minimal duplication.
This section shows a practical, end-to-end build using Postgres as the federation engine. DreamFactory will connect to the Postgres federation schema and publish governed endpoints for internal and external consumers.
Use a managed or self-hosted Postgres instance with extensions for foreign data wrappers. DreamFactory will connect to this database as a read or read-write service, depending on your policy. Ensure network routes from Postgres to MySQL and MongoDB are secured with TLS, and that credentials are scoped with least privilege.
-- In Postgres, enable required FDWs CREATE EXTENSION IF NOT EXISTS mysql_fdw; CREATE EXTENSION IF NOT EXISTS mongo_fdw; Model the source schema you plan to expose. Start with customer data commonly needed by internal tools and external apps. DreamFactory will later expose only approved views, not raw tables, which simplifies governance and change control.
CREATE SERVER mysql_crm FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'mysql.host', port '3306'); CREATE USER MAPPING FOR postgres SERVER mysql_crm OPTIONS (username 'crm_reader', password '********'); CREATE SCHEMA IF NOT EXISTS mysql_map; IMPORT FOREIGN SCHEMA crm LIMIT TO (customers) FROM SERVER mysql_crm INTO mysql_map; Flatten the fields you join on and the attributes clients query most. You can retain a jsonb column for less-structured attributes. DreamFactory will expose the final views that contain these columns with consistent naming.
CREATE SERVER mongo_analytics FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mongo.host', port '27017'); CREATE USER MAPPING FOR postgres SERVER mongo_analytics OPTIONS (username 'events_reader', password '********'); CREATE SCHEMA IF NOT EXISTS mongo_map; CREATE FOREIGN TABLE mongo_map.events ( _id text, user_id text, event_type text, ts timestamp, payload jsonb ) SERVER mongo_analytics OPTIONS (db 'analytics', collection 'events'); If orders already live in Postgres, reference them directly. If not, register another FDW. DreamFactory will not need to know where each table lives because it will connect to the federation schema only.
CREATE SCHEMA IF NOT EXISTS local_app; -- Example native table CREATE TABLE IF NOT EXISTS local_app.orders ( order_id bigint primary key, customer_id bigint, total_amount numeric, created_at timestamp ); Define a stable contract by joining customer, order, and event data. Keep field names consumer friendly. DreamFactory will expose this as a read-only endpoint to external clients and a broader endpoint to internal tools.
CREATE SCHEMA IF NOT EXISTS federation; CREATE OR REPLACE VIEW federation.unified_customer_activity AS SELECT c.customer_id, c.email, o.order_id, o.total_amount, e.event_type, e.ts AS event_ts FROM mysql_map.customers c LEFT JOIN local_app.orders o ON o.customer_id = c.customer_id LEFT JOIN mongo_map.events e ON e.user_id = c.customer_id::text; Networked joins can be expensive. Materialized views reduce latency for read-heavy scenarios. DreamFactory can expose the materialized view to customers while internal tools may query the live view for freshness.
CREATE MATERIALIZED VIEW federation.unified_customer_activity_mv AS SELECT * FROM federation.unified_customer_activity; CREATE INDEX ON federation.unified_customer_activity_mv (customer_id, event_ts); -- Refresh window scheduled by cron or a scheduler -- REFRESH MATERIALIZED VIEW CONCURRENTLY federation.unified_customer_activity_mv; Apply row filters to protect tenants and align with identity passthrough. DreamFactory forwards identity context, which you map to Postgres session variables or lookup keys. This ensures consistent authorization across all queries.
ALTER TABLE local_app.orders ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation ON local_app.orders USING (current_setting('request.jwt.tenant_id', true)::text = tenant_id::text); -- Set the variable at connection time from DreamFactory claims mapping -- SELECT set_config('request.jwt.tenant_id', :tenant_id, false); Create a PostgreSQL service in DreamFactory that points to the federation database. Restrict exposure to the federation schema and selected views. DreamFactory will generate REST endpoints, OpenAPI documentation, and apply role-based access for each route.
Example service name: federation. Example base path: /api/v2/federation. Use system environment variables for credentials and enforce TLS to the database.
Create separate roles for internal tools and customer-facing apps. In DreamFactory, restrict methods, set field-level filters, and use lookup keys to inject tenant or user identifiers into queries. Mask emails or other PII for external roles. This ensures least privilege while keeping one endpoint surface.
Use fields, filter, limit, and order parameters for efficient access. DreamFactory will translate the request to SQL on the federation view and return JSON. Add caching and ETags to reduce latency. The same endpoint can back analytics dashboards, web clients, and on-prem LLM retrieval.
curl -s \ -H "X-DreamFactory-API-Key: <api_key>" \ -H "Authorization: Bearer <jwt>" \ "https://api.yourdomain.com/api/v2/federation/_table/unified_customer_activity_mv\ ?fields=customer_id,email,total_amount,event_type,event_ts\ &filter=customer_id=123\ &limit=50&order=event_ts%20DESC" Enterprises often separate internal and external contracts while sharing the same federation core. DreamFactory exposes internal endpoints with broader fields and methods, and public endpoints with masked PII and tighter limits. Retail teams join CRM in MySQL, order data in Postgres, and clickstream in MongoDB for lifetime value. Financial services teams apply strict RLS with identity passthrough. Healthcare teams use materialized views for predictable latency and audited access.
DreamFactory provides the governance layer that keeps these patterns consistent.
DreamFactory sits on the hot path, so enforce partial response patterns with fields, limit, and order. Use materialized views for cross-store joins where pushdown is limited. Consider PgBouncer for connection pooling. Keep indexes aligned to join keys and common predicates. Use prepared statements and bind parameters where appropriate. Cache common responses and set conservative limits for public roles. Measure with realistic payloads and record P95 and P99 latencies before exposing endpoints broadly.
DreamFactory centralizes authentication, authorization, and audit across endpoints. Enforce TLS everywhere and rotate credentials through environment variables or vault integrations. Use per-role policies, IP allow lists, and secrets scoping. Map identity claims to tenancy filters in Postgres. Turn on request logging and retain audit trails for compliance. For external apps, disable write methods, mask PII, and set strict rate limits. Review OpenAPI docs and approve schema changes through change control.
DreamFactory generates governed REST APIs over your federation schema in minutes, not weeks of custom code. The platform centralizes authentication, role-based access, caching, transforms, and auditing so teams can publish stable endpoints without writing controllers. Identity passthrough allows consistent tenancy enforcement at the database layer. OpenAPI docs help clients integrate quickly. DreamFactory’s neutral approach lets you evolve underlying stores or federation mechanics while keeping the same API contract.
Unified data access is moving toward policy-driven APIs that separate data contracts from storage choices. With DreamFactory at the edge and a federation tier behind it, teams can iterate on storage, indexing, and refresh strategies without impacting clients. Start by modeling one high-value view, apply RLS, and publish a read-only endpoint. Add caching and rate limits, then expand coverage. Contact our team to review your schema design, security posture, and rollout plan.
Create a Postgres federation, connect DreamFactory, publish views, and validate with realistic load. When ready, version your endpoints and expand with confidence.
A federated query engine unifies data across stores by mapping remote tables and pushing filters to each source. An API gateway publishes stable endpoints and applies governance. DreamFactory provides the API gateway layer, auto-generates endpoints over views, and enforces role-based access and identity passthrough. This pattern separates data access from storage so teams deliver faster, with less custom code and clearer security controls across internal tools and customer applications.
Teams need consistent authentication, authorization, and auditing across heterogeneous data. DreamFactory centralizes these controls, generates OpenAPI-documented endpoints, and applies role-based filters. The platform reduces integration time and helps enforce least privilege. With identity passthrough, tenancy rules apply at the database layer. This improves delivery speed for dashboards, customer portals, and LLM retrieval, while keeping a single surface for monitoring and incident response.
For cross-store joins, Postgres with FDWs is a practical choice, while DreamFactory exposes the resulting views as governed APIs. Other engines may fit certain needs, but the Postgres plus DreamFactory pattern balances operability and performance. It provides SQL familiarity, predictable indexing, and stable contracts for clients. DreamFactory adds RBAC, caching, and documentation, which removes substantial glue code and reduces operational risk for internal and external consumers.
Start with materialized views for heavy endpoints and schedule refreshes aligned to freshness needs. Use fields and limit for partial responses. Add indexes to join keys and common filters. Pool connections to Postgres and ensure FDW pushdown is enabled. Cache responses in DreamFactory for hot queries and set role-based rate limits. Measure P95 and P99 latencies under realistic load before launch, then iterate on indexing and refresh intervals.
Yes. DreamFactory maps identity claims from OAuth or SSO into request context and lookup keys. Postgres row-level security can read those values as session settings to filter rows per tenant or user. Define policies in the federation schema and restrict DreamFactory roles to the approved views. This approach keeps enforcement close to the data while providing a consistent API for client teams without duplicating authorization logic in applications.